file.name
"550f96b7-1867-4b93-b7f5-a936d2e9623f.csv"
viewof clean_monthly_april = clean_monthly
.filter(d => d.TCPA_April_2023 != null && d.currently_active != 'paused' && d.title != 'YEG' && d.title != 'YYC')
//.filter(aq.escape( d => d.month_date >= new Date(2023,2,31) && d.month_date < new Date(2023,4,1) )) // month greater than March 30, 2023
.filter(aq.escape( d => d.month_date >= new Date(2023,3,30) )) // month greater than April 30, 2023
.groupby('title')
.rollup({
spend: d => op.sum(d.spend),
leads: d => op.sum(d.leads),
bgc: d => op.sum(d.bgc),
activations: d => op.sum(d.activations)
})
.derive({
cpA: d => d.spend / d.activations,
})
.join(clean_base_tcpa, ['title', 'Region'])
.derive({
Diff_TCPA_Dec_2022: d => d.cpA - d.TCPA_Dec_2022,
Diff_TCPA_April_2023: d => d.cpA - d.TCPA_April_2023,
})
.derive({
perc_diff_april_2023: d => d.Diff_TCPA_April_2023 / d.TCPA_April_2023
})
.orderby(aq.desc('Diff_TCPA_April_2023'))
.view()
tcpa_april_active = clean_base_tcpa
.filter(d => d.currently_active != 'paused')
.array('Region')
aq.from(clean_pub_month_2023)
.filter(d => d.title == 'ATL')
.view()
viewof clean_pub_month = clean_base_performance
.derive({
month_date: aq.escape( d => d3.utcMonth(d.date) )
})
.groupby('month_date', 'title', 'Publisher')
.rollup({
spend: d => op.sum(d.spend),
leads: d => op.sum(d.leads),
bgc: d => op.sum(d.bgc),
activations: d => op.sum(d.activations)
})
.derive({
cpA: d => d.spend / d.activations,
})
.join(clean_base_tcpa, ['title', 'Region'])
.derive({
Diff_TCPA_Dec_2022: d => d.cpA - d.TCPA_Dec_2022,
Diff_TCPA_Apr_2023: d => d.cpA - d.TCPA_April_2023,
})
.view()
viewof performance_and_ci_daily = clean_daily
.join_left(clean_ci_by_day, (a,b) => op.equal(a.date, b.date) && op.equal(a.title, b.search_market))
// .join(other, (a, b) => op.equal(a.keyL, b.keyR)) <-- example from docs
.orderby('date_1')
.derive({
doordash_present: d => op.includes(op.lower(d.competitor_list), 'doordash'),
gopuff_present: d => op.includes(op.lower(d.competitor_list), 'gopuff'),
insta_present: d => op.includes(op.lower(d.competitor_list), 'instacart')
})
.view()
Plot.plot({
marks: [
Plot.dot(performance_and_ci_rank_daily, {
x: 'leads'
})
]
})
viewof clean_ci_filtered = clean_ci_rank_by_day
.filter(aq.escape( d => d.search_market == campaign_selector) )
.orderby('date')
.view()
viewof performance_and_ci_rank_daily = clean_daily
.join_left(clean_ci_rank_by_day, (a,b) => op.equal(a.date, b.date) && op.equal(a.title, b.search_market) && op.equal(a.company, b.company), ["date", "title","spend","clicks","leads","bgc","activations","cpl","cpbgc","cpA","CtoL","LtoBGC", "BGCtoA","company", "median_rank"])
.filter(aq.escape(d => d.title == campaign_selector))
// .join(other, (a, b) => op.equal(a.keyL, b.keyR)) <-- example from docs
// .orderby('date_1')
// .derive({
// doordash_present: d => op.includes(op.lower(d.competitor_list), 'doordash'),
// gopuff_present: d => op.includes(op.lower(d.competitor_list), 'gopuff'),
// insta_present: d => op.includes(op.lower(d.competitor_list), 'instacart')
// })
.view()
ci_perf_columns = performance_and_ci_rank_daily.columnNames()
viewof clean_filtered = clean_base_performance
.filter(aq.escape(d => d.title == campaign_selector))
.groupby('date', 'title')
.rollup({
spend: d => op.sum(d.spend),
leads: d => op.sum(d.leads),
bgc: d => op.sum(d.bgc),
activations: d => op.sum(d.activations)
})
.derive({
cpl: d => d.spend / d.leads,
cpBGC: d => d.spend / d.bgc,
cpA: d => d.spend / d.activations,
LtoBGC: d => d.bgc / d.leads,
BGCtoA: d => d.activations / d.bgc
})
.view()
raw_ci = file1.csv()
viewof clean_ci = aq.from(raw_ci)
.derive({
corrected_zip: aq.escape( d => d3.count(d.geo_zipcode) == 4 ? `0${d.geo_zipcode}` : d.geo_zipcode ),
date: d => op.parse_date(d.date)
})
.view()
viewof clean_ci_by_day = clean_ci
.filter(d => d.company != 'Lyft')
.groupby('date', 'search_market')
.rollup({
competitor_list: d => op.array_agg_distinct(d.company),
source_list: d => op.array_agg_distinct(d.source),
post_count: d => op.count()
})
.view()
viewof clean_ci_by_week = clean_ci
.filter(d => d.company != 'Lyft')
.derive({week_date: aq.escape(d => d3.utcMonday(d.date))})
.groupby('week_date', 'search_market')
.rollup({
competitor_list: d => op.array_agg_distinct(d.company),
source_list: d => op.array_agg_distinct(d.source),
post_count: d => op.count()
})
.orderby('week_date')
.view()
viewof clean_ci_rank_by_day = clean_ci
.derive({rank: d => +d.rank})
//.filter(d => d.company != 'Lyft')
.groupby('date', 'search_market', 'company')
.rollup({
median_rank: d => op.median(d.rank)
})
.view()
viewof clean_monthly = clean_base_performance
.derive({
month_date: aq.escape( d => d3.utcMonth(d.date) )
})
.groupby('month_date', 'title')
.rollup({
spend: d => op.sum(d.spend),
leads: d => op.sum(d.leads),
bgc: d => op.sum(d.bgc),
activations: d => op.sum(d.activations)
})
.derive({
cpA: d => d.spend / d.activations,
})
.join(clean_base_tcpa, ['title', 'Region'])
.derive({
Diff_TCPA_Dec_2022: d => d.cpA - d.TCPA_Dec_2022,
Diff_TCPA_April_2023: d => d.cpA - d.TCPA_April_2023,
})
.view()
viewof clean_daily = clean_base_performance
.derive({
month_date: aq.escape( d => d3.utcMonth(d.date) )
})
.groupby('date', 'title')
.rollup({
spend: d => op.sum(d.spend),
clicks: d => op.sum(d.clicks),
leads: d => op.sum(d.leads),
bgc: d => op.sum(d.bgc),
activations: d => op.sum(d.activations)
})
.derive({
cpl: d => d.spend / d.leads,
cpbgc: d => d.spend / d.bgc,
cpA: d => d.spend / d.activations,
CtoL: d => d.leads / d.clicks,
LtoBGC: d => d.bgc / d.leads,
BGCtoA: d => d.activations / d.bgc
})
.orderby('date', 'title')
.derive({company: d => 'Lyft'})
.view()
viewof clean_weekly = clean_base_performance
.derive({
week_date: aq.escape( d => d3.utcMonday(d.date) )
})
.groupby('week_date', 'title')
.rollup({
spend: d => op.sum(d.spend),
clicks: d => op.sum(d.clicks),
leads: d => op.sum(d.leads),
bgc: d => op.sum(d.bgc),
activations: d => op.sum(d.activations)
})
.derive({
cpl: d => d.spend / d.leads,
cpbgc: d => d.spend / d.bgc,
cpA: d => d.spend / d.activations,
CtoL: d => d.leads / d.clicks,
LtoBGC: d => d.bgc / d.leads,
BGCtoA: d => d.activations / d.bgc
})
.orderby('week_date', 'title')
.view()
viewof clean_base_performance = aq.from(raw_performance_data)
.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'
)
//.rename({campaign_name: 'title'})
.derive({
Publisher: d => d.jb_id == 14658 || d.jb_id == 30409 || d.Publisher == 'ZipRecruiter CPA' || d.Publisher == 'ZipRecruiter CPC' ? 'ZipRecruiter' : d.Publisher
})
.view()
raw_performance_data = file.csv({typed: true})
tcpa_map = new Map(clean_base_tcpa.objects().map(d => [d.Region, d]))
viewof clean_base_tcpa = aq.from(raw_tcpa)
.derive({
TCPA_April_2023: d => op.replace(d.TCPA_April_2023, '$',''),
TCPA_Dec_2022: d => op.replace(d.TCPA_Dec_2022, '$',''),
})
.derive({
TCPA_April_2023: d => op.replace(d.TCPA_April_2023, '$', ''),
TCPA_Dec_2022: d => op.replace(d.TCPA_Dec_2022, '$',''),
})
.derive({
TCPA_April_2023: d => d.TCPA_April_2023 == 'pause' ? null : +op.replace(d.TCPA_April_2023, ',',''),
TCPA_Dec_2022: d => d.TCPA_Dec_2022 == 'Pause' ? null : +op.replace(d.TCPA_Dec_2022, ',',''),
})
.rename({'30-Mar': 'currently_active'})
.view()
raw_tcpa = FileAttachment("TCPAs_2022 Q4_2023 Q1.csv").csv({typed: true})
// this is a really cool way of displaying tables. Much more aesthetically pleasing than the defaults of arquero and input.table
import { formatTable } from "@saneef/pretty-tables"
import {toc} from "@nebrius/indented-toc"
date_parser = d3.utcParse('%m/%d/%Y')
// sample of how to place html blocks in a grid
// html `<div style="display: grid; grid-template-columns: 1fr 1fr 1fr; column-gap: 0px; row-gap: 5px;">
// ${viewof spend}
// ${viewof cpl}
// ${viewof cpc}
// </div>`
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
