2f:T463,Plot.plot({ insetTop: 10, marginLeft: 50, marginBottom: 50, height: 450, facet: { data: by_pub_by_day, y: 'Publisher', marginRight: 150 }, fy: { padding: 0.2, label: null }, y: { type: scale_selector }, marks: [ Plot.frame(), Plot.areaY(by_pub_by_day, {x: "date", y: metric_selector}), // axis & memorial day annotation Plot.axisX([new Date(2023,4,27)], {anchor: 'top', tickFormat: '%b %d',stroke: appcast_color_palette[2], fill: appcast_color_palette[2], tickSize: 15}), Plot.axisX({interval: d3.utcMonday}), Plot.gridX({interval: d3.utcMonday}), // // memorial day area highlight Plot.areaY(by_pub_by_day, { //filter: d => +d.date > +d3.utcParse("%Y-%m-%d")("2023-05-27") && d.date < +d3.utcParse("%Y-%m-%d")("2023-05-29"), filter: d => d.date >= memorial_weekend_start && d.date <= memorial_weekend_end, x: "date", y: metric_selector, fill: appcast_color_palette[2], fillOpacity: 0.5 }), // bid annotation //Plot.ruleX([new Date(2023, 4, 24)], {stroke: appcast_color_palette[3]}), ] })30:T45c,"by pub" // Plot.plot({ // marginLeft: 50, // marginBottom: 50, // height: 4000, // facet: { // marginRight: 150 // }, // fy: { // padding: 0.2, // label: null // }, // marks: [ // Plot.frame(), // Plot.areaY(by_acx_pub_by_day, {x: "date", y: metric_selector, fy: 'master_publisher', sort: {fy: 'y', reverse: true}}), // // axis & memorial day annotation // Plot.axisX([new Date(2023,4,27)], {anchor: 'top', tickFormat: '%b %d',stroke: appcast_color_palette[2], fill: appcast_color_palette[2], tickSize: 20}), // Plot.axisX({anchor: 'top', interval: 'month'}), // Plot.gridX({interval: d3.utcMonday}), // // // memorial day area highlight // Plot.areaY(by_acx_pub_by_day, { // filter: d => d.date >= memorial_weekend_start && d.date <= memorial_weekend_end, // x: "date", // y: metric_selector, // fill: appcast_color_palette[2], // fillOpacity: 0.5, // fy: 'master_publisher' // }), // // bid annotation // Plot.ruleX([new Date(2023, 4, 24)], {stroke: appcast_color_palette[3]}), // ] // })31:T4dc,Plot.plot({ width: 600, height: 600, marginLeft: 50, marginBottom: 50, facet: { data: by_pub_by_day, y: 'Publisher', marginRight: 150, }, fy: { padding: 0.2, label: null }, marks: [ Plot.frame(), // main data Plot.lineY(by_pub_by_day, {x: "date", y: rate_selector, strokeOpacity: 0.1}), Plot.lineY(by_pub_by_day, Plot.windowY({x: 'date', y: rate_selector, k: 7})), // axis & memorial day annotation Plot.axisX([memorial_weekend_start], {anchor: 'top', tickFormat: '%b %d',stroke: appcast_color_palette[2], fill: appcast_color_palette[2], tickSize: 20}), Plot.axisX({anchor: 'top', interval: 'month'}), Plot.gridY(), //Plot.gridX({interval: d3.utcMonday}), // // // memorial day area highlight // Plot.areaY(by_pub_by_day, { // filter: d => d.date >= memorial_weekend_start && d.date <= memorial_weekend_end, // x: "date", // y: metric_selector, // fill: appcast_color_palette[2], // fillOpacity: 0.5, // fy: 'Publisher' // }), // bid annotation // Plot.ruleX([new Date(2023, 4, 24)], {stroke: appcast_color_palette[3]}), Plot.ruleX([memorial_weekend_start], {stroke: appcast_color_palette[2]}) ] })32:T4fb,"by day" // Plot.plot({ // marginLeft: 50, // marginBottom: 50, // insetTop: 10, // height: 3000, // facet: { // marginRight: 150 // }, // fy: { // padding: 0.2, // label: null // }, // marks: [ // Plot.frame(), // // main data // Plot.areaY(by_market, {x: "date", y: metric_selector, fy: 'title', sort: {fy: 'y', reverse: true}}), // // axis & memorial day annotation // Plot.axisX([new Date(2023,4,27)], {anchor: 'top', tickFormat: '%b %d',stroke: appcast_color_palette[2], fill: appcast_color_palette[2], tickSize: 20}), // // main axis // Plot.axisX({anchor: 'top', interval: d3.utcMonday}), // Plot.gridX({interval: d3.utcMonday}), // // // memorial day area highlight // Plot.areaY(by_market, { // //filter: d => +d.date > +d3.utcParse("%Y-%m-%d")("2023-05-27") && d.date < +d3.utcParse("%Y-%m-%d")("2023-05-29"), // filter: d => d.date >= memorial_weekend_start && d.date <= memorial_weekend_end, // x: "date", // y: metric_selector, // fill: appcast_color_palette[2], // fillOpacity: 0.5, // fy: 'title' // }), // // bid annotation // Plot.ruleX([new Date(2023, 4, 24)], {stroke: appcast_color_palette[3]}), // ] // })33:T601,Plot.plot({ width: 500, height: 3500, marginLeft: 50, marginBottom: 50, facet: { // data: by_market, // y: 'title', marginRight: 150, }, fy: { padding: 0.3, label: null }, y: { domain: [0, 1], clamp: true }, marks: [ Plot.frame(), // main data Plot.lineY(by_market, { x: "date", y: rate_selector, strokeOpacity: 0.1, fy: 'title', channels: {clicks: {value: 'clicks'}}, sort: {fy: 'clicks', reduce: 'sum', reverse: true}}), //sort: {fy: 'y', reverse: true, reduce: 'median'}}), //, strokeOpacity: 0.1}), Plot.lineY(by_market, Plot.windowY({ x: 'date', y: rate_selector, fy: 'title', k: 7 })), // axis & memorial day annotation Plot.axisX([new Date(2023,4,27)], {anchor: 'top', tickFormat: '%b %d',stroke: appcast_color_palette[2], fill: appcast_color_palette[2], tickSize: 20}), Plot.axisX({anchor: 'top', interval: 'month'}), Plot.gridX({interval: d3.utcMonday}), // // // memorial day area highlight // Plot.areaY(by_pub_by_day, { // filter: d => d.date >= memorial_weekend_start && d.date <= memorial_weekend_end, // x: "date", // y: metric_selector, // fill: appcast_color_palette[2], // fillOpacity: 0.5, // fy: 'Publisher' // }), // annotation Plot.ruleX([memorial_weekend_start], {stroke: appcast_color_palette[2]}) //Plot.ruleX([new Date(2023, 4, 24)], {stroke: appcast_color_palette[3]}), ] })34:T41e,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), leads: d => op.sum(d.leads), bgc: d => op.sum(d.bgc) }) .derive({ CtoL: d => d.leads / d.clicks, LtoBGC: d => d.bgc / d.leads }) .orderby('date') .objects()35:[{"id":"ec39da6698d8f9c4a448402295b1c0ae5d228945625c2bdb9ae82d1019cb65b8e64c76d40f2779514b5c86b9fc97835ec14ea6bea12d7580650f461e2baa10be","href":"https://static.observableusercontent.com/files/ec39da6698d8f9c4a448402295b1c0ae5d228945625c2bdb9ae82d1019cb65b8e64c76d40f2779514b5c86b9fc97835ec14ea6bea12d7580650f461e2baa10be","name":"Lyft Timeline (2).xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1685976415338.954,"size":17528}] 2d:{"title":"Lyft - Alyssa question - 6/2","cells":[{"id":0,"value":"md `# Lyft - Alyssa question - 6/2`","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":"file.name","pinned":true,"hidden":false,"mode":"ojs"},{"id":725,"value":"md `I have a couple of questions relating to the non-incentive test and recent performance:\n- Clicks started trending up on 5/22, so that is likely unrelated to incentive removal, but curious if we're observing a shift in source mix?\n- It looks like the click to lead rate was trending down prior to the incentive removal, do we know why? \n- Despite BGCs being down slightly, as you noted, we're seeing a huge increase in lead to background check rate. Let's keep an eye on this, I'm hoping the trend continues! `","pinned":false,"hidden":false,"mode":"ojs"},{"id":992,"value":"viewof memorial_weekend_start = Inputs.date({label: \"MDW_start\", value: \"2023-05-27\", disabled: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":995,"value":"viewof memorial_weekend_end = Inputs.date({label: \"MDW_end\", value: \"2023-05-29\", disabled: 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":931,"value":"viewof metric_selector = Inputs.radio(['clicks', 'leads', 'bgc'], {label: \"metric\", value: \"leads\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":982,"value":"viewof scale_selector = Inputs.radio(['linear', 'sqrt'], {label: \"Scale\", value: \"sqrt\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":723,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":886,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":1020,"value":"viewof rate_selector = Inputs.radio(['CtoL', 'LtoBGC'], {label: \"Select a rate\", value: \"LtoBGC\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":786,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":944,"value":"md `# by Market`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1112,"value":"viewof column_selector = Inputs.checkbox(summary_by_market.columnNames(),{ multiple: true, value: ['title', 'leads']})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1058,"value":"top_markets = Inputs.table(summary_by_market, {\n width: 400,\n layout: 'auto',\n format: {\n clicks_percent: d3.format('.1%'),\n leads_percent: d3.format('.1%'),\n bgc_percent: d3.format('.1%')\n },\n columns: column_selector\n })","pinned":false,"hidden":false,"mode":"ojs"},{"id":949,"value":"Inputs.bind(Inputs.radio(['clicks', 'leads', 'bgc'], {label: \"metric\", value: \"leads\"}), viewof metric_selector)","pinned":false,"hidden":false,"mode":"ojs"},{"id":946,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":1091,"value":"Plot.plot({\n insetTop: 10,\n width: 250,\n height: 3000,\n facet: {\n data: by_market_by_week,\n y: 'title'\n },\n fy: {\n padding: 0.2\n },\n x: {\n label: null\n },\n marks: [\n Plot.frame(),\n Plot.rectY(by_market_by_week, {\n x: 'week_date',\n y: metric_selector,\n interval: d3.utcMonday,\n insetRight: 3,\n insetLeft: 3,\n channels: {clicks: {value: 'clicks'}},\n sort: {fy: 'clicks', reduce: 'sum', reverse: true}\n //sort: {fy: 'y', reverse: true}\n }), \n\n //axis\n //Plot.gridY(),\n Plot.axisX([new Date(2023,4,27)], {anchor: 'top', tickFormat: '%b %d',stroke: appcast_color_palette[2], fill: appcast_color_palette[2], tickSize: 20}),\n Plot.axisX({anchor: 'top'}),\n\n\n // bid and memorial day annotation\n //Plot.ruleX([new Date(2023, 4, 24)], {stroke: appcast_color_palette[3]}),\n Plot.ruleX([memorial_weekend_start], {stroke: appcast_color_palette[2]})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1104,"value":"Inputs.bind(Inputs.radio(['CtoL', 'LtoBGC'], {label: \"Select a rate\", value: \"LtoBGC\"}), viewof rate_selector)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1032,"value":"$33","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":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":940,"value":"viewof by_market = clean\n .filter(d => d.clicks > 0)\n .filter(d => op.includes(op.lower(d.title), 'craigs') == false)\n .groupby('date', 'title')\n .rollup({\n clicks: d => op.sum(d.clicks),\n leads: d => op.sum(d.leads),\n bgc: d => op.sum(d.bgc)\n })\n .derive({\n CtoL: d => d.leads / d.clicks,\n LtoBGC: d => d.bgc / d.leads\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1129,"value":"viewof summary_by_market = by_market\n .groupby('title')\n .rollup({\n clicks: d => op.sum(d.clicks),\n leads: d => op.sum(d.leads),\n bgc: d => op.sum(d.bgc)\n })\n .derive({\n clicks_percent: d => d.clicks / op.sum(d.clicks),\n leads_percent: d => d.leads / op.sum(d.leads),\n bgc_percent: d => d.bgc / op.sum(d.bgc)\n })\n //.select()\n .orderby(aq.desc('leads_percent'))\n .filter(d => op.rank() <= 10)\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1089,"value":"viewof by_market_by_week = clean\n .filter(d => d.clicks > 0)\n .filter(d => op.includes(op.lower(d.title), 'craigs') == false)\n .derive({week_date: aq.escape(d => d3.utcMonday(d.date))})\n .groupby('week_date', 'title')\n .rollup({\n clicks: d => op.sum(d.clicks),\n leads: d => op.sum(d.leads),\n bgc: d => op.sum(d.bgc)\n })\n .derive({\n CtoL: d => d.leads / d.clicks,\n LtoBGC: d => d.bgc / d.leads\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":731,"value":"viewof network_by_day = clean\n .groupby('date')\n .rollup({\n clicks: d => op.sum(d.clicks)\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":897,"value":"$34","pinned":true,"hidden":false,"mode":"ojs"},{"id":762,"value":"by_pub_by_day = aq.from(clean)\n .rename({master_publisher_aggregated: 'Publisher'})\n .filter(d => d.Publisher != 'Upward.net CPA' &&\n d.Publisher != 'Jobs2Careers CPA' &&\n d.Publisher != 'Google Test CPC' &&\n d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)' &&\n d.Publisher != 'Jobcase CPA' &&\n d.Publisher != 'CareerBuilder CPA' &&\n d.Publisher != 'Birddog CPA'&&\n d.Publisher != null\n )\n .derive({\n 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\n })\n .groupby('date', 'Publisher')\n .rollup({\n clicks: d => op.sum(d.clicks),\n leads: d => op.sum(d.leads),\n bgc: d => op.sum(d.bgc)\n })\n .derive({\n CtoL: d => d.leads / d.clicks,\n LtoBGC: d => d.bgc / d.leads\n })\n .orderby('date')\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":812,"value":"by_pub_by_week = aq.from(clean)\n .rename({master_publisher_aggregated: 'Publisher'})\n .filter(d => d.Publisher != 'Upward.net CPA' &&\n d.Publisher != 'Jobs2Careers CPA' &&\n d.Publisher != 'Google Test CPC' &&\n d.Publisher != 'Facebook Jobs Sponsored (TIER0 only)' &&\n d.Publisher != 'Jobcase CPA' &&\n d.Publisher != 'CareerBuilder CPA' &&\n d.Publisher != 'Birddog CPA'&&\n d.Publisher != null\n )\n .derive({\n 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\n })\n .derive({\n week_date: aq.escape(d => d3.utcMonday(d.date))\n })\n .groupby('week_date', 'Publisher')\n .rollup({\n clicks: d => op.sum(d.clicks),\n leads: d => op.sum(d.leads)\n })\n .derive({\n CtoL: d => d.leads / d.clicks\n })\n .orderby('week_date')\n .objects()","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) leads, round(sum(jd.spend),2) spend, sum(jd.qualified) bgc, sum(hired) activations\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 (4972,6140)\n--and jb.id in (14658, 30409) \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":"$W35","stdlib":"1","titling":"auto"}