Costruire un data warehouse Amazon con FastAPI e TimescaleDB

La SP-API di Amazon dà accesso a un volume considerevole di dati operativi: ordini, scorte, liquidazioni e prestazioni del catalogo. I dati pubblicitari provengono invece dall’API separata Amazon Ads API. Ma i dati sono utili solo se puoi interrogarli. I report di Amazon sono temporanei, soggetti a limiti di frequenza e non progettati per le domande operative più importanti per un venditore che opera su più marketplace. Puoi vedere che cosa è successo, ma non chiedere facilmente che cosa stia accadendo su tutti i marketplace contemporaneamente o quale sia stato l’andamento della domanda negli ultimi novanta giorni per un ASIN specifico in una determinata regione logistica.

Questo articolo descrive l’architettura che utilizziamo per un data warehouse Amazon: FastAPI come livello API, TimescaleDB per archiviare le serie temporali e Celery per la pipeline asincrona dei report SP-API. L’obiettivo è un sistema nel quale i dati che recuperiamo dalle API di Amazon siano archiviati in una forma interrogabile sotto il tuo controllo, con una struttura sufficiente a supportare previsioni della domanda e analisi operative senza richiedere un team di ingegneria dei dati per mantenerlo.

Perché gli strumenti di Amazon non bastano

I report di Seller Central coprono la maggior parte delle esigenze operative per un singolo marketplace con un catalogo ridotto. Le lacune emergono su larga scala. I report vengono generati su richiesta e scadono. Non esiste un livello persistente per le query: esegui un report, scarichi un file e quel file costituisce i dati. Eseguire lo stesso report tra sei mesi restituisce i dati di quel momento, non quelli di oggi.

Qualsiasi analisi storica richiede quindi che qualcuno abbia scaricato e archiviato tutti i report pertinenti agli intervalli giusti, oppure che si ricostruisca la cronologia dalle esportazioni delle singole transazioni. Anche queste, però, sono soggette a limiti di frequenza, consentono la paginazione su finestre limitate e sono strutturate per recuperare singoli record, non per analisi massive.

La dashboard Business Reports di Amazon fornisce dati aggregati sulle tendenze, ma granularità e periodo di conservazione dipendono dalle scelte di prodotto di Amazon, non dalle tue esigenze di analisi. La console pubblicitaria ha un proprio modello dati, che non si collega facilmente ai dati di evasione degli ordini. I dati di liquidazione arrivano ogni due settimane e richiedono un’elaborazione tutt’altro che banale per essere riconciliati con quelli degli ordini.

Un data warehouse personalizzato risolve insieme i problemi di persistenza e interrogabilità. Controlli l’archiviazione, definisci la conservazione e scrivi le query che rispondono alle domande effettive della tua azienda, anziché a quelle per cui sono state progettate le dashboard di Amazon.

Scelte tecnologiche

Abbiamo scelto TimescaleDB come archivio principale perché la maggior parte dei dati operativi Amazon è costituita da serie temporali. Gli ordini hanno timestamp. Le istantanee delle scorte rappresentano un momento preciso. I periodi di liquidazione hanno date di inizio e fine. Spesa pubblicitaria e impressioni si accumulano nel tempo. TimescaleDB è PostgreSQL con estensioni per hypertable che rendono le query su intervalli temporali di grandi dataset molto più efficienti rispetto a PostgreSQL standard, mantenendo la piena compatibilità SQL. Non occorre imparare un nuovo linguaggio di query e il modello operativo non differisce sostanzialmente da una normale installazione Postgres.

FastAPI fornisce il livello API tra i worker Celery che acquisiscono dati dalla SP-API e i sistemi interni che interrogano il data warehouse. Gestisce autenticazione, validazione delle richieste e serializzazione delle risposte con poco codice ripetitivo. Per un servizio interno non esposto a Internet, la documentazione OpenAPI automatica di FastAPI è particolarmente utile: fornisce una definizione consultabile dell’interfaccia, che facilita lo sviluppo di nuovi sistemi utilizzatori senza dover ricostruire il modello dati dallo schema del database.

Celery gestisce la pipeline asincrona dei report SP-API. La generazione dei report SP-API è asincrona per definizione: richiedi un report, ne controlli periodicamente lo stato, scarichi il risultato quando è pronto ed elabori il file. Questo processo corrisponde naturalmente a una catena di attività Celery: un’attività avvia la richiesta, una periodica controlla lo stato, una scarica e archivia il file grezzo e una analizza e carica i dati strutturati in TimescaleDB. Redis funge da broker Celery e da backend dei risultati.

Modello dati

Le tabelle principali rispecchiano i domini di dati fondamentali della SP-API. Il principio progettuale è che ogni tabella debba rispondere a una specifica categoria di domande operative senza richiedere join tra più tipi di report.

Ordini è una hypertable partizionata su purchase_date. Ogni riga rappresenta un singolo articolo di un ordine anziché un ordine intero, perché l’analisi a livello di articolo, per ASIN, canale logistico e marketplace, è la granularità più comune. Gli aggregati a livello di ordine sono derivati dai record dei singoli articoli. L’intervallo di partizionamento della hypertable è di una settimana, con buone prestazioni per le query su finestre mobili utilizzate nelle previsioni della domanda.

Istantanee delle scorte archivia l’output dei report sulle scorte al momento dell’acquisizione. Le scorte FBA non sono un flusso di eventi, ma uno stato che cambia continuamente e che non è interamente ricostruibile dai soli ordini: resi, rettifiche e trasferimenti modificano le scorte senza ordini corrispondenti. Acquisiamo le istantanee due volte al giorno e le archiviamo come record riferiti a un momento preciso. Gli aggregati continui di TimescaleDB calcolano medie mobili giornaliere e settimanali senza richiedere la scansione dell’intera cronologia delle istantanee.

Liquidazioni archivia i report di liquidazione elaborati, con una riga per ciascuna voce. Il modello dati delle liquidazioni presenta alcune delle difficoltà di analisi più complesse dell’ecosistema SP-API: Amazon utilizza codici diversi per i tipi di transazione nei vari marketplace e convenzioni di denominazione delle commissioni non del tutto coerenti tra regioni. Manteniamo una tabella di corrispondenza che normalizza i nomi delle tipologie di commissione secondo una tassonomia interna uniforme.

Catalogo archivia un’istantanea del catalogo prodotti, con ASIN, titoli, categorie e relazioni tra varianti, aggiornata settimanalmente. Questa tabella serve soprattutto come riferimento per le altre e non usa una struttura hypertable, perché i dati non hanno caratteristiche significative di serie temporali.

TimescaleDB nella pratica

Le hypertable sono la funzionalità centrale di TimescaleDB. Per creare una hypertable da una normale tabella PostgreSQL basta un comando che specifica la colonna temporale e l’intervallo di partizionamento. Da quel momento le query SQL standard funzionano senza modifiche e il pianificatore di query di TimescaleDB esclude automaticamente i chunk, saltando le partizioni esterne all’intervallo temporale della query. Le query delimitate nel tempo risultano così rapide anche su grandi dataset.

Gli aggregati continui sono la seconda funzionalità che utilizziamo ampiamente. Un aggregato continuo è una vista materializzata che TimescaleDB aggiorna in modo incrementale quando arrivano nuovi dati nella hypertable. Per le previsioni della domanda manteniamo un aggregato continuo giornaliero delle vendite per ASIN e marketplace, dal quale si calcolano i totali mobili delle vendite a 7, 30 e 90 giorni. L’aggregato viene aggiornato automaticamente secondo una pianificazione definita; le query leggono dati precalcolati anziché l’intera hypertable, rendendo le analisi delle dashboard abbastanza rapide per un uso interattivo.

CREATE MATERIALIZED VIEW daily_sales_by_asin
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', purchase_date) AS day,
  asin,
  marketplace_id,
  SUM(quantity_ordered) AS units_sold,
  SUM(item_price_amount) AS revenue
FROM order_items
GROUP BY day, asin, marketplace_id;

La vista viene aggiornata automaticamente dalla policy in background di TimescaleDB, che configuriamo per un’esecuzione ogni ora. Le somme mobili a 90 giorni utilizzate nelle previsioni sono calcolate con query basate su funzioni finestra su questa vista materializzata, non sulla tabella grezza degli articoli degli ordini.

La pipeline Celery

La pipeline dei report SP-API viene eseguita come insieme di attività Celery con una pianificazione gestita da Celery Beat. Ogni tipo di report ha la propria cadenza: gli ordini vengono acquisiti ogni ora, le scorte due volte al giorno e le liquidazioni ogni due settimane, seguendo i periodi di liquidazione di Amazon.

La pipeline segue la stessa struttura per ogni tipo di report: un’attività pianificata avvia una richiesta tramite SP-API e ne archivia l’ID in Redis. Un’attività di controllo viene eseguita ogni pochi minuti, verifica lo stato tramite SP-API e, quando il report è pronto, accoda un’attività di download. Questa recupera il documento, archivia il file grezzo su disco e accoda un’attività di elaborazione. Quest’ultima analizza il file e carica i record strutturati in TimescaleDB.

La deduplicazione avviene durante l’elaborazione, anziché a livello di database. Talvolta la SP-API restituisce record duplicati tra finestre temporali sovrapposte. Manteniamo un hash dei campi identificativi di ogni record e verifichiamo se esiste già prima di inserirlo, evitando l’accumulo di righe duplicate nel data warehouse, purché ogni tipo di report sia elaborato in sequenza; per l’elaborazione concorrente servirebbe anche un vincolo di unicità nel database.

La gestione degli errori segue una regola semplice: gli errori transitori della SP-API, come risposte dovute ai limiti di frequenza o indisponibilità temporanee, attivano un nuovo tentativo Celery con attesa esponenzialmente crescente. Gli errori persistenti, come documenti malformati, modifiche inattese allo schema o problemi di analisi, vengono registrati con il contesto completo e interrompono l’elaborazione per quel tipo di report senza influire sulle altre pipeline. Esaminiamo il registro degli errori nella revisione operativa settimanale, anziché considerarli incidenti urgenti, salvo quando riguardano i dati di riconciliazione delle liquidazioni.

La base per le previsioni della domanda

Il data warehouse non implementa direttamente le previsioni della domanda. Fornisce la base dati che le rende possibili: serie temporali pulite delle vendite, con granularità ASIN-marketplace-giorno e una cronologia sufficiente a identificare andamenti stagionali e calcolare stime delle tendenze statisticamente significative.

L’aggregato continuo giornaliero descritto sopra alimenta segnali della domanda su finestre mobili di 7, 30 e 90 giorni. Questi alimentano modelli previsionali semplici: medie mobili ponderate ed estrapolazione delle tendenze lineari per prodotti stabili, con fattori di correzione manuali per prodotti dalla stagionalità nota. I modelli vengono eseguiti come endpoint FastAPI che interrogano gli aggregati continui e restituiscono previsioni puntuali con intervalli di confidenza.

Una volta disponibile la base dati, diventano praticabili approcci previsionali più sofisticati. Abbiamo sperimentato lo smorzamento esponenziale di Holt-Winters per prodotti con cicli stagionali chiari e regressioni semplici rispetto a segnali esterni, come spesa pubblicitaria e variazioni di prezzo, quando queste variabili incidono significativamente sulla domanda. In realtà, per la maggior parte dei prodotti il miglioramento della precisione rispetto a medie mobili ponderate ben calibrate è modesto: qualità dei dati e lunghezza della cronologia contano più della complessità del modello.

Che cosa diventa possibile

Vale la pena indicare le capacità specifiche rese possibili dal data warehouse: rappresentano un cambiamento qualitativo nelle operazioni, non solo un miglioramento incrementale della velocità di reportistica.

La pianificazione delle scorte su più marketplace diventa praticabile. Con i dati sulla velocità delle vendite a livello ASIN-marketplace e le scorte FBA attuali della tabella delle istantanee, possiamo calcolare i giorni di copertura per ogni SKU in ogni marketplace e generare automaticamente raccomandazioni di riordino, anziché effettuare revisioni manuali per ciascun marketplace.

La riconciliazione delle liquidazioni diventa verificabile. Con i dati degli ordini e delle liquidazioni nello stesso sistema, è possibile controllare che ogni articolo ordinato sia stato liquidato all’importo previsto, rilevando discrepanze nelle commissioni, rimborsi errati o errori di liquidazione altrimenti visibili solo a chi riconcilia manualmente le singole voci. Il controllo viene eseguito come report settimanale sul data warehouse, senza richiedere lavoro manuale.

L’analisi pubblicitaria diventa possibile a livello di ASIN. Collegando i dati pubblicitari a quelli degli ordini tramite identificativi condivisi, possiamo seguire come la spesa pubblicitaria e le vendite organiche variano insieme nel tempo. La console pubblicitaria di Amazon non risponde direttamente a questa domanda: mostra le vendite attribuite alla pubblicità, ma non le vendite organiche nello stesso contesto temporale. I dati collegati mostrano una correlazione; per attribuire le variazioni del posizionamento organico alla pubblicità servirebbero anche dati sul posizionamento e un confronto controllato.

---

Approfondimenti correlati

• Automazione dei file flat: gestire le schede prodotto su più marketplace Amazon — il livello di gestione del catalogo che affianca il data warehouse
• Ingegneria della distribuzione: costruire sistemi che vendono al posto tuo — il quadro strategico supportato dal data warehouse
• Oltre cento container Docker: la nostra routine mensile di verifica dello stato — la disciplina di manutenzione dell’infrastruttura che mantiene efficiente questo stack