Progetto SQL per il portafoglio: Northwind Store

I progetti di portafoglio SQL sono una parte importante della formazione di un analista di dati. Come si inizia un progetto e come si utilizzano le informazioni che si scoprono? Per rispondere a questa domanda utilizzeremo il database del negozio Northwind.

Realizzare un progetto SQL solido è un elemento essenziale del processo di apprendimento di qualsiasi analista di dati. Un progetto SQL è un ottimo strumento per l'apprendimento, in quanto consente di utilizzare l'SQL su un set di dati reali. Questo è particolarmente importante se siete principianti e non avete la possibilità di lavorare con set di dati reali. Lavorando con più set di dati reali, si imparano i problemi che si possono incontrare nel mondo reale. Inoltre, la realizzazione di diversi progetti SQL per il vostro portfolio è sempre una bella cosa da vedere in un curriculum.

La domanda è: come si prepara un portfolio di progetti SQL? Una volta trovato un set di dati online gratuito che si desidera analizzare, cosa si fa dopo? Risponderemo a queste domande utilizzando il database del negozio Northwind.

Questo dataset è disponibile nel corso SQL Databases for Practice. Si tratta di uno dei sei dataset che includono i dati di un'università, del traffico di un blog, dei punteggi sportivi, di un negozio di musica e del Museum of Modern Art (MoMA). Questo corso fa parte della tracciaPratica su SQL , dove è possibile esercitarsi con aggregazioni, JOIN, subquery, CTE, CASE WHEN e altri importanti argomenti di SQL. Se avete bisogno di rinfrescare le vostre conoscenze su alcune di queste aree importanti per il reporting, provate il nostro corso. Come creare semplici report SQL corso.

Prendiamo ora il dataset del negozio Northwind e utilizziamolo per un progetto SQL per un portfolio di analisti di dati. Il progetto sarà realizzato in PostgreSQL, ma tutto ciò che faccio è trasferibile ad altri database con piccole modifiche alla sintassi.

Nel frattempo, consultate il nostro foglio informativo gratuito sull 'SQL per l'analisi dei dati.

Il set di dati del negozio Northwind

L'unica informazione che vi darò su questo database è che è composto da sei tabelle:

1. categories - Un elenco di categorie di prodotti.
2. channels - Un elenco di fonti attraverso le quali il negozio acquisisce clienti.
3. customers - Un elenco dei clienti del negozio.
4. order_items - Un elenco dei prodotti inclusi in ogni ordine.
5. orders - Un elenco degli ordini effettuati dai clienti.
6. products - Un elenco dei prodotti offerti dal negozio.

Il resto delle informazioni lo raccoglieremo con il nostro progetto. In effetti, questa ispezione del database dovrebbe essere la fase iniziale di ogni progetto, quella che precede l'analisi.

1. Ispezione del database

Questa fase della preparazione di un progetto SQL prevede la conoscenza dei dati, come i nomi delle tabelle e delle colonne, le chiavi primarie e quelle esterne, le relazioni tra le tabelle e i tipi di dati presenti in ogni tabella.

Ispezione dei nomi delle tabelle

Un modo primitivo per conoscere le tabelle del dataset è quello di trovarle nel browser del RDBMS in cui è stato importato il dataset, ad esempio PostgreSQL, SQL Server o MySQL.

In PostgreSQL, è possibile scrivere questa query per ottenere un elenco di tutte le tabelle di un database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

Cerca i dati nella cartella information_schemadove si trovano i metadati del database. Poiché voglio il nome di ogni tabella, uso la vista tables dopo aver fatto riferimento allo schema e inserisco table_name in SELECT.

La prima condizione in WHERE filtra gli schemi di sistema e lascia solo le tabelle definite dall'utente. La seconda condizione assicura che vengano elencate solo le tabelle di base, senza le viste e le altre tabelle.

Ecco l'elenco delle tabelle del database Northwind:

table_name
categories
channels
customers
order_items
orders
products

Ispezione delle informazioni sulle colonne

Ora vogliamo capire meglio i dettagli di ogni tabella. Conoscere le loro colonne è un buon inizio.

Possiamo nuovamente interrogare information_schema per ottenere informazioni importanti sulle colonne:

SELECT table_name,
       column_name,
       data_type,
       is_nullable,
       column_default
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_name, ordinal_position;

Possiamo trovare i nomi delle tabelle, i nomi delle colonne e il tipo di dati di ciascuna colonna elencando table_name, column_name e data_type nella colonna SELECT.

La colonna is_nullable ci informa se la colonna accetta i valori NULL.

table_name	column_name	data_type	is_nullable
categories	category_id	integer	NO
categories	category_name	character varying	NO
categories	description	text	YES
channels	id	integer	NO
channels	channel_name	character varying	NO
customers	customer_id	integer	NO
customers	email	character varying	NO
customers	full_name	character varying	NO
customers	address	character varying	YES
customers	city	character varying	YES
customers	region	character varying	YES
customers	postal_code	character varying	YES
customers	country	character varying	YES
customers	phone	character varying	YES
customers	registration_date	timestamp without time zone	NO
customers	channel_id	integer	NO
customers	first_order_id	integer	YES
customers	first_order_date	timestamp without time zone	YES
customers	last_order_id	integer	YES
customers	last_order_date	timestamp without time zone	YES
order_items	order_id	integer	NO
order_items	product_id	integer	NO
order_items	unit_price	numeric	NO
order_items	quantity	smallint	NO
order_items	discount	numeric	NO
orders	order_id	integer	NO
orders	customer_id	integer	NO
orders	order_date	timestamp without time zone	YES
orders	total_amount	numeric	NO
orders	ship_name	character varying	YES
orders	ship_address	character varying	YES
orders	ship_city	character varying	YES
orders	ship_region	character varying	YES
orders	ship_postalcode	character varying	YES
orders	ship_country	character varying	YES
orders	shipped_date	timestamp without time zone	YES
products	product_id	integer	NO
products	product_name	character varying	NO
products	category_id	integer	NO
products	unit_price	numeric	YES
products	discontinued	boolean	NO

In questo modo, abbiamo tutte le informazioni in un unico posto, il che rende più facile indagare.

Per prima cosa, possiamo capire quali dati mostra ogni tabella vedendo i nomi delle colonne. I tipi di dati sembrano tutti logici. Ad esempio, non ci sono ID definiti come character varying. Pertanto, in questa fase non sarà necessario convertire i dati in un formato adeguato.

Per quanto riguarda NULL, otteniamo alcune informazioni preziose sulle tabelle:

• La tabella categories permette che la descrizione della categoria sia NULL.
• La tabella channels non consente alcuna NULLs.
• La tabella customers consente NULLin molte colonne, compreso il Paese. Se si vuole creare una segmentazione geografica dei clienti, questi NULLpotrebbero creare problemi.
• L'output di cui sopra mostra che nessuna colonna della tabella order_items sono nullable.
• Per la tabella products è interessante notare che il prezzo unitario può essere NULL.
• Sembra un po' strano che la tabella orders abbia così tante colonne nullable. In pratica, è possibile avere un ID ordine e nessun'altra informazione sull'ordine. Dobbiamo tenerlo presente quando analizziamo questa tabella.

Trovare le chiavi primarie e straniere

La fase successiva dell'ispezione dei dati consiste nel capire come le tabelle lavorano insieme. Per farlo, dobbiamo prima trovare le chiavi primarie (PK) e le chiavi esterne (FK).

Le PK ci mostrano quali colonne vengono utilizzate da una tabella per identificare in modo univoco i dati. In questo modo, è possibile comprendere meglio la struttura della tabella.

Le FK ci mostrano quale colonna è collegata a una chiave primaria di un'altra tabella. Questa è la base per capire le relazioni tra le tabelle.

È possibile elencare sia le PK che le FK utilizzando la query seguente:

SELECT kcu.table_name,
       kcu.column_name,
       tc.constraint_type
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
ON kcu.constraint_name = tc.constraint_name
AND kcu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY kcu.table_name ASC, tc.constraint_type DESC;

Questa volta dobbiamo utilizzare due viste della tabella information_schemae cioè table_constraints e key_column_usage.

La vista table_constraints contiene informazioni sui vincoli di ogni tabella. La vista key_column_usage serve per ottenere informazioni sulle colonne con vincoli di chiave.

Queste due viste sono unite da due colonne comuni: constraint_name (il nome del vincolo) e constraint_schema (il nome dello schema contenente il vincolo).

Uso la clausola WHERE per produrre solo i vincoli di chiave primaria e di chiave esterna.

Come ultimo passo, ordino l'output in ordine alfabetico per il nome della tabella e poi in ordine alfabetico inverso per il tipo di vincolo.

Il codice produce questa tabella:

table_name	column_name	constraint_type
categories	category_id	PRIMARY KEY
channels	id	PRIMARY KEY
customers	customer_id	PRIMARY KEY
customers	last_order_id	FOREIGN KEY
customers	first_order_id	FOREIGN KEY
customers	channel_id	FOREIGN KEY
order_items	order_id	PRIMARY KEY
order_items	product_id	PRIMARY KEY
order_items	order_id	FOREIGN KEY
order_items	product_id	FOREIGN KEY
orders	order_id	PRIMARY KEY
orders	customer_id	FOREIGN KEY
products	product_id	PRIMARY KEY
products	category_id	FOREIGN KEY

Le tabelle categories e channels sono le uniche tabelle che hanno PK ma non FK.

Tutte le altre tabelle hanno una PK e almeno una FK. L'unica eccezione è la tabella order_itemsche ha il vincolo PK su due colonne: order_id e product_id. In altre parole, la tabella ha una chiave primaria composita, il che significa che l'identificatore unico dei dati è la combinazione unica di due colonne. In questo caso, si tratta di mostrare un solo ID prodotto per ogni singolo ordine.

Un'analisi più attenta rivela che le stesse colonne sono anche una chiave esterna per la stessa tabella. Ciò significa che anche le chiavi primarie sono estranee, poiché provengono da altre tabelle.

Capire le relazioni tra le tabelle

L'elenco delle PK e delle FK ci dà già un'idea delle relazioni tra le tabelle. Tuttavia, possiamo comprenderle meglio mostrando le tabelle e le colonne padre e figlio delle chiavi esterne.

Perché? Una chiave esterna è sempre la chiave primaria di un'altra tabella. L'elencazione di tutte le tabelle e le colonne padre e figlio rappresenta un miglioramento rispetto alla query precedente, in quanto è possibile vedere facilmente le colonne condivise tra le tabelle.

Queste informazioni sono utili per capire le relazioni tra le tabelle, per sapere quali colonne si possono usare per unire le tabelle e se queste possono essere unite direttamente o attraverso un'altra tabella.

Ecco il codice:

SELECT ccu.table_name AS parent_table,
       ccu.column_name AS parent_column,
       kcu.table_name AS child_table,
       kcu.column_name AS child_column      
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY kcu.table_name;

Nella query, utilizziamo le informazioni provenienti dalle tabelle table_constraints, key_column_usage, e constraint_column_usage delle viste.

La vista constraint_column_usage mostra le colonne utilizzate da un vincolo. La useremo per mostrare la tabella e la colonna padre dell'FK, cioè la tabella e la colonna in cui questo FK è effettivamente una PK.

I dati di key_column_usage mostreranno la tabella e la colonna di un FK.

parent_table	parent_column	child_table	child_column
orders	order_id	customers	last_order_id
orders	order_id	customers	first_order_id
channels	id	customers	channel_id
orders	order_id	order_items	order_id
products	product_id	order_items	product_id
customers	customer_id	orders	customer_id
categories	category_id	products	category_id

Dall'output precedente, si nota che la tabella orders è direttamente collegata alla tabella customers attraverso le colonne order_id (PK) e customer_id (FK). È collegata alla tabella order_items tramite order_id (PK).

La tabella channels è collegata direttamente solo alla tabella customers.

Abbiamo già stabilito che le chiavi primarie della tabella order_items sono anche chiavi esterne. Dalla tabella precedente, possiamo vedere che provengono dalle tabelle orders e products.

La tabella categories è collegata solo alla tabella products.

2. Esplorazione dei dati

Quando si inizia a lavorare con un nuovo database, il primo passo dovrebbe essere l'esplorazione dei dati per ottenere alcune informazioni di base su ogni tabella del database. È buona norma esaminare quanto segue:

• Contare le righe.
• Trovare i valori minimi e massimi (soprattutto per le date).
• Elencare categorie distinte.
• Conteggio dei valori distinti in ogni categoria.
• Conteggio delle righe per ogni valore della categoria.
• Somme e medie per valori numerici chiave.

Questo non è un elenco esaustivo e può cambiare a seconda della tabella che si sta esplorando. Tuttavia, è un minimo e dovrebbe funzionare per la maggior parte delle tabelle.

In generale, questa fase si riduce ad alcune operazioni fondamentali di aggregazione e raggruppamento dei dati. Vi mostrerò come funziona su una tabella del nostro database e poi potrete esplorare altre tabelle nello stesso modo.

Esplorazione della tabella degli ordini

Conteggio delle righe

Come previsto, qui utilizziamo la funzione di aggregazione COUNT():

SELECT COUNT(*) AS number_of_rows
FROM orders;

Io uso la funzione COUNT() con l'asterisco; questa conterà tutte le righe, compresi i NULL.

number_of_rows
11,618

La tabella orders ha 11.618 righe. In altre parole, ci sono 11.618 ordini.

Valori minimi e massimi

Vediamo quali colonne sono adatte a essere utilizzate con le funzioni MIN() e MAX(). Perfetto - possiamo trovare le date più vecchie e più recenti in cui un ordine è stato effettuato e spedito:

SELECT MIN(order_date) AS oldest_order_date, 
	 MAX(order_date) AS latest_order_date,
	 MIN(shipped_date) AS oldest_shipped_date,
	 MAX(shipped_date) AS latest_shipped_date
FROM orders;

Dall'output, possiamo vedere che il primo ordine risale a 2017-01-01 e l'ultimo a 2024-06-24. Per quanto riguarda le date di spedizione, la prima risale a 2017-01-01 e la più recente a 2024-06-23.

oldest_order_date	latest_order_date	oldest_shipped_date	latest_shipped_date
2017-01-01 0:00:00	2024-06-24 0:00:00	2017-01-06 0:00:00	2024-06-23 0:00:00

Elenco delle categorie

Per fare questo non abbiamo bisogno di funzioni aggregate:

SELECT DISTINCT ship_country
FROM orders
ORDER BY ship_country;

Nella query, usiamo la clausola DISTINCT per mostrare ogni paese una sola volta.

Ecco un elenco dei Paesi in questa tabella:

ship_country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland
Italy
Mexico
Norway
Poland
Portugal
Spain
Sweden
Switzerland
UK
USA
Venezuela

Sarà utile anche visualizzare un elenco di città:

SELECT DISTINCT ship_city
FROM orders
ORDER BY ship_city;

Poiché ci sono molte città, ecco un elenco parziale:

ship_city
Aachen
Abilene
Achille
Adelphi
Adrian
Akron
Albany
Alberton
Albuquerque
…
Yucca Valley

Conteggio dei valori distinti in ogni categoria

Vediamo ora il numero di Paesi in cui Northwind ha consegnato le spedizioni:

SELECT COUNT(DISTINCT ship_country) AS number_of_countries
FROM orders;

Utilizziamo nuovamente COUNT(), ma questa volta aggiungiamo DISTINCT e facciamo riferimento alla colonna che vogliamo contare.

Il risultato mostra che ci sono 21 paesi diversi in cui l'azienda effettua consegne:

number_of_countries
21

Possiamo fare lo stesso per le città:

SELECT COUNT(DISTINCT ship_city) AS number_of_cities
FROM orders;

Ci sono 494 città uniche:

number_of_cities
494

Anche se non le abbiamo elencate come categoria separata, sapere quanti clienti hanno effettuato ordini sarà utile:

SELECT COUNT(DISTINCT customer_id) AS number_of_customers_with_orders
FROM orders;

Utilizziamo COUNT() per contare gli ID dei clienti. Naturalmente, abbiamo bisogno di DISTINCT, poiché si può prevedere che alcuni clienti compaiano più volte, cioè ogni volta che effettuano un ordine.

Ci sono 761 clienti unici che hanno ordinato almeno una volta dall'azienda.

number_of_customers_with_orders
761

Conteggio delle righe per valore di categoria

A questo scopo, occorre elencare ogni valore di categoria (come abbiamo fatto in precedenza) e contare il numero di righe per ogni valore.

Ad esempio, si otterrà il numero di ordini per paese:

SELECT ship_country,
	 COUNT(*) AS number_of_orders
FROM orders
GROUP BY ship_country
ORDER BY number_of_orders DESC;

Seleziono la colonna ship_country e poi uso COUNT(*) per contare tutte le righe. Affinché il conteggio avvenga per paese, è necessario introdurre la clausola GROUP BY ship_country.

Si ottiene così il numero di ordini per paese. L'output mostra che gli Stati Uniti sono il mercato più grande, con 9.789 ordini:

ship_country	number_of_orders
USA	9,789
France	236
Brazil	233
Germany	230
UK	144
Spain	123
Mexico	118
Venezuela	103
Argentina	87
Canada	82
Italy	64
Austria	58
Portugal	58
Belgium	56
Denmark	56
Finland	47
Norway	30
Sweden	28
Poland	27
Ireland	26
Switzerland	23

Somma e media dei valori

Se i dati sono adeguati, è possibile trovare medie e totali di valori numerici. È possibile farlo per la colonna total_amount, che rappresenta il valore di un ordine:

SELECT SUM(total_amount) AS orders_total_value,
       AVG(total_amount) AS average_order_value
FROM orders;

Per mostrare questi due calcoli, utilizzate le funzioni aggregate SUM() e AVG().

Possiamo vedere che il fatturato totale del negozio è di poco superiore a 19 milioni. Il valore medio dell'ordine è di 1.636,15:

orders_total_value	average_order_value
19,008,819.69	1,636.15

Esplorazione dei dati bonus

Come ho già detto, le aggregazioni precedenti dovrebbero essere il minimo che si possa fare per ogni tabella del database.

Questa esplorazione dei dati va un po' oltre (ma non troppo) le semplici aggregazioni e GROUP BY. Sebbene questa sia ancora la base, è possibile utilizzare altri concetti, come filtrare i dati (utilizzando WHERE e/o HAVING), estrarre parti di data o l'ora, utilizzare CASE WHEN per etichettare i dati e così via.

Esaminiamo alcuni esempi.

Numero di prodotti

Possiamo utilizzare la funzione COUNT() per trovare il numero totale di prodotti venduti dal negozio.

SELECT COUNT(*) AS number_of_active_products
FROM products
WHERE discontinued IS FALSE; 

La clausola WHERE contiene una condizione per mostrare solo i prodotti che non sono fuori produzione, cioè quelli che il negozio vende attualmente.

Dalla sezione precedente, sappiamo che la colonna discontinued è di tipo booleano. Dobbiamo quindi utilizzare l'operatore IS FALSE per includere nel conteggio solo i prodotti non fuori produzione.

Il conteggio è 69:

number_of_active_products
69

Fatturato per anno

Un semplice report che mostra i ricavi per anno può essere creato utilizzando SUM():

SELECT EXTRACT(YEAR FROM order_date) AS revenue_year,
       SUM(total_amount) AS revenue  
FROM orders
GROUP BY revenue_year;

Utilizziamo la funzione EXTRACT() per ottenere solo gli anni a partire dalle date degli ordini. Quindi, sommiamo gli importi totali di tutti gli ordini e raggruppiamo per anno per mostrare i valori di ciascun anno separatamente.

Dai risultati si evince che l'anno di punta dell'azienda è stato il 2018. L'anno con il fatturato più basso è il 2024, ma questo potrebbe essere dovuto al fatto che l'anno non è ancora terminato (al momento dell'analisi).

Un'altra cosa interessante è che non ci sono dati sulle entrate per gli anni 2019-2022. Occorre verificare se i dati mancano per un motivo o se si tratta di un errore.

revenue_year	revenue
2017	3,088,759.84
2018	9,368,330.91
2023	4,646,048.11
2024	1,905,680.83

Segmentazione dei clienti per Paese

Disponiamo di dati sui Paesi dei clienti, quindi una panoramica del numero di clienti in ciascun Paese sarebbe informativa.

Inoltre, non ci interessano i clienti che non hanno effettuato un ordine, perché potrebbero gonfiare artificialmente il numero di clienti. No, vogliamo solo i clienti che ordinano da noi. Ecco la query:

SELECT country,
	 COUNT(*) AS number_of_customers
FROM customers
WHERE first_order_id IS NOT NULL
GROUP BY country
ORDER BY number_of_customers DESC;

Selezioniamo e raggruppiamo per paese e utilizziamo la funzione aggregata COUNT() per trovare il numero di clienti. Se i dati della colonna first_order non sono nulli, allora questo cliente ha effettuato almeno un ordine; questa è la condizione che dobbiamo utilizzare in WHERE.

I dati sono ordinati dal numero di clienti più alto a quello più basso.

L'output mostra che il mercato più grande di Northwind in termini di clienti è quello degli Stati Uniti. Si può anche concludere che si tratta del mercato più grande in termini di fatturato.

country	number_of_customers
USA	697
Germany	8
France	8
Brazil	8
UK	5
Venezuela	4
Spain	4
Mexico	4
Argentina	3
Canada	3
Belgium	2
Denmark	2
Portugal	2
Finland	2
Italy	2
Austria	2
Sweden	1
Poland	1
Ireland	1
Switzerland	1
Norway	1

3. Analisi avanzata dei dati e approfondimenti praticabili

Quanto fatto finora è un buon inizio. Tuttavia, l'analisi dei dati in un progetto SQL per un portafoglio non dovrebbe fermarsi alla semplice aggregazione ed esplorazione per ogni tabella.

Ora andremo oltre per scrivere query più complesse che ci permetteranno di agire e migliorare l'attività di Northwind. Ad esempio, potremmo voler vedere come cambiano le vendite a seconda del canale. Vediamo cosa otteniamo e poi decidiamo quali saranno i nostri prossimi passi.

Vendite per canale

Per ogni canale, vogliamo mostrare le vendite totali, il valore medio degli ordini, il numero totale di ordini e il numero di clienti unici. Vogliamo anche classificare i canali in base alle vendite totali. Ecco la query:

SELECT ch.channel_name,
       SUM(o.total_amount) AS total_sales,
       AVG(o.total_amount) AS average_order_value,
       COUNT(o.order_id) AS total_orders,
       COUNT(DISTINCT o.customer_id) AS unique_customers,
       RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS sales_rank
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name
ORDER BY sales_rank;

Questa query utilizza diverse aggregazioni: SUM() per calcolare le vendite totali, AVG() per il valore medio degli ordini e COUNT() per il numero totale di ordini e (con DISTINCT) per i clienti unici.

Quindi, si utilizza la funzione finestra RANK() per classificare i canali in base alle vendite totali. Poiché le funzioni finestra vengono eseguite prima dell'aggregazione, non è possibile utilizzare semplicemente la colonna total_sales nella funzione finestra. È invece necessario replicare l'intero calcolo, un'operazione semplice che si completa con il copia e incolla.

Utilizziamo i dati delle tabelle channels e orders. Tuttavia, non posso unire direttamente queste due tabelle perché non hanno una colonna condivisa. Dobbiamo invece unirle tramite la tabella customers.

Ecco il risultato:

channel_name	total_sales	average_order_value	total_orders	unique_customers	sales_rank
Organic Search	14,003,046.95	1,603.10	8,735	565	1
Direct	2,436,649.06	1,799.59	1,354	91	2
Referral	1,034,734.45	1,645.05	629	46	3
Social	837,378.77	1,824.35	459	32	4
Paid Search	483,824.24	1,645.66	294	19	5
Email	213,186.22	1,450.25	147	8	6

Vediamo che la maggior parte delle vendite di Northwind proviene dalla ricerca organica. Non ci sono contraddizioni nei dati: la classifica delle vendite totali riflette anche il numero di ordini e di clienti unici.

È interessante notare che il canale della ricerca organica non ha il valore medio dell'ordine più alto. Questo richiede un miglioramento!

Immaginiamo una strategia di incremento dei ricavi che si concentri sull'aumento del valore medio degli ordini di tutti i canali piuttosto che sulle nuove acquisizioni.

Probabilmente non potremo adottare un approccio uniforme basato solo sul canale di marketing. All'interno di ogni segmento, potrebbero esserci clienti con abitudini di spesa diametralmente opposte. Questo, possiamo supporre, vale soprattutto per la ricerca organica, che è un canale così vasto.

Pertanto, è necessario conoscere meglio la distribuzione del valore dell'ordine per ciascun canale.

Distribuzione del valore dell'ordine

Calcoliamo il valore medio dell'ordine, la mediana, il quartile superiore, il quartile inferiore e la deviazione standard per ciascun canale:

SELECT ch.channel_name,
	 AVG(total_amount) AS average_order_value,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_order_value,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS upper_quartile_order_value,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS lower_quartile_order_value,
       STDDEV(total_amount) AS order_value_stddev
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name;

Il valore medio dell'ordine è calcolato da AVG().

La mediana (o 50° percentile), il quartile superiore e il quartile inferiore sono calcolati utilizzando la funzione aggregata di set ordinati PERCENTILE_CONT(), con il percentile specificato tra parentesi.

Per il calcolo del percentile, i dati devono essere ordinati in ordine crescente; a tale scopo si utilizza la clausola WITHIN GROUP.

Dopo il calcolo del percentile, STDDEV() viene utilizzato per calcolare la deviazione standard.

Ecco l'output:

channel_name	average_order_value	median_order_value	upper_quartile_order_value	lower_quartile_order_value	order_value_stddev
Direct	1,799.59	1,005.90	2,166.80	517.40	2,323.63
Email	1,450.25	960.00	1,970.41	492.50	1,655.97
Organic Search	1,603.10	1,007.70	2,018.20	480.00	1,916.39
Paid Search	1,645.66	1,083.00	2,104.40	486.55	1,813.22
Referral	1,645.05	1,064.00	2,034.50	482.90	1,969.01
Social	1,824.35	1,122.80	2,123.20	559.90	2,319.10

Analizziamo le metriche del primo canale.

La deviazione standard del canale Direct è 2.323,63, superiore al valore medio. Ciò indica un'elevata variabilità, ossia la presenza di valori anomali o di un'ampia gamma di ordini.

I quartili inferiore e superiore mostrano che il 50% degli ordini è compreso tra 517,40 e 2.166,80. Tuttavia, un'elevata deviazione standard significa che molti ordini sono al di fuori di questo intervallo.

La mediana è significativamente più bassa della media, il che indica che la distribuzione è a destra, cioè con un basso numero di valori di ordine elevato che aumentano la media.

Possiamo analizzare ogni canale allo stesso modo.

Questo ci dà un'altra idea. Per creare strategie personalizzate per aumentare i ricavi, possiamo segmentare ulteriormente i clienti in base al quartile superiore e inferiore. Considereremo tutti i clienti al di sopra del quartile superiore come clienti che spendono molto, mentre quelli che spendono poco saranno i clienti al di sotto del quartile inferiore.

In questo modo, possiamo adattare la nostra strategia a ciascun canale e alle abitudini di spesa di ciascun gruppo.

Clienti di alto e basso valore per canale

Eseguirò questo calcolo per un solo canale di marketing. Potete farlo per tutti gli altri, perché il codice rimarrà lo stesso; cambierete solo il canale in WHERE.

I clienti nel quartile superiore sono clienti di alto valore e vogliamo che siano inseriti in un elenco separato:

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
           ch.channel_name
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN channels ch ON c.channel_id = ch.id
    WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

upper_quartile_value AS (
    SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_order_value) AS upper_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot
JOIN upper_quartile_value uqv 
ON cot.total_order_value >= uqv.upper_quartile
ORDER BY cot.total_order_value DESC;

La prima CTE calcola il valore totale dell'ordine di ciascun cliente utilizzando SUM() e unendo le tabelle customers e ordini. In WHERE, filtriamo tutti i canali tranne Direct.

La seconda CTE calcola il quartile superiore in modo analogo.

La terza SELECT quindi CROSS JOINs le due CTE per trovare tutti i clienti il cui valore dell'ordine è superiore al quartile superiore.

Ecco le prime dieci righe dell'output:

customer_id	full_name	total_order_value	channel_name
134	Barry Michael	79,371.50	Direct
152	Carolann Williams	64,365.21	Direct
7	Frédérique Citeaux	61,865.74	Direct
17	Sven Ottlieb	57,251.14	Direct
64	Sergio Gutiérrez	55,140.75	Direct
490	Alice Blevins	54,736.24	Direct
8	Martín Sommer	54,499.55	Direct
303	Gregory Mack	52,554.20	Direct
316	Jeff Heard	51,976.31	Direct
129	Stephan Bufford	50,868.70	Direct

In totale, l'output contiene 23 clienti. È possibile utilizzare queste informazioni per individuare questi clienti di alto valore. Ad esempio, possiamo creare programmi di fidelizzazione in cui questi clienti possono ottenere sconti speciali, servizi personalizzati, punti da riscattare per gli acquisti, programmi di affiliazione VIP e così via.

Allo stesso modo, possiamo elencare i clienti i cui ordini sono inferiori al quartile inferiore:

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
	       ch.channel_name
    FROM customers c
    JOIN orders o 
    ON c.customer_id = o.customer_id
    JOIN channels ch
    ON c.channel_id = ch.id
	WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

lower_quartile_value AS (
    SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_order_value) AS lower_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot, lower_quartile_value lqv
WHERE cot.total_order_value <= lqv.lower_quartile
ORDER BY cot.total_order_value ASC;

Questa query è quasi identica alla precedente, ma questa volta calcoliamo il quartile inferiore. Inoltre, cerchiamo i clienti con ordini inferiori a tale valore.

Ancora una volta, la query restituisce 191 clienti. Ecco le prime dieci righe:

customer_id	full_name	total_order_value	channel_name
939	Shannon Aguilar	98.40	Direct
997	Barbra Armstrong	251.50	Direct
687	Andrew Scott	452.90	Direct
787	Dennis Myer	912.00	Direct
917	Les Allen	991.40	Direct
921	Shelby Turgeon	1,162.25	Direct
560	Nancy Wiggins	1,425.80	Direct
678	Tracey Thomas	2,555.20	Direct
756	Dora Rowlands	2,713.50	Direct
715	George Scott	2,906.50	Direct

Questi clienti devono essere affrontati in modo diverso, perché devono essere motivati a spendere di più. Le strategie per aumentare le entrate da questi clienti possono includere pacchetti di prodotti personalizzati, spedizione gratuita al di sopra di un certo importo, aumento degli sconti per soglie d'ordine più elevate o offerta di un regalo gratuito al di sopra di un certo importo dell'ordine.

Ora fate la stessa analisi per tutti gli altri canali e pensate a quali tattiche potreste usare per aumentare il valore degli ordini da quel canale.

Pronti per il vostro progetto di portafoglio SQL?

In questo articolo abbiamo mostrato come preparare un progetto di portafoglio SQL utilizzando il database del negozio Northwind. Naturalmente, se siete abbastanza creativi, potete anche creare il vostro set di dati.

Completare un progetto SQL per il vostro portfolio è una parte importante della preparazione al processo di assunzione. I portfolio sono un'ottima vetrina delle vostre capacità pratiche di analisi dei dati in SQL. Il progetto di cui sopra è solo un esempio. Ci sono molte altre idee di analisi che potete provare nel nostro corso SQL Databases for Practice, che vi consigliamo vivamente!