Combinazione di Google Analytics e Shopify: creazione di un data warehouse eCommerce in BigQuery

Google Analytics è un ottimo strumento che ti permette di capire il comportamento degli utenti di chi visita il tuo sito web. Considerando che, Shopify è fantastico nel fornire una copertura dettagliata delle prestazioni dal punto di vista dell'e-commerce. Entrambi sono bravissimi in quello che fanno, ma arriva un punto in cui i dati di Shopify e Google Analytics (GA) isolati non riescono a fornire le informazioni più approfondite che potresti desiderare. Mentre ognuno ha la capacità di fornire informazioni potenti, c'è un livello molto maggiore di comprensione che può essere ottenuto combinando i dati.

In questo particolare esempio, spiegheremo come abbiamo raggiunto questo obiettivo per i nostri amici di Pooch & Mutt, un'azienda con sede nel Regno Unito che produce alimenti per cani naturali e sani. Volevamo migliorare le informazioni prendendo i dati raccolti in Google Analytics e utilizzandoli insieme ai dati di e-commerce di Shopify, nonché ai dati sugli abbonamenti di Recharge e unendoli ai dati sugli abbonamenti legacy di Bold. Utilizzando questa varietà di fonti di dati, potremmo creare un quadro più ricco e completo.

Questo blog cercherà di fornire una panoramica di come aggreghiamo questi dati, quindi di delineare i processi di pensiero alla base della loro combinazione per estrarre e visualizzare queste intuizioni basate sui dati. Nello specifico, spiegheremo come abbiamo risposto alle seguenti tre domande;

1. Quali canali generano iscrizioni agli abbonamenti?
2. Combinazioni di basket oltre GA; quali sono i panieri più popolari per abbonati e non abbonati e quali sono i più redditizi?
3. Quanto tempo e quanti acquisti ci vogliono in media prima che un utente diventi un abbonato?

Metodologia

Prima di rispondere a queste domande, vediamo innanzitutto come abbiamo raccolto tutti i dati dalle rispettive piattaforme in una posizione centralizzata e creato una tabella che potremmo utilizzare per raccogliere questi dati.

Struttura dati

Abbiamo scelto di utilizzare BigQuery (BQ) per centralizzare i dati per la sua versatilità e capacità di gestire la combinazione di set di dati. Il diagramma di flusso seguente evidenzia le connessioni generali tra i dati e il modo in cui ciò è stato ottenuto:

Cucire

Stitch è uno strumento conveniente che ti consente di estrarre e caricare i dati in data warehouse predefiniti.

Ci siamo facilmente collegati a Shopify e Recharge tramite Stitch, dove abbiamo semplicemente selezionato i tavoli che volevamo. In questo caso, le tabelle più importanti sono "clienti" e "abbonamenti" di Recharge e "ordini" di Shopify.

Queste informazioni vengono quindi sincronizzate con BQ ogni 6 ore tramite il processo di pianificazione automatizzato di Stitch.

Grassetto

Per i dati storici sugli abbonati, abbiamo eseguito un'esportazione finale e l'abbiamo caricata su BQ tramite Fogli Google.

statistiche di Google

Sebbene Stitch possa essere utilizzato anche per questo, abbiamo scelto di utilizzare RStudio per inserire questi dati in BQ. Se sei interessato a saperne di più su come farlo, puoi leggere un blog del nostro esperto di R Studio Danny Smith su come utilizzare l'API GA con R.

Costruire una tabella principale

Una volta che avessimo tutti i dati richiesti in BQ, potremmo iniziare a combinarli. I passi che abbiamo fatto per farlo sono i seguenti:

1. Abbiamo combinato e allineato i dati degli abbonati legacy di Bold con i dati degli abbonati attuali di Recharge.
2. Abbiamo quindi estratto i dati di e-commerce da GA che includono gli ID ordine (ad es. SPFYXXXXX), insieme a sorgente, mezzo e canale.
3. Abbiamo creato nuove colonne basate sulle tabelle di GA e sui dati degli abbonati sindacalizzati prima di unirci ai dati degli ordini di Shopify. In questo caso, alcune di queste colonne aggiuntive includevano;
   1. Identificativo dell'abbonato "attivo".
   2. Data originale dell'abbonamento
   3. Sorgente, mezzo e canale dell'ordine
   4. Primo o ordine ripetuto del cliente
4. Successivamente, abbiamo selezionato i campi pertinenti dai dati dell'ordine di Shopify a cui volevamo unire questi nuovi dati. Alcuni esempi chiave sono;
   1. identificativo del cliente
   2. ID ordine
   3. Data dell'ordine
   4. SKU
   5. Articolo Quantità
   6. Prezzo dell'articolo
   7. Prezzo totale dell'ordine
   8. Codice di sconto
   9. Totale sconto
   10. Tag (utilizzato come metodo aggiuntivo per classificare gli abbonati rispetto ai non abbonati)
5. La fase finale qui è stata la creazione di alcune colonne aggiuntive per gestire più facilmente le righe duplicate per elementi diversi nello stesso ordine;
   1. Prezzo medio
   2. Sconto medio
   3. Stato dell'abbonato (tag + se presenti nella tabella Bold & Recharge)

Abbiamo quindi pianificato l'esecuzione di questa query una volta al giorno e utilizzato il risultato come tabella principale per query aggiuntive. Ciò significava che non dovevamo eseguire la query completa più volte, poiché si tratta di una tabella statica che si aggiorna solo quando pianificata, anziché ogni volta che viene effettuata una richiesta tramite la dashboard di Looker Studio.

Approfondimenti

Ora che abbiamo coperto la metodologia e delineato i passi compiuti per ottenere tutti i dati di cui abbiamo bisogno, possiamo passare a discutere di come abbiamo utilizzato questi dati per rispondere alle nostre domande.

Quali canali generano iscrizioni agli abbonamenti?

1. Per calcolarlo, abbiamo esaminato i primi ordini effettuati dai clienti, utilizzando il campo che abbiamo creato nella tabella principale che restituisce "Primo" o "Ripeti" in base alla data del primo ordine.
2. Utilizzando le colonne aggiuntive di raggruppamento dei canali e stato dell'abbonato all'interno della tabella principale, siamo in grado di esaminare i primi ordini di persone che sono diventate abbonati, al primo acquisto o all'acquisto ripetuto, e a quale canale sono stati attribuiti in GA .
3. Eliminando il passaggio per guardare solo agli abbonati, potremmo anche vedere quali canali guidano prevalentemente i primi acquisti.

Combinazioni di basket oltre GA; quali sono i panieri più popolari per abbonati e non abbonati e quali sono i più redditizi?

1. Utilizzando la funzione STRING_AGG, siamo stati in grado di combinare i diversi prodotti di singoli ordini in un'unica riga, separati da ' | '. Questo essenzialmente crea un paniere per ogni ordine.
2. Insieme a queste informazioni, abbiamo sommato le entrate per ciascuno di questi articoli come "Totale carrello", con l'obiettivo di avere una riga per ordine con tutte le informazioni pertinenti.
3. Naturalmente, c'è molta varianza tra i prodotti (gusto, dimensione, ecc.), quindi abbiamo aggiunto una colonna che eliminava questa varianza per esaminare le combinazioni del carrello dei prodotti di base.
4. Abbiamo quindi utilizzato "ROW_NUMBER() OVER (PARTITION BY Transaction_ID) AS Identifier" per assegnare valori sequenziali a ciascun ordine. Questo è stato poi utilizzato come filtro nel dashboard per isolare le singole righe che contenevano ancora tutte le informazioni.

Quanto tempo e quanti acquisti ci vogliono in media prima che un utente diventi un abbonato?

1. Utilizzando la nostra tabella principale, abbiamo estratto tutti gli ID cliente che sapevamo fossero abbonati e abbiamo preso la loro prima data di abbonamento.
2. Abbiamo quindi preso la data minima dell'ordine e utilizzato la funzione DATE_DIFF per calcolare la differenza tra queste date in "giorni".
3. Il passaggio finale ha inserito il canale e il numero medio di giorni prima dell'iscrizione, che ha portato a una tabella che mostra il numero medio di giorni prima che i clienti diventino in genere abbonati.
4. Per calcolare il numero medio di ordini effettuati dai clienti prima di diventare abbonati, abbiamo semplicemente diviso il conteggio degli ID ordine univoci per il conteggio degli ID cliente univoci in cui erano "Abbonati" e avevano date dell'ordine precedenti alla data minima di abbonamento.

Questi sono solo alcuni esempi superficiali delle visualizzazioni che abbiamo generato utilizzando questi rapporti. Con i dati, ci sono infinite possibilità: altri esempi di questo progetto includono l'osservazione del lifetime value del cliente da varie prospettive e la valutazione dell'uso dei codici sconto e del loro impatto sul comportamento dei clienti nel corso della vita. Con queste intuizioni, possono essere utilizzate per integrare le decisioni aziendali. Ad esempio, sapendo che sono necessari in media 3 ordini prima di diventare un abbonato, puoi cercare di condividere offerte e incentivi che incoraggino i clienti a diventare abbonati dopo 2 acquisti. I risultati di questo possono quindi essere misurati in un secondo momento.

Riepilogo

Questo caso di studio dimostra il potenziale di informazioni incredibilmente preziose basate sui dati che possono essere ottenute attraverso la combinazione di più fonti di dati, da GA a Shopify e oltre. Una volta creata questa infrastruttura, può essere gestita in modo dinamico e utilizzata per riferire su numerosi aspetti diversi dell'attività.

Se le domande a cui abbiamo risposto in questo blog sono simili a quelle che potrebbero interessarti per la tua attività, puoi metterti in contatto con noi compilando il nostro modulo di contatto.