Come Analizzare un Calo di Performance con BigQuery

Analizzare il calo delle performance legata ad una qualsiasi metrica (visitatori, fatturati, ordini, ecc…) e comprendere i fattori interni ed esterni che l’hanno causata, rappresenta una delle sfide più importanti per chi si occupa di analisi dati ed è in cerca di interpretazioni significative. L’obiettivo di questo articolo è quello di insegnare un metodo per analizzare un qualsiasi calo di performance, identificandone le cause.

Alcuni giorni fa mi sono imbattuto in post su Linkedin di Federica Brancale che recitava così:

[ANALISI CALO DELLE PERFORMANCE – 🎸 ESEMPIO GUIDATO]

a tutti capita di vedere un -x% e dover spiegare il perchè. vi condivido il METODO che uso per trovare quel perchè, frutto di tanti anni di fallimenti e successi:

👩‍💻 1) Contesto quantitativo: aggiungo un trend (più lungo del calo) per capire il trend (stagionalità, caso isolato) di quel calo. se la KPI lo permette faccio un benchmark per vedere se è una cosa comune al mercato o è solo mia. Potete aggiungere una previsione di quel trend per capire la priorità del problema.
☑ Esempio: il traffico è in calo da settembre, il calo è esponenziale e la previsione è che continui con un -30% se non facciamo niente. Nessun concorrente ha un calo simile.

👩‍💻2) Segmentazione: comincio a segmentare quella KPI per dimensioni che mi aiutano a restringere il campo del problema.
☑ Esempio: segmento per country, canale e device finchè non trovo una grande variazione. il calo è in italia, la SEO è calata del 40% in più rispetto agli altri canali. è lei!

👩‍💻3) Albero delle KPI: comincio a tornare indietro passando da KPI strategiche a tattiche,
☑ Esempio: guardo se è calato il posizionamento o il CTR. il posizionamento è uguale ma è calato il CTR del 50%

👩‍💻4) Contesto qualitativo: chiedo quali sono stati gli interventi (es, cambio di strategia di bids, cambio creatività ecc) ai responsabili di quel canale.
☑ Esempio: il team aveva appena ricaricato tutti i metatag e in quel paese il caricamento era stato fatto in francese (non ridere che è successo davvero 😉

Vi sono capitati casi in cui avete usato altri metodi?
Vi lascio il materiale per approfondire nei commenti, un articolo gratuito e il corso dove insegniamo questo metodo.

Vi lascio il link per leggere l’articolo completo che spiega step by step il metodo: Analisi del Traffico: La Guida per Analizzare il Calo.

La lettura di questo post è stata illuminante. In poche righe, ha la capacità di riordinare le idee nella testa di chi si dedica quotidianamente all’analisi dei dati, forse senza adottare un approccio chiaro e sistematico.

La mia prima riflessione è stata: perché non trasformare questa metodologia in un processo strutturato attorno a un’analisi condotta con BigQuery?

Per analizzare il calo delle performance del numero di visitatori utilizzando i dati di Google Analytics 4 (GA4) su BigQuery, potremmo seguire un approccio strutturato che si allinea ai punti elencati. Di seguito, fornirò delle query di esempio per ciascuno dei punti.

Tabella dei Contenuti

1) Analisi del Contesto Quantitativo

Per individuare il calo, è necessario calcolare il trend storico dei visitatori.

La seguente query restituisce il conteggio dei visitatori unici giornalieri. Utilizzando questi dati, è possibile esaminare l’andamento nel tempo e confrontarlo con dei benchmark esterni, quando questi sono disponibili.

Il lasso temporale preso in considerazione per questa e per le analisi successive è di 60 giorni, a partire dal 15/01/2024. Ipotizziamo che sia questo il periodo in cui abbiamo individuato il calo.

SELECT
  DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp)), DAY) AS day,
  COUNT(DISTINCT user_pseudo_id) AS unique_visitors
FROM
  `your_project.your_dataset.your_table`
WHERE
  event_name = 'session_start'
  AND DATE(TIMESTAMP_MICROS(event_timestamp)) BETWEEN '2024-01-15' AND DATE_ADD('2024-01-15', INTERVAL 60 DAY)
GROUP BY
  day
ORDER BY
  day;

PS. Ricorda di sostituire your_project, your_dataset e your_table con i tuoi identificativi di progetto, dataset e tabella effettivi. Queste query sono solo punti di partenza e potrebbero necessitare di personalizzazioni per adattarsi alle tue esigenze specifiche e alla struttura dei tuoi dati.

Questo è il report prodotto dalla query, fornito qui a scopo dimostrativo:

Potremmo condurre la stessa analisi sul trend dei visitatori con una query che fornisce il numero di visitatori unici per ogni mese.

SELECT
  DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp)), MONTH) AS month,
  COUNT(DISTINCT user_pseudo_id) AS unique_visitors
FROM
  `your_project.your_dataset.your_table`
WHERE
  event_name = 'session_start'
GROUP BY
  month
ORDER BY
  month

Ecco il risultato della query:

2) Segmentazione

Attraverso la segmentazione, circoscriviamo l’ambito del problema. La questione che ci poniamo è: quale dimensione sta influenzando il calo? Il paese, il canale o il dispositivo?

Ovviamente sii libero di analizzare altre dimensioni.

Per dividere i dati secondo queste dimensioni, potresti adottare un approccio simile:

SELECT
  geo.country AS country,
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  device.category AS device_category,
  COUNT(DISTINCT user_pseudo_id) AS unique_visitors
FROM
   `your_project.your_dataset.your_table`
WHERE
  event_name = 'session_start'
  AND TIMESTAMP_MICROS(event_timestamp) BETWEEN TIMESTAMP('2024-01-15') AND TIMESTAMP(DATE_ADD(DATE '2024-01-15', INTERVAL 60 DAY))
GROUP BY
  country, source, medium, device_category
ORDER BY
  unique_visitors DESC;

La segmentazione per dimensioni ti aiuta a identificare dove si verifica il maggior calo.

Per rendere più evidente il calo di prestazioni, le righe sono ordinate per unique_visitors decrescente.

Ecco il report generato:

3) Albero delle KPI

Se la diminuzione è attribuibile alla sorgente/mezzo, come Google Organic, ci chiediamo: quali sono le cause? Una riduzione della visibilità o del CTR? Per esaminare KPI più dettagliate quali il Posizionamento o il CTR, è necessario disporre di dati specifici relativi a queste metriche. Naturalmente, si potrebbe considerare l’impiego di una Query sulla Google Search Console per ottenere una tabella dedicata a tali metriche.

Calcolo delle variazioni percentuali per query della posizione e del CTR

WITH current_period AS (
  SELECT
    query,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    ROUND(SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)), 2) AS avg_position,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS avg_ctr_percentage
  FROM
 `your_project.searchconsole.searchdata_site_impression*`
  WHERE
    data_date BETWEEN '2024-01-15' AND DATE_ADD('2024-01-15', INTERVAL 60 DAY)
  GROUP BY
    query
),
previous_period AS (
  SELECT
    query,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    ROUND(SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)), 2) AS avg_position,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS avg_ctr_percentage
  FROM
  `your_project.searchconsole.searchdata_site_impression*`
  WHERE
    data_date BETWEEN DATE_SUB('2024-01-15', INTERVAL 60 DAY) AND '2024-01-14'
  GROUP BY
    query
)
SELECT
  cp.query,
  cp.total_clicks,
  cp.total_impressions,
  cp.avg_position,
  cp.avg_ctr_percentage,
  pp.avg_position AS prev_avg_position,
  pp.avg_ctr_percentage AS prev_avg_ctr_percentage,
  ROUND(SAFE_DIVIDE(cp.avg_position - pp.avg_position, pp.avg_position) * 100, 2) AS diff_avg_position_percentage,
  ROUND(SAFE_DIVIDE(cp.avg_ctr_percentage - pp.avg_ctr_percentage, pp.avg_ctr_percentage) * 100, 2) AS diff_avg_ctr_percentage
FROM
  current_period cp
JOIN
  previous_period pp
ON
  cp.query = pp.query
ORDER BY
  cp.total_clicks DESC;

Questa query produce un report che evidenzia le variazioni percentuali, sia positive che negative, in termini di posizione e CTR, mettendo a confronto due periodi (nell’esempio, si considerano 60 giorni a partire dal 15/01/2024 e si effettua il confronto con gli stessi giorni del periodo antecedente).

Essa è strategiche perchè evidenzia a colpo d’occhio la metrica “incriminata” e le query su cui sta impattando!

Trattandosi di una query un pò più complessa rispetto alle altre facciamo una sintesi del processo:

Definizione del Periodo Attuale (current_period):

Seleziona query, clicks, impressions e calcola il totale dei clic, il totale delle impressioni, la posizione media (calcolata come la divisione sicura tra la somma delle posizioni di cima e il totale delle impressioni) e il tasso medio di click-through (CTR) percentuale per ogni query.
Filtra i dati tra ‘2024-01-15’ e 60 giorni dopo questa data.
Raggruppa i risultati per query.

Definizione del Periodo Precedente (previous_period):

Simile al “periodo attuale”, ma seleziona e calcola le metriche per il periodo che va da 60 giorni prima del ‘2024-01-15’ al giorno precedente (‘2024-01-14’).
Anche in questo caso, raggruppa i risultati per query.

Confronto dei Periodi:

Confronta i dati del “periodo attuale” con quelli del “periodo precedente” per ogni query.
Seleziona le query e le relative metriche (click totali, impressioni totali, posizione media, CTR medio percentuale) per il periodo attuale.
Include anche la posizione media e il CTR medio percentuale del periodo precedente per confronto.
Calcola la differenza percentuale della posizione media e del CTR medio tra i due periodi.

Ordinamento e Risultati Finali:

Ordina i risultati in base al numero totale di clic del periodo attuale in ordine decrescente.

Calcolo delle variazioni percentuali per pagina delle impressions, clicks e CTR

L’analisi può essere approfondita mostrando per ciascuna pagina le variazioni percentuali, sia positive che negative, in impressions, clicks e CTR:

WITH current_period AS (
  SELECT
    url AS page,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS avg_ctr_percentage
  FROM
    `my-project.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN '2024-01-15' AND DATE_ADD('2024-01-15', INTERVAL 60 DAY)
  GROUP BY
    url
),
previous_period AS (
  SELECT
    url AS page,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS avg_ctr_percentage
  FROM
    `my-project.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN DATE_SUB('2024-01-15', INTERVAL 60 DAY) AND '2024-01-14'
  GROUP BY
    url
)
SELECT
  cp.page,
  cp.total_impressions,
  cp.total_clicks,
  cp.avg_ctr_percentage,
  pp.total_impressions AS prev_total_impressions,
  pp.total_clicks AS prev_total_clicks,
  pp.avg_ctr_percentage AS prev_avg_ctr_percentage,
  ROUND(SAFE_DIVIDE(cp.total_impressions - pp.total_impressions, pp.total_impressions) * 100, 2) AS var_total_impressions_percentage,
  ROUND(SAFE_DIVIDE(cp.total_clicks - pp.total_clicks, pp.total_clicks) * 100, 2) AS var_total_clicks_percentage,
  ROUND(SAFE_DIVIDE(cp.avg_ctr_percentage - pp.avg_ctr_percentage, pp.avg_ctr_percentage) * 100, 2) AS var_avg_ctr_percentage
FROM
  current_period cp
JOIN
  previous_period pp
ON
  cp.page = pp.page
ORDER BY
  cp.total_impressions DESC;

Ecco il report generato dalla query mettendo a confronto i due periodi (60 giorni a partire dal 15/01/2024 confrontati con gli stessi giorni del periodo antecedente):

In sintesi il processo implementato nella query:

Definizione del Periodo Attuale (current_period):

Seleziona l’URL come page e calcola il totale delle impressioni, il totale dei clic e la percentuale media di CTR per ogni pagina.
Filtra i dati tra ‘2024-01-15’ e 60 giorni dopo questa data.
Raggruppa i risultati per URL.

Definizione del Periodo Precedente (previous_period):

Esegue operazioni simili a quelle del “periodo attuale”, ma per il periodo che va da 60 giorni prima del ‘2024-01-15’ fino al ‘2024-01-14’.
Raggruppa i risultati per URL.

Confronto dei Periodi:

Effettua un join tra i dati del “periodo attuale” e quelli del “periodo precedente” basandosi sulle page (URL).
Seleziona l’URL, le metriche di impressioni e clic per entrambi i periodi, oltre alla percentuale media di CTR.
Calcola la variazione percentuale delle impressioni totali, dei clic totali e della percentuale media di CTR tra i due periodi.

Ordinamento e Risultati Finali:

Ordina i risultati in base al numero totale di impressioni del periodo attuale in ordine decrescente.

La query fornisce una visione dettagliata delle variazioni nelle prestazioni delle pagine web, misurate tramite impressioni, clic e CTR, permettendo di identificare le tendenze positive o negative nel comportamento degli utenti e l’efficacia delle pagine nel catturare l’attenzione e generare interazioni nel tempo.

4) Contesto Qualitativo

Per il contesto qualitativo, dovresti integrare le informazioni qualitative con le tue analisi quantitative. Non esiste una query SQL specifica per questo, poiché dipende dalla raccolta e dall’integrazione di feedback o dati di intervento.

Conclusione

In conclusione, il metodo presentato da Federica offre un approccio strutturato e dettagliato per analizzare e comprendere le cause dietro un calo delle performance, combinando analisi quantitative e qualitative. Attraverso l’esame del contesto quantitativo, la segmentazione mirata, l’analisi approfondita delle KPI e infine l’indagine del contesto qualitativo, esso si rivela un potente strumento per identificare le radici del problema e intraprendere le azioni correttive necessarie.

Giuseppe Cristofaro

Data Scientist
Sono un Data Scientist con esperienza nell’applicazione di tecniche avanzate di Machine Learning per l’analisi di complessi set di dati nel campo del marketing digitale. Specializzato nell’uso di Python, sviluppo modelli predittivi e di analisi dei dati che permettono di identificare e risolvere criticità nei progetti.

Data Genius

Hai un Calo delle Performance? Ecco un Metodo per Analizzare le Cause Utilizzando BigQuery