31:T447,notebooks = [ { title: "Which items are purchased together?", path: "@observablehq/which-items-are-purchased-together", thumbnail: "b39e834adfbe703df48916786d9a9bfef04dc0100c3b684a44db43878c28b266" }, { title: "Part 1: Co-occurrence SQL query", path: "@observablehq/arsc-part-1-co-occurrence-sql-query", thumbnail: "5fb57905caca18de4511d89f9ec6b3dfb8dfa6c6b38f62d2798e7cb7e9b4c4e2" }, { title: "Part 2: Interactive filtering", path: "@observablehq/arsc-part-2-interactive-filtering", thumbnail: "7bb7f01497112ba5c08237ffeea20ccc87dbbc03b646afc9ffb32a3e38c024ef" }, { title: "Part 3: Matrix visualization", path: "@observablehq/arsc-part-3-matrix-visualization", thumbnail: "6788484f6814389c2ccdab1a2a00f0ddffdb3f88bc5e870a5d6a3194631a2c61" }, { title: "Part 4: Force layout visualization", path: "@observablehq/arsc-part-4-force-layout-visualization", thumbnail: "915226635db14cbb92290d78ef53a37c834437861a1e124cbfeb1d6385d8e11b" }, ].map(d => ({...d, collection: collection.path}))32:T401,function preview({path, title, author, thumbnail, collection}, {target = "_blank", selected = false} = {}) { return htl.html` event.currentTarget.firstElementChild.style.borderColor = "#1b1e23"} onmouseout=${event => !selected ? event.currentTarget.firstElementChild.style.borderColor = "#e8e8e8" : ""}>

`; }33:T50e,navigation = (notebookOrder, collection, currIndex) => { const previous = {...notebookOrder[currIndex - 1], order:"previous"}; const next = {...notebookOrder[currIndex + 1], order:"next"} const pages = currIndex === 0 ? [next] : currIndex === notebookOrder.length - 1 ? [previous] : [previous, next] return htl.html`

More in the ${collection.title} collection:

${pages.map((d, i) => { return htl.html` ${d.order}
${d.title} `; })}

`; }34:[] 2f:{"title":"Which items are purchased together?","cells":[{"id":0,"value":"# Which items are purchased together?\n## Build an advanced report with simple components\nThe goal of this tutorial series is to build an interactive dashboard that lets you explore which items are purchased together from an online store. We cover SQL queries, user input and dynamic visualization using simple components that combine into a powerful dashboard. We've set it up using [Google Merchandise Sales Data](https://observablehq.com/@observablehq/ecommerce-analytics-demo-data) but you can easily fork this notebook and [connect your own database](https://observablehq.com/@observablehq/databases). \n\nThis notebook demonstrates two [co-occurrence](https://en.wikipedia.org/wiki/Co-occurrence) reports: a simple dashboard and an advanced dashboard with visualizations. Try them both out below, and then follow our \n[4 part tutorial series](https://observablehq.com/collection/@observablehq/which-items-are-purchased-together) to see how we build them piece by piece.","pinned":false,"hidden":false,"mode":"md"},{"id":1011,"value":"---\n## A simple dashboard\nTo start with, we can zoom in on a particular item and simply count the number of items that are purchased together with it. Try searching to narrow down the selections, and choose an item from the first table below:","pinned":false,"hidden":false,"mode":"md"},{"id":1016,"value":"viewof searched2 = Inputs.search(items, { placeholder: \"Search items\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1034,"value":"viewof search2 = Inputs.table(searched2, { height: 140, multiple:false, value: searched2[0] })","pinned":true,"hidden":false,"mode":"ojs"},{"id":1047,"value":"Items most frequently purchased with **${search2?.name}**:","pinned":false,"hidden":false,"mode":"md"},{"id":1056,"value":"Inputs.table(data2.links.map(d => {\n return {\n item: d.item_a === search2.name ? d.item_b : d.item_a,\n count: d.count,\n \"percent of total\": Math.floor(100*d.count/search2.count) + \"%\"\n }\n}))","pinned":true,"hidden":false,"mode":"ojs"},{"id":1073,"value":"---\n## Advanced dashboard\nWhile the simple dashboard can be an efficient and powerful way to answer the question, it requires a known starting point. You already have to be curious about a particular item to find out more about it. \n\nOften we want to get a more global understanding of our data, this dashboard uses visualizations to enable the exploration of items purchased together in a given time frame. These techniques are referred to as co-occurrence analysis. \n\nBy searching and selecting subsets of items one can gain insight into purchasing patterns around different types of items. For example Try searching for terms like **campus**, **journal** or **pen**:","pinned":false,"hidden":false,"mode":"md"},{"id":275,"value":"viewof searched = Inputs.search(items, { placeholder: \"Search items\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":903,"value":"viewof startDate = Inputs.date({label: \"Start Date\", value: \"2020-11-01\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":906,"value":"viewof endDate = Inputs.date({label: \"End Date\", value: d3.utcDay.offset(startDate, 30) })","pinned":false,"hidden":false,"mode":"ojs"},{"id":924,"value":"_(demo data goes from 11/01/2020 to 01/31/2021)_ ","pinned":false,"hidden":false,"mode":"md"},{"id":692,"value":"viewof search = Inputs.table(searched, { height: 140 })","pinned":false,"hidden":false,"mode":"ojs"},{"id":699,"value":"Showing _${data.links.length} item pairs purchased \nbetween ${dateFormat(startDate)} and ${dateFormat(endDate)} \nwith count > 5_","pinned":false,"hidden":false,"mode":"md"},{"id":282,"value":"CustomForceGraph(data, {\n nodeId: d => d.name,\n // nodeGroup: d => d.category,\n nodeGroup: d => !!search.find(s => s.name === d.name),\n nodeTitle: d => `${d.name}`,\n linkSource: d => d.item_a,\n linkTarget: d => d.item_b,\n linkStrokeWidth: l => Math.sqrt(l.count),\n width,\n nodeStrength: -5,\n // linkStrength: 2,\n invalidation // a promise to stop the simulation when the cell is re-run\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":279,"value":"

\n ${Matrix(data.links, { width: subwidth })}\n

\n ${Inputs.table(data.links, { width: subwidth})}\n

","pinned":false,"hidden":false,"mode":"html"},{"id":1241,"value":"## Tutorial series\nTo see how we built these dashboards up, check out the rest of the tutorial series:","pinned":false,"hidden":false,"mode":"md"},{"id":1193,"value":"previews(notebooks, notebooks[0])","pinned":false,"hidden":false,"mode":"ojs"},{"id":6,"value":"---\n## The Data","pinned":false,"hidden":false,"mode":"md"},{"id":954,"value":"The database used in this report is imported from [Google Merchandise Sales Data](https://observablehq.com/@observablehq/ecommerce-analytics-demo-data) but you can easily fork this notebook and [connect your own database](https://observablehq.com/@observablehq/databases). Then you would edit the SQL queries below to reflect the contents of your data.\n\nTo better understand the two queries powering this report, see the first tutorial \n[Part 1: Co-occurrence SQL query](https://observablehq.com/@observablehq/arsc-part-1-co-occurrence-sql-query).","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"import { db } from \"@observablehq/google-merchandise-sales-data\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":27,"value":"with purchased_items as (\n SELECT * FROM events\n JOIN items ON events.item_id = items.id\n WHERE type = 'purchase'\n AND date > ${startDate}\n AND date < ${endDate}\n)\n\nSELECT \n a.name as item_a,\n b.name as item_b,\n count(*)::INT as count\nFROM purchased_items a \nJOIN purchased_items b ON a.ga_session_id = b.ga_session_id\nWHERE a.name < b.name\nGROUP BY a.name, b.name\n HAVING count > 5\nORDER BY count DESC","pinned":true,"hidden":false,"mode":"sql","output":"links","database":"var:db"},{"id":40,"value":"with purchased_items as (\n SELECT * FROM events\n JOIN items ON events.item_id = items.id\n WHERE type = 'purchase'\n)\n \nselect \n name, \n count(*)::INT as count,\n sum(price_in_usd)::INT as revenue,\n FIRST(category) as category\nfrom purchased_items\ngroup by name, category\norder by count desc","pinned":true,"hidden":false,"mode":"sql","output":"items","database":"var:db"},{"id":901,"value":"To better understand the interactive user interface elements like the search and the date pickers, see the second tutorial [Part 2: Interactive filtering](https://observablehq.com/@observablehq/arsc-part-2-interactive-filtering).","pinned":false,"hidden":false,"mode":"md"},{"id":1028,"value":"data = getFiltered(search, items, links)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1032,"value":"data2 = getFiltered([search2], items, links)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1024,"value":"function getFiltered(search, items, links) {\n const searchedLinks = links.filter(l => search.find(d => d?.name === l.item_a || d?.name === l.item_b))\n const searchedItems = items.filter(d => searchedLinks.find(l => d.name === l.item_a || d.name === l.item_b))\n return {\n nodes: searchedItems,\n links: searchedLinks\n }\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":911,"value":"dateFormat = d3.utcFormat(\"%Y-%m-%d\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":650,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":960,"value":"The subwidth here is used to limit the size of the Matrix and table display in the report above. It allows us to place them side-by-side unless they get too small (like on mobile) and then they will stack instead.","pinned":false,"hidden":false,"mode":"md"},{"id":673,"value":"subwidth = width/2 - 10 < 460 ? width : width/2 - 10","pinned":false,"hidden":false,"mode":"ojs"},{"id":894,"value":"To better understand the matrix visualization in the report, see tutorial \n[Part 3: Matrix visualization](https://observablehq.com/@observablehq/arsc-part-3-matrix-visualization). ","pinned":false,"hidden":false,"mode":"md"},{"id":861,"value":"import { Matrix } from \"@observablehq/arsc-part-3-matrix-visualization\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":941,"value":"To better understand the force layout visualization in the report, see tutorial \n[Part 4: Force layout visualization](https://observablehq.com/@observablehq/arsc-part-3-matrix-visualization). ","pinned":false,"hidden":false,"mode":"md"},{"id":808,"value":"import { CustomForceGraph } from \"@observablehq/arsc-part-4-force-layout-visualization\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":963,"value":"import { Plot } from \"@mkfreeman/plot-tooltip\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1159,"value":"---\n## Tutorial navigation","pinned":false,"hidden":false,"mode":"md"},{"id":1168,"value":"$31","pinned":true,"hidden":false,"mode":"ojs"},{"id":1181,"value":"collection = ({ title: \"Co-occurrence dashboard tutorial\", path: \"@observablehq/which-items-are-purchased-together\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1161,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":1163,"value":"function previews(notebooks, selected, options) {\n return htl.html`

${notebooks.map(notebook => preview(notebook, {...options, selected: notebook == selected}))}

`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1165,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W34","stdlib":"1","titling":"auto"}