Report not found

There was a problem finding this report.
Retention by Joindate
June 30, 2016 · Refreshed almost 3 years ago
Description
This report shows the percentage of users retained cohorted by the date they joined.

Collaborators

Run History
--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
<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-12"> <mode-chart id="chart_b1e885fe9dba" 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