Proyecto SQL para cartera: Tienda Northwind

Los proyectos SQL de cartera son una parte importante de la formación de un analista de datos. ¿Cómo se inicia un proyecto y a dónde se va con los conocimientos que se descubren? Utilizaremos la base de datos del almacén Northwind para responder a esta pregunta.

Realizar un proyecto SQL sólido es un elemento esencial del proceso de aprendizaje de cualquier analista de datos. Un proyecto SQL es una gran herramienta de aprendizaje, ya que te obliga a utilizar SQL en un conjunto de datos del mundo real. Esto es especialmente importante si eres un principiante que carece de oportunidades para trabajar con conjuntos de datos del mundo real. Al trabajar con varios conjuntos de datos del mundo real, aprenderás qué problemas puedes encontrarte en el mundo real. Además, hacer varios proyectos SQL para tu cartera siempre es algo agradable de ver en un currículum.

La pregunta es, ¿cómo se prepara una cartera de proyectos SQL? Una vez que encuentre un conjunto de datos en línea gratuito que desee analizar, ¿qué debe hacer a continuación? Responderemos a estas preguntas utilizando la base de datos del almacén Northwind.

Puede encontrar este conjunto de datos en el curso Bases de datos SQL para la práctica. Es uno de los seis conjuntos de datos que incluyen datos de una universidad, tráfico de blogs, resultados deportivos, una tienda de música y el Museo de Arte Moderno (MoMA). Este curso forma parte del cursoEjercicio de SQL , en el que podrás practicar agregación, JOINs, subconsultas, CTEs, CASE WHEN, y otros temas importantes de SQL. Si necesita refrescar sus conocimientos sobre algunas de esas áreas importantes para la elaboración de informes, pruebe nuestro Cómo crear informes básicos con SQL curso.

Tomemos ahora el conjunto de datos de la tienda Northwind y utilicémoslo para un proyecto SQL para un portafolio de analista de datos. Haré este proyecto en PostgreSQL, pero todo lo que hago es transferible a otras bases de datos con pequeños ajustes de sintaxis.

A medida que avanzamos, no dudes en consultar nuestra hoja de trucos gratuita de SQL para análisis de datos.

El conjunto de datos de Northwind Store

La única información que le daré sobre esta base de datos es que consta de seis tablas:

1. categories - Una lista de categorías de productos.
2. channels - Una lista de fuentes a través de las cuales la tienda adquiere clientes.
3. customers - Una lista de los clientes de la tienda.
4. order_items - Una lista de los productos incluidos en cada pedido.
5. orders - Una lista de los pedidos realizados por los clientes.
6. products - Una lista de los productos que ofrece la tienda.

Recopilaremos el resto de la información haciendo nuestro proyecto. De hecho, esta inspección de la base de datos debería ser la etapa inicial de todo proyecto, la que precede al análisis.

1. Inspección de la base de datos

Esta etapa de la preparación de un proyecto SQL implica conocer los datos, como los nombres de las tablas y las columnas, las claves primarias y foráneas, las relaciones entre las tablas y los tipos de datos de cada tabla.

Inspección de los Nombres de las Tablas

Una forma primitiva de conocer las tablas del conjunto de datos es buscarlas en el navegador del RDBMS donde importó el conjunto de datos, por ejemplo, PostgreSQL, SQL Server o MySQL.

En PostgreSQL, puede escribir esta consulta para obtener una lista de todas las tablas de una base de datos:

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;

Busca datos en information_schemadonde se encuentran los metadatos de la base de datos. Como quiero el nombre de cada tabla, uso la vista tables después de referenciar el esquema y pongo table_name en el SELECT.

La primera condición en WHERE filtra los esquemas del sistema y deja sólo las tablas definidas por el usuario. La segunda condición asegura que sólo se listan las tablas base, sin vistas ni otras tablas.

Esta es la lista de tablas de la base de datos Northwind:

table_name
categories
channels
customers
order_items
orders
products

Inspección de la información de las columnas

Ahora queremos entender mejor los detalles de cada tabla. Conocer sus columnas es un buen comienzo.

Podemos volver a consultar information_schema para obtener información importante sobre las columnas:

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;

Podemos encontrar los nombres de las tablas, los nombres de las columnas y el tipo de datos de cada columna listando table_name, column_name, y data_type en SELECT.

La columna is_nullable nos dará información sobre si la columna acepta valores de 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

De esta forma, tenemos toda la información en un solo lugar, lo que facilita la investigación.

En primer lugar, podemos entender qué datos muestra cada tabla viendo los nombres de las columnas. Todos los tipos de datos parecen lógicos. Por ejemplo, no hay ID definidos como character varying. Por lo tanto, no será necesario convertir los datos a un formato adecuado en esta fase.

En cuanto a NULLs, obtenemos información valiosa sobre las tablas:

• La tabla categories permite que la descripción de la categoría sea NULL.
• La tabla channels no permite NULLs en absoluto.
• La tabla customers permite NULLs en muchas columnas, incluido el país. Si queremos crear una segmentación geográfica de los clientes, estos NULLs podrían causarnos problemas.
• El resultado anterior muestra que ninguna columna de la tabla order_items es anulable.
• Para la tabla products es interesante observar que el precio unitario puede ser NULL.
• Parece un poco extraño que la tabla orders tenga tantas columnas anulables. Básicamente hace posible tener un ID de pedido y ninguna otra información sobre el pedido. Debemos tenerlo en cuenta al analizar esta tabla.

Encontrar claves primarias y foráneas

El siguiente paso de la inspección de datos es comprender cómo funcionan las tablas entre sí. Para ello, primero tenemos que encontrar sus claves primarias (PK) y sus claves externas (FK).

Las PK nos mostrarán qué columna(s) utiliza una tabla para identificar los datos de forma exclusiva. De este modo, comprenderás mejor la estructura de la tabla.

Las FK nos mostrarán qué columna está vinculada a una clave primaria de otra tabla. Esta es la base para entender las relaciones entre las tablas.

Puedes listar tanto las PKs como las FKs utilizando la consulta de abajo:

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;

Esta vez, necesitamos utilizar dos vistas de la tabla information_schemaa saber table_constraints y key_column_usage.

La vista table_constraints contiene información sobre las restricciones de cada tabla. La vista key_column_usage sirve para obtener información sobre las columnas con restricciones clave.

Estas dos vistas están unidas por dos columnas comunes: constraint_name (el nombre de la restricción) y constraint_schema (el nombre del esquema que contiene la restricción).

Utilizo la cláusula WHERE para mostrar sólo las restricciones de clave primaria y de clave externa.

Como paso final, ordeno la salida alfabéticamente por el nombre de la tabla y luego la invierto alfabéticamente por el tipo de restricción.

El código genera esta tabla:

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

Las tablas categories y channels son las únicas tablas que tienen PK pero no FK.

Todas las demás tablas tienen un PK y al menos un FK. La única excepción es la tabla order_itemsque tiene la restricción PK en dos columnas: order_id y product_id. En otras palabras, la tabla tiene una clave primaria compuesta, lo que significa que el identificador único de los datos es la combinación única de dos columnas. En este caso, se trata de mostrar un único identificador de producto por pedido individual.

Una mirada más atenta revela que las mismas columnas son también una clave ajena para la misma tabla. Esto significa que las claves primarias también son foráneas, ya que proceden de otras tablas.

Comprender las relaciones entre las tablas

El listado de PKs y FKs ya nos da una idea de las relaciones entre las tablas. Sin embargo, podemos entenderlas mejor mostrando las tablas y columnas padre e hija de las claves externas.

¿Por qué? Una clave externa es siempre la clave primaria de otra tabla. Listar todas las tablas y columnas padre e hijas es una mejora sobre la consulta anterior porque veremos fácilmente las columnas compartidas entre tablas.

Esta información es útil para comprender las relaciones entre las tablas, saber qué columnas se pueden utilizar para unir tablas y si se pueden unir directamente o a través de otra tabla.

He aquí el código:

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;

En la consulta, utilizamos la información de las tablas table_constraints, key_column_usagey constraint_column_usage vistas.

La vista constraint_column_usage muestra las columnas utilizadas por una restricción. La utilizaremos para mostrar la tabla y columna padre de la FK, es decir, la tabla y columna donde esta FK es en realidad un PK.

Los datos de key_column_usage mostrarán la tabla y la columna de una 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

De la salida anterior, vemos que la tabla orders está directamente conectada a la tabla customers a través de las columnas order_id (PK) y customer_id (FK). Está conectado a la tabla order_items a través de order_id (PK).

La tabla channels está conectada directamente sólo con la tabla customers.

Ya hemos establecido que las claves primarias de la tabla order_items son también claves externas. En la tabla anterior, podemos ver que proceden de las tablas orders y products.

La tabla categories sólo está conectada con la tabla products.

2. Exploración de datos

Cuando se empieza a trabajar con una nueva base de datos, el primer paso debería ser explorar los datos para obtener información básica sobre cada tabla de la base de datos. Es una buena práctica mirar lo siguiente:

• Contar filas.
• Encontrar valores mínimos y máximos (especialmente para fechas).
• Listar categorías distintas.
• Contar los valores distintos de cada categoría.
• Contar filas por valor de categoría.
• Sumas y promedios de valores numéricos clave.

Esta lista no es exhaustiva y puede variar en función de la tabla que esté explorando. Sin embargo, es un mínimo y debería funcionar en la mayoría de las tablas.

En general, este paso se reduce a la agregación y agrupación de datos fundamentales. Te mostraré cómo funciona esto en una tabla de nuestra base de datos, y luego puedes explorar otras tablas de la misma manera.

Exploración de la tabla de pedidos

Recuento de filas

Como era de esperar, aquí utilizamos la función de agregación COUNT():

SELECT COUNT(*) AS number_of_rows
FROM orders;

Yo uso la función COUNT() con un asterisco; esto contará todas las filas, incluyendo los NULLs.

number_of_rows
11,618

La tabla orders tiene 11.618 filas. En otras palabras, hay 11.618 pedidos.

Valores mínimos y máximos

Veamos qué columnas se pueden utilizar con las funciones MIN() y MAX(). Perfecto: podemos encontrar las fechas más antigua y más reciente en las que se realizó y envió un pedido:

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;

El primer pedido se realizó en 2017-01-01 y el último en 2024-06-24. En cuanto a las fechas de envío, la primera es 2017-01-01 y la más reciente 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

Listado de categorías

No necesitamos ninguna función de agregación para hacer esto:

SELECT DISTINCT ship_country
FROM orders
ORDER BY ship_country;

En la consulta, utilizamos la cláusula DISTINCT para mostrar cada país una sola vez.

Aquí tienes una lista de los países de esta tabla:

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

También será útil ver una lista de ciudades:

SELECT DISTINCT ship_city
FROM orders
ORDER BY ship_city;

Como hay muchas ciudades, aquí tienes una lista parcial:

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

Contar valores distintos en cada categoría

Veamos ahora el número de países a los que Northwind ha realizado envíos:

SELECT COUNT(DISTINCT ship_country) AS number_of_countries
FROM orders;

Volvemos a utilizar COUNT(), pero esta vez añadimos DISTINCT y hacemos referencia a la columna que queremos contar.

El resultado muestra que hay 21 países diferentes en los que la empresa realiza entregas:

number_of_countries
21

Podríamos hacer lo mismo con las ciudades:

SELECT COUNT(DISTINCT ship_city) AS number_of_cities
FROM orders;

Hay 494 ciudades únicas:

number_of_cities
494

Aunque no las hayamos incluido en una categoría aparte, será útil saber cuántos clientes han realizado pedidos:

SELECT COUNT(DISTINCT customer_id) AS number_of_customers_with_orders
FROM orders;

Usamos COUNT() para contar IDs de clientes. Por supuesto, necesitamos DISTINCT, ya que cabe esperar que algunos clientes aparezcan muchas veces, es decir, cada vez que hacen un pedido.

Hay 761 clientes únicos que hicieron un pedido a la empresa al menos una vez.

number_of_customers_with_orders
761

Contar filas por valor de categoría

Para esto, queremos listar cada valor de categoría (ya lo hicimos antes) y contar el número de filas para cada valor.

Por ejemplo, esto devolverá el número de pedidos por país:

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

Selecciono la columna ship_country y luego uso COUNT(*) para contar todas las filas. Para que el recuento sea por cada país, tengo que introducir la cláusula GROUP BY ship_country.

Lo que obtenemos con esto es el número de pedidos por país. El resultado muestra que EE.UU. es el mayor mercado, con 9.789 pedidos:

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

Suma y valores medios

Si sus datos son adecuados, debe encontrar promedios y totales de valores numéricos. Podemos hacerlo para la columna total_amount, que es el valor de un pedido:

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

Para mostrar estos dos cálculos, utilice las funciones de suma SUM() y AVG().

Podemos ver que los ingresos totales de la tienda son algo más de 19 millones. El valor medio de los pedidos es de 1.636,15:

orders_total_value	average_order_value
19,008,819.69	1,636.15

Exploración de datos adicionales

Como he mencionado, las agregaciones anteriores deberían ser lo mínimo que hagas para cada tabla de la base de datos.

Esta exploración de datos va un poco más allá (pero no demasiado) de las simples agregaciones y GROUP BY. Aunque ésta sigue siendo la base, también puedes emplear otros conceptos como filtrar datos (usando WHERE y/o HAVING), extraer partes de la fecha o la hora, usar CASE WHEN para etiquetar datos, etc.

Examinemos varios ejemplos.

Número de productos

Podemos emplear la función COUNT() para hallar el número total de productos que vende la tienda.

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

Hay una condición en la cláusula WHERE para mostrar sólo los productos que no están descatalogados, es decir, son los productos que la tienda vende actualmente.

Por la sección anterior, sabemos que la columna discontinued es de tipo booleano. Por lo tanto, tenemos que utilizar el operador IS FALSE para incluir en el recuento sólo los productos no descatalogados.

El recuento es 69:

number_of_active_products
69

Ingresos por año

Se puede crear un informe sencillo que muestre los ingresos por año utilizando SUM():

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

Utilizamos la función EXTRACT() para obtener sólo los años a partir de las fechas de pedido. A continuación, sumamos los importes totales de todos los pedidos y agrupamos por año para mostrar los valores de cada año por separado.

Podemos ver en los resultados que el año con mayores ingresos de la empresa fue 2018. El año con los ingresos más bajos es 2024, pero esto puede deberse a que el año no ha terminado (en el momento de hacer el análisis).

Otra cosa interesante es que no hay datos de ingresos para los años 2019-2022. Esto debería comprobarse para ver si los datos faltan por algún motivo o si se trata de un error.

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

Segmentación de clientes por país

Tenemos datos sobre los países de los clientes, por lo que una visión general del número de clientes en cada país sería informativa.

Además, no nos interesan los clientes que no han realizado ningún pedido, ya que esto puede inflar artificialmente el número de clientes. No, sólo queremos clientes que nos hagan pedidos. Esta es la consulta:

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;

Seleccionamos y agrupamos por país y utilizamos la función de agregado COUNT() para encontrar el número de clientes. Si los datos de la columna first_order no son nulos, entonces este cliente realizó al menos un pedido; esta es la condición que tenemos que utilizar en WHERE.

Los datos se ordenan de mayor a menor número de clientes.

El resultado muestra que el mayor mercado de Northwind en términos de clientes es Estados Unidos. También se puede concluir que es el mayor mercado por ingresos.

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. Análisis avanzado de datos e información práctica

Lo que hemos hecho hasta ahora es un buen comienzo. Sin embargo, el análisis de datos en un proyecto SQL para una cartera no debería detenerse en la simple agregación y exploración de cada tabla.

Ahora iremos más allá para escribir consultas más complejas que nos permitan tomar medidas y mejorar el negocio de Northwind. Por ejemplo, podemos querer ver cómo cambian las ventas en función del canal. Veamos qué obtenemos y decidamos cuáles serán nuestros próximos pasos.

Ventas por canal

Para cada canal, queremos mostrar las ventas totales, el valor medio de los pedidos, el número total de pedidos y el número de clientes únicos. También queremos clasificar los canales por ventas totales. Esta es la consulta:

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;

Esta consulta utiliza varias agregaciones: SUM() para calcular las ventas totales, AVG() para el valor medio de los pedidos y COUNT() para el número total de pedidos y (con DISTINCT) para los clientes únicos.

A continuación, utilizamos la función de ventana RANK() para clasificar los canales por ventas totales. Dado que las funciones de ventana se ejecutan antes de la agregación, no podemos utilizar simplemente la columna total_sales en la función de ventana. En su lugar, es necesario replicar todo el cálculo, una tarea sencilla que se completa copiando y pegando.

Utilizamos los datos de las tablas channels y orders. Sin embargo, no puedo unir directamente estas dos tablas, ya que no tienen una columna compartida. En su lugar, tenemos que unirlas a través de la tabla customers.

Este es el resultado:

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

Vemos que la mayoría de las ventas de Northwind proceden de la búsqueda orgánica. No hay contradicciones en los datos: la clasificación por ventas totales también refleja el número de pedidos y de clientes únicos.

Es interesante observar que el canal de búsqueda orgánica no tiene el valor medio de pedido más alto. Esto merece una mejora.

Imaginemos una estrategia de aumento de los ingresos que se centre en aumentar el valor medio de los pedidos de todos los canales en lugar de en nuevas adquisiciones.

Probablemente no podamos adoptar un enfoque uniforme basado únicamente en el canal de comercialización. Dentro de cada segmento, puede haber clientes con hábitos de gasto polarmente opuestos. Esto, podemos suponer, se aplica especialmente a la búsqueda orgánica, que es un canal tan enorme.

Por lo tanto, necesitamos saber más sobre la distribución del valor del pedido para cada canal.

Distribución del valor del pedido

Calculemos el valor medio de los pedidos, la mediana, el cuartil superior, el cuartil inferior y la desviación típica de cada canal:

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;

El valor medio de los pedidos se calcula mediante AVG().

La mediana (o percentil 50), el cuartil superior y el cuartil inferior se calculan utilizando la función agregada de conjunto ordenado PERCENTILE_CONT(), con el percentil especificado entre paréntesis.

Para el cálculo del percentil, los datos deben ordenarse de forma ascendente; para ello se utiliza la cláusula WITHIN GROUP.

Tras el cálculo del percentil, se utiliza STDDEV() para calcular la desviación típica.

Este es el resultado:

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

Analicemos las métricas del primer canal.

La desviación estándar del canal Directo es de 2.323,63, superior al valor medio o promedio. Esto indica una gran variabilidad, es decir, que probablemente haya valores atípicos o una amplia gama de valores de pedidos.

Los cuartiles inferior y superior muestran que el 50% de los pedidos se sitúan entre 517,40 y 2.166,80. Sin embargo, una desviación típica elevada significa que muchos pedidos están fuera de este intervalo.

La mediana es significativamente inferior a la media, lo que indica que la distribución es sesgada a la derecha, es decir, un número bajo de valores de pedido altos que aumentan la media.

Podemos analizar cada canal de la misma manera.

Esto nos da otra idea. Para crear estrategias personalizadas de aumento de ingresos, podemos segmentar aún más los clientes por el cuartil superior e inferior. Consideraremos a todos los clientes por encima del cuartil superior como los que más gastan; los que menos gastan serán los clientes por debajo del cuartil inferior.

De este modo, podemos adaptar nuestra estrategia a cada canal y a los hábitos de gasto de cada grupo.

Clientes de alto y bajo valor por canal

Haré este cálculo para un solo canal de comercialización. Puede hacerlo para todos los demás, ya que el código seguirá siendo el mismo; sólo cambiará el canal en WHERE.

Los clientes en el cuartil superior son clientes de alto valor, y los queremos en una lista separada:

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;

El primer CTE calcula el valor total del pedido de cada cliente utilizando SUM() y uniendo las tablas customers y pedidos. En WHERE, filtramos todos los canales excepto Directo.

El segundo CTE calcula el cuartil superior de manera familiar.

A continuación, el tercer SELECT CROSS JOINs los dos CTE para encontrar todos los clientes cuyo valor de pedido está por encima del cuartil superior.

Aquí están las diez primeras filas de la salida:

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

En total, hay 23 clientes en la salida. Podemos utilizar esta información para dirigirnos a estos clientes de alto valor. Por ejemplo, podemos crear programas de fidelización en los que estos clientes puedan obtener descuentos especiales, un servicio personalizado, puntos canjeables por compras, programas de afiliación VIP, etcétera.

Del mismo modo, podemos hacer una lista de los clientes cuyos pedidos están por debajo del cuartil inferior:

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;

Esta consulta es casi igual que la anterior, salvo que esta vez calculamos el cuartil inferior. Además, buscamos clientes con pedidos por debajo de ese cuartil.

Una vez más, la consulta devuelve 191 clientes. Aquí están las diez primeras filas:

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

A estos clientes hay que dirigirse de forma diferente, ya que hay que motivarlos para que gasten más. Las estrategias para aumentar los ingresos procedentes de ellos pueden incluir paquetes de productos personalizados, envío gratuito a partir de un determinado importe, aumentar los descuentos para umbrales de pedido superiores u ofrecer un regalo gratuito a partir de un determinado importe de pedido.

Ahora, haga el mismo análisis para todos los demás canales y piense qué tácticas podría utilizar para aumentar los valores de los pedidos procedentes de ese canal.

¿Listo para su propio proyecto de cartera SQL?

En este artículo, mostramos cómo preparar un proyecto de cartera SQL utilizando la base de datos de la tienda Northwind. Por supuesto, si se siente lo suficientemente creativo, también puede crear su propio conjunto de datos.

Completar un proyecto SQL para tu portafolio es una parte importante de la preparación para el proceso de contratación. Los portafolios son un gran escaparate de tus habilidades prácticas de análisis de datos en SQL. El proyecto anterior es sólo un ejemplo. Hay muchas más ideas de análisis que puedes probar en nuestro curso Bases de Datos SQL para la Práctica, ¡que recomendamos encarecidamente!