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

Report not found

There was a problem finding this report.
Retention Cohorts
August 17, 2016 · Refreshed over 3 years ago
Description
This report looks into how well users retain depending on the day of the week that they first signed up. This type of report can be expanded to a variety of cohorts, including signup source, user device, and user location. For details, see: https://help.modeanalytics.com/articles/customize-cohorts-for-customer-retention/

Collaborators

Run History
-- For details on how to use this report, visit -- https://modeanalytics.zendesk.com/hc/en-us/articles/203503600 WITH users AS ( SELECT user_id, activated_at FROM tutorial.playbook_users ), events AS ( SELECT user_id, occurred_at, event_name FROM tutorial.playbook_events WHERE event_type = 'engagement' ) SELECT CASE WHEN dow = 0 THEN 'Sunday' WHEN dow = 1 THEN 'Monday' WHEN dow = 2 THEN 'Tuesday' WHEN dow = 3 THEN 'Wednesday' WHEN dow = 4 THEN 'Thursday' WHEN dow = 5 THEN 'Friday' WHEN dow = 6 THEN 'Saturday' ELSE 'error' END AS "Signup day", COUNT(*) AS users, COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '2 DAY' AND z.r_1_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '2 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "1 day retention", COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '14 DAY' AND z.r_7_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '14 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "7 day retention", COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '21 DAY' AND z.r_14_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '21 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "14 day retention", COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '35 DAY' AND z.r_28_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '35 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "28 day retention" FROM ( SELECT u.user_id, EXTRACT('DOW' FROM u.activated_at) AS dow, u.activated_at, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '1 DAY' AND e.occurred_at < u.activated_at + INTERVAL '2 DAY' THEN u.user_id ELSE NULL END) AS r_1_day, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '7 DAY' AND e.occurred_at < u.activated_at + INTERVAL '14 DAY' THEN u.user_id ELSE NULL END) AS r_7_day, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '14 DAY' AND e.occurred_at < u.activated_at + INTERVAL '21 DAY' THEN u.user_id ELSE NULL END) AS r_14_day, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '28 DAY' AND e.occurred_at < u.activated_at + INTERVAL '35 DAY' THEN u.user_id ELSE NULL END) AS r_28_day FROM users u LEFT JOIN events e ON e.user_id = u.user_id AND e.occurred_at >= u.activated_at AND e.occurred_at < u.activated_at + INTERVAL '35 DAY' WHERE u.activated_at IS NOT NULL GROUP BY 1,2,3 ) z GROUP BY dow ORDER BY dow
-- For details on how to use this report, visit -- https://modeanalytics.zendesk.com/hc/en-us/articles/203503600 WITH users AS ( SELECT user_id, activated_at FROM tutorial.playbook_users ), events AS ( SELECT user_id, occurred_at, event_name FROM tutorial.playbook_events WHERE event_type = 'engagement' ) SELECT CASE WHEN dow = 0 THEN 'Sunday' WHEN dow = 1 THEN 'Monday' WHEN dow = 2 THEN 'Tuesday' WHEN dow = 3 THEN 'Wednesday' WHEN dow = 4 THEN 'Thursday' WHEN dow = 5 THEN 'Friday' WHEN dow = 6 THEN 'Saturday' ELSE 'error' END AS "Signup day", COUNT(*) AS users, COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '2 DAY' AND z.r_1_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '2 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "1 day retention", COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '14 DAY' AND z.r_7_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '14 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "7 day retention", COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '21 DAY' AND z.r_14_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '21 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "14 day retention", COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '35 DAY' AND z.r_28_day > 0 THEN z.user_id ELSE NULL END)/ (COUNT(CASE WHEN z.activated_at <= NOW() - INTERVAL '35 DAY' THEN z.user_id ELSE NULL END) + 1)::FLOAT AS "28 day retention" FROM ( SELECT u.user_id, EXTRACT('DOW' FROM u.activated_at) AS dow, u.activated_at, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '1 DAY' AND e.occurred_at < u.activated_at + INTERVAL '2 DAY' THEN u.user_id ELSE NULL END) AS r_1_day, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '7 DAY' AND e.occurred_at < u.activated_at + INTERVAL '14 DAY' THEN u.user_id ELSE NULL END) AS r_7_day, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '14 DAY' AND e.occurred_at < u.activated_at + INTERVAL '21 DAY' THEN u.user_id ELSE NULL END) AS r_14_day, COUNT(CASE WHEN e.occurred_at >= u.activated_at + INTERVAL '28 DAY' AND e.occurred_at < u.activated_at + INTERVAL '35 DAY' THEN u.user_id ELSE NULL END) AS r_28_day FROM users u LEFT JOIN events e ON e.user_id = u.user_id AND e.occurred_at >= u.activated_at AND e.occurred_at < u.activated_at + INTERVAL '35 DAY' WHERE u.activated_at IS NOT NULL GROUP BY 1,2,3 ) z GROUP BY dow ORDER BY dow
<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: "by column", // Other option is "by column" excluded_columns: ["Signup day","users"], title: "Retention rate by the day of the week", 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") { 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