Volver a la lista de artículos Artículos
Lectura de 12 minutos

Análisis del crecimiento de las ventas con la hoja de trucos del analista de datos: Parte 2

Author's photo
Scott Davis

Índice

Bienvenido a la segunda parte de nuestro viaje por el análisis de datos de ventas. Utilizando la hoja de trucos de SQL para análisis de datos, profundizaremos en métricas clave y aplicaremos consultas SQL para descubrir información sobre consumidores y productos. Perfeccionemos nuestras habilidades SQL.

A partir de mi artículo anterior, Análisis de ventas reales con la hoja de trucos del analista, este artículo explora métricas de ventas adicionales utilizando el mismo conjunto de datos. Nos centraremos en diferentes casos de uso relacionados con consumidores y productos, y seguiremos las directrices de la hoja de consulta SQL for Data Analysis de LearnSQL.es.

En este artículo, nos centraremos en tres elementos clave: temas básicos de SQL, identificación de patrones de métricas y referencias rápidas a las secciones pertinentes de la hoja de consulta de SQL para análisis de datos. Utilizando un conjunto de datos de Tableau que muestra datos de ventas de artículos para el hogar, responderemos a preguntas empresariales relacionadas con clientes y productos.

El objetivo es practicar el reconocimiento de patrones de informes y la aplicación de consultas SQL para resolver estas preguntas. Aunque el conjunto de datos puede evolucionar, la atención se centrará en el proceso y en los pasos lógicos para elaborar consultas.

Lo que aprenderá

  • Reconocer patrones en consultas SQL, análisis de métricas y referencias de hojas de trucos.
  • Aplicar estos patrones a su trabajo diario de informes y SQL

Resumen

Este artículo se divide en dos secciones: análisis de clientes y análisis de productos, que abarcan KPI, tendencias y diversos métodos de segmentación.

Para responder a las preguntas, aplico técnicas SQL del artículo anterior, incluidas agregaciones, ratios y expresiones comunes de tabla (CTE). El análisis se centra en métricas como los KPI, la agrupación, el seguimiento de tendencias, la clasificación y la segmentación. Las referencias a SQL for Data Analysis Cheat Sheet incluyen GROUP BY, clasificación, extracciones de partes de fechas, ratios y división de enteros. En cada ejemplo, señalo claramente las técnicas SQL relevantes, los patrones métricos y las referencias a la hoja de trucos.

Los ejemplos están organizados por dificultad, empezando por consultas sencillas y avanzando hacia técnicas más avanzadas, lo que facilita la aplicación de estos métodos a otros conjuntos de datos empresariales.

Cómo preparar los datos

Como en el artículo anterior, utilizo un conjunto de datos abierto de Tableau Public, que contiene datos de ventas de artículos para el hogar, como escritorios y artículos de papelería. Se utiliza mucho en la elaboración de informes empresariales y es un gran recurso para practicar consultas SQL. Estos son los pasos para preparar los datos.

Descargue el conjunto de datos de Tableau Public.

  • Vaya a Learn → Sample Data → Business → Superstore Sales.
  • Descargue el conjunto de datos Superstore Sales.
Análisis del crecimiento de las ventas con la hoja de trucos del analista de datos: Parte 2
  1. Los datos relevantes se encuentran en la pestaña "Pedidos".
  2. Abra el archivo .xls
  3. Abra la pestaña "Pedidos" del archivo .xls y expórtela a un archivo CSV.

Para un IDE SQL, recomiendo utilizar MySQL Workbench y MySQL Community Server. Cargue el archivo CSV y en su base de datos MySQL para iniciar la consulta.

Como en el artículo anterior, puedes descargar el código utilizado en este artículo desde mi cuenta de Github.

Analizando Clientes

Vamos a empezar en el nivel fácil en el subdominio de clientes. Resumiremos diferentes formas de analizar clientes para casos de uso financieros, de pedidos y de segmentación.

Pregunta 1: ¿Cuántos clientes hay por periodo de tiempo?

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;

Resultado: La consulta calcula el total de clientes por periodo de tiempo en todo el conjunto de datos. Proporciona una tendencia de cuántos clientes han realizado compras por año y mes. Un usuario puede ver cómo ha cambiado la base de clientes a lo largo de la historia de la empresa.

Para aprender a escribir informes SQL como éste, recomiendo el curso Cómo crear informes básicos con SQL curso. Tiene casi 100 ejercicios que te enseñarán a escribir informes complejos en SQL.

Pregunta 2: Crear métricas financieras basadas en el cliente por categoría y subcategoría de producto para el ingreso medio por usuario (ARPU) y el beneficio medio por usuario (APPU) por ubicación

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;

Resultado: Esta consulta calcula el ARPU y el APPU para una combinación de región, estado y ciudad y clasifica las ciudades por el ARPU en orden descendente. Los resultados muestran las ciudades que generan más ARPU y también su APPU. Esta consulta puede utilizarse para identificar ciudades de alto valor que generen los mayores ingresos por cliente para casos de uso de estrategias de marketing y/o ventas.

Pregunta 3: ¿Cuál es el número medio de pedidos por cliente? ¿Cuál es el valor medio de los pedidos por 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

Resultado: Esta consulta calcula dos KPI relacionados con los pedidos (frente a los relacionados con las finanzas de la última consulta): Cantidad Media por Usuario (CMPU), que mide la cantidad media de productos comprados por cliente único, y Valor Medio de Pedido (VMP), que mide el importe medio de ventas por pedido. Un interesado puede comprender mejor el comportamiento de compra de los clientes a través de los pedidos.

A continuación se presentan un par de preguntas difíciles, ambas relacionadas con segmentaciones de clientes de estilo clasificado. La primera examina a los clientes a través de 4 métricas diferentes, mientras que la última combina combinaciones de métricas para encontrar el segmento adecuado.

Pregunta 4: ¿Cuáles son las 3 principales cuentas/clientes por margen de beneficio, beneficio total, ventas totales y frecuencia de ventas?

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

Resultados: Se trata más bien de un informe de tipo ascendente que muestra varias métricas (ventas totales, beneficio total, margen de beneficio y frecuencia de ventas) a un nivel granular: clientes individuales. Los resultados muestran los 3 principales clientes en cada una de las 4 categorías. Esta consulta puede utilizarse para priorizar clientes de alto valor para estrategias de marketing y ventas específicas.

Descargo de responsabilidad - Este método funciona para segmentar clientes, pero debe decidir qué métrica filtrar primero (ingresos, beneficios o margen). El orden afecta a los resultados y no siempre es obvio. Más adelante, veremos una consulta que clasifica a los clientes utilizando todas las métricas juntas.

Para obtener más información sobre el uso de WITH en las consultas SQL, consulte el curso Consultas recursivas y expresiones de tabla comunes.

Pregunta 5: Identifique los clientes de alta prioridad basándose en métricas como el beneficio, el margen de beneficio, la frecuencia de compra y la cantidad vendida. Segmente a los clientes en grupos de prioridad baja, media y alta, donde la prioridad alta representa a aquellos con mayores beneficios y mayor frecuencia de compra.

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;

Resultado: Esta consulta segmenta a los clientes en función del margen de beneficios y la frecuencia de ventas, identificando el 10% más alto en ambas categorías. Los resultados muestran que los clientes más valiosos son los de "Alto beneficio y alta frecuencia". Esto contrasta con la última consulta que tenía las 4 categorías separadas y era difícil combinar los resultados para mostrar los clientes más valiosos. Esta consulta se puede utilizar para priorizar clientes y luego dirigirlos a campañas de marketing o ventas.

Para aprender sobre las funciones de ventana en SQL, recomiendo nuestro curso interactivo Funciones de ventana.

Analizando Productos

Aquí hay más preguntas de nivel fácil, esta vez enfocadas en productos. Las dos primeras preguntas son lineales a algunas de las preguntas de los clientes y requieren análisis de tendencias, pero la última es un tipo diferente de problema de segmentación.

Pregunta 6: ¿Cuántos productos diferentes se piden en cada periodo de tiempo?

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

Resultado: Esta consulta muestra una tendencia en el lado del producto para los artículos únicos vendidos. Esta métrica puede utilizarse como una comprobación inicial del tipo de inventario de los productos que se venden.

Pregunta 7: Crear un informe de las ventas, beneficios y margen de beneficios de cada producto

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

Resultado:

Esta consulta calcula las ventas totales, el beneficio total y el margen de beneficio de cada producto y muestra el ID de producto, la categoría y la subcategoría de cada uno. Los resultados muestran el rendimiento a nivel de producto, destacando los productos más vendidos y los márgenes de beneficio de cada uno. La consulta se puede utilizar para encontrar los productos más vendidos y mostrar su rentabilidad - es un informe general que se puede utilizar para la fijación de precios y el inventario.

Este es otro tipo de pregunta de nivel Hard, se trata de segmentación, pero diferente de las dos primeras de clientes. Se trata de segmentar un resultado agrupado - una métrica por una categoría.

Pregunta 8: Cuáles son los 3 artículos con menor margen de beneficio para cada ubicación, considerando combinaciones de artículo-ubicación. Si hay productos con empates, muéstrelos también.

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;

Resultado: Esta consulta calcula las métricas de rendimiento de los productos por combinaciones de ciudad-estado-producto_id y muestra los márgenes de beneficio más bajos. Los resultados muestran las combinaciones menos rentables y pueden utilizarse para mostrar las áreas de bajo rendimiento, ya sea para mejorar o recortar determinados productos en esas áreas.

Recapitulación

Estos 8 ejemplos de este artículo continúan la discusión del anterior sobre cómo responder a las preguntas de los informes en un contexto empresarial. Comenzamos con métricas básicas de KPI, pasamos al análisis de tendencias y concluimos con cálculos más avanzados de clasificación y segmentación.

Al trabajar a través de estas consultas, usted ha visto patrones SQL comunes utilizados en la presentación de informes de negocios, incluyendo agregaciones, funciones de ventana y subconsultas. Tanto si ha seguido paso a paso como si simplemente ha revisado los ejemplos, estas técnicas pueden servirle de referencia práctica para analizar datos de ventas en SQL.

Espero que este análisis le haya ayudado a aclarar cómo aplicar la Hoja de trucos del analista de datos en la práctica. Si hay algo que no te ha quedado claro, si detectas algún error o si tienes alguna pregunta sobre el artículo o sobre algún fragmento de código, ¡discutimos y mejoramos juntos! Estaré encantado de conectar contigo en LinkedIny puede encontrar más ejemplos y opiniones en mi perfil público de perfil público de Tableau o en mi blog. Espero sus comentarios.

Tags:

También te pueden interesar