Documentation

Sendgrid

Suggest Edits

Opened Email

Customer opened an email

Type: Activity Transformation

Opens

SELECT 
    NVL(sg_event_id, sg_message_id) as activity_id
    
    , o.timestamp as ts 
    
    , NULL as anonymous_customer_id
    
    , email as customer
    , 'opened_email' as activity
    
    , o.category AS feature_1
    , NULL as feature_2
    ,  CASE 
			WHEN nullif ( substring ( regexp_substr ( useragent , '\ [^;| |/]*' ) , 3 , 30 ) , '' ) in ( 'iphone' , 'android' , 'blackberry' , 'ipad' ) 
      	then 'mobile' 
      ELSE 'desktop'
		 END as feature_3
    
    , NULL as revenue_impact
    , NULL as link
    
FROM sendgrid.opens o



Clicked Email

SELECT 
    NVL(sg_event_id, sg_message_id) as activity_id
    
    , c.timestamp as ts 

    , NULL as anonymous_customer_id
    
    , email as customer
    , 'clicked_email' as activity
    
    , c.category AS feature_1
    ,  CASE 
			WHEN nullif ( substring ( regexp_substr ( useragent , '\ [^;| |/]*' ) , 3 , 30 ) , '' ) in ( 'iphone' , 'android' , 'blackberry' , 'ipad' ) 
      then 'mobile' 
			ELSE 'desktop'
		 END as feature_2
    , NULL as feature_3
    
    , NULL as revenue_impact
    , c.url as link
    
FROM sendgrid.click c



Spam Reported

SELECT 
    NVL(sg_event_id, sg_message_id) as activity_id
    
    , s.timestamp as ts 

    , NULL as anonymous_customer_id
    
    , email as customer
    , 'reported_spam' as activity
    
    , s.category AS feature_1
    , NULL as feature_2
    , NULL as feature_3
    
    , NULL as revenue_impact
    , NULL as link
    
FROM sendgrid.spamreport s



Why not add Received email?
We recommend not adding the concept of a received_email as an activity because it leads to a lot of bad analysis. Receiving an email doesn't really mean anything to the user. If they open it then you know it was received by the user.

We have seen so many cases of people looking at conversion from received_email and thinking one campaign is better than another when it is nearly always driven by the opened_email

Tips:

šŸ‘

Contribute to the Narrator Community

Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.




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 almost 3 years ago

Whatā€™s Next

Don't see what you're looking for? Browse the Activity Index for more examples...