REPORT PARAMETERS

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

Report not found

There was a problem finding this report.
When Do Users Slip Away?
November 2, 2014 · Refreshed over 4 years ago
Description
The table shows the percentage of users who take a certain action after having not having taken the action for a given number of days (the value down the left column) and after having taken the action a given number of times before (the values along the top). For details, see: https://help.modeanalytics.com/articles/when-do-customers-churn/

Collaborators

Run History
-- For details on how to use this report, visit -- https://modeanalytics.zendesk.com/hc/en-us/articles/203326234 WITH events AS ( SELECT user_id, occurred_at FROM tutorial.playbook_events WHERE event_name = 'send_message' ) SELECT days_since_last_event AS "Days since last event", COUNT(CASE WHEN event_number BETWEEN 1 AND 5 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 1 AND 5 THEN user_id ELSE NULL END) + 1)::FLOAT AS "1-5", COUNT(CASE WHEN event_number BETWEEN 6 AND 10 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 6 AND 10 THEN user_id ELSE NULL END) + 1)::FLOAT AS "6-10", COUNT(CASE WHEN event_number BETWEEN 11 AND 15 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 11 AND 15 THEN user_id ELSE NULL END) + 1)::FLOAT AS "10-15", COUNT(CASE WHEN event_number BETWEEN 16 AND 20 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 16 AND 20 THEN user_id ELSE NULL END) + 1)::FLOAT AS "16-20", COUNT(CASE WHEN event_number BETWEEN 21 AND 25 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 21 AND 25 THEN user_id ELSE NULL END) + 1)::FLOAT AS "21-25", COUNT(CASE WHEN event_number BETWEEN 26 AND 30 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 26 AND 30 THEN user_id ELSE NULL END) + 1)::FLOAT AS "26-30", COUNT(CASE WHEN event_number BETWEEN 31 AND 35 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 31 AND 35 THEN user_id ELSE NULL END) + 1)::FLOAT AS "31-35", COUNT(CASE WHEN event_number BETWEEN 36 AND 40 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 36 AND 40 THEN user_id ELSE NULL END) + 1)::FLOAT AS "36-40", COUNT(CASE WHEN event_number BETWEEN 41 AND 45 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 41 AND 45 THEN user_id ELSE NULL END) + 1)::FLOAT AS "41-45", COUNT(CASE WHEN event_number BETWEEN 46 AND 50 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 46 AND 50 THEN user_id ELSE NULL END) + 1)::FLOAT AS "46-50", COUNT(CASE WHEN event_number BETWEEN 51 AND 55 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 51 AND 55 THEN user_id ELSE NULL END) + 1)::FLOAT AS "51-55", COUNT(CASE WHEN event_number BETWEEN 56 AND 60 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 56 AND 60 THEN user_id ELSE NULL END) + 1)::FLOAT AS "56-60", COUNT(CASE WHEN event_number BETWEEN 61 AND 65 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 61 AND 65 THEN user_id ELSE NULL END) + 1)::FLOAT AS "61-65", COUNT(CASE WHEN event_number BETWEEN 66 AND 70 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 66 AND 70 THEN user_id ELSE NULL END) + 1)::FLOAT AS "66-70", COUNT(CASE WHEN event_number BETWEEN 71 AND 75 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 71 AND 75 THEN user_id ELSE NULL END) + 1)::FLOAT AS "71-75" FROM ( SELECT c.counter AS days_since_last_event, x.* FROM (SELECT s.a AS counter FROM generate_series(0,100) AS s(a)) c LEFT JOIN ( SELECT e.user_id, e.occurred_at, EXTRACT('day' FROM NOW() - e.occurred_at) AS days_until_now, COUNT(*) OVER (PARTITION BY e.user_id) AS total_events, ROW_NUMBER() OVER (PARTITION BY e.user_id ORDER BY e.occurred_at) AS event_number, COALESCE( EXTRACT('DAY' FROM LEAD(e.occurred_at,1) OVER (PARTITION BY e.user_id ORDER BY e.occurred_at) - e.occurred_at), EXTRACT('DAY' FROM NOW() - e.occurred_at) ) AS days_until_next FROM events e ) x ON x.days_until_next > c.counter ) z WHERE days_until_now >= 30 AND days_since_last_event <= 100 AND event_number <= 75 GROUP BY 1 ORDER BY 1
-- For details on how to use this report, visit -- https://modeanalytics.zendesk.com/hc/en-us/articles/203326234 WITH events AS ( SELECT user_id, occurred_at FROM tutorial.playbook_events WHERE event_name = 'send_message' ) SELECT days_since_last_event AS "Days since last event", {% if display == 'percent_of_users_returning' %} COUNT(CASE WHEN event_number BETWEEN 1 AND 5 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 1 AND 5 THEN user_id ELSE NULL END) + 1)::FLOAT AS "1-5", COUNT(CASE WHEN event_number BETWEEN 6 AND 10 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 6 AND 10 THEN user_id ELSE NULL END) + 1)::FLOAT AS "6-10", COUNT(CASE WHEN event_number BETWEEN 11 AND 15 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 11 AND 15 THEN user_id ELSE NULL END) + 1)::FLOAT AS "10-15", COUNT(CASE WHEN event_number BETWEEN 16 AND 20 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 16 AND 20 THEN user_id ELSE NULL END) + 1)::FLOAT AS "16-20", COUNT(CASE WHEN event_number BETWEEN 21 AND 25 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 21 AND 25 THEN user_id ELSE NULL END) + 1)::FLOAT AS "21-25", COUNT(CASE WHEN event_number BETWEEN 26 AND 30 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 26 AND 30 THEN user_id ELSE NULL END) + 1)::FLOAT AS "26-30", COUNT(CASE WHEN event_number BETWEEN 31 AND 35 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 31 AND 35 THEN user_id ELSE NULL END) + 1)::FLOAT AS "31-35", COUNT(CASE WHEN event_number BETWEEN 36 AND 40 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 36 AND 40 THEN user_id ELSE NULL END) + 1)::FLOAT AS "36-40", COUNT(CASE WHEN event_number BETWEEN 41 AND 45 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 41 AND 45 THEN user_id ELSE NULL END) + 1)::FLOAT AS "41-45", COUNT(CASE WHEN event_number BETWEEN 46 AND 50 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 46 AND 50 THEN user_id ELSE NULL END) + 1)::FLOAT AS "46-50", COUNT(CASE WHEN event_number BETWEEN 51 AND 55 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 51 AND 55 THEN user_id ELSE NULL END) + 1)::FLOAT AS "51-55", COUNT(CASE WHEN event_number BETWEEN 56 AND 60 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 56 AND 60 THEN user_id ELSE NULL END) + 1)::FLOAT AS "56-60", COUNT(CASE WHEN event_number BETWEEN 61 AND 65 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 61 AND 65 THEN user_id ELSE NULL END) + 1)::FLOAT AS "61-65", COUNT(CASE WHEN event_number BETWEEN 66 AND 70 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 66 AND 70 THEN user_id ELSE NULL END) + 1)::FLOAT AS "66-70", COUNT(CASE WHEN event_number BETWEEN 71 AND 75 AND event_number < total_events THEN user_id ELSE NULL END)/ (COUNT(CASE WHEN event_number BETWEEN 71 AND 75 THEN user_id ELSE NULL END) + 1)::FLOAT AS "71-75" {% else %} COUNT(CASE WHEN event_number BETWEEN 1 AND 5 THEN user_id ELSE NULL END) AS "1-5", COUNT(CASE WHEN event_number BETWEEN 6 AND 10 THEN user_id ELSE NULL END) AS "6-10", COUNT(CASE WHEN event_number BETWEEN 11 AND 15 THEN user_id ELSE NULL END) AS "10-15", COUNT(CASE WHEN event_number BETWEEN 16 AND 20 THEN user_id ELSE NULL END) AS "16-20", COUNT(CASE WHEN event_number BETWEEN 21 AND 25 THEN user_id ELSE NULL END) AS "21-25", COUNT(CASE WHEN event_number BETWEEN 26 AND 30 THEN user_id ELSE NULL END) AS "26-30", COUNT(CASE WHEN event_number BETWEEN 31 AND 35 THEN user_id ELSE NULL END) AS "31-35", COUNT(CASE WHEN event_number BETWEEN 36 AND 40 THEN user_id ELSE NULL END) AS "36-40", COUNT(CASE WHEN event_number BETWEEN 41 AND 45 THEN user_id ELSE NULL END) AS "41-45", COUNT(CASE WHEN event_number BETWEEN 46 AND 50 THEN user_id ELSE NULL END) AS "46-50", COUNT(CASE WHEN event_number BETWEEN 51 AND 55 THEN user_id ELSE NULL END) AS "51-55", COUNT(CASE WHEN event_number BETWEEN 56 AND 60 THEN user_id ELSE NULL END) AS "56-60", COUNT(CASE WHEN event_number BETWEEN 61 AND 65 THEN user_id ELSE NULL END) AS "61-65", COUNT(CASE WHEN event_number BETWEEN 66 AND 70 THEN user_id ELSE NULL END) AS "66-70", COUNT(CASE WHEN event_number BETWEEN 71 AND 75 THEN user_id ELSE NULL END) AS "71-75" {% endif %} FROM ( SELECT c.counter AS days_since_last_event, x.* FROM (SELECT s.a AS counter FROM generate_series(0,100) AS s(a)) c LEFT JOIN ( SELECT e.user_id, e.occurred_at, EXTRACT('day' FROM NOW() - e.occurred_at) AS days_until_now, COUNT(*) OVER (PARTITION BY e.user_id) AS total_events, ROW_NUMBER() OVER (PARTITION BY e.user_id ORDER BY e.occurred_at) AS event_number, COALESCE( EXTRACT('DAY' FROM LEAD(e.occurred_at,1) OVER (PARTITION BY e.user_id ORDER BY e.occurred_at) - e.occurred_at), EXTRACT('DAY' FROM NOW() - e.occurred_at) ) AS days_until_next FROM events e ) x ON x.days_until_next > c.counter ) z WHERE days_until_now >= 30 AND days_since_last_event <= 100 AND event_number <= 75 GROUP BY 1 ORDER BY 1 {% form %} display: type: select default: 'percent_of_users_returning' options: [['percent_of_users_returning', 'percent_of_users_returning'], ['number_of_users', 'number_of_users'] ] {% endform %}
<style> .heatmap-container { border: 1px solid #F2F3F3; margin: 20px; overflow: scroll; } .heatmap-title { background: #F2F3F3; line-height: 26px; height: 26px; padding-left: 8px; font-weight: 500; } .heatmap-pivot-label { font-size: 12px; text-align: center; margin: 10px; } .heatmap-table { table-layout: fixed; border-collapse: separate; font-size: 10px; border-spacing: 1px; margin: 5px auto 10px auto; } .heatmap-table-header-cell { overflow: hidden; font-size: 12px; padding: 4px; color: white; background: gray; } .heatmap-string { text-align: left; } .heatmap-number { text-align: center; } .heatmap-table td { overflow: hidden; padding: 6px 6px 4px 6px; /*border-radius: 4px;*/ } .label { text-align: center; color: #8D9D9F; } .title { padding-left: 10px; } </style> <div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div id="graphic1"></div> <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.8.2/underscore-min.js"></script> <script> var options = [ { html_element: "#graphic1", query_name: "Query 1", gradient: "full table", // Other option is "by column" excluded_columns: ["Days since last event"], title: "When do users slip away?", value_is_percent: true, colors: ["#f7fbff","#deebf7","#c6dbef","#9ecae1","#6baed6","#4292c6","#2171b5"] } ] drawGrid(options[0]) function drawGrid(o) { var htmlElement = o["html_element"] || "body"; colors = o["colors"] || ["#d73027","#f46d43","#fdae61","#fee08b","#ffffbf","#d9ef8b","#a6d96a","#66bd63","#1a9850"], exlcudedColumns = o["excluded_columns"] || [], gradient = o["gradient"] || "full table"; $(htmlElement).addClass("heatmap-container"); var data = datasets.filter(function(d) { return d.queryName == o["query_name"]; })[0].content, columns = datasets.filter(function(d) { return d.queryName == o["query_name"]; })[0].columns; if (gradient == "by column") { var colorArray = {}; columns.forEach(function(c) { if (exlcudedColumns.indexOf(c.name) == -1) { var columnRange = d3.extent(data, function(d) { return d[c.name]; }); colorArray[c.name] = d3.scale.quantize() .domain(d3.extent(columnRange)) .range(colors) } }) } else { var fullValues = []; columns.forEach(function(c) { if (exlcudedColumns.indexOf(c.name) == -1) { var columnRange = d3.extent(data, function(d) { return d[c.name]; }); fullValues = fullValues.concat(columnRange); } }) color = d3.scale.quantize() .domain(d3.extent(fullValues)) .range(colors) } d3.select(htmlElement) .append("div") .attr("class","heatmap-title") .text(function() { if (o["title"]) { return o["title"]; } }) var table = d3.select(htmlElement).append("table") .attr("class","heatmap-table"); table.selectAll(".heatmap-table-header") .data([0]) .enter().append("tr") .attr("class","heatmap-table-header") .selectAll("heatmap-table-header-cell") .data(columns) .enter().append("td") .attr("class",function(d) { if (isNaN(d)) { return "heatmap-table-header-cell heatmap-string"; } else { return "heatmap-table-header-cell heatmap-number"; } }) .text(function(d) { return d.name; }) table.selectAll(".heatmap-table-row") .data(data) .enter().append("tr") .attr("class","heatmap-table-row") .selectAll(".heatmap-table-cell") .data(function(d) { return makeRow(d,columns); }) .enter().append("td") .style("background",function(d) { if (checkShade(d,o)) { return pickColor(d,o); } }) .attr("class",function(d) { return cellClass(d); }) .text(function(d) { return fmt(d,o); }) function pickColor(entry,options) { if (gradient == "by column") { color = colorArray[entry.column]; } return color(entry.value); } function checkShade(entry,options) { if (entry.value === "") { return false; } else if (exlcudedColumns.indexOf(entry.column) == -1) { return true; } else { return false; } } function cellClass(entry) { var type = getDataType(entry.column); if (type == "float" || type == "integer") { return "heatmap-number"; } else { return "heatmap-string"; } } function getDataType(column) { return columns.filter(function(d) { return d.name == column })[0].type; } function makeRow(rowObject,columns) { var row = []; columns.forEach(function(c) { row.push({column: c.name, value: rowObject[c.name] }); }) return row; } function fmt(entry,options) { var type = getDataType(entry.column); var c = d3.format(","), p = d3.format(".2p"), t = d3.time.format("%b %d, %Y"), r = d3.time.format("%Y-%m-%dT%H:%M:%S.000Z").parse; if (entry.value === "") { return entry.value; } else if (type == "datetime" || type == "timestamp" || type == "date") { var newDate = new Date(Date.parse(entry.value)); parsedString = r(newDate.toISOString()); return t(parsedString); } else if (checkShade(entry,options) && options["value_is_percent"]) { return p(entry.value); } else if (type == "number" || type == "float" || type == "integer") { return c(entry.value); } else { return entry.value; } } } </script>
{{ 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