Come analizzare i dati di Google Search Console con Excel

Vediamo come analizzare i dati di Google Search Console con Excel utilizzando Power Query e le ultime formule di Excel 365. Questa procedura può essere molto utile per aiutarti nella revisione delle analisi dei dati che arrivano da Search Console, che possono aiutare i professionisti a capire dove intervenire per migliorare la presenza online dei singoli siti web.

Se hai già esperienza con l'utilizzo di Power Query, puoi scaricare uno di questi due file per poi aggiornare la fonte dati e inserire i dati del tuo sito web:

Scaricando il file, potrai selezionare i parametri per una ricerca dinamica in tempo reale che ti restituirà i dati che ti interessano per migliorare i risultati del tuo sito. Se vuoi approfondire il tema nel suo complesso, continua pure a leggere per scoprire meglio come ricreare questo file da zero e il perchè di alcune formule speciali.

Come scaricare i dati di Google Search Console

Prima di analizzare i dati su Excel, dobbiamo aggiungere Google Search Console al nostro sito e aspettare qualche giorno per avere i risultati. Il nostro export si popolerà di dati e potremo cominciare ad analizzarli. Se non conosci l'argomento, non ti preoccupare: online ci sono tantissimi guide dettagliata per eseguire questa procedura, che varierà a seconda dell'architettura del tuo sito e dell'hosting che stai utilizzando.

Per accedere ai risultati, dobbiamo andare su Search results, che trovi nella barra laterale sulla sinistra sotto Overview.

Accedi alla stessa schermata cliccando su Full report in alto a destra rispetto al grafico che trovi su Overview.

Se ti trovi su Search results, noterai una scritta EXPORT subito sotto la tua foto profilo. Clicca e scarica i dati in formato Excel, se vuoi seguire questa guida nel dettaglio. Lo stesso risultato si può raggiungere con un file CSV, ma alcune trasformazioni potrebbero essere leggermente diverse.

A questo punto, abbiamo il nostro file di export con i nostri dati di ricerca. Questo file includerà le 1000 query più importanti del tuo sito, quindi offre uno spaccato già piuttosto preciso per effettuare delle analisi. Se al momento non disponi di Google Search Console ma vuoi approfondire questa procedura. puoi scaricare il file da qui. Il database che ho creato è realistico perchè prende le query di ricerca più utilizzate al mondo per "Excel", anche se i dati sono stati generati in maniera casuale.

Buttarsi subito a capofitto nella parte di analisi sarebbe un errore, dato che vogliamo creare una struttura scalabile. L'ultima cosa che vogliamo fare è rifare la procedura da zero alla prossima estrazione dei nostri dati, quindi passiamo da Power Query per automatizzare questa prima parte. Esistono anche tool a pagamento che permettono di collegare direttamente Google Search Console a Power Query, ma qui vediamo il caso gratuito. Purtroppo, al momento, non esiste un connettore dedicato.

Come caricare i dati di Google Search Console in Power Query su Excel

Una volta ottenuti i dati di Google Search Console, abbiamo due opzioni per caricare i nostri dati su Excel tramite Power Query:

• Cambiamo il nome del file estratto, così avremo sempre un file di origine chiamato "PERFORMANCE_SEARCH_CONSOLE_EXCEL"
• Utilizziamo un parametro in Power Query che modificheremo ogni volta che scarichiamo un nuovo file di export

Primo caso: rinominiamo il file e lo chiamiamo PERFORMANCE_SEARCH_CONSOLE_EXCEL, inserendo sul nostro desktop che è sul disco G.

Lato Power Query, avremo un codice tipo questo:

let
Origine = Excel.Workbook(File.Contents("C:\Users\marco\OneDrive\Desktop\SEARCH_CONSOLE\PERFORMANCE_SEARCH_CONSOLE_EXCEL.xlsx"), null, true),
Queries_Sheet = Origine{[Item="Queries",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(Queries_Sheet, [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"Top queries", type text}, {"Clicks", Int64.Type}, {"Impressions", Int64.Type}, {"CTR", Percentage.Type}, {"Position", type number}}),
#"Filtrate righe" = Table.SelectRows(#"Modificato tipo", each ([Top queries] <> null))
in
#"Filtrate righe"

Vediamolo nel dettaglio:

1. Excel.Workbook: Apre un file Excel e restituisce le tabelle, le intervalli e le definizioni dei nomi nel file.
   • Utilizza File.Contents per leggere il contenuto del file Excel specificato nel percorso.
   • Imposta il parametro 'useHeaders' su true per utilizzare le intestazioni delle colonne presenti nel foglio.
2. Origine{[Item="Queries",Kind="Sheet"]}[Data]: Seleziona il foglio "Queries" dal file Excel e ne estrae i dati. In questo caso, i nostri dati non sono in una tabella, quindi Excel selezionerà tutti i dati finchè li trova sulle singole righe
3. Table.PromoteHeaders: Promuove la prima riga della tabella a intestazioni di colonna.
   • Imposta il parametro 'PromoteAllScalars' su true per promuovere tutti i valori scalari come intestazioni. Abbiamo quindi le singole intestazioni per la nostra tabella finale
4. Table.TransformColumnTypes: Modifica i tipi di dati delle colonne specificate nella tabella. Questo passaggio è automatico, ma possiamo sempre intervenire sul codice per modificarli singolarmente
   • Imposta il tipo di dati per la colonna "Top queries" come 'text'.
   • Imposta il tipo di dati per la colonna "Clicks" come 'Int64.Type'.
   • Imposta il tipo di dati per la colonna "Impressions" come 'Int64.Type'.
   • Imposta il tipo di dati per la colonna "CTR" come 'Percentage.Type'.
   • Imposta il tipo di dati per la colonna "Position" come 'type number'.
5. Table.SelectRows: Filtra le righe della tabella in base alla condizione specificata. Ho inserito questa condizione giusto per evitare che vengano pescate dei valori vuoti nelle singole query
   • Filtra le righe in modo da mantenere solo quelle in cui il valore della colonna "Top queries" è diverso da 'null'.
6. in: Indica la fine delle istruzioni 'let' e specifica il risultato finale del codice Power Query, in questo caso, la tabella filtrata ottenuta dopo aver applicato tutte le trasformazioni.

Lato codice, non c'è niente di particolarmente complicato dato che sono singoli passaggi manuali abbastanza rapidi: per fortuna, il file che arriva da Google Search console è già abbastanza preciso quindi ci va benissimo così.

Prima di usare la query, verifica dove si trovi il file scaricato da Google Search Console. Clicca con il tasto destro del mouse e copia il percorso del file, togliendo le virgolette "". A questo punto, ti basterà cambiare la stringa da:

C:\Users\marco\OneDrive\Desktop\SEARCH_CONSOLE\PERFORMANCE_SEARCH_CONSOLE_EXCEL.xlsx

al percorso del tuo nuovo file, scaricato da Google Search Console. Cliccando su Aggiorna Query, avrai i tuoi dati anzichè quelli della guida!

La tabella finale si chiamerà Queries, teniamolo a mente per la parte successiva della guida.

Come dicevamo, possiamo però velocizzare questa procedura creando un parametro su Power Query. Vediamo come.

Come creare un parametro su Power Query

Un modo più elegante per cambiare il percorso file velocemente su Power Query è la creazione di un parametro.

Abbiamo già creato la nostra tabella, quindi torniamo su Power Query e nella schermata Home selezioniamo Parametri > Nuovo parametro.

Le sezioni sono piuttosto intuitive:

• Nome sarà il nome del nostro parametro. Io l'ho chiamato GSC_SOURCE ma puoi decidere il nome che preferisci
• Descrizione ci permette, se lo vogliamo, di inserire una breve descrizione del nostro parametro
• Obbligatorio e tipo possono essere utili se lavorassimo con dati più complicati, ma non ci interessano in questo caso
• Valori suggeriti non richiede alcun valore suggerito, in questo caso
• Valore corrente è molto importante. Qui ci inserisco l'intero percorso del file scaricato con i dati di Google Search Console, senza inserire però le virgolette ""

Clicchiamo su OK e andiamo avanti.

La nostra schermata di Power Query cambia leggermente, dato che vedremo il nuovo parametro inserito come se fosse una nuova query. Ti accorgi che non è una query standard dato che vediamo già parte del percorso del file.

A questo punto andiamo nella nostra query chiamata Queries. Recuperiamo la prima parte dove troviamo Excel.Workbook(File.Contents, che è la sezione Origine. Qui troviamo l'intero percorso del nostro file attuale, che dovremo sostituire con il nostro nuovo parametro. Attenzione alle virgolette perchè devono essere rimosse.

Adesso, Excel pescherà il valore del parametro e non il percorso inserito precedentemente. Il vantaggio è che potremo modificare il percorso del parametro direttamente dalla query dedicata, evitando di toccare continuamente il codice visto in precedenza. Avremo, in questo modo, meno possibilità di sbagliare e più semplicità nella parte di debugging finale.

Nel pannello Query e connessioni vedrai che il parametro è stato caricato come Sola connessione. Questo è normale, dato che, aggiornata la query chiamata Queries, sarà aggiornata anche il nostro parametro - che non ha bisogno di essere caricato da nessuna parte, non essendo utilizzato per altri scopi se non per orientare la ricerca dei dati di export di Google Search Console.

Cliccando con il tasto destro e selezionando Modifica, potrai modificare il percorso come preferisci. A prescindere dalla soluzione che preferisci, dovrai ricordarti di fare un'operazione manuale quando avrai scaricato i nuovi dati per avere la tua dashboard sempre aggiornata.

Un'ulteriore terza strada potrebbe essere di inserire l'export all'interno di una cartella solamente con questo file all'interno. Ogni settimana cancelleresti il file precedente per aggiungere quello più recente, sfruttando la possibilità di pescare i dati da File > da Cartella all'interno di Power Query. Fra queste tre, sicuramente il parametro è quella più comprensibile per utenti meno esperti.

A questo punto, abbiamo i nostri dati di Google Search Console caricati all'interno di una tabella su Excel. Non ci resta che passare alla parte di analisi per creare la nostra dashboard.

Come analizzare i dati di Google Search Console su Excel

Vediamo adesso come analizzare i dati di Google Search Console su Excel, riassumendo la nostra vista sui dati con una sola formula un po' complessa ma efficace.

Disclaimer: questa formula funziona esclusivamente con Excel 365. Se non ce l'hai a disposizione, puoi sempre aprire questo file dalla versione online che è completamente gratuita.

Vediamola nel dettaglio:

=LET(text;B3;

text_search;

VAL.NUMERO(RICERCA(text;Queries[Top queries]));

lower_click;C3; upper_click;C5;

click;

SE(VAL.VUOTO(upper_click);Queries[Clicks]>=lower_click;

SE(VAL.VUOTO(lower_click);Queries[Clicks]<=upper_click;

(Queries[Clicks]>=lower_click)*(Queries[Clicks]<=upper_click)));

lower_impr;D3; upper_impr;D5;

impr;

SE(VAL.VUOTO(upper_impr);Queries[Impressions]>=lower_impr;

SE(VAL.VUOTO(lower_impr);Queries[Impressions]<=upper_impr;

(Queries[Impressions]>=lower_impr)*(Queries[Impressions]<=upper_impr)));

lower_ctr;E3; upper_ctr;E5;

ctr;

SE(VAL.VUOTO(upper_ctr);Queries[CTR]>=lower_ctr;

SE(VAL.VUOTO(lower_ctr);Queries[CTR]<=upper_ctr;

(Queries[CTR]>=lower_ctr)*(Queries[CTR]<=upper_ctr)));

lower_pos;F3; upper_pos;F5;

pos;

SE(VAL.VUOTO(upper_pos);Queries[Position]>=lower_pos;

SE(VAL.VUOTO(lower_pos);Queries[Position]<=upper_pos;

(Queries[Position]>=lower_pos)*(Queries[Position]<=upper_pos)));

total_queries;

FILTRO(Queries;text_search*click*impr*ctr*pos;"");

total_queries;

FILTRO(Queries;text_search*click*impr*ctr*pos;"");

sort_primary;

CONFRONTA(I7;B7:F7;0);

sort_order;

SE(sort_primary=5;1;-1);

total_queries_sorted;

SE.ERRORE(DATI.ORDINA(total_queries;sort_primary;sort_order);"Nessuna query corrisponde a questi parametri");

total_queries_sorted)

Se ti è venuto un mezzo infarto leggendo questa formula, non ti preoccupare perchè la rivedremo a breve, spezzettandola nelle singole parti per una maggiore comprensione.

Rivediamo solo un momento la struttura della nostra piccola dashboard:

• Step 1: qui inseriamo il testo che è inserito nella query di ricerca (che possiamo tenere vuoto), così come un valore inferiore e superiore per ciascuno dei parametri (Click, Impression, CTR e Position)
• Step 2: qui scegliamo l'ordine per restituire i risultati, dando priorità a una certa colonna anzichè un'altra
• Step 3: qui vediamo il numero di query che sono comprese nella nostra selezione

Torniamo alla nostra formula, vedendola passo passo.

Come verificare se una stringa di testo è inserita in una cella Excel

Cominciamo dal testo della nostra query inserita su Google Search Console. In questo caso possiamo utilizzare questa formula:

LET(text;B3;

text_search;

VAL.NUMERO(RICERCA(text;Queries[Top queries]));

Utilizziamo la funzione LET perchè ci permette di definire un certo parametro da utilizzare poi nella parte successiva della formula. In questo caso dichiariamo che text sarà uguale alla cella B3, mentre text_search verificherà se il nostro testo è incluso nelle query di ricerca che abbiamo inserito in precedenza.

Questo passaggio è utile per verificare corrispondenze parziali, dato che con VAL.NUMERO e RICERCA possiamo verificare se il valore è incluso in una certa cella, non solo una corrispondenza esatta. Questo piccolo trucco può tornare utile in numerose circostanze, dobbiamo solo ricordarci di inserire come primo parametro in RICERCA i valori che stiamo cercando e poi il "dove" li stiamo cercando. Excel converte il risultato in una serie di numeri, perchè RICERCA ci restituisce la posizione relativa all'interno della stringa di un carattere - ad esempio in "La business unit", se cerchiamo unit, ci ridarà il valore 13 perchè prima di unit ci sono 12 caratteri. VAL.NUMERO non fa altro che confermare che questo valore sia incluso nella cella di origine: se è presente, ci sarà un numero come il 13 che abbiamo descritto e avremo un risultato VERO. Se non esiste, vuol dire che non c'è alcun numero ottenuto da RICERCA, quindi sarà FALSO.

Come verificare le condizioni per filtrare su Excel

I dati di origine da Google Search Console sono tutti numerici, quindi il procedimento è uguale per click, impression, CTR e position. Vediamolo per i click:

lower_click;C3; upper_click;C5;

click;

SE(VAL.VUOTO(upper_click);Queries[Clicks]>=lower_click;

SE(VAL.VUOTO(lower_click);Queries[Clicks]<=upper_click;

(Queries[Clicks]>=lower_click)*(Queries[Clicks]<=upper_click)));

In questo caso stiamo dichiarando che c'è un intervallo dati, delimitato da C3 e C5, che ho chiamato lower_click e upper_click. Questo succede perchè questo è il proseguimento della parte precedente di funzione, quindi LET è ancora valida e ci permette di dichiarare questi due parametri. Viene sempre inserito prima il nome che vogliamo dare, poi il valore. Do per scontato che C3 sia inferiore a C5, dato che nel foglio c'è scritto "DA" e poi "A". La formula funziona comunque anche se inseriamo uno solo dei parametri, dato che sfrutteremo la combinazione di questi valori con il nuovo parametro chiamato click.

Click ha tre possibilità, indicate con la funzione SE:

• Caso #1: upper_click (C5) è vuota, come identificato da VAL.VUOTO. In questo caso, vogliamo solamente le query dove il numero di click è superiore o uguale a lower_click
• Caso #2: lower_click (C3) è vuota. In questo caso, vogliamo l'opposto del caso precedente: le query che hanno avuto un numero di click inferiore o pari a upper_click
• Caso #3: supponiamo che entrambi i valori siano inseriti dall'utente. In questo caso, stiamo prendendo esclusivamente le query che abbiano un numero di click almeno pari a lower_click e non superiori a upper_click. Il * inserito ci indica che entrambe queste condizioni devono essere vere

Come dicevamo, la procedura è esattamente la stessa anche per gli altri parametri, quindi possiamo evitare di analizzarla ulteriormente. Vedrai che cambiano solamente i numeri e le colonne che ci interessano della tabella di partenza, ma la logica è esattamente la stessa.

Come restituire i risultati di un filtro complesso su Excel

A questo punto, non ci resta che restituire tutti i valori che ci servono sul nostro Excel, sempre presi da Google Search Console. Vediamo come fare:

total_queries;

FILTRO(Queries;text_search*click*impr*ctr*pos;"");

Anche qui stiamo sempre dichiarando le nostre variabili, come nei casi precedenti. Total_queries sta restituendo i casi nei quali l'intera tabella Queries stia rispettando tutte le nostre condizioni precedenti, grazie alla funzione FILTRO. Se non ci sono risultati, restituirà il valore vuoto.

sort_primary;

CONFRONTA(I7;B7:F7;0);

I7 ha un menu a tendina che comprende le intestazioni delle colonne da Clicks a Position. Utilizzeremo questo valore per definire quale sarà la colonna che ci interessa per ordinare i risultati. CONFRONTA ci serve perchè ci restituisce la posizione relativa della selezione che faremo nella cella I7 - se selezioniamo Clicks, ci restituirà come risultato 2 perchè il valore si trova nella cella C7. La formula diventerebbe =CONFRONTA(C7;B7:F7;0), cioè dove si trova C7 nell'intervallo da B7 a F7, ovvero nella seconda posizione

sort_order;

SE(sort_primary=5;1;-1);

In questo caso stiamo decidendo se l'ordine debba essere crescente (1) o decrescente (-1). In linea di massima, ci interessa che sia decrescente perchè stiamo ragionando sul numero di click, impression o CTR dal valore più grande al più piccolo. La logica di fondo è che ci incuriosirà lavorare sulle pagine con il più alto numero di impression, per poi passare via via a quelle meno visitate o visualizzate. L'unico caso che ribalta questa logica è la posizione, dato che vorremo che sia crescente (1): se nel caso precedente inserissimo Position, il risultato sarebbe 5 perchè è l'ultimo valore del nostro intervallo. Solo in questo caso, desideriamo che sia crescente, mentre per tutti gli altri casi teniamo come buono il valore decrescente.

total_queries_sorted;

SE.ERRORE(DATI.ORDINA(total_queries;sort_primary;sort_order);"Nessuna query corrisponde a questi parametri");

total_queries_sorted)

Non ci resta che ordinare la nostra query, dato che ci interessa avere prima i risultati con il maggior numero di impression. In questo caso possiamo utilizzare la funzione DATI.ORDINA che prende total_queries, ordinandola poi per la colonna che abbiamo scelto in precedenza (sort_primary) nell'ordine collegato proprio a quest'ultima scelta (sort_order). Se non c'è un risultato valido, il file restituirà il testo "Nessuna query corrisponde a questi parametri". La prima volta stiamo dichiarando che cosa vogliamo calcolare, la seconda scriviamo nuovamente total_queries_sorted perchè è il valore che vogliamo ottenere come risultato della nostra formula.

Come migliorare la visualizzazione della dashboard su Excel

Per vedere il numero di query pertinenti della nostra ricerca usiamo questa formula:

=CONTA.NUMERI(B8#)/4

Dato che ogni riga con una query genera 4 numeri, ho pensato di contare i numeri totali con CONTA.NUMERI e poi dividere per 4. Non elegantissima, ma efficace. Vedrai anche che c'è un formato personalizzato:

[>1]#,### "Queries";[=1]"1 Query";;

Questo formato personalizzato inserisce il valore con Queries, se sono più di una - altrimenti, se è una sola, inserisce 1 Query. Se sono 0, non inserisce niente.

Qualche ritocco alla parte di formattazione condizionale può rendere la nostra dashboard più comprensibile. In particolare, ho impostato delle regole per modificare la visualizzazione della cella I7, quella utilizzata per l'ordine dei nostri risultati. Se il valore corrisponde a una delle intestazioni dei nostri risultati, ne copia esattamente il formato: quindi per Impressions sarà con testo bianco su sfondo blu, per Position sarà con testo bianco su sfondo arancione e così via. La barra dei dati da D8 a D1000 è invece utile per comprendere meglio la scala dei risultati che avremo per le Impressions, che sono considerate la metrica più importante in questo caso specifico.

Conclusione

Abbiamo finito la nostra dashboard che riassume i dati di Google Search Console su Excel! A questo punto non ci resta che:

• Decidere come caricare i dati su Power Query, se cambiando il nome del file o creando un parametro
• Aggiornare i dati inseriti
• Analizzare la nostra dashboard finale

Spero che la guida ti sia stata utile - io uso spesso questo file per rivedere i dati del mio sito velocemente, puoi scaricare da qui l'ultima versione e modificarla come preferisci per caricare i tuoi dati e rivedere le performance!