There is a more recent run of this report. Click here to view it.

Report not found

There was a problem finding this report.
Path Analysis
June 14, 2016 · Refreshed 5 months ago
Description
The top 20 flows through your product

Collaborators

Run History
WITH event_count AS ( SELECT event_table_name ,count(*) AS cardinality FROM heap.all_events WHERE TIME > current_date - 2592000 GROUP BY 1 ) ,all_events AS ( SELECT DISTINCT event_id ,heap.all_events.user_id AS user_id ,heap.all_events.event_table_name AS event_name ,heap.all_events.TIME AS occurred_at ,cardinality AS number FROM heap.all_events LEFT JOIN event_count ON heap.all_events.event_table_name =event_count.event_table_name ORDER BY 1 ,2 ) ,events AS ( SELECT all_events.user_id ,all_events.event_id ,event_name ,occurred_at ,cardinality FROM all_events INNER JOIN ( SELECT event_id ,user_id ,min(number) AS cardinality FROM all_events GROUP BY 1 ,2 ) AS event ON all_events.number = event.cardinality AND all_events.event_id = event.event_id AND all_events.user_id = event.user_id ORDER BY 1 ,2 ,4 ) SELECT e1 ,e2 ,e3 ,e4 ,e5 ,COUNT(*) AS occurrances FROM ( --Pivot out first five events in each session SELECT user_id ,session ,MAX(CASE WHEN event_number = 1 THEN event_name ELSE NULL END) AS e1 ,MAX(CASE WHEN event_number = 2 THEN event_name ELSE NULL END) AS e2 ,MAX(CASE WHEN event_number = 3 THEN event_name ELSE NULL END) AS e3 ,MAX(CASE WHEN event_number = 4 THEN event_name ELSE NULL END) AS e4 ,MAX(CASE WHEN event_number = 5 THEN event_name ELSE NULL END) AS e5 FROM ( -- Find event number in session SELECT z.* ,ROW_NUMBER() OVER ( PARTITION BY user_id ,session ORDER BY occurred_at ) AS event_number FROM ( -- Sum breaks to find sessions SELECT y.* ,SUM(BREAK) OVER ( ORDER BY user_id ,occurred_at ROWS UNBOUNDED PRECEDING ) AS session FROM ( -- Add flag if last event was more than 10 minutes ago SELECT x.* ,CASE WHEN last_event IS NULL OR occurred_at >= last_event + INTERVAL '10 MINUTE' THEN 1 ELSE 0 END AS BREAK FROM ( -- Find last event SELECT * ,LAG(occurred_at, 1) OVER ( PARTITION BY user_id ORDER BY occurred_at ) AS last_event FROM events ) x ) y ) z ) a -- remove this to get more than 5 events/ session WHERE event_number <= 5 GROUP BY 1 ,2 ) final --remove this to include single page views where e5 is not null GROUP BY 1 ,2 ,3 ,4 ,5 ORDER BY 6 DESC LIMIT 20
WITH event_count AS ( SELECT event_table_name ,count(*) AS cardinality FROM heap.all_events WHERE TIME > current_date - 2592000 GROUP BY 1 ) ,all_events AS ( SELECT DISTINCT event_id ,heap.all_events.user_id AS user_id ,heap.all_events.event_table_name AS event_name ,heap.all_events.TIME AS occurred_at ,cardinality AS number FROM heap.all_events LEFT JOIN event_count ON heap.all_events.event_table_name =event_count.event_table_name ORDER BY 1 ,2 ) ,events AS ( SELECT all_events.user_id ,all_events.event_id ,event_name ,occurred_at ,cardinality FROM all_events INNER JOIN ( SELECT event_id ,user_id ,min(number) AS cardinality FROM all_events GROUP BY 1 ,2 ) AS event ON all_events.number = event.cardinality AND all_events.event_id = event.event_id AND all_events.user_id = event.user_id ORDER BY 1 ,2 ,4 ) SELECT e1 ,e2 ,e3 ,e4 ,e5 ,COUNT(*) AS occurrances FROM ( --Pivot out first five events in each session SELECT user_id ,session ,MAX(CASE WHEN event_number = 1 THEN event_name ELSE NULL END) AS e1 ,MAX(CASE WHEN event_number = 2 THEN event_name ELSE NULL END) AS e2 ,MAX(CASE WHEN event_number = 3 THEN event_name ELSE NULL END) AS e3 ,MAX(CASE WHEN event_number = 4 THEN event_name ELSE NULL END) AS e4 ,MAX(CASE WHEN event_number = 5 THEN event_name ELSE NULL END) AS e5 FROM ( -- Find event number in session SELECT z.* ,ROW_NUMBER() OVER ( PARTITION BY user_id ,session ORDER BY occurred_at ) AS event_number FROM ( -- Sum breaks to find sessions SELECT y.* ,SUM(BREAK) OVER ( ORDER BY user_id ,occurred_at ROWS UNBOUNDED PRECEDING ) AS session FROM ( -- Add flag if last event was more than 10 minutes ago SELECT x.* ,CASE WHEN last_event IS NULL OR occurred_at >= last_event + INTERVAL '10 MINUTE' THEN 1 ELSE 0 END AS BREAK FROM ( -- Find last event SELECT * ,LAG(occurred_at, 1) OVER ( PARTITION BY user_id ORDER BY occurred_at ) AS last_event FROM events ) x ) y ) z ) a -- remove this to get more than 5 events/ session WHERE event_number <= 5 GROUP BY 1 ,2 ) final --remove this to include single page views where e5 is not null GROUP BY 1 ,2 ,3 ,4 ,5 ORDER BY 6 DESC LIMIT 20
<style> body { font-size: 12px; font-weight: 400; } #main { width: 620px; margin: 0 auto; } .legend-container { display: block; margin: 0 auto; text-align: center; max-width: 600px; } .legend { position: relative; vertical-align: middle; display: inline-block; margin-left: 5px; margin-top: 5px; text-align: center; width: 195px; height: 100px padding: 5px; } #sequence text, .legend-container text { fill: #fff; } #chart { position: relative; } #chart path { stroke: #fff; } #explanation { position: absolute; margin-left: auto; margin-right: auto; top: 215px; left: 0; right: 0; width: 140px; text-align: center; color: #666; z-index: -1; } #percentage { font-size: 2.5em; color: #666; } p { color: #666; text-align: center; } #title { font-size: 18px } #details { font-size: 14px; } #cond_percentage { font-size: 1.8em; color: #666; } </style> <p id="title">Event Sequences by Session</p> <p id="details">Hover over bars in the circle for details</p> <div id="main"> <div id="sequence"></div> <div id="chart"> <div id="explanation" style="visibility: hidden;"> <span id="percentage"></span> <br> of visits begin with this sequence of pages. <br> <br> Conditional percent of <span id="cond_percentage"></span> <br> from previous location </div> </div> </div> <div class="legend-container"></div> <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.6.0/underscore-min.js"></script> <script> // Dimensions of sunburst. var width = 620; var height = 600; var radius = Math.min(width, height) / 2; // Breadcrumb dimensions: width, height, spacing, width of tip/tail. var b = { w: 120, h: 30, s: 3, t: 10 }; var data = dataset.content, e1 = _.uniq(_.pluck(data,"e1")), e2 = _.uniq(_.pluck(data,"e2")), e3 = _.uniq(_.pluck(data,"e3")), e4 = _.uniq(_.pluck(data,"e4")), e5 = _.uniq(_.pluck(data,"e5")), events = _.uniq(e1.concat(e2).concat(e3).concat(e4).concat(e5)); // Mapping of step names to colors. // var colorRange = colorbrewer.Set1[9] var colorRange = ["#33cccc","#00cc99","#339966","#ffcc00","#ff6699","#cc66ff","#6666ff","#3366ff","#0033cc", "#009999", "#009933", "#ff6600", "##6600cc", "#ff0000", "#0000ff", "#cc0066"] var colors = {} events.forEach(function(e,i) { if (e != null) { colors[e] = colorRange[i % 36]; } }) colors["end"] = "#666" // Total size of all segments; we set this later, after loading the data. var totalSize = 0; var vis = d3.select("#chart").append("svg:svg") .attr("width", width) .attr("height", height) .append("svg:g") .attr("id", "container") .attr("transform", "translate(" + width / 2 + "," + height / 2 + ")"); var partition = d3.layout.partition() .size([2 * Math.PI, radius * radius]) .value(function(d) { return d.size; }); var arc = d3.svg.arc() .startAngle(function(d) { return d.x; }) .endAngle(function(d) { return d.x + d.dx; }) .innerRadius(function(d) { return Math.sqrt(d.y); }) .outerRadius(function(d) { return Math.sqrt(d.y + d.dy); }); // Use d3.text and d3.csv.parseRows so that we do not need to have a header // row, and can receive the csv as an array of arrays. formattedData = [] data.forEach(function(d) { var c = 1 var sequence = d.e1; if (d.e2 != null && c == 1) { e2 = "-~-" + d.e2; } else if (c == 1) { e2 = "-~-end"; c = 0; } else { e2 = ""; } if (d.e3 != null && c == 1) { e3 = "-~-" + d.e3; } else if (c == 1) { e3 = "-~-end"; c = 0; } else { e3 = ""; } if (d.e4 != null && c == 1) { e4 = "-~-" + d.e4; } else if (c == 1) { e4 = "-~-end"; c = 0; } else { e4 = ""; } if (d.e5 != null && c == 1) { e5 = "-~-" + d.e5; } else if (c == 1) { e5 = "-~-end"; c = 0; } else { e5 = ""; } sequence = sequence + e2 + e3 + e4 + e5; var ent = {0:sequence, 1:d.occurrances} formattedData.push(ent) }) var json = buildHierarchy(formattedData); createVisualization(json); // Main function to draw and set up the visualization, once we have the data. function createVisualization(json) { // Basic setup of page elements. initializeBreadcrumbTrail(); drawLegend(); d3.select("#togglelegend").on("click", toggleLegend); // Bounding circle underneath the sunburst, to make it easier to detect // when the mouse leaves the parent g. vis.append("svg:circle") .attr("r", radius) .style("opacity", 0); // For efficiency, filter nodes to keep only those large enough to see. var nodes = partition.nodes(json) .filter(function(d) { return (d.dx > 0.005); // 0.005 radians = 0.29 degrees }); var path = vis.data([json]).selectAll("path") .data(nodes) .enter().append("svg:path") .attr("display", function(d) { return d.depth ? null : "none"; }) .attr("d", arc) .attr("fill-rule", "evenodd") .style("fill", function(d) { return colors[d.name]; }) .style("opacity", 1) .on("mouseover", mouseover); // Add the mouseleave handler to the bounding circle. d3.select("#container").on("mouseleave", mouseleave); // Get total size of the tree = value of root node from partition. totalSize = path.node().__data__.value; }; // Fade all but the current sequence, and show it in the breadcrumb trail. function mouseover(d) { var percentage = (100 * d.value / totalSize).toPrecision(3); var percentageString = percentage + "%"; if (percentage < 0.1) { percentageString = "< 0.1%"; } //Calculate conditional percentage var sequenceArray = getAncestors(d); var parent_conditional_value = d.parent.value; var cond_percentage = (100*d.value/parent_conditional_value).toPrecision(3); var cond_percentageString = cond_percentage + "%"; if (cond_percentage < 1.0) { percentageString = "< 1%"; } d3.select("#cond_percentage") .text(cond_percentageString); d3.select("#percentage") .text(percentageString); d3.select("#explanation") .style("visibility", ""); var sequenceArray = getAncestors(d); updateBreadcrumbs(sequenceArray, percentageString); // Fade all the segments. d3.selectAll("path") .style("opacity", 0.3); // Then highlight only those that are an ancestor of the current segment. vis.selectAll("path") .filter(function(node) { return (sequenceArray.indexOf(node) >= 0); }) .style("opacity", 1); } // Restore everything to full opacity when moving off the visualization. function mouseleave(d) { // Hide the breadcrumb trail d3.select("#trail") .style("visibility", "hidden"); // Deactivate all segments during transition. d3.selectAll("path").on("mouseover", null); // Transition each segment to full opacity and then reactivate it. d3.selectAll("path") .transition() .duration(1000) .style("opacity", 1) .each("end", function() { d3.select(this).on("mouseover", mouseover); }); d3.select("#explanation") .style("visibility", "hidden"); } // Given a node in a partition layout, return an array of all of its ancestor // nodes, highest first, but excluding the root. function getAncestors(node) { var path = []; var current = node; while (current.parent) { path.unshift(current); current = current.parent; } return path; } function initializeBreadcrumbTrail() { // Add the svg area. var trail = d3.select("#sequence").append("svg:svg") .attr("width", width) .attr("height", 50) .attr("id", "trail"); // Add the label at the end, for the percentage. trail.append("svg:text") .attr("id", "endlabel") .style("fill", "#000"); } // Generate a string that describes the points of a breadcrumb polygon. function breadcrumbPoints(d, i) { var points = []; points.push("0,0"); points.push(b.w + ",0"); points.push(b.w + b.t + "," + (b.h / 2)); points.push(b.w + "," + b.h); points.push("0," + b.h); if (i > 0) { // Leftmost breadcrumb; don't include 6th vertex. points.push(b.t + "," + (b.h / 2)); } return points.join(" "); } // Update the breadcrumb trail to show the current sequence and percentage. function updateBreadcrumbs(nodeArray, percentageString) { // Data join; key function combines name and depth (= position in sequence). var g = d3.select("#trail") .selectAll("g") .data(nodeArray, function(d) { return d.name + d.depth; }); // Add breadcrumb and label for entering nodes. var entering = g.enter().append("svg:g"); entering.append("svg:polygon") .attr("points", breadcrumbPoints) .style("fill", function(d) { return colors[d.name]; }); entering.append("svg:text") .attr("x", (b.w + b.t) / 2) .attr("y", b.h / 2) .attr("dy", "0.35em") .attr("text-anchor", "middle") .text(function(d) { return d.name; }); // Set position for entering and updating nodes. g.attr("transform", function(d, i) { return "translate(" + i * (b.w + b.s) + ", 0)"; }); // Remove exiting nodes. g.exit().remove(); // Make the breadcrumb trail visible, if it's hidden. d3.select("#trail") .style("visibility", ""); } function drawLegend() { // Dimensions of legend item: width, height, spacing, radius of rounded rect. var li = { w: 195, h: 30, s: 3, r: 3 }; d3.entries(colors).forEach(function(c) { divContainer = d3.select(".legend-container") .append("div") .attr("class","legend") svg = divContainer.append("svg:svg") .attr("width", li.w) .attr("height", li.h); svg.append("svg:rect") .attr("rx", li.r) .attr("ry", li.r) .attr("width", li.w) .attr("height", li.h) .style("fill", function() { return c.value; }); svg.append("svg:text") .attr("x", li.w / 2) .attr("y", li.h / 2) .attr("dy", "0.35em") .attr("text-anchor", "middle") .text(function() { return c.key; }); }) } function toggleLegend() { var legend = d3.select("#legend"); if (legend.style("visibility") == "hidden") { legend.style("visibility", ""); } else { legend.style("visibility", "hidden"); } } // Take a 2-column CSV and transform it into a hierarchical structure suitable // for a partition layout. The first column is a sequence of step names, from // root to leaf, separated by hyphens. The second column is a count of how // often that sequence occurred. function buildHierarchy(csv) { var root = {"name": "root", "children": []}; for (var i = 0; i < csv.length; i++) { var sequence = csv[i][0]; var size = +csv[i][1]; if (isNaN(size)) { // e.g. if this is a header row continue; } var parts = sequence.split("-~-"); var currentNode = root; for (var j = 0; j < parts.length; j++) { var children = currentNode["children"]; var nodeName = parts[j]; var childNode; if (j + 1 < parts.length) { // Not yet at the end of the sequence; move down the tree. var foundChild = false; for (var k = 0; k < children.length; k++) { if (children[k]["name"] == nodeName) { childNode = children[k]; foundChild = true; break; } } // If we don't already have a child node for this branch, create it. if (!foundChild) { childNode = {"name": nodeName, "children": []}; children.push(childNode); } currentNode = childNode; } else { // Reached the end of the sequence; create a leaf node. childNode = {"name": nodeName, "size": size}; children.push(childNode); } } } return root; }; </script> <div class="mode-grid container"> <div class="row"> <div class="col-md-12"> <mode-table id="table_870c93dec08b" dataset="dataset" options="table_options"></mode-table> <h5>**To use with Heap data, modify your query from heap.table_name to main_production.table_name</h5> </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