2f:T404,monthly_spend = Plot.plot({ //width: 700, marginLeft: 325, marginBottom: 100, marginRight: 50, insetLeft: 0, insetBottom: 10, insetRight: 15, y: { tickSize: 0, label: null }, x: { domain: [0,1] }, color: { range: [appcast_color_palette[5], appcast_color_palette[2]] }, marks: [ Plot.axisX({interval: 0.2, tickFormat: '%', labelOffset: 50, label: 'Percent to budget'}), Plot.gridX({interval: 0.2}), Plot.ruleY(monthly_budget_by_campaign, { y: 'parent_campaign', x: 'percent_to_budget', stroke: 'Status', strokeWidth: d => d.exported_jobs > 0 ? 5 : 1, sort: {y: 'x', reverse: true} }), Plot.tickX(monthly_budget_by_campaign, { y: 'parent_campaign', x: 'percent_to_budget', r: 1, stroke: 'Status' }), Plot.textX(monthly_budget_by_campaign, { y: 'parent_campaign', x: 'percent_to_budget', text: d => d3.format('.0%')(d.percent_to_budget), dx: 15, fill: 'Status' }), ] })30:T4d5,viewof clean = aq.from(raw_data) .filter(aq.escape(d => d.date >= start_date && d.date <= end_date)) .derive({ business_line: d => op.includes(op.lower(d.parent_campaign), 'allied') == true ? 'Allied' : op.includes(op.lower(d.parent_campaign), 'school') == true ? 'schools/education' : op.includes(op.lower(d.parent_campaign), 'therapy/rehab') == true ? 'therapy/rehab' : op.includes(op.lower(d.segment), 'per diem') == true ? 'per diem' : op.includes(op.lower(d.parent_campaign), 'rn') == true ? 'RN' : op.includes(op.lower(d.segment), 'interim') == true ? 'Interim Leadership' : 'Other' }) .derive({ parent_campaign: d => d.business_line == 'per diem' ? d.segment : d.parent_campaign }) .derive({ segment_type: d => op.includes(op.lower(d.segment), 'low submissions') == true ? 'low submissions' : op.includes(op.lower(d.segment), 'strategic') == true ? 'strategic' : op.includes(op.lower(d.segment), 'catch') == true ? 'catch all' : 'Other' }) .rename({parent_id: 'campaign_id'}) .orderby(aq.desc('parent_campaign')) .view()31:[{"id":"b5b9ebc4532e9fd0cbcde9d6817630e069d48d012287c08d32e1b271577b2eee551c3a19c86684366d1006c3d9cb9116ab6959c8a73a8027e0e8c1cf241a91fc","href":"https://static.observableusercontent.com/files/b5b9ebc4532e9fd0cbcde9d6817630e069d48d012287c08d32e1b271577b2eee551c3a19c86684366d1006c3d9cb9116ab6959c8a73a8027e0e8c1cf241a91fc","name":"Campaigns-Premium - Campaigns-Premium (2).csv","type":"text/csv","lastModified":1687786672473.416,"size":4789642},{"id":"a6f7e54035171f6bb795037576c45fd053d705645329853df08648d64827f713c7acfc0514e987e0396ec29600bd39219f361d439169376c6306517fbc1e62e1","href":"https://static.observableusercontent.com/files/a6f7e54035171f6bb795037576c45fd053d705645329853df08648d64827f713c7acfc0514e987e0396ec29600bd39219f361d439169376c6306517fbc1e62e1","name":"Campaigns-Premium - Campaigns-Premium (1).csv","type":"text/csv","lastModified":1687532890415.646,"size":4784176},{"id":"f1d45e6a87d20643668464245d11edc4389bcb7fd240f6e441f88a65d324890c824079b6fa5740b135a1f105518247bccb2217d58f1bae248c5bcda2925790ee","href":"https://static.observableusercontent.com/files/f1d45e6a87d20643668464245d11edc4389bcb7fd240f6e441f88a65d324890c824079b6fa5740b135a1f105518247bccb2217d58f1bae248c5bcda2925790ee","name":"Campaigns-Premium - Campaigns-Premium (3).csv","type":"text/csv","lastModified":1687890625326.618,"size":4790545},{"id":"5d100cfde4a4ff404ba9b45b100b268643ff9397711c138bad4eda19c8df49d87dc85c1666c7443e346d5a7307b18c73b4d17dfa3ea143f774f5c359a05ec7c4","href":"https://static.observableusercontent.com/files/5d100cfde4a4ff404ba9b45b100b268643ff9397711c138bad4eda19c8df49d87dc85c1666c7443e346d5a7307b18c73b4d17dfa3ea143f774f5c359a05ec7c4","name":"Campaigns-Premium - Campaigns-Premium (4).csv","type":"text/csv","lastModified":1688049119722.651,"size":4818640}] 2d:{"title":"Daily view","cells":[{"id":0,"value":"md `# Daily view`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1759,"value":"viewof campaigns_tab = Inputs.file({label: \"Campaigns tab MTD download\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2123,"value":"md `**Overpacing**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1986,"value":"monthly_budget_by_campaign_all = Inputs.table(aq.from(clean_campaigns_tab)\n .derive({\n percent_to_budget: d => d.spend / d.spend_goal\n })\n .derive({\n over_pacing: aq.escape( d => d.percent_to_budget >= month_progress == true ? true : false )\n })\n .filter(d => d.over_pacing == true)\n .rename({'Parent Campaign name': 'campaign'})\n .select(0,1,2,5,6,7)\n .orderby(aq.desc('percent_to_budget'))\n .derive({'%': d => d.percent_to_budget})\n .objects(), {\n width: 950,\n layout: 'auto',\n format: {\n spend_goal: d3.format('$,.0f'),\n spend: d3.format('$,.0f'),\n '%': d3.format('.0%'),\n percent_to_budget: sparkbar(1),\n }\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":2062,"value":"html `

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1715,"value":"md `**By Business Unit - Budget pacing by campaign**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":931,"value":"viewof BL_selector = Inputs.radio(clean_base_campaigns_tab.objects().map(d => d.business_line), {label: \"Business Line\", unique: true, value: 'RN'})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2127,"value":"html `

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1363,"value":"Plot.plot({\n height: 75, \n marginLeft: 50,\n x: {\n nice: d3.utcMonth,\n domain: month_domain\n },\n marks: [\n Plot.axisX({interval: d3.utcMonth, anchor: 'top', label: 'Month progress', labelAnchor: 'center'}),\n Plot.rectX(data, {\n x1: month_start,\n x2: 'date',\n insetTop: 0,\n insetBottom: 30,\n fill: appcast_color_palette[0]\n }),\n Plot.text(data, {\n x: 'date',\n dx: 20,\n dy: -15,\n text: d => `${d3.format('.0%')(days_since_start_of_month/days_in_month)}`\n })\n ]\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":1616,"value":"monthly_spend.legend('color')","pinned":false,"hidden":false,"mode":"ojs"},{"id":1282,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":1260,"value":"viewof monthly_budget_by_campaign = clean_campaigns_tab\n .filter(aq.escape(d => d.business_line == BL_selector))\n .derive({\n percent_to_budget: d => d.spend / d.spend_goal\n })\n .rename({'Parent Campaign name': 'parent_campaign'})\n // .orderby(aq.desc('percent_to_budget'),'Status')\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1718,"value":"md `**CPA's (#wip)**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1941,"value":"viewof daily = Inputs.file({label: \"UI Daily\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1944,"value":"raw_daily = daily.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1956,"value":"exported = Plot.plot({\n height: 200,\n marks: [\n Plot.ruleY([0]),\n Plot.axisY({tickFormat: '.0s'}),\n Plot.lineY(clean_daily, {x: \"Date\", y: \"Exported Jobs\"})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1963,"value":"spend_per_job = Plot.plot({\n height: 200,\n marks: [\n Plot.ruleY([0]),\n Plot.axisY({tickFormat: '$.0'}),\n Plot.lineY(clean_daily, {x: \"Date\", y: \"spend_per_job\", strokeOpacity: 0.2, strokeWidth: 1}),\n Plot.lineY(clean_daily, Plot.windowY({x: 'Date', y: 'spend_per_job', k:7, reduce: 'mean'}))\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1982,"value":"apps_per_job = Plot.plot({\n height: 200,\n marks: [\n Plot.ruleY([0]),\n Plot.axisY({tickFormat: '$.0'}),\n Plot.lineY(clean_daily, {x: \"Date\", y: \"apps_per_job\", strokeOpacity: 0.2, strokeWidth: 1}),\n Plot.lineY(clean_daily, Plot.windowY({x: 'Date', y: 'apps_per_job', k:7, reduce: 'mean'}))\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1947,"value":"viewof clean_daily = aq.from(raw_daily)\n .derive({\n 'Active Jobs': d => +op.replace(d['Active Jobs'], ',',''),\n 'Exported Jobs': d => +op.replace(d['Exported Jobs'], ',',''),\n 'Expansion Jobs': d => +op.replace(d['Expansion Jobs'], ',',''),\n 'Original Jobs': d => +op.replace(d['Original Jobs'], ',',''),\n Sponsored: d => +op.replace(d.Sponsored, ',',''),\n Actual: d => +op.replace(d.Actual, ',',''),\n 'Total Applies': d => +d['Total Applies']\n })\n .derive({\n spend_per_job: d => d.Actual / d['Exported Jobs'],\n apps_per_job: d => d['Total Applies'] / d['Exported Jobs']\n })\n .view()","pinned":true,"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":824,"value":"viewof start_date = Inputs.date({label: \"Start date\", value: d3.utcMonth(new Date())})","pinned":false,"hidden":false,"mode":"ojs"},{"id":830,"value":"viewof end_date = Inputs.date({label: \"End date\", value: d3.utcDay(new Date())})","pinned":false,"hidden":false,"mode":"ojs"},{"id":198,"value":"### *Data*","pinned":false,"hidden":false,"mode":"md"},{"id":1762,"value":"md `**Campaigns tab file**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1767,"value":"viewof clean_campaigns_tab = clean_base_campaigns_tab\n .groupby('Parent Campaign name', 'business_line', 'Status')\n .rollup({\n active_jobs: d => op.sum(d['Active Jobs']),\n exported_jobs: d => op.sum(d.Exported),\n spend_goal: d => op.max(d['Spend Goal']),\n spend: d => op.sum(d.Actual)\n })\n .derive({\n 'Parent Campaign name': d => op.replace(d['Parent Campaign name'], '?', '-'),\n })\n .derive({\n 'Parent Campaign name': d => op.replace(d['Parent Campaign name'], '- Paused', ''),\n })\n .derive({\n 'Parent Campaign name': d => op.trim(d['Parent Campaign name'])\n })\n .orderby(aq.desc('Parent Campaign name'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1829,"value":"viewof clean_base_campaigns_tab = aq.from(raw_campaigns)\n .derive({\n 'Active Jobs': d => +op.replace(d['Active Jobs'], ',',''),\n Exported: d => +op.replace(d.Exported, ',',''),\n 'Spend Goal': d => +op.replace(d['Spend Goal'], ',',''),\n Actual: d => +op.replace(d.Actual, ',',''),\n })\n .derive({\n business_line: d => op.includes(op.lower(d.Campaign), 'allied') == true ? 'Allied' : \n op.includes(op.lower(d.Campaign), 'school') == true ? 'schools/education' :\n op.includes(op.lower(d.Campaign), 'therapy/rehab') == true ? 'therapy/rehab' :\n op.includes(op.lower(d.Campaign), 'per diem') == true ? 'per diem' :\n op.includes(op.lower(d.Campaign), 'rn') == true ? 'RN' :\n op.includes(op.lower(d.Campaign), 'interim') == true ? 'Interim Leadership' :\n 'Other'\n })\n .orderby(aq.desc('Campaign'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1765,"value":"raw_campaigns = campaigns_tab.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1233,"value":"md `**Budgets file**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1242,"value":"clean_budgets = aq.from(raw_budgets)\n .filter(d => d.Employer == 'Aya Healthcare' || d.Employer == 'Aya Locums' || d.Employer == 'Aya Corporate')\n .rename({Campaign: 'segment'}, {'Parent Campaign': 'campaign'})\n .reify()\n .derive({\n campaign: d => d.campaign == null ? d.segment : d.campaign\n })\n .derive({\n 'Parent Campaign ID': d => d['Parent Campaign ID'] == null ? d['Campaign ID'] : d['Parent Campaign ID']\n })\n .groupby('Employer', 'campaign', 'Status')\n .rollup({\n 'Monthly Budget': d => op.max(d['Monthly Budget'])\n })\n .filter(d => d['Monthly Budget'] != undefined)\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1239,"value":"raw_budgets = FileAttachment(\"Campaigns-Premium - Campaigns-Premium (4).csv\").csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1236,"value":"md `**Performance file**`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1224,"value":"viewof performance_by_campaign = clean\n .groupby('parent_campaign', 'business_line')\n .rollup({\n clicks: d => op.sum(d.clicks),\n applies: d => op.sum(d.applies),\n spend: d => op.sum(d.spend),\n aco_array: d => op.array_agg_distinct(d.aco_type)\n })\n .orderby('business_line')\n //.filter(d => op.includes(d.parent_campaign, 'Opera') == true)\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1186,"value":"md `## Main data source`","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"viewof file = Inputs.file({label: \"Data\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":508,"value":"$30","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"raw_data = file.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1610,"value":"md `### Query\n~~~sql\nselect jd.date, jb.master_publisher_aggregated publisher, jb.master_publisher, jd.job_group_id, jg.parent_campaign, jg.title as segment, jg.aco_type, jd.req_number, jg.parent_id,\n 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-06-01' as date) and cast('2023-06-23' as date)\ngroup by jd.date, jb.master_publisher_aggregated, jb.master_publisher, jd.job_group_id, jg.parent_campaign, jg.title, jg.aco_type, jd.req_number, jg.parent_id\norder by jd.date asc\n\n\n~~~\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":187,"value":"### *Secondary data sources*","pinned":false,"hidden":false,"mode":"md"},{"id":1601,"value":"md `**Dates, etc**`","pinned":true,"hidden":false,"mode":"ojs"},{"id":1346,"value":"month_start = d3.utcMonth(new Date())","pinned":true,"hidden":false,"mode":"ojs"},{"id":1349,"value":"month_end = d3.utcDay.offset(d3.utcMonth(d3.utcMonth.offset(new Date(), 1)), -1)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1354,"value":"month_domain = [month_start, month_end]","pinned":true,"hidden":false,"mode":"ojs"},{"id":1408,"value":"days_in_month = d3.timeDay.count(d3.utcDay.offset(month_start, -1), month_end)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1356,"value":"days_since_start_of_month = d3.timeDay.count(month_start, new Date())","pinned":true,"hidden":false,"mode":"ojs"},{"id":1984,"value":"month_progress = days_since_start_of_month / days_in_month","pinned":true,"hidden":false,"mode":"ojs"},{"id":1370,"value":"year = d3.utcYear(new Date()).toISOString()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1375,"value":"month = d3.utcMonth(new Date()).toISOString()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1377,"value":"my_date = d3.utcParse('%Y-%m-%d')(year.slice(0,4) + '-' + month.slice(5,7) + '-' + days_since_start_of_month.toString())","pinned":true,"hidden":false,"mode":"ojs"},{"id":1390,"value":"data = [\n {date: my_date}\n]","pinned":true,"hidden":false,"mode":"ojs"},{"id":1603,"value":"html `

`","pinned":false,"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":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":1996,"value":"function sparkbar(max) {\n return x => htl.html`

${x.toLocaleString(\"en\")}`\n}","pinned":true,"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":"$W31","stdlib":"1","titling":"auto"}