Analisi dei Nuovi Utenti con BigQuery per Campagne Marketing

Uno degli obiettivi che ci possiamo prefissare per una nuova campagna marketing, sia essa online od offline, può essere l’attrarre nuovi utenti nel nostro sito web o ottenere nuovi clienti per la nostra azienda.

In questo articolo vi mostro come creare un grafico che tenga traccia dei nuovi utenti che visitano il nostro sito web. Tenete conto che lo stesso codice può essere applicato in caso avessimo la lista di tutti gli ordini effettuati negli anni precedenti e volessimo tenere traccia di quanti nuovi clienti abbiamo avuto ogni mese.

Cosa intendo però con “nuovi utenti”? È possibile infatti personalizzare la query SQL sottostante secondo la propria personale definizione, ma in questo esempio io intendo “gli utenti che hanno visitato l’ ecommerce nell’ ultimo mese, ma che non lo hanno fatto negli 11 mesi precedenti”.

Ciò significa che, nel caso un utente ci abbia visitato 2 anni fa per poi sparire nel vuoto cosmico e ripresentarsi durante l’ ultimo mese, verrà considerato “nuovo utente”.

Vantaggi:

In questo modo è possibile tener traccia dei risultati delle attività svolte con l’obiettivo di attrarre nuovi utenti/clienti.

Dati:

Dataset con gli eventi triggerati dagli utenti nel sito web o con gli ordini fatti dai clienti, divisi per i giorni dell’anno. Come nei precedenti articoli, ho usato un sample di Google Analytics 4 che Google mette a disposizione https://developers.google.com/analytics/bigquery/web-ecommerce-demo-dataset

Gli strumenti che ho utilizzato per fare quest’ analisi sono BigQuery e qualche riga di Python per ottenere il grafico finale.

Nuovi utenti in BigQuery – La funzione

Il primo passo verso la realizzazione di questo report è ottenere il numero di nuovi utenti per un singolo mese, per poi ripetere la stessa procedura anche per i mesi passati.

Proprio per questo “ripetere”, ho deciso di utilizzare una User Defined Function in BigQuery. È possibile infatti creare delle funzioni, salvarle e utilizzarle poi in una successiva query.

Qui di seguito trovate la funzione completa, andremo a spezzettarla subito dopo.

Codice funzione

CREATE OR REPLACE FUNCTION `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set INT64, date_last_day DATE) RETURNS INT64 AS (


(#all users
  with t1 as (
    select distinct PARSE_DATE("%Y%m%d", event_date) as data_ordine, user_pseudo_id as ID_Utente
    from `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_*`
  )


  select count(distinct t1.ID_Utente) as new_users_count
  from t1
  where t1.ID_Utente in (
    #users last month
    SELECT distinct ID_Utente
    FROM t1
    WHERE t1.data_ordine BETWEEN DATE_SUB(date_last_day, INTERVAL 1+off_set MONTH) AND DATE_SUB(date_last_day, interval off_set MONTH)
  )

  and t1.ID_Utente not in (
    #users of the 11 months before the previous month
    SELECT distinct ID_Utente
    FROM t1
    WHERE t1.data_ordine BETWEEN DATE_SUB(date_last_day, INTERVAL 12+off_set MONTH) AND DATE_SUB(date_last_day, INTERVAL 1+off_set MONTH))
  )
);

I parametri che la funzione si aspetta come input sono 2:

off_set —> è di tipo INT64, perciò accetta solo numeri interi come valore. Questa variabile serve per dire alla funzione di quanti mesi spostarsi indietro rispetto alla data odierna per iniziare i suoi calcoli. Forse ora non è semplice da capire perchè è necessaria, ma fidatevi, dopo sarà chiarissimo
date_last_day —> qui passiamo in formato DATE la data dell’ ultimo giorno del mese corrente

Con la prima query andiamo a salvare in una tabella, che con tanta creatività ho chiamato t1, tutti gli utenti che hanno visitato il nostro sito web assieme alla data in cui lo hanno fatto.

Non ci servono duplicati in questo caso, quindi utilizziamo un SELECT DISTINCT.

In caso volessimo andare ad estrarre solamente i dati relativi ad uno specifico periodo, possiamo utilizzare la variabile _table_suffix che si va a creare quando utilizziamo un asterisco nel parametro FROM, quando cioè vogliamo cercare in più tabelle con una sola query.

Codice

SELECT distinct PARSE_DATE("%Y%m%d", event_date) as data_ordine, user_pseudo_id as ID_Utente
FROM `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.events_*`
WHERE REGEXP_CONTAINS(_table_suffix, '^(2020|2021).*')

Qui diciamo a BigQuery di darci la data e il codice utente, senza duplicati, di tutti gli utenti che hanno visitato il nostro sito web tra il 2020 e il 2021.

Note:
In questo esempio tratto ogni evento di GA4 come se fosse un ‘purchase’ per semplicità

Se volete approfondire cosa sia e come può essere usata la variabile _table_suffix, vi riporto qui il link alla documentazione Google

https://cloud.google.com/bigquery/docs/querying-wildcard-tables

Passiamo adesso alla prima subquery:

Codice

#users last month
SELECT distinct ID_Utente
FROM t1
WHERE t1.data_ordine BETWEEN DATE_SUB(date_last_day, INTERVAL 1+off_set MONTH) AND DATE_SUB(date_last_day, interval off_set MONTH)

Qui selezioniamo solamente gli utenti che hanno visitato l’ ecommerce nell’ ultimo mese. Per farlo, utilizziamo il controllo BETWEEN che ci dice se t1.data_ordine, cioè la data in cui l’ utente ha fatto l’ordine, si trova tra le due date che noi specifichiamo.

Attenzione però: per rendere il grafico più comprensibile all’essere umano, non sottraiamo semplicemente 30 giorni dalla data odierna per calcolare il mese, ma manteniamo la normale divisione dei mesi durante l’anno. È qui che entra in gioco il parametro date_last_day definito in precedenza. Utilizzando sempre le date di fine mese e la funzione DATE_SUB di BigQuery per sottrarre mesi a quelle date, noi siamo sicuri di mantenere la divisione da calendario tra i mesi.

Il secondo parametro utilizzato nella funzione DATE_SUB è off_set, cioè il numero di mesi di cui vogliamo traslare la nostra funzione nel passato. Provo a spiegare con l’ aiuto di uno schema:

Come vediamo qui, off_set è una sorta di blocco che mettiamo tra la data di fine mese odierno e la data da cui vogliamo far partire la funzione con i suoi calcoli. In questo modo è possibile richiamare sempre la stessa funzione, ma tornando di volta in volta indietro di un mese.

Vediamo ora la seconda subquery.

Codice

#users of the 11 months before the previous month
SELECT distinct ID_Utente
FROM t1
WHERE t1.data_ordine BETWEEN DATE_SUB(date_last_day, INTERVAL 12+off_set MONTH) AND DATE_SUB(date_last_day, INTERVAL 1+off_set MONTH))

Il ragionamento è lo stesso della subquery precedente, solo che stavolta andiamo a prenderci gli utenti che hanno visitato il sito web negli 11 mesi precedenti all’ ultimo mese. Possiamo vedere infatti che i valori del secondo parametro passato alla funzione DATE_SUB sono leggermente differenti.

Il tutto viene infine ripreso dalla query principale, dove andiamo a selezionare i codici utente degli utenti appartenenti al primo gruppo (sito web visitato nell’ ultimo mese) ma non al secondo (sito web visitato negli 11 mesi precedenti all’ ultimo mese).

La funzione è così completa, andiamo a vedere ora come richiamarla dalla query che utilizzeremo per creare la tabella con i risultati finali.

Nuovi utenti in BigQuery – Query finale

Ora, per poter avere i dati per la costruzione del grafico, dobbiamo avere una tabella con i risultati del codice SQL visto sopra ma relativo ai 12 mesi passati.

Proprio perchè abbiamo creato una funzione, ci è possibile richiamarla 12 volte ma con il valore off_set sempre differente.

Ricordiamoci però anche del secondo parametro, date_last_day. Con la prima riga di codice ce lo andiamo a creare utilizzando la funzione LAST_DAY, già presente in BigQuery.

Query finale

declare date_to_use date default LAST_DAY(CURRENT_DATE(), MONTH);

SELECT 
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(28,date_to_use) as month_0,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(29,date_to_use) as month_1,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(30,date_to_use) as month_2,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(31,date_to_use) as month_3,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(32,date_to_use) as month_4,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(33,date_to_use) as month_5,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(34,date_to_use) as month_6,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(35,date_to_use) as month_7,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(36,date_to_use) as month_8,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(37,date_to_use) as month_9,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(38,date_to_use) as month_10,
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(39,date_to_use) as month_11

La tabella risultante è la seguente:

Note:
month_0 è il mese presente. Perciò month_1 non si riferisce a Gennaio, ma al mese precedente di quello per cui la funzione ha iniziato a fare i calcoli (quindi con offset_month=1)

Perfetto, abbiamo tutto ciò che ci serve. Passiamo all’ ultima fase.

Nuovi utenti in BigQuery – Il grafico

Ora ci basterà prendere qualsiasi altro strumento per creare il grafico. Nel mio caso uso una libreria in Python. Una piccola particolarità qui è data da “invert_xaxis()”. Proprio perchè vogliamo dare l’ idea di andare a ritroso con i mesi, facciamo partire il grafico da destra anzichè da sinistra.

Codice per il grafico

import matplotlib.pyplot as plt
plt.bar(range(12), result.iloc[0])
plt.title('Bar Chart')
plt.xlabel('Offset from current month')
plt.ylabel('New users')

plt.gca().invert_xaxis()

plt.show()

Ed eccolo qui, in tutta la sua bellezza monocromatica. Immaginiamo di aver lanciato questa query ad Agosto e di aver spinto molto con dei forti sconti estivi in una campagna a Luglio.

Grazie a questo grafico possiamo vedere che l’ effetto c’è effettivamente stato data la differenza tra Luglio e Giugno, ma che già ad Agosto si sta ritornando verso i valori più bassi pre-campagna marketing.

Qui avrebbe dovuto chiudersi l’ articolo, ma nel rileggerlo mi sono immaginato tutte le persone che non hanno mai visto il codice che sta dietro a questo grafico e che quindi farebbero molta fatica a capire di che mesi stiamo parlando. Così ho migliorato il tutto, sempre a grazie a Python e alle sue innumerevoli librerie gratuite che ci permettono di fare ogni cosa.

Importiamo quindi le librerie necessarie:

Codice

import calendar
from datetime import date
from dateutil.relativedelta import relativedelta
off_set_months = 28

Modifichiamo poi la query che passiamo a BigQuery.

Codice python

query = """

declare date_to_use date default LAST_DAY(CURRENT_DATE(), MONTH);
declare off_set_months int64 default """+str(off_set_months)+""";

SELECT 
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+1,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-1)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+2,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-2)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+3,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-3)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+4,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-4)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+5,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-5)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+6,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-6)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+7,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-7)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+8,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-8)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+9,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-9)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+10,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-10)).month])+""",
  `analyticstraining-358318.ga4_obfuscated_sample_ecommerce_copy.func_users`(off_set_months+11,date_to_use) as """+str(calendar.month_name[(date.today()+relativedelta(months=-off_set_months-11)).month])+"""



        """

result = client.query(query).result().to_dataframe()

Quali sono i cambiamenti?

Prima di tutto vediamo come la variabile off_set_months, chiamata nello stesso modo In SQL e in Python, ora viene inizializzata in Python e passata poi a BigQuery inserendola all’ interno della query manualmente. Perchè questo? Perchè avere quel numero disponibile in Python ci permette di fare una cosa molto interessante che altrimenti non sarebbe possibile in SQL: dare nomi dinamici alle colonne.

Vediamo infatti che i nomi delle colonne non sono più “month_0, month_1” etc, ma sono creati dinamicamente da Python tramite questa matriosca di funzioni

Codice

calendar.month_name[(date.today()+relativedelta(months=-off_set_months)).month]

Sembra complicato ma non lo è veramente.

Per spiegare cosa fa questa riga di codice, partiamo da quello che dovrebbe essere l’obiettivo finale: senza ricordarci l’off_set che abbiamo inserito (o che un altro tizio del reparto IT ha inserito) dobbiamo essere in grado di capire subito a che mesi il grafico si riferisce. Di fatto questa cosa è possibile visto che Python e SQL lavorano direttamente con le date.

Come prima cosa utilizzo date.today()+relativedelta(months=-off_set_months) per ottenere la data a cui sta puntando la query SQL e utilizzo poi il metodo .month in modo da ottenere il numero di quel mese.

Potrebbe anche bastare ma con una semplice libreria possiamo stampare il nome di quel mese, in modo che il grafico sia ancora più leggibile. Ecco quindi che uso la funzione calendar.month_name.

Vediamo il risultato finale:

Codice per il grafico

import matplotlib.pyplot as plt
plt.bar(result.columns, result.iloc[0])
plt.title('Bar Chart')
plt.xlabel('Offset from current month')
plt.ylabel('New users')

plt.gca().invert_xaxis()
plt.xticks(rotation=45)

plt.show()

Ora è decisamente più semplice leggere questo grafico 💪

L’articolo finisce qui, questa volta veramente. Spero che questa lettura vi sia piaciuta. Per qualsiasi feedback o domanda, vi lascio alla zona commenti.

Nuovi utenti in BigQuery – La funzione

Nuovi utenti in BigQuery – Query finale

Nuovi utenti in BigQuery – Il grafico

Lascia un commento Annulla risposta