Discuss a project

DL-020 · Case study

Amazon data platform for an advertising agency

An Amazon Ads agency wanted its own data layer instead of rented reporting tools. We built a platform that loads seller, vendor, Ads and DSP data into BigQuery.

Client
Name withheld
Location
Germany
Industry
Amazon Ads agency
Capabilities
03Build02Stabilize
01

Challenge

  • Client reporting depended on third-party tools the agency did not control.
  • Each new client account meant manual setup across seller, vendor and Ads APIs.
  • Exports to the warehouse were too slow for daily agency reporting.
02

Environment

  • Many agency clients across Seller Central, Vendor Central and Amazon Ads.
  • Sponsored Products, Brands, Display and DSP reporting on several marketplaces.
  • Reports consumed in BigQuery and Looker Studio dashboards.
03

Intervention

  • Admin portal for self-service client onboarding with paired SP-API and Ads authorisation.
  • Raw report layer plus modelled tables, exported to the agency's own BigQuery.
  • Rebuilt the BigQuery exporter and fixed the warehouse cost drivers.
  • Per-seller concurrency so one account's backlog cannot stall the rest.
04

Outcome

  • The agency owns its Amazon data pipeline instead of renting one.
  • Export throughput rose tenfold, to tens of millions of rows a day.
  • Daily warehouse spend fell by about 90%.
Seller, vendor and Ads plus DSP data flow into one data platform, onboarded through an admin portal, and land as raw and modelled tables in BigQuery that feed Looker Studio dashboards.client auth10x exportSELLER CENTRALSP-APIVENDOR CENTRALSP-APIADS + DSPAds APIDATA PLATFORMLambda / SQSADMIN PORTALClient onboardingBIGQUERYRaw + modelledLOOKER STUDIODashboards
  1. 01SELLER CENTRALSP-APIDATA PLATFORMLambda / SQS
  2. 02VENDOR CENTRALSP-APIDATA PLATFORMLambda / SQS
  3. 03ADS + DSPAds APIDATA PLATFORMLambda / SQS
  4. 04ADMIN PORTALClient onboardingDATA PLATFORMLambda / SQSclient auth
  5. 05DATA PLATFORMLambda / SQSBIGQUERYRaw + modelled10x export
  6. 06BIGQUERYRaw + modelledLOOKER STUDIODashboards
Fig. 01Agency data platformSeller, vendor and Ads plus DSP data flow into one data platform, onboarded through an admin portal, and land as raw and modelled tables in BigQuery that feed Looker Studio dashboards.Scale NTS

Results, as published.

10x
Export throughput to BigQuery
~90%
Lower daily warehouse cost

Stack and services.

Stack08

  • Amazon SP-API
  • Amazon Ads API
  • Node.js
  • TypeScript
  • AWS Lambda
  • Amazon SQS
  • PostgreSQL
  • Google BigQuery

Services used03

Tell us what has to work.

A senior engineer reads every brief. Describe the system, what it runs on today and what happens when it fails.

Discuss a project