SELECT
	 e.id AS "activity_id"
	 , e.timestamp AS "ts"
	 
	 , NULL  AS "anonymous_customer_id"
	 
	 , p.email AS "customer"
	 
	 , 'opened_email' AS "activity"
	 
	 , e.property_subject AS "feature_1"
	 , e.property_client_os  AS "feature_2"
	 , e.property_client_type AS "feature_3"
	 , NULL AS "revenue_impact"
	 , 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(NVL(p.first_name,'')) || '-' || lower(NVL(p.last_name,''))  AS "link"

FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Opened Email'

SELECT
     e.id AS activity_id
     , e.timestamp AS ts
     
     , NULL  AS anonymous_customer_id
     
     , p.email AS customer
     
     , 'opened_email' AS activity
     
     , e.property_subject AS feature_1
     , e.property_client_os  AS feature_2
     , e.property_client_type AS feature_3
     , NULL AS revenue_impact
     , 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(coalesce(p.first_name,'')) || '-' || lower(coalesce(p.last_name,''))  AS link

FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Opened Email'

SELECT
     e.id AS activity_id
     , e.timestamp AS ts

     , NULL  AS anonymous_customer_id
     
     , p.email AS customer
     
     , 'clicked_email' AS activity
     
     , e.property_subject AS feature_1
     , e.property_client_os  AS feature_2
     , e.property_client_type AS feature_3
     , NULL AS revenue_impact
     , e.property_url AS link
     
FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Clicked Email'

SELECT
     e.id AS activity_id
     , e.timestamp AS ts

     , NULL  AS anonymous_customer_id
     
     , p.email AS customer
     
     , 'clicked_email' AS activity
     
     , e.property_subject AS feature_1
     , e.property_client_os  AS feature_2
     , e.property_client_type AS feature_3
     , NULL AS revenue_impact
     , e.property_url AS link

FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Clicked Email'

SELECT
     e.id AS activity_id
     , e.timestamp AS ts
     
     , NULL  AS anonymous_customer_id
     
     , p.email AS customer
     
     , 'unsubscribed_email' AS activity
     
     , e.property_subject AS feature_1
     , NULL  AS feature_2
     , NULL AS feature_3
     , NULL AS revenue_impact
     , NULL  AS link
     
FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Unsubscribed'

SELECT
     e.id AS activity_id
     , e.timestamp AS ts
     
     , NULL  AS anonymous_customer_id
     
     , p.email AS customer
     
     , 'unsubscribed_email' AS activity
     
     , e.property_subject AS feature_1
     , NULL  AS feature_2
     , NULL AS feature_3
     , NULL AS revenue_impact
     , NULL  AS link
     
FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Unsubscribed'

SELECT
	 e.id AS "activity_id"
	 , e.timestamp AS "ts"
	 
	 , NULL  AS "anonymous_customer_id"
	 
	 , p.email AS "customer"
	 
	 , 'received_email' AS "activity"
	 
	 , e.property_campaign_name AS "feature_1"
	 , e.property_subject  AS "feature_2"
	 , e.property_tags AS "feature_3"
	 , NULL AS "revenue_impact"
	 , 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(NVL(p.first_name,'')) || '-' || lower(NVL(p.last_name,''))  AS "link"

FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Received Email'

SELECT
     e.id AS activity_id
     , e.timestamp AS ts

     , NULL  AS anonymous_customer_id
     
     , p.email AS customer
     
     , 'received_email' AS activity
     
     , e.property_campaign_name AS feature_1
     , e.property_subject  AS feature_2
     , e.property_tags AS feature_3
     , NULL AS revenue_impact
     , 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(coalesce(p.first_name,'')) || '-' || lower(coalesce(p.last_name,''))  AS link

FROM klaviyo.event AS e
JOIN klaviyo.person p   
    on (p.id = e.person_id)
WHERE e.type = 'Received Email'