Real Projects, Real Results

The Challenge

The team was stitching together CSVs from Seller Central, Amazon Ads, and Brand Analytics every Monday morning — up to half a day of manual work just to answer "how did we do last week?" Margins were a black box because PPC, fees, and refunds lived in separate reports that nobody had time to reconcile against revenue.

What I Built

• ✓ Automated SP-API + Ads API pipelines into a partitioned BigQuery warehouse — daily refreshes, no manual exports
• ✓ Full P&L waterfall: revenue → COGS → FBA fees → PPC → refunds → net contribution, computed at SKU level
• ✓ PPC performance layer: spend / ACOS / ROAS by campaign, plus wasted-spend detection (high-spend, zero-conversion search terms)
• ✓ Brand Analytics SQP integration — brand-vs-market funnel showing impressions → clicks → cart adds → purchases at the search-query level
• ✓ Data Studio dashboard with daily / weekly / monthly grain switch, filtered to a rolling 12-month window

The Impact

• → Monday-morning reporting collapsed from half a day → one click
• → Surfaced ~$500 / week of wasted ad spend on zero-conversion search terms — first negative-keyword harvest paid for the project
• → Made margin visible at the SKU level for the first time — product mix decisions now backed by net margin, not gut feel

The Challenge

A premium cold-brew brand sold across Amazon, Walmart, Instacart, and Criteo — each with its own ad platform, its own attribution rules, and its own export format — plus SPINS retail-scan data for its brick-and-mortar distribution. Answering "how is the brand doing across everything?" meant pulling reports from 5+ APIs, lining up mismatched click/order/sales definitions by hand, and there was no view that tied online demand to retail velocity. Most Amazon-only freelancers stop at Seller Central; this brand needed the full picture.

What I Built

• ✓ Unified BigQuery warehouse pulling SP-API, Walmart API, Instacart Ads API, Criteo API, GA4, and SPINS retail-scan into one daily-refreshed model
• ✓ Blended ROAS & TACOS across all 4 retail channels, with a per-marketplace revenue / units / ad-sales snapshot and period-over-period deltas
• ✓ Normalized clicks → orders → attributed-sales funnel across channels, with each platform's quirks reconciled (e.g. Instacart units as an order proxy)
• ✓ GA4 modeled as an owned-demand layer feeding an owned-to-retail funnel — sessions → clicks → retail orders → attributed revenue in one chain
• ✓ SPINS retail-scan layer — $ volume across 70+ retailer chains, category share by SKU, and a retailer×product velocity heatmap (the brick-and-mortar half most Amazon dashboards ignore)
• ✓ Data Studio dashboard with daily / weekly / monthly grain switch and a single channel dimension for clean per-channel breakdowns

The Impact

• → 6 separate API exports → one omnichannel command center — total performance without manual consolidation
• → Online and brick-and-mortar finally sit side by side — retail-scan velocity next to e-commerce ROAS, not in a separate silo
• → Owned demand → retail conversion is one funnel — the brand sees GA4 sessions flow through to attributed revenue, and spotted GA4 checkout tracking as a quick-win gap