Report not found

There was a problem finding this report.
Scenarios - Retention including previous period
March 16, 2016 · Refreshed about 3 years ago
Description
Retention rate by if users appeared in the previous period for both scenarios.

Collaborators

Run History
WITH events AS ( SELECT user_id, occurred_at, MIN(occurred_at) OVER (PARTITION BY user_id) AS first_event FROM modeanalytics.retention_scenarios WHERE event_type = 'engagement' AND scenario = 1 ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out 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_scenarios WHERE event_type = 'engagement' AND scenario = 1 ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out 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_scenarios WHERE event_type = 'engagement' AND scenario = 2 ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out 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_scenarios WHERE event_type = 'engagement' AND scenario = 2 ), event_age AS ( SELECT DISTINCT user_id, FLOOR((EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM first_event))/(60*60*24)) AS periods_out 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
<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_145b8c6ade11" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-6"> <mode-chart id="chart_6a492125cde6" 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