INSTACART ORDERS SQL

Author

Ing. Gabriel Chávez Camargo

Published

October 23, 2024

Objetivo

Instacart es una plataforma de entregas de comestibles donde la clientela puede registrar un pedido y hacer que se lo entreguen, similar a Uber Eats y Door Dash.

Tu misión es limpiar los datos y preparar un informe que brinde información sobre los hábitos de compra de los clientes de Instacart.

Contenido de los datos

Para cada hora del día, ¿cuántas personas hacen órdenes?
¿Qué día de la semana compran víveres las personas?
¿Cuánto tiempo esperan las personas hasta hacer otro pedido? Comenta sobre los valores mínimos y máximos.
Diferencia entre miércoles y sábados para 'order_hour_of_day'. Traza gráficos de barra para los dos días y describe las diferencias que veas.
¿Cuál es la distribución para el número de pedidos por cliente?
¿Cuáles son los 20 productos más populares (muestra su ID y nombre)?
¿Cuántos artículos compran normalmente las personas en un pedido? ¿Cómo es la distribución?
¿Cuáles son los 20 principales artículos que vuelven a pedirse con mayor frecuencia (muestra sus nombres e IDs de los productos)?
Para cada producto, ¿cuál es la proporción de las veces que se pide y que se vuelve a pedir?
Para cada cliente, ¿qué proporción de sus productos ya los había pedido?
¿Cuáles son los 20 principales artículos que las personas ponen primero en sus carritos?

Hay cinco tablas en el conjunto de datos, y tendrás que usarlas todas para hacer el preprocesamiento de datos y el análisis exploratorio de datos. A continuación se muestra un diccionario de datos que enumera las columnas de cada tabla y describe los datos que contienen.

Code

with open('./contenido.txt', 'r', encoding='utf-8') as file:
    contenido = file.read()
print(contenido)

- instacart_orders: cada fila corresponde a un pedido en la aplicación Instacart.

  - 'order_id': número de ID que identifica de manera única cada pedido.

  - 'user_id': número de ID que identifica de manera única la cuenta de cada cliente.

  - 'order_number': el número de veces que este cliente ha hecho un pedido.

  - 'order_dow': día de la semana en que se hizo un pedido (0 si es domingo).

  - 'order_hour_of_day': hora del día en que se hizo el pedido.

  - 'days_since_prior_order': número de días transcurridos desde que este cliente hizo su pedido anterior.

- products: cada fila corresponde a un producto único que pueden comprar los clientes.

  - 'product_id': número ID que identifica de manera única cada producto.

  - 'product_name': nombre del producto.

  - 'aisle_id': número ID que identifica de manera única cada categoría de pasillo de víveres.

  - 'department_id': número ID que identifica de manera única cada departamento de víveres.

- order_products: cada fila corresponde a un artículo pedido en un pedido.

  - 'order_id': número de ID que identifica de manera única cada pedido.

  - 'product_id': número ID que identifica de manera única cada producto.

  - 'add_to_cart_order': el orden secuencial en el que se añadió cada artículo en el carrito.

  - 'reordered': 0 si el cliente nunca ha pedido este producto antes, 1 si lo ha pedido.

- aisles

  - 'aisle_id': número ID que identifica de manera única cada categoría de pasillo de víveres.

  - 'aisle': nombre del pasillo.

- departments

  - 'department_id': número ID que identifica de manera única cada departamento de víveres.

  - 'department': nombre del departamento.

Carga de librerías

Code

import sys
import os
import sqlite3
import pandas as pd 
import matplotlib.pyplot as plt 
import seaborn as sns

Conexión a la Base de Datos

Code

conn=sqlite3.connect('base_datos_instacart.db')

Insights

1. Para cada hora del día, ¿cuántas personas hacen órdenes?

Code

query='''
SELECT count(order_number) as count
FROM instacart_orders
GROUP BY order_hour_of_day
'''

consulta_1=pd.read_sql_query(query,conn)

#print(ejemplo)

consulta_1.plot(title="Día de la semana la gente hace sus compras",
                        kind="bar",
                        xlabel="Hora del pedido",
                         ylabel="No.Personas que hacen pedido",
                         legend=False)
plt.show()

Los pedidos comienzan alrededor de las 7:00 a.m., con un aumento significativo a partir de las 10:00 a.m. hasta las 4:00 p.m., alcanzando un pico cercano a los 4,000 pedidos por hora.

2. ¿Qué día de la semana compran víveres las personas?

Code

consulta_2='''
SELECT COUNT(order_number), order_dow
FROM instacart_orders
GROUP BY order_dow


'''

query_2=pd.read_sql_query(consulta_2,conn)

#print(query_2)

# Define los nombres de los días de la semana
dias_semana = ['Domingo', 'Lunes', 'Martes', 'Miércoles', 'Jueves', 'Viernes', 'Sábado']

# Grafica tu consulta
ax = query_2.plot(title="Día de la semana en que la gente hace sus compras",
                  kind="bar",
                  xlabel="Día de la semana",
                  ylabel="Número de personas que hacen pedidos",
                  legend=False,
                  figsize=(10, 6))

# Asigna las etiquetas personalizadas para el eje X
ax.set_xticklabels(dias_semana, rotation=45)

# Muestra el gráfico
plt.show()

“Consideramos que los usuarios realizan un mayor número de pedidos los días domingo y lunes, alcanzando un total aproximado de 80,000 pedidos en general.

3. ¿Cuánto tiempo esperan las personas hasta hacer otro pedido? Comenta sobre los valores mínimos y máximos.

Code

    consulta_3 = '''
    SELECT days_since_prior_order, COUNT(*) AS recuento_pedidos
    FROM instacart_orders
    WHERE days_since_prior_order BETWEEN 1 AND 30
    GROUP BY days_since_prior_order
    ORDER BY days_since_prior_order ASC
    '''

    consulta_3 = pd.read_sql_query(consulta_3, conn)

    #print(consulta_3)

    consulta_3.plot(title="Días desde la orden anterior",
                kind="bar",
                xlabel="Día del mes",
                ylabel="No. de pedidos",
                legend=False)

    plt.xticks(ticks=range(0, 30), labels=range(1, 31))
    plt.show()

De acuerdo con los datos, observamos que los clientes tienden a realizar un segundo pedido después de 7 días o 30 días, siendo estos los picos más altos en la recurrencia de compras. Este patrón sugiere que los clientes podrían estar planificando sus adquisiciones de manera semanal o mensual. Sería recomendable analizar el tipo de productos solicitados en función de estos intervalos.

4. Diferencia entre miércoles y sábados para `'order_hour_of_day'`.

Code

query4 = '''
    SELECT order_dow, order_hour_of_day, COUNT(order_id) AS total_pedidos
    FROM instacart_orders
    WHERE order_dow = 3 or order_dow = 6
    GROUP BY order_dow, order_hour_of_day
    ORDER BY order_dow, order_hour_of_day;
    '''
graf4 = pd.read_sql_query(query4, conn)
#print(graf4)

# Acomodar los datos para una mejor gráfica
graf4_pivot = graf4.pivot(
    index='order_hour_of_day', columns='order_dow', values='total_pedidos')

ax = graf4_pivot.plot(kind='bar',
                      figsize=(10, 6),
                      title="Comparación de pedidos entre Miércoles y Sábados por hora del día",
                      xlabel="Hora del día",
                      ylabel="Número de pedidos",
                      legend=False)

ax.legend(['Miércoles', 'Sábado'])
plt.show()

La mayoría de las órdenes por cliente se concentra entre las primeras 5 a 10 compras, mostrando una disminución gradual a medida que aumenta el número de órdenes. Esta tendencia puede deberse a diversas razones que no se pueden identificar claramente en este gráfico. Sería útil analizar el tiempo que el cliente lleva registrado en la plataforma y cuántas órdenes ha realizado durante ese período para obtener una mejor comprensión del comportamiento.

5. ¿Cuál es la distribución para el número de pedidos por cliente?

Code

consulta_5 ='''
SELECT total_pedidos, COUNT(*) as Total_de_Clientes
FROM (
SELECT user_id, COUNT(*) as total_pedidos
FROM instacart_orders
GROUP BY user_id
) AS subconsulta
GROUP BY total_pedidos

'''
consulta_5=pd.read_sql_query(consulta_5,conn)
#print(consulta_5)

consulta_5.plot(title="Distribución para el número de pedidos por total de clientes",
                        x='total_pedidos',
                        y='Total_de_Clientes',
                        kind="bar",
                        xlabel="Nº de Pedidos",
                         ylabel="Nº.Personas que hacen pedido",
                         legend=False,
                         grid=True)
plt.show()

Sabemos que los clientes realizan un solo pedido cuando el monto supera los 5,000, mientras que efectúan menos de 7 pedidos cuando los montos son menores.

6. ¿Cuáles son los 20 productos más populares (muestra su ID y nombre)?

Code

query_6 ='''
SELECT p.product_id, p.product_name, COUNT(o.product_id) AS count_products
FROM order_products o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY count_products DESC
LIMIT 20
'''
graf_6 = pd.read_sql_query(query_6, conn)
#print(graf_6)

#Graficando el resultado de la consulta
plt.figure(figsize=(10, 6))

# Crear la gráfica de barras con Matplotlib
plt.barh(graf_6['product_name'], graf_6['count_products'], color='skyblue')

# Añadir títulos y etiquetas
plt.title('Top 20 productos más populares', fontsize=16)
plt.xlabel('Número de productos vendidos')
plt.ylabel('Nombre del producto')

# Invertir el orden del eje y para que el producto más vendido esté arriba
plt.gca().invert_yaxis()

# Mostrar la gráfica
plt.tight_layout()
plt.show()

Las bananas destacan como el artículo más popular, liderando entre los 20 productos más demandados con más de 60,000 pedidos.

7. ¿Cuántos artículos compran normalmente las personas en un pedido? ¿Cómo es la distribución?

Code

   consulta_7 = '''
    SELECT num_articulos, COUNT(*) AS num_pedidos
    FROM (
        SELECT order_id, COUNT(product_id) AS num_articulos
        FROM order_products
        GROUP BY order_id
    ) AS subquery
    GROUP BY num_articulos
    ORDER BY num_articulos
    '''
consulta_7 =pd.read_sql_query(consulta_7,conn)
    #print(graf_7)

    # Conocer el limite del 3er cuartil para delimitar el siguien gráfico
sns.boxplot(x=consulta_7['num_articulos'])

plt.title('Distribución del número de artículos por pedido')
plt.xlabel('Número de artículos por pedido')

plt.show()

# Se realiza el gráfico hasta el número aproximado del 3er cuartil, el 75% de los datos
graf_7_filtrado = consulta_7[consulta_7['num_articulos'] <= 70]

# Crear el gráfico de barras
plt.figure(figsize=(10, 6))
plt.bar(graf_7_filtrado['num_articulos'],
        graf_7_filtrado['num_pedidos'])
plt.title('Distribución del número de artículos por pedido (hasta 70)')
plt.xlabel('Número de artículos por pedido')
plt.ylabel('Número de pedidos')
plt.xticks(rotation=90)
plt.grid(True)
plt.show()

Se decide usar un gráfico de bigotes para poder delimitar el número de artículos en cada pedido, considerando el límite del 3er cuartil. Esto nos lleva a delimitar nuestro gráfico hasta 70 artículos por pedido. En el segundo gráfico, se observa que el punto máximo del gráfico se localiza en el número 5, visualmente podemos determinar que la mayoria de los pedidos serán solicitados por 1 hasta 10 pedidos, donde comienza un decremento gradual llegando a sus mínimos al rededor de 49 artículos.

8. ¿Cuáles son los 20 principales artículos que vuelven a pedirse con mayor frecuencia (muestra sus nombres e IDs de los productos)?

Code

consulta_8='''
select product_name, count(o.product_id) as count_products

FROM [order_products] o 

JOIN [products] p ON o.product_id=p.product_id

Group by o.product_id

Order by count_products DESC

LIMIT 20

'''

consulta_8=pd.read_sql_query(consulta_8,conn)

#print(ejemplo1)


consulta_8.plot(title="Hora del pedido",
                        kind="barh",
                        figsize=[15, 4],
                        x='product_name',
                        y='count_products',
                        xlabel="Pedidos",
                         ylabel="Clientes",
                         legend=False)
plt.show()

Obtenemos que el producto que se pide con mas frecuncia es banana nuevamente.

9. Para cada producto, ¿cuál es la proporción de las veces que se pide y que se vuelve a pedir?

Code

    consulta_9 = '''
 SELECT product_name, AVG(o.reordered) AS mean
FROM [order_products] o
JOIN [products] p ON o.product_id = p.product_id
WHERE o.reordered IS NOT NULL
  AND p.product_name IS NOT NULL
  AND o.product_id IS NOT NULL
GROUP BY p.product_name
--ORDER BY mean DESC
LIMIT 30;'''

consulta_9 = pd.read_sql_query(consulta_9, conn)
print(consulta_9)

                                       product_name      mean
0                                 #2 Coffee Filters  0.254545
1                      #2 Cone White Coffee Filters  0.000000
2                             #2 Mechanical Pencils  0.000000
3                   #4 Natural Brown Coffee Filters  0.358974
4            & Go! Hazelnut Spread + Pretzel Sticks  0.466667
5     (70% Juice!) Mountain Raspberry Juice Squeeze  1.000000
6      +Energy Black Cherry Vegetable & Fruit Juice  0.428571
7           0 Calorie Acai Raspberry Water Beverage  0.583333
8          0 Calorie Fuji Apple Pear Water Beverage  0.833333
9   0 Calorie Strawberry Dragonfruit Water Beverage  0.782609
10               0% Fat Black Cherry Greek Yogurt y  0.636364
11                    0% Fat Blueberry Greek Yogurt  0.623377
12                         0% Fat Free Organic Milk  0.694301
13   0% Fat Greek Yogurt Black Cherry on the Bottom  0.526316
14                      0% Fat Greek Yogurt Vanilla  0.687500
15              0% Fat Organic Greek Vanilla Yogurt  0.665339
16                        0% Fat Peach Greek Yogurt  0.388889
17                   0% Fat Strawberry Greek Yogurt  0.775510
18                  0% Fat Superfruits Greek Yogurt  0.579439
19                      0% Fat Vanilla Greek Yogurt  0.631579
20                         0% Greek Strained Yogurt  0.835959
21       0% Greek Yogurt Black Cherry on the Bottom  0.629870
22         0% Greek- Blueberry on the Bottom Yogurt  0.708543
23                    0% Milkfat Greek Plain Yogurt  0.333333
24                    0% Milkfat Greek Yogurt Honey  0.666667
25                   007 Vodka With Martini Glasses  0.000000
26                                  1 % Lowfat Milk  0.641026
27                      1 Apple + 1 Mango Fruit Bar  0.628019
28                       1 Apple + 1 Pear Fruit Bar  0.606335
29                              1 Cup Measuring Cup  0.200000

-2 Coffee Filters: 0.2545 significa que aproximadamente el 25.45% de las veces que se pidió este producto, se volvió a pedir.

-2 Cone White Coffee Filters: 0.0 indica que este producto nunca se volvió a pedir.

-(70% Juice!) Mountain Raspberry Juice Squeeze: 1.0 sugiere que cada vez que se pidió este producto, se volvió a pedir.

10.Para cada cliente, ¿qué proporción de sus productos ya los había pedido?

Code

    consulta_10 = '''
    SELECT o.user_id, 
       SUM(CASE WHEN op.reordered = 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(op.product_id) 
       AS reorder_ratio
    FROM instacart_orders o
    JOIN order_products op ON o.order_id = op.order_id
    GROUP BY o.user_id;'''

    consulta_10 = pd.read_sql_query(consulta_10, conn)
    #print(consulta_10)

    sns.histplot(consulta_10['reorder_ratio'], bins=30, kde=True)
    plt.title('Distribución de la Proporción de Productos Reordenados por Cliente')
    plt.xlabel('Proporción de Reordenes')
    plt.ylabel('Frecuencia')
    plt.show()

Los valores varían desde 0, donde se representa que el usuario no ha reordenado ningún producto, hasta valores más altos, lo que indica que casi todos los productos de ese usuario han sido reordenados. Sabiendo esto, podemos observar que un importante número de productos no son reodenados, lo que podría investigarse, podría ser resultado de productos de descuento en promoción. Posteriormente vemos que la proporción de reordenes crece en al rededor de .5 y teniendo otro pico en 1. Se sugiere revisar si estamos ante una “canasta surtida” donde el cliente compra los productos de interés y uso cotidiano y aprovecha para probar otros productos con descuento o promoción.

11.¿Cuáles son los 20 principales artículos que las personas ponen primero en sus carritos?

Code

query_11 = '''
SELECT p.product_name, COUNT(o.product_id) AS count_first_added
FROM order_products o
JOIN products p ON o.product_id = p.product_id
WHERE o.add_to_cart_order = 1
GROUP BY o.product_id, p.product_name
ORDER BY count_first_added DESC
LIMIT 20;
'''
graf_11 = pd.read_sql_query(query_11, conn)
#print(graf_11)
#Graficando el resultado de la consulta
plt.figure(figsize=(10, 6))
# Crear la gráfica de barras con Matplotlib
plt.barh(graf_11['product_name'], graf_11['count_first_added'], color='skyblue')
# Añadir títulos y etiquetas
plt.title('Top 20 productos elegidos en primer lugar', fontsize=16)
plt.xlabel('Número de veces añadido al carrito')
plt.ylabel('Nombre del producto')
# Invertir el orden del eje y para que el producto más vendido esté arriba
plt.gca().invert_yaxis()
# Mostrar la gráfica
plt.tight_layout()
plt.show()

El producto que se agrega principalmente al carrito es la banana, con más de 15,000 pedidos, lo que la convierte en el artículo más vendido.

Objetivo

Contenido de los datos

Carga de librerías

Conexión a la Base de Datos

Insights

1. Para cada hora del día, ¿cuántas personas hacen órdenes?

2. ¿Qué día de la semana compran víveres las personas?

3. ¿Cuánto tiempo esperan las personas hasta hacer otro pedido? Comenta sobre los valores mínimos y máximos.

4. Diferencia entre miércoles y sábados para 'order_hour_of_day'.

5. ¿Cuál es la distribución para el número de pedidos por cliente?

6. ¿Cuáles son los 20 productos más populares (muestra su ID y nombre)?

7. ¿Cuántos artículos compran normalmente las personas en un pedido? ¿Cómo es la distribución?

8. ¿Cuáles son los 20 principales artículos que vuelven a pedirse con mayor frecuencia (muestra sus nombres e IDs de los productos)?

9. Para cada producto, ¿cuál es la proporción de las veces que se pide y que se vuelve a pedir?

10.Para cada cliente, ¿qué proporción de sus productos ya los había pedido?

11.¿Cuáles son los 20 principales artículos que las personas ponen primero en sus carritos?

4. Diferencia entre miércoles y sábados para `'order_hour_of_day'`.