[Exact Attribution]
Performance-Marketing Reporting Backend
Daily ingestion from checkouts, ad tracker, click tracker and video analytics, exact cost attribution per video sales letter, and QA that blocks bad numbers.
A direct-response marketing client sells through video sales letters (VSLs) on several checkout platforms. Daily reports lived in spreadsheets filled by scripts that held everything in memory.
Backend owner: ingestion, schema, views, attribution, QA and RPCs. The dashboard front end on top of it was built by another developer.
Spend was split across creatives in proportion to page views, sources disagreed, and nothing stopped a wrong number from reaching the report.
01 · Idempotent daily ingestion
One Python fetcher per source — three checkouts, the ad tracker's clicks and reports, a click tracker, video analytics and a multi-account affiliate platform via a headless-browser login. Raw JSON is kept for replay; upserts are chunked.
02 · Exact attribution by landing
Every click carries its landing id. Cost is summed per landing and day, and orders join clicks by click id. Each landing maps to one video player — the one with the most events.
03 · Don't lose quiet days
Attribution is driven by the union of all landing-dates from cost, revenue and page events, so spend on a day with no page events isn't dropped.
04 · Auto-discovery
A database function finds players with spend that aren't in the catalog and registers them flagged “needs review”.
05 · QA before the report
A PL/pgSQL refresh writes results to a QA table: L1 per-source sanity (amount identities, refund pairing, coverage) and L2 cross-source (checkout vs tracker per offer × day, ad spend vs tracker cost), with block / warn / info severities.
what you're seeing
A small table of days × landings × video players with cost, views and revenue. On the right, cost and revenue per creative under each method, and whether totals add up.try this
- 1.Start in proportional mode: a day with spend but no views loses its cost.
- 2.Switch to exact, then turn on “union of all landing-dates” — the lost cost comes back.
- 3.Turn on “collapse per player” to stop a shared player's revenue from counting twice. Then add a new creative.
real vs simplified
The two failure modes and their fixes are the real ones from the client's attribution views. All rows are synthetic.! brokeProportional splits put cost on the wrong creative when two players shared a landing.
→ changedExact attribution by landing id, one player per landing.
! brokeCost on the first day of a campaign vanished because there were no page events yet.
→ changedThe union of all landing-dates.
! brokeAuto-registered creatives got niches parsed from free-text campaign names by position, so campaign IDs and gateway names showed up as niches.
→ changedCorrected the data, kept “needs review” as the safety net, and documented the robust fix: map niche from product, parse text only as a fallback.