Marketing channel groupings

For data modelers & consumers
6

Tweak the below to match your needs. (make sure you change the pseudo variables).

If you have classic ga.js (not analytics.js) still running, Snowplow tracker can pick up the cookie data and you can LEFT JOIN on events.event_id = com_google_analytics_cookies_1.root_id. Here is a link to that post if it applies: Use ga.js cookie campaign data in Snowplow atomic events

Classify marketing channels in Snowplow.

SELECT
  app_id,
  collector_tstamp,
  derived_tstamp,
EVENT,
  event_id,
  user_ipaddress,
  user_fingerprint,
  domain_userid,
  domain_sessionidx,
  network_userid,
CASE
  -- direct
WHEN refr_medium ILIKE '%unknown%' AND refr_source IS NULL AND mkt_network IS NULL AND mkt_medium IS NULL
THEN 'direct'
WHEN refr_medium IS NULL AND refr_source IS NULL AND mkt_network IS NULL AND mkt_medium IS NULL
THEN 'direct'
  -- organic search
WHEN refr_medium = 'search' AND refr_source IN ('Google', 'Bing', 'Yahoo!', 'DuckDuckGo', 'Ask', 'MySearch', 'Baidu', 'Yandex', 'Ask Toolbar', '360.cn', 'AOL', 'InfoSpace', 'Maxwebsearch', 'Findwide', 'Google Images', 'Interia', 'Bing Images', 'Genieo', 'Rakuten', 'Excite'
)
AND mkt_network IS NULL AND mkt_medium IS NULL
THEN 'search'
-- Paid Search
WHEN mkt_medium IN ('cpc', 'CPC', 'sem'
)
AND mkt_source IN ('GooglePaidSearch', 'BingPaidSearch', 'PaidSearchAdwords', 'msn_s', 'googlepaidsearch'
)
THEN 'paid_search'
WHEN mkt_network ILIKE '%Google%'
THEN 'paid_search'
WHEN mkt_medium = 'Chat'
THEN 'paid_search'
  -- Display
WHEN mkt_source IN ('Display', 'RTG'
)
THEN 'Display'
WHEN mkt_medium = 'CTABanner'
THEN 'Display'
  -- Social
WHEN mkt_source IN ('FacebookPaidSocial', 'Facebook', 'organic facebook', 'facebook.com'
)
THEN 'social'
WHEN mkt_medium ILIKE '%social%'
THEN 'social'
WHEN refr_medium ILIKE '%social%'
THEN 'social'
  -- emal
WHEN mkt_medium IN ('EDM', 'email', 'Email'
)
THEN 'email'
WHEN refr_medium = 'email'
THEN 'email'
END AS marketing_channel,
CASE
WHEN page_urlpath ~* '<my_conversion_url>' THEN INTEGER '1'
END AS web_conversion,
  geo_country,
  geo_region,
  geo_city,
  geo_zipcode,
  geo_region_name,
  page_url,
  page_title,
  page_referrer,
  page_urlhost,
  page_urlpath,
  page_urlquery,
  refr_urlhost,
  refr_urlpath,
  refr_urlquery,
  refr_urlfragment,
  refr_medium,
  refr_source,
  refr_term,
  mkt_medium,
  mkt_source,
  mkt_term,
  mkt_content,
  mkt_campaign,
  mkt_clickid,
  mkt_network,
  br_name,
  br_family,
  br_version,
  br_type,
  os_name,
  os_family,
  dvce_type
FROM atomic.events
WHERE app_id = '<my_app_id>'
AND event in ('page_view', 'unstruct')
AND page_urlhost NOT IN ('gtm-msr.appspot.com')
AND (refr_medium IS NULL OR refr_medium != 'internal')
AND derived_tstamp > current_date - integer '1'
ORDER BY domain_userid, domain_sessionidx, collector_tstamp
)
2 Likes