The most recent run of this report has failed. Click here to view it.

Report not found

There was a problem finding this report.

Report

ARPU by UTM Source

Query 2

Query 3

ARPU per UTM Source
June 14, 2016 · Refreshed almost 3 years ago
Description
average revenue per user per UTM source

Collaborators

Run History
select sum(order_total)/count(distinct customer_id) from heap.transaction LIMIT 100
select sum(order_total)/count(distinct customer_id) from heap.transaction
With first_session as( SELECT all_sessions.user_id ,identity ,first_session.first_time AS first_seen ,referrer ,utm_source 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 INNER JOIN heap.users AS users ON all_sessions.user_id = users.user_id --[optional] insert where clause here ORDER BY 3 DESC ) SELECT CASE WHEN utm_source is null THEN 'None' ELSE utm_source END ,'$'|| ROUND(SUM(order_total::decimal),2) as total_revenue , COUNT(DISTINCT identity) as user_total , '$'||ROUND(SUM(order_total::decimal)/COUNT(DISTINCT identity), 2) as avg_revenue_per_user FROM first_session JOIN heap.transaction ON first_session.identity=heap.transaction.customer_id GROUP BY 1
With first_session as( SELECT all_sessions.user_id ,identity ,first_session.first_time AS first_seen ,referrer ,utm_source 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 INNER JOIN heap.users AS users ON all_sessions.user_id = users.user_id --[optional] insert where clause here ORDER BY 3 DESC ) SELECT CASE WHEN utm_source is null THEN 'None' ELSE utm_source END ,'$'|| ROUND(SUM(order_total::decimal),2) as total_revenue , COUNT(DISTINCT identity) as user_total , '$'||ROUND(SUM(order_total::decimal)/COUNT(DISTINCT identity), 2) as avg_revenue_per_user FROM first_session JOIN heap.transaction ON first_session.identity=heap.transaction.customer_id GROUP BY 1
With first_session as( SELECT all_sessions.user_id ,identity ,first_session.first_time AS first_seen ,referrer ,utm_source 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 INNER JOIN heap.users AS users ON all_sessions.user_id = users.user_id --[optional] insert where clause here ORDER BY 3 DESC ) SELECT CASE WHEN utm_source is null THEN 'None' ELSE utm_source END ,'$'|| ROUND(SUM(total_revenue::decimal),2) as total_revenue , COUNT(DISTINCT identity) as user_total , '$'||ROUND(SUM(total_revenue::decimal)/COUNT(DISTINCT identity), 2) as avg_revenue_per_user FROM first_session JOIN heap.revenue_data ON first_session.identity=heap.revenue_data.user_id GROUP BY 1
With first_session as( SELECT all_sessions.user_id ,identity ,first_session.first_time AS first_seen ,referrer ,utm_source 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 INNER JOIN heap.users AS users ON all_sessions.user_id = users.user_id --[optional] insert where clause here ORDER BY 3 DESC ) SELECT CASE WHEN utm_source is null THEN 'None' ELSE utm_source END ,'$'|| ROUND(SUM(total_revenue::decimal),2) as total_revenue , COUNT(DISTINCT identity) as user_total , '$'||ROUND(SUM(total_revenue::decimal)/COUNT(DISTINCT identity), 2) as avg_revenue_per_user FROM first_session JOIN heap.revenue_data ON first_session.identity=heap.revenue_data.user_id GROUP BY 1
<div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> <h5>To use with Heap data, modify your query from heap.table_name to main_production.table_name. For help joining revenue and Heap data contact <a href="mailto:support@heapanlytics.com">Heap's support team</a>. </h5> </div> <div class="mode-grid container"> <div class="row"> <div class="col-md-12"> <mode-table id="table_fa3eda599dd1" dataset="dataset" options="table_options"></mode-table> </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