In questo articolo vedremo come utilizzare semplici comandi SQL e Python per ottenere un grafico con l’andamento del traffico giornaliero di un sito web.
Vantaggi:
- Generale controllo sull’andamento
- Identificare visivamente pericolosi cali di traffico
Dati:
- Dataset Google Analytics 4 del Google Merchandise Store, per gentile concessione di Google https://developers.google.com/analytics/bigquery/web-ecommerce-demo-dataset
Cali di Traffico in BigQuery
Come prima cosa apriamo la nostra console SQL di BigQuery.
Incolliamo quindi il codice SQL che ci serve:
Codice
select
count(distinct user_pseudo_id) as number_users,
event_date
from `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_2020112*`
group by event_date
Iniziamo a capire il codice partendo dal comando from.
Questo indicherà a BigQuery da dove attingere i dati per soddisfare le nostre richieste. Fortunatamente Google ci mette a disposizione moltissimi database su cui possiamo esercitarci. Nel mio caso, ho fatto una copia in un mio progetto personale del database Google Analytics 4 basato sull’ecommerce del merchandise Google.
Alla fine della riga c’è un asterisco. Questo sta a significare “hei BigQuery, dammi i dati appartenenti a tutte le tabelle con il nome coincidente con quanto ho scritto, seguito poi da qualsiasi altro carattere”.
In questo modo con un comando solo ci possiamo riferire a più tabelle contemporaneamente. Comodo eh?
Le tabelle sono infatti le seguenti:
Codice
`analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_ events_20201101`
`analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_ events_20201102`
`analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_ events_20201103`
…
`analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_ events_20210129`
`analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_ events_20210130`
`analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_ events_20210131`
Con il comando “select” selezioniamo due variabili:
- count(distinct user_pseudo_id) à con questa variabile contiamo tutti gli utenti che hanno visitato il sito web. Utilizziamo infatti la funzione count(). C’è però un particolare: dobbiamo utilizzare anche il parametro “distinct” così da evitare le duplicazioni dovute ai molti eventi triggerati da uno stesso utente (ricordiamoci che in GA4, ogni riga della tabella corrisponde ad un singolo evento triggerato nel sito)
- Event_date à è la data in cui l’utente ha visitato il nostro sito. Ci servirà poi per costruire il grafico
L’ultimo comando che usiamo è il group by. Questo comando SQL viene usato per raggruppare i dati secondo un certo criterio, in questo caso la data.
Così, se un utente ha visitato lo store Google il 15/11/2020 per poi visitarlo nuovamente il 20/12/2020, verrà giustamente conteggiato in entrambe le giornate.
Una volta raccolti i dati che ci servono, entra in gioco Python. Dobbiamo creare un semplicissimo grafico dove mostreremo i vari cali e i picchi di traffico per il periodo preso in analisi.
Ci sono vari modi per esportare i dati da BigQuery, ma per grandi dataset l’unico modo è creare un collegamento diretto da Python (sarà materiale per un altro articolo).
Nel mio caso, ho salvato il dataset risultante all’interno della variabile “result”. Ecco qui una sua preview.
Usiamo adesso il seguente codice Python:
Codice
from datetime import datetime, timedelta
result['date'] = ''
for i in range(0,len(result['event_date'])):
result['date'][i] = datetime(year=int(result['event_date'][i][0:4]), month=int(result['event_date'][i][4:6]), day=int(result['event_date'][i][6:8]))
L’idea generale è quella di convertire la colonna “event_date” in qualcosa di più facilmente interpretabile da Python. Ho quindi importato le opportune librerie (datetime e timedelta) e creato la nuova colonna, che per il momento rimane vuota.
A seguire c’è il ciclo for di cui abbiamo bisogno. Per tutta la lunghezza della tabella, lui si prende il valore che trova nella colonna “event_date” e lo divide in 3 pezzi distinti:
- Le prime 4 cifre saranno l’anno
- Le seconde 2 cifre il mese
- Le ultime 2 cifre il giorno
Viene poi tutto ricomposto in modo da formare la colonna nel formato che abbiamo impostato noi (e che soprattutto piace a Python). Ecco il risultato:
Eccoci arrivati all’ultimo passaggio, la creazione del grafico. In questo caso è molto semplice, ci basta una sola riga di codice dove indichiamo cosa usare come asse x e asse y.
Codice
result.plot.line(x='date',y='number_users')
Finalmente possiamo vedere il nostro bel grafico. Vediamo subito come l’andamento del traffico ha un ciclo settimanale, con un picco di utenti prima di Natale (chi non vorrebbe un gadget Google come regalo?) e un forte calo per le feste di capodanno (meglio non fare campagne marketing quando la gente è già troppo occupata a brindare).
Siamo arrivati alla fine di questo breve tutorial. Se hai consigli o richieste, commenta pure nel box che vedi qui sotto 👍