Report not found

There was a problem finding this report.

Report

Funnel with Conversion Rates

Three Step Funnel

Onboarding Funnel limited to one session
June 15, 2016 · Refreshed about 1 year ago
Description
This three step funnel requires a user to complete all onboarding steps within the same session.

Collaborators

Run History
with e1 AS ( SELECT DISTINCT user_id, session_id , 1 AS step_1 , MIN(time) AS step_1_time -- change to table name of event 1 in your funnel FROM heap.dashboard_galleries_view_gallery AS e1 WHERE time BETWEEN '2016-04-13' AND '2016-04-21' GROUP BY 1, 2 ), e2 AS ( SELECT e1.user_id, e1.session_id , 1 AS step_2 , MIN(time) AS step_2_time -- change to table name of event 2 in your funnel FROM heap.dashboard_galleries_click_add_new_files AS e2 INNER JOIN e1 ON e1.user_id = e2.user_id AND e1.session_id = e2.session_id WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1, 2 ), e3 AS ( SELECT e3.user_id, e3.session_id , 1 AS step_3 , MIN(time) AS step_3_time -- change to table name of event 3 in your funnel FROM heap.dashboard_sidebar_click_publish_site AS e3 INNER JOIN e2 ON e2.user_id = e3.user_id AND e2.session_id = e3.session_id WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1, 2 ) SELECT count(DISTINCT user_id) AS step_one, count(DISTINCT step_2) AS step_two, count(DISTINCT step_3) AS step_three, ROUND(COUNT(DISTINCT step_3)::decimal/COUNT(DISTINCT user_id), 4) AS conversion_rate from ( SELECT e1.user_id, e1.session_id, step_1, step_1_time, e2.user_id AS step_2, step_2_time, e3.user_id AS step_3, step_3_time FROM e1 LEFT JOIN e2 ON e1.user_id = e2.user_id AND e1.session_id=e2.session_id LEFT JOIN e3 ON e2.user_id= e3.user_id AND e2.session_id=e3.session_id ORDER BY 1) as user_count
with e1 AS ( SELECT DISTINCT user_id, session_id , 1 AS step_1 , MIN(time) AS step_1_time -- change to table name of event 1 in your funnel FROM heap.dashboard_galleries_view_gallery AS e1 WHERE time BETWEEN '2016-04-13' AND '2016-04-21' GROUP BY 1, 2 ), e2 AS ( SELECT e1.user_id, e1.session_id , 1 AS step_2 , MIN(time) AS step_2_time -- change to table name of event 2 in your funnel FROM heap.dashboard_galleries_click_add_new_files AS e2 INNER JOIN e1 ON e1.user_id = e2.user_id AND e1.session_id = e2.session_id WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1, 2 ), e3 AS ( SELECT e3.user_id, e3.session_id , 1 AS step_3 , MIN(time) AS step_3_time -- change to table name of event 3 in your funnel FROM heap.dashboard_sidebar_click_publish_site AS e3 INNER JOIN e2 ON e2.user_id = e3.user_id AND e2.session_id = e3.session_id WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1, 2 ) SELECT count(DISTINCT user_id) AS step_one, count(DISTINCT step_2) AS step_two, count(DISTINCT step_3) AS step_three, ROUND(COUNT(DISTINCT step_3)::decimal/COUNT(DISTINCT user_id), 4) AS conversion_rate from ( SELECT e1.user_id, e1.session_id, step_1, step_1_time, e2.user_id AS step_2, step_2_time, e3.user_id AS step_3, step_3_time FROM e1 LEFT JOIN e2 ON e1.user_id = e2.user_id AND e1.session_id=e2.session_id LEFT JOIN e3 ON e2.user_id= e3.user_id AND e2.session_id=e3.session_id ORDER BY 1) as user_count
WITH e1 AS ( SELECT DISTINCT user_id, session_id ,1 AS step_1 ,MIN(time) AS step_1_time -- change to table name of event 1 in your funnel FROM heap.dashboard_galleries_view_gallery AS e1 WHERE time BETWEEN '2016-04-13' AND '2016-04-21' GROUP BY 1, 2 ), e2 AS ( SELECT e1.user_id, e1.session_id ,1 AS step_2 ,MIN(time) AS step_2_time -- change to table name of event 2 in your funnel FROM heap.dashboard_galleries_click_add_new_files AS e2 INNER JOIN e1 ON e1.user_id = e2.user_id and e1.session_id = e2.session_id WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1, 2 ), e3 AS ( SELECT e3.user_id, e3.session_id ,1 AS step_3 ,MIN(time) AS step_3_time -- change to table name of event 3 in your funnel FROM heap.dashboard_sidebar_click_publish_site AS e3 INNER JOIN e2 ON e2.user_id = e3.user_id AND e2.session_id = e3.session_id WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1, 2 ) SELECT step, SUM(users) OVER (ORDER BY step DESC) AS total FROM ( SELECT CASE WHEN step_3 is not null THEN 'Step 3' WHEN step_2 is not null THEN 'Step 2' WHEN step_1 is not null THEN 'Step 1' ELSE 'z end' END AS step, SUM(1) AS users FROM( SELECT distinct user_id as step_1, MAX(step_2) as step_2, MAX(step_3) as step_3 FROM ( select e1.user_id, e1.session_id, step_1, step_1_time, e2.user_id as step_2, step_2_time, e3.user_id as step_3, step_3_time From e1 left join e2 on e1.user_id = e2.user_id and e1.session_id=e2.session_id left join e3 on e2.user_id= e3.user_id and e2.session_id=e3.session_id order by 1 ) as user_count GROUP BY 1 ) as c GROUP BY 1 ) as funnel ORDER BY 1
WITH e1 AS ( SELECT DISTINCT user_id, session_id ,1 AS step_1 ,MIN(time) AS step_1_time -- change to table name of event 1 in your funnel FROM heap.dashboard_galleries_view_gallery AS e1 WHERE time BETWEEN '2016-04-13' AND '2016-04-21' GROUP BY 1, 2 ), e2 AS ( SELECT e1.user_id, e1.session_id ,1 AS step_2 ,MIN(time) AS step_2_time -- change to table name of event 2 in your funnel FROM heap.dashboard_galleries_click_add_new_files AS e2 INNER JOIN e1 ON e1.user_id = e2.user_id and e1.session_id = e2.session_id WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1, 2 ), e3 AS ( SELECT e3.user_id, e3.session_id ,1 AS step_3 ,MIN(time) AS step_3_time -- change to table name of event 3 in your funnel FROM heap.dashboard_sidebar_click_publish_site AS e3 INNER JOIN e2 ON e2.user_id = e3.user_id AND e2.session_id = e3.session_id WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1, 2 ) SELECT step, SUM(users) OVER (ORDER BY step DESC) AS total FROM ( SELECT CASE WHEN step_3 is not null THEN 'Step 3' WHEN step_2 is not null THEN 'Step 2' WHEN step_1 is not null THEN 'Step 1' ELSE 'z end' END AS step, SUM(1) AS users FROM( SELECT distinct user_id as step_1, MAX(step_2) as step_2, MAX(step_3) as step_3 FROM ( select e1.user_id, e1.session_id, step_1, step_1_time, e2.user_id as step_2, step_2_time, e3.user_id as step_3, step_3_time From e1 left join e2 on e1.user_id = e2.user_id and e1.session_id=e2.session_id left join e3 on e2.user_id= e3.user_id and e2.session_id=e3.session_id order by 1 ) as user_count GROUP BY 1 ) as c GROUP BY 1 ) as funnel ORDER BY 1
<style> .mode-bar-chart-funnel-background { fill: rgba(0, 0, 0, 0.7); border-radius: 2px; } .mode-bar-chart-funnel-label { fill: #fff; } </style> <div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> <h5>To use with Heap data, modify your query from 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_70329f388d05" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-table id="table_fa7bcfd740f1" dataset="dataset" options="table_options"></mode-table> </div> </div> </div> <script> barChartFunnel({ "chart_id": "#chart_70329f388d05", "include_overall_rate": true }) function barChartFunnel(o) { var p = d3.format(".3p"); var includeOverall = o["include_overall_rate"], chartId = o["chart_id"], jqChart = $(chartId); function drawFunnel() { d3.selectAll(chartId + " .r").remove(); var svgTrans = getTrans(jqChart.find("g.nvd3.nv-wrap g").attr("transform")); var bars = jqChart.find(".nv-barsWrap .nv-groups .nv-bar"); var funnelObj = []; bars.each(function(i) { var bar = $(this).find("rect"); var value = +$(this).find("text").text().replace(/,/g, ''); var barTrans = getTrans($(this).attr("transform")); barWidth = bar.attr("width"), barHeight = bar.attr("height"); bar.attr("width",barWidth/2); bar.attr("transform","translate(" + barWidth/4 + ",0)"); obj = { step: i, value: value, x: barTrans[0], y: barTrans[1], h: barHeight} funnelObj.push(obj) }) var svg = d3.select(chartId + " .nvd3svg"); funnelObj.forEach(function(d,i) { if (i != 0) { var h = 30, x = d.x + svgTrans[0] + barWidth/8, y = Math.min(svgTrans[1] + d.y + d.h/2 - h/2, svgTrans[1] + d.y + d.h/1 - h), w = barWidth/2; var value = p(d.value/funnelObj[i-1]["value"]); var pts = (x-w) + "," + y + " " + (x-.2*w) + "," + y + " " + x + "," + (y+h/2) + " " + (x-.2*w) + "," + (y+h) + " " + (x-w) + "," + (y+h) svg.append("polygon") .attr("points",pts) .attr("class","mode-bar-chart-funnel-background r"); svg.append("text") .attr("x",(x - w*.6)) .attr("y",y + h/2) .attr("dy",".37em") .attr("text-anchor","middle") .attr("class","mode-bar-chart-funnel-label r") .style("font-size","12px") .text(value) } }) if (includeOverall) { var legendX = funnelObj[funnelObj.length-1]["x"]+svgTrans[0], overallVal = funnelObj[funnelObj.length-1]["value"]/funnelObj[0]["value"]; svg.append("rect") .attr("x",funnelObj[funnelObj.length-1]["x"]+svgTrans[0]) .attr("y",0) .attr("width",100) .attr("height",50) .attr("class","mode-bar-chart-funnel-background r") svg.append("text") .attr("x",funnelObj[funnelObj.length-1]["x"]+svgTrans[0] + 10) .attr("y",15) .attr("class","mode-bar-chart-funnel-label r") .style("font-size","12px") .text("Overall Rate"); svg.append("text") .attr("x",funnelObj[funnelObj.length-1]["x"]+svgTrans[0] + 10) .attr("y",40) .attr("class","mode-bar-chart-funnel-label r") .style("font-size","18px") .text(p(overallVal)); } } function getTrans(translation) { var openPos = translation.indexOf("("), closePos = translation.indexOf(")"), commaPos = translation.indexOf(","); var xTrans = +translation.slice(openPos+1,commaPos), yTrans = +translation.slice(commaPos+1,closePos); return [xTrans,yTrans]; } setTimeout(function(){ drawFunnel() }, 750); $(window).resize(function () { waitForFinalEvent(function(){ drawFunnel() }, 500, ""); }); var waitForFinalEvent = (function () { var timers = {}; return function (callback, ms, uniqueId) { if (!uniqueId) { uniqueId = "Don't call this twice without a uniqueId"; } if (timers[uniqueId]) { clearTimeout (timers[uniqueId]); } timers[uniqueId] = setTimeout(callback, ms); }; })(); } </script> <style> .js-table-content-container{ height: 50px!important; } </style>
{{ 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