Come costruire un cruscotto di dati (anche se avete appena iniziato a imparare SQL)

Immergetevi nella costruzione del vostro cruscotto di dati con questa guida per principianti, anche se siete alle prime armi con SQL. Utilizzando BigQuery, set di dati pubblici e Looker Studio, imparerete a creare facilmente una dashboard di grande effetto.

Se siete analisti di dati o volete diventarlo, imparare l'SQL è essenziale. SQL, o Structured Query Language, è il linguaggio utilizzato per estrarre e analizzare i dati dai database, aiutandovi a trasformare i numeri grezzi in vere e proprie intuizioni. Ma se siete alle prime armi, trovare idee per un progetto può essere la parte più difficile. È qui che entra in gioco questa guida. Costruirete un cruscotto di dati utilizzando gli strumenti gratuiti BigQuery e Looker Studio di Google che vi permetteranno di analizzare i dati reali sulla criminalità della città statunitense di Chicago.

Che siate alle prime armi con l'SQL o che vogliate affinare le vostre capacità, la guida SQL per l'analisi dei dati su LearnSQL.it è il punto di partenza ideale. Si tratta di un percorso di apprendimento completo pensato per i futuri analisti, che copre tutto, dalle query di base agli argomenti più avanzati come la creazione di report complessi e l'uso delle funzioni di finestra. Ognuno dei quattro corsi inclusi nel percorso è interattivo e pratico e consente di imparare facendo esempi reali che preparano al lavoro sui dati. Alla fine del corso, avrete la sicurezza di poter scrivere le vostre query, analizzare insiemi di dati reali e costruire progetti come il dashboard che creeremo in questa esercitazione.

Strumenti utilizzati per creare il dashboard

Per creare la nostra dashboard utilizzeremo due strumenti: BigQuery e Looker Studio.

BigQuery

BigQuery è un data warehouse offerto da Google. Un data warehouse è un tipo di database progettato per gestire grandi quantità di dati e rispondere a domande analitiche.

BigQuery è particolarmente utile per i principianti perché non è necessario installare nulla; basta aprire il browser e iniziare a scrivere SQL. È possibile utilizzare BigQuery gratuitamente mentre si impara.

Google offre un livello gratuito con un generoso limite mensile. Se si eseguono solo query semplici e si esplorano i dati, è molto improbabile che si superi tale limite. BigQuery consente anche di accedere a serie di dati pubblici gratuiti, tra cui dati reali su argomenti come la criminalità, la salute e i trasporti.

Looker Studio

Looker Studio è una piattaforma di Business Intelligence offerta da Google. Consente di trasformare i dati in cruscotti interattivi e condivisibili e in rapporti di facile lettura. È un ottimo strumento per i principianti perché è visivo, intuitivo e funziona nel browser. È possibile creare report dall'aspetto professionale con grafici e filtri drag-and-drop. Inoltre, è possibile aggiornare automaticamente il lavoro in base alle modifiche dei dati.

Esempio di dashboard in Looker Studio

Looker Studio è gratuito (con alcuni limiti). Funziona con un'ampia gamma di fonti di dati, tra cui Google Sheets, Google Analytics e (cosa più importante per noi) BigQuery.

In questo articolo, collegheremo Looker Studio a BigQuery per costruire una dashboard utilizzando uno dei dataset di BigQuery disponibili pubblicamente.

Il set di dati Chicago Crimes

Per questa esercitazione utilizzeremo il dataset Chicago Crimes, disponibile pubblicamente attraverso il programma gratuito di Google BigQuery per i dataset pubblici. È pubblicato dalla città di Chicago e si basa sulle informazioni raccolte dal Dipartimento di polizia di Chicago. Viene aggiornato settimanalmente con i dati della settimana precedente.

Il set di dati è ideale per fare pratica con l'SQL e per costruire il vostro primo cruscotto di dati. Include vari tipi di crimini che possono essere raggruppati e filtrati, oltre a campi di data e ora per provare query basate sul tempo. Poiché viene aggiornato regolarmente, si lavora con dati freschi e reali.

Questo set di dati contiene migliaia di righe, ognuna delle quali rappresenta un crimine segnalato nella città di Chicago. C'è solo una tabella nel dataset, chiamata bigquery-public-data.chicago_crime.crime. Alcune delle colonne più importanti della tabella sono:

• date - La data e l'ora in cui si è verificato il crimine (memorizzata come DATETIME).
• block - L'indirizzo approssimativo in cui è avvenuto l'incidente.
• primary_type - La categoria del crimine di alto livello (ad esempio THEFT, BATTERY, ROBBERY).
• description - Descrizione più specifica del reato (ad es. SIMPLE, STRONG ARM – NO WEAPON).
• location_description - Il tipo di luogo in cui si è verificato il reato (ad es. STREET, PARKING LOT, CHA HALLWAY).
• arrest - Un valore booleano (TRUE/FALSE) che indica se qualcuno è stato arrestato.
• domestic - Un valore booleano (TRUE/FALSE) che indica se si è trattato di un incidente domestico.
• beat, district, ward - Identificatori numerici per le zone di polizia, i distretti e le circoscrizioni cittadine.
• latitude, longitude - Le coordinate del luogo in cui è stato commesso il reato (possono essere utilizzate per la mappatura).
• year - L'anno in cui si è verificato il reato.

Domande da porre sul set di dati

Per costruire un dashboard significativo, è necessario iniziare con le domande giuste. È possibile formulare le proprie domande per costruire il dashboard. Condividerò con voi le domande e le query che ho utilizzato per costruire un semplice dashboard di dati per visualizzare i crimini in un determinato periodo di tempo.

Le domande che ho usato per guidare il mio dashboard sono state:

• Quanti crimini sono stati denunciati in un determinato periodo?
• Quanti arresti sono stati effettuati nel periodo considerato?
• Quali sono i 5 tipi di reato più comuni denunciati?
• Quali sono i primi 5 tipi di reato che hanno portato ad arresti?

Prima di iniziare a scrivere le query per rispondere a queste domande, esploriamo il dataset.

Esplorare un set di dati in BigQuery

Il primo passo per creare il dashboard è conoscere meglio il set di dati.

1. Aprire la console di BigQuery all'indirizzo console.cloud.google.com/bigquery.
2. Nel pannello di sinistra, cercare bigquery-public-data. Espandetela per trovare chicago_crime e fate clic sulla tabella crime per visualizzarne la struttura.
3. Fare clic sul pulsante query SQL in alto.
4. Nell'editor di query, è possibile iniziare a scrivere l'SQL:
   

   SELECT *
   FROM `bigquery-public-data.chicago_crime.crime`
   LIMIT 10;
   

   
   Questo restituirà le prime 10 righe del set di dati.

Da qui si può iniziare a esplorare i dati. Eseguire alcune query per familiarizzare con il set di dati. È possibile filtrare per data, raggruppare per tipo di reato o analizzare gli arresti. Queste sono le stesse tecniche che si usano in qualsiasi database basato su SQL.

Ricordarsi di racchiudere sempre il nome completo della tabella tra parentesi graffe: `bigquery-public-data.dataset_name.table_name`.

Creare le query per l'esecuzione del cruscotto

Ora scriviamo le query per il nostro dashboard. Per mantenere le cose semplici, mi concentrerò sui dati dell'anno 2024, ma è possibile modificare facilmente i filtri per coprire qualsiasi periodo di tempo.

Queste query sono solo un punto di partenza. Sentitevi liberi di modificarle o di utilizzare le vostre idee. I dashboard sono sempre costruiti passo dopo passo: si inizia con una versione di base, poi si migliora man mano.

Totale dei reati denunciati

La nostra prima query è:

SELECT COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024;

Questa query conta il numero totale di crimini registrati nel dataset Chicago Crime per l'anno 2024. Utilizziamo la funzione COUNT(*) per restituire il numero di righe e la colonna anno per filtrare i crimini commessi nell'anno 2024.

Totale arresti

SELECT COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
  AND arrest IS TRUE;

Questa query è simile alla precedente, tranne che per il fatto che stiamo filtrando (e contando solo) le righe in cui la colonna arresti è impostata su TRUE.

Top 5 Tipi di reato

SELECT
  primary_type,
  COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
GROUP BY primary_type
ORDER BY COUNT(*) DESC
LIMIT 5;

Questa query mostra i 5 tipi di reato più comuni nel 2024. Filtra i dati per quell'anno, raggruppa i record per tipo di reato (primary_type), conta quante volte ogni tipo si è verificato e poi li ordina dal più frequente al meno frequente. Infine, limita il risultato ai 5 principali.

I 5 principali tipi di arresto

SELECT
  primary_type,
  COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
AND arrest IS TRUE
GROUP BY primary_type
ORDER BY COUNT(*) DESC
LIMIT 5;

Questa query trova i primi 5 tipi di reato nel 2024 che più spesso hanno portato a un arresto. Filtra i dati per includere solo i crimini del 2024 in cui è stato effettuato un arresto, li raggruppa per primary_type, conta quanti arresti sono avvenuti per ogni tipo, ordina i risultati per numero in ordine decrescente e restituisce i 5 principali.

Collegare Looker Studio a BigQuery

Per costruire il nostro cruscotto utilizzeremo Looker Studio. Può collegarsi a molte fonti di dati, ma funziona particolarmente bene con quelle dell'ecosistema Google (come BigQuery).

Per mantenere le cose pulite ed efficienti, utilizzeremo l'opzione Query personalizzata. Questo ci permette di filtrare, organizzare e modellare i dati con SQL prima che raggiungano il dashboard.

Si inizia aprendo Looker Studio e creando un nuovo report vuoto. Nel pannello Aggiungi dati al report, scegliere BigQuery come origine dati. Quindi, selezionare Query personalizzata. Scegliere il progetto di fatturazione. (Questo è il progetto che verrà addebitato per l'utilizzo di BigQuery; se si utilizza BigQuery Sandbox, non verrà addebitato). Incollare la query SQL nel campo Inserisci query personalizzata e fare clic su Aggiungi.

È interessante notare che in questa fase non è possibile rinominare l'origine dati; Looker Studio la nominerà automaticamente. Dopo aver aggiunto l'origine dati al report, è possibile rinominarla andando su Risorse > Gestisci origini dati aggiunte, dove è possibile anche aggiornare la query o rinominare i campi.

È possibile aggiungere altre query SQL al dashboard, se necessario.

Creare una dashboard in Looker Studio

Successivamente, è necessario creare le visualizzazioni. Per prima cosa, fare clic sul pulsante Aggiungi un grafico. È possibile scegliere tra diversi tipi di grafico, come il grafico a barre o il grafico a linee.

Per impostare un grafico, è necessario selezionare l'origine dati giusta e definire le colonne da visualizzare. L'editor di grafici di Looker Studio ha due schede principali che aiutano a farlo. La scheda IMPOSTAZIONE consente di scegliere l'origine dei dati, le dimensioni e le metriche; è qui che si decide cosa verrà visualizzato nel grafico. La scheda STILE consente di personalizzare l'aspetto del grafico, ad esempio scegliendo caratteri, colori, bordi e titoli.

Ad esempio, quando si crea un grafico a barre, si usa la scheda IMPOSTAZIONE per mostrare i tipi di reato e i conteggi e la scheda STILE per regolare l'aspetto del grafico.

È possibile aggiungere altri grafici al dashboard, come le scorecard che visualizzano un singolo numero, i grafici a linee per mostrare le tendenze nel tempo o le mappe geografiche per visualizzare i dati in base alla località. Ogni grafico può essere stilizzato separatamente, dandovi il pieno controllo del layout e del design per adattarlo alle vostre preferenze o per evidenziare le informazioni chiave.

Ecco come appare il mio dashboard finale:

Approfondite le vostre competenze in SQL

Se vi è piaciuto costruire questo dashboard, non fermatevi qui! Provate a esplorare altri set di dati e a costruire i vostri dashboard. Più vi eserciterete, più diventerete bravi a porre le domande giuste e a trovare le risposte nei dati.

Volete portare le vostre competenze a un livello superiore? Ancora una volta, vi consiglio il nostro percorso SQL per l'analisi dei dati. Si tratta di un percorso di apprendimento pratico progettato per aiutarvi a superare le basi e iniziare a pensare come un analista di dati. Imparerete a scrivere query più avanzate, a lavorare con set di dati reali, a individuare tendenze, a confrontare periodi di tempo, a segmentare i dati e a prepararli per dashboard e report. Ogni corso include esercizi interattivi, feedback immediato e pratica di SQL. Con questo percorso interattivo, si impara facendo piuttosto che leggendo.

Alla fine del corso, sarete in grado di esplorare i set di dati con sicurezza e di creare dashboard che rispondano effettivamente a domande importanti. Scopritelo oggi stesso!