SQL per Marketing: Le Query Che Ogni Marketer Dovrebbe Conoscere

Nel 2012, Jonathan Rosenberg, ex SVP of Products di Google, mandò una mail interna: “In futuro, ogni decisione di business sara presa da chi sa leggere i dati. Chi non sa farlo sara irrilevante.” Non stava parlando di data scientist. Stava parlando di marketer, product manager, direttori commerciali.

Oggi, quasi tre quarti dei CMO citano la “data literacy” come la skill mancante nei loro team. La barriera principale si chiama SQL.

Ho visto team di marketing spendere 40 ore a settimana ad aspettare risposte dal data team — risposte a domande elementari come “quanti nuovi clienti abbiamo preso questo mese da Google?” o “qual e il ROAS della campagna Black Friday?”. Questa dipendenza e costosa, frustrante per entrambe le parti, e completamente evitabile.

L’SQL non e un linguaggio da programmatori. E il linguaggio per fare domande ai database. Queste 15 query coprono il 90% delle domande che un team marketing si pone ogni settimana.

Prerequisiti e Struttura dei Dati

Le query sono scritte per BigQuery (Google Cloud), ma funzionano con minimi adattamenti su PostgreSQL, Snowflake, e Redshift. Presuppongono tabelle tipiche di un e-commerce o SaaS:

orders — (order_id, user_id, order_date, revenue, utm_source, utm_medium, utm_campaign)
sessions — (session_id, user_id, session_date, channel_grouping, landing_page, has_purchase)
ga4_events — (event_name, event_timestamp, user_pseudo_id, page_location)
marketing_spend — (date, source, medium, campaign, spend, impressions, clicks)
email_campaigns — (campaign_id, campaign_name, send_date, emails_sent, emails_opened, emails_clicked)
order_items — (order_id, order_date, product_name, quantity, revenue)

Se non hai queste tabelle esatte, le query si adattano facilmente. L’importante e capire la logica sottostante.

Query 1: Revenue per Canale — Questo Mese vs Mese Scorso con Trend

-- Revenue aggregato per canale di acquisizione, con confronto mensile e trend
SELECT
  utm_source,
  utm_medium,
  DATE_TRUNC(order_date, MONTH) AS mese,
  COUNT(DISTINCT order_id) AS numero_ordini,
  SUM(revenue) AS revenue_totale,
  ROUND(AVG(revenue), 2) AS aov_medio,
  LAG(SUM(revenue)) OVER (
    PARTITION BY utm_source, utm_medium
    ORDER BY DATE_TRUNC(order_date, MONTH)
  ) AS revenue_mese_precedente,
  ROUND(
    SAFE_DIVIDE(
      SUM(revenue) - LAG(SUM(revenue)) OVER (
        PARTITION BY utm_source, utm_medium
        ORDER BY DATE_TRUNC(order_date, MONTH)
      ),
      LAG(SUM(revenue)) OVER (
        PARTITION BY utm_source, utm_medium
        ORDER BY DATE_TRUNC(order_date, MONTH)
      )
    ) * 100, 1
  ) AS variazione_pct
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
GROUP BY 1, 2, 3
ORDER BY mese DESC, revenue_totale DESC;

Spiegazione: Questa query usa LAG() (una window function) per prendere il valore del mese precedente per lo stesso canale. La variazione percentuale e calcolata come (mese_corrente - mese_precedente) / mese_precedente * 100. Utile per vedere tendenze rapide.

Query 2: CAC (Customer Acquisition Cost) per Canale — Approfondito

WITH new_customers AS (
  -- Identifica i nuovi clienti e il loro primo canale di acquisizione
  SELECT
    user_id,
    MIN(order_date) AS first_order_date,
    MIN(utm_source) AS source,
    MIN(utm_medium) AS medium,
    MIN(utm_campaign) AS campaign
  FROM orders
  GROUP BY user_id
  HAVING first_order_date >= DATE_TRUNC(CURRENT_DATE(), MONTH)
),

customer_counts AS (
  -- Conta i nuovi clienti per canale
  SELECT
    source,
    medium,
    campaign,
    COUNT(DISTINCT user_id) AS nuovi_clienti,
    COUNT(DISTINCT first_order_date) AS giorni_attivi
  FROM new_customers
  GROUP BY source, medium, campaign
),

marketing_spend_aggregated AS (
  -- Aggrega la spesa per canale nello stesso periodo
  SELECT
    source,
    medium,
    campaign,
    SUM(spend) AS spesa_totale,
    SUM(impressions) AS impressioni_totali,
    SUM(clicks) AS click_totali
  FROM marketing_spend
  WHERE date >= DATE_TRUNC(CURRENT_DATE(), MONTH)
  GROUP BY source, medium, campaign
)

SELECT
  cc.source,
  cc.medium,
  cc.campaign,
  cc.nuovi_clienti,
  COALESCE(msa.spesa_totale, 0) AS spesa,
  ROUND(SAFE_DIVIDE(COALESCE(msa.spesa_totale, 0), cc.nuovi_clienti), 2) AS cac,
  ROUND(SAFE_DIVIDE(msa.click_totali, cc.nuovi_clienti), 2) AS clicks_per_acquisizione,
  ROUND(SAFE_DIVIDE(COALESCE(msa.spesa_totale, 0), msa.click_totali), 2) AS cpc_effettivo
FROM customer_counts cc
LEFT JOIN marketing_spend_aggregated msa
  ON cc.source = msa.source
  AND cc.medium = msa.medium
  AND cc.campaign = msa.campaign
ORDER BY cac ASC;

Spiegazione: Usa una CTE per identificare i nuovi clienti, aggrega i dati per canale, e calcola il CAC come spesa diviso numero di nuovi clienti. Include anche CPC (cost per click) per capire l’efficienza del traffico a monte.

Query 3: Cohort Retention — Settimana per Settimana

WITH first_purchase AS (
  -- Per ogni cliente, identifica la settimana del primo acquisto
  SELECT
    user_id,
    DATE_TRUNC(MIN(order_date), WEEK(MONDAY)) AS cohort_week,
    MIN(order_date) AS first_purchase_date
  FROM orders
  GROUP BY user_id
),

cohort_data AS (
  -- Per ogni cliente, conta gli acquisti in ogni settimana dopo il primo
  SELECT
    fp.user_id,
    fp.cohort_week,
    DATE_DIFF(DATE_TRUNC(o.order_date, WEEK(MONDAY)), fp.cohort_week, WEEK) AS weeks_since_first,
    COUNT(DISTINCT o.order_id) AS ordini_settimana,
    SUM(o.revenue) AS revenue_settimana
  FROM first_purchase fp
  LEFT JOIN orders o
    ON fp.user_id = o.user_id
    AND o.order_date >= fp.first_purchase_date
  GROUP BY fp.user_id, fp.cohort_week, weeks_since_first
)

SELECT
  cohort_week,
  weeks_since_first,
  COUNT(DISTINCT user_id) AS clienti_attivi,
  ROUND(
    COUNT(DISTINCT user_id) * 100.0 /
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
      PARTITION BY cohort_week ORDER BY weeks_since_first
    ), 1
  ) AS retention_pct,
  ROUND(SUM(revenue_settimana), 2) AS revenue_totale_settimana
FROM cohort_data
WHERE cohort_week >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
  AND weeks_since_first <= 12
GROUP BY 1, 2
ORDER BY 1 DESC, 2 ASC;

Spiegazione: Questo calcola una retention table classica. FIRST_VALUE() OVER () prende il numero di clienti nella settimana 0 (prima settimana) per calcolare il retention percentuale.

Query 4: ROAS (Return On Ad Spend) per Campagna — Con Dettagli

SELECT
  ms.date,
  ms.source,
  ms.medium,
  ms.campaign,
  ms.spend AS spesa_giornaliera,
  SUM(ms.spend) OVER (
    PARTITION BY ms.campaign
    ORDER BY ms.date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS spesa_30gg_mobile,
  COUNT(DISTINCT o.order_id) AS conversioni_giornaliere,
  SUM(o.revenue) AS revenue_giornaliero,
  ROUND(SAFE_DIVIDE(SUM(o.revenue), ms.spend), 2) AS roas_giornaliero,
  ROUND(
    SUM(SUM(o.revenue)) OVER (
      PARTITION BY ms.campaign
      ORDER BY ms.date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) /
    SUM(ms.spend) OVER (
      PARTITION BY ms.campaign
      ORDER BY ms.date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 2
  ) AS roas_30gg_mobile,
  ROUND(SAFE_DIVIDE(SUM(o.revenue), COUNT(DISTINCT o.order_id)), 2) AS aov_medio
FROM marketing_spend ms
LEFT JOIN orders o
  ON ms.source = o.utm_source
  AND ms.medium = o.utm_medium
  AND ms.campaign = o.utm_campaign
  AND ms.date = DATE(o.order_date)
WHERE ms.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)
GROUP BY 1, 2, 3, 4, 5
HAVING ms.spend > 0
ORDER BY ms.date DESC, roas_giornaliero DESC;

Spiegazione: Usa window functions con ROWS BETWEEN per calcolare metriche mobili (rolling 30 giorni). Permette di vedere ROAS giornaliero ma anche trend a 30 giorni per evitare false conclusioni da giornate cattive.

Query 5: Top Landing Page per Conversione — Con Attribution

SELECT
  landing_page,
  channel_grouping,
  COUNT(DISTINCT session_id) AS sessioni,
  COUNT(DISTINCT CASE WHEN has_purchase THEN session_id END) AS sessioni_con_acquisto,
  ROUND(
    COUNT(DISTINCT CASE WHEN has_purchase THEN session_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT session_id), 0), 2
  ) AS conversion_rate_pct,
  ROUND(AVG(CASE WHEN has_purchase THEN 1 ELSE 0 END), 4) AS conversion_probability,
  ROW_NUMBER() OVER (PARTITION BY channel_grouping ORDER BY COUNT(DISTINCT session_id) DESC) AS rank_per_canale
FROM sessions
WHERE session_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND landing_page IS NOT NULL
GROUP BY 1, 2
HAVING COUNT(DISTINCT session_id) >= 100
ORDER BY conversion_rate_pct DESC
LIMIT 20;

Spiegazione: ROW_NUMBER() crea un ranking per canale, permettendoti di vedere la top landing page per Google Search, per Facebook, etc. Utile per capire quali pagine funzionano meglio per diversi canali.

Query 6: AOV (Average Order Value) Trend — Con Decili

SELECT
  DATE_TRUNC(order_date, WEEK(MONDAY)) AS settimana,
  COUNT(DISTINCT order_id) AS ordini,
  ROUND(AVG(revenue), 2) AS aov_media,
  ROUND(STDDEV_POP(revenue), 2) AS stddev_revenue,
  ROUND(PERCENTILE_CONT(revenue, 0.1) OVER (), 2) AS p10_revenue,
  ROUND(PERCENTILE_CONT(revenue, 0.25) OVER (), 2) AS q1_revenue,
  ROUND(PERCENTILE_CONT(revenue, 0.5) OVER (), 2) AS mediana_revenue,
  ROUND(PERCENTILE_CONT(revenue, 0.75) OVER (), 2) AS q3_revenue,
  ROUND(PERCENTILE_CONT(revenue, 0.9) OVER (), 2) AS p90_revenue,
  ROUND(
    AVG(revenue) - LAG(AVG(revenue)) OVER (ORDER BY DATE_TRUNC(order_date, WEEK(MONDAY)))
  ) AS aov_variazione_assoluta
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1
ORDER BY 1 DESC;

Spiegazione: Mostra non solo la media, ma tutta la distribuzione (decili, quartili). Permette di capire se la media sale perche il cliente medio spende di piu, o perche pochi clienti spendono molto di piu.

Query 7: Funnel di Conversione — Multi-Step con Drop-off

WITH funnel_steps AS (
  SELECT
    DATE(event_timestamp) AS data,
    COUNT(DISTINCT CASE WHEN event_name = 'page_view' THEN user_pseudo_id END) AS step_1_viewers,
    COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN user_pseudo_id END) AS step_2_item_views,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_pseudo_id END) AS step_3_add_to_cart,
    COUNT(DISTINCT CASE WHEN event_name = 'view_cart' THEN user_pseudo_id END) AS step_4_view_cart,
    COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_pseudo_id END) AS step_5_begin_checkout,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) AS step_6_purchase,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' AND purchase_revenue > 0 THEN user_pseudo_id END) AS step_7_paid_purchase
  FROM ga4_events
  WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY 1
)

SELECT
  data,
  step_1_viewers,
  step_2_item_views,
  step_3_add_to_cart,
  step_4_view_cart,
  step_5_begin_checkout,
  step_6_purchase,
  step_7_paid_purchase,
  ROUND(step_2_item_views * 100.0 / NULLIF(step_1_viewers, 0), 1) AS view_to_item_pct,
  ROUND(step_3_add_to_cart * 100.0 / NULLIF(step_2_item_views, 0), 1) AS item_to_cart_pct,
  ROUND(step_5_begin_checkout * 100.0 / NULLIF(step_3_add_to_cart, 0), 1) AS cart_to_checkout_pct,
  ROUND(step_6_purchase * 100.0 / NULLIF(step_5_begin_checkout, 0), 1) AS checkout_to_purchase_pct,
  ROUND(step_7_paid_purchase * 100.0 / NULLIF(step_1_viewers, 0), 1) AS overall_conversion_pct,
  -- Identifica il step con il drop-off piu grande
  CASE
    WHEN (step_1_viewers - step_2_item_views) > (step_2_item_views - step_3_add_to_cart) THEN 'Browse -> Item View'
    WHEN (step_2_item_views - step_3_add_to_cart) > (step_3_add_to_cart - step_4_view_cart) THEN 'Item View -> Cart'
    WHEN (step_3_add_to_cart - step_4_view_cart) > (step_4_view_cart - step_5_begin_checkout) THEN 'Add to Cart -> View Cart'
    ELSE 'Checkout -> Purchase'
  END AS biggest_drop_step
FROM funnel_steps
ORDER BY data DESC;

Spiegazione: Calcola ogni step della conversione e identifica automaticamente dove perde piu visitatori. Cruciale per prioritizzare gli sforzi di ottimizzazione.

Query 8: Clienti a Rischio Churn — Score-Based Approach

WITH client_activity AS (
  SELECT
    user_id,
    MAX(order_date) AS last_purchase_date,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS days_inactive,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT DATE_TRUNC(order_date, MONTH)) AS months_active,
    SUM(revenue) AS lifetime_value,
    AVG(revenue) AS avg_order_value,
    STDDEV_POP(revenue) AS stddev_order_value,
    DATE_DIFF(MAX(order_date), MIN(order_date), DAY) AS customer_lifespan_days
  FROM orders
  GROUP BY user_id
),

churn_scoring AS (
  SELECT
    user_id,
    last_purchase_date,
    days_inactive,
    total_orders,
    lifetime_value,
    -- Score basato su fattori multipli (0-100)
    ROUND(
      (days_inactive / NULLIF(customer_lifespan_days, 1) * 40) +  -- 40 punti per inattivita relativa
      (CASE WHEN total_orders <= 2 THEN 30 ELSE GREATEST(0, 30 - (total_orders * 5)) END) +  -- 30 punti per poca frequenza
      (CASE WHEN lifetime_value < 200 THEN 20 ELSE 5 END) +  -- 20 punti per LTV basso
      (CASE WHEN stddev_order_value > avg_order_value * 2 THEN 10 ELSE 0 END)  -- 10 punti per comportamento inconsistente
    , 0) AS churn_score,
    CASE
      WHEN days_inactive BETWEEN 30 AND 60 THEN 'At Risk'
      WHEN days_inactive BETWEEN 61 AND 90 THEN 'High Risk'
      WHEN days_inactive > 90 THEN 'Very High Risk'
      WHEN days_inactive > 180 THEN 'Lost'
      ELSE 'Active'
    END AS churn_status
  FROM client_activity
  WHERE total_orders >= 1
)

SELECT
  user_id,
  last_purchase_date,
  days_inactive,
  lifetime_value,
  churn_score,
  churn_status,
  -- Seppellisci quelli che hai perso per lungamente
  RANK() OVER (PARTITION BY churn_status ORDER BY lifetime_value DESC) AS rank_per_segment
FROM churn_scoring
WHERE churn_status IN ('At Risk', 'High Risk', 'Very High Risk')
ORDER BY churn_score DESC
LIMIT 500;

Spiegazione: Crea uno score di churn che combina recency, frequency e monetary value. I clienti high-LTV con alto churn score sono i target per campagne di retention mirate.

Query 9: Best e Worst Seller — Con Dinamiche Mensili

WITH product_performance AS (
  SELECT
    product_name,
    DATE_TRUNC(order_date, MONTH) AS mese,
    COUNT(DISTINCT order_id) AS ordini,
    SUM(quantity) AS unita_vendute,
    SUM(revenue) AS revenue,
    ROUND(AVG(revenue / NULLIF(quantity, 0)), 2) AS prezzo_medio_unitario,
    COUNT(DISTINCT user_id) AS clienti_unici,
    ROUND(SUM(revenue) / COUNT(DISTINCT user_id), 2) AS revenue_per_cliente
  FROM order_items
  GROUP BY 1, 2
)

SELECT
  product_name,
  mese,
  ordini,
  unita_vendute,
  revenue,
  prezzo_medio_unitario,
  clienti_unici,
  revenue_per_cliente,
  LAG(revenue) OVER (PARTITION BY product_name ORDER BY mese) AS revenue_mese_precedente,
  ROUND(
    (revenue - LAG(revenue) OVER (PARTITION BY product_name ORDER BY mese)) /
    LAG(revenue) OVER (PARTITION BY product_name ORDER BY mese) * 100, 1
  ) AS revenue_mom_pct,
  RANK() OVER (PARTITION BY mese ORDER BY revenue DESC) AS rank_revenue,
  RANK() OVER (PARTITION BY mese ORDER BY ordini DESC) AS rank_ordini
FROM product_performance
WHERE DATE_TRUNC(order_date, MONTH) >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
ORDER BY mese DESC, revenue DESC;

Spiegazione: LAG() permette di calcolare la variazione month-over-month per ogni prodotto. Ranking multipli mostano sia il prodotto con piu revenue che quello con piu ordini — a volte sono diversi.

Query 10: Email Performance — Revenue Attribution

WITH email_performance AS (
  SELECT
    e.campaign_name,
    e.send_date,
    e.emails_sent,
    e.emails_opened,
    e.emails_clicked,
    ROUND(e.emails_opened * 100.0 / NULLIF(e.emails_sent, 0), 2) AS open_rate,
    ROUND(e.emails_clicked * 100.0 / NULLIF(e.emails_sent, 0), 2) AS click_rate,
    COUNT(DISTINCT o.order_id) AS conversioni,
    COALESCE(SUM(o.revenue), 0) AS revenue_stesso_giorno,
    ROUND(COALESCE(SUM(o.revenue), 0) / NULLIF(e.emails_sent, 0), 4) AS revenue_per_email_sent,
    ROUND(COALESCE(SUM(o.revenue), 0) / NULLIF(e.emails_clicked, 1), 2) AS revenue_per_click
  FROM email_campaigns e
  LEFT JOIN orders o
    ON o.utm_campaign = e.campaign_id
    AND o.order_date BETWEEN e.send_date AND DATE_ADD(e.send_date, INTERVAL 7 DAY)
  WHERE e.send_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY 1, 2, 3, 4, 5
),

email_ranked AS (
  SELECT
    *,
    RANK() OVER (ORDER BY revenue_per_email_sent DESC) AS rank_revenue_efficiency,
    RANK() OVER (ORDER BY open_rate DESC) AS rank_open_rate,
    RANK() OVER (ORDER BY click_rate DESC) AS rank_ctr
  FROM email_performance
)

SELECT
  campaign_name,
  send_date,
  emails_sent,
  open_rate,
  click_rate,
  conversioni,
  revenue_stesso_giorno,
  revenue_per_email_sent,
  rank_revenue_efficiency,
  rank_open_rate,
  CASE
    WHEN rank_open_rate <= 3 AND rank_ctr > 10 THEN 'Alta apertura, click scarso — rivedere CTA'
    WHEN rank_open_rate > 10 AND rank_ctr > 10 THEN 'Sia apertura che CTR bassi — tema interessante?'
    WHEN rank_revenue_efficiency <= 5 THEN 'Vincente! Replicare questo template'
    ELSE 'Normale'
  END AS insight
FROM email_ranked
ORDER BY send_date DESC;

Spiegazione: Mostra performance assolute e relative (ranking) di ogni campagna. Gli insights automatici aiutano a identificare pattern vincenti.

Query 11: Customer Lifetime Value (CLV) Storico — Per Coorte

WITH customer_history AS (
  SELECT
    user_id,
    DATE_TRUNC(MIN(order_date), MONTH) AS acquisition_month,
    DATE_DIFF(CURRENT_DATE(), MIN(order_date), MONTH) AS months_since_acquisition,
    SUM(revenue) AS total_lifetime_revenue,
    COUNT(DISTINCT order_id) AS total_orders,
    AVG(revenue) AS avg_order_value,
    MAX(order_date) AS last_purchase_date,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS days_since_last_purchase
  FROM orders
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 48 MONTH)
  GROUP BY user_id
),

clv_by_cohort AS (
  SELECT
    acquisition_month,
    COUNT(DISTINCT user_id) AS cohort_size,
    ROUND(AVG(total_lifetime_revenue), 2) AS avg_clv,
    ROUND(PERCENTILE_CONT(total_lifetime_revenue, 0.5) OVER (), 2) AS median_clv,
    ROUND(PERCENTILE_CONT(total_lifetime_revenue, 0.95) OVER (), 2) AS clv_p95,
    ROUND(PERCENTILE_CONT(total_lifetime_revenue, 0.05) OVER (), 2) AS clv_p5,
    ROUND(STDDEV_POP(total_lifetime_revenue), 2) AS clv_stddev,
    AVG(total_orders) AS avg_orders_per_customer,
    AVG(CASE WHEN days_since_last_purchase <= 30 THEN 1 ELSE 0 END) AS pct_active_30d
  FROM customer_history
  WHERE months_since_acquisition >= 24  -- Solo coorti con almeno 24 mesi di history
  GROUP BY 1
),

clv_trend AS (
  SELECT
    acquisition_month,
    cohort_size,
    avg_clv,
    median_clv,
    clv_p95,
    LAG(avg_clv) OVER (ORDER BY acquisition_month) AS avg_clv_precedente,
    ROUND(
      (avg_clv - LAG(avg_clv) OVER (ORDER BY acquisition_month)) /
      LAG(avg_clv) OVER (ORDER BY acquisition_month) * 100, 1
    ) AS clv_yoy_pct,
    pct_active_30d
  FROM clv_by_cohort
)

SELECT
  acquisition_month,
  cohort_size,
  avg_clv,
  median_clv,
  clv_p95,
  clv_yoy_pct,
  CASE
    WHEN clv_yoy_pct > 10 THEN 'Cohorte di qualita crescente!'
    WHEN clv_yoy_pct < -10 THEN 'Cohorte peggiore anno-su-anno'
    ELSE 'Stabile'
  END AS trend_assessment,
  pct_active_30d
FROM clv_trend
ORDER BY acquisition_month DESC;

Spiegazione: Calcola il CLV per ogni coorte di acquisition. Permette di misurare se la qualita dei clienti acquisiti sta migliorando o peggiorando nel tempo.

Query 12: Attribution Multi-Touch — Linear e Time-Decay

WITH customer_journey AS (
  SELECT
    user_id,
    order_id,
    order_date,
    revenue,
    utm_source,
    utm_medium,
    utm_campaign,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS purchase_order_number,
    COUNT(*) OVER (PARTITION BY user_id) AS total_purchases_by_user
  FROM orders
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
),

--Attribution Linear: ogni touch point riceve 1/n del valore
linear_attribution AS (
  SELECT
    utm_source || ' > ' || utm_medium AS channel_pair,
    SUM(revenue / total_purchases_by_user) AS attributed_revenue_linear,
    COUNT(*) AS touches
  FROM customer_journey
  GROUP BY utm_source, utm_medium
),

-- Attribution Time-Decay: ultimi touch ricevono piu credito (40% final, 20% penultimo, 40% diviso tra i rimanenti)
time_decay_attribution AS (
  SELECT
    utm_source || ' > ' || utm_medium AS channel_pair,
    SUM(
      CASE
        WHEN purchase_order_number = total_purchases_by_user THEN revenue * 0.40  -- Final touch
        WHEN purchase_order_number = total_purchases_by_user - 1 THEN revenue * 0.20  -- Penultimo
        ELSE revenue * 0.40 / GREATEST(total_purchases_by_user - 2, 1)  -- Rimanenti
      END
    ) AS attributed_revenue_time_decay,
    COUNT(*) AS touches
  FROM customer_journey
  GROUP BY utm_source, utm_medium
)

SELECT
  COALESCE(l.channel_pair, td.channel_pair) AS channel,
  ROUND(COALESCE(l.attributed_revenue_linear, 0), 2) AS revenue_linear,
  ROUND(COALESCE(td.attributed_revenue_time_decay, 0), 2) AS revenue_time_decay,
  ROUND(
    COALESCE(td.attributed_revenue_time_decay, 0) -
    COALESCE(l.attributed_revenue_linear, 0), 2
  ) AS diff_time_decay_vs_linear,
  COALESCE(l.touches, 0) + COALESCE(td.touches, 0) AS total_touches
FROM linear_attribution l
FULL OUTER JOIN time_decay_attribution td
  ON l.channel_pair = td.channel_pair
ORDER BY COALESCE(td.attributed_revenue_time_decay, 0) DESC;

Spiegazione: Confronta due modelli di attribution. Linear e piu “democratico”, Time-Decay favorisce i touch point finali (che sono piu vicini alla conversione). Il differ mostra quale canale viene penalizzato/favorito da ciascun modello.

Query 13: RFM Segmentation — Query Completa

WITH rfm_raw AS (
  SELECT
    user_id,
    MAX(order_date) AS last_purchase_date,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(revenue) AS monetary
  FROM orders
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  GROUP BY user_id
),

rfm_scores AS (
  SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,  -- Decrescente: recente = score alto
    NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,  -- Crescente: frequente = score alto
    NTILE(5) OVER (ORDER BY monetary ASC) AS m_score  -- Crescente: spendente = score alto
  FROM rfm_raw
)

SELECT
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
    WHEN r_score >= 4 AND f_score >= 3 AND m_score >= 3 THEN 'Loyal Customers'
    WHEN r_score >= 4 AND f_score >= 3 THEN 'Potential Loyalists'
    WHEN r_score >= 3 AND f_score >= 1 AND m_score >= 1 THEN 'At Risk'
    WHEN r_score >= 4 AND f_score = 1 THEN 'New Customers'
    WHEN r_score <= 2 AND f_score = 1 THEN 'Lost'
    ELSE 'Need Attention'
  END AS rfm_segment,
  COUNT(DISTINCT user_id) AS customer_count,
  ROUND(AVG(monetary), 2) AS avg_clv,
  ROUND(AVG(frequency), 2) AS avg_order_frequency,
  ROUND(AVG(recency_days), 0) AS avg_days_since_purchase,
  ROUND(
    COUNT(DISTINCT CASE WHEN r_score >= 4 THEN user_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT user_id), 0), 1
  ) AS pct_recent
FROM rfm_scores
GROUP BY rfm_segment
ORDER BY avg_clv DESC;

Spiegazione: NTILE(5) divide i dati in quintili. Ogni utente riceve un score da 1-5 per Recency, Frequency e Monetary. Poi combini questi score per creare segmenti. Champions sono r>=4, f>=4, m>=4.

Query 14: Trend Funnel per Device Type — Week-Over-Week

WITH device_events AS (
  SELECT
    DATE_TRUNC(event_timestamp, WEEK(MONDAY)) AS settimana,
    CASE
      WHEN page_location LIKE '%mobile%' OR page_location LIKE '%m.%' THEN 'Mobile'
      WHEN page_location LIKE '%app%' THEN 'App'
      ELSE 'Desktop'
    END AS device_type,
    event_name,
    user_pseudo_id
  FROM ga4_events
  WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 WEEK)
)

SELECT
  settimana,
  device_type,
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' THEN user_pseudo_id END) AS visitors,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_pseudo_id END) AS cart_users,
  COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_pseudo_id END) AS checkout_users,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) AS purchasers,
  ROUND(
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_pseudo_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'page_view' THEN user_pseudo_id END), 0), 2
  ) AS cart_rate,
  ROUND(
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'page_view' THEN user_pseudo_id END), 0), 2
  ) AS conversion_rate,
  -- Identifica se il device sta migliorando o peggiorando
  LAG(
    ROUND(
      COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) * 100.0 /
      NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'page_view' THEN user_pseudo_id END), 0), 2
    )
  ) OVER (PARTITION BY device_type ORDER BY settimana) AS conversion_rate_prev_week
FROM device_events
GROUP BY 1, 2
ORDER BY settimana DESC, device_type;

Spiegazione: Permette di vedere se mobile sta migliorando settimana per settimana (o se peggiora). Critico per e-commerce dove mobile e desktop hanno dinamiche diverse.

Query 15: Propensione Repeat Purchase per Coorte

WITH first_purchase AS (
  SELECT
    user_id,
    order_date AS first_purchase_date,
    DATE_TRUNC(order_date, MONTH) AS cohort_month,
    revenue AS first_order_value
  FROM orders
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) = 1
),

repeat_purchases AS (
  SELECT
    fp.user_id,
    fp.first_purchase_date,
    fp.cohort_month,
    COUNT(DISTINCT o.order_id) AS total_repeat_orders,
    MIN(o.order_date) AS second_purchase_date,
    DATE_DIFF(MIN(o.order_date), fp.first_purchase_date, DAY) AS days_to_repeat,
    SUM(o.revenue) AS repeat_revenue
  FROM first_purchase fp
  LEFT JOIN orders o
    ON fp.user_id = o.user_id
    AND o.order_date > fp.first_purchase_date
  GROUP BY 1, 2, 3
)

SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS first_time_buyers,
  COUNT(DISTINCT CASE WHEN days_to_repeat IS NOT NULL THEN user_id END) AS repeat_buyers,
  ROUND(
    COUNT(DISTINCT CASE WHEN days_to_repeat IS NOT NULL THEN user_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT user_id), 0), 2
  ) AS repeat_purchase_rate,
  ROUND(AVG(CASE WHEN days_to_repeat IS NOT NULL THEN days_to_repeat END), 0) AS avg_days_to_repeat,
  ROUND(AVG(CASE WHEN days_to_repeat IS NOT NULL THEN repeat_revenue END), 2) AS avg_repeat_revenue,
  -- Velocity: quanti clienti tornano entro 30 giorni?
  ROUND(
    COUNT(DISTINCT CASE WHEN days_to_repeat <= 30 THEN user_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT user_id), 0), 2
  ) AS repeat_within_30d_pct
FROM repeat_purchases
WHERE cohort_month >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY cohort_month
ORDER BY cohort_month DESC;

Spiegazione: Fondamentale per capire l’health del business. Se il repeat purchase rate crolla o i giorni al repeat aumentano, e segnale di problemi. Usa QUALIFY (BigQuery) o ROW_NUMBER() per identificare il primo acquisto.

Tabella Riassuntiva: Le 15 Query e Quando Usarle

#	Query	Metrica Principale	Frequenza Ideale	Modulo GinnyTech Collegato	Caso d’Uso
1	Revenue per Canale	Revenue YoY	Settimanale	Analytics Engineering	Budget allocation decisions
2	CAC per Canale	Customer Acquisition Cost	Mensile	Advanced SQL	Channel efficiency analysis
3	Cohort Retention	Retention Rate	Settimanale	Significativita Statistica	Product health monitoring
4	ROAS per Campagna	Return on Ad Spend	Giornaliera	Metriche Fondamenti	Real-time campaign tuning
5	Top Landing Page	Conversion Rate	Settimanale	Analisi Marketing	A/B test prioritization
6	AOV Trend	Average Order Value	Settimanale	Matematica Analisi Dati	Pricing strategy review
7	Funnel Analysis	Drop-off Identification	Settimanale	Real-Time Analytics	UX optimization
8	Churn Scoring	Churn Risk	Settimanale	Marketing Data Science	Retention campaign targeting
9	Best/Worst Sellers	Product Revenue	Mensile	Analisi Marketing	Inventory management
10	Email Performance	Revenue per Email	Settimanale	Dashboard Visualizzazione	Email strategy refinement
11	CLV Storico	Customer Lifetime Value	Mensile	Significativita Statistica	Acquisition strategy validation
12	Attribution Multi-Touch	Channel Credit Allocation	Mensile	Analytics Engineering	Marketing mix optimization
13	RFM Segmentation	Segment Health	Mensile	Marketing Data Science	Customer targeting
14	Device Funnel	Device-Specific Conversion	Settimanale	Real-Time Analytics	Mobile optimization priority
15	Repeat Purchase	Repeat Rate	Mensile	Metriche Fondamenti	Product-market fit signal

Come Iniziare in Pratica

Se non hai mai scritto una query SQL, la barriera psicologica e piu grande di quella tecnica.

Giorno 1: Attiva l’export BigQuery in GA4 (Amministrazione → BigQuery Linking). E gratuito.

Giorni 2-7: Apri la console BigQuery. Copia la Query 7 sul funnel di conversione — e la piu semplice. Esegui. Guarda il risultato. Modifica un numero e riesegui per capire l’effetto.

Settimana 2-3: Adatta la Query 1 e la Query 4 ai tuoi dati di spesa pubblicitaria. Salva le query che usi piu spesso come “Saved Queries”. Crea una cartella “My Marketing Queries”.

Mese 2: Automatizza con le “Scheduled Queries” di BigQuery: le query girano ogni lunedi mattina alle 6:00 AM, i risultati vengono salvati in una tabella. Hai appena costruito il tuo sistema di reporting automatico.

Mese 3: Collega i risultati a Google Data Studio o Looker. I grafici si aggiornano automaticamente. Le dashboard che aggiornano sempre sono piu utili delle dashboard che devi aggiornare manualmente.

Window Functions: La Skill Segreta

Le window functions (OVER, PARTITION BY, ROW_NUMBER, LAG, LEAD) sono la vera potenza del SQL moderno per il marketing. Ti permettono di:

Calcolare running totals e medie mobili (ROWS BETWEEN)
Confrontare ogni riga con la riga precedente (LAG) o seguente (LEAD)
Rankingizzare all’interno di gruppi (RANK, ROW_NUMBER)
Calcolare percentili e quartili (PERCENTILE_CONT)

Padroneggiarle = liberarti da Excel e fogli di calcolo per sempre.

Perche Questa Skill e Diventata Non-Negoziabile

Nel 2023, LinkedIn ha inserito SQL tra le top 10 skill piu richieste in tutte le professioni legate al marketing digitale. Le offerte di lavoro che richiedono SQL hanno un salary premium del 15-25% rispetto a ruoli equivalenti senza SQL, secondo i dati di Indeed per il mercato europeo.

Ma al di la della carriera, c’e un motivo piu pratico. Un marketing manager che sa scrivere query SQL e autonomo. Non dipende dal data team per le domande quotidiane. Puo esplorare i dati con la curiosita di chi capisce il business.

Queste 15 query non sono esaustive. Sono un punto di partenza. Salvale, adattale ai tuoi dati, falle diventare parte del tuo flusso di lavoro settimanale. Ogni volta che qualcuno ti fa una domanda sui dati, pensa: “Quale query potrei scrivere per rispondere?” Non “Dovrei aspettare il data team”.

Relazione con il Corso GinnyTech

Approfondisci il modulo Advanced SQL su GinnyTech per padroneggiare le tecniche piu avanzate di query optimization, partitioning, clustering in BigQuery, e come costruire pipeline di dati per marketing analytics. Il modulo Analytics Engineering insegna come trasformare queste query in data models riutilizzabili per tutto il team.

Conclusione

Le 15 query che hai visto non cambieranno il tuo lavoro domani. Ma nei prossimi tre mesi, quando avrai costruito il set di query personalizzate per il tuo business e le avrai automatizzate, scoprirai che stai facendo decisioni di marketing basate su dati che ieri non avevi nemmeno accesso a. Quel e il vero valore: autonomia, velocita, precisione.

SQL e il linguaggio in cui il futuro del marketing racconta storie ai numeri. Impara a parlarlo.

SQL per Marketing: 15 Query Template Pronte all'Uso