MAISON CODE .
/ Data · SQL · BigQuery · Analytics · Attribution · Marketing

Modellazione dell'attribuzione con SQL: oltre l'"ultimo clic"

Il raggruppamento dei canali predefinito GA4 ti sta mentendo. Come scrivere SQL non elaborato in BigQuery per calcolare il decadimento temporale e l'attribuzione basata sulla posizione.

AB
Alex B.
Modellazione dell'attribuzione con SQL: oltre l'"ultimo clic"

La tua dashboard dice: “Entrate email: \€50.000”. Il tuo gestore di Facebook dice: “Entrate Facebook: \€80.000”. Il totale Shopify è: ”\€100.000”. I conti non tornano. Perché? Doppia attribuzione. Sia Facebook che Email richiedono credito per la stessa vendita. La maggior parte degli strumenti utilizza l’attribuzione Ultimo clic. L’ultimo tocco ottiene il 100% di credito. Questo uccide il tuo marketing Top-of-Funnel (TikTok, Influencer) perché raramente guidano l’ultimo clic. Determinano il primo clic.

Perché Maison Code ne parla

In Maison Code Paris, agiamo come la coscienza architettonica dei nostri clienti. Spesso ereditiamo stack “moderni” costruiti senza una comprensione fondamentale della scala.

Discutiamo di questo argomento perché rappresenta un punto di svolta critico nella maturità ingegneristica. Implementarlo correttamente differenzia un MVP fragile da una piattaforma resiliente di livello aziendale.

Perché Maison Code parla di ingegneria dei dati

Il marketing non è più “Arti e Mestieri”. Si tratta di “scienza dei dati”. Se spendi \€ 1 milione all’anno in annunci senza un data warehouse, stai volando alla cieca. Implementiamo modelli di attribuzione personalizzata. Non ci fidiamo della scatola nera di Google. Ci fidiamo dell’SQL grezzo. Aiutiamo i CFO a capire dove vanno effettivamente i soldi.

1. La pipeline dei dati grezzi

Per risolvere questo problema, abbiamo bisogno di dati grezzi sul flusso di clic. Esportiamo i dati GA4 in BigQuery. Abbiamo una tabella events con user_pseudo_id, event_name, timestamp, source, medium, campaign. L’interfaccia utente GA4 standard aggrega questo. BigQuery ci fornisce il registro non elaborato. Ora possiamo ricostruire il Percorso Utente.

##2. I Modelli

1. Attribuzione lineare (socialista)

Ogni punto di contatto ottiene lo stesso credito. Percorso utente: “TikTok -> Google Ads -> Email -> Acquista (€100)”.

  • TikTok: € 33,33 *Google: € 33,33
  • E-mail: \ € 33,33
-- BigQuery SQL per l'attribuzione lineare
CON percorsi AS (
  SELEZIONA
    utente_id,
    ID_transazione,
    ARRAY_AGG(source ORDER BY timestamp) come punti di contatto,
    ANY_VALUE(entrate) come entrate_totali
  DA `analytics.events`
  GRUPPO PER 1,2
)
SELEZIONA
  fonte,
  SUM(entrate_totali / ARRAY_LENGTH(punti di contatto)) come entrate_attribuite
DA percorsi, UNNEST(punti di contatto) come origine
GRUPPO PER 1

2. Decadimento temporale (realista)

I tocchi più vicini alla conversione valgono di più. Applichiamo una formula di emivita (ad esempio, 7 giorni). Percorso utente: TikTok (10 giorni fa) -> Email (oggi) -> Acquista. L’e-mail ottiene l’80%. TikTok ottiene il 20%. Ciò rispetta il potere di “Chiusura” del canale.

3. Basato sulla posizione (a forma di U)

Il Primo Tocco (Discovery) e l’Ultimo Tocco (Closer) sono gli eroi. Il centro è il “Manutentore”.

  • Primo: 40%
  • Ultimo: 40%
  • Medio: 20% (Condiviso). Questo è lo standard per i team di crescita. Premia il “Cacciatore” (TikTok) e il “Closer” (Email).

3. Il problema delle finestre (Lookback)

Quanto indietro guardi?

  • Impostazione predefinita di Facebook: clic su 7 giorni, visualizzazione su 1 giorno.
  • Ciclo di acquisto del lusso: 45 giorni. Se vendi mobili costosi, una finestra di 7 giorni è inutile. Con SQL, possiamo impostare LOOKBACK_WINDOW su 90 giorni. Possiamo analizzare il “Tempo di conversione”. Vediamo che gli annunci TikTok impiegano 20 giorni per maturare, mentre Google Ads si converte in 2 ore.

4. Risoluzione dell’identità (tra dispositivi)

La parte più difficile. L’utente fa clic sull’annuncio su iPhone (dispositivo mobile). L’utente acquista su laptop (desktop). GA4 vede 2 utenti. Usiamo cuciture ID utente. Quando accedono o fanno clic su un collegamento e-mail (con hash “user_id”), mappiamo “device_id_A” e “device_id_B” su “master_user_X”. Questo unifica il percorso. Senza questo, si conteggeranno eccessivamente gli utenti e si sottovaluterà l’impatto sui dispositivi mobili.

Safari (ITP) cancella i cookie dopo 7 giorni. Chrome sta deprecando i cookie di terze parti. Il tracciamento lato client sta morendo. Monitoraggio lato server (CAPI) è la risposta. Inviamo eventi dal Server (Shopify/Node.js) direttamente a Facebook/Google. Ignoriamo il browser. Ciò migliora la precisione dei dati del 20%. È robusto contro gli ad blocker.

5. L’Apocalisse dei Biscotti (Dettagliato)

I biscotti stanno morendo. ITP (Intelligent Tracking Prevention) su Safari limita la durata dei cookie a 7 giorni (o 24 ore). Firefox li blocca per impostazione predefinita. Chrome li sta gradualmente eliminando. Ciò significa che gli “Utenti di ritorno” assomigliano a “Nuovi utenti”. Le tue metriche di “Acquisizione di nuovi utenti” sono gonfiate. La tua “Ritenzione” è sgonfia. La soluzione:

  1. Cookie proprietari: imposta i cookie “HttpOnly” dal tuo dominio (api.maisoncode.paris). ITP li tratta meglio.
  2. ID persistenti: chiedi agli utenti di accedere in anticipo. Utilizza l’e-mail come ID, non il cookie.

6. Data Clean Room (Snowflake/Ads Data Hub)

In un mondo in cui la privacy è al primo posto, non è possibile condividere i dati a livello di utente con Google. Inserisci Data Clean Room. Carichi i tuoi dati (e-mail con hash) in un ambiente sicuro e neutrale (Snowflake). Google carica i propri dati (e-mail con hash che hanno visto annunci). La Clean Room li abbina e fornisce Risultati aggregati. “500 persone che hanno visto l’annuncio hanno acquistato il prodotto.” Non vedi mai chi sono. Google non vede mai il tuo CRM. Questo è il futuro della misurazione degli annunci.

7. Test di incrementalità (annunci fantasma)

I modelli di attribuzione sono teorici. Incrementalità è scientifica. La domanda: “Avrebbero comprato comunque?” La prova:

  • Gruppo A: vede l’annuncio.
  • Gruppo B (Controllo): vede un “annuncio fantasma” (o un annuncio pubblicitario generico) oppure niente. Confronta i tassi di conversione. “Incremento = (Tasso conv. A - Tasso conv. B)”. Se l’incremento è 0, i tuoi annunci sono inutili, anche se GA4 afferma che hanno generato \€ 1 milione. Smetti di pagare per le conversioni che avresti ottenuto gratuitamente.

8. Il modello della catena di Markov (algoritmico)

Lineare e a forma di U sono euristiche (regole che abbiamo inventato). Le catene di Markov sono probabilistiche. L’algoritmo analizza 10.000 percorsi. Calcola l‘“Effetto Rimozione”. “Se togliamo TikTok dal mix, quante conversioni perdiamo?” Questo è il valore “Vero” del canale. Richiede Python/R, ma è il gold standard.

9. L’analisi della finestra di conversione

SQL ci consente di rispondere a domande a cui GA4 non è in grado di rispondere. “Un utente che visita 5 volte acquista più di un utente che visita 2 volte?” (Sorprendentemente, spesso No. Potrebbero essere bot o indecisi). “Qual è il tempo medio tra il primo clic e l’acquisto?” Se sono 3 minuti, il tuo SEO funziona. Se sono trascorsi 30 giorni, il tuo Email Nurture funziona. Lo segmentiamo per categoria di prodotto. I “calzini” acquistano velocemente. I “divani” acquistano lentamente.

10. Conversioni offline (integrazione POS)

Se disponi di negozi fisici, i tuoi annunci online incentivano le vendite offline. Se non tieni traccia di questo, sottoinvesti in pubblicità. Flusso di lavoro:

  1. Cattura “customer_email” o “loyalty_id” nel punto vendita (POS).
  2. Carica questo elenco su Facebook/Google (API Eventi offline).
  3. La piattaforma pubblicitaria corrisponde all’utente.
  4. “Acquisito tramite Instagram -> Acquistato nel negozio di Parigi”. Questo chiude il ciclo. Spesso raddoppia il ROAS riportato delle tue campagne.

11. La trappola del ROAS (entrate vs profitti)

I responsabili marketing ottimizzano per il ROAS (entrate/spesa pubblicitaria). Ma le Entrate non sono Profitti. Se vendi un prodotto a basso margine con un ROAS elevato, perdi denaro. Calcoliamo il POAS (profitto sulla spesa pubblicitaria). “POAS = (Entrate - COGS - Spedizione - Tasse) / Spesa pubblicitaria”. Inseriamo i dati sul margine da Shopify a BigQuery. Diciamo all’Ad Manager: “Elimina la campagna con 4,0 ROAS perché ha 0,8 POAS”. “Ridimensiona la campagna con un ROAS di 2,0 perché ha 1,5 POAS.” Ottimizza per il conto bancario, non per la metrica della vanità.

12. Il futuro: Media Mix Modeling (MMM)

Mentre il tracciamento muore, torniamo agli anni ‘60. Econometria. Mettiamo in relazione i “Picchi di spesa pubblicitaria” con i “Picchi di entrate”. “Ogni volta che spendiamo € 10.000 in TV, la ricerca organica aumenta del 5%.” Utilizziamo librerie open source come Meta Robyn (R) o Google LightweightMMM (Python) per eseguire modelli di regressione bayesiana. Questo modello non si preoccupa dei cookie. Si preoccupa della correlazione. È la fonte definitiva di verità in un mondo in cui la privacy è al primo posto.

13. Conclusione

L’attribuzione non è la Verità. È una negoziazione. Non esiste un modello “corretto”. Ma “L’ultimo clic” è sicuramente il modello di crescita “sbagliato”. Passando a SQL, possiedi la logica. Puoi vedere il valore delle tue campagne di notorietà del marchio. Smettila di indovinare. Inizia a interrogare.

Bruci soldi con la pubblicità?

Controlliamo i tuoi modelli di attribuzione per trovare il ritorno sulla spesa pubblicitaria reale utilizzando BigQuery e dbt.

Correggi i miei dati. Assumi i nostri architetti.