2f:T519,viewof goal_cpl = Inputs.table(clean .groupby('title') .rollup({ leads: d => op.sum(d.leads), activations: d => op.sum(d.activations), spend: d => op.sum(d.spend) }) .derive({ l_per_activ: d => d.leads / d.activations, cpl: d => d.spend / d.leads }) .join(master, ['title', 'Campaign']) .derive({ goal_cpl: d => d.TCPA / d.l_per_activ }) .relocate(['goal_cpl'], {after: 'cpl'}) .derive({ cpl_diff: d => d.cpl - d.goal_cpl }) .derive({ cpl_diff_perc: d => d.cpl_diff / d.goal_cpl }) .derive({ change: d => d.cpl_diff_perc < -0.5 ? 'raise' : d.cpl_diff_perc > 0.5 ? 'reduce' : 'same' }) .derive({ adjust_cpl_10perc: d => d.change == 'raise' ? d.goal_cpl * 1.1 : d.change == 'reduce' ? d.goal_cpl * 0.9 : d.change == 'same' ? d.goal_cpl : null }) .join_left(batcher_clean, ['Campaign', 'Campaign']) .select(aq.not(aq.range(14,21))) .orderby('cpl_diff_perc') .select(aq.not('needs to be active?')) .rename({'Bid': 'Current Bid'}) .select(aq.not('TCPA')) .derive({ curr_vs_adj10: d => d.adjust_cpl_10perc - d['Current Bid'] }) .objects(), { layout: 'auto', format: { curr_vs_adj10: d3.format('+.1f') } })30:[] 2d:{"title":"$$70k - Setup","cells":[{"id":0,"value":"md `# $70k - Setup`","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"viewof file = Inputs.file({label: \"Data\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":618,"value":"\"6c6993f3-99a0-491e-9b46-d5cca8f3c6ea.csv\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":712,"value":"viewof file1 = Inputs.file({label: \"70k master\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":714,"value":"raw_master = file1.csv()","pinned":false,"hidden":false,"mode":"ojs"},{"id":869,"value":"viewof file2 = Inputs.file({label: \"batcher\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":874,"value":"raw_batcher = file2.csv({typed: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":765,"value":"md `First we need the master list of campaigns that have to be active:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":716,"value":"viewof master = aq.from(raw_master)\n .filter(d => d['needs to be active?'] == 'TRUE')\n .select(0,2,4)\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":770,"value":"md `Once we have the list, we can see the overall spend for the markets that should be active vs the ones that should not:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":728,"value":"vl.markLine({point: {size: 30, stroke: 'white'}, strokeWidth: 1})\n .data(clean_spend)\n .encode(\n vl.x().fieldT('week_date'),\n vl.y().sum('leads'),\n //vl.y().sum('spend'),\n vl.color().fieldN('segment'),\n vl.tooltip([\n {field: 'week_date', type: 'temporal', format: '%b %d (W%U)'},\n {field: 'spend', type: 'quantitative', aggregate: 'sum', format: '$,.0f', title: 'Spend'},\n {field: 'leads', type: 'quantitative', aggregate: 'sum'}\n ])\n )\n .config({\n axis: {gridOpacity: 0.25, labelFontWeight: 200},\n view: {stroke: null}\n })\n .width(900)\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":938,"value":"clean_spend = aq.from(raw_data)\n //.filter(aq.escape(d => active_list.includes(d.title)))\n .derive({\n segment: aq.escape(d => active_list.includes(d.title) == true ? 'should be active' : 'should not active')\n })\n // .filter(d => d.master_publisher_aggregated == 'Appcast Exchange' || \n // d.master_publisher_aggregated == 'Indeed CPC' || \n // d.master_publisher_aggregated == 'ZipRecruiter CPC'\n // )\n .derive({\n week_date: aq.escape( d => d3.utcMonday(d.date))\n })\n .groupby('week_date', 'segment') //, 'master_publisher_aggregated')\n .rollup({\n spend: d => op.sum(d.spend),\n leads: d => op.sum(d.leads)\n })\n .derive({\n cpl: d => d.spend / d.leads\n })\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":773,"value":"md `In the past 4 weeks, it looks like we are spending between $40k and $60k for the campaigns that should be active. This includes **all publishers**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":776,"value":"md `
\n\n### *CPA*\n\nNow, we also need to set the CPL bids for ACX based on the TCPA. So how do the new TCPA goals compare to the CPA actuals by campaign in the past 6 months or so?\n\n*All data below is filtered to include only ACX and the date range is from October until February (i.e. 45 days ago)*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":746,"value":"vl.markRect({tooltip: true})\n .data(cpa_month)\n .encode(\n vl.x().fieldT('month_date')\n .timeUnit('utcyearmonth'),\n vl.y().fieldN('Campaign')\n .sort(vl.median('cpa').order('descending')),\n vl.color({format: '$,.0f'}).fieldQ('cpa_diff')\n .scale({\n type: 'diverging',\n scheme: 'redblue',\n reverse: true,\n domainMid: 0\n }),\n vl.tooltip([\n {field: 'cpa_diff', type: 'quantitative', format: '+$,.0f'},\n {field: 'cpa', type: 'quantitative', format: '$,.0f'},\n {field: 'TCPA', type: 'quantitative', format: '$,.0f'}\n ])\n )\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":736,"value":"viewof cpa_month = clean\n .filter(d => d.master_publisher_aggregated == 'Appcast Exchange')\n .derive({\n month_date: aq.escape( d => d3.utcMonth(d.date) )\n })\n .groupby('month_date', 'title')\n .rollup({\n activations: d => op.sum(d.activations),\n spend: d => op.sum(d.spend)\n })\n .derive({\n cpa: d => d.spend / d.activations\n })\n .join(master, ['title', 'Campaign'])\n .derive({\n cpa_diff: d => d.cpa - d.TCPA\n })\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":804,"value":"md `A fair amount of variance MoM, but in general, one could say we run hot for half and cool for the other half.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":807,"value":"md `
\n\n### *CPL*\n\nNow for the CPL, this is how the actual differs from the goal:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":792,"value":"vl.markRect({tooltip: true, height: {band: 0.9}})\n .data(goal_cpl)\n .encode(\n vl.x().fieldQ('cpl_diff')\n .axis({orient: 'top'}),\n vl.y().fieldN('title')\n .sort(vl.fieldQ('cpl_diff')),\n vl.color({format: '$,.0f'}).fieldQ('cpl_diff')\n .scale({\n type: 'diverging',\n scheme: 'redblue',\n reverse: true,\n domainMid: 0\n }),\n vl.tooltip([\n {field: 'cpl_diff', type: 'quantitative', format: '+$,.0f'},\n {field: 'cpl', type: 'quantitative', format: '$,.0f'},\n {field: 'goal_cpl', type: 'quantitative', format: '$,.0f'}\n ])\n )\n .config({\n view: {stroke: null}\n })\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":848,"value":"md `Instead of actual, this is the percent difference from the CPL`","pinned":false,"hidden":false,"mode":"ojs"},{"id":830,"value":"vl.markRect({tooltip: true, height: {band: 0.9}})\n .data(goal_cpl)\n .encode(\n vl.x().fieldQ('cpl_diff_perc')\n .axis({format: '%', orient: 'top'}),\n vl.y().fieldN('Campaign')\n .sort(vl.fieldQ('cpl_diff_perc')),\n vl.color({format: '$,.0f'}).fieldQ('cpl_diff_perc')\n .scale({\n type: 'diverging',\n scheme: 'redblue',\n reverse: true,\n domainMid: 0\n })\n .legend({\n format: '%'\n }),\n vl.tooltip([\n {field: 'cpl_diff_perc', type: 'quantitative', format: '+.0%'},\n {field: 'cpl', type: 'quantitative', format: '$,.0f'},\n {field: 'goal_cpl', type: 'quantitative', format: '$,.0f'}\n ])\n )\n .config({\n view: {stroke: null}\n })\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":911,"value":"md `
\n\nBased on the above, we can categorize whether our CPL bid should be raised, left as is or reduced. In order to start slow, a 10% increase/decrease should be a good starting point to adjust. These adjustments are proposed for those CPL's that are +/- 50% from the CPL goal. See table below:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":779,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":838,"value":"aq.from(goal_cpl)\n .groupby('change')\n .count()\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":914,"value":"md `Lastly, our table above also includes the current bid (from batcher) so we can see the difference between the bid and the proposed change (adjusted cpl)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":851,"value":"
\n
\n
\n

","pinned":false,"hidden":false,"mode":"html"},{"id":855,"value":"md `## now Craigslist`","pinned":false,"hidden":false,"mode":"ojs"},{"id":863,"value":"vl.markLine()\n .data(cl_clean)\n .encode(\n vl.x().fieldT('date'),\n vl.y().sum('spend')\n )\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":858,"value":"viewof cl_clean = aq.from(raw_data)\n .join(master, ['title', 'Campaign'])\n .filter(d => d.master_publisher_aggregated === 'Craigslist Official Posting')\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":88,"value":"
\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":198,"value":"### *Data*","pinned":false,"hidden":false,"mode":"md"},{"id":508,"value":"viewof clean = aq.from(raw_data)\n .filter(aq.escape(d => d.date <= forty_five_days_ago))\n .filter(aq.escape(d => active_list.includes(d.title)))\n //.join_left(master, ['title', 'Campaign'])\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":882,"value":"viewof batcher_clean = aq.from(raw_batcher)\n .filter(d => d.Publisher == 'Xcelerate Exchange')\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":816,"value":"forty_five_days_ago = d3.utcDay(d3.utcDay.offset(new Date(), -45))","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"raw_data = file.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":930,"value":"active_list = master\n .array('Campaign')","pinned":true,"hidden":false,"mode":"ojs"},{"id":187,"value":"### *Secondary data sources*","pinned":false,"hidden":false,"mode":"md"},{"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":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":59,"value":"import { aq, op } from '@uwdata/arquero'","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":514,"value":"import {textcolor} from \"@observablehq/text-color-annotations-in-markdown\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":702,"value":"import {toc} from \"@nebrius/indented-toc\"","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":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"},{"id":632,"value":"// sample of how to place html blocks in a grid\n\n// html `

\n// ${viewof spend}\n// ${viewof cpl}\n// ${viewof cpc}\n//

`","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W30","stdlib":"1","titling":"auto"}