Report not found

There was a problem finding this report.

Report

Funnel with Conversion Rates

Three Step Funnel

Onboarding Flow - Funnel Report
June 15, 2016 · Refreshed 2 months ago
Description
This three step funnel will align with Heap once you have adjusted the timezone to match the time zone set within Heap's UI.

Collaborators

Run History
WITH e1 AS ( SELECT DISTINCT user_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 ) ,e2 AS ( SELECT e1.user_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 WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1 ) ,e3 AS ( SELECT e3.user_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 WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1 ) SELECT SUM(step_1) AS "Step 1" --change to event name ,SUM(step_2) AS "Step 2" --change to event name ,SUM(step_3) AS "Step 3" --change to event name ,ROUND(SUM(step_2)/SUM(step_1)::DECIMAL, 4) as step_1to2_conversion ,ROUND(SUM(step_3)/SUM(step_2)::DECIMAL, 4) as step_2to3_conversion ,ROUND(SUM(step_3)/SUM(step_1)::DECIMAL, 4) as overall_conversion FROM ( SELECT e1.user_id ,step_1 ,step_1_time ,step_2 ,step_2_time ,step_3 ,step_3_time FROM e1 LEFT JOIN e2 ON e1.user_id = e2.user_id LEFT JOIN e3 ON e2.user_id = e3.user_id ) as funnel
WITH e1 AS ( SELECT DISTINCT user_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 ) ,e2 AS ( SELECT e1.user_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 WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1 ) ,e3 AS ( SELECT e3.user_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 WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1 ) SELECT SUM(step_1) AS "Step 1" --change to event name ,SUM(step_2) AS "Step 2" --change to event name ,SUM(step_3) AS "Step 3" --change to event name ,ROUND(SUM(step_2)/SUM(step_1)::DECIMAL, 4) as step_1to2_conversion ,ROUND(SUM(step_3)/SUM(step_2)::DECIMAL, 4) as step_2to3_conversion ,ROUND(SUM(step_3)/SUM(step_1)::DECIMAL, 4) as overall_conversion FROM ( SELECT e1.user_id ,step_1 ,step_1_time ,step_2 ,step_2_time ,step_3 ,step_3_time FROM e1 LEFT JOIN e2 ON e1.user_id = e2.user_id LEFT JOIN e3 ON e2.user_id = e3.user_id ) as funnel
WITH e1 AS ( SELECT DISTINCT user_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 -- modify date range (between denotes from the beginning of 4/13 until the very end of 4/20) WHERE time BETWEEN '2016-04-13' AND '2016-04-21' GROUP BY 1 ) ,e2 AS ( SELECT e1.user_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 WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1 ) ,e3 AS ( SELECT e3.user_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 WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1 ) SELECT step, SUM(users) OVER (ORDER BY step DESC) AS total FROM ( SELECT CASE WHEN step_3 = 1 THEN 'Step 3' WHEN step_2 = 1 THEN 'Step 2' WHEN step_1 = 1 THEN 'Step 1' ELSE 'z end' END AS step, SUM(1) AS users FROM ( SELECT e1.user_id ,step_1 ,step_1_time ,step_2 ,step_2_time ,step_3 ,step_3_time FROM e1 LEFT JOIN e2 ON e1.user_id = e2.user_id LEFT JOIN e3 ON e2.user_id = e3.user_id ) as funnel GROUP BY 1 ) a ORDER BY 1
WITH e1 AS ( SELECT DISTINCT user_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 -- modify date range (between denotes from the beginning of 4/13 until the very end of 4/20) WHERE time BETWEEN '2016-04-13' AND '2016-04-21' GROUP BY 1 ) ,e2 AS ( SELECT e1.user_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 WHERE time BETWEEN step_1_time AND '2016-04-21' GROUP BY 1 ) ,e3 AS ( SELECT e3.user_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 WHERE TIME BETWEEN step_2_time AND '2016-04-21' GROUP BY 1 ) SELECT step, SUM(users) OVER (ORDER BY step DESC) AS total FROM ( SELECT CASE WHEN step_3 = 1 THEN 'Step 3' WHEN step_2 = 1 THEN 'Step 2' WHEN step_1 = 1 THEN 'Step 1' ELSE 'z end' END AS step, SUM(1) AS users FROM ( SELECT e1.user_id ,step_1 ,step_1_time ,step_2 ,step_2_time ,step_3 ,step_3_time FROM e1 LEFT JOIN e2 ON e1.user_id = e2.user_id LEFT JOIN e3 ON e2.user_id = e3.user_id ) as funnel GROUP BY 1 ) a 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_62d035e74a52" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-table id="table_9562d6a4c1ba" dataset="dataset" options="table_options"></mode-table> </div> </div> </div> <script> barChartFunnel({ "chart_id": "#chart_62d035e74a52", "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