Report not found

There was a problem finding this report.
Compare Retention across cohorts
June 30, 2016 · Refreshed over 1 year ago
Description
This report allows you to compare the different retention rates across different cohorts.

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", 'Month ' || --change month to change granularity extract("month" from age(future_activity.time, new_user_activity.date)) 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
--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", 'Month ' || --change month to change granularity extract("month" from age(future_activity.time, new_user_activity.date)) 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_13a8fe1f5550" dataset="dataset" options="chart_options"></mode-chart> </div> </div> </div> <style> .nv-y .tick{ display:none; } .key.total{ display:none; } .key.total{ display:none; } .key.total+.value{ display:none; }</style>
{{ 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