2f:T43e,by_acx_pub_by_day = aq.from(clean) .rename({master_publisher_aggregated: 'Publisher'}) .filter(d => d.clicks > 0) .filter(d => d.Publisher != 'Upward.net CPA' && d.Publisher != 'Jobs2Careers CPA' && d.Publisher != 'Google Test CPC' && d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)' && d.Publisher != 'Jobcase CPA' && d.Publisher != 'CareerBuilder CPA' && d.Publisher != 'Birddog CPA'&& d.Publisher != null ) .derive({ Publisher: d => d.jb_id == 14658 || d.jb_id == 30409 || d.Publisher == 'ZipRecruiter CPA' || d.Publisher == 'ZipRecruiter CPC' ? 'ZipRecruiter' : d.Publisher == 'Jobcase CPC' ? 'Appcast Exchange' : d.Publisher }) .groupby('date', 'Publisher', 'master_publisher') .rollup({ clicks: d => op.sum(d.clicks), applies: d => op.sum(d.applies), qualified: d => op.sum(d.qualified) }) .derive({ CtoL: d => d.applies / d.clicks, Ltoqualified: d => d.qualified / d.applies }) .orderby('date') .objects()30:T409,by_pub_by_day = aq.from(clean) .rename({master_publisher_aggregated: 'Publisher'}) .filter(d => d.Publisher != 'Upward.net CPA' && d.Publisher != 'Jobs2Careers CPA' && d.Publisher != 'Google Test CPC' && d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)' && d.Publisher != 'Jobcase CPA' && d.Publisher != 'CareerBuilder CPA' && d.Publisher != 'Birddog CPA'&& d.Publisher != null ) .derive({ Publisher: d => d.jb_id == 14658 || d.jb_id == 30409 || d.Publisher == 'ZipRecruiter CPA' || d.Publisher == 'ZipRecruiter CPC' ? 'ZipRecruiter' : d.Publisher == 'Jobcase CPC' ? 'Appcast Exchange' : d.Publisher }) .groupby('date', 'Publisher') .rollup({ clicks: d => op.sum(d.clicks), applies: d => op.sum(d.applies), qualified: d => op.sum(d.qualified) }) .derive({ CtoL: d => d.applies / d.clicks, Ltoqualified: d => d.qualified / d.applies }) .orderby('date') .objects()31:T400,by_pub_by_week = aq.from(clean) .rename({master_publisher_aggregated: 'Publisher'}) .filter(d => d.Publisher != 'Upward.net CPA' && d.Publisher != 'Jobs2Careers CPA' && d.Publisher != 'Google Test CPC' && d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)' && d.Publisher != 'Jobcase CPA' && d.Publisher != 'CareerBuilder CPA' && d.Publisher != 'Birddog CPA'&& d.Publisher != null ) .derive({ Publisher: d => d.jb_id == 14658 || d.jb_id == 30409 || d.Publisher == 'ZipRecruiter CPA' || d.Publisher == 'ZipRecruiter CPC' ? 'ZipRecruiter' : d.Publisher == 'Jobcase CPC' ? 'Appcast Exchange' : d.Publisher }) .derive({ week_date: aq.escape(d => d3.utcMonday(d.date)) }) .groupby('week_date', 'Publisher') .rollup({ clicks: d => op.sum(d.clicks), applies: d => op.sum(d.applies) }) .derive({ CtoL: d => d.applies / d.clicks }) .orderby('week_date') .objects()32:[{"id":"ec39da6698d8f9c4a448402295b1c0ae5d228945625c2bdb9ae82d1019cb65b8e64c76d40f2779514b5c86b9fc97835ec14ea6bea12d7580650f461e2baa10be","href":"https://static.observableusercontent.com/files/ec39da6698d8f9c4a448402295b1c0ae5d228945625c2bdb9ae82d1019cb65b8e64c76d40f2779514b5c86b9fc97835ec14ea6bea12d7580650f461e2baa10be","name":"Lyft Timeline (2).xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1686079283175.469,"size":17528}] 2d:{"title":"Aya Healthcare - First Look","cells":[{"id":0,"value":"md `# Aya Healthcare - First Look`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1427,"value":"md `Download the .csv file from [here](https://stepstonecore.sharepoint.com/:x:/s/AppcastServiceDeliveryTraining/sckc/EdtPQvEZzctPubp-53OxxDMBERxy4nkRiyNr5MrKrGP4Rw?e=LF2WBq)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"viewof file = Inputs.file({label: \"Load .csv\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":824,"value":"viewof start_date = Inputs.date({label: \"Start date\", value: \"2023-03-01\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":830,"value":"viewof end_date = Inputs.date({label: \"End date\", value: \"2023-06-03\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1248,"value":"md `## Account Characteristics`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1315,"value":"md `\n- Account is in PST\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1236,"value":"Plot.plot({\n height: chart_height,\n marks: [\n Plot.ruleY([0]),\n Plot.gridY({opacity: 0.05}),\n Plot.lineY(campaign_count, {x: \"date\", y: \"spending_campaign_count\", strokeOpacity: 0.1}),\n Plot.lineY(campaign_count, Plot.windowY({x: \"date\", y: \"spending_campaign_count\", k: 7})),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1273,"value":"md `The number of campaigns spending increased around mid-May from ~40 to ~100 and after going down to ~70 it is back to ~80 in June.\n\n**Aya had a spike in demand Mid-May**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":931,"value":"viewof metric_selector = Inputs.radio(['spend', 'clicks', 'applies'], {label: \"metric\", value: \"applies\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1252,"value":"Plot.plot({\n height: chart_height,\n marginLeft: 40,\n y: {\n tickFormat: metric_selector == 'spend' ? '$.0s' : ''\n },\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(campaign_count, {x: \"date\", y: metric_selector, strokeOpacity: 1}),\n //Plot.lineY(campaign_count, Plot.windowY({x: \"date\", y: \"campaign_count\", k: 7})),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1282,"value":"md `Given the activation of more campaigns, apply volume increased late may. As a result of this, may is the second-highest spending month YTD (January only above by about $1k)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1380,"value":"Inputs.table(aq.from(campaign_count)\n .derive({\n date: aq.escape(d => d3.utcMonth(d.date))\n })\n .groupby('date')\n .rollup({\n spend: d => op.sum(d.spend)\n })\n .derive({\n month: aq.escape(d => d3.utcFormat('%B')(d.date))\n })\n .select(aq.not('date'))\n .relocate('month', {before: 'spend'})\n .objects(), {\n format: {\n spend: d3.format('$,.0f')\n },\n layout: 'auto',\n width: 200\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":1255,"value":"Plot.plot({\n height: chart_height,\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(campaign_count, {x: \"date\", y: \"cpa\", strokeOpacity: 0.1}),\n Plot.lineY(campaign_count, Plot.windowY({x: \"date\", y: \"cpa\", k: 7})),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1285,"value":"md `The increase in demand came with an increase in apply cost: the CPA was at its lowest YTD at the beginning of May (~$20) and ended at its highest YTD at the end of May (~$50).\n\n**Cost has increased and it seems to have remained there**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1267,"value":"Plot.plot({\n height: chart_height,\n y: {\n percent: true\n },\n marks: [\n Plot.ruleY([0]),\n Plot.gridY({opacity: 0.05}),\n Plot.lineY(campaign_count, {x: \"date\", y: \"CtoA\", strokeOpacity: 0.1}),\n Plot.lineY(campaign_count, Plot.windowY({x: \"date\", y: \"CtoA\", k: 7})),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1289,"value":"md `Click to Apply CR % is trending downwards YTD, it is particularly low towards the end of the month of May (~1.75%).\n\n**Is the offer competitive enough?**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1231,"value":"campaign_count = clean\n .filter(d => d.spend > 0)\n .groupby('date')\n .rollup({\n spending_campaign_count: d => op.distinct(d.title),\n spend: d => op.sum(d.spend),\n applies: d => op.sum(d.applies),\n clicks: d => op.sum(d.clicks),\n qualified: d => op.sum(d.qualified)\n })\n .derive({\n cpa: d => d.spend / d.applies,\n CtoA: d => d.applies / d.clicks\n })\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1299,"value":"md `

\n\n## Internal Account Changes`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1302,"value":"viewof file1 = Inputs.file({label: \"Configuration Audit\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1330,"value":"Plot.plot({\n //width,\n height: chart_height,\n color: {\n legend: true\n },\n marks: [\n Plot.ruleY([0]),\n Plot.gridX({interval: 'month'}),\n Plot.rectY(users, Plot.binX({y: 'sum'},{x: \"date\", y: \"count\", fill: 'department', thresholds: d3.utcWeek, insetRight: 4, insetLeft: 4}))\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1321,"value":"users = clean_config_audit\n .derive({\n department: d => d.User == 'jason.kwok@appcast.io' || d.User == 'Jack.Russell@appcast.io' || d.User == 'thomas.abbasi@appcast.io' ? 'AM' :\n d.User == 'Jose.Castillo@appcast.io' || d.User == 'joshua.goodrich@appcast.io' || d.User == 'nicole.stewart@appcast.io' || d.User == 'ivona.mihaescu@appcast.io' ? 'AdOps' : \n d.User == 'amy.muzik@appcast.io' || d.User == 'dan.larsen@appcast.io' ? 'Implementation' : 'Other'\n })\n .groupby('date', 'department')\n .count()\n .objects()","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":1410,"value":"md `### *Chart Configs*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1239,"value":"viewof chart_height = Inputs.range([0, 500], {label: \"Chart height\", step: 1, value: 200})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1306,"value":"md `### *Config Audit*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1353,"value":"user_list = clean_config_audit\n .select('User')\n .dedupe()\n .derive({\n department: d => d.User == 'jason.kwok@appcast.io' || d.User == 'Jack.Russell@appcast.io' || d.User == 'thomas.abbasi@appcast.io' ? 'AM' :\n d.User == 'Jose.Castillo@appcast.io' || d.User == 'joshua.goodrich@appcast.io' || d.User == 'nicole.stewart@appcast.io' || d.User == 'ivona.mihaescu@appcast.io' ? 'AdOps' : \n d.User == 'amy.muzik@appcast.io' || d.User == 'dan.larsen@appcast.io' ? 'Implementation' : 'Other'\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1313,"value":"viewof clean_config_audit = aq.from(raw_config_audit)\n .rename({'Time (Pacific Time (US & Canada) -8)': 'date'})\n .orderby('date')\n .derive({date: d => op.parse_date(d.date)})\n .derive({\n date: aq.escape(d => d3.utcDay(d.date))\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1310,"value":"raw_config_audit = file1.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":198,"value":"### *Data*","pinned":false,"hidden":false,"mode":"md"},{"id":739,"value":"timeline = aq.from(raw_timeline.sheet('SC2 Timeline', {headers: true}))\n .derive({Date: d => op.parse_date(d.Date)})\n .filter(aq.escape(d => d.Date >= start_date && d.Date <= end_date))\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":737,"value":"raw_timeline = FileAttachment(\"Lyft Timeline (2).xlsx\").xlsx()","pinned":true,"hidden":false,"mode":"ojs"},{"id":897,"value":"$2f","pinned":true,"hidden":false,"mode":"ojs"},{"id":762,"value":"$30","pinned":true,"hidden":false,"mode":"ojs"},{"id":812,"value":"$31","pinned":true,"hidden":false,"mode":"ojs"},{"id":1186,"value":"md `## Main data source`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1189,"value":"md `Query used:\n\n~~~sql\n\nselect jd.date, e.name employer, jb.master_publisher_aggregated, jb.master_publisher, jb.id jb_id, jd.job_group_id, jg.title title, sum(jd.impressions) impressions, sum(jd.clicks_paid) clicks, sum(jd.applies_paid) applies, round(sum(jd.spend),2) spend, sum(jd.qualified) qualified, sum(hired) hired\nfrom cc_warehouse.jobdata jd\nleft join cc_warehouse.employers e\non e.id = jd.employer_id\nleft join cc_warehouse.job_boards jb\non jb.id = jd.job_board_id\nleft join cc_warehouse.job_groups jg\non jg.id = jd.job_group_id\nwhere jd.employer_id in (12033)\nand jd.date between cast('2023-01-01' as date) and cast('2023-06-06' as date)\ngroup by jd.date, e.name, jb.master_publisher_aggregated, jb.master_publisher, jb.id, jd.job_group_id, jg.title\norder by jd.date asc\n\n~~~`","pinned":true,"hidden":false,"mode":"ojs"},{"id":508,"value":"viewof clean = aq.from(raw_data)\n .filter(aq.escape(d => d.date >= start_date && d.date <= end_date))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"raw_data = file.csv({typed: true})","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":710,"value":"appcast_color_palette = ['#05062C', '#3138BB','#FF7D6F', '#AF9BFC', '#C7FFE2', '#127FFC', '#FF9B77', '#B7D8FE', '#DAE2FD', '#FAFAFA']","pinned":false,"hidden":false,"mode":"ojs"},{"id":717,"value":"Plot.cell(appcast_color_palette, {x: d => d, fill: d => d}).plot()","pinned":false,"hidden":false,"mode":"ojs"},{"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":"$W32","stdlib":"1","titling":"auto"}