Report not found

There was a problem finding this report.
Scenarios - Percent of users who return after absence
March 16, 2016 · Refreshed about 3 years ago
Description
The percent of users who return after periods of inactivity for both scenarios.

Collaborators

Run History
WITH counter AS ( SELECT ROW_NUMBER() OVER () AS counter FROM modeanalytics.retention_scenarios ), 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, FLOOR((EXTRACT('EPOCH' FROM '2016-04-01'::TIMESTAMP) - 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 <= 15 GROUP BY 1 ) a ORDER BY 1 LIMIT 100
WITH counter AS ( SELECT ROW_NUMBER() OVER () AS counter FROM modeanalytics.retention_scenarios ), 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, FLOOR((EXTRACT('EPOCH' FROM '2016-04-01'::TIMESTAMP) - 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 <= 15 GROUP BY 1 ) a ORDER BY 1
WITH counter AS ( SELECT ROW_NUMBER() OVER () AS counter FROM modeanalytics.retention_scenarios ), 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, FLOOR((EXTRACT('EPOCH' FROM '2016-04-01'::TIMESTAMP) - 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 <= 15 GROUP BY 1 ) a ORDER BY 1
WITH counter AS ( SELECT ROW_NUMBER() OVER () AS counter FROM modeanalytics.retention_scenarios ), 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, FLOOR((EXTRACT('EPOCH' FROM '2016-04-01'::TIMESTAMP) - 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 <= 15 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-6"> <mode-chart id="chart_be1643bffdf5" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-6"> <mode-chart id="chart_12fabbc32be3" 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