Marketing channel groupings

For data modelers & consumers
1

Hi all,

Does anyone out there have a standard set of rules or queries that group refr_medium, mkt_medium, refr_source and mkt_source etc. into standard marketing acquisition channels?

I assume things like “if there is a ‘gclid’ parameter, class this as PPC traffic” and similar, I was just curious if there was a set of standard models.

Thanks in advance!

Jordan

2

The general logic we have used:

  1. Look to UTM parameters first. [This requires strict guidelines over our UTM parameter tagging in PPC, paid social, and email campaigns]

  2. Look to refr_medium second (if necessary). Because we tag PPC campaigns with UTM parameters, if we get to second step and refr_medium = ‘search’ then we can classify user as coming from organic search. Ditto for social traffic. Internal is classified as direct.

3a) When the value of refr_medium=‘unknown’, we look to the refr_source field. We take the value of refr_source and classify the user’s source several ways depending on the values we often see (such as .gov sites). GA classifies much of this traffic as “referral” by default and we go even more granular.

3b) When the refr_source field is NULL, check mobile attribution context fields. If mobile attribution data is available from our third party attribution provider, then we can can attribute the user to various mobile channels / campaigns.

  1. If there still isn’t clarity by this step, classify user’s source as direct
5 Likes
3

Travis’ reply was really helpful on this, but I’d be interested to hear other peoples opinions on how to do this.

And… if there are any generic SQL models out there to give us a kick-start that’d be hugely appreciated!

Thanks!

4

Continuing my efforts to accurately categorise marketing traffic from our Snowplow data, I have a follow up question.

Screen Shot 2017-05-02 at 14.28.03.png1724×564 140 KB

This is a screen shows referrer information for traffic where the referring page is ‘googleadservices’.

Is it fair to attribute this to all be display ads from Google networks?

5

I would follow Travis’ approach (it’s also what we usually recommend) :slight_smile:

We don’t have any generic models ready, but if anyone has something they can share, feel free to do so here!

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
7

This is fantastic! Thank you!

1 Like
8

Awesome @digdeep! Thanks for sharing.

1 Like