2b:null 2c:["$","title",null,{"children":"Moneo Report ver2 - Coca Cola | leoutskot | Observable"}] 31:T4c9,purchasesData = { let result = [] let start = new Date(startDate.valueOf()) do { let count = {} commerces.forEach(c => { count[c] = { count: 0, cbs: [] } }) reportData.forEach(r => { if (gender !== "All") { if (gender !== r['Gender']) { return } } // Filtrar por comercios if (commerces.indexOf(r['Commerce Name']) === -1) { return } if (!(r['Purchased At Date'] != null && r['Product Count'] != null)) { return } let d = r['Purchased At Date'] // Datos corresponden a la fecha start if (d.getDate() === start.getDate() && d.getMonth() === start.getMonth() && d.getFullYear() == start.getFullYear()) { if (count[r['Commerce Name']]['cbs'].indexOf(r['Cashback ID']) === -1) { count[r['Commerce Name']]['cbs'].push(r['Cashback ID']) count[r['Commerce Name']]['count']++ } } }) commerces.forEach(c => { result.push({ "date": new Date(start.valueOf()), "count": count[c]['count'], "commerce": c, }) }) start.setDate(start.getDate() + 1) } while (start <= endDate) return result }32:T4c2,mapData = { let count = {} reportData.forEach(r => { // Filtrar por género if (gender !== "All") { if (gender !== r['Gender']) { return } } // Filtrar por comercios if (commerces.indexOf(r['Commerce Name']) === -1) { return } if (r['Purchased At Date'] == null) { return } // Filtrar datos por rango de fechas if (!(r['Purchased At Date'] >= startDate && r['Purchased At Date'] <= endDate)) { return } if (r['Store Name'] == null || r['Store Lat'] == null || r['Store Long'] == null) { return } if (!count.hasOwnProperty(`${r['Commerce Name']}-${r['Store Name']}`)) { count[`${r['Commerce Name']}-${r['Store Name']}`] = { name: `${r['Commerce Name']} - ${r['Store Name']}`, cbCount: 0, lat: r['Store Lat'], lng: r['Store Long'], cbs: [], } } if (count[`${r['Commerce Name']}-${r['Store Name']}`]['cbs'].indexOf(r['Cashback ID']) === -1) { count[`${r['Commerce Name']}-${r['Store Name']}`]['cbs'].push(r['Cashback ID']) count[`${r['Commerce Name']}-${r['Store Name']}`]['cbCount']++ } }) return Object.keys(count).map(k => count[k]) }33:[{"id":"8f1de66a5d2a27003bfaa3f588ab8b9dbb9cc6a50629a8eeb7259a95cab19b6dab4cbf48176c7eb8b754b5283cf1278dac36fb74238b824f3492c6310d90090b","href":"https://static.observableusercontent.com/files/8f1de66a5d2a27003bfaa3f588ab8b9dbb9cc6a50629a8eeb7259a95cab19b6dab4cbf48176c7eb8b754b5283cf1278dac36fb74238b824f3492c6310d90090b","name":"coca-cleaned.csv","type":"text/csv","lastModified":1645210543871.971,"size":995533}] 2f:{"id":"07a75800f91be5e7","slug":null,"title":"Moneo Report ver2 - Coca Cola","thumbnail":"eabd2971cfc1b0df0f820c1cae311c72595ba7fff9577b9f22c6b9f799dd7efa","access_level":"public","create_time":"$D2022-02-18T18:55:43.871Z","update_time":"$D2022-02-18T21:53:21.720Z","view_time":null,"first_public_time":"$D2023-02-08T16:00:06.590Z","has_unpublished_edits":null,"fork":true,"stars":0,"starred":false,"owner":{"type":"team","id":"ffde04ddc3661a67","login":"leoutskot","name":"","avatar_url":"https://avatars-next.observableusercontent.com/e06cb3e7721cf308519ea7dc5f656183aeb1ef76f48f9eae36a1d4d65982ceb2","tier":"starter_2024"},"version":348,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"dcdd2e87e2398a5b","login":"leoutskot","name":"","avatar_url":"https://avatars-next.observableusercontent.com/e06cb3e7721cf308519ea7dc5f656183aeb1ef76f48f9eae36a1d4d65982ceb2","tier":null}],"members":[{"type":"individual","id":"dcdd2e87e2398a5b","login":"leoutskot","name":"","avatar_url":"https://avatars-next.observableusercontent.com/e06cb3e7721cf308519ea7dc5f656183aeb1ef76f48f9eae36a1d4d65982ceb2","tier":null,"role":"editor"},{"type":"individual","id":"efc1fde016f2a19f","login":"segimanzanares","name":"Segi Manzanares","avatar_url":"https://avatars-next.observableusercontent.com/ecbb428a294310dcc49f10a83c126f63d4ed2f692420e7c5bbb7f53092182460","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Moneo Report ver2 - Coca Cola","cells":[{"id":0,"value":"# Moneo Report ver2 - Coca Cola","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":190,"value":"## Summary\nOn Dec 16th 2021 we added a specific Coca Cola offer for bottles of 2L with a huge 20% cashback offer in **Walmart**, **7-eleven** and **Soriana**.\n\nThis was later modified to include any Coca Cola product and then on Jan 7th 2022 the offer was reduced to 10%.\n\nSince Coca Cola is a very popular product this gives us the chance to see how these variations of the offer changed consumer behaviour.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":154,"value":"## Dataset\n\nThe initial dataset includes 6,892 cashbacks from 2006 (this is garbage of course) to Feb 15th 2022. If a cashback includes several Coca Cola items it is registered in the dataset as two or more rows in order to properly count the number of product items.\n\nWe'll do some cleaning:\n* Get rid of rejected tickets (1878)\n* Get rid of the ones that haven't been validated yet (7)\n* Get rid of a few (8) that have dates totally our range.\n* Quickly look at the product description and get rid of some items that might not be Coca Cola including \"Rollo de **coca**da San Martín\". This was about 20 cashbacks only.\n\n### This left a solid dataset of 4,964 items representing 4,721 unique cashbacks.\n\n* 2205 have status Accepted \n* 2759 have status Transferred\n* Consider that these numbers represents 243 less *unique* cashbacks.\n\n*Shoutout to Google Spreadsheet for having the COUNTUNIQUE formula..unlike Apple Numbers*\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":222,"value":"## Demographics\nThere are 1,334 unique people in the dataset. So on average each person has 3.5 cashbacks where they buy Coca Cola.\n\n---\n\nWe know the gender of about 44% of the people, only 3 of them would \"Rather not say\". \n\n### The dataset has a **54% female** and **46% male** representation.\n\n#### This is about 3% more female compared to the entire Moneo dataset.\n\n---\n\nWe know the age of about 37% of the people. \n\n### The average age is 40.\n\n---\n\nInteresting side-notes:\n* 5 of the females said their age is 0, none of the males did the same.\n* 17 of the males said their age is 5, none of the females did the same.\n* The youngest females are 20 and the youngest male is 21.\n* The oldest female is 66 and the oldest male is 67.\n* About 80% more men preferred to not register their age compared to women. We expected the opposite since \"you're not supposed to ask a women's age\".\n* Only 7 people registered their age, but not their gender, but 76 did the opposite. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":241,"value":"---\n## Looking for increased purchases of Coca Cola after the offer was published\nWe obviously want to see if the offer increased purchases of Coca Cola or not.\n\nWe tried marking the purchases with ticket, but that doesn't really work since we're dealing with almost 5000 items. There's going to be at least one tick every day.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"Plot.plot({\n x: {\n domain: [new Date(\"2021-03-01\"), new Date(\"2022-02-15\")],\n label: \"Purchase Date\"\n },\n y: {\n label: \"\",\n axis: \"right\"\n },\n marks: [\n Plot.tickX(reportData, {\n x: \"Purchased At Date\", \n y: \"Commerce Name\",\n stroke: \"Commerce Name\",\n strokeWidth: 0.5\n }),\n Plot.ruleX([new Date(\"2021-12-08\")], {\n stroke: \"green\",\n strokeWidth: 2\n }),\n ],\n height: 200,\n width: 660,\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":256,"value":"---\n\nInstead we'll use a line graph where the sum of Coca cola purchase is drawn, based on the Product Count in the ticket so not only individual tickets. Each color represents a store brand.\n\nThe graph has a few variables, since trends sometimes like to hide in the data. There's quite a bit of variation day by day so we've also added a **black** line which is the mean value and that can be adjusted too. A value of around 100 is good if the daterange is a full year, but it should be lowered when the range is shorter. Move the slider around until it smooths out a bit, but not too far.\n\nYou can also view the graph of all the stores or just the ones with the offer as well as filter by gender.\n\nNote that the Y axis has a logarithmic scale.\n\n---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":64,"value":"viewof startDate = Inputs.date({label: \"Start date\", value: \"2021-11-01\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":66,"value":"viewof endDate = Inputs.date({label: \"End date\", value: new Date()})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":195,"value":"viewof gender = Inputs.select([\"All\", \"Female\", \"Male\"], {label: \"Gender\", value: \"All\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":273,"value":"viewof wichStores = Inputs.radio(storesOptions, { label: \"Stores\", format: option => option.name, value: storesOptions[1] })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":309,"value":"viewof mean = Inputs.range([10, 200], { label: \"Mean\", value: 100, step: 1 })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":258,"value":"pplot.legend(\"color\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":68,"value":"pplot = Plot.plot({\n x: {\n label: \"Purchase Date\"\n },\n y: {\n label: \"CB Count\",\n type: \"log\",\n grid: true,\n },\n marks: [\n Plot.line(purchasesData, {\n x: \"date\", \n y: \"count\",\n stroke: \"commerce\"\n }),\n Plot.line(purchasesData, \n Plot.windowY({ reduce: \"mean\", k: mean }, { x: \"date\", y: \"count\", stroke: \"black\" })),\n Plot.ruleX([new Date(\"2021-12-07\")], {stroke: \"pink\"}),\n\n /*Plot.text(purchasesData, Plot.selectLast({\n x: \"date\",\n y: \"count\",\n z: \"commerce\",\n text: \"commerce\",\n textAnchor: \"start\",\n dx: 3\n }))*/\n ],\n style: \"overflow: visible;\",\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":316,"value":"## Map, because it's cool","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":290,"value":"cbMap = {\n let container = DOM.element('div', { style: `width:${width}px;height:${width/1.6}px` })\n yield container\n let map = L.map(container).setView([19.432608, -99.133208], 11)\n let osmLayer = L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {\n attribution: '© OpenStreetMap contributors'\n }).addTo(map)\n mapData.forEach(s => {\n let tooltipText = `\n Sucursal: ${s.name}
\n Ubicación: ${s.lat},${s.lng}
\n No. CBs: ${s.cbCount}
\n `;\n L.circle([s.lat, s.lng], {\n color: '#007bff',\n fillColor: '#007bff',\n fillOpacity: 0.5,\n radius: s.cbCount * 40\n })\n .addTo(map)\n .bindPopup(tooltipText);\n })\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":162,"value":"## Appendix with data and stuff","pinned":true,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3,"value":"rawData = FileAttachment(\"coca-cleaned.csv\").csv({typed:true})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":76,"value":"reportData = rawData.filter(r => ['Solicitado', 'Rechazado'].indexOf(r['Status']) === -1)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":25,"value":"import {PieChart} from \"@d3/pie-chart\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":27,"value":"genderData = {\n let maleCount = 0;\n let femaleCount = 0;\n reportData.forEach(r => {\n if (r['Gender'] === 'Male') {\n maleCount++\n }\n else if (r['Gender'] === 'Female') {\n femaleCount++\n }\n })\n return [\n { name: \"Male\", value: parseFloat((maleCount / (maleCount + femaleCount) * 100).toFixed(1)) },\n { name: \"Female\", value: parseFloat((femaleCount / (maleCount + femaleCount) * 100).toFixed(1)) },\n ]\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":277,"value":"storesOptions = [{ name: \"Show only stores with offer\", value: \"with_offer\" }, { name: \"Show all stores\", value: \"all\" }]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":321,"value":"commerces = {\n let result = []\n reportData.forEach(r => {\n if (result.indexOf(r['Commerce Name']) === -1) {\n result.push(r['Commerce Name'])\n }\n })\n result = result.filter(c => {\n if (wichStores.value === \"all\") {\n return [\"Walmart\", \"Walmart Express\", \"Soriana\", \"7-Eleven\", \"City Market\"].indexOf(c) !== -1\n }\n return [\"Walmart\", \"Soriana\", \"7-Eleven\"].indexOf(c) !== -1\n })\n return result\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":72,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":285,"value":"L = require('leaflet@1.2.0')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":287,"value":"html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":292,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1","titling":"auto"}} 30:"$2f:body"