2f:T422,spend2 = Plot.plot({ marginTop: 50, marginLeft: 50, marginRight: 50, insetTop: 15, height: 250, grid: false, y: { zero: true, tickFormat: '$,.0s' }, // x: { // tickSize: 0, // tickFormat: '%a (%b-%d)', // ticks: 5, // label: null // }, marks: [ Plot.ruleY([0]), Plot.axisX({interval: 'day'}), Plot.rectY(clean, { filter: d => d.date == d3.utcDay(new Date(now)).toString(), x: 'date', y: d => d.spend + 1200, // adding the daily craigslist spend interval: d3.utcDay, insetRight: 5, fill: null, stroke: 'grey', strokeDasharray: [2,2] }), Plot.rectY(clean, { x: 'date', y: 'spend', interval: d3.utcDay, insetRight: 5, }), Plot.ruleY([weekly_budget/7], { strokeDasharray: [2,3], stroke: 'green' }), Plot.text(clean, { x: 'date', y: 'spend', text: d => `${d3.format('$.3s')(d.spend)}`, dy: -10, dx: 20, fill: 'currentColor', stroke: 'white' }), ] })30:T1259,vl.vconcat( vl.hconcat( // spend vl.layer( vl.markBar({fill: 'black', point: {fill: 'black', size: 25}}) .title({text: 'Spend'}) .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday') .axis({grid: false}), vl.y().fieldQ('spend') .axis({format: '$~s'}), vl.tooltip([ {field: 'date', type: 'temporal', scale: {type: 'utc'}, format: '(%a) %b %d, %Y'}, {field: 'spend', type: 'quantitative', format: '$,'} ]) ), vl.markBar({stroke: 'red', fill: null}) //.title({text: 'Spend'}) .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday'), vl.y().fieldQ('daily_amount') ) // vl.markRule() // .data(daily_budget) // .encode( // vl.y().fieldQ('daily_amount') // ) ) .width(200).height(150), // leads vl.markBar({fill: 'firebrick'}) .title({text: 'Leads'}) .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday') .axis({grid: false}), vl.y().fieldQ('leads') .scale({ domain: [0, d3.max(clean.slice(0,-1), d => d.leads)] }), vl.tooltip([ {field: 'date', type: 'temporal', scale: {type: 'utc'}, format: '(%a) %b %d, %Y'}, {field: 'leads', type: 'quantitative', format: ','} ]) ) .width(200).height(150), // bgc vl.markBar() .title({text: 'BGC'}) .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday') .axis({grid: false}), vl.y().fieldQ('bgc') .scale({ domain: [0, d3.max(clean.slice(0,-1), d => d.leads)] }), vl.tooltip([ {field: 'date', type: 'temporal', scale: {type: 'utc'}, format: '(%a) %b %d, %Y'}, {field: 'bgc', type: 'quantitative', format: ','} ]) ) .width(200).height(150), // LtoBGC vl.markLine({point: {size: 17.5}}) .title({text: 'LtoBGC'}) .data(clean.slice(0,-1)) .encode( vl.x().fieldT('date') .timeUnit('utcday') .axis({grid: false}), vl.y().fieldQ('LtoBGC') .axis({ format: '%' }), ) .width(200).height(150) ).spacing(50), // BOTTOM THREE CHARTS vl.hconcat( // weekly spend vl.markText({fontSize: 30, fontWeight: 100, align: 'right'}) .title({text: 'Week Total Spend'}) .data(clean) .encode( vl.text({format: '$,.0f'}).sum('spend') ) .width(200).height(100), // cpl vl.layer( vl.markLine({stroke: 'firebrick', point: {fill: 'firebrick', size: 17.5}}) .title({text: 'CPL'}) .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday') .axis({grid: false}) .scale({padding: 20}), vl.y().fieldQ('cpl') .axis({format: '$.0f'}) .scale({ domain: [0, (d3.max(clean.filter(d => d.cpbgc != Infinity), d => d.cpbgc)) + 50], //padding: 15 }), vl.tooltip([ {field: 'date', type: 'temporal', scale: {type: 'utc'}, format: '(%a) %b %d, %Y'}, {field: 'cpl', type: 'quantitative', format: '$,.1f'} ]) ), vl.markText() .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday'), vl.y().fieldQ('cpl'), vl.text({format: '$,.0f'}).fieldQ('cpl') ) ) .width(200).height(150), // cpbgc vl.layer( vl.markLine({point: {size: 17.5}}) .title({text: 'CPBGC'}) .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday') .axis({grid: false}) .scale({padding: 20}), vl.y().fieldQ('cpbgc') .axis({format: '$.0f'}) .scale({ domain: [0, (d3.max(clean.filter(d => d.cpbgc != Infinity), d => d.cpbgc)) + 50], // padding: 15 }), ), vl.markText() .data(clean) .encode( vl.x().fieldT('date') .timeUnit('utcday'), vl.y().fieldQ('cpbgc'), vl.text({format: '$,.0f'}).fieldQ('cpbgc') ) ) .width(200).height(150) ).spacing(50) ) .padding({bottom: 100, top: 50, left: 25, right: 25}) .config({ view: {stroke: null}, axis: {labelFontWeight: 200, gridOpacity: 0.5}, axisX: {title: null, tickCount: 'day'}, axisY: {tickOpacity: 0.5, domain: false, title: null, labelOpacity: 0.5, labelFontSize: 8}, bar: {width: {band: 0.4}}, //,continuousBandSize: 25 }, title: {offset: 20, fontWeight: 200}, line: {strokeWidth: 1}, text: {dx: 5, dy: -7.5, align: 'center', fontSize: 9, fontWeight: 200}, rule: {strokeDash: [2,2], stroke: 'green'} }) .spacing(50) .render()31:T450,vl.markCircle({tooltip: true}) //, width: {band: 0.8}, height: {band: 0.8}}) .data(clean_by_campy_pub.objects() .filter(d => active_list.includes(d.title) && d.date >= by_campy_start_date) .filter(d => d.Publisher == select_pub) ) .encode( //vl.column().fieldN('Publisher'), vl.x().fieldT('date').title(null) .axis({orient: 'top'}), vl.y().fieldN('title').title(null) .sort(vl.sum('spend').order('descending')), vl.size().sum('spend'), vl.tooltip([ {field: 'title', type: 'nominal', title: 'Market'}, {field: 'date', type: 'temporal', format: '%b %d (%a)',scale: {type: 'utc'}}, {field: 'spend', type: 'quantitative', format: '$,.0f'}, {field: 'leads', type: 'quantitative', aggregate: 'sum'}, {field: 'cpl', type: 'quantitative', title: 'CPL', format: '$,.1f'} ]) ) .spacing(100) .config({ view: {stroke: null}, axis: {grid: false, domain: false}, axisY: {ticks: false, labelPadding: 10} }) .resolve({scale: {color: 'independent', size: 'independent'}}) .width(chart_width_) .render()32:T479,Plot.plot({ width, marginTop: 50, insetLeft: 25, insetRight: 25, insetTop: 25, marginLeft: 50, facet: { marginRight: 150, }, fy: { padding: 0.2 }, color: { legend: true }, y: { //domain: [0,10000], nice: true, grid: true, tickFormat: '$,.0s' }, marks: [ Plot.frame(), Plot.gridX({interval: 'week', facet: true}), Plot.axisX({interval: 'month', label: null, facet: false}), Plot.lineY(clean_by_pub, { x: 'week_date', y: 'cpl', stroke: 'Publisher', marker: true, strokeWidth: 1.25, title: d => `${d3.format('$,.0f')(d.cpl)} CPL`, clip: 'frame', fy: 'Publisher', strokeWidth: 1 }), Plot.text(clean_by_pub, { x: 'week_date', y: 'cpl', text: d => `${d3.format('$,.0f')(d.cpl)}`, dy: -10, fill: 'black', stroke: 'white', fy: 'Publisher' }), Plot.ruleX([forty_five_days_ago], {strokeDasharray: [5,2], strokeWidth: 0.5, facet: true}), Plot.textX([forty_five_days_ago], {frameAnchor: 'top', text: ['45 days ago'], rotate: 90, dx: 10, dy: 30, fontSize: 7.5}) ] })33:T484,Plot.plot({ width, marginTop: 50, insetLeft: 25, insetRight: 25, //insetTop: 10, marginLeft: 50, facet: { marginRight: 150 }, fy: { padding: 0.2 }, color: { legend: true }, y: { //domain: [0,10000], nice: true, grid: true, tickFormat: '$,.0s' }, marks: [ Plot.frame(), Plot.gridX({interval: 'week', facet: true}), Plot.axisX({interval: 'month', label: null, facet: false}), Plot.lineY(clean_by_pub, { x: 'week_date', y: 'cpbgc', stroke: 'Publisher', marker: true, strokeWidth: 1.25, title: d => `${d3.format('$,.0f')(d.cpbgc)} CPBGC`, clip: 'frame', fy: 'Publisher', strokeWidth: 1 }), Plot.text(clean_by_pub, { x: 'week_date', y: 'cpbgc', text: d => `${d3.format('$,.0f')(d.cpbgc)}`, dy: -10, fill: 'black', stroke: 'white', fy: 'Publisher' }), Plot.ruleX([forty_five_days_ago], {strokeDasharray: [5,2], strokeWidth: 0.5, facet: true}), Plot.textX([forty_five_days_ago], {frameAnchor: 'top', text: ['45 days ago'], rotate: 90, dx: 10, dy: 30, fontSize: 7.5}) ] })34:T478,Plot.plot({ width, marginTop: 50, insetLeft: 25, insetRight: 25, //insetTop: 10, marginLeft: 50, facet: { marginRight: 150 }, fy: { padding: 0.2 }, color: { legend: true }, y: { domain: [0,10000], nice: true, grid: true, tickFormat: '$,.0s' }, marks: [ Plot.frame(), Plot.gridX({interval: 'week', facet: true}), Plot.axisX({interval: 'month', label: null, facet: false}), Plot.lineY(clean_by_pub, { x: 'week_date', y: 'cpa', stroke: 'Publisher', marker: true, strokeWidth: 1.25, title: d => `${d3.format('$,.0f')(d.cpa)} CPA`, clip: 'frame', fy: 'Publisher', strokeWidth: 1 }), Plot.text(clean_by_pub, { x: 'week_date', y: 'cpa', text: d => `${d3.format('$,.0f')(d.cpa)}`, dy: -10, fill: 'black', stroke: 'white', fy: 'Publisher' }), Plot.ruleX([forty_five_days_ago], {strokeDasharray: [5,2], strokeWidth: 0.5, facet: true}), Plot.textX([forty_five_days_ago], {frameAnchor: 'top', text: ['45 days ago'], rotate: 90, dx: 10, dy: 30, fontSize: 7.5}) ] })35:T437,Plot.plot({ width, marginTop: 50, insetLeft: 25, insetRight: 25, insetTop: 10, marginLeft: 50, facet: { marginRight: 150 }, fy: { padding: 0.2 }, color: { legend: true }, y: { nice: true, grid: true }, marks: [ Plot.frame(), Plot.gridX({interval: 'week', facet: true}), Plot.axisX({interval: 'month', label: null, facet: false}), Plot.lineY(clean_by_pub, { x: 'week_date', y: 'activations', stroke: 'Publisher', marker: true, strokeWidth: 1.25, //title: d => `${d3.format('$,.0f')(d.cpa)} CPA`, clip: 'frame', fy: 'Publisher' }), Plot.text(clean_by_pub, { x: 'week_date', y: 'activations', text: d => `${d3.format(',.0f')(d.activations)}`, dy: -10, fill: 'black', stroke: 'white', fy: 'Publisher' }), Plot.ruleX([forty_five_days_ago], {strokeDasharray: [5,2], facet: true}), Plot.textX([forty_five_days_ago], {frameAnchor: 'top', text: ['45 days ago'], rotate: 90, dx: 10, dy: 30, fontSize: 7.5}) ] })36:T43c,Plot.plot({ width, marginTop: 50, insetLeft: 25, insetRight: 25, insetTop: 10, marginLeft: 50, facet: { marginRight: 150 }, fy: { padding: 0.2 }, color: { legend: true }, y: { nice: true, grid: true, tickFormat: '.0s' }, marks: [ Plot.frame(), Plot.gridX({interval: 'week', facet: true}), Plot.axisX({interval: 'month', label: null, facet: false}), Plot.lineY(clean_by_pub, { x: 'week_date', y: 'leads', stroke: 'Publisher', marker: true, strokeWidth: 1.25, //title: d => `${d3.format('$,.0f')(d.cpa)} CPA`, clip: 'frame', fy: 'Publisher' }), Plot.text(clean_by_pub, { x: 'week_date', y: 'leads', text: d => `${d3.format(',.0f')(d.leads)}`, dy: -10, fill: 'black', stroke: 'white', fy: 'Publisher' }), Plot.ruleX([forty_five_days_ago], {strokeDasharray: [5,2], facet: true}), Plot.textX([forty_five_days_ago], {frameAnchor: 'top', text: ['45 days ago'], rotate: 90, dx: 10, dy: 30, fontSize: 7.5}) ] })37:T436,Plot.plot({ width, marginTop: 50, insetLeft: 25, insetRight: 25, insetTop: 10, marginLeft: 50, facet: { marginRight: 150 }, fy: { padding: 0.2 }, color: { legend: true }, y: { nice: true, grid: true, tickFormat: '.0s' }, marks: [ Plot.frame(), Plot.gridX({interval: 'week', facet: true}), Plot.axisX({interval: 'month', label: null, facet: false}), Plot.lineY(clean_by_pub, { x: 'week_date', y: 'bgc', stroke: 'Publisher', marker: true, strokeWidth: 1.25, //title: d => `${d3.format('$,.0f')(d.cpa)} CPA`, clip: 'frame', fy: 'Publisher' }), Plot.text(clean_by_pub, { x: 'week_date', y: 'bgc', text: d => `${d3.format(',.0f')(d.bgc)}`, dy: -10, fill: 'black', stroke: 'white', fy: 'Publisher' }), Plot.ruleX([forty_five_days_ago], {strokeDasharray: [5,2], facet: true}), Plot.textX([forty_five_days_ago], {frameAnchor: 'top', text: ['45 days ago'], rotate: 90, dx: 10, dy: 30, fontSize: 7.5}) ] })38:T468,viewof clean_by_day_ath = aq.from(raw_by_pub) .rename({master_publisher_aggregated: 'Publisher'}) .filter(d => d.Publisher != null && d.Publisher != 'CareerBuilder CPA' && d.Publisher != 'Jobcase CPA' && d.Publisher != 'Jobcase CPC' && d.Publisher != 'Upward.net CPA' && d.Publisher != 'Google Test CPC' && d.Publisher != 'Jobs2Careers CPA' && d.Publisher != 'Birddog CPA' && d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)' ) .derive({ Publisher: d => d.master_publisher == 'ZipRecruiter CPA' ? 'ZipRecruiter' : d.Publisher }) // .groupby('date') // .rollup({ // leads: d => op.sum(d.leads), // bgc: d => op.sum(d.bgc), // clicks: d => op.sum(d.clicks), // spend: d => op.sum(d.spend), // activations: d => op.sum(d.activations) // }) // .derive({ // cpl: d => d.spend / d.leads, // cpbgc: d => d.spend / d.bgc, // LtoBGC: d => d.bgc / d.leads, // cpa: d => d.spend / d.activations // }) .filter(d => d.Publisher == 'ZipRecruiter') .view()39:T41b,viewof cpbgc_wow_pub = clean_by_pub .derive({ publisher: d => op.includes(op.lower(d.Publisher),'zip') == true ? 'ZipRecruiter' : d.Publisher }) .groupby('week_date', 'publisher') .rollup({ spend: d => op.sum(d.spend), leads: d => op.sum(d.leads), bgc: d => op.sum(d.bgc), clicks: d => op.sum(d.clicks) }) .derive({ cpbgc: d => d.spend / d.bgc }) .groupby('week_date') .pivot('publisher','cpbgc') .derive({ wow_acx_cpbgc: d => (d['Appcast Exchange'] - op.lag(d['Appcast Exchange'],1)) / d['Appcast Exchange'], wow_cl_cpbgc: d => (d['Craigslist Official Posting'] - op.lag(d['Craigslist Official Posting'],1)) / d['Craigslist Official Posting'], wow_indeed_cpbgc: d => (d['Indeed CPC'] - op.lag(d['Indeed CPC'],1)) / d['Indeed CPC'], wow_zip_cpbgc: d => (d['ZipRecruiter'] - op.lag(d['ZipRecruiter'],1)) / d['ZipRecruiter'], }) .select(aq.not(1,2,3,4)) .fold(aq.range('wow_acx_cpbgc','wow_zip_cpbgc'), {as: ['pub','wow_cpbgc_percent']}) .impute({wow_cpbgc_percent: () => 0}) .view()3a:T4ae,viewof clean_by_pub = aq.from(raw_by_pub) .rename({master_publisher_aggregated: 'Publisher'}) .filter(d => d.Publisher != null && d.Publisher != 'CareerBuilder CPA' && d.Publisher != 'Jobcase CPA' && d.Publisher != 'Jobcase CPC' && d.Publisher != 'Upward.net CPA' && d.Publisher != 'Google Test CPC' && d.Publisher != 'Jobs2Careers CPA' && d.Publisher != 'Birddog CPA' && d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)' ) .derive({ week_date: aq.escape( d => d3.timeWeek(d.date) ) }) .derive({ Publisher: d => d.master_publisher == 'ZipRecruiter CPA' || d.Publisher == 'ZipRecruiter CPC' || d.Publisher == 'ZipRecruiter CPA' ? 'ZipRecruiter' : d.Publisher, }) .groupby('week_date','Publisher') .rollup({ leads: d => op.sum(d.leads), bgc: d => op.sum(d.bgc), clicks: d => op.sum(d.clicks), spend: d => op.sum(d.spend), activations: d => op.sum(d.activations) }) .derive({ cpl: d => d.spend / d.leads, cpbgc: d => d.spend / d.bgc, LtoBGC: d => d.bgc / d.leads, cpa: d => d.spend / d.activations }) .view()3b:[] 2d:{"title":"Lyft - Daily run rate & Weekly Trends","cells":[{"id":0,"value":"md `# Lyft - Daily run rate & Weekly Trends`","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"viewof file = Inputs.file({label: \"Load Daily csv\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1065,"value":"weekly_budget = 153000","pinned":true,"hidden":false,"mode":"ojs"},{"id":2711,"value":"hours_to_date","pinned":true,"hidden":false,"mode":"ojs"},{"id":587,"value":"md `## Spend`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2730,"value":"days_left_week = 4","pinned":true,"hidden":false,"mode":"ojs"},{"id":521,"value":"md `Today, our spend hourly run rate is: **${dollar_formatter(today_spend/hours_to_date)} p/hour**. At this rate, we will spend **${(dollar_formatter(run_rate))}** today (**${Math.abs(hours_to_date - 24)}** hours left in UTC). That would put us at **${dollar_formatter(run_rate + wtd_spend[0].weekly_spend)}** for the week and leave **${dollar_formatter((weekly_budget - (run_rate + wtd_spend[0].weekly_spend))/days_left_week)}** per day for the next few days.\n\nOur daily goal is to spend **${dollar_formatter(weekly_budget/7)}** based on our weekly budget of **${dollar_formatter(weekly_budget)}**.\n\nWe've spent **${dollar_formatter(today_spend)}** total today`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1291,"value":"weekly_projection = md `If we spend at the daily average over the next days, we'll come in around **${d3.format('$,.0f')(daily_spend[0].daily_avg_spend * 7)}** total spend for the week`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2204,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":2533,"value":"d3.utcSunday(new Date())","pinned":false,"hidden":false,"mode":"ojs"},{"id":1295,"value":"wtd_spend = aq.from(clean)\n .derive({\n day_of_week: d => op.utcdayofweek(d.date)\n })\n .rollup({\n weekly_spend: d => op.sum(d.spend),\n weekdays: d => op.array_agg(d.day_of_week),\n spend_array: d => op.array_agg(d.spend)\n })\n // .derive({\n // whole_days_spend: d => op.slice(d.spend_array,0,-1)\n // })\n // .derive({\n // test: d => op.sum(d.whole_days_spend)\n // })\n // .derive({\n // daily_avg_spend: aq.escape( d => d.weekly_spend / d.weekdays.at(-2) )\n // })\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1988,"value":"daily_spend = aq.from(clean.slice(0,-1))\n .derive({\n day_of_week: d => op.utcdayofweek(d.date)\n })\n .derive({\n daily_avg_spend: d => op.sum(d.spend) / op.count()\n })\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":577,"value":"spend = Plot.plot({\n marginTop: 50,\n marginLeft: 50,\n insetTop: 15,\n height: 250,\n label: 'Daily budget above/below',\n y: {\n domain: [-10000,10000],\n grid: true\n },\n x: {\n axis: false,\n //tickFormat: '%d'\n },\n // color: {\n // domain: ['above', 'below'],\n // range: ['#73c05d', '#d65c6e']\n // },\n marks: [\n Plot.ruleY([0]),\n Plot.rectX(clean, {\n x: 'date',\n y1: 0,\n y2: d => d.spend - d.daily_budget,\n insetRight: '40',\n insetLeft: '40',\n title: d => `${dollar_formatter(d.spend - d.daily_budget)}`\n })\n // Plot.areaY(clean, {\n // x: 'date',\n // y: 'spend',\n // opacity: 0.15,\n // }),\n // Plot.lineY(clean, {\n // x: 'date',\n // y: 'spend',\n // marker: true,\n // title: d => `${d.spend}`\n // }),\n // Plot.ruleY([weekly_budget/7], {\n // strokeDasharray: [3,3],\n // stroke: 'green'\n // })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":722,"value":"md `
`","pinned":false,"hidden":false,"mode":"ojs"},{"id":551,"value":"Inputs.table(wtd_spend, {\n width: 200,\n header: {\n weekly_spend: 'Total Spend WTD'\n },\n columns: ['weekly_spend'],\n format: {\n weekly_spend: d3.format('$,')\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":612,"value":"md `

\n\n## Leads`","pinned":false,"hidden":false,"mode":"ojs"},{"id":626,"value":"md `Today, our hourly lead run rate today is: **${(today_apps/hours_to_date).toFixed(2)} apps p/hour**. At this rate, we will get **${((app_run_rate.toFixed(0)))}** leads today.\n\n
`","pinned":false,"hidden":false,"mode":"ojs"},{"id":646,"value":"Plot.plot({\n marginTop: 50,\n marginLeft: 50,\n insetTop: 15,\n height: 200,\n// width: 200,\n y: {\n zero: true,\n grid: true\n },\n x: {\n tickFormat: '%A',\n ticks: 5\n },\n marks: [\n Plot.ruleY([0]),\n Plot.rectY(clean, {\n x: 'date',\n y: 'leads',\n interval: d3.utcDay,\n //insetLeft: '15',\n insetRight: '25',\n fill: 'firebrick',\n title: \"leads\"\n }),\n // Plot.text(clean, {\n // x: 'date',\n // y: d => d.leads,\n // text: d => `${d.leads.toFixed(0)}`,\n // fill: 'white',\n // interval: d3.utcDay,\n // dy: 25,\n // dx: 100,\n // //fontSize: 12.5 \n // })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":670,"value":"md `
\n## CPL`","pinned":false,"hidden":false,"mode":"ojs"},{"id":655,"value":"Plot.plot({\n marginTop: 50,\n marginLeft: 50,\n insetTop: 25,\n height: 300,\n grid: true,\n y: {\n zero: true,\n tickFormat: '$,.0f'\n },\n x: {\n tickFormat: '%a (%b-%d)',\n ticks: 3\n },\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(clean, {\n x: 'date',\n y: 'cpl',\n marker: true,\n stroke: 'firebrick'\n }),\n Plot.text(clean, {\n x: 'date',\n y: 'cpl',\n text: d => `${dollar_formatter(d.cpl)}`,\n dy: -10,\n fill: 'firebrick'\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1350,"value":"Plot.plot({\n height: 300,\n insetTop: 25,\n grid: true,\n y: {\n domain: [0,3],\n label: \"↑ Normalized Change (%) - Indexed to the first value available\",\n tickFormat: (f => x => f((x - 1) * 100))(d3.format(\"+d\"))\n },\n marks: [\n Plot.ruleY([1]),\n Plot.line(clean, Plot.normalizeY({\n x: \"date\",\n y: \"cpl\",\n marker: true,\n stroke: 'firebrick',\n //title: 'cpl'\n })),\n // Plot.text(stocks, Plot.selectLast(Plot.normalizeY({\n // x: \"Date\",\n // y: \"Close\",\n // z: \"Symbol\",\n // text: \"Symbol\",\n // textAnchor: \"start\",\n // dx: 3\n // })))\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":788,"value":"md `
\n## BGC`","pinned":false,"hidden":false,"mode":"ojs"},{"id":807,"value":"cpbgc = Plot.plot({\n marginTop: 50,\n marginLeft: 50,\n insetTop: 15,\n height: 200,\n // width: 200,\n y: {\n zero: true,\n grid: true,\n },\n x: {\n tickFormat: '%A',\n ticks: 5\n },\n marks: [\n Plot.ruleY([0]),\n Plot.rectY(clean, {\n x: 'date',\n y: 'bgc',\n interval: d3.utcDay,\n //insetLeft: '15',\n insetRight: '25',\n fill: 'steelBlue',\n title: 'bgc'\n }),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":792,"value":"Plot.plot({\n marginTop: 50,\n marginLeft: 50,\n insetTop: 25,\n height: 300,\n grid: true,\n y: {\n zero: true,\n tickFormat: '$,.0f'\n },\n x: {\n tickFormat: '%a (%b-%d)',\n ticks: 3\n },\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(clean, {\n x: 'date',\n y: 'cpbgc',\n marker: true,\n stroke: 'steelBlue'\n }),\n Plot.text(clean, {\n x: 'date',\n y: 'cpbgc',\n text: d => `${dollar_formatter(d.cpbgc)}`,\n dy: -10,\n fill: 'steelBlue'\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":846,"value":"Plot.plot({\n height: 300,\n insetTop: 25,\n grid: true,\n y: {\n domain: [0,3],\n label: \"↑ Normalized Change (%) - Indexed to the first value available\",\n tickFormat: (f => x => f((x - 1) * 100))(d3.format(\"+d\"))\n },\n marks: [\n Plot.ruleY([1]),\n Plot.line(clean, Plot.normalizeY({\n x: \"date\",\n y: \"cpbgc\",\n marker: true,\n stroke: 'steelBlue',\n //basis: 'median'\n //stroke: \"Symbol\"\n })),\n // Plot.text(stocks, Plot.selectLast(Plot.normalizeY({\n // x: \"Date\",\n // y: \"Close\",\n // z: \"Symbol\",\n // text: \"Symbol\",\n // textAnchor: \"start\",\n // dx: 3\n // })))\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1949,"value":"daily_budget = [\n {daily_amount: weekly_budget/7}\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":2010,"value":"md `
\n## Exported`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2013,"value":"Plot.plot({\n grid: true,\n height: 200,\n marginLeft: 50,\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(clean, {x: \"date\", y: \"exported\", marker: true})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":885,"value":"md `

\n\n## WTD\n\nOur current weekly budget is **${d3.format('$,.0f')(weekly_budget)}**.` // ${weekly_projection}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":908,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":1345,"value":"weekly_summary = Inputs.table(aq.from(clean)\n .rollup({\n avg_cpl: d => op.median(d.cpl),\n t_leads: d => op.sum(d.leads),\n avg_cpbgc: d => op.median(d.cpbgc),\n t_spend: d => op.sum(d.spend)\n })\n .objects(), {\n width: 500,\n layout: 'auto',\n format: {\n t_spend: dollar_formatter,\n avg_cpl: dollar_formatter,\n avg_cpbgc: dollar_formatter\n },\n header: {\n avg_cpl: 'Average CPL',\n t_leads: 'Leads',\n avg_cpbgc: 'Average CPBGC',\n t_spend: 'Spend'\n }\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":2362,"value":"md `

\n# by Campaign`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2413,"value":"viewof by_campy_start_date = Inputs.date({label: \"Date\", value: \"2023-04-10\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2615,"value":"viewof select_pub = Inputs.select(clean_by_campy_pub.objects().map(d => d.Publisher), {label: \"Select a Pub\", unique: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2662,"value":"viewof chart_width_ = Inputs.range([0, 1000], {label: \"Chart width\", step: 1})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2368,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":2683,"value":"Plot.plot({\n width: 150,\n height: 2700,\n insetTop: 15,\n fy: {\n padding: 0.1\n },\n marks: [\n Plot.frame(),\n Plot.rectY(by_campaign, {\n x: 'date',\n y: 'leads',\n fy: 'title',\n interval: d3.utcMonday,\n insetRigth: 5,\n insetLeft: 5,\n sort: {y: 'y', reduce: 'sum', reverse: true}\n })\n ]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":2677,"value":"viewof by_campaign = clean_by_campy_pub\n .filter(aq.escape(d => active_list.includes(d.title) && d.date >= by_campy_start_date))\n .filter(aq.escape(d => d.Publisher == select_pub))\n .derive({\n date: aq.escape(d => d3.utcMonday(d.date))\n })\n .groupby('date', 'title')\n .rollup({\n spend: d => op.sum(d.spend),\n leads: d => op.sum(d.leads)\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":2372,"value":"active_list = [\n \"ATL\",\n \"AUS\",\n \"BDL\",\n \"BDR\",\n \"BNA\",\n \"BOS\",\n \"BWI\",\n \"CHI\",\n \"CLT\",\n \"CMH\",\n \"DCA\",\n \"DEN\",\n \"DFW\",\n \"DTW\",\n \"HVN\",\n \"IAH\",\n \"LAS\",\n \"LAX\",\n \"MCO\",\n \"MIA\",\n \"OCX\",\n \"OLM\",\n\"PDX\",\n \"PHX\",\n \"RDU\",\n \"RIC\",\n \"SAN\",\n\"SAT\",\n\"SEA\",\n \"SFO\",\n \"SJC\",\n \"TAC\",\n \"TPA\",\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":1360,"value":"md `

\n# WoW`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1363,"value":"viewof thirty_day_lookback = Inputs.file({label: \"30 day lookback\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1394,"value":"viewof metric_selector = Inputs.radio(new Map([['Leads','leads'],['BGC','bgc'],['Activations','activations'], ['Spend','spend'], ['CPL', 'cpl'], ['CPBGC', 'cpbgc']]), {label: \"Select a Metric\", value: \"leads\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1876,"value":"viewof color_scale = Inputs.radio(new Map([[\"More is Good\",\"PiYG\"], [\"More is Bad\",\"BuRd\"]]), {label: \"Color scale\", value: \"PiYG\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2113,"value":"md `### by Day`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1735,"value":"viewof two_heats = html `\n

\n ${viewof calendar}\n

\n ${viewof week_calendar}\n

\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2116,"value":"md `### by Week`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2133,"value":"

\n ${wow_30}\n ${wow_30_amount}\n ${wow_30_perc}\n

","pinned":false,"hidden":false,"mode":"html"},{"id":2039,"value":"wow_30 = vl.markLine({point: {stroke: 'white', size: 50}, strokeWidth: 1})\n .data(clean_wow)\n .title(metric_selector + ' total WoW')\n .encode(\n vl.x().fieldT('week_date').title(null)\n .timeUnit('yearweek'),\n vl.y().fieldQ(metric_selector).title(null),\n vl.tooltip([\n {field: 'week_date', type: 'temporal'},\n {field: metric_selector, type: 'quantitative', format: ','}\n ])\n )\n .width(200)\n .height(150)\n .config({\n view: {stroke: null},\n axisX: {grid: false, tickCount: 3},\n axis: {labelFontWeight: 200, gridOpacity: 0.5},\n axisY: {domain: false},\n title: {fontWeight: 200}\n })\n .padding({top: 50, bottom: 50})\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2546,"value":"wow_30_athena = vl.markLine({point: {stroke: 'white', size: 50}, strokeWidth: 1})\n .data(clean_by_day_ath)\n .title(metric_selector + ' total WoW')\n .encode(\n vl.x().fieldT('date').title(null)\n .timeUnit('yearweek'),\n vl.y().sum(metric_selector).title(null),\n vl.tooltip([\n {field: 'week_date', type: 'temporal'},\n {field: metric_selector, type: 'quantitative', format: ','}\n ])\n )\n .width(200)\n .height(150)\n .config({\n view: {stroke: null},\n axisX: {grid: false, tickCount: 3},\n axis: {labelFontWeight: 200, gridOpacity: 0.5},\n axisY: {domain: false},\n title: {fontWeight: 200}\n })\n .padding({top: 50, bottom: 50})\n .render()","pinned":true,"hidden":false,"mode":"ojs"},{"id":2056,"value":"wow_30_amount = vl.markBar()\n .data(clean_wow)\n .title(metric_selector + ' difference WoW')\n .encode(\n vl.x().fieldT('week_date').title(null)\n .timeUnit('yearweek'),\n vl.y().fieldQ(metric_selector + '_diff').title(null),\n vl.tooltip([\n {field: metric_selector + '_diff', type: 'quantitative', format: ','}\n // {field: 'spend_diff', type: 'quantitative', format: '$,.0f', title: 'WoW Spend Difference'},\n // {field: 'spend_perc_diff', type: 'quantitative', format: '.1%', title: \"WoW Spend % Difference\"}, \n ])\n )\n .config({\n view: {stroke: null},\n bar: {width: {band: 0.5}},\n axis: {domain: false, labelFontWeight: 200, gridOpacity: 0.5},\n axisX: {grid: false, tickCount: 3},\n title: {fontWeight: 200}\n })\n .padding({top: 50, bottom: 50})\n .width(200)\n .height(150)\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2122,"value":"wow_30_perc = vl.markBar()\n .data(clean_wow)\n .title(metric_selector + ' percent difference WoW')\n .encode(\n vl.x().fieldT('week_date').title(null)\n .timeUnit('yearweek'),\n vl.y().fieldQ(metric_selector + '_perc_diff').title(null)\n .axis({format: '%'}),\n vl.tooltip([\n {field: metric_selector + '_perc_diff', type: 'quantitative', format: '.0%'}\n // {field: 'spend_diff', type: 'quantitative', format: '$,.0f', title: 'WoW Spend Difference'},\n // {field: 'spend_perc_diff', type: 'quantitative', format: '.1%', title: \"WoW Spend % Difference\"}, \n ])\n )\n .config({\n view: {stroke: null},\n bar: {width: {band: 0.5}},\n axis: {domain: false, labelFontWeight: 200, gridOpacity: 0.5},\n axisX: {grid: false, tickCount: 3},\n title: {fontWeight: 200}\n })\n .padding({top: 50, bottom: 50})\n .width(200)\n .height(150)\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2026,"value":"clean_wow = aq.from(clean30)\n .derive({\n week_date: aq.escape( d => d3.timeWeek(d.Date) )\n })\n .groupby('week_date')\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 .derive({\n cpl: d => d.spend / d.leads,\n cpbgc: d => d.spend / d.bgc,\n spend_diff: d => d.spend - op.lag(d.spend,1),\n leads_diff: d => d.leads - op.lag(d.leads,1),\n bgc_diff: d => d.bgc - op.lag(d.bgc,1),\n activations_diff: d => d.activations - op.lag(d.activations,1),\n //percentages\n spend_perc_diff: d => ( d.spend - op.lag(d.spend,1) ) / op.lag(d.spend,1),\n leads_perc_diff: d => ( d.leads - op.lag(d.leads,1) ) / op.lag(d.leads,1),\n bgc_perc_diff: d => ( d.bgc - op.lag(d.bgc,1) ) / op.lag(d.bgc,1),\n activations_perc_diff: d => ( d.activations - op.lag(d.activations,1) ) / op.lag(d.activations,1),\n \n })\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1383,"value":"// amounts calendar heatmap\nviewof calendar = Plot.plot({\n //marginLeft: 50,\n width: 200,\n facet: {\n data: clean30,\n y: d => d.Date.getUTCFullYear()\n },\n fy: {\n tickFormat: ''\n },\n y: {\n tickFormat: Plot.formatWeekday('en-US', 'short'),\n tickSize: 0,\n //domain: [1,2,3,4,5,6,0]\n },\n color: {\n legend: true,\n scheme: metric_selector == 'leads' ? 'reds' :\n metric_selector == 'bgc' ? 'blues' :\n metric_selector == 'activations' ? 'greens' :\n metric_selector == 'spend' ? 'greys' : \n metric_selector == 'cpl' ? 'oranges' :\n metric_selector == 'cpbgc' ? 'purples' : null\n },\n marks: [\n Plot.cell(clean30, {\n x: d => d3.utcWeek.count(d3.utcYear(d.Date), d.Date),\n y: d => d.Date.getUTCDay(),\n fill: metric_selector,\n title: metric_selector //`d => `${d.Date}` + metric_selector\n //title: 'Date'\n})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1475,"value":"// calendar heat difference\nviewof week_calendar = Plot.plot({\n width: 200,\n facet: {\n data: clean30,\n y: d => d.Date.getUTCFullYear()\n },\n fy: {\n tickFormat: ''\n },\n y: {\n tickFormat: Plot.formatWeekday('en-US', 'short'),\n tickSize: 0,\n },\n color: {\n legend: true,\n scheme: color_scale,\n label: 'Difference WoW (by weekday) ' + metric_selector\n },\n marks: [\n Plot.cell(clean30, heat_selector)\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1481,"value":"md `

\n# by Pub\n\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1533,"value":"md `First, we need an athena query\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\nand date between cast('2022-12-23' as date) and cast('2023-01-23' as date)\ngroup by date, job_groups.title, job_boards.master_publisher_aggregated\norder by date\n\n~~~\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1536,"value":"viewof file1 = Inputs.file({label: \"DW data\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1632,"value":"md `# YTD`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1590,"value":"md `### CPL by pub`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1646,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":1603,"value":"md `### CPBGC by pub`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1654,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":2325,"value":"md `### CPA by pub`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2316,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":2510,"value":"$35","pinned":false,"hidden":false,"mode":"ojs"},{"id":2464,"value":"forty_five_days_ago = d3.utcDay.offset(d3.utcDay(new Date(now)), -45)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1892,"value":"md `### Volume by pub`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1901,"value":"md `#### *Leads*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1894,"value":"$36","pinned":false,"hidden":false,"mode":"ojs"},{"id":1908,"value":"md `#### *BGC*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1905,"value":"$37","pinned":false,"hidden":false,"mode":"ojs"},{"id":88,"value":"
\n
\n
\n
\n
\n
\n
\n

","pinned":false,"hidden":false,"mode":"html"},{"id":20,"value":"md `## Appendix\n\n_Implementation & data stuff_`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2548,"value":"md `### By Campaign from athena`","pinned":true,"hidden":false,"mode":"ojs"},{"id":2550,"value":"$38","pinned":true,"hidden":false,"mode":"ojs"},{"id":2279,"value":"md `### By Pub data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1556,"value":"viewof cpl_wow_pub = clean_by_pub\n .groupby('week_date')\n .pivot('Publisher','cpl')\n // calculate the WoW percent change\n // the lag method has 3 inputs, but in this case I'm omitting the third one in order to replace the first value by 0. This so that the chart can be read as indexed from the first value (i.e. 0% change on the first value)\n .derive({\n wow_acx_cpl: d => (d['Appcast Exchange'] - op.lag(d['Appcast Exchange'],1)) / d['Appcast Exchange'],\n wow_cl_cpl: d => (d['Craigslist Official Posting'] - op.lag(d['Craigslist Official Posting'],1)) / d['Craigslist Official Posting'],\n wow_indeed_cpl: d => (d['Indeed CPC'] - op.lag(d['Indeed CPC'],1)) / d['Indeed CPC'],\n wow_zip_cpl: d => (d['ZipRecruiter'] - op.lag(d['ZipRecruiter'],1)) / d['ZipRecruiter'],\n })\n .select(aq.not(1,2,3,4))\n .fold(aq.range('wow_acx_cpl','wow_zip_cpl'), {as: ['pub','wow_cpl_percent']})\n .impute({wow_cpl_percent: () => 0})\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1593,"value":"$39","pinned":true,"hidden":false,"mode":"ojs"},{"id":2293,"value":"clean_by_pub_obj = clean_by_pub.objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":2366,"value":"viewof clean_by_campy_pub = aq.from(raw_by_pub)\n .rename({master_publisher_aggregated: 'Publisher'})\n .filter(d => d.Publisher != null &&\n d.Publisher != 'CareerBuilder CPA' &&\n d.Publisher != 'Jobcase CPA' &&\n d.Publisher != 'Jobcase CPC' &&\n d.Publisher != 'Upward.net CPA' &&\n d.Publisher != 'Google Test CPC' &&\n d.Publisher != 'Jobs2Careers CPA' &&\n d.Publisher != 'Birddog CPA' &&\n d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)'\n )\n .derive({\n week_date: aq.escape( d => d3.timeWeek(d.date) )\n })\n .derive({\n Publisher: d => d.master_publisher == 'ZipRecruiter CPA' || d.Publisher == 'ZipRecruiter CPC' || d.Publisher == 'ZipRecruiter CPA' ? 'ZipRecruiter' : d.Publisher,\n cpl: d => d.spend / d.leads,\n cpbgc: d => d.spend / d.bgc\n })\n // .groupby('week_date')\n // .pivot('Publisher', 'spend')\n // .derive()\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1543,"value":"$3a","pinned":true,"hidden":false,"mode":"ojs"},{"id":1747,"value":"md `### *Setup for heatmaps*`","pinned":true,"hidden":false,"mode":"ojs"},{"id":1706,"value":"heat_selector = {\n if (metric_selector == 'cpl') \n return cpl_heat;\n else if (metric_selector == 'cpbgc')\n return cpbgc_heat;\n else if (metric_selector == 'leads')\n return leads_heat;\n else if (metric_selector == 'bgc')\n return bgc_heat;\n else if (metric_selector == 'spend')\n return spend_heat;\n else if (metric_selector == 'activations')\n return activations_heat;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1711,"value":"cpl_heat = ({\n x: d => d3.utcWeek.count(d3.utcYear(d.Date), d.Date),\n y: d => d.Date.getUTCDay(),\n fill: (d,i) => i > 6 ? d.cpl - clean30[i - 7].cpl : NaN,\n title: (d,i) => i > 6 ? `${dollar_formatter(d.cpl - clean30[i - 7].cpl)}` : NaN,\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1716,"value":"cpbgc_heat = ({\n x: d => d3.utcWeek.count(d3.utcYear(d.Date), d.Date),\n y: d => d.Date.getUTCDay(),\n fill: (d,i) => i > 6 ? d.cpbgc - clean30[i - 7].cpbgc : NaN,\n title: (d,i) => i > 6 ? `${dollar_formatter(d.cpbgc - clean30[i - 7].cpbgc)}` : NaN,\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1723,"value":"leads_heat = ({\n x: d => d3.utcWeek.count(d3.utcYear(d.Date), d.Date),\n y: d => d.Date.getUTCDay(),\n fill: (d,i) => i > 6 ? d.leads - clean30[i - 7].leads : NaN,\n title: (d,i) => i > 6 ? d.leads - clean30[i - 7].leads : NaN,\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1728,"value":"bgc_heat = ({\n x: d => d3.utcWeek.count(d3.utcYear(d.Date), d.Date),\n y: d => d.Date.getUTCDay(),\n fill: (d,i) => i > 6 ? d.bgc - clean30[i - 7].bgc : NaN,\n title: (d,i) => i > 6 ? d.bgc - clean30[i - 7].bgc : NaN,\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1731,"value":"spend_heat = ({\n x: d => d3.utcWeek.count(d3.utcYear(d.Date), d.Date),\n y: d => d.Date.getUTCDay(),\n fill: (d,i) => i > 6 ? d.spend - clean30[i - 7].spend : NaN,\n title: (d,i) => i > 6 ? `${dollar_formatter(d.spend - clean30[i - 7].spend)}` : NaN,\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1856,"value":"activations_heat = ({\n x: d => d3.utcWeek.count(d3.utcYear(d.Date), d.Date),\n y: d => d.Date.getUTCDay(),\n fill: (d,i) => i > 6 ? d.activations - clean30[i - 7].activations : NaN,\n title: (d,i) => i > 6 ? d.activations - clean30[i - 7].activations : NaN,\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1539,"value":"md `### *Data by pub*`","pinned":true,"hidden":false,"mode":"ojs"},{"id":2715,"value":"aq.from(raw_by_pub)\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1541,"value":"raw_by_pub = file1.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":2596,"value":"aq.from(raw_by_pub)\n .filter(d => op.includes(op.lower(d.master_publisher),'zip') == true)\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":198,"value":"### *Data Week*","pinned":false,"hidden":false,"mode":"md"},{"id":682,"value":"Inputs.table(clean)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1132,"value":"// playground\naq.from(data)\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":508,"value":"clean = aq.from(data)\n .derive({\n date: d => op.parse_date(d.Date),\n spend: d => +op.replace(d.Actual, ',',''),\n leads: d => +op.replace(d['Total Leads'], ',',''),\n bgc: d => +d.BGC,\n exported: d => +op.replace(d['Exported Jobs'], ',','')\n })\n .derive({\n day_week: d => op.utcdayofweek(d.date),\n cpl: d => d.spend / d.leads,\n cpbgc: d => d.spend / d.bgc,\n LtoBGC: d => d.bgc / d.leads,\n })\n .derive({\n cpl_perc_diff: d => (d.cpl / op.lag(d.cpl,1,0)) - 1,\n cpbgc_perc_diff: d => (d.cpbgc / op.lag(d.cpbgc,1,0)) - 1\n })\n .select('date', 'day_week','spend', 'leads', 'cpl', 'cpl_perc_diff', 'LtoBGC', 'bgc','cpbgc', 'cpbgc_perc_diff', 'exported')\n .orderby('date')\n .derive({\n daily_budget: aq.escape( d => weekly_budget / 7 )\n })\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1367,"value":"md `### *Data 30 days*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1371,"value":"raw_30 = thirty_day_lookback.csv({typed: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1373,"value":"clean30 = aq.from(raw_30)\n .derive({\n leads: d => +op.replace(d['Total Leads'], ',',''),\n bgc: d => +d.BGC,\n spend: d => +op.replace(d.Actual, ',', ''),\n activations: d => d.Activations\n })\n .derive({\n cpl: d => d.spend / d.leads,\n cpbgc: d => d.spend / d.bgc,\n LtoBGC: d => d.bgc / d.leads,\n })\n .orderby('Date')\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":618,"value":"md `

\n#### **spend variables**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":563,"value":"dollar_formatter = d3.format('$,.2f')","pinned":true,"hidden":false,"mode":"ojs"},{"id":568,"value":"run_rate = (today_spend/hours_to_date) * 24","pinned":true,"hidden":false,"mode":"ojs"},{"id":524,"value":"today_spend = aq.from(clean).filter(aq.escape(d => +d3.utcDay(d.date) === +today_date)).get('spend',0)","pinned":true,"hidden":false,"mode":"ojs"},{"id":621,"value":"md `#### **app variables**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":632,"value":"today_apps = aq.from(clean).filter(aq.escape(d => +d3.utcDay(d.date) === +today_date)).get('leads',0)","pinned":true,"hidden":false,"mode":"ojs"},{"id":637,"value":"app_run_rate = (today_apps/hours_to_date) * 24","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"data = file.csv()","pinned":true,"hidden":false,"mode":"ojs"},{"id":187,"value":"### *Secondary data sources*","pinned":false,"hidden":false,"mode":"md"},{"id":513,"value":"hours_to_date = new Date(now).getUTCHours()","pinned":true,"hidden":false,"mode":"ojs"},{"id":526,"value":"today_date = d3.utcDay(new Date(now))","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":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":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":742,"value":"perc_formatter = d3.format('.1%')","pinned":true,"hidden":false,"mode":"ojs"},{"id":208,"value":"### *Chart configuration*","pinned":false,"hidden":false,"mode":"md"},{"id":202,"value":"clean_config = ({\n view: { stroke: null },\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":false,"hidden":false,"mode":"ojs"}],"files":"$W3b","stdlib":"1","titling":"auto"}