Report not found

There was a problem finding this report.
Rolling Conversion Rate
June 16, 2016 · Refreshed 11 months ago
Description
This report gives the user a 7 day window to complete steps two and three upon completing step one. A user is counted in the week in which Step 1 occurs. For example, If user performs step 1 April 29, and steps 2 and 3 May 3rd (which is in the 7 day window), they will be included in the total conversion rate for April 29.

Collaborators

Run History
With e1 as ( select distinct user_id, 1 as step_1, min(time) as step_1_time, to_char(e1.time, 'YYYY-WW') as "week" from heap.dashboard_galleries_view_gallery as e1 WHERE time BETWEEN '2016-04-01' AND '2016-06-15' group by 1, 4 ), e2 as ( select e1.user_id, 1 as step_2, min(e2.time) as step_2_time, e1.step_1_time from heap.dashboard_galleries_click_add_new_files as e2 join e1 on e1.user_id=e2.user_id and e1.step_1_time >= (e2.time - interval '7 days') where time BETWEEN step_1_time and '2016-06-15' group by 1, 4 ), e3 as ( select e3.user_id, 1 as step_3, min(time) as step_3_time, e2.step_1_time from heap.dashboard_sidebar_click_publish_site as e3 join e2 on e2.user_id=e3.user_id and e2.step_1_time >= (e3.time - interval '7 days') where time BETWEEN step_2_time and '2016-06-15' group by 1, 4 ) select 'Week of '|| to_timestamp(31556926*(substring(week, 0, 5)::int-1970)+(substring(week, 6,8)::int -1)*604800)::date as week, round(sum(step_3)/sum(step_1)::decimal, 4) as "Conversion Rate" from( select e1.user_id, step_1, e1.step_1_time, step_3, step_3_time, e1.week From e1 left join e2 on e1.user_id = e2.user_id and e1.step_1_time=e2.step_1_time left join e3 on e2.user_id= e3.user_id and e2.step_1_time=e3.step_1_time ) as event_count group by 1 order by 1
With e1 as ( select distinct user_id, 1 as step_1, min(time) as step_1_time, to_char(e1.time, 'YYYY-WW') as "week" from heap.dashboard_galleries_view_gallery as e1 WHERE time BETWEEN '2016-04-01' AND '2016-06-15' group by 1, 4 ), e2 as ( select e1.user_id, 1 as step_2, min(e2.time) as step_2_time, e1.step_1_time from heap.dashboard_galleries_click_add_new_files as e2 join e1 on e1.user_id=e2.user_id and e1.step_1_time >= (e2.time - interval '7 days') where time BETWEEN step_1_time and '2016-06-15' group by 1, 4 ), e3 as ( select e3.user_id, 1 as step_3, min(time) as step_3_time, e2.step_1_time from heap.dashboard_sidebar_click_publish_site as e3 join e2 on e2.user_id=e3.user_id and e2.step_1_time >= (e3.time - interval '7 days') where time BETWEEN step_2_time and '2016-06-15' group by 1, 4 ) select 'Week of '|| to_timestamp(31556926*(substring(week, 0, 5)::int-1970)+(substring(week, 6,8)::int -1)*604800)::date as week, round(sum(step_3)/sum(step_1)::decimal, 4) as "Conversion Rate" from( select e1.user_id, step_1, e1.step_1_time, step_3, step_3_time, e1.week From e1 left join e2 on e1.user_id = e2.user_id and e1.step_1_time=e2.step_1_time left join e3 on e2.user_id= e3.user_id and e2.step_1_time=e3.step_1_time ) as event_count group by 1 order by 1
<div class="mode-header"> <h1 class='embed-hidden'>{{ title }}</h1> <p>{{ description }}</p> <h5>In order to use this with Heap data change heap.table_name to main_production.table_name</h5> </div> <div class="mode-grid container"> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_e281210aa5f8" 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