Typically, you'll set up identity resolution by using the anonymous_customer_id column 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_customer_id and customer columns, and then all other activities using the same anonymous_customer_id will be able to benefit from that mapping.

Why to create a dedicated mapping transformation?

Efficiency in processing: Narrator updates identity resolution in time and we allow you to write new mappings in the past, this means we need to keep track of every mapping and use it to ensure if a transformation is later added which affects the past we can correctly process it. This expensive so better to maintain less mapping if it is not needed.

Simpler updates: When you want to update the mapping, it is better to update it in one place. For example, if you want to fix a couple of users who are mapped, having to resync all viewed pages is a lot of extra work, so with a smaller transformation it will allow only the difference in the data to be updated.

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 anonymous_customer_id and customer are non-NULL in order to associate a customer's anonymous_customer_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_customer_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)

How to use this with other transformations

Simply ensure all your NON mapping transformations just use the anonymous_customer_id and NULL for the customer.

select
    i.id as activity_id
    , i.timestamp as ts 
    
    , anonymous_id as anonymous_customer_id
    , NULL as customer  --THIS IS IMPORTANT
    
    , 'viewed_page' as activity 
    
    ...
    
    , NULL as link
    , NULL as revenue_impact
    
from segment_data.pages i 

Examples

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
    i.id as activity_id
    , i.timestamp as ts 
    
    , anonymous_id as anonymous_customer_id
    , nvl(u.user_email_address, i.email) as customer
    
    , 'identified_visitor' as activity 
    
    , 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, i.email) 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

select 
    p.enriched_activity_id as activity_id
    , p.enriched_ts as ts
    
    , p.anonymous_id as anonymous_customer_id
    , e2.email as customer
    
    , 'identified_visitor' as activity
    
    , NULL as revenue_impact
    , NULL as link
from dw.enriched_pages p
join (
        select
		    e.received_at
		    , NVL(recipient,email) as email
		    , e._ip as ip_address 
		    , e.original_link
		from postmark.events 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 
    u.id as activity_id 
    , u.updated_at as ts
    
    , u.phone_number as anonymous_customer_id 
    , u.email as customer 
    
    
    , "updated_phone" as activity 
    
    
    , 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

select 
    e._id as activity_id
    , e.created as ts 
    
    , p.anonymous_id as anonymous_customer_id 
    , e.email as customer 
    
    , "identified_visitor" as activity 
   	
    
    , NULL as revenue_impact 
    , NULL as link 

from calendly.events 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 
	 c.id AS activity_id
	 , min(e.ts) AS ts
	 
    , min(user_pseudo_id) as anonymous_customer_id
	 , min(c.email)  AS customer
	 
	 , 'identified_visitor' AS activity
	 
	 , NULL AS revenue_impact
	 , NULL AS link
	 
FROM shopify.order c 
JOIN (
    SELECT 
        TIMESTAMP_MICROS(event_timestamp) as ts,
        e.user_pseudo_id,
        (
        SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            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 c.email is not NULL 
    and lower(e.url) like '%/checkouts/%'
group by c.id

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