user_list = clean_config_audit
.select('User')
.dedupe()
.derive({
department: d => d.User == 'jason.kwok@appcast.io' || d.User == 'Jack.Russell@appcast.io' || d.User == 'thomas.abbasi@appcast.io' ? 'AM' :
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' :
d.User == 'amy.muzik@appcast.io' || d.User == 'dan.larsen@appcast.io' ? 'Implementation' : 'Other'
})
.view()
viewof clean_config_audit = aq.from(raw_config_audit)
.rename({'Time (Pacific Time (US & Canada) -8)': 'date'})
.orderby('date')
.derive({date: d => op.parse_date(d.date)})
.derive({
date: aq.escape(d => d3.utcDay(d.date))
})
.view()
raw_config_audit = file1.csv({typed: true})
timeline = aq.from(raw_timeline.sheet('SC2 Timeline', {headers: true}))
.derive({Date: d => op.parse_date(d.Date)})
.filter(aq.escape(d => d.Date >= start_date && d.Date <= end_date))
.objects()
raw_timeline = FileAttachment("Lyft Timeline (2).xlsx").xlsx()
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()
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()
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()
md `Query used:
~~~sql
select 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
from cc_warehouse.jobdata jd
left join cc_warehouse.employers e
on e.id = jd.employer_id
left join cc_warehouse.job_boards jb
on jb.id = jd.job_board_id
left join cc_warehouse.job_groups jg
on jg.id = jd.job_group_id
where jd.employer_id in (12033)
and jd.date between cast('2023-01-01' as date) and cast('2023-06-06' as date)
group by jd.date, e.name, jb.master_publisher_aggregated, jb.master_publisher, jb.id, jd.job_group_id, jg.title
order by jd.date asc
~~~`
viewof clean = aq.from(raw_data)
.filter(aq.escape(d => d.date >= start_date && d.date <= end_date))
.view()
raw_data = file.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.
