GA4 + BigQuery per ecommerce: 5 query SQL per analizzare le vendite che Looker Studio da solo non ti dà
Looker Studio (ex Data Studio) è ottimo per dashboard operative e report visivi, ma ha limiti strutturali per le analisi avanzate di un ecommerce: non permette join complesse tra tabelle, non supporta funzioni di finestra (window functions) e non può fare cohort analysis vera. BigQuery, integrato con GA4, sblocca un livello di analisi che trasforma i dati di un ecommerce in decisioni di business concrete.
Queste 5 query SQL sono pronte all’uso nel tuo progetto BigQuery con esportazione GA4 attiva. Ogni query include commenti nel codice che spiegano la logica. Il prerequisito è avere l’esportazione BigQuery attivata nella proprietà GA4 (gratuita per lo standard, a pagamento per l’esportazione continua su GA4 360).
Prerequisiti: configurare l’esportazione GA4 su BigQuery
Per usare queste query, vai su Google Analytics 4 > Amministrazione > Connessioni prodotto > BigQuery. Attiva l’esportazione e seleziona il progetto BigQuery. L’esportazione crea una tabella events_{data} per ogni giorno. Le query seguenti usano la struttura standard di questa esportazione. Sostituisci il-tuo-progetto.analytics_XXXXXXXXX con il percorso reale del tuo dataset BigQuery.
| Query | Obiettivo | Looker Studio lo fa? | Difficolta SQL |
|---|---|---|---|
| 1. Cohort Analysis | LTV per coorte di primo acquisto | No (nativo) | Media |
| 2. Prodotti abbandonati | Prodotti aggiunti al carrello mai acquistati | No | Bassa-media |
| 3. LTV predittivo semplice | Stima LTV 90 giorni per cliente | No | Media-alta |
| 4. Canale per AOV | AOV medio per canale di acquisizione | Parziale | Bassa |
| 5. Stagionalita vendite | Pattern orario e giornaliero per categoria | No (granulare) | Bassa |
Query 1 — Cohort Analysis: LTV per mese di primo acquisto
La cohort analysis mostra quanto spendono nel tempo i clienti acquisiti in un determinato mese. Ti dice se i clienti di dicembre (periodo saldi) hanno un LTV più basso di quelli di maggio, se una campagna specifica ha attratto clienti di qualità inferiore, e quanto fatturato aspettarti dai clienti acquisiti nelle ultime settimane.
— QUERY 1: Cohort Analysis LTV per mese di primo acquisto
— Prerequisito: evento purchase configurato con parametri ecommerce standard
WITH first_purchase AS (
SELECT
user_pseudo_id,
— Mese del primo acquisto (coorte)
FORMAT_DATE(‘%Y-%m’, DATE(TIMESTAMP_MICROS(MIN(event_timestamp)))) AS cohort_month,
MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS first_purchase_date
FROM `il-tuo-progetto.analytics_XXXXXXXXX.events_*`
WHERE event_name = ‘purchase’
AND _TABLE_SUFFIX BETWEEN ‘20250101’ AND ‘20261231’
GROUP BY user_pseudo_id
),
all_purchases AS (
SELECT
e.user_pseudo_id,
— Valore dell’acquisto in valuta locale
(SELECT value.double_value FROM UNNEST(event_params) WHERE key = ‘value’) AS revenue,
DATE(TIMESTAMP_MICROS(e.event_timestamp)) AS purchase_date
FROM `il-tuo-progetto.analytics_XXXXXXXXX.events_*` e
WHERE e.event_name = ‘purchase’
AND _TABLE_SUFFIX BETWEEN ‘20250101’ AND ‘20261231’
)
SELECT
f.cohort_month,
COUNT(DISTINCT f.user_pseudo_id) AS cohort_size,
ROUND(SUM(p.revenue), 2) AS total_revenue,
ROUND(SUM(p.revenue) / COUNT(DISTINCT f.user_pseudo_id), 2) AS ltv_per_user,
DATE_DIFF(p.purchase_date, f.first_purchase_date, MONTH) AS months_since_first_purchase
FROM first_purchase f
JOIN all_purchases p ON f.user_pseudo_id = p.user_pseudo_id
GROUP BY f.cohort_month, f.cohort_size, months_since_first_purchase
ORDER BY f.cohort_month, months_since_first_purchase
Query 2 — Prodotti abbandonati: add-to-cart senza acquisto
Questa query identifica i prodotti più frequentemente aggiunti al carrello ma non acquistati, il tasso di abbandono per prodotto e il fatturato potenziale perso. È la base per costruire campagne di remarketing dinamico e per capire se ci sono problemi di prezzo, disponibilità o descrizione su specifici prodotti.
— QUERY 2: Prodotti aggiunti al carrello ma non acquistati
WITH cart_events AS (
SELECT
user_pseudo_id,
(SELECT value.string_value FROM UNNEST(items), UNNEST([items]) i WHERE i.item_id IS NOT NULL LIMIT 1) AS item_id,
(SELECT value.string_value FROM UNNEST(items), UNNEST([items]) i WHERE i.item_name IS NOT NULL LIMIT 1) AS item_name,
(SELECT value.double_value FROM UNNEST(items), UNNEST([items]) i WHERE i.price IS NOT NULL LIMIT 1) AS item_price
FROM `il-tuo-progetto.analytics_XXXXXXXXX.events_*`, UNNEST(items) AS items
WHERE event_name = ‘add_to_cart’
AND _TABLE_SUFFIX BETWEEN ‘20260101’ AND ‘20261231’
),
purchased_items AS (
SELECT DISTINCT
user_pseudo_id,
(SELECT value.string_value FROM UNNEST(items) i WHERE i.item_id IS NOT NULL LIMIT 1) AS item_id
FROM `il-tuo-progetto.analytics_XXXXXXXXX.events_*`, UNNEST(items) AS items
WHERE event_name = ‘purchase’
AND _TABLE_SUFFIX BETWEEN ‘20260101’ AND ‘20261231’
)
SELECT
c.item_id,
c.item_name,
c.item_price,
COUNT(DISTINCT c.user_pseudo_id) AS users_added_to_cart,
COUNT(DISTINCT p.user_pseudo_id) AS users_purchased,
ROUND((COUNT(DISTINCT c.user_pseudo_id) – COUNT(DISTINCT p.user_pseudo_id)) * 100.0 / COUNT(DISTINCT c.user_pseudo_id), 1) AS abandonment_rate_pct,
ROUND((COUNT(DISTINCT c.user_pseudo_id) – COUNT(DISTINCT p.user_pseudo_id)) * c.item_price, 2) AS potential_revenue_lost
FROM cart_events c
LEFT JOIN purchased_items p ON c.user_pseudo_id = p.user_pseudo_id AND c.item_id = p.item_id
GROUP BY c.item_id, c.item_name, c.item_price
ORDER BY potential_revenue_lost DESC
LIMIT 50
Query 3 — LTV predittivo semplice (90 giorni)
Questa query stima il LTV a 90 giorni per ogni cliente basandosi sulla frequenza e il valore degli acquisti storici. Non è un modello ML avanzato, ma è una stima pragmatica che permette di segmentare i clienti per valore potenziale e ottimizzare le campagne di remarketing di conseguenza.
— QUERY 3: LTV predittivo a 90 giorni (modello semplificato RFM)
WITH customer_metrics AS (
SELECT
user_pseudo_id,
COUNT(DISTINCT event_date) AS purchase_frequency,
SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = ‘value’)) AS total_revenue,
MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS first_purchase,
MAX(DATE(TIMESTAMP_MICROS(event_timestamp))) AS last_purchase,
DATE_DIFF(MAX(DATE(TIMESTAMP_MICROS(event_timestamp))), MIN(DATE(TIMESTAMP_MICROS(event_timestamp))), DAY) AS days_active
FROM `il-tuo-progetto.analytics_XXXXXXXXX.events_*`
WHERE event_name = ‘purchase’
AND _TABLE_SUFFIX BETWEEN ‘20250101’ AND ‘20261231’
GROUP BY user_pseudo_id
HAVING purchase_frequency > 1 — Solo clienti con almeno 2 acquisti per previsione affidabile
)
SELECT
user_pseudo_id,
purchase_frequency,
ROUND(total_revenue, 2) AS historical_revenue,
ROUND(total_revenue / purchase_frequency, 2) AS avg_order_value,
ROUND(purchase_frequency / NULLIF(days_active, 0) * 90, 1) AS predicted_purchases_90d,
ROUND((purchase_frequency / NULLIF(days_active, 0) * 90) * (total_revenue / purchase_frequency), 2) AS predicted_ltv_90d
FROM customer_metrics
ORDER BY predicted_ltv_90d DESC
Query 4 — AOV per canale di acquisizione
Questa query confronta l’Average Order Value (AOV) per canale di traffico. Risponde a una domanda che Looker Studio non riesce a rispondere in modo accurato: i clienti di Google Ads spendono di più o di meno rispetto a quelli organici? Il canale con AOV più alto non è necessariamente il più profittevole, ma è un input fondamentale per calcolare il ROAS obiettivo per campagna.
— QUERY 4: AOV e revenue per canale di acquisizione
SELECT
traffic_source.medium AS channel,
traffic_source.source AS source,
COUNT(DISTINCT user_pseudo_id) AS unique_buyers,
COUNT(*) AS total_orders,
ROUND(SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = ‘value’)), 2) AS total_revenue,
ROUND(AVG((SELECT value.double_value FROM UNNEST(event_params) WHERE key = ‘value’)), 2) AS avg_order_value,
ROUND(SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = ‘value’)) / COUNT(DISTINCT user_pseudo_id), 2) AS ltv_proxy
FROM `il-tuo-progetto.analytics_XXXXXXXXX.events_*`
WHERE event_name = ‘purchase’
AND (SELECT value.double_value FROM UNNEST(event_params) WHERE key = ‘value’) > 0
AND _TABLE_SUFFIX BETWEEN ‘20260101’ AND ‘20261231’
GROUP BY channel, source
ORDER BY total_revenue DESC
Query 5 — Stagionalita: pattern orario e giornaliero per categoria
La quinta query analizza la distribuzione delle vendite per ora del giorno, giorno della settimana e mese dell’anno, segmentata per categoria prodotto. Questo serve per ottimizzare gli orari delle campagne Google Ads e Meta Ads, per pianificare le email al momento giusto e per capire la stagionalità specifica di ogni categoria del catalogo.
— QUERY 5: Stagionalita vendite per categoria prodotto
SELECT
EXTRACT(MONTH FROM DATE(TIMESTAMP_MICROS(event_timestamp))) AS month_number,
FORMAT_DATE(‘%B’, DATE(TIMESTAMP_MICROS(event_timestamp))) AS month_name,
EXTRACT(DAYOFWEEK FROM DATE(TIMESTAMP_MICROS(event_timestamp))) AS day_of_week,
EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp)) AS hour_of_day,
item_category,
COUNT(*) AS orders,
ROUND(SUM(item_revenue), 2) AS revenue,
ROUND(AVG(item_revenue), 2) AS avg_item_revenue
FROM `il-tuo-progetto.analytics_XXXXXXXXX.events_*`,
UNNEST(items) AS items
LEFT JOIN UNNEST([items.item_category]) AS item_category
WHERE event_name = ‘purchase’
AND _TABLE_SUFFIX BETWEEN ‘20260101’ AND ‘20261231’
GROUP BY month_number, month_name, day_of_week, hour_of_day, item_category
ORDER BY month_number, day_of_week, hour_of_day
Come usare i risultati di BigQuery in Looker Studio
Una volta eseguite le query in BigQuery, puoi esportare i risultati in due modi: come tabella BigQuery permanente (su cui collegare Looker Studio come data source) o come file CSV per analisi puntuali in Excel o Google Sheets. La soluzione più efficiente per un monitoraggio continuativo è creare scheduled queries in BigQuery: ogni query viene eseguita automaticamente (es. ogni lunedì mattina) e aggiorna la tabella di output, che Looker Studio legge in tempo quasi reale.
Domande frequenti
Devo pagare per usare BigQuery con GA4?
L’esportazione BigQuery da GA4 standard è gratuita. BigQuery ha un livello gratuito che include 10 GB di storage e 1 TB di query al mese. Per la maggior parte degli ecommerce di piccole-medie dimensioni, il costo rimane nella fascia gratuita o molto bassa (pochi euro al mese). Sopra un certo volume di dati e query, i costi scalano linearmente.
Devo essere un programmatore per usare queste query?
Non serve essere sviluppatori esperti, ma serve saper leggere e modificare SQL di base. Le query presentate hanno commenti che spiegano ogni passaggio. L’unica modifica necessaria è sostituire il percorso del dataset BigQuery con quello del tuo progetto. BigQuery ha un’interfaccia web (BigQuery Studio) che permette di eseguire query senza installare nulla.
GA4 ha già un rapporto LTV integrato: perché usare BigQuery?
Il rapporto LTV nativo di GA4 mostra dati aggregati ma non permette di fare cohort analysis personalizzata, confrontare LTV per canale su finestre temporali specifiche o combinare dati GA4 con dati da altre fonti (CRM, ERP). BigQuery sblocca questi livelli di analisi che i report GA4 standard non supportano.
Quanto tempo richiedono queste query su un dataset grande?
La durata dipende dalla dimensione del dataset (numero di eventi al giorno e finestra temporale analizzata). Su un ecommerce medio con 100.000-500.000 eventi/giorno e una finestra di 12 mesi, le query presentate richiedono tipicamente 10-60 secondi. BigQuery scala automaticamente la potenza di calcolo: non è necessario fare ottimizzazioni particolari per dataset di questa dimensione.
Conclusione
Looker Studio e i report nativi GA4 sono sufficienti per il monitoraggio operativo. BigQuery è necessario per le domande che contano davvero: quali clienti hanno il LTV più alto, quali prodotti vengono sistematicamente abbandonati, quale canale porta acquirenti di qualità e non solo traffico. Chi domina i dati del proprio ecommerce a questo livello non prende decisioni su medie aggregate: prende decisioni su pattern reali. E le decisioni basate su pattern reali portano a meno budget sprecato e più fatturato.
Mamagari supporta gli ecommerce nell’attivazione dell’esportazione BigQuery, nella costruzione delle query di analisi avanzata e nell’integrazione dei risultati con Looker Studio e con le strategie di campagna. Dal dato grezzo alla decisione strategica.
Vuoi sfruttare GA4 + BigQuery per il tuo ecommerce?
Mamagari attiva l’esportazione BigQuery, costruisce le query di analisi avanzata e collega i risultati alle strategie di marketing del tuo ecommerce. Contattaci!
