Discuss a project

DL-030 · Case study

Vendor Central and Ads reporting in Power BI

A vendor-management agency needed one reporting layer for the brands it runs on Vendor Central. We built a warehouse fed by Vendor and Ads reports, presented in Power BI.

Client
Name withheld
Location
Germany
Industry
Vendor-management agency
Capabilities
03Build
01

Challenge

  • Vendor Central data for several brands lived in separate manual downloads.
  • Advertising results sat apart from sales, inventory and forecast data.
  • First data for a newly onboarded brand took days to appear.
02

Environment

  • An agency managing Amazon Vendor accounts for manufacturers.
  • Brands selling across several European marketplaces.
  • Agency staff and their clients working in Power BI.
03

Intervention

  • Scheduled serverless jobs pulling SP-API Vendor reports and Ads API v3 reports.
  • PostgreSQL warehouse covering sales views, inventory, forecasts and search terms.
  • Power BI reports behind a log-in layer, one view per brand.
  • Repeatable onboarding for new brands with history loaded from the start.
04

Outcome

  • One place for Vendor sales, inventory, forecasts and advertising per brand.
  • New brands onboarded through a repeatable process, not one-off work.
  • Extended to several European marketplaces; maintained since delivery.
Scheduled jobs pull Vendor Central reports through SP-API and campaign reports through the Ads API into a PostgreSQL warehouse that Power BI reads behind a log-in layer.dailySP-API VENDORSales / inventory / forecastADS APICampaign reportsSCHEDULED JOBSServerlessWAREHOUSEPostgreSQLLOG-IN LAYERPer brandPOWER BIAgency reports
  1. 01SP-API VENDORSales / inventory / forecastSCHEDULED JOBSServerlessdaily
  2. 02ADS APICampaign reportsSCHEDULED JOBSServerless
  3. 03SCHEDULED JOBSServerlessWAREHOUSEPostgreSQL
  4. 04WAREHOUSEPostgreSQLPOWER BIAgency reports
  5. 05LOG-IN LAYERPer brandPOWER BIAgency reports
Fig. 31Vendor and Ads data into Power BIScheduled jobs pull Vendor Central reports through SP-API and campaign reports through the Ads API into a PostgreSQL warehouse that Power BI reads behind a log-in layer.Scale NTS

Stack and services.

Stack07

  • Amazon SP-API (Vendor)
  • Amazon Ads API v3
  • TypeScript
  • AWS Lambda
  • Amazon EventBridge
  • PostgreSQL
  • Power BI

Services used02

Tell uswhat 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