2f:T6a6,WITH purchase_orders AS ( SELECT case when type == 'add_to_cart' then 'Add To Cart' when type = 'begin_checkout' then 'Begin Checkout' when type = 'purchase' then 'Purchased' end as type , case when type == 'add_to_cart' then 1 when type = 'begin_checkout' then 2 when type = 'purchase' then 3 end as purchase_order , items.category , events.ga_session_id , events.user_id , date FROM events JOIN items ON events.item_id = items.id WHERE items.category = ${selectCategory} ), first_step AS ( SELECT * FROM purchase_orders WHERE purchase_order = 1 ), second_step AS ( SELECT * FROM purchase_orders WHERE purchase_order = 2 ), third_step AS ( SELECT * FROM purchase_orders WHERE purchase_order = 3 ), sankeyRaw AS ( SELECT DISTINCT first_step.type as source , coalesce(second_step.type, 'Did Not Checkout') as target , first_step.ga_session_id , first_step.user_id FROM first_step LEFT JOIN second_step ON first_step.user_id = second_step.user_id AND first_step.ga_session_id = second_step.ga_session_id AND first_step.date <= second_step.date UNION SELECT DISTINCT second_step.type as source , coalesce(third_step.type, 'Did Not Purchase') as target , second_step.ga_session_id , second_step.user_id FROM second_step LEFT JOIN third_step ON second_step.user_id = third_step.user_id AND second_step.ga_session_id = third_step.ga_session_id AND second_step.date <= third_step.date ) SELECT source , target , count(*)::int as value FROM sankeyRaw GROUP BY 1, 2 30:[] 2d:{"title":"Merchandise Dashboard","cells":[{"id":0,"value":"# Merchandise Dashboard","pinned":false,"hidden":false,"mode":"md"},{"id":344,"value":"viewof filterDate = Scrubber(dates.map(d => d.date), {\n autoplay: false,\n loop: false,\n delay: 100,\n format: date => date.toLocaleString(\"en\", {month: \"long\", day: \"numeric\", year: \"numeric\"})\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":528,"value":"\n

${drawBAN(d3.format('$,')(totalRevenue), \"Total Revenue\")}

${drawBAN(d3.format(',')(totalCarts), \"Total Add to Carts\", typeColorScale('Add To Cart'))}

${drawBAN(d3.format(',')(totalPurchases), \"Total Purchases\", typeColorScale('Purchased'))}

${purchasesBarChart(filterDate)}

${purchasesBarChart(filterDate).legend(\"color\")}

${topTenCategories(100)}

${distOfPrices()}

","pinned":false,"hidden":false,"mode":"html"},{"id":587,"value":"--- \n\n### Results by Category","pinned":false,"hidden":false,"mode":"md"},{"id":368,"value":"viewof selectCategory = Inputs.select(categories.map(d => d.category), {label: \"Select one\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":397,"value":"

${purchaseSankey(sankey2)}

${topTenChart(200)}

","pinned":false,"hidden":false,"mode":"html"},{"id":1182,"value":"viewof table = Inputs.table(itemsDetails, {\n width: {\n Name: \"250px\",\n },\n format: {\n \"Item Price\": d => d3.format('$,')(d),\n \"Total Revenue\": d => d3.format('$,')(d),\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":427,"value":"---\n\n### Dashboard helpers","pinned":false,"hidden":false,"mode":"md"},{"id":620,"value":"typeColorScale = d3.scaleOrdinal(d3.schemeTableau10)\n .domain(['Add To Cart', 'Begin Checkout', 'Purchased'])","pinned":false,"hidden":false,"mode":"ojs"},{"id":602,"value":"commaFormat = d3.format(',')","pinned":false,"hidden":false,"mode":"ojs"},{"id":592,"value":"totalPurchases = d3.sum(eventsByDay.filter(d => d.type == 'Purchased' && d.date <= filterDate), d => d.num_events)","pinned":false,"hidden":false,"mode":"ojs"},{"id":597,"value":"totalCheckouts = d3.sum(eventsByDay.filter(d => d.type == \"Begin Checkout\" && d.date <= filterDate), d => d.num_events)","pinned":false,"hidden":false,"mode":"ojs"},{"id":599,"value":"totalCarts = d3.sum(eventsByDay.filter(d => d.type == 'Add To Cart' && d.date <= filterDate), d => d.num_events)","pinned":false,"hidden":false,"mode":"ojs"},{"id":886,"value":"totalRevenue = d3.sum(revenueByDay.filter(d => d.date <= filterDate), d => d.total_revenue) ","pinned":false,"hidden":false,"mode":"ojs"},{"id":926,"value":"filteredItemsByType = d3.rollups(allItemsByType.filter(d => d.date <= filterDate), v => d3.sum(v, d => d.total_revenue), d => d.category, d => d.name).flatMap(d => d[1].map(e => ({category: d[0], name: e[0], total_revenue: e[1]})))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1089,"value":"filteredItemsByCategory = d3.rollups(itemsByCategory.filter(d => d.date <= filterDate), v => d3.sum(v, d => d.num_events), d => d.category, d => d.type).flatMap(d => d[1].map(e => ({category: d[0], type: e[0], num_events: e[1]})))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1173,"value":"filteredItemsRevenueHist = itemsRevenueHist.filter(d => d.date <= filterDate)","pinned":false,"hidden":false,"mode":"ojs"},{"id":415,"value":"dashboardWidth = (window.innerWidth * .85) / 2","pinned":false,"hidden":false,"mode":"ojs"},{"id":419,"value":"dashboardHeight = 250","pinned":false,"hidden":false,"mode":"ojs"},{"id":1237,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":1239,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":1240,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":1242,"value":"","pinned":false,"hidden":false,"mode":"md"},{"id":744,"value":"---\n### Visualization Helpers","pinned":false,"hidden":false,"mode":"md"},{"id":387,"value":"purchasesBarChart = (filterDate) => Plot.plot({\n marks: [\n Plot.rectY(eventsByDay, Plot.binX({y: 'sum'}, {\n x: \"date\",\n y: \"num_events\",\n fill: \"type\",\n fillOpacity: d => d[0].date <= filterDate ? 1 : .25 ,\n interval: d3.utcDay,\n })),\n Plot.ruleY([0]),\n Plot.ruleX([filterDate])\n ],\n color: {\n legend: false,\n domain: typeColorScale.domain(),\n range: typeColorScale.range()\n },\n marginLeft: 80,\n width: window.innerWidth,\n height: dashboardHeight\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":298,"value":"purchaseSankey = (data) => SankeyChart({\n links: data\n}, {\n nodeGroup: d => d.id,\n nodeAlign: 'left',\n width: dashboardWidth,\n height: dashboardHeight,\n colors: [\"#4e79a7\", \"#f28e2c\",\"#ccc\", \"#e15759\", \"darkgray\", \"#76b7b2\"]\n}) ","pinned":false,"hidden":false,"mode":"ojs"},{"id":817,"value":"drawBAN = (text, subText, fill = \"#000\") => Plot.plot({\n width: dashboardWidth,\n height: 175,\n marks: [\n Plot.text([text], {frameAnchor: \"middle\", fontSize: 80, fill}),\n Plot.text([subText], {frameAnchor: \"middle\", fontSize: 30, dy: 60, fill})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":509,"value":"topTenChart = (marginLeft) => Plot.plot({\n y: {\n label: ''\n },\n x: {\n label: '# of Events'\n },\n marks: [\n Plot.barX(itemsByType, {x: \"num_events\", y: 'name', fill: 'type', sort: {y: \"x\", reverse: true, limit: 10}}),\n Plot.ruleX([0])\n ],\n marginLeft: marginLeft,\n marginRight: 50,\n color: {\n legend: true,\n domain: typeColorScale.domain(),\n range: typeColorScale.range()\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":961,"value":"topTenCategories = (marginLeft) => Plot.plot({\n y: {\n label: ''\n },\n x: {\n label: ''\n },\n marks: [\n Plot.barX(filteredItemsByCategory, {x: \"num_events\", y: 'category', order: 'appearance', fill: 'type', sort: {y: \"x\", reverse: true, limit: 10}}),\n Plot.ruleX([0])\n ],\n marginLeft: marginLeft,\n marginRight: 100,\n height: dashboardHeight,\n width: dashboardWidth,\n color: {\n domain: typeColorScale.domain(),\n range: typeColorScale.range()\n },\n caption: \"Top ten categories on Google Merchadise Store by # of Events\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1152,"value":"distOfPrices = () => Plot.plot({\n x: {\n label: 'Price',\n tickFormat: d3.format('$')\n },\n y: {\n label: '# of Purchases'\n },\n marks: [\n Plot.rectY(filteredItemsRevenueHist, Plot.binX({y: \"count\"}, {x: \"revenue\"})),\n Plot.ruleY([0])\n ],\n height: dashboardHeight,\n width: dashboardWidth,\n caption: 'Distribution of items purchased by item price'\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1127,"value":"relationshipScatter = () =>\n Plot.plot({\n height: dashboardHeight,\n width: dashboardWidth,\n marks: [\n Plot.dot(\n itemsByCategoryScatter.filter((d) => d.date <= filterDate),\n Plot.bin(\n { x: \"sum\", y: \"sum\", r: \"sum\" },\n { x: \"num_purchases\", y: \"num_carts\" }\n )\n )\n ]\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":754,"value":"---\n### Data","pinned":false,"hidden":false,"mode":"md"},{"id":60,"value":"import { db } from \"@observablehq/google-merchandise-sales-data\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":363,"value":"SELECT DISTINCT category FROM items ORDER BY 1","pinned":true,"hidden":false,"mode":"sql","output":"categories","database":"var:db"},{"id":378,"value":"SELECT date_trunc('day', events.date) as date\n , case when type = 'add_to_cart' then 'Add To Cart'\n when type = 'begin_checkout' then 'Begin Checkout'\n when type = 'purchase' then 'Purchased' end as type\n , count(*)::int as num_events\n FROM events\n JOIN items \n ON events.item_id = items.id\n GROUP BY 1, 2\n ORDER BY 1 ASC, 2","pinned":true,"hidden":false,"mode":"sql","output":"eventsByDay","database":"var:db"},{"id":850,"value":"SELECT date_trunc('day', events.date) as date\n , sum(items.price_in_usd)::int as total_revenue\n FROM events\n JOIN items \n ON events.item_id = items.id\n AND type = 'purchase'\n GROUP BY 1","pinned":true,"hidden":false,"mode":"sql","output":"revenueByDay","database":"var:db"},{"id":554,"value":"SELECT date_trunc('day', events.date) as date\n , sum(case when type = 'purchase' then 1 else 0 end)::int as num_purchases\n , sum(case when type = 'add_to_cart' then 1 else 0 end)::int as num_carts\n , sum(case when type = 'begin_checkout' then 1 else 0 end)::int as num_checkouts\n FROM events\n JOIN items \n ON events.item_id = items.id\n GROUP BY 1\n ORDER BY 1","pinned":true,"hidden":false,"mode":"sql","output":"allEventsByDay","database":"var:db"},{"id":505,"value":"SELECT items.name\n , case when type = 'add_to_cart' then 'Add To Cart'\n when type = 'begin_checkout' then 'Begin Checkout'\n when type = 'purchase' then 'Purchased' end as type\n , count(*)::int as num_events\n FROM events\n JOIN items \n ON events.item_id = items.id\n WHERE items.category = ${selectCategory}\n GROUP BY 1, 2\n ORDER BY 1 ASC, 2","pinned":true,"hidden":false,"mode":"sql","output":"itemsByType","database":"var:db"},{"id":1184,"value":"SELECT items.name as \"Name\"\n , items.price_in_usd as \"Item Price\"\n , sum(case when type = 'purchase' then items.price_in_usd else 0 end)::int as \"Total Revenue\"\n , sum(case when type = 'add_to_cart' then 1 else 0 end)::int as \"# of Add to Carts\"\n , sum(case when type = 'begin_checkout' then 1 else 0 end)::int as \"# of Checkouts\"\n , sum(case when type = 'purchase' then 1 else 0 end)::int as \"# of Purchases\"\n FROM events\n JOIN items \n ON events.item_id = items.id\n JOIN users\n ON events.user_id = users.id\n WHERE items.category = ${selectCategory}\n GROUP BY 1, 2\n ORDER BY 1 ASC, 2","pinned":true,"hidden":false,"mode":"sql","output":"itemsDetails","database":"var:db"},{"id":965,"value":"SELECT category\n , date_trunc('day', events.date) as date\n , case when type = 'add_to_cart' then 'Add To Cart'\n when type = 'begin_checkout' then 'Begin Checkout'\n when type = 'purchase' then 'Purchased' end as type\n , count(*)::int as num_events\n FROM events\n JOIN items \n ON events.item_id = items.id\n GROUP BY 1, 2, 3\n ORDER BY 1 ASC, 2","pinned":false,"hidden":false,"mode":"sql","output":"itemsByCategory","database":"var:db"},{"id":1101,"value":"SELECT category\n , date_trunc('day', events.date) as date\n , sum(case when type = 'purchase' then 1 else 0 end)::int as num_purchases\n , sum(case when type = 'add_to_cart' then 1 else 0 end)::int as num_carts\n , sum(case when type = 'begin_checkout' then 1 else 0 end)::int as num_checkouts\n FROM events\n JOIN items \n ON events.item_id = items.id\n GROUP BY 1, 2\n ORDER BY 1 ASC, 2","pinned":true,"hidden":false,"mode":"sql","output":"itemsByCategoryScatter","database":"var:db"},{"id":1154,"value":"SELECT category\n , items.name\n , date_trunc('day', events.date) as date\n , items.price_in_usd::int as revenue\n FROM events\n JOIN items \n ON events.item_id = items.id\n WHERE type = 'purchase'","pinned":true,"hidden":false,"mode":"sql","output":"itemsRevenueHist","database":"var:db"},{"id":919,"value":"SELECT items.category\n , items.name\n , date_trunc('day', events.date) as date\n , sum(items.price_in_usd)::int as total_revenue\n FROM events\n JOIN items \n ON events.item_id = items.id\n WHERE type = 'purchase'\n GROUP BY 1, 2, 3\n ORDER BY 1 ASC, 2","pinned":true,"hidden":false,"mode":"sql","output":"allItemsByType","database":"var:db"},{"id":339,"value":"SELECT DISTINCT date_trunc('day', date) as date FROM events ORDER BY 1 ASC","pinned":true,"hidden":false,"mode":"sql","output":"dates","database":"var:db"},{"id":228,"value":"$2f","pinned":true,"hidden":false,"mode":"sql","output":"sankey2","database":"var:db"},{"id":750,"value":"---\n### Imported Modules","pinned":false,"hidden":false,"mode":"md"},{"id":329,"value":"import {Scrubber} from \"@mbostock/scrubber\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":567,"value":"import {Treemap} from \"@d3/treemap\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":719,"value":"import {SankeyChart} from \"@d3/sankey\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":828,"value":"import {tooltipPlugin} from \"@mkfreeman/plot-tooltip\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":835,"value":"tooltipPlugin(Plot)","pinned":false,"hidden":false,"mode":"ojs"},{"id":910,"value":"import {Pack} from \"@d3/pack\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":933,"value":"import {ForceGraph} from \"@d3/force-directed-graph\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":1007,"value":"import {Icicle} from \"@d3/icicle\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":1027,"value":"import {Sunburst} from \"@d3/sunburst\"","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W30","stdlib":"1","titling":"auto"}