Torna all'elenco degli articoli Articoli
Tempo di lettura: 11 minuti

Analisi della crescita delle vendite con il Cheatsheet dell'analista di dati: Parte 2

Author's photo
Scott Davis

Indice

Benvenuti alla seconda parte del nostro viaggio nell'analisi dei dati di vendita. Utilizzando il Cheat Sheet SQL per l'analisi dei dati, approfondiremo le metriche chiave e applicheremo le query SQL per scoprire informazioni su consumatori e prodotti. Affiniamo le nostre abilità con l'SQL!

Sulla base del mio precedente articolo, Esplorazione del set di dati sulla crescita delle vendite - Utilizzo del foglio informativo per analisti di dati su dati di vendita reali, questo pezzo esplora altre metriche di vendita utilizzando lo stesso set di dati. Ci concentreremo su casi d'uso diversi che coinvolgono consumatori e prodotti, continuando a seguire le indicazioni del foglio informativo SQL for Data Analysis di LearnSQL.it.

In questo articolo ci soffermeremo su tre elementi chiave: gli argomenti fondamentali dell'SQL, l'identificazione dei modelli di metrica e l'indirizzamento alle sezioni pertinenti del foglio di istruzioni SQL per l'analisi dei dati per una rapida consultazione. Utilizzando un set di dati Tableau che presenta i dati di vendita di articoli per la casa, risponderemo a domande commerciali relative a clienti e prodotti.

L'obiettivo è quello di esercitarsi a riconoscere gli schemi di reporting e ad applicare le query SQL per risolvere queste domande. Anche se il set di dati può evolvere, l'attenzione deve essere rivolta al processo e ai passaggi logici nella creazione delle query.

Cosa imparerete:

  • Riconoscere gli schemi nelle query SQL, nell'analisi delle metriche e nei riferimenti ai fogli di calcolo.
  • Applicare questi schemi al vostro lavoro quotidiano di reporting e SQL.

Panoramica

Questo articolo è diviso in due sezioni: l'analisi dei clienti e l'analisi dei prodotti, che coprono KPI, tendenze e vari metodi di segmentazione.

Per rispondere alle domande, applico le tecniche SQL dell'articolo precedente, tra cui le aggregazioni, i rapporti e le espressioni di tabella comuni (CTE). L'analisi si concentra su metriche quali KPI, raggruppamento, monitoraggio delle tendenze, classifica e segmentazione. I riferimenti a SQL for Data Analysis Cheat Sheet includono GROUP BY, ranking, estrazioni di parti di data, rapporti e divisione di interi. In ogni esempio sono chiaramente indicate le tecniche SQL, i modelli metrici e i riferimenti al foglio informativo.

Gli esempi sono organizzati per difficoltà, a partire da query semplici fino a tecniche più avanzate, per facilitare l'applicazione di questi metodi ad altri insiemi di dati aziendali.

Come preparare i dati

Come nell'articolo precedente, utilizzo un dataset aperto di Tableau Public, che contiene dati di vendita di articoli per la casa come scrivanie e cancelleria. È ampiamente utilizzato nella reportistica aziendale ed è un'ottima risorsa per esercitarsi con le query SQL. Ecco i passaggi per preparare i dati.

Scaricare il set di dati da Tableau Public.

  • Andate su Impara → Dati di esempio → Business → Vendite del superstore.
  • Scaricate il dataset Superstore Sales.
Analisi della crescita delle vendite con il Cheatsheet dell'analista di dati: Parte 2
  1. I dati rilevanti si trovano nella scheda "Ordini".
  2. Aprire il file .xls
  3. Aprire la scheda "Ordini" dal file .xls ed esportarla in un file CSV.

Per un IDE SQL, consiglio di utilizzare MySQL Workbench e MySQL Community Server. Caricate il file CSV nel vostro database MySQL per iniziare a fare le query.

Come nell'articolo precedente, potete scaricare il codice utilizzato in questo articolo dal mio account Github.

Analizzare i clienti

Partiamo dal livello più semplice , il sottodominio dei clienti. Riassumeremo i diversi modi di analizzare i clienti per i casi d'uso finanziari, di ordine e di segmentazione.

Domanda 1: Quanti clienti ci sono per periodo di tempo?

SELECT
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_year,
    EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_month,
    COUNT(DISTINCT(Customer_ID)) AS active_customers
FROM `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2;

Risultato: La query calcola il totale dei clienti per periodo di tempo nell'intero set di dati. Fornisce una tendenza del numero di clienti che hanno effettuato acquisti per anno e mese. L'utente può vedere come è cambiata la base clienti nel corso della storia dell'azienda.

Per imparare a scrivere report SQL come questo, vi consiglio il corso Come creare semplici report SQL corso. Contiene quasi 100 esercizi che vi insegneranno a scrivere report complessi in SQL.

Domanda 2: Creare metriche finanziarie basate sui clienti per categoria e sottocategoria di prodotto per il ricavo medio per utente (ARPU) e il profitto medio per utente (APPU) in base alla località.

SELECT
      Region,
      State,
      City,
      ROUND(SUM(Sales) / COUNT(DISTINCT `Customer_ID`), 1) AS ARPU,
      ROUND(SUM(Profit) / COUNT(DISTINCT `Customer_ID`), 1) AS APPU
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Risultato: Questa query calcola l'ARPU e l'APPU per la combinazione di regioni, stati e città e classifica le città in base all'ARPU in ordine decrescente. I risultati mostrano le città che generano il maggior numero di ARPU e mostrano anche il loro APPU. Questa query può essere utilizzata per identificare le città ad alto valore che generano il maggior numero di entrate per cliente per gli usi strategici del marketing e/o della strategia di vendita.

Domanda 3: Qual è il numero medio di ordini per cliente? Qual è il valore medio dell'ordine (AOV) per cliente?

SELECT
    ROUND(SUM(Quantity) / COUNT(DISTINCT `Customer_ID`), 1) AS AQPU,
    ROUND(SUM(Sales)/COUNT(order_id), 2) as average_order_value
FROM `Tableau Superstore`.orders

Risultato: Questa query calcola due KPI relativi agli ordini (rispetto a quelli finanziari dell'ultima query): Average Quantity Per User (AQPU), che misura la quantità media di prodotti acquistati per cliente unico, e Average Order Value (AOV), che misura l'importo medio delle vendite per ordine. Gli stakeholder possono capire meglio il comportamento di acquisto dei clienti in base agli ordini.

Ecco un paio di domande difficili, entrambe relative a segmentazioni dei clienti di tipo classificato. La prima esamina i clienti attraverso 4 diverse metriche, mentre l'ultima combina combinazioni di metriche per trovare il giusto segmento.

Domanda 4: Quali sono i primi 3 conti/clienti per margine di profitto, profitto totale, vendite totali e frequenza di vendita?

WITH customer_metrics AS (
    SELECT 
         customer_name, 
         ROUND(SUM(Sales), 1) AS sales_total,
         ROUND(SUM(Profit), 1) AS profit_total,
         ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
         COUNT(order_id) AS sales_frequency
    FROM  `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        'Profit Margin' AS metric, 
        profit_margin AS amount,
        DENSE_RANK() OVER (ORDER BY profit_margin DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Profit' AS metric, 
        profit_total AS amount,
        DENSE_RANK() OVER (ORDER BY profit_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Sales' AS metric, 
        sales_total AS amount,
        DENSE_RANK() OVER (ORDER BY sales_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Sales Frequency' AS metric, 
        sales_frequency AS amount,
        DENSE_RANK() OVER (ORDER BY sales_frequency DESC) AS customer_rank
    FROM customer_metrics
)
SELECT 
    customer_name, 
    metric, 
    amount,
    customer_rank
FROM ranked_customers
WHERE customer_rank <= 3
ORDER BY 2, 4

Risultato: Si tratta di un report di tipo bottom-up che mostra diverse metriche (vendite totali, profitto totale, margine di profitto e frequenza delle vendite) a livello granulare, ovvero per singolo cliente. I risultati mostrano i primi 3 clienti in ciascuna delle 4 categorie. Questa query può essere utilizzata per dare priorità ai clienti di alto valore per strategie di marketing e di vendita mirate.

Disclaimer - Questo metodo funziona per segmentare i clienti, ma è necessario decidere quale metrica filtrare per prima (fatturato, profitto o margine). L'ordine influisce sui risultati e non è sempre ovvio. Più avanti vedremo una query che classifica i clienti utilizzando tutte le metriche insieme.

Per saperne di più sull'uso di WITH nelle query SQL, consultate il corso Query ricorsive.

Domanda 5: Identificare i clienti ad alta priorità in base a metriche come il profitto, il margine di profitto, la frequenza di acquisto e la quantità venduta. Segmentare i clienti in gruppi a bassa, media e alta priorità, dove l'alta rappresenta i clienti con il maggior profitto e la più alta frequenza di acquisto.

WITH customer_metrics AS (
    SELECT 
        customer_name, 
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
        COUNT(order_id) AS sales_frequency
    FROM `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        sales_total, 
        profit_total, 
        profit_margin,
        sales_frequency, 
        PERCENT_RANK() OVER (ORDER BY profit_margin DESC) AS profit_rank,
        PERCENT_RANK() OVER (ORDER BY sales_frequency DESC) AS frequency_rank
    FROM customer_metrics
),
segmented_customers AS (
 SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    CASE 
        WHEN profit_rank <= 0.1 AND frequency_rank <= 0.1 THEN 'High Profit & High Frequency'
        WHEN profit_rank <= 0.1 THEN 'High Profit'
        WHEN frequency_rank <= 0.1 THEN 'High Frequency'
        ELSE 'Low Profit & Low Frequency'
    END AS customer_segment
 FROM ranked_customers
)
SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    customer_segment
FROM segmented_customers
WHERE customer_segment = 'High Profit & High Frequency'
ORDER BY 3 DESC;

Risultato: Questa query segmenta i clienti in base al margine di profitto e alla frequenza delle vendite, identificando il 10% più alto in entrambe le categorie. I risultati mostrano che i clienti più preziosi sono "Alto profitto e alta frequenza". Questo è in contrasto con l'ultima query che aveva le 4 categorie separate ed era difficile combinare i risultati per mostrare i clienti di maggior valore. Questa query può essere utilizzata per definire le priorità dei clienti e quindi destinarli a campagne di marketing o di vendita.

Per conoscere le funzioni a finestra in SQL, vi consiglio il nostro corso interattivo Window Functions (Funzioni Finestra).

Analisi dei prodotti

Ecco altre domande di livello facile, questa volta incentrate sui prodotti. Le prime due domande sono lineari rispetto ad alcune domande dei clienti e richiedono un'analisi delle tendenze, ma l'ultima è un diverso tipo di problema di segmentazione.

Domanda 6: Quanti prodotti diversi vengono ordinati in ogni periodo di tempo?

SELECT 
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
    EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Month,
    COUNT(DISTINCT product_id) AS unique_items
FROM 
    `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2 DESC

Risultato: Questa query mostra una tendenza sul lato prodotto per gli articoli unici venduti. Questa metrica può essere utilizzata come controllo iniziale del tipo di inventario dei prodotti venduti.

Domanda 7: Creare un report delle vendite, dei profitti e del margine di profitto per ogni prodotto.

SELECT 
    product_id,
    category,
    sub_category,
    ROUND(SUM(sales), 1) AS sales_total,
    ROUND(SUM(profit), 1) AS profit_total,
    ROUND(SUM(profit)/SUM(sales), 1) as profit_margin
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC

Risultato:

Questa query calcola le vendite totali, il profitto totale e il margine di profitto per ogni prodotto e mostra l'ID del prodotto, la categoria e la sottocategoria per ciascuno di essi. I risultati mostrano le prestazioni a livello di prodotto, evidenziando i prodotti più venduti e i relativi margini di profitto. La query può essere utilizzata per trovare i prodotti più venduti e mostrarne la redditività; è un report generale che può essere utilizzato per la determinazione dei prezzi e l'inventario.

Questo è un altro tipo di domanda di livello Hard, che riguarda la segmentazione, ma è diversa dalle prime due che riguardano i clienti. Si tratta di segmentare un risultato raggruppato - una metrica per categoria.

Domanda 8: Quali sono i 3 articoli con il margine di profitto più basso per ogni sede, considerando le combinazioni articolo-sede. Se ci sono prodotti con legami, mostrateli.

WITH product_metrics AS (
    SELECT 
        product_id, 
        state,
        city,
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 2) as profit_margin
    FROM `Tableau Superstore`.orders
    GROUP BY 1, 2, 3
),
ranked_products AS (
    SELECT 
        product_id,
        state,
        city, 
        sales_total, 
        profit_total,
        profit_margin,
        DENSE_RANK() OVER (ORDER BY profit_margin ASC) AS profit_rank
    FROM product_metrics
   WHERE profit_margin IS NOT NULL
)
SELECT 
    product_id,
    state,
    city,
    sales_total, 
    profit_margin,
    profit_rank
FROM ranked_products
WHERE profit_rank <= 3
ORDER BY 5 ASC;

Risultato: Questa query ha calcolato le metriche di performance dei prodotti per combinazioni città-stato-prodotto_id e mostra i margini di profitto più bassi. I risultati mostrano le combinazioni meno redditizie e possono essere utilizzati per evidenziare le aree con prestazioni insufficienti, per migliorare o tagliare alcuni prodotti in quelle aree.

Conclusione

Gli 8 esempi di questo articolo proseguono la discussione del precedente sulla risposta alle domande di reporting in un contesto aziendale. Abbiamo iniziato con le metriche KPI di base, siamo passati all'analisi dei trend e abbiamo concluso con calcoli più avanzati di ranking e segmentazione.

Lavorando su queste query, avete visto gli schemi SQL comuni utilizzati nella reportistica aziendale, tra cui le aggregazioni, le funzioni finestra e le subquery. Sia che abbiate seguito passo dopo passo o che abbiate semplicemente rivisto gli esempi, queste tecniche possono essere un riferimento pratico per l'analisi dei dati di vendita in SQL.

Spero che questa analisi sia stata utile per chiarire come applicare le tecniche di Scheda dell'analista di dati nella pratica. Se qualcosa non è chiaro, se notate qualche errore o se avete domande sull'articolo o su qualche pezzo di codice, discutiamone e miglioriamo insieme! Sarei felice di collegarmi su LinkedIne potete trovare ulteriori esempi e approfondimenti sul mio profilo profilo Tableau Public o sul mio blog. Attendo con ansia le vostre riflessioni!

Tags:

Ti potrebbe interessare anche