Report not found

There was a problem finding this report.

Report

Average Events Per Session

Retention By Joindate

Rolling Conversion Rate

Heap Dashboard
July 11, 2016 · Refreshed 5 months ago
Description
An example of some of the analyses you can conduct in Mode using Heap SQL.

Collaborators

Run History
--in Redshift you can also use TO_CHAR(time, 'YYYY-MM') SELECT SUBSTRING(time::date||'', 0, 8) as month, ROUND(COUNT(DISTINCT event_id)::DECIMAL / COUNT(DISTINCT session_id), 2) AS "average" --FROM heap.all_events -- modify your date range here with WHERE time >= DATEADD('month', -6, getdate()) GROUP BY 1
--in Redshift you can also use TO_CHAR(time, 'YYYY-MM') SELECT SUBSTRING(time::date||'', 0, 8) as month, ROUND(COUNT(DISTINCT event_id)::DECIMAL / COUNT(DISTINCT session_id), 2) AS "average" --FROM heap.all_events -- modify your date range here with WHERE time >= DATEADD('month', -6, getdate()) GROUP BY 1
With e1 as ( select distinct user_id, 1 as step_1, min(time) as step_1_time, to_char(e1.time, 'YYYY-WW') as "week" from heap.dashboard_galleries_view_gallery as e1 WHERE time BETWEEN '2016-04-01' AND '2016-06-15' group by 1, 4 ), e2 as ( select e1.user_id, 1 as step_2, min(e2.time) as step_2_time, e1.step_1_time from heap.dashboard_galleries_click_add_new_files as e2 join e1 on e1.user_id=e2.user_id and e1.step_1_time >= (e2.time - interval '7 days') where time BETWEEN step_1_time and '2016-06-15' group by 1, 4 ), e3 as ( select e3.user_id, 1 as step_3, min(time) as step_3_time, e2.step_1_time from heap.dashboard_sidebar_click_publish_site as e3 join e2 on e2.user_id=e3.user_id and e2.step_1_time >= (e3.time - interval '7 days') where time BETWEEN step_2_time and '2016-06-15' group by 1, 4 ) select 'Week of '|| to_timestamp(31556926*(substring(week, 0, 5)::int-1970)+(substring(week, 6,8)::int -1)*604800)::date as week, round(sum(step_3)/sum(step_1)::decimal, 4) as "Conversion Rate" from( select e1.user_id, step_1, e1.step_1_time, step_3, step_3_time, e1.week From e1 left join e2 on e1.user_id = e2.user_id and e1.step_1_time=e2.step_1_time left join e3 on e2.user_id= e3.user_id and e2.step_1_time=e3.step_1_time ) as event_count group by 1 order by 1 LIMIT 100
With e1 as ( select distinct user_id, 1 as step_1, min(time) as step_1_time, to_char(e1.time, 'YYYY-WW') as "week" from heap.dashboard_galleries_view_gallery as e1 WHERE time BETWEEN '2016-04-01' AND '2016-06-15' group by 1, 4 ), e2 as ( select e1.user_id, 1 as step_2, min(e2.time) as step_2_time, e1.step_1_time from heap.dashboard_galleries_click_add_new_files as e2 join e1 on e1.user_id=e2.user_id and e1.step_1_time >= (e2.time - interval '7 days') where time BETWEEN step_1_time and '2016-06-15' group by 1, 4 ), e3 as ( select e3.user_id, 1 as step_3, min(time) as step_3_time, e2.step_1_time from heap.dashboard_sidebar_click_publish_site as e3 join e2 on e2.user_id=e3.user_id and e2.step_1_time >= (e3.time - interval '7 days') where time BETWEEN step_2_time and '2016-06-15' group by 1, 4 ) select 'Week of '|| to_timestamp(31556926*(substring(week, 0, 5)::int-1970)+(substring(week, 6,8)::int -1)*604800)::date as week, round(sum(step_3)/sum(step_1)::decimal, 4) as "Conversion Rate" from( select e1.user_id, step_1, e1.step_1_time, step_3, step_3_time, e1.week From e1 left join e2 on e1.user_id = e2.user_id and e1.step_1_time=e2.step_1_time left join e3 on e2.user_id= e3.user_id and e2.step_1_time=e3.step_1_time ) as event_count group by 1 order by 1 LIMIT 100
--get a cohort based on join date WITH new_user_activity AS ( SELECT activity.user_id, activity.time AS date FROM heap.sessions AS activity JOIN heap.users AS users ON users.user_id = activity.user_id AND users.joindate = activity.time WHERE Date_part('year', joindate) > 2014 ) --number of users in cohort , cohort_active_user_count AS ( SELECT To_char(date,'YYYY-MM') AS date, Count(DISTINCT user_id) AS count FROM new_user_activity GROUP BY 1 ) --change format to change granularity SELECT joindate, period, new_users, retained_users, retention FROM ( SELECT To_char(new_user_activity.date, 'YYYY-MM') AS "joindate", --change month to change granularity To_char(future_activity.time, 'YYYY-MM') AS period, Max(cohort_size.count) AS new_users, -- all equal in group Count(DISTINCT future_activity.user_id) AS retained_users, Round(Count(DISTINCT future_activity.user_id) / Max(cohort_size.count)::decimal, 2) AS retention FROM new_user_activity LEFT JOIN heap.sessions AS future_activity ON new_user_activity.user_id = future_activity.user_id AND new_user_activity.date < future_activity.time AND new_user_activity.date + interval '9 months' >= future_activity.time LEFT JOIN cohort_active_user_count AS cohort_size ON to_char(new_user_activity.date, 'YYYY-MM') = cohort_size.date GROUP BY 1, 2) t WHERE period IS NOT NULL ORDER BY 1, 2 LIMIT 100
--get a cohort based on join date WITH new_user_activity AS ( SELECT activity.user_id, activity.time AS date FROM heap.sessions AS activity JOIN heap.users AS users ON users.user_id = activity.user_id AND users.joindate = activity.time WHERE Date_part('year', joindate) > 2014 ) --number of users in cohort , cohort_active_user_count AS ( SELECT To_char(date,'YYYY-MM') AS date, Count(DISTINCT user_id) AS count FROM new_user_activity GROUP BY 1 ) --change format to change granularity SELECT joindate, period, new_users, retained_users, retention FROM ( SELECT To_char(new_user_activity.date, 'YYYY-MM') AS "joindate", --change month to change granularity To_char(future_activity.time, 'YYYY-MM') AS period, Max(cohort_size.count) AS new_users, -- all equal in group Count(DISTINCT future_activity.user_id) AS retained_users, Round(Count(DISTINCT future_activity.user_id) / Max(cohort_size.count)::decimal, 2) AS retention FROM new_user_activity LEFT JOIN heap.sessions AS future_activity ON new_user_activity.user_id = future_activity.user_id AND new_user_activity.date < future_activity.time AND new_user_activity.date + interval '9 months' >= future_activity.time LEFT JOIN cohort_active_user_count AS cohort_size ON to_char(new_user_activity.date, 'YYYY-MM') = cohort_size.date GROUP BY 1, 2) t WHERE period IS NOT NULL ORDER BY 1, 2 LIMIT 100
<div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div class="mode-grid container"> <div class="row"> <div class="col-md-6"> <mode-chart id="chart_eaf1e15e8525" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-6"> <mode-chart id="chart_1fa85ce3b2b4" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_5bd4e0b0ad4d" 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