Report not found

There was a problem finding this report.

Report

Cohorted retention

Cumulative retention

Likelihood of return

Overall retention

User type retention

Percent of users who return after absence
March 10, 2016 · Refreshed over 3 years ago

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))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ), first_back AS ( SELECT user_id, MIN(days_out) AS first_return FROM event_age WHERE days_out != 0 GROUP BY 1 ) SELECT c.counter, u.users, COUNT(DISTINCT f.user_id) AS users_back, COUNT(DISTINCT f.user_id)/u.users::FLOAT AS percent_back FROM counter c JOIN ( SELECT COUNT(DISTINCT user_id) AS users FROM event_age ) u ON u.users >= 10 LEFT JOIN first_back f ON f.first_return < c.counter WHERE c.counter <= 100 GROUP BY 1,2
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))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ), first_back AS ( SELECT user_id, MIN(days_out) AS first_return FROM event_age WHERE days_out != 0 GROUP BY 1 ) SELECT c.counter, u.users, COUNT(DISTINCT f.user_id) AS users_back, COUNT(DISTINCT f.user_id)/u.users::FLOAT AS percent_back FROM counter c JOIN ( SELECT COUNT(DISTINCT user_id) AS users FROM event_age ) u ON u.users >= 10 LEFT JOIN first_back f ON f.first_return < c.counter WHERE c.counter <= 100 GROUP 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))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ) SELECT * FROM ( SELECT days_out, users, users/MAX(users) OVER ()::FLOAT AS rate FROM ( SELECT days_out, COUNT(user_id) AS users FROM event_age GROUP BY 1 ) a ) b WHERE days_out != 0 AND days_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))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ) SELECT * FROM ( SELECT days_out, users, users/MAX(users) OVER ()::FLOAT AS rate FROM ( SELECT days_out, COUNT(user_id) AS users FROM event_age GROUP BY 1 ) a ) b WHERE days_out != 0 AND days_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, DATE_TRUNC('week',occurred_at) AS time_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.time_id, e.user_age, e.days_out, LAG(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) AS last_event, LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) AS next_event, COALESCE( LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) - e.days_out, e.user_age - e.days_out ) AS days_until_next FROM event_age e ) SELECT time_id, cohort, retained/(retained+churned)::FLOAT AS rate FROM ( SELECT time_id, CASE WHEN c.days_out <= 10 THEN 'new' WHEN c.days_out - c.last_event >= 14 THEN 'revived' ELSE 'existing' END AS cohort, COUNT(CASE WHEN c.next_event IS NOT NULL AND c.next_event - c.days_out <= 14 THEN user_id ELSE NULL END) AS retained, COUNT(CASE WHEN c.next_event IS NULL OR c.next_event - c.days_out > 14 THEN user_id ELSE NULL END) AS churned FROM adjacent_events c GROUP BY 1,2 ) a WHERE time_id >= NOW() - INTERVAL '6 MONTH' AND time_id <= NOW() - INTERVAL '1 MONTH' 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, DATE_TRUNC('week',occurred_at) AS time_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.time_id, e.user_age, e.days_out, LAG(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) AS last_event, LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) AS next_event, COALESCE( LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) - e.days_out, e.user_age - e.days_out ) AS days_until_next FROM event_age e ) SELECT time_id, cohort, retained/(retained+churned)::FLOAT AS rate FROM ( SELECT time_id, CASE WHEN c.days_out <= 10 THEN 'new' WHEN c.days_out - c.last_event >= 14 THEN 'revived' ELSE 'existing' END AS cohort, COUNT(CASE WHEN c.next_event IS NOT NULL AND c.next_event - c.days_out <= 14 THEN user_id ELSE NULL END) AS retained, COUNT(CASE WHEN c.next_event IS NULL OR c.next_event - c.days_out > 14 THEN user_id ELSE NULL END) AS churned FROM adjacent_events c GROUP BY 1,2 ) a WHERE time_id >= NOW() - INTERVAL '6 MONTH' AND time_id <= NOW() - INTERVAL '1 MONTH' 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, DATE_TRUNC('month',first_event) AS signup_month, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(3600*24*7)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24*7)) AS user_age FROM events WHERE first_event >= DATE_TRUNC('month',NOW() - INTERVAL '5 MONTH') -- AND ) SELECT * FROM ( SELECT days_out, users, TO_CHAR(signup_month, 'Mon YY') AS month, users/MAX(users) OVER (PARTITION BY signup_month)::FLOAT AS rate FROM ( SELECT signup_month, days_out, COUNT(user_id) AS users FROM event_age GROUP BY 1,2 ) a ) b WHERE days_out != 0 AND days_out <= 100 ORDER BY 1,3
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, DATE_TRUNC('month',first_event) AS signup_month, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(3600*24*7)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24*7)) AS user_age FROM events WHERE first_event >= DATE_TRUNC('month',NOW() - INTERVAL '5 MONTH') -- AND ) SELECT * FROM ( SELECT days_out, users, TO_CHAR(signup_month, 'Mon YY') AS month, users/MAX(users) OVER (PARTITION BY signup_month)::FLOAT AS rate FROM ( SELECT signup_month, days_out, COUNT(user_id) AS users FROM event_age GROUP BY 1,2 ) a ) b WHERE days_out != 0 AND days_out <= 100 ORDER BY 1,3
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))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.user_age, e.days_out, LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) AS next_event, COALESCE( LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) - e.days_out, e.user_age - e.days_out ) AS days_until_next FROM event_age e ) SELECT *, returning_users/users::FLOAT AS "% ever returning", returning_week/users::FLOAT AS "% returning in week", returning_day/users::FLOAT AS "% returning in day", avg_time AS "Average time to return" FROM ( SELECT c.counter, COUNT(DISTINCT e.user_id) AS users, COUNT(DISTINCT CASE WHEN e.next_event IS NOT NULL THEN e.user_id ELSE NULL END) AS returning_users, COUNT(DISTINCT CASE WHEN e.next_event IS NOT NULL AND e.days_until_next - c.counter <= 7 THEN e.user_id ELSE NULL END) AS returning_week, COUNT(DISTINCT CASE WHEN e.next_event IS NOT NULL AND e.days_until_next - c.counter <= 1 THEN e.user_id ELSE NULL END) AS returning_day, AVG(CASE WHEN e.next_event IS NOT NULL THEN e.days_until_next - c.counter ELSE NULL END) AS avg_time FROM counter c LEFT JOIN adjacent_events e ON days_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))/(3600*24)) AS days_out, FLOOR((EXTRACT('EPOCH' FROM NOW()) - EXTRACT('EPOCH' FROM first_event))/(3600*24)) AS user_age FROM events ), adjacent_events AS ( SELECT e.user_id, e.user_age, e.days_out, LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) AS next_event, COALESCE( LEAD(e.days_out,1) OVER (PARTITION BY user_id ORDER BY e.days_out) - e.days_out, e.user_age - e.days_out ) AS days_until_next FROM event_age e ) SELECT *, returning_users/users::FLOAT AS "% ever returning", returning_week/users::FLOAT AS "% returning in week", returning_day/users::FLOAT AS "% returning in day", avg_time AS "Average time to return" FROM ( SELECT c.counter, COUNT(DISTINCT e.user_id) AS users, COUNT(DISTINCT CASE WHEN e.next_event IS NOT NULL THEN e.user_id ELSE NULL END) AS returning_users, COUNT(DISTINCT CASE WHEN e.next_event IS NOT NULL AND e.days_until_next - c.counter <= 7 THEN e.user_id ELSE NULL END) AS returning_week, COUNT(DISTINCT CASE WHEN e.next_event IS NOT NULL AND e.days_until_next - c.counter <= 1 THEN e.user_id ELSE NULL END) AS returning_day, AVG(CASE WHEN e.next_event IS NOT NULL THEN e.days_until_next - c.counter ELSE NULL END) AS avg_time FROM counter c LEFT JOIN adjacent_events e ON days_until_next >= c.counter WHERE c.counter <= 100 GROUP BY 1 ) a 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-8"> <mode-chart id="chart_f9ed7585c09e" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-4"></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