Narrator Documentation

Dedicated activities for identity resolution

Typically, you'll set up identity resolution by using the source and source_id columns in an activity, but sometimes you might want to go beyond that to define an activity that is specifically for identity resolution. This activity will maintain the link between the anonymous identifier (source_id) and customer columns, and then all other activities using the same source will be able to benefit from that mapping.

How to create a dedicated activity for user mapping:

An activity for user mapping is created in the same way that all other activities are created. It's important that the source, source_id and customer are non-NULL in order to associate a customer's anonymous source_id with their customer identifier.

Tip: Add a note in the description of the activity to let your team know that this activity is used for user mapping purposes. These types of activities are not usually used in analyses or datasets.

When to use:

Any time the linking between an anonymous ID (source_id) and the identified customer is available, but not already captured on another activity.

  • Identify events on your website (example)
  • IP address mapping from emails (example)
  • Associate phone numbers to customers from your database (example)
  • Identify customers from 3rd party sites using UTMs in the redirect links (example)


Identify Events on Your Website

It's common practice to use identify calls when a customer submits their email on your website. This allows you to associate their anonymous cookie with their email address and understand all the actions they did before they converted.

The example below shows an activity to represent the mapping between Segment's anonymous_id and the identified email address of the customer.

Example: Identified Visitor Activity

select as activity_id
    , i.timestamp as ts 
    , 'segment' as source
    , anonymous_id as source_id
    , nvl(u.user_email_address, as customer
    , 'identified_visitor' as activity 
    , NULL as feature_1
    , NULL as feature_2
    , NULL as feature_3
    , NULL as link
    , NULL as revenue_impact
from segment_data.identifies i 
left outer join db.users u 
    on (u.user_id = i.user_id)
where nvl(u.user_email_address, is not null

IP Mapping from Emails

Create an activity that maps a customer's email address to a customer's anonymous cookie through their IP address. By associating individual IP addresses with individual users during different moments in time, this allows Narrator to map often anonymous activities—like page views on your website that happened in the past—to identified users.

The example looks for email clicks and web visits with:

  • Same IP address
  • Happened within 5 minutes of each other
  • Link clicked in the email matches the URL visited on the website

Once these visitors are identified, we can link the email address from the email click to the anonymous ID of the web visitor.

Example: Identified Visitor Activity

    p.enriched_activity_id as activity_id
    , p.enriched_ts as ts
    , as customer
    , 'segment' as source
    , p.anonymous_id as source_id
    , 'identified_visitor' as activity
    , 'email_ip_mapping' as feature_1
    , NULL as feature_2
    , NULL as feature_3
    , NULL as revenue_impact
    , NULL as link
from dw.enriched_pages p
join (
            , NVL(recipient,email) as email
            , e._ip as ip_address 
            , e.original_link
        from e
        where e.record_type = 'Click'
            and message_id <> '00000000-0000-0000-0000-000000000000' -- the ID used for test events in Postmark

) e2
    on ( p.referral_url = e2.original_link and e2.ip_address = p.ip_address and 
ABS(DATE_DIFF('minutes', e2.received_at , p.enriched_ts)) < 5 )
where p.referral_url is not null

Phone Numbers From Your Database

Use the user profile in your database to identify customers who only share their phone number.

Example: Updated Phone Activity

SELECT as activity_id 
    , u.updated_at as ts
    , as customer 
    , 'phone_number' as source 
    , u.phone_number as source_id 
    , "updated_phone" as activity 
    , 'database_update' as feature_1 
    , NULL as feature_2
    , NULL as feature_3
    , NULL as revenue_impact 
    , NULL as link 

from db.users u
where u.phone_number is not NULL

UTMs in the redirect links

You can also use utm_params from systems that identify the users. For example, Calendly controls the redirect link after signing up. You can look at the utm_param and the page view and tie data in Calendly (has email) to the web visit.

This method can be very helpful when you use a lot of 3rd party tools in your sales process (Calendly, Eventbrite, etc).

Example: Identified Visitor Activity

    e._id as activity_id
    , e.created as ts 
    , as customer
    , p.anonymous_id as source_id 
    , 'segment' as source 
    , "identified_visitor" as activity 
    , "calendly_utm" as feature_1
    , NULL as feature_2
    , NULL as feature_3 
    , NULL as revenue_impact 
    , NULL as link 

from e 
join segment_data.viewed_pages p
    on (p.utm_content = e.event_id) 
where p.utm_content is not null and p.utm_source = 'calendly'

Shopify Checkout mapping

Shopify provides unique checkout URLs for each checkout.

This allows us to identify the user off of the unique checkout ID

Example: Identified Visitor Activity

SELECT AS activity_id
     , min(e.ts) AS ts
    ,'google_analytics' as source
    , min(user_pseudo_id) as source_id
     , min(  AS customer
     , 'identified_visitor' AS activity
     , NULL AS feature_1
     , NULL AS feature_2
     , NULL  AS feature_3
     , NULL AS revenue_impact
     , NULL AS link
FROM shopify.order c 
        TIMESTAMP_MICROS(event_timestamp) as ts,
            key = 'page_location') AS  url

    FROM `YOURSCHEMAHERE.events_*` as e 
    WHERE event_name in ('page_view')
) e
 on (c.checkout_token = nullif ( substring ( regexp_substr ( lower(e.url) , 'checkouts/[^(/|?)]*' ) , 11 ) , '' ) )
where is not NULL 
    and lower(e.url) like '%/checkouts/%'
group by

Still have questions?

Our data team is here to help! Here are a couple ways to get in touch...

💬 Chat us from within Narrator

💌 Email us at [email protected]

🗓 Or schedule a 15 minute meeting with our data team

Updated about a month ago

Dedicated activities for identity resolution

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.