Report not found

There was a problem finding this report.
Retention Cohort - User Language
May 8, 2015 · Refreshed 9 months ago
Description
User cohorts by user language.

Collaborators

Run History
WITH users AS ( SELECT user_id, activated_at, state, language FROM tutorial.playbook_users ), events AS ( SELECT user_id, event_name, occurred_at, device, location FROM tutorial.playbook_events ) SELECT x.cohort AS "Language", MAX(x.period_age) OVER (PARTITION BY x.cohort) AS cutoff_age, x.period_age, x.tally, x.tally/MAX(x.tally) OVER (PARTITION BY x.cohort)::FLOAT AS retention_rate, MAX(x.tally) OVER (PARTITION BY x.cohort) AS "New Users" FROM ( SELECT u.language AS cohort, FLOOR(EXTRACT('day' FROM e.occurred_at - u.activated_at)/7) AS period_age, COUNT(DISTINCT u.user_id) AS tally FROM users u LEFT JOIN events e ON e.user_id = u.user_id WHERE u.activated_at IS NOT NULL AND u.activated_at >= '2014-06-01' GROUP BY 1,2 ) x ORDER BY 3,1
WITH users AS ( SELECT user_id, activated_at, state, language FROM tutorial.playbook_users ), events AS ( SELECT user_id, event_name, occurred_at, device, location FROM tutorial.playbook_events ) SELECT x.cohort AS "Language", MAX(x.period_age) OVER (PARTITION BY x.cohort) AS cutoff_age, x.period_age, x.tally, x.tally/MAX(x.tally) OVER (PARTITION BY x.cohort)::FLOAT AS retention_rate, MAX(x.tally) OVER (PARTITION BY x.cohort) AS "New Users" FROM ( SELECT u.language AS cohort, FLOOR(EXTRACT('day' FROM e.occurred_at - u.activated_at)/7) AS period_age, COUNT(DISTINCT u.user_id) AS tally FROM users u LEFT JOIN events e ON e.user_id = u.user_id WHERE u.activated_at IS NOT NULL AND u.activated_at >= '2014-06-01' GROUP BY 1,2 ) x ORDER BY 3,1
<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", cohort_column: "Language", pivot_column: "period_age", value_column: "retention_rate", total_column: "New Users", title: "Retention rate by language", pivot_label: "Retention rates by weeks after signup", value_is_percent: true, include_first_period: false } ] drawGrid(options[0]) function drawGrid(o) { if (o["html_element"]) { htmlElement = o["html_element"]; } else { htmlElement = "body"; } $(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, cohorts = _.uniq( _.pluck(data, o["cohort_column"]) ), pivots = _.uniq( _.pluck(data, o["pivot_column"]) ), filteredDataForColors = data; if (o["include_first_period"] == false) { var firstPeriod = pivots[0], filteredDataForColors = data.filter(function(d) { return d[o["pivot_column"]] != firstPeriod; }); pivots = pivots.slice(1,10000); } var color = d3.scale.quantize() .domain(d3.extent(filteredDataForColors, function(d) { return d[o["value_column"]]; })) .range(["#d73027","#f46d43","#fdae61","#fee08b","#ffffbf","#d9ef8b","#a6d96a","#66bd63","#1a9850"]) d3.select(htmlElement) .append("div") .attr("class","heatmap-title") .text(function() { if (o["title"]) { return o["title"]; } }) d3.select(htmlElement) .append("div") .attr("class","heatmap-pivot-label") .text(function() { if (o["pivot_label"]) { return o["pivot_label"]; } }) if (o["total_column"]) { headers = [o["cohort_column"],o["total_column"]].concat(pivots) } else { headers = [o["cohort_column"]].concat(pivots) } 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(headers) .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; }) table.selectAll(".heatmap-table-row") .data(cohorts) .enter().append("tr") .attr("class","heatmap-table-row") .selectAll(".heatmap-table-cell") .data(function(d) { return makeRow(data,d,pivots,o); }) .enter().append("td") .style("background",function(d) { if (checkShade(d,o)) { return color(d.value); } }) .attr("class",function(d) { return cellClass(d); }) .text(function(d) { return fmt(d,o); }) function checkShade(entry,options) { if (entry.value == "") { return false; } else if (entry.column == options["pivot_column"] || entry.column == options["total_column"]) { return false; } else if (entry.column == options["value_column"]) { 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(data,cohort,pivots,options) { var row = [ {column: options["cohort_column"], value: cohort } ]; if (options["total_column"]) { var total = _.filter(data, function(d) { return d[options["cohort_column"]] == cohort; })[0], totalObject = { column: options["total_column"], value: total[options["total_column"]] }; row = row.concat(totalObject); } pivots.forEach(function(p,i) { var matches = _.filter(data, function(d) { return d[options["cohort_column"]] == cohort && d[options["pivot_column"]] == p }); if (matches.length > 0) { entry = d3.mean( _.pluck(matches,options["value_column"]) ); } else { entry = ""; } row = row.concat( {column: options["value_column"], value: entry} ) }) return row; } function fmt(entry,options) { var type = getDataType(entry.column), valueColumn = options["value_column"], totalColumn = options["total_column"]; var c = d3.format(","), p = d3.format(".2p"), t = d3.time.format("%b %d, %Y"); if (entry.value == "") { return entry.value; } else if (type == "datetime" || type == "timestamp") { return t(new Date(entry.value)) } else if (entry.column == totalColumn) { return c(entry.value); } else if (entry.column == valueColumn && options["value_is_percent"]) { return p(entry.value); } else if (entry.column == valueColumn && !options["value_is_percent"]) { 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