SELECT
	 e.event_id || e.collector_tstamp AS "activity_id"
	 , e.collector_tstamp AS "ts"

	 , e.domain_userid AS "anonymous_customer_id"
	 
	 , e.user_id  AS "customer"
	 
	 , 'viewed_page' AS "activity"
	 
	 , e.page_urlpath AS "feature_path"
	 
	 , nullif ( e.page_referrer, '' ) AS "feature_referrer"
	 , e.os_name  AS "feature_3"
	 
	 , NULL AS "revenue_impact"
	 , e.page_url AS "link"
	 
FROM snowplow.event e
where e.event = 'pv'

SELECT
	 activity_id
	 , date_add ( 'second' , -1 , ts ) AS "ts"
	 , anonymous_customer_id
	 , customer
	 , 'started_session' AS "activity"
	 , activity as feature_1
	 , NULL AS feature_2
	 , NULL AS feature_3
	 , NULL AS revenue_impact
	 , link
FROM (

		select
       e.event_id || e.collector_tstamp AS "activity_id"
       , e.collector_tstamp AS "ts"

       , e.domain_userid AS "anonymous_customer_id"

       , e.user_id  AS "customer"

       , 'viewed_page' AS "activity"

       , e.page_urlpath AS "feature_path"

       , nullif ( e.page_referrer, '' ) AS "feature_referrer"
       , e.os_name  AS "feature_os"

       , NULL AS "revenue_impact"
       , e.page_url AS "link"
      , lag (e.collector_tstamp) over ( partition by nvl ( e.user_id , e.domain_userid ) order by e.collector_tstamp ) AS "last_ts"

    FROM snowplow.event e
    where e.event = 'pv'
) AS s

WHERE date_diff ( 'minutes' , last_ts , s.ts ) >=30 
	OR last_ts is null

SELECT
	 *
	 , CASE 
		WHEN referring_domain in ( 'google' , 'yahoo' , 'bing' , 'ask' , 'aol' , 'baidu' , 'wolframalpha' , 'duckduckgo' , 'archive' , 'yandex' ) THEN 'Search' 
		WHEN referring_domain in ( 'facebook' , 'fb' , 'ig' , 'snap' , 'linkedin' , 't' , 'instagram' , 'youtube' , 'pinterest' , 'tumblr' , 'reddit' , 'flickr' ) THEN 'Social' 
		WHEN referring_domain is null or referring_domain ilike '%MYCOMPANYURL%' THEN 'Direct' 
		ELSE 'Referral'
	 END AS "referral_kind"
	 , CASE 
		WHEN gclid is not NULL or utm_medium is not null or (referring_domain =  'pinterest' and landing_url ilike '%epik=%') THEN 'Paid' 
		ELSE 'Organic'
	 END AS "medium_kind"
	 , CASE 
	    WHEN gclid is not null or utm_source in ('google') or (nvl(utm_medium, utm_source) is not null and referring_domain = 'google') then 'adwords'
	    WHEN utm_campaign ilike'%facebook%' or utm_source in ('fb', 'facebook') or  ( (nvl(utm_medium, utm_source) is not null and  referring_domain in ('fb', 'facebook', 'ig') ) )  then 'facebook'
	    WHEN utm_source in ('pinterest') or landing_url ilike '%epik=%' then 'pinterest' 
      ELSE 'Organic'
	  END as ad_source
FROM (
	SELECT
		 e.event_id AS "enriched_activity_id"
		 , e.collector_tstamp as enriched_ts
		 , nullif ( e.refr_urlquery , '' ) ::varchar ( 2000 ) AS "search"
		 , nullif ( e.page_referrer , '' ) ::varchar ( 2000 ) AS "referrer"
		 , nullif ( reverse ( split_part ( reverse ( replace ( regexp_substr ( e.page_referrer , '//[^/\\\,=@\\+]+\\.[^/:;,\\\\\(\\)]+' ) , '//' , '' ) ) , '.' , 2 ) ) , '' ) AS "referring_domain"
		 , e.geo_country
		 , e.geo_region
		 , e.geo_city
		 , e.geo_region_name
		 , e.geo_timezone
		 , e.os_name 
		 , e.os_family
		 , CASE WHEN e.dvce_ismobile THEN 'mobile' else 'desktop' end AS "device"
		 , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'fbclid=[^&]*' ) , 8 ) , '' ) AS "fbclid"
		 , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'gclid=[^&]*' ) , 7 ) , '' )  AS "gclid"
		 , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_source=[^&]*' ) , 12 ) , '' )  AS "utm_source"
		 , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_medium=[^&]*' ) , 12 ) , '' ) AS "utm_medium"
		 , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_campaign=[^&]*' ) , 14 ) , '' ) AS "utm_campaign"
		 , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_content=[^&]*' ) , 13 ) , '' )::varchar ( 255 ) AS "utm_content"
		 , e.page_url::varchar ( 255 ) AS "landing_url"
		 , e.page_urlpath::varchar ( 255 ) AS "landing_page"
	FROM snowplow.event e 
	WHERE e.event = 'pv'
)

SELECT
	 e.event_id AS "activity_id"
	 
	 , e.dvce_tstamp AS "ts"

	 , e.domain_userid AS "anonymous_customer_id"
	 
	 , e.se_label  AS "customer"
	 
	 , 'identified_visitor' AS "activity"

	 
	 , NULL AS "revenue_impact"
	 , e.page_url AS "link"
	 
FROM snowplow.event e
where e.event = 'se' 
		and se_action='identify'
    and e.se_label ilike '%@%'