2f:T460,wow_spend_plot = Plot.plot({ marginTop: 50, marginRight: 35, height: chart_height, width: chart_width, x: { nice: d3.utcMonth }, marks: [ //axes Plot.axisX({anchor: 'bottom', label: null, interval: 'month'}), Plot.axisY({tickFormat: '$s', label: 'Spend ↑'}), // data marks Plot.rectY(wow_date_filtered, { x: 'Week', y: 'spend', interval: d3.utcMonday, fill: appcast_color_palette[3], insetRight: barCart_inset, insetLeft: barCart_inset, opacity: opacity_range }), //Plot.gridY({stroke: 'white', strokeOpacity: 0.5}), Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'spend', dx: -22, dy: -28, fontSize: 11, textAnchor: 'start', text: d => `${d3.format('+.1%')(d.wow_spend_diff_perc)} WoW` })), Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'spend', dx: -7, dy: -15, fontSize: 11, textAnchor: 'start', text: d => `(${d3.format('$,.0f')(d.spend)})`, opacity: 0.5 })), Plot.ruleY([0]) ] })30:T4f9,wow_cpl_plot = Plot.plot({ marginTop: 50, insetRight: 45, height: chart_height, width: chart_width, x: { nice: d3.utcMonth }, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}), Plot.gridX({interval: 'quarter', opacity: 0.05}), Plot.gridY({opacity: 0.05}), Plot.axisX({anchor: 'bottom', label: null}), Plot.axisY({label: 'CPL ↑', tickFormat: '$.0f'}), // data marks Plot.line(wow_date_filtered, { x: 'Week', y: 'cpl', stroke: appcast_color_palette[2], strokeWidth: 1 }), Plot.dot(wow_date_filtered, { x: 'Week', y: 'cpl', fill: appcast_color_palette[2], r: 2, stroke: 'white' }), Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'cpl', dx: -10, dy: 15, fontSize: 11, textAnchor: 'start', text: d => `${d3.format('+.1%')(d.wow_cpl_diff_perc)} WoW` })), Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'cpl', dx: 0, dy: 25, fontSize: 11, opacity: 0.5, textAnchor: 'start', text: d => `\n (${d3.format('$.1f')(d.cpl)})` })), Plot.ruleY([0]) ] })31:T58d,wow_cpbgc_plot = Plot.plot({ marginTop: 50, insetRight: 45, height: chart_height, width: chart_width, x: { nice: d3.utcMonth }, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}), Plot.gridX({interval: 'quarter', opacity: 0.05}), Plot.gridY({opacity: 0.05}), Plot.axisX({anchor: 'bottom', label: null}), Plot.axisY({label: 'CPBGC ↑', tickFormat: '$.0f'}), // data marks Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'cpbgc', dx: -20, // move cpbgc (black) left/right dy: -30, // move cpbgc (black) up/down fontSize: 11, textAnchor: 'start', text: d => `${d3.format('+.1%')(d.wow_cpbgc_diff_perc)} WoW` })), Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'cpbgc', dx: -10, // move cpbgc (grey) left/right dy: -20, // move cpbgc (grey) up/down fontSize: 11, opacity: 0.5, textAnchor: 'start', text: d => `\n (${d3.format('$.1f')(d.cpbgc)})` })), Plot.line(wow_date_filtered, { x: 'Week', y: 'cpbgc', stroke: appcast_color_palette[2], strokeWidth: 1 }), Plot.dot(wow_date_filtered, { x: 'Week', y: 'cpbgc', fill: appcast_color_palette[2], r: 2, stroke: 'white' }), Plot.ruleY([0]) ] })32:T432,wow_ltobgc_plot = Plot.plot({ marginTop: 50, insetRight: 45, height: chart_height, width: chart_width, x: { nice: d3.utcMonth }, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}), Plot.gridX({interval: 'quarter', opacity: 0.05}), Plot.gridY({opacity: 0.05}), Plot.axisX({anchor: 'bottom', label: null}), Plot.axisY({label: 'Lead to BGC CR ↑', tickFormat: '.0%'}), // data marks Plot.line(wow_date_filtered, { x: 'Week', y: 'LtoBGC', stroke: appcast_color_palette[2], strokeWidth: 1 }), Plot.dot(wow_date_filtered, { x: 'Week', y: 'LtoBGC', fill: appcast_color_palette[2], stroke: 'white', title: d => `${d.LtoBGC}`, r: 2, }), Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'LtoBGC', dx: -10, dy: 15, fontSize: 11, textAnchor: 'start', text: d => `${d3.format('.1%')(d.LtoBGC)}` })), Plot.ruleY([0]) ] })33:T416,Plot.plot({ marginTop: 50, insetRight: 45, height: chart_height, width: chart_width, x: { nice: d3.utcMonth }, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}), Plot.gridX({interval: 'quarter', opacity: 0.05}), Plot.gridY({opacity: 0.05}), Plot.axisX({anchor: 'bottom', label: null}), Plot.axisY({label: 'Click to Lead CR ↑', tickFormat: '.0%'}), // data marks Plot.line(wow_date_filtered, { x: 'Week', y: 'CtoL', stroke: appcast_color_palette[2], strokeWidth: 1 }), Plot.dot(wow_date_filtered, { x: 'Week', y: 'CtoL', fill: appcast_color_palette[2], stroke: 'white', title: d => `${d.CtoL}`, r: 2, }), Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'CtoL', dx: 0, dy: 15, fontSize: 11, textAnchor: 'start', text: d => `${d3.format('.1%')(d.CtoL)}` })), Plot.ruleY([0]) ] })34:T416,wow_bgc_plot = Plot.plot({ //style: "overflow: visible;", marginTop: 50, marginRight: 35, height: chart_height, width: chart_width, x: { nice: d3.utcMonth }, marks: [ //axes Plot.axisX({anchor: 'bottom', ticks: d3.utcMonth, label: null}), Plot.axisY({tickFormat: '.0s', interval: 1000, label: 'BGC ↑'}), // data marks Plot.text(wow_date_filtered, Plot.selectLast({ x: 'Week', y: 'bgc', dx: bgc_text.x, dy: bgc_text.y, fontSize: 11, textAnchor: 'start', text: d => `${d3.format('+.1%')(d.wow_bgc_diff_perc)} WoW`, stroke: 'white', fill: 'black' //text: d => `${d3.utcFormat('%b %d')(d.Week)}\n \n ${d3.format('+.1%')(d.wow_bgc_diff_perc)} WoW` })), Plot.rectY(wow_date_filtered, Plot.bin({y: 'sum'}, { x: 'Week', y: 'bgc', interval: d3.utcMonday, fill: appcast_color_palette[3], insetRight: barCart_inset, insetLeft: barCart_inset, opacity: opacity_range })), Plot.ruleY([0]) ] })35:T4ea,Plot.plot({ marginTop: 50, //marginRight: 50, insetRight: 45, height: chart_height, width: chart_width, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}), Plot.gridX({interval: 'quarter', opacity: 0.05}), Plot.gridY({opacity: 0.05}), Plot.axisX({anchor: 'bottom', label: null}), Plot.axisY({label: 'CPL ↑', tickFormat: '$.0f'}), // data marks Plot.line(clean_monthly, { x: 'month', y: 'cpl', stroke: appcast_color_palette[2], strokeWidth: 1 }), Plot.dot(clean_monthly, { x: 'month', y: 'cpl', fill: appcast_color_palette[2], //fillOpacity: opacity_range, stroke: 'white' }), Plot.text(clean_monthly, Plot.selectLast({ x: 'month', y: 'cpl', dx: -10, dy: -15, fontSize: 11, textAnchor: 'start', text: d => `${d3.format('+.0%')(d.wow_cpl_diff_perc)} MoM` })), Plot.text(clean_monthly, Plot.selectLast({ x: 'month', y: 'cpl', dx: 0, dy: -5, fontSize: 11, opacity: 0.5, textAnchor: 'start', text: d => `\n (${d3.format('$.1f')(d.cpl)})` })), Plot.ruleY([0]) ] })36:T4e3,Plot.plot({ marginTop: 50, insetRight: 45, height: chart_height, width: chart_width, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}), Plot.gridX({interval: 'quarter', opacity: 0.05}), Plot.gridY({opacity: 0.05}), Plot.axisX({anchor: 'bottom', label: null}), Plot.axisY({label: 'CPBGC ↑', tickFormat: '$.0f'}), // data marks Plot.line(clean_monthly, { x: 'month', y: 'cpbgc', stroke: appcast_color_palette[2], strokeWidth: 1 }), Plot.dot(clean_monthly, { x: 'month', y: 'cpbgc', fill: appcast_color_palette[2], //fillOpacity: opacity_range, stroke: 'white' }), Plot.text(clean_monthly, Plot.selectLast({ x: 'month', y: 'cpbgc', dx: -10, dy: -15, fontSize: 11, textAnchor: 'start', text: d => `${d3.format('+.0%')(d.wow_cpbgc_diff_perc)} MoM` })), Plot.text(clean_monthly, Plot.selectLast({ x: 'month', y: 'cpbgc', dx: 0, dy: -7, fontSize: 11, opacity: 0.5, textAnchor: 'start', text: d => `\n (${d3.format('$.1f')(d.cpbgc)})` })), Plot.ruleY([0]) ] })37:T446,//"timeline viz #wip" timeline_plot = Plot.plot({ marginTop: 50, width, x: { nice: d3.utcMonth }, y: { axis: null }, color: { range: appcast_color_palette }, marks: [ //Plot.frame({strokeOpacity: 0.1}), Plot.axisX({interval: 'month'}), //Plot.gridX({interval: 'week', opacity: 0.05}), //Plot.ruleY([0], {strokeOpacity: .5}), Plot.dot(category_filtered_timeline, Plot.dodgeY({anchor: 'top'},{ x: 'Date', y: 0, fill: 'Category', r: 5, //padding: 20, title: d => `${d.Note}` })), Plot.arrow(category_filtered_timeline, { filter: d => d.Category == 'Budget', x2: 'Date', y2: 0, x1: 'Date', y1: -1, bend: true, inset: 15, opacity: 0.25, strokeWidth: 1 }), Plot.text(category_filtered_timeline, Plot.dodgeY({ filter: d => d.Category == 'Budget', x: 'Date', text: d => `${d.Note}`, lineWidth: 9, textAnchor: 'start', r: 75, // rotate: -45, // fill: 'Category', fontSize: 15 })) ] })38:T620,Plot.plot({ marginTop: 50, height: chart_height, width: chart_width, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}), Plot.gridX({interval: 'week', opacity: 0.05}), Plot.axisX({anchor: 'bottom'}), Plot.axisY({label: 'BGC ↑'}), // data marks Plot.areaY(clean.filter(aq.escape(d => d.date >= wow_date_start) ), { x: 'date', y: 'bgc', r: 1.5, fill: appcast_color_selector, opacity: opacity_range }), // 45 Plot.areaY(wow_date_filtered, { filter: (d) => d.date >= forty_five_days_ago_today, x: "date", y: "bgc", fill: "white", fillOpacity: 0.7 }), Plot.ruleX([forty_five_days_ago_today], { stroke: "lightgray", strokeWidth: 1, strokeDasharray: "4 2" } ), Plot.textX([forty_five_days_ago_today], { fill: 'currentColor', text: ['45 days ago'], rotate: 90, fontSize: 7, dx: 4, dy: -30, frameAnchor: 'bottom' }), // // 30 // Plot.areaY(wow_date_filtered, { // filter: (d) => d.date >= thirty_days_ago_today, // x: "date", // y: "bgc", // fill: "white", // fillOpacity: 0.8 // }), // Plot.ruleX([thirty_days_ago_today], // { stroke: "lightgray", strokeWidth: 1, strokeDasharray: "4 2" } // ), // Plot.textX([thirty_days_ago_today], // { fill: 'currentColor', text: ['30 days ago'], rotate: 90, fontSize: 7, dx: 4, dy: -30, frameAnchor: 'bottom' }), // axis rule Plot.ruleY([0]) ] })39:T407,Plot.plot({ marginTop: 50, height: chart_height, width: chart_width, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}), Plot.gridX({interval: 'week', opacity: 0.05}), Plot.axisX({anchor: 'bottom'}), Plot.axisY({label: 'CPA ↑', tickFormat: '$s'}), // data marks Plot.line(clean.filter(aq.escape(d => d.date >= wow_date_start) ), { x: 'date', y: 'cpA', stroke: appcast_color_selector, strokeWidth: 1 }), Plot.line(wow_date_filtered, { filter: (d) => d.date >= forty_five_days_ago_today, x: "date", y: "cpA", stroke: "white", strokeOpacity: 0.8 }), Plot.ruleX([forty_five_days_ago_today], { stroke: "lightgray", strokeWidth: 1, strokeDasharray: "4 2" } ), Plot.textX([forty_five_days_ago_today], { fill: 'currentColor', text: ['45 days ago'], rotate: 90, fontSize: 7, dx: 4, dy: -30, frameAnchor: 'bottom' }), Plot.ruleY([0]) ] })3a:T457,Plot.plot({ marginTop: 50, height: chart_height, width: chart_width, marks: [ //axes Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}), Plot.gridX({interval: 'week', opacity: 0.05}), Plot.axisX({anchor: 'bottom'}), Plot.axisY({label: 'BGC to Activation CR % ↑', tickFormat: '.0%'}), // data marks Plot.line(clean.filter(aq.escape(d => d.date >= wow_date_start) ), { x: 'date', y: 'BGCtoA', stroke: appcast_color_selector, strokeWidth: 1, //marker: true }), // shade Plot.line(wow_date_filtered, { filter: (d) => d.date >= forty_five_days_ago_today, x: "date", y: "BGCtoA", stroke: 'white', strokeWidth: 1, // marker: true }), Plot.ruleX([forty_five_days_ago_today], { stroke: "lightgray", strokeWidth: 1, strokeDasharray: "4 2" } ), Plot.textX([forty_five_days_ago_today], { fill: 'currentColor', text: ['45 days ago'], rotate: 90, fontSize: 7, dx: 4, dy: -30, frameAnchor: 'bottom' }), Plot.ruleY([0]) ] })3b:T584,viewof clean_weekly = aq.from(data) .derive({date: d => op.parse_date(d.date)}) .rename({'master_publisher_aggregated': 'publisher'}) //filters .filter(d => d.publisher != null) //.filter(d => d.publisher === 'Jobcase CPC') //cleanup .derive({ Week: aq.escape(d => d3.utcMonday(d.date)) }) //agregation by week .groupby('Week') .rollup({ clicks: d => op.sum(d.clicks), spend: d => op.sum(d.spend), leads: d => op.sum(d.leads), bgc: d => op.sum(d.bgc), Activations: d => op.sum(d.activations) }) .derive({ cpl: d => d.spend / d.leads, cpbgc: d => d.spend / d.bgc, cpA: d => d.spend / d.Activations, CtoL: d => d.leads / d.clicks, BGCtoA: d => d.Activations / d.bgc, LtoBGC: d => d.bgc / d.leads }) .derive({ wow_lead_diff: d => d.leads - op.lag(d.leads,1), wow_bgc_diff: d => d.bgc - op.lag(d.bgc,1), wow_cpl_diff: d => d.cpl - op.lag(d.cpl,1), wow_cpbgc_diff: d => d.cpbgc - op.lag(d.cpbgc,1), wow_spend_diff: d => d.spend - op.lag(d.spend,1), }) .derive({ wow_lead_diff_perc: d => d.wow_lead_diff / op.lag(d.leads,1), wow_bgc_diff_perc: d => d.wow_bgc_diff / op.lag(d.bgc,1), wow_cpl_diff_perc: d => d.wow_cpl_diff / op.lag(d.cpl,1), wow_cpbgc_diff_perc: d => d.wow_cpbgc_diff / op.lag(d.cpbgc,1), wow_spend_diff_perc: d => d.wow_spend_diff / op.lag(d.spend,1), }) .orderby('Week') .view()3c:T5b9,viewof clean_monthly = aq.from(data) .derive({date: d => op.parse_date(d.date)}) .rename({'master_publisher_aggregated': 'publisher'}) //filters .filter(d => d.publisher != null) //.filter(d => d.publisher === 'Jobcase CPC') //cleanup .derive({ month: aq.escape(d => d3.utcMonth(d.date)) }) .filter(aq.escape(d => d.month < mom_end_date)) //agregation by week .groupby('month') .rollup({ clicks: d => op.sum(d.clicks), spend: d => op.sum(d.spend), leads: d => op.sum(d.leads), bgc: d => op.sum(d.bgc), Activations: d => op.sum(d.activations) }) .derive({ cpl: d => d.spend / d.leads, cpbgc: d => d.spend / d.bgc, cpA: d => d.spend / d.Activations, CtoL: d => d.leads / d.clicks, BGCtoA: d => d.Activations / d.bgc, LtoBGC: d => d.bgc / d.leads }) .derive({ wow_lead_diff: d => d.leads - op.lag(d.leads,1), wow_bgc_diff: d => d.bgc - op.lag(d.bgc,1), wow_cpl_diff: d => d.cpl - op.lag(d.cpl,1), wow_cpbgc_diff: d => d.cpbgc - op.lag(d.cpbgc,1), wow_spend_diff: d => d.spend - op.lag(d.spend,1), }) .derive({ wow_lead_diff_perc: d => d.wow_lead_diff / op.lag(d.leads,1), wow_bgc_diff_perc: d => d.wow_bgc_diff / op.lag(d.bgc,1), wow_cpl_diff_perc: d => d.wow_cpl_diff / op.lag(d.cpl,1), wow_cpbgc_diff_perc: d => d.wow_cpbgc_diff / op.lag(d.cpbgc,1), wow_spend_diff_perc: d => d.wow_spend_diff / op.lag(d.spend,1), }) .orderby('month') .view()3d:[{"id":"6942d14b484a123f44ba5d26660c96904d59e2e82ab65e4b52c2b06397e227249abff797c7e1f4940fcf8eff01f1963e94f2001d51d837ed0bfb2992b40d83d2","href":"https://static.observableusercontent.com/files/6942d14b484a123f44ba5d26660c96904d59e2e82ab65e4b52c2b06397e227249abff797c7e1f4940fcf8eff01f1963e94f2001d51d837ed0bfb2992b40d83d2","name":"Lyft Repository (1).xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1683558602578.132,"size":3243685},{"id":"2052ba4ded24c3488806de203f10dfff6de7e02c8f3fb34df7988f173d7da4b3b7ad28fce4dafcd46628224c27c3727243d19dd39eaa99281d6d82d35ab51f99","href":"https://static.observableusercontent.com/files/2052ba4ded24c3488806de203f10dfff6de7e02c8f3fb34df7988f173d7da4b3b7ad28fce4dafcd46628224c27c3727243d19dd39eaa99281d6d82d35ab51f99","name":"Lyft Repository (4).xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1684765551159.827,"size":3258988},{"id":"c38e3b721f06c7b1420508a04d356827375551327e212479c2c96d6b74be828e446e6bc9bdbbee6155031a55c5751a477189822a15ee698d1e9afa7aabef9567","href":"https://static.observableusercontent.com/files/c38e3b721f06c7b1420508a04d356827375551327e212479c2c96d6b74be828e446e6bc9bdbbee6155031a55c5751a477189822a15ee698d1e9afa7aabef9567","name":"Lyft Repository (5).xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1692022924433.631,"size":3355321}] 2d:{"title":"Lyft Weekly Report","cells":[{"id":0,"value":"md `# Lyft Weekly Report`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1986,"value":"toc({\n headers: \"h1\",\n skip: ['Lyft Weekly Report', 'Parameters', 'View and Explore', 'Timeline']\n //title: \"My ToC\",\n //hideStartingFrom: \"Implementation\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4113,"value":"html `
`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4108,"value":"md `## Load Data Here 👇🏽`","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"viewof file = Inputs.file({label: \"Load file 'Lyft Performance.csv'\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2311,"value":"md `

\n# WoW`","pinned":false,"hidden":false,"mode":"ojs"},{"id":5872,"value":"md `*These dates below control the WoW charts. The report should show YTD for 2023*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2090,"value":"viewof wow_date_start = Inputs.date({label: \"WoW Date Start\", value: \"2023-01-01\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2203,"value":"viewof wow_date_end = Inputs.date({label: \"WoW Date End\", value: d3.utcMonday(new Date())})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2193,"value":"wow_leads_plot = Plot.plot({\n marginTop: 50,\n marginRight: 35,\n height: chart_height,\n width: chart_width,\n x: {\n nice: d3.utcMonth\n },\n marks: [\n //axes\n Plot.axisX({anchor: 'bottom', label: null, interval: 'month'}),\n Plot.axisY({tickFormat: 's', label: 'Leads ↑'}),\n \n // data marks\n Plot.text(wow_date_filtered, Plot.selectLast({\n x: 'Week',\n y: 'leads',\n dx: leads_text.x, // move text left/right\n dy: leads_text.y, // move text up/down\n fontSize: 11,\n textAnchor: 'start',\n text: d => `${d3.format('+.0%')(d.wow_lead_diff_perc)} WoW`\n })),\n Plot.rectY(wow_date_filtered, { //.filter(aq.escape( d => d.Week >= date) ), {\n x: 'Week',\n y: 'leads',\n interval: d3.utcMonday,\n fill: appcast_color_palette[3],\n insetLeft: barCart_inset,\n insetRight: barCart_inset,\n opacity: opacity_range,\n tip: false\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":5915,"value":"md `*Use the sliders below to move/adjust the percent change label for the chart above*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":5909,"value":"viewof leads_text = Inputs.form({\n x: Inputs.range([-50, 50], {label: \"x\", step: 1}),\n y: Inputs.range([-50, 50], {label: \"y\", step: 1}),\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":5918,"value":"html `

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2992,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":2887,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":4204,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":4177,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":4638,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":2840,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":5926,"value":"md `*Use the sliders below to move/adjust the percent change label for the chart above*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":5922,"value":"viewof bgc_text = Inputs.form({\n x: Inputs.range([-50, 50], {label: \"x\", step: 1}),\n y: Inputs.range([-50, 50], {label: \"y\", step: 1}),\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1828,"value":"md `

\n\n# 30-day Review`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4358,"value":"md `The date 45-days ago was **${d3.utcFormat('%B %d, %Y')(forty_five_days_ago_today)}**. The date range for the summary below is from **${d3.utcFormat('%B %d, %Y')(date_range_30[0])}** to **${d3.utcFormat('%B %d, %Y')(date_range_30[1])}**\n

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":5996,"value":"// notes for Josh\n\n// their goals are the TARGET COST PER ACTIVATION (HIRE)\n// client updates this about every quarter, they live here: https://docs.google.com/spreadsheets/d/1_bgH_6aUN9aiga_JIHGv5iXKV4WsVVfhY7yp_f7Ke5k/edit#gid=1654330183\n// this document provides a TCPA by market","pinned":true,"hidden":false,"mode":"ojs"},{"id":5971,"value":"current_tcpa_date = op.parse_date(market_goals.columnNames()[12].slice(4,-33))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1887,"value":"Plot.plot({\n height: 700,\n marginLeft: 75, \n marginTop: 50,\n marginRight: 55,\n y: {\n label: 'Region'\n },\n color: {\n range: [appcast_color_palette[0], appcast_color_palette[6]]\n },\n marks: [\n Plot.axisX({anchor: 'top', tickFormat: '+$,.0f', labelOffset: 45, labelAnchor: 'center',\n label: '← below · ($) Difference between Target and Actual CPA · above →'}),\n Plot.barX(clean_30_day.filter( d => d.title != 'All Other' && d.title != 'Min Bid'), {\n x: \"tcpa_difference\", \n y: \"title\", \n fill: d => Math.sign(d.tcpa_difference),\n sort: {y: \"x\", reverse: true}\n }),\n Plot.axisY({x: 0, textAnchor: 'start', dx: 15, tickSize: 0, label: null}),\n Plot.ruleX([0]),\n Plot.gridX({interval: 1000, stroke: '#fff', strokeOpacity: 1}),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1919,"value":"Plot.plot({\n height: 700,\n width: 300,\n marginLeft: 75, \n marginTop: 50,\n y: {\n label: 'Region',\n //domain: list_30_day\n },\n x: {\n label: 'Total Activations',\n },\n marks: [\n Plot.axisX({anchor: 'top', labelAnchor: 'center', labelOffset: 45, interval: 10}),\n Plot.barX(clean_30_day.filter( d => d.title != 'All Other' && d.title != 'Min Bid'), {\n x: \"activations\", \n y: \"title\", \n channels: {tcpa_difference: {value: 'tcpa_difference'}},\n sort: {y: 'tcpa_difference', reverse: true},\n fill: appcast_color_palette[0]\n }),\n Plot.text(clean_30_day, {\n x: 'activations',\n y: 'title',\n fill: d => d.activations > 10 ? appcast_color_palette[0] : null,\n text: 'activations',\n dx: 10\n }),\n Plot.ruleX([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4384,"value":"md `### Good performing markets \n*(more than 10 activations and at or below TCPA)*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2741,"value":"viewof ten_activations = clean_30_day\n .filter(aq.escape(d => list_30_day.includes(d.title)))\n .filter(d => d.activations >= 10)\n .select(0,4,7)\n .orderby(aq.desc(2))\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2756,"value":"ten_activations_array = ten_activations.select('title')\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":4067,"value":"md `

\n# MoM`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4778,"value":"viewof mom_end_date = Inputs.date({label: \"MoM End Date\", value: \"2023-06-01\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":3044,"value":"mom_leads_plot = Plot.plot({\n marginTop: 50,\n marginRight: 30,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'bottom', label: null}),\n Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}),\n Plot.axisY({tickFormat: 's', label: 'Leads ↑'}),\n Plot.gridX({interval: 'quarter', opacity: 0.05}),\n // data marks\n Plot.rectY(clean_monthly, {\n x: 'month',\n y: 'leads',\n interval: d3.utcMonth,\n fill: appcast_color_selector,\n insetRight: barCart_inset,\n insetLeft: barCart_inset,\n //opacity: opacity_range, \n title: d => `${d3.format(',')(d.leads)}`\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":3060,"value":"mom_bgc_plot = Plot.plot({\n marginTop: 50,\n marginRight: 30,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'bottom', label: null}),\n Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}),\n Plot.axisY({tickFormat: 's', label: 'BGC ↑'}),\n Plot.gridX({interval: 'quarter', opacity: 0.05}),\n // data marks\n Plot.rectY(clean_monthly, {\n x: 'month',\n y: 'bgc',\n interval: d3.utcMonth,\n fill: appcast_color_selector,\n insetRight: barCart_inset,\n insetLeft: barCart_inset,\n //opacity: opacity_range\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4785,"value":"mom_act_plot = Plot.plot({\n marginTop: 50,\n marginRight: 30,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'bottom', label: null}),\n Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}),\n Plot.axisY({tickFormat: 's', label: 'Activations ↑'}),\n Plot.gridX({interval: 'quarter', opacity: 0.05}),\n // data marks\n Plot.rectY(clean_monthly, {\n x: 'month',\n y: 'Activations',\n interval: d3.utcMonth,\n fill: appcast_color_selector,\n insetRight: barCart_inset,\n insetLeft: barCart_inset,\n //opacity: opacity_range\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4239,"value":"viewof spend_text = Inputs.toggle({label: \"Spend text\", value: false})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4233,"value":"mom_spend_plot = Plot.plot({\n marginTop: 50,\n marginRight: 30,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'bottom', label: null}),\n Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}),\n Plot.axisY({tickFormat: '$.0s', label: 'Spend ↑', interval: 1000000}),\n Plot.gridX({interval: 'quarter', opacity: 0.05}),\n // data marks\n Plot.rectY(clean_monthly, {\n x: 'month',\n y: 'spend',\n interval: d3.utcMonth,\n fill: appcast_color_selector,\n insetRight: barCart_inset,\n insetLeft: barCart_inset,\n //opacity: opacity_range,\n title: d => `${d3.format('$,.0f')(d.spend)}`\n }),\n spend_text ? Plot.text(clean_monthly, Plot.selectLast({\n x: 'month',\n y: 'spend',\n text: d => `${d3.format('$,.0f')(d.spend)}`,\n dy: -10,\n dx: 3,\n textAnchor: 'start',\n fontSize: 11\n })) : null,\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":3049,"value":"$35","pinned":false,"hidden":false,"mode":"ojs"},{"id":3069,"value":"$36","pinned":false,"hidden":false,"mode":"ojs"},{"id":3055,"value":"Plot.plot({\n marginTop: 50,\n insetTop: 10,\n insetRight: 45,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}),\n Plot.gridX({interval: 'quarter', opacity: 0.05}),\n Plot.axisX({anchor: 'bottom', label: null}),\n Plot.axisY({label: 'Lead to BGC CR ↑', tickFormat: '.0%'}),\n Plot.gridY({opacity: 0.05}),\n // data marks\n Plot.line(clean_monthly, {\n x: 'month',\n y: 'LtoBGC',\n stroke: appcast_color_palette[2],\n strokeWidth: 1\n }),\n Plot.dot(clean_monthly, {\n x: 'month',\n y: 'LtoBGC',\n fill: appcast_color_palette[2],\n //fillOpacity: opacity_range,\n stroke: 'white'\n }),\n Plot.text(clean_monthly, Plot.selectLast({\n x: 'month',\n y: 'LtoBGC',\n dx: -20,\n dy: 15,\n fontSize: 14,\n textAnchor: 'start',\n text: d => `${d3.format('.1%')(d.LtoBGC)}`\n })),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":3065,"value":"Plot.plot({\n marginTop: 50,\n insetRight: 45,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'quarter', tickSize: 0, tickFormat: 'Q' + '%q', opacity: 0.25, label: null}),\n Plot.gridX({interval: 'quarter', opacity: 0.05}),\n Plot.gridY({opacity: 0.05}),\n Plot.axisX({anchor: 'bottom', label: null}),\n Plot.axisY({label: 'CPA ↑', tickFormat: '$.0s'}),\n // data marks\n Plot.line(clean_monthly, {\n x: 'month',\n y: 'cpA',\n stroke: appcast_color_palette[2],\n strokeWidth: 1\n }),\n Plot.dot(clean_monthly, {\n x: 'month',\n y: 'cpA',\n fill: appcast_color_palette[2],\n //fillOpacity: opacity_range,\n stroke: 'white'\n }),\n Plot.text(clean_monthly, Plot.selectLast({\n x: 'month',\n y: 'cpA',\n dx: -20,\n dy: -15,\n fontSize: 14,\n textAnchor: 'start',\n text: d => `${d3.format('$,.0f')(d.cpA)}`\n })),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2087,"value":"md `

\n# Parameters`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4173,"value":"Inputs.bind(Inputs.radio(appcast_color_palette, {\n label: \"Chart Fill/Stroke Color\", \n value: appcast_color_palette[3],\n format: (x) => html`${x}`\n}), viewof appcast_color_selector)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2331,"value":"viewof appcast_color_selector = Inputs.radio(appcast_color_palette, {\n label: \"Chart Fill/Stroke Color\", \n value: appcast_color_palette[3],\n format: (x) => html`${x}`\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2479,"value":"viewof opacity_range = Inputs.range([0, 1], {label: \"Opacity\", step: 0.05})","pinned":false,"hidden":false,"mode":"ojs"},{"id":721,"value":"viewof chart_width = Inputs.range([400, 1000], {label: \"Chart Width\", step: 10, value: 400})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4055,"value":"md `

\n# Timeline\n*(not needed for report, so no worries if this is empty)*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2380,"value":"viewof file1 = Inputs.file({label: \"Timeline (not needed for the report)\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2446,"value":"viewof note_category_selector = Inputs.select(timeline.map(d => d.Category), {label: \"Select one\", unique: true, multiple: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":3161,"value":"timeline_plot.legend('color', {columns: 3, width: 660, swatchWidth: 15})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2411,"value":"$37","pinned":false,"hidden":false,"mode":"ojs"},{"id":943,"value":"md `

\n### *Chart Configurations*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":568,"value":"style_metrics = ({\n view: {stroke: null},\n axis: {gridOpacity: 0.5},\n axisY: {domain: null, grid: true, titlePadding: 10, labelOpacity: 0.25, tickOpacity: 0.5},\n axisX: {title: null, title: null, tickCount: 'week', labelOpacity: 0.5},\n line: {strokeWidth: 0.75, point: {size: 30, color: 'white', filled: false, fill: appcast_color_selector}, stroke: appcast_color_selector},\n rule: {stroke: appcast_color_palette[1], strokeWidth: 1.5},\n text: {fill: appcast_color_palette[1]}\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":579,"value":"style_perf = ({\n view: {stroke: null},\n //axis: {grid: null},\n axisY: {domain: null, grid: true, gridOpacity: 0.5, labelOpacity: 0.25, ticks: false, titlePadding: 10},\n axisX: {tickCount: 'week', grid: false, labelSeparation: 5, title: null, labelOpacity: 0.5},\n bar: {continuousBandSize: 12, fill: appcast_color_palette[4], opacity: 0.75},\n rule: {stroke: appcast_color_palette[1], strokeWidth: 1.5},\n text: {fill: appcast_color_palette[1]}\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":713,"value":"style_cr = ({\n view: {stroke: null},\n axis: {gridOpacity: 0.25},\n axisY: {domain: null, grid: true, format: '%', titlePadding: 10, labelOpacity: 0.25, tickOpacity: 0.25},\n axisX: {title: null, title: null, tickCount: 'week', labelOpacity: 0.5},\n line: {strokeWidth: 0.75, point: {size: 30, color: 'white', filled: false, fill: 'steelBlue', fill: appcast_color_palette[2]}, stroke: appcast_color_palette[2]},\n rule: {stroke: appcast_color_palette[1], strokeWidth: 1.5},\n text: {fill: appcast_color_palette[1]}\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":850,"value":"viewof chart_height = Inputs.range([50, 400], {label: \"Chart Height\", step: 10, value: 200})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4209,"value":"viewof barCart_inset = Inputs.range([0, 10], {label: \"Bar chart inset\", step: 1, value: 2})","pinned":false,"hidden":false,"mode":"ojs"},{"id":840,"value":"md `### *Tooltips*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":645,"value":"metrics_tooltip = [\n {field: 'Week', type: 'temporal', format: '%b-%d-%Y ' + '(W'+'%U' + ')'},\n {field: 'cpA', type: 'quantitative', format: '$,.2f', title: 'CPA'},\n {field: 'cpbgc', type: 'quantitative', format: '$,.2f', title: 'CPBGC'},\n {field: 'cpl', type: 'quantitative', format: '$,.2f', title: 'CPL'}\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":709,"value":"cRates_tooltip = [\n {field: 'Week', type: 'temporal', format: '%b %d-%Y ' + '(W'+'%U' + ')'},\n {field: 'BGCtoA', type: 'quantitative', format: '.1%', title: 'BGC to A'},\n {field: 'LtoBGC', type: 'quantitative', format: '.1%', title: 'L to BGC'},\n {field: 'CtoL', type: 'quantitative', format: '.2%', title: 'C to Lead'}\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":4378,"value":"md `## 30 day dates`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4860,"value":"viewof custom_date_start = Inputs.date({label: \"custom 30 day range\", value: d3.utcDay.offset(new Date(), 3) })","pinned":true,"hidden":false,"mode":"ojs"},{"id":1860,"value":"thirty_day_lookback_date = \n// for offsetting from today\n // d3.utcDay.offset(forty_five_days_ago_today, -30)\n\n// for offseting from Monday\nd3.utcDay.offset(forty_five_days_ago_a_different_day, -30)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1938,"value":"date_range_30 = \n// option one: if it's Monday, use this\n // [thirty_day_lookback_date, forty_five_days_ago_today]\n\n// option two: if it's a custom day, use this\n[thirty_day_lookback_date, forty_five_days_ago_a_different_day]","pinned":true,"hidden":false,"mode":"ojs"},{"id":609,"value":"md `### *Dates*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":612,"value":"date_range = d3.extent(clean, d => d.Week)","pinned":true,"hidden":false,"mode":"ojs"},{"id":616,"value":"forty_five_days_ago = d3.timeDay.offset(date_range[1], -45)","pinned":true,"hidden":false,"mode":"ojs"},{"id":745,"value":"forty_five_days_ago_today = d3.utcDay(d3.utcDay.offset(new Date(), -45))","pinned":true,"hidden":false,"mode":"ojs"},{"id":5438,"value":"forty_five_days_ago_a_different_day = d3.utcDay(d3.utcDay.offset(custom_date_start, -45))","pinned":true,"hidden":false,"mode":"ojs"},{"id":2323,"value":"thirty_days_ago_today = d3.utcDay(d3.utcDay.offset(new Date(), -30))","pinned":true,"hidden":false,"mode":"ojs"},{"id":630,"value":"dateLine = [\n {date: forty_five_days_ago_today}, \n {week_num: op.week(forty_five_days_ago_today)}, \n {week_date: d3.utcWeek(forty_five_days_ago_today)},\n // {week_time_date: d3.timeWeek(forty_five_days_ago_today)},\n // {week_monday: d3.utcMonday(forty_five_days_ago_today)},\n // {test: d3.utcMonday(test_date)}\n ]","pinned":true,"hidden":false,"mode":"ojs"},{"id":1101,"value":"test_date = new Date('2022-10-31')","pinned":true,"hidden":false,"mode":"ojs"},{"id":1104,"value":"d3.utcWeek(test_date)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1106,"value":"d3.utcMonday(test_date)","pinned":true,"hidden":false,"mode":"ojs"},{"id":4381,"value":"md `## Other data stuff`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1952,"value":"viewof perc_clean = clean_30_day\n .derive({\n cpa_diff_perc: d => d.tcpa_difference / d['New TCPA_']\n })\n .select('cpa_diff_perc')\n // .groupby({\n // bin_0: aq.bin('cpa_diff_perc', {maxbins: 10}),\n // bin_1: aq.bin('cpa_diff_perc', {maxbins: 10, offset: 1})\n // })\n // .count()\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1858,"value":"// clean up of the lyft repository\nviewof clean_30_day = aq.from(raw_athena_30_day_review)\n .derive({date: d => op.parse_date(d.date)})\n .filter(aq.escape(d => d.date >= thirty_day_lookback_date && d.date <= forty_five_days_ago_today))\n .filter(d => op.includes(op.lower(d.title),'craigs') == false)\n .groupby('title')\n .rollup({\n spend: d => op.sum(d.spend),\n leads: d => op.sum(d.leads),\n bgc: d => op.sum(d.bgc),\n activations: d => op.sum(d.activations)\n })\n //.filter(d => d.activations > 0)\n .derive({\n CPA: d => d.spend / d.activations\n })\n .join_left(aq.from(clean_repository_tcpas), ['title', 'Region'])\n .filter(d => d['New TCPA_'] != undefined)\n .filter(aq.escape(d => active_markets.includes(d.title)))\n .select(0,1,2,3,4,5,7)\n .orderby(aq.desc('spend'))\n .derive({\n tcpa_difference: d => d.CPA - d['New TCPA_']\n })\n .orderby(aq.desc('tcpa_difference'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1923,"value":"list_30_day = clean_30_day\n .filter(d => d.title != 'All Other' && d.title != 'Min Bid')\n .array('title')","pinned":true,"hidden":false,"mode":"ojs"},{"id":1853,"value":"md `### cleaning athena`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1855,"value":"raw_athena_30_day_review = file.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1851,"value":"md `### cleaning repository`","pinned":true,"hidden":false,"mode":"ojs"},{"id":1845,"value":"clean_repository_tcpas = aq.from(parsed_repository)\n .select('Region', 'New TCPA_')\n .filter(d => d['New TCPA_'] != 'pause')\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":4397,"value":"file7 = FileAttachment(\"Lyft Repository (5).xlsx\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":1831,"value":"//if you want to load the file every time\nviewof file7_old = Inputs.file({label: \"Repository\", required: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1834,"value":"raw_repository = file7.xlsx()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1840,"value":"parsed_repository = raw_repository.sheet(\"Active Markets & Bids\", {headers: true, range: \"B3:Q170\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":2541,"value":"viewof campaign_selector = Inputs.select(data.map(d => d.title), {label: \"Select a campy\", unique: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2522,"value":"Plot.plot({\n grid: true,\n height: 600,\n width,\n zero: true,\n inset: 50,\n r: {\n range: [1,30]\n },\n color: {\n scheme: 'blues'\n },\n marks: [\n Plot.line(test_line, {x: 'leads', y: 'cpl', curve: 'catmull-rom'}),\n Plot.dot(test_line, {x: 'leads', y: 'cpl', r: 'leads', stroke: d => d.leads, strokeWidth: 6}),\n Plot.text(test_line, {x: 'leads', y: 'cpl', text: d => `${d3.utcFormat('%d')(d.date)}`, stroke: 'white', fill: 'black'}),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2513,"value":"test_line = aq.from(data)\n .filter(aq.escape( d => d.title === campaign_selector && d.leads > 0 && d.date < new Date(2022,9,15) ))\n .filter(d => d.master_publisher_aggregated == 'Appcast Exchange')\n .derive({\n cpl: d => d.spend / d.leads,\n date: aq.escape(d => d3.utcDay(d.date))\n })\n .orderby('date')\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":2551,"value":"test_cluster = aq.from(data)\n //.filter(aq.escape( d => d.title === campaign_selector && d.leads > 0 && d.date < new Date(2022,9,15) ))\n .filter(d => d.master_publisher_aggregated == 'Appcast Exchange')\n .derive({\n cpl: d => d.spend / d.leads,\n date: aq.escape(d => d3.utcDay(d.date))\n })\n .orderby('date')\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":3758,"value":"md `## Additional Charts`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1177,"value":"md `# View and Explore\n*WoW*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":925,"value":"viewof performance_charts = Inputs.checkbox(['leads', 'spend', 'bgc', 'Activations'], {label: \"Select metrics\", value: ['leads', 'spend', 'bgc', 'Activations']})","pinned":false,"hidden":false,"mode":"ojs"},{"id":546,"value":"vl.layer(\n \n // bar charts\n vl.markBar()\n .data(clean)\n .encode(\n vl.x().fieldT('Week'),\n vl.y().fieldQ(vl.repeat('row')),\n )\n .height(chart_height - 30).width(chart_width),\n \n // 45 days-ago marker\n vl.markRule()\n .data(dateLine)\n .encode(\n vl.x().fieldT('week_date')\n ),\n\n // 45 days-ago text\n vl.markText({angle: 90, dy: -10, dx: 25, fontSize: 7.5})\n .data(dateLine)\n .encode(\n vl.x().fieldT('week_date'),\n vl.y().value(-'height'),\n vl.text().value(['45 days back'])\n ),\n \n )\n .repeat({\n row: performance_charts\n })\n .spacing(50)\n .padding({top: 25, bottom: 100})\n .config(style_perf)\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":931,"value":"viewof metrics_charts = Inputs.checkbox(['cpl', 'cpbgc', 'cpA'], {label: \"Select some\", value: ['cpl', 'cpbgc', 'cpA']})","pinned":false,"hidden":false,"mode":"ojs"},{"id":560,"value":"vl.layer(\n \n // 45 days-ago marker\n vl.markRule()\n .data(dateLine)\n .encode(\n vl.x().fieldT('date')\n ),\n\n\n // 45 days-ago text\n vl.markText({angle: 90, dy: -7.5, dx: 25, fontSize: 7.5})\n .data(dateLine)\n .encode(\n vl.x().fieldT('date'),\n vl.y().value(-'height'),\n vl.text().value(['45 days back'])\n ),\n \n // line chart\n vl.markLine()\n .data(clean)\n .encode(\n vl.x().fieldT('Week'),\n vl.y().fieldQ(vl.repeat('row')),\n //vl.y().fieldQ('leads'),\n vl.tooltip(metrics_tooltip)\n )\n .height(chart_height).width(chart_width),\n \n )\n .repeat({\n row: metrics_charts\n })\n .spacing(50)\n .padding({bottom: 100, top: 25})\n .config(style_metrics)\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":937,"value":"viewof cr_charts = Inputs.checkbox(['CtoL', 'LtoBGC', 'BGCtoA'], {label: \"Select some\", value: ['CtoL', 'LtoBGC', 'BGCtoA']})","pinned":false,"hidden":false,"mode":"ojs"},{"id":707,"value":"vl.layer(\n \n // 45 days-ago marker\n vl.markRule()\n .data(dateLine)\n .encode(\n vl.x().fieldT('date')\n ),\n\n // 45 days-ago text\n vl.markText({angle: 90, dy: -7.5, dx: 25, fontSize: 7.5})\n .data(dateLine)\n .encode(\n vl.x().fieldT('date'),\n vl.y().value(-'height'),\n vl.text().value(['45 days back'])\n ),\n \n // line chart\n vl.markLine()\n .data(clean)\n .encode(\n vl.x().fieldT('Week'),\n vl.y().fieldQ(vl.repeat('row')),\n //vl.y().fieldQ('leads'),\n vl.tooltip(cRates_tooltip)\n )\n .height(chart_height).width(chart_width)\n \n )\n .repeat({\n row: cr_charts\n })\n .spacing(50)\n .padding({bottom: 100, top: 25})\n .config(style_cr)\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":88,"value":"
\n
\n
\n

","pinned":false,"hidden":false,"mode":"html"},{"id":2095,"value":"Plot.plot({\n marginTop: 50,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}),\n Plot.gridX({interval: 'week', opacity: 0.05}),\n Plot.axisX({anchor: 'bottom'}),\n Plot.axisY({label: 'Leads ↑'}),\n // data marks\n Plot.areaY(dod_date_filtered, {\n x: 'date',\n y: 'leads',\n fill: appcast_color_selector,\n opacity: opacity_range\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2175,"value":"Plot.plot({\n marginTop: 50,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}),\n Plot.gridX({interval: 'week', opacity: 0.05}),\n Plot.axisX({anchor: 'bottom'}),\n Plot.axisY({label: 'CPL ↑', tickFormat: '$.0f'}),\n // data marks\n Plot.line(clean.filter(aq.escape(d => d.date >= wow_date_start) ), {\n x: 'date',\n y: 'cpl',\n stroke: appcast_color_selector,\n strokeWidth: 1\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2242,"value":"Plot.plot({\n marginTop: 50,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}),\n Plot.gridX({interval: 'week', opacity: 0.05}),\n Plot.axisX({anchor: 'bottom'}),\n Plot.axisY({label: 'Click to Lead CR % ↑', tickFormat: '.0%'}),\n // data marks\n Plot.line(clean.filter(aq.escape(d => d.date >= wow_date_start) ), {\n x: 'date',\n y: 'CtoL',\n stroke: appcast_color_selector,\n strokeWidth: 1\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2244,"value":"$38","pinned":false,"hidden":false,"mode":"ojs"},{"id":2250,"value":"Plot.plot({\n marginTop: 50,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}),\n Plot.gridX({interval: 'week', opacity: 0.05}),\n Plot.axisX({anchor: 'bottom'}),\n Plot.axisY({label: 'CPBGC ↑', tickFormat: '$.0f'}),\n // data marks\n Plot.line(clean.filter(aq.escape(d => d.date >= wow_date_start) ), {\n x: 'date',\n y: 'cpbgc',\n stroke: appcast_color_selector,\n strokeWidth: 1\n }),\n Plot.ruleY([0]),\n // Plot.dotX(timeline, {\n // x: 'Date',\n // y: 0,\n // fill: 'Category'\n // }),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2254,"value":"Plot.plot({\n marginTop: 50,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}),\n Plot.gridX({interval: 'week', opacity: 0.05}),\n Plot.axisX({anchor: 'bottom'}),\n Plot.axisY({label: 'Lead to BGC CR % ↑', tickFormat: '.0%'}),\n // data marks\n Plot.line(clean.filter(aq.escape(d => d.date >= wow_date_start) ), {\n x: 'date',\n y: 'LtoBGC',\n stroke: appcast_color_selector,\n strokeWidth: 1\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2169,"value":"Plot.plot({\n marginTop: 50,\n height: chart_height,\n width: chart_width,\n marks: [\n //axes\n Plot.axisX({anchor: 'top', ticks: 'week', tickSize: 0, tickFormat: 'W' + '%U', opacity: 0.25}),\n Plot.gridX({interval: 'week', opacity: 0.05}),\n Plot.axisX({anchor: 'bottom'}),\n Plot.axisY({label: 'Activations ↑'}),\n // data marks\n Plot.areaY(wow_date_filtered, {\n x: 'date',\n y: 'Activations',\n r: 1.5,\n fill: appcast_color_selector,\n opacity: opacity_range\n }),\n Plot.areaY(wow_date_filtered, {\n filter: (d) => d.date >= forty_five_days_ago_today,\n x: \"date\",\n y: \"Activations\",\n fill: \"white\",\n fillOpacity: 0.7\n }),\n Plot.ruleX([forty_five_days_ago_today],\n { stroke: \"lightgray\", strokeWidth: 1, strokeDasharray: \"4 2\" }\n ),\n Plot.textX([forty_five_days_ago_today], \n { fill: 'currentColor', text: ['45 days ago'], rotate: 90, fontSize: 7, dx: 4, dy: -30, frameAnchor: 'bottom' }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2258,"value":"$39","pinned":false,"hidden":false,"mode":"ojs"},{"id":2317,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs"},{"id":20,"value":"md `## Appendix\n\n_Implementation & data stuff_`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4281,"value":"md `The query used for the main data set is the following: \n\n~~~sql\n\nselect date, sum(clicks) clicks, sum(spend) spend, sum(impressions) impressions, sum(applies_paid) leads, sum(qualified) bgc, sum(hired) activations, job_groups.title, job_boards.master_publisher_aggregated\nfrom jobdata\nleft join job_boards\non jobdata.job_board_id = job_boards.id\nleft join job_groups\non jobdata.job_group_id = job_groups.id\nwhere employer_id = 4972 -- USA\n--where employer_id = 6140 -- Canada\nand date between cast('2022-01-01' as date) and cast('2023-05-08' as date) -- YTD, so change the latest date for today\n--adjust line above for the date range needed\ngroup by date, job_groups.title, job_boards.master_publisher_aggregated\norder by date\n--data is not matching 100% to the UI due to Lyft running on UTC and the data warehouse on EST\n\n\n~~~\n\n

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":198,"value":"### *Weekly Performance Data*","pinned":false,"hidden":false,"mode":"md"},{"id":1825,"value":"date_filtered_clean = clean.objects().filter(d => d.Week >= forty_five_days_ago_today)","pinned":true,"hidden":false,"mode":"ojs"},{"id":508,"value":"viewof clean = aq.from(data)\n .rename({'master_publisher_aggregated': 'publisher'})\n//filters\n .filter(d => d.publisher != null)\n //.filter(d => d.publisher === 'Jobcase CPC')\n//cleanup\n .derive({\n Week: aq.escape(d => d3.utcMonday(d.date))\n })\n// aggregation by day\n .groupby('date')\n .rollup({\n clicks: d => op.sum(d.clicks),\n spend: d => op.sum(d.spend),\n leads: d => op.sum(d.leads),\n bgc: d => op.sum(d.bgc),\n Activations: d => op.sum(d.activations)\n })\n//agregation by week\n // .groupby('Week')\n // .rollup({\n // clicks: d => op.sum(d.clicks),\n // spend: d => op.sum(d.spend),\n // leads: d => op.sum(d.leads),\n // bgc: d => op.sum(d.bgc),\n // Activations: d => op.sum(d.activations)\n // })\n .derive({\n cpl: d => d.spend / d.leads,\n cpbgc: d => d.spend / d.bgc,\n cpA: d => d.spend / d.Activations,\n CtoL: d => d.leads / d.clicks,\n BGCtoA: d => d.Activations / d.bgc,\n LtoBGC: d => d.bgc / d.leads\n })\n .orderby('date')\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":4835,"value":"md `**ad-hoc table because hire data failed**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4840,"value":"viewof ad_hoc_weekly = Inputs.file({label: \"Data\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4845,"value":"raw_ad_hoc = ad_hoc_weekly.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":4848,"value":"viewof clean_weekly_ad_hoc = aq.from(raw_ad_hoc)\n .derive({Week: d => op.parse_date(d.Week)})\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":2844,"value":"$3b","pinned":true,"hidden":false,"mode":"ojs"},{"id":5446,"value":"Inputs.table(clean_weekly)","pinned":true,"hidden":false,"mode":"ojs"},{"id":2210,"value":"dod_date_filtered = clean.filter(aq.escape( d => d.date >= wow_date_start && d.date < wow_date_end ))","pinned":false,"hidden":false,"mode":"ojs"},{"id":2850,"value":"wow_date_filtered = \n // \"normal\" code\n clean_weekly.filter(aq.escape( d => d.Week >= wow_date_start && d.Week < wow_date_end ))\n\n // ad-hoc code\n //clean_weekly_ad_hoc.filter(aq.escape( d => d.Week >= wow_date_start && d.Week < wow_date_end ))","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"data = file.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":3039,"value":"md `### *Monthly Performance Data*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":3042,"value":"$3c","pinned":true,"hidden":false,"mode":"ojs"},{"id":187,"value":"### *Secondary data sources*","pinned":false,"hidden":false,"mode":"md"},{"id":1316,"value":"md `#### *File with goals*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2383,"value":"raw_timeline = file1.xlsx()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2386,"value":"timeline = raw_timeline.sheet('SC2 Timeline', {headers: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":4668,"value":"viewof market_goals = aq.from(parsed_repository)\n .rename({'Sun Aug 06 2023 20:00:00 GMT-0400 (Eastern Daylight Time)': 'August_7_2023'})\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1323,"value":"clean_tcpa = aq.from(market_goals)\n .derive({\n tcpa: d => op.replace(d['New TCPA'], '$','')\n })\n .derive({tcpa: d => +op.replace(d.tcpa, ',','')})\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1329,"value":"active_markets = aq.from(market_goals)\n .filter(d => d.August_7_2023 == 'active')\n .array('Region')","pinned":true,"hidden":false,"mode":"ojs"},{"id":69,"value":"// all vega data-sets\n\n// to 'extract' a dataset as an array (only for .csv and .json)\n// use this format: football = v_data['football.json']()\n// for some .csv, it's better to use the url format: zipcodes = v_data['zipcodes.csv'].url\n// this is due to the fact that parsing it as an array of objects results in zipcodes of the form \"00313\" turning into \"313\"\n\nv_data = require('vega-datasets@2')","pinned":false,"hidden":false,"mode":"ojs"},{"id":98,"value":"zips = v_data['zipcodes.csv'].url","pinned":false,"hidden":false,"mode":"ojs"},{"id":159,"value":"us_holidays_2021 = USholidays.filter(d => op.utcyear(date_parser(d.date)) == 2021)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2488,"value":"category_filtered_timeline = timeline.filter(d => note_category_selector.includes(d.Category))","pinned":false,"hidden":false,"mode":"ojs"},{"id":193,"value":"### *Libraries*","pinned":false,"hidden":false,"mode":"md"},{"id":4,"value":"import {vl} from '@vega/vega-lite-api-v5'","pinned":false,"hidden":false,"mode":"ojs"},{"id":1983,"value":"import {toc} from \"@nebrius/indented-toc\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":36,"value":"import {uniqueValid} from '@uwdata/data-utilities'","pinned":false,"hidden":false,"mode":"ojs"},{"id":59,"value":"import { aq, op } from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs"},{"id":137,"value":"// object containing all of the FiPS us county codes\n\nimport {counties} from \"@d3/choropleth\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":391,"value":"// list of US Holidays\n\nimport {USholidays} from \"@observablehq/microsoft-reactor-hackathon\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":480,"value":"import {Legend, Swatches} from \"@d3/color-legend\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":483,"value":"import {DuckDBClient} from \"@cmudig/duckdb\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":486,"value":"import { palettes } from '@makio135/give-me-colors'","pinned":false,"hidden":false,"mode":"ojs"},{"id":496,"value":"// this is a really cool way of displaying tables. Much more aesthetically pleasing than the defaults of arquero and input.table\n\nimport { formatTable } from \"@saneef/pretty-tables\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":456,"value":"md `### *Functions*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":409,"value":"date_parser = d3.utcParse('%m/%d/%Y')","pinned":true,"hidden":false,"mode":"ojs"},{"id":747,"value":"formatter = d3.utcFormat('%B %d, %Y')","pinned":true,"hidden":false,"mode":"ojs"},{"id":208,"value":"### *Chart configuration*","pinned":false,"hidden":false,"mode":"md"},{"id":810,"value":"appcast_color_palette = ['#05062C', '#3138BB','#FF7D6F', '#AF9BFC', '#C7FFE2', '#127FFC', '#FF9B77', '#B7D8FE', '#DAE2FD', '#FAFAFA']","pinned":true,"hidden":false,"mode":"ojs"},{"id":202,"value":"clean_config = ({\n view: { \n stroke: null,\n step: 10\n },\n rect: {\n width: {band: 0.9},\n height: {band: 0.9}\n },\n axis: {\n gridOpacity: 0.5,\n labelFontWeight: 75,\n domain: false,\n offset: 5,\n titleFontWeight: 350,\n titleFontSize: 10\n },\n axisX: {\n tickCount: 5,\n titlePadding: 10,\n labelPadding: 4\n },\n axisY: {\n ticks: false,\n labelLimit: 300,\n labelFontSize: 7\n },\n title: {\n fontWeight: 300,\n fontSize: 13,\n offset: 25\n }\n})","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W3d","stdlib":"1","titling":"auto"}