REPORT PARAMETERS

There is a more recent run of this report. Click here to view it.

Report not found

There was a problem finding this report.
Describing Retention
August 31, 2016 · Refreshed over 2 years ago
Description
Retention and churn rates by user cohort. For details, see: https://help.modeanalytics.com/articles/cohort-analysis-for-customer-retention-and-churn-rate/

Collaborators

Run History
WITH users AS ( SELECT user_id, MIN(occurred_at) AS activated_at FROM modeanalytics.retention_events WHERE occurred_at <= NOW() GROUP BY 1 ), events AS ( SELECT user_id, event_name, occurred_at FROM modeanalytics.retention_events WHERE occurred_at <= NOW() ) SELECT * FROM ( SELECT *, MAX(CASE WHEN user_period = 0 THEN retained_users ELSE NULL END) OVER (PARTITION BY "Signup Date") AS "New Users", retained_users/ MAX(CASE WHEN user_period = 0 THEN retained_users ELSE NULL END) OVER (PARTITION BY "Signup Date")::FLOAT AS retention_rate, (LAG(retained_users) OVER (PARTITION BY "Signup Date" ORDER BY "Signup Date") - retained_users)/ MAX(CASE WHEN user_period = 0 THEN retained_users ELSE NULL END) OVER (PARTITION BY "Signup Date")::FLOAT AS churn_rate, (LAG(retained_users) OVER (PARTITION BY "Signup Date" ORDER BY "Signup Date") - retained_users)/ LAG(retained_users) OVER (PARTITION BY "Signup Date" ORDER BY "Signup Date")::FLOAT AS churn_rate_from_previous_period FROM ( SELECT DATE_TRUNC('month',u.activated_at) AS "Signup Date", (EXTRACT('year' FROM e.occurred_at) - EXTRACT('year' FROM u.activated_at)) * 12 + (EXTRACT('month' FROM e.occurred_at) - EXTRACT('month' FROM u.activated_at)) - CASE WHEN (CEILING(DATE_PART('day',e.occurred_at) - DATE_PART('day',u.activated_at))) < 0 THEN 1 ELSE 0 END AS user_period, COUNT(DISTINCT e.user_id) AS retained_users FROM users u JOIN events e ON e.user_id = u.user_id AND e.occurred_at >= u.activated_at WHERE u.activated_at >= DATE_TRUNC('month',NOW()) - INTERVAL '11 month' GROUP BY 1,2 ) x ) z WHERE user_period != 0 ORDER BY 1,2
WITH users AS ( SELECT user_id, MIN(occurred_at) AS activated_at FROM modeanalytics.retention_events WHERE occurred_at <= NOW() GROUP BY 1 ), events AS ( SELECT user_id, event_name, occurred_at FROM modeanalytics.retention_events WHERE occurred_at <= NOW() ) SELECT * FROM ( SELECT *, MAX(CASE WHEN user_period = 0 THEN retained_users ELSE NULL END) OVER (PARTITION BY "Signup Date") AS "New Users", retained_users/ MAX(CASE WHEN user_period = 0 THEN retained_users ELSE NULL END) OVER (PARTITION BY "Signup Date")::FLOAT AS retention_rate, (LAG(retained_users) OVER (PARTITION BY "Signup Date" ORDER BY "Signup Date") - retained_users)/ MAX(CASE WHEN user_period = 0 THEN retained_users ELSE NULL END) OVER (PARTITION BY "Signup Date")::FLOAT AS churn_rate, (LAG(retained_users) OVER (PARTITION BY "Signup Date" ORDER BY "Signup Date") - retained_users)/ LAG(retained_users) OVER (PARTITION BY "Signup Date" ORDER BY "Signup Date")::FLOAT AS churn_rate_from_previous_period FROM ( SELECT DATE_TRUNC('{{time_interval}}',u.activated_at) AS "Signup Date", {% if time_interval == 'month' %} (EXTRACT('year' FROM e.occurred_at) - EXTRACT('year' FROM u.activated_at)) * 12 + (EXTRACT('month' FROM e.occurred_at) - EXTRACT('month' FROM u.activated_at)) - CASE WHEN (CEILING(DATE_PART('day',e.occurred_at) - DATE_PART('day',u.activated_at))) < 0 THEN 1 ELSE 0 END AS user_period, {% elsif time_interval == 'week' %} TRUNC(DATE_PART('day',e.occurred_at - u.activated_at)/7) AS user_period, {% endif %} COUNT(DISTINCT e.user_id) AS retained_users FROM users u JOIN events e ON e.user_id = u.user_id AND e.occurred_at >= u.activated_at WHERE u.activated_at >= DATE_TRUNC('{{time_interval}}',NOW()) - INTERVAL '11 {{time_interval}}' GROUP BY 1,2 ) x ) z WHERE user_period != 0 ORDER BY 1,2 {% form %} time_interval: type: select default: month options: [[week, week], [month, month] ] {% endform %}
<link rel="stylesheet" href="https://mode.github.io/alamode/alamode.min.css"> <script src="https://mode.github.io/alamode/alamode.min.js"></script> <div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div class="mode-grid container"></div> <script> alamode.retentionHeatmap( { query_name: "Query 1", cohort_column: "Signup Date", pivot_column: "user_period", value_column: "retention_rate", total_column: "New Users", title: "Cutomer retention by signup date", pivot_label: "Periods after signup", value_is_percent: true } ) alamode.retentionHeatmap( { query_name: "Query 1", cohort_column: "Signup Date", pivot_column: "user_period", value_column: "churn_rate", total_column: "New Users", title: "Churn rate by signup date", pivot_label: "Periods after signup", value_is_percent: true } ) alamode.retentionHeatmap( { query_name: "Query 1", cohort_column: "Signup Date", pivot_column: "user_period", value_column: "churn_rate_from_previous_period", total_column: "New Users", title: "Churn rate from previous period", pivot_label: "Periods after signup", value_is_percent: true } )</script>
{{ 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