Report not found

There was a problem finding this report.

Report

Multitouch Attribution UTM Campaign

Multitouch Attribution
June 14, 2016 · Refreshed 12 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

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>
{{ dataSourceName(params.queryId) }}

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