Report not found

There was a problem finding this report.
Monthy ARPU per UTM
July 6, 2016 · Refreshed over 2 years ago
Description
This query calculated the monthly ARPU based on their first touch properties.

Collaborators

Run History
WITH first_touch AS (SELECT all_sessions.user_id, identity, first_session.first_time AS first_seen, utm_source --add any user level properties or session level properties here 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) SELECT utm_source, CASE WHEN Length(Substring(order_date, 0, Position('/' IN order_date))) = 2 THEN '20' || Right(order_date, 2) || '-' || Substring(order_date, 0, Position('/' IN order_date)) ELSE '20' || Right(order_date, 2) ||'-0' || Substring(order_date, 0, Position('/' IN order_date)) END AS order_month, Count(DISTINCT customer_id), Round(SUM(order_total) :: DECIMAL, 2) AS total_revenue, Round(SUM(order_total) :: DECIMAL / Count(DISTINCT customer_id) :: DECIMAL, 2) AS avg_per_customer FROM heap.TRANSACTION join first_touch ON customer_id = identity WHERE utm_source IS NOT NULL GROUP BY 1, 2 ORDER BY 2 LIMIT 100
WITH first_touch AS (SELECT all_sessions.user_id, identity, first_session.first_time AS first_seen, utm_source --add any user level properties or session level properties here 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) SELECT utm_source, CASE WHEN Length(Substring(order_date, 0, Position('/' IN order_date))) = 2 THEN '20' || Right(order_date, 2) || '-' || Substring(order_date, 0, Position('/' IN order_date)) ELSE '20' || Right(order_date, 2) ||'-0' || Substring(order_date, 0, Position('/' IN order_date)) END AS order_month, Count(DISTINCT customer_id), Round(SUM(order_total) :: DECIMAL, 2) AS total_revenue, Round(SUM(order_total) :: DECIMAL / Count(DISTINCT customer_id) :: DECIMAL, 2) AS avg_per_customer FROM heap.TRANSACTION join first_touch ON customer_id = identity WHERE utm_source IS NOT NULL GROUP BY 1, 2 ORDER BY 2
<div class="mode-header"> <h1 class="embed-hidden">{{ title }}</h1> <p>{{ description }}</p> </div> <div class="mode-grid container"> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_5a8a19a4ed7c" dataset="dataset" options="chart_options"></mode-chart> </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