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

Report not found

There was a problem finding this report.

Report

1. Overall retention rate

2. Retention from previous period

3. Average time since last activity

4. Likelihood of return

Retention drill-down dashboard
March 16, 2016 · Refreshed about 3 years ago
Description
Metrics to help you better understand your retention rate.

Collaborators

Run History
WITH counter AS ( SELECT ROW_NUMBER() OVER () AS counter FROM modeanalytics.retention_events ), events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.user_age, e.periods_out, LEAD(e.periods_out,1) OVER (PARTITION BY user_id ORDER BY e.periods_out) AS next_event, COALESCE( LEAD(e.periods_out,1) OVER (PARTITION BY user_id ORDER BY e.periods_out) - e.periods_out, e.user_age - e.periods_out ) AS periods_until_next FROM event_age e ) SELECT *, returning_month/users::FLOAT AS "% returning in month", returning_week/users::FLOAT AS "% returning in week", returning_day/users::FLOAT AS "% returning in day" FROM ( SELECT c.counter, COUNT(e.user_id) AS users, COUNT(CASE WHEN e.next_event IS NOT NULL AND e.periods_until_next - c.counter <= 28 THEN e.user_id ELSE NULL END) AS returning_month, COUNT(CASE WHEN e.next_event IS NOT NULL AND e.periods_until_next - c.counter <= 7 THEN e.user_id ELSE NULL END) AS returning_week, COUNT(CASE WHEN e.next_event IS NOT NULL AND e.periods_until_next - c.counter <= 1 THEN e.user_id ELSE NULL END) AS returning_day FROM counter c LEFT JOIN adjacent_events e ON periods_until_next >= c.counter WHERE c.counter <= 100 GROUP BY 1 ) a ORDER BY 1
WITH counter AS ( SELECT ROW_NUMBER() OVER () AS counter FROM modeanalytics.retention_events ), events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.user_age, e.periods_out, LEAD(e.periods_out,1) OVER (PARTITION BY user_id ORDER BY e.periods_out) AS next_event, COALESCE( LEAD(e.periods_out,1) OVER (PARTITION BY user_id ORDER BY e.periods_out) - e.periods_out, e.user_age - e.periods_out ) AS periods_until_next FROM event_age e ) SELECT *, returning_month/users::FLOAT AS "% returning in month", returning_week/users::FLOAT AS "% returning in week", returning_day/users::FLOAT AS "% returning in day" FROM ( SELECT c.counter, COUNT(e.user_id) AS users, COUNT(CASE WHEN e.next_event IS NOT NULL AND e.periods_until_next - c.counter <= 28 THEN e.user_id ELSE NULL END) AS returning_month, COUNT(CASE WHEN e.next_event IS NOT NULL AND e.periods_until_next - c.counter <= 7 THEN e.user_id ELSE NULL END) AS returning_week, COUNT(CASE WHEN e.next_event IS NOT NULL AND e.periods_until_next - c.counter <= 1 THEN e.user_id ELSE NULL END) AS returning_day FROM counter c LEFT JOIN adjacent_events e ON periods_until_next >= c.counter WHERE c.counter <= 100 GROUP BY 1 ) a ORDER BY 1
WITH events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.user_age, e.periods_out, e.periods_out - LAG(e.periods_out,1) OVER (PARTITION BY user_id ORDER BY e.periods_out) AS periods_since_last FROM event_age e ) SELECT e.periods_out, AVG(e.periods_since_last) AS avg_time FROM adjacent_events e WHERE periods_out >= 1 AND periods_out <= 100 GROUP BY 1 ORDER BY 1
WITH events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.user_age, e.periods_out, e.periods_out - LAG(e.periods_out,1) OVER (PARTITION BY user_id ORDER BY e.periods_out) AS periods_since_last FROM event_age e ) SELECT e.periods_out, AVG(e.periods_since_last) AS avg_time FROM adjacent_events e WHERE periods_out >= 1 AND periods_out <= 100 GROUP BY 1 ORDER BY 1
WITH events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ), last_event AS ( SELECT *, CASE WHEN periods_out - LAG(periods_out,1) OVER (PARTITION BY user_id ORDER BY periods_out) <= 1 THEN TRUE ELSE FALSE END AS retained_last_period FROM event_age ) SELECT * FROM ( SELECT periods_out, users, users/MAX(users) OVER ()::FLOAT AS rate, (users - returning_users)/MAX(users) OVER ()::FLOAT AS "Not in last period", (returning_users)/MAX(users) OVER ()::FLOAT AS "In last period" FROM ( SELECT periods_out, COUNT(user_id) AS users, COUNT(CASE WHEN retained_last_period = TRUE THEN user_id ELSE NULL END) AS returning_users FROM last_event GROUP BY 1 ) a ) b WHERE periods_out != 0 AND periods_out <= 100 ORDER BY 1,2
WITH events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ), last_event AS ( SELECT *, CASE WHEN periods_out - LAG(periods_out,1) OVER (PARTITION BY user_id ORDER BY periods_out) <= 1 THEN TRUE ELSE FALSE END AS retained_last_period FROM event_age ) SELECT * FROM ( SELECT periods_out, users, users/MAX(users) OVER ()::FLOAT AS rate, (users - returning_users)/MAX(users) OVER ()::FLOAT AS "Not in last period", (returning_users)/MAX(users) OVER ()::FLOAT AS "In last period" FROM ( SELECT periods_out, COUNT(user_id) AS users, COUNT(CASE WHEN retained_last_period = TRUE THEN user_id ELSE NULL END) AS returning_users FROM last_event GROUP BY 1 ) a ) b WHERE periods_out != 0 AND periods_out <= 100 ORDER BY 1,2
WITH events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ) SELECT * FROM ( SELECT periods_out, users, users/MAX(users) OVER ()::FLOAT AS rate FROM ( SELECT periods_out, COUNT(user_id) AS users FROM event_age GROUP BY 1 ) a ) b WHERE periods_out != 0 AND periods_out <= 100 ORDER BY 1
WITH events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_events WHERE event_type = 'engagement' AND occurred_at <= NOW() ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS user_age FROM events ) SELECT * FROM ( SELECT periods_out, users, users/MAX(users) OVER ()::FLOAT AS rate FROM ( SELECT periods_out, COUNT(user_id) AS users FROM event_age GROUP BY 1 ) a ) b WHERE periods_out != 0 AND periods_out <= 100 ORDER BY 1
<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_233bbfe7cb71" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-6"> <mode-chart id="chart_9c31ef180e2e" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-6"> <mode-chart id="chart_8b252ad87e75" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-6"> <mode-chart id="chart_6033238e0483" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_697ec914ab1a" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_308a43f32da1" 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