Multitouch Attribution
June 14, 2016 · Refreshed 9 months ago
Description
This table compares first touch and last touch properties for a given user. We used Mode's Python Notebooks to generate a pivot table to process the information more easily
Collaborators
Taylor Udell
Run History
SELECT CASE WHEN initial.referrer IS NULL or initial.referrer ~'foliohd'
THEN 'Direct'
WHEN initial.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(initial.referrer, 0, 25)
END AS "first_touch"
,CASE
WHEN event_2.referrer IS NULL or event_2.referrer ~'foliohd'
THEN 'Direct'
WHEN event_2.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(event_2.referrer, 0, 25)
END AS "last_touch"
,count(DISTINCT initial.user_id) AS "Number of Users"
FROM (
SELECT all_sessions.user_id
, all_sessions.referrer
FROM heap.sessions AS all_sessions
INNER JOIN (
SELECT user_id
,MIN(time) AS first_time
FROM heap.sessions
GROUP BY user_id
) AS first_session ON all_sessions.user_id = first_session.user_id
AND all_sessions.time = first_session.first_time
) AS initial
JOIN heap.account_sign_up AS event_2
ON initial.user_id = event_2.user_id
GROUP BY 1
,2
ORDER BY 3 DESC
SELECT CASE WHEN initial.referrer IS NULL or initial.referrer ~'foliohd'
THEN 'Direct'
WHEN initial.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(initial.referrer, 0, 25)
END AS "first_touch"
,CASE
WHEN event_2.referrer IS NULL or event_2.referrer ~'foliohd'
THEN 'Direct'
WHEN event_2.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(event_2.referrer, 0, 25)
END AS "last_touch"
,count(DISTINCT initial.user_id) AS "Number of Users"
FROM (
SELECT all_sessions.user_id
, all_sessions.referrer
FROM heap.sessions AS all_sessions
INNER JOIN (
SELECT user_id
,MIN(time) AS first_time
FROM heap.sessions
GROUP BY user_id
) AS first_session ON all_sessions.user_id = first_session.user_id
AND all_sessions.time = first_session.first_time
) AS initial
JOIN heap.account_sign_up AS event_2
ON initial.user_id = event_2.user_id
GROUP BY 1
,2
ORDER BY 3 DESC
<div class="mode-header">
<h1 class='embed-hidden'>{{ title }}</h1>
<p>{{ description }}</p>
<h5>To use with Heap data, modify your query from heap.table_name to main_production.table_name</h5>
</div>
<div class="mode-grid container">
<div class="row">
<div class="col-md-12">
<mode-python id="python_89e98c65f85b" options="python_options"></mode-python>
</div>
</div>
</div>
The dataset is too large to view in browser
Export
Looks like something went wrong with your query.
{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled