Imparare l'SQL con dati reali GA4: Guida pratica per principianti

Siete curiosi di sapere come appaiono i dati reali dell'e-commerce in SQL? In questo articolo analizzerete gli eventi di Google Analytics 4 (GA4) del Google Merchandise Store utilizzando BigQuery e imparerete a conoscere l'SQL in modo pratico.

Volete migliorare le vostre competenze SQL con dati reali di e-commerce? In questo articolo esplorerete i dati di Google Analytics 4(GA4) utilizzando l'SQL in BigQuery, il data warehouse di Google basato sul cloud. GA4 è la piattaforma di analisi web di Google utilizzata da milioni di siti web e applicazioni per monitorare l'interazione degli utenti con i loro contenuti. È ampiamente adottata nel campo dell'analisi digitale, il che rende le competenze GA4 molto preziose nel mercato del lavoro e in molte aziende. Se si collega la proprietà GA4 a BigQuery, è possibile memorizzare i dati grezzi degli eventi e interrogarli con SQL. Ciò consente di andare oltre i report integrati di GA4 e di analizzare il comportamento degli utenti in modo molto più dettagliato.

Google offre un set di dati GA4 gratuito in BigQuery: ga4_obfuscated_sample_ecommerce.events_20210113. Contiene i dati di eventi reali del Google Merchandise Store - il negozio online ufficiale di Google per prodotti di marca come magliette, tazze e accessori - acquisiti il 13 gennaio 2021. Vi si trovano visualizzazioni di prodotti, aggiunte al carrello, acquisti e informazioni sugli utenti, come la posizione o il tipo di dispositivo. Si tratta di un set di dati leggero e reale, perfetto per imparare l'SQL.

Avete bisogno di un rapido ripasso di SQL? Provate la nostra traccia interattiva SQL per l'analisi dei dati. È il modo più veloce per imparare le basi che utilizzerete in questa guida.

Conoscere il set di dati

In questa guida utilizzeremo un dataset pubblico e gratuito in BigQuery:
bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113 .

GA4 utilizza un modello basato sugli eventi, in cui ogni interazione dell'utente - come la visualizzazione di un prodotto, l'aggiunta al carrello o il completamento di un acquisto - viene tracciata come un evento separato. Ogni riga del dataset rappresenta una di queste interazioni e contiene sia campi standard (come il nome dell'evento e il timestamp) sia strutture annidate (come i dettagli del prodotto o i parametri dell'evento).

Ecco alcune colonne chiave con cui si lavorerà:

• event_name: il tipo di azione dell'utente, come page_view, view_item, add_to_cart, begin_checkout, o purchase
• user_pseudo_id: identificativo anonimo dell'utente
• event_timestamp: quando si è verificato l'evento
• event_params: dettagli relativi all'evento (memorizzati come coppie chiave-valore)
• items: dati di prodotto annidati per eventi come view_item o acquisto

Quali sono gli eventi più comuni?

Iniziamo a vedere cosa fanno gli utenti sul sito. Questa query mostra i tipi di evento più comuni e il numero di utenti che li hanno attivati:

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS unique_users,
  COUNT(*) AS total_events
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
GROUP BY event_name
ORDER BY total_events DESC;

Questa query mostra le azioni più comuni degli utenti. Come previsto, page_view è in testa con oltre 15.000 eventi. Solo 34 utenti hanno completato l'acquisto, mentre molti altri hanno visualizzato i prodotti o li hanno aggiunti al carrello: un evidente calo tipico degli imbuti di e-commerce.

Tracciare l'imbuto di acquisto

Costruiamo quindi un imbuto di base per capire quanti utenti passano dalla navigazione all'acquisto. Conteremo quanti utenti unici hanno attivato ciascuno degli eventi chiave dell'e-commerce:

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
WHERE event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
GROUP BY event_name
ORDER BY users DESC;

Questo mostra come gli utenti abbandonano ogni fase del percorso di acquisto. Probabilmente noterete un numero maggiore di utenti che visualizzano gli articoli rispetto a quelli che completano effettivamente gli acquisti, proprio come nella maggior parte dei negozi online.

Analizzare gli acquisti degli utenti

Volete sapere quali sono i prodotti più acquistati dagli utenti? Molti eventi di e-commerce includono i dettagli dei prodotti in un campo annidato chiamato articoli. Per accedervi, utilizzeremo la funzione UNNEST().

Ecco come trovare i 10 prodotti più acquistati:

SELECT
  item.item_name,
  COUNT(*) AS purchases
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`,
  UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY item.item_name
ORDER BY purchases DESC
LIMIT 10;

Ed ecco il risultato:

È anche possibile analizzare i prezzi dei prodotti e calcolare il fatturato totale o medio.

Ad esempio, questa query restituisce il fatturato totale per prodotto:

SELECT
  item.item_name,
  ROUND(SUM(item.price * item.quantity), 2) AS total_revenue
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`,
  UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY item.item_name
ORDER BY total_revenue DESC
LIMIT 10;

È un ottimo modo per trovare i prodotti più venduti del vostro negozio.

Segmentare gli utenti per Paese

I dati di GA4 includono anche informazioni sulla posizione e sul dispositivo di ciascun utente. È possibile segmentare il traffico in base a geo.country.

Ecco una query che mostra da dove gli utenti acquistano:

SELECT
  geo.country,
  COUNT(DISTINCT user_pseudo_id) AS unique_buyers
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
WHERE event_name = 'purchase'
GROUP BY geo.country
ORDER BY unique_buyers DESC;

Esercitarsi di più da soli

Dopo aver lavorato sugli esempi guidati, è arrivato il momento di esercitarsi da soli. Il modo migliore per sviluppare le vostre competenze SQL e pensare come un analista di dati è esplorare i dati con le vostre domande in mente.

• Suddividere gli acquisti per fonte di traffico (traffic_source.source)
• Confrontare la dimensione media del carrello tra utenti mobile e desktop
• Analizzare le visualizzazioni dei prodotti che non hanno portato a un acquisto
• Costruire un percorso a livello di sessione per un singolo utente

Ecco alcune sfide pratiche da provare:

• Suddividere gli acquisti per fonte di traffico. Scoprite quali sono le fonti di provenienza (come Google, dirette o di riferimento) che determinano il maggior numero di acquisti utilizzando traffic_source.source.
• Confrontare la dimensione media del carrello tra utenti mobili e desktop. Utilizzate i dati dei dispositivi per scoprire se il comportamento degli utenti cambia a seconda della piattaforma.
• Analizzare le visualizzazioni dei prodotti che non hanno portato a un acquisto. Identificare i punti di caduta nell'imbuto confrontando gli eventi view_item e purchase.
• Costruire un percorso a livello di sessione per un singolo utente. Scegliete un utente user_pseudo_id e ricostruite il suo percorso passo dopo passo utilizzando event_timestamp.

Più sperimenterete query come queste, più capirete i dati e più diventerete sicuri nell'applicare l'SQL a problemi aziendali reali.

Riflessioni finali

L'apprendimento dell'SQL è molto più coinvolgente e pratico quando si lavora con dati reali. Soprattutto quando questi dati riflettono il comportamento reale degli utenti in un negozio online, come il Google Merchandise Store. Non è solo teoria: è il modo in cui analisti di dati, marketer e team di prodotto lavorano ogni giorno.

In questo articolo avete imparato a:

• Scrivere query utilizzando i dati reali dell'e-commerce GA4
• Lavorare con campi annidati utilizzando UNNEST()
• Esplorare eventi, prodotti, segmenti di utenti e funnel

Lavorando direttamente con i dati di GA4 in BigQuery, avete fatto un grande passo avanti per pensare come un analista di dati - ponendo domande, scrivendo query significative e trovando intuizioni importanti.

Volete andare oltre? Date un'occhiata alla nostra traccia SQL dalla A alla Z per costruire una solida base, oppure immergetevi in argomenti più avanzati con i nostri corsi SQL interattivi.

Continuate a esercitarvi, rimanete curiosi e buon lavoro con le query!