Team Origin Data Visualization

Analisis de datos para la visualizacion de Atida Mifarma

Abrir conexion a SQLite atida.db

import pandas as pd
import sqlite3

# Conectar a database de SQLite
con = sqlite3.connect('atida.db')

Parte 1: Analizar datos de ventas para Dashboard

Vamos a obtener los parámetros:

• Dia
• Ingresos
• Cantidad (cantidad de productos vendidos)
• Beneficio
• Pedidos
• Descuento Medio

Posteriormente hacemos un analisis mas detallado incluyendo los mismos parametros pero desglosados por dia y categoria.

Las categorias son:

• Cosmética y Belleza
• Higiene
• Infantil
• Herbolario
• Otros
• Nutrición
• Vida Íntima
• Óptica
• Ortopedia
• Perfumeria
• Veterinaria

Datos de dashboard_contadores con SQLite

# Crear dataframe para guardar datos
dashboard_contadores = pd.DataFrame()

query = '''
SELECT 
strftime('%Y-%m-%d', created_at) Dia,
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
COUNT (DISTINCT num_order) Pedidos,
AVG(discount) "Descuento Medio"
FROM items
GROUP BY Dia
ORDER BY Dia
'''

dashboard_contadores = pd.read_sql_query(query,con)

# Crear dataframe para guardar datos
dashboard_contadores = pd.DataFrame()

query = '''
SELECT Mes,
AVG(Ingresos/Pedidos) "€ por Pedido",
AVG(Cantidad/Pedidos) "Productos por Pedido"
FROM
(
SELECT 
strftime('%Y-%m', created_at) Mes,
strftime('%Y-%m-%d', created_at) Dia,
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
COUNT (DISTINCT num_order) Pedidos,
AVG(discount) "Descuento Medio"
FROM items
GROUP BY Dia
ORDER BY Dia
)
GROUP BY Mes
ORDER BY Mes
'''

dashboard_contadores = pd.read_sql_query(query,con)

# dashboard_contadores.head(25)

Datos de dashboard_categorias con SQLite

# Query para obtener las ventas por Dia y CATEGORIA de los items
dashboard_categorias = pd.DataFrame()

query = '''
SELECT 
strftime('%Y-%m-%d', created_at) Dia,
analytic_category Categoria,
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
COUNT (DISTINCT num_order) Pedidos,
AVG(discount) "Descuento Medio"
FROM items
LEFT JOIN products ON products.product_id = items.product_id
GROUP BY Dia, Categoria
ORDER BY Dia, Ingresos DESC
'''

dashboard_categorias = pd.read_sql_query(query,con)

# Mapear para convertir categoria None a Otros
def otros(Categoria):
    if Categoria == None:
        return "Otros"
    else:
        return Categoria

dashboard_categorias['Categoria'] = dashboard_categorias['Categoria'].map(otros)

# Guardar datos
dashboard_categorias.to_csv("dashboard_categorias.csv", sep="|", index=False)

Parte 2: Analizar datos de nuevos clientes

Primero vamos a obtener una lista con todos los clientes incluyendo:

• customer_id
• num_order: id del primer pedido -> nos servira para analizar que compran los clientes por primera vez
• dia: dia de la primera compra
• city
• zipcode: asociamos a cada usuario el CP de la primera compra que aparece
• weekday: Dia de la semana de su primera compra
• provincia

Añadimos esta lista a atida.db

Luego haremos un analisis mas detallado de lo que compran la primera vez.

# Abrir fichero de items con pandas
clientes = pd.read_csv("items_o_2_corregido.csv", sep="|")

# Marcar la columna "zipcode" como string
clientes["zipcode"] = clientes["zipcode"].astype("string")

# Corregir zipcodes a los que les han quitado 0's al principio por leerlos como numeros
def anadir0(zipcode):
    while len(zipcode) < 5:
        zipcode = '0' + zipcode
    return zipcode

clientes["zipcode"] = clientes["zipcode"].map(anadir0)

# Elimina las lineas que tienen customer_id repetidas, manteniendo la primera
clientes = clientes.drop_duplicates(subset='customer_id', keep='first')

# Mantener solo las columnas que nos interesan
del clientes['item_id']
del clientes['product_id']
del clientes['qty_ordered']
del clientes['base_cost']
del clientes['price']
del clientes['discount']

# Escribir a csv
clientes.to_csv("clientes.csv", sep="|", index=False)

# Crear una nueva tabla llamada clientes en la database
clientes.to_sql('clientes', con, if_exists='replace', index=False)

clientes.head(2)

Analisis detallado clientes nuevos

Ahora vamos a hacer un analisis de lo que compran los clientes por primera vez.

Limitaremos este analisis SOLO A 2018 y la segunda mitad de 2017 porque habra clientes que compraron a principios de 2017 que ya habian comprado antes, y por tanto no son clientes nuevos de verdad. Entendemos que todos los clientes que compraron en 2018 o segunda mitad de 2017, y no antes, son clientes nuevos.

Clientes nuevos por dia

# Crear nuevo dataframe
clientes_nuevos = pd.DataFrame()

# Obtener los clientes nuevos por dia para el periodo 2017-07-01 a 2018-12-31
query = '''
SELECT strftime('%Y-%m-%d', created_at) Dia, COUNT(customer_id) "Clientes Nuevos"
FROM clientes
WHERE Dia > "2017-06-31"
GROUP BY Dia
''' 

# Guardar los resultados en el dataframe
clientes_nuevos = pd.read_sql_query(query,con)

# Insertar los nuevos datos a dashboard_contadores haciendo un Left Join
dashboard_contadores = dashboard_contadores.merge(clientes_nuevos[['Clientes Nuevos', 'Dia']], on = 'Dia', how = 'left')

dashboard_contadores.to_csv("dashboard_contadores.csv", index=False)

dashboard_contadores.sample(2)

Qué compraron los clientes nuevos en su primera compra

Vamos a estudiar la Primera Compra de los Clientes Nuevos incluyendo:

• customer_id
• Ingresos: Suma de los Ingresos
• Cantidad: Suma de qty_ordered
• Beneficio: Suma de los Beneficios de cada item comprado
• Descuento Medio: Media de los Descuentos
• Numero de productos comprados desglosado por categoria

Agrupado por dia, sacando la media de las primeras compras de ese dia

# Crear nuevo dataframe
primera_compra_stats = pd.DataFrame()

# Obtener los clientes nuevos por dia para el periodo 2017-07-01 a 2018-12-31
# Posteriormente obtener los datos de items con INNER JOIN a la query anterior y agruparlos por pedido
query = '''
SELECT
Dia,
SUM(Ingresos) Ingresos,
SUM(Cantidad) Cantidad,
SUM(Beneficio) Beneficio,
AVG("Descuento Medio") "Descuento Medio"
FROM
(
SELECT
Dia,
items.num_order,
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
AVG(discount) "Descuento Medio"
FROM items
INNER JOIN
(
SELECT strftime('%Y-%m-%d', created_at) Dia,
num_order
FROM clientes
WHERE Dia > "2017-06-31"
)
AS aux
ON items.num_order = aux.num_order
GROUP BY items.num_order
ORDER BY Dia
)
GROUP BY Dia
''' 

# Guardar los resultados en el dataframe
primera_compra_stats = pd.read_sql_query(query,con)

primera_compra_stats.to_csv("primera_compra_stats.csv", sep="|", index=False)

# Crear nuevo dataframe
primera_compra_cat = pd.DataFrame()

# Obtener el n. de productos comprados en cada subcategoria-3 por clientes en su primera compra
query = '''
SELECT cat1 "Categoria 1", cat2 "Categoria 2", cat3 "Categoria 3", COUNT(*) items
FROM
(
SELECT cat1, cat2, cat3
FROM
(
    SELECT
    Dia,
    items.num_order num_order,
    items.product_id pid
    FROM items
    INNER JOIN
    (
    SELECT strftime('%Y-%m-%d', created_at) Dia,
    num_order
    FROM clientes
    WHERE Dia > "2017-06-31"
    )
    AS clientes_nuevos
    ON items.num_order = clientes_nuevos.num_order
    ORDER BY Dia
)
INNER JOIN products ON products.product_id = pid
INNER JOIN products_cat ON products.sku= products_cat.sku
)
GROUP BY cat1, cat2, cat3
''' 

# Guardar los resultados en el dataframe
primera_compra_cat = pd.read_sql_query(query,con)

primera_compra_cat.to_csv("primera_compra_cat.csv", sep="|", index=False)

# Obetener datos de compra por categorias DE TODOS LOS CLIENTES para comparar con clientes nuevos
# Crear nuevo dataframe
primera_compra_cat_comp = pd.DataFrame()

# Obtener los datos de items y agruparlos por categorias
query = '''
SELECT cat1 "Categoria 1", cat2 "Categoria 2", cat3 "Categoria 3", COUNT(*) items
FROM
(
SELECT cat1, cat2, cat3
FROM
(
    SELECT
    strftime('%Y-%m-%d', created_at) Dia,
    items.num_order num_order,
    items.product_id pid
    FROM items
    ORDER BY Dia
)
INNER JOIN products ON products.product_id = pid
INNER JOIN products_cat ON products.sku= products_cat.sku
)
GROUP BY cat1, cat2, cat3
''' 

# Guardar los resultados en el dataframe
primera_compra_cat_comp = pd.read_sql_query(query,con)

primera_compra_cat_comp.to_csv("primera_compra_cat_comp.csv", sep="|", index=False)

# Obtener stats de compras hechas por clientes nuevos
# Incluyendo, para cada dia, los Ingresos, Beneficio, n. Pedidos, dto. Medio de compras hechas por clientes nuevos

Stats de compras hechas por clientes nuevos

Parte 3: Analizar datos geograficos

Primero vamos a obtener una lista con todos los pedidos, con su codigo postal pasado a direccion para que pueda ser transformado por Google Maps a localizacion (latitud, longitud) y como peso los ingresos, beneficio, cantidad de dicha compra. Posteriormente se agrupan por codigo postal y se SUMA los ingresos, cantidad y beneficio Asi, los CP con mas ingresos totales tienen mas peso

Los parametros, por tanto, son:

• num_order: numero del pedido
• ingresos
• cantidad
• beneficio
• numero de pedidos
• direccion (ciudad + zipcode)

Posteriormente vamos a obtener los mismos parametros agrupados por provincia y comunidad

En estos casos vamos a añadir, para ver en que zonas de España la media del tamano, cantidad y beneficio de ventas por pedido es mas grande:

• ingresos por pedido
• cantidad por pedido
• beneficio por pedido

Los datos geograficos de esta seccion no van a estar relacionados con el tiempo

Datos geograficos por codigo postal

# Crear nuevo dataframe
geo_zipcode = pd.DataFrame()

query = '''
SELECT
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
COUNT(num_order) "Numero de pedidos",
zipcode || " " || city || " Spain" AS Direccion
FROM items
GROUP BY zipcode
ORDER BY Direccion DESC
''' 

# Guardar los resultados en el dataframe
geo_zipcode = pd.read_sql_query(query,con)

geo_zipcode.to_csv("geo_zipcode.csv", index=False, sep="|")

#geo_zipcode.head(2)

Datos geograficos por provincia

# Ingresos, Cantidad, Benefico por Provincia

# Crear nuevo dataframe
geo_provincias = pd.DataFrame()

query = '''
SELECT
Provincia,
SUM("Ingresos por pedido") Ingresos,
AVG("Ingresos por pedido") "Ingresos por pedido",
SUM("Cantidad por pedido") Cantidad,
AVG("Cantidad por pedido") "Cantidad por pedido",
SUM("Beneficio por pedido") Beneficio,
AVG("Beneficio por pedido") "Beneficio por pedido",
COUNT(num_order) "Numero de pedidos"
FROM
(
SELECT
num_order,
Provincia,
SUM(price * qty_ordered * (1-discount)) "Ingresos por pedido",
SUM(qty_ordered) "Cantidad por pedido",
SUM((price * qty_ordered * (1-discount)) - base_cost) "Beneficio por pedido"
FROM items
WHERE Provincia IS NOT NULL
GROUP BY num_order
)
GROUP BY Provincia
ORDER BY Beneficio DESC
''' 

# Guardar los resultados en el dataframe
geo_provincias = pd.read_sql_query(query,con)

geo_provincias.to_csv("geo_provincias.csv", index=False, sep="|")

#geo_provincias.head(2)

Datos geograficos por comunidad

# Crear nuevo dataframe
geo_comunidades = pd.DataFrame()

query = '''
SELECT
comunidad,
SUM("Ingresos por pedido") Ingresos,
AVG("Ingresos por pedido") "Ingresos por pedido",
SUM("Cantidad por pedido") Cantidad,
AVG("Cantidad por pedido") "Cantidad por pedido",
SUM("Beneficio por pedido") Beneficio,
AVG("Beneficio por pedido") "Beneficio por pedido",
COUNT(num_order) "Numero de pedidos"
FROM
(
SELECT
num_order,
Comunidad,
SUM(price * qty_ordered * (1-discount)) "Ingresos por pedido",
SUM(qty_ordered) "Cantidad por pedido",
SUM((price * qty_ordered * (1-discount)) - base_cost) "Beneficio por pedido"
FROM items
WHERE comunidad IS NOT NULL
GROUP BY num_order
)
GROUP BY Comunidad
ORDER BY Beneficio DESC
''' 

# Guardar los resultados en el dataframe
geo_comunidades = pd.read_sql_query(query,con)

geo_comunidades.to_csv("geo_comunidades.csv", index=False, sep="|")

#geo_comunidades.head(20)

Penetracion de Mercado por Provincia y evolucion temporal

Queremos obtener Ingresos y Pedidos agrupados por Provincia y mes

Posteriormente lo enriquecemos en Google Sheets con datos del INE de poblacion de 2018 para calcualr la penetracion

# Crear nuevo dataframe
penetracion = pd.DataFrame()

query = '''
SELECT
SUM(price * qty_ordered * (1-discount)) Ingresos,
COUNT(*) Pedidos,
strftime('%Y-%m', created_at) Mes,
Provincia,
Comunidad
FROM items
WHERE Provincia IS NOT NULL
AND Provincia IS NOT "Palmas, Las"
AND Provincia IS NOT "Santa Cruz de Tenerife"
GROUP BY Mes, Provincia
ORDER BY Mes, Provincia
''' 

# Guardar los resultados en el dataframe
penetracion = pd.read_sql_query(query,con)

#penetracion.head()

penetracion.to_csv("penetracion.csv", index=False, sep="|")

Productos mas populares por Provincia y mes

# Crear nuevo dataframe
productos_populares_provincia = pd.DataFrame()

query = '''
SELECT Provincia, Mes, name Nombre, marca_value Marca, ifnull(analytic_category, 'Otros') AS Categoria, Ventas
FROM
(
SELECT product_id id, SUM(qty_ordered) Ventas, Provincia, strftime('%Y-%m', created_at) Mes
FROM items
GROUP BY Mes, product_id
ORDER BY Ventas DESC
)
INNER JOIN products ON products.product_id = id
WHERE Marca IS NOT NULL
AND Nombre IS NOT NULL
AND Ventas > 9
AND Provincia IS NOT NULL
ORDER BY Provincia, Mes, Ventas DESC
'''

# Guardar los resultados en el dataframe
productos_populares_provincia = pd.read_sql_query(query,con)

#productos_populares_provincia.head()

productos_populares_provincia.to_csv("productos_populares_provincia.csv", index=False, sep="|")

Parte 4 Clientes

Analisis Clientes

# Crear nuevo dataframe
clientes_dia = pd.DataFrame()

query = '''
SELECT
strftime('%Y-%m-%d', created_at) Dia,
COUNT (DISTINCT customer_id) clientes_dia
FROM items
GROUP BY Dia
ORDER BY Dia
''' 

# Guardar los resultados en el dataframe
clientes_dia = pd.read_sql_query(query,con)

#clientes_dia.head()

clientes_dia.to_csv("clientes_dia.csv", index=False, sep="|")

Estadisticas Clientes

Vamos a calcular:

• Edad Media: obtenida en funcion de la provincia de los clientes, con datos del INE
• Sexo: Hombres vs Mujeres Extraplado de los datos del INE por Provincia
• Renta Media en Ciudades: por Codigo Postal con datos de la Agencia Tributaria
• Relacion entre la renta media y el numero de clientes, beneficio medio, productos vendidos por CP
• Poblaciones Distintas: total de Codigos Postales distintos en el dataset

# Edad Media y Sexo
# Fuente: INE

# Crear nuevo Dataframe
clientes_por_provincia = pd.DataFrame()

# Query para obtener los clientes que hay de cada provincia
query = '''
SELECT Provincia, COUNT(*) Clientes
FROM clientes
WHERE Provincia IS NOT NULL
GROUP BY Provincia
ORDER BY Provincia
'''
clientes_por_provincia = pd.read_sql_query(query, con)

# Guardar en CSV para abrir en Google Sheets y continuar con los calculos
clientes_por_provincia.to_csv("clientes_por_provincia.csv", sep="|")

# Renta Media por Codigo Postal

# Leer clientes
clientes = pd.read_csv("clientes.csv", sep="|")

# Leer datos limpiados de renta por codigo postal
renta = pd.read_csv("Datos Adicionales/renta_bruta_media_cp.csv")
# Renombra a "zipcode" y "Renta"
renta.rename(columns = {'Codigo Postal':'zipcode', 'Renta Bruta Media':'Renta'}, inplace = True)

# Hacer inner join
clientes = clientes.merge(renta[['zipcode', 'Renta']], on = 'zipcode', how = 'inner')

# Calcular la Renta Burta Media
renta_media = clientes['Renta'].mean()

print(f"Renta Media: {renta_media}")

# Relacion entre la Renta Media en un CP y el numero de clientes, productos vendidos, beneficio, ingresos, pedidos

# Leer datos limpiados de renta por codigo postal
renta_cp = pd.read_csv("Datos Adicionales/renta_bruta_media_cp.csv")

# Renombra a "zipcode" y "Renta"
renta_cp.rename(columns = {'Codigo Postal':'zipcode', 'Renta Bruta Media':'Renta'}, inplace = True)

# Marcar la columna "zipcode" como string
renta_cp["zipcode"] = renta_cp["zipcode"].astype("string")

# Corregir zipcodes a los que les han quitado 0's al principio por leerlos como numeros
def anadir0(zipcode):
    while len(zipcode) < 5:
        zipcode = '0' + zipcode
    return zipcode

renta_cp["zipcode"] = renta_cp["zipcode"].map(anadir0)

# Eliminar las columnas que no hacen falta
del renta_cp["Ciudad"]
del renta_cp["Barrio"]

# Calcular el numero de clientes de ese codigo postal
# Leer clientes
clientes = pd.read_csv("clientes.csv", sep="|")

# Marcar la columna "zipcode" como string
clientes["zipcode"] = clientes["zipcode"].astype("string")

# Corregir zipcodes a los que les han quitado 0's al principio por leerlos como numeros
clientes["zipcode"] = clientes["zipcode"].map(anadir0)

set_aux = set() # Set auxiliar que contiene los zipcode incluidos en renta_bruta_media_cp.csv
for zipcode in renta_cp["zipcode"]:
    set_aux.add(zipcode)

d = {} # Diccionario que relaciona zipcode con numero de clientes
# Para cada cliente, si su zipcode esta entre los que vamos a usar, contarlo en el diccionario
for zipcode in clientes["zipcode"]: 
    if zipcode in set_aux:
        # Si ese zipcode ya estaba en el diccionario:
        if zipcode in d:
            d[zipcode] += 1
        else: # Si no estaba, contarlo anadiendolo al diccionario
            d[zipcode] = 1

# Anadir a renta_cp el numero de clientes de cada cp con map
def mapear_clientes_cp(zipcode):
    if zipcode in d:
        return d[zipcode]
    else:
        return 0

# Crear nueva columna con el numero de clientes en cada codigo postal de los CP seleccionados
renta_cp["N. Clientes"] = renta_cp["zipcode"].map(mapear_clientes_cp)

# Calcular el beneficio y los productos vendidos en cada uno de los CP seleccionados
query = '''
SELECT 
zipcode,
SUM(qty_ordered) "Productos Vendidos",
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
SUM(price * qty_ordered * (1-discount)) Ingresos,
COUNT(DISTINCT num_order) Pedidos
FROM items
GROUP BY zipcode
ORDER BY zipcode
'''

aux = pd.read_sql_query(query, con)

# Hacer un merge entre renta_cp y aux para quedar solo los datos de los zipcode que disponemos
renta_cp = pd.merge(renta_cp, aux, on = 'zipcode', how = 'inner')

# Dejar solo aquellos zipcodes donde el numero de clientes sea mayor a 10, para evitar conclusiones estadisticamente sesgadas
renta_cp = renta_cp[(renta_cp['N. Clientes'] > 10)]

# Cambiar Beneficio a Beneficio por Cliente y P. Vendidos a P. Vendidos por Cliente e Ingresos a Ingresos por Cliente y por Pedido
def beneficio_medio(row):
    beneficio = row['Beneficio']
    n_clientes = row['N. Clientes']
    return beneficio / n_clientes

def ingresos_medio_cliente(row):
    ingresos = row['Ingresos']
    n_clientes = row['N. Clientes']
    return ingresos / n_clientes

def ingresos_medio_pedido(row):
    ingresos = row['Ingresos']
    pedidos = row['Pedidos']
    return ingresos / pedidos

def prod_vendidos_medio(row):
    prod_vendidos = row['Productos Vendidos']
    n_clientes = row['N. Clientes']
    return prod_vendidos / n_clientes

renta_cp['Beneficio por Cliente'] = renta_cp.apply(beneficio_medio, axis=1)
renta_cp['Productos Vendidos por Cliente'] = renta_cp.apply(prod_vendidos_medio, axis=1)
renta_cp['Ingresos por Cliente'] = renta_cp.apply(ingresos_medio_cliente, axis=1)
renta_cp['Ingresos por Pedido'] = renta_cp.apply(ingresos_medio_pedido, axis=1)

# Eliminar columnas qur ya no hacen falta
del renta_cp["Beneficio"]
del renta_cp["Productos Vendidos"]

renta_cp.to_csv("renta_cp_relacion_beneficio.csv", sep="|", index=False)

# Poblaciones Distintas
query = '''
SELECT COUNT(DISTINCT zipcode)
FROM clientes
'''
cursor = con.cursor()
cursor.execute(query)
n = cursor.fetchall()
print(n)

Parte 5 Que compran nuestros clientes

Marcas mas populares por categoria

# Crear nuevo Dataframe
marcas = pd.DataFrame()

# Query
query = '''
SELECT marca_value Marca, analytic_category Categoria, SUM(Ventas) Ventas
FROM
(
SELECT product_id id, SUM(qty_ordered) Ventas
FROM items
GROUP BY product_id
ORDER BY Ventas DESC
)
INNER JOIN products ON products.product_id = id
GROUP By Marca, Categoria
ORDER BY Categoria, Ventas DESC
'''
marcas = pd.read_sql_query(query, con)

marcas.head()

# Guardar en CSV
marcas.to_csv("marcas.csv", sep="|", index=False)

Productos mas populares por categoria

# Crear nuevo Dataframe
productos = pd.DataFrame()

# Query
query = '''
SELECT name Nombre, marca_value Marca, analytic_category Categoria, Ventas
FROM
(
SELECT product_id id, SUM(qty_ordered) Ventas
FROM items
GROUP BY product_id
ORDER BY Ventas DESC
)
INNER JOIN products ON products.product_id = id
WHERE Marca IS NOT NULL
AND Nombre IS NOT NULL
AND Ventas > 9
ORDER BY Categoria, Ventas DESC
'''
productos = pd.read_sql_query(query, con)

productos.head()

# Guardar en CSV
productos.to_csv("productos.csv", sep="|", index=False)

# Crear nuevo Dataframe
productos = pd.DataFrame()

# Query
query = '''
SELECT 

FROM products_cat
'''
marcas = pd.read_sql_query(query, con)

#marcas.count

# Guardar en CSV
marcas.to_csv("marcas.csv", sep="|", index=False)

Paquetizacion de productos

Paquetizacion mas frecuente de los 25 productos mas populares

# 25 productos mas populares
# Crear nuevo Dataframe
productos_25 = pd.DataFrame()

# Query
query = '''
SELECT items.product_id producto_inicial, SUM(qty_ordered) Ventas, analytic_category Categoria, name Nombre, marca_value Marca
FROM items
INNER JOIN products ON products.product_id = producto_inicial
WHERE Categoria IS NOT NULL
AND Nombre IS NOT NULL
AND Marca IS NOT NULL
GROUP BY producto_inicial
ORDER BY Ventas DESC
LIMIT 25
'''
productos_25 = pd.read_sql_query(query, con)

# Crear nuevos DataFrames
paquetizacion = pd.DataFrame()
mas_veces = pd.DataFrame()

# Hacer un loop que calcule para cada producto de los 25 mas vendidos los 5 con los que mas se suele comprar junto
for id_a_buscar in productos_25["id"]:
    # SQL Query que recibe un product_id y encuentra el product_id del producto que mas veces se ha comprado junto con el primero
    # Se considera que si dos productos se compran en el mismo pedido, esto es 1 emparejamiento independientemente de cuantos
    # productos de cada tipo se hayan comprado
    query = f'''
SELECT items.product_id, COUNT(*) Emparejamientos, analytic_category Categoria, name Nombre, marca_value Marca
FROM items
INNER JOIN products ON products.product_id = items.product_id
WHERE num_order IN
(
SELECT DISTINCT num_order
FROM items
WHERE items.product_id = {id_a_buscar}
)
AND items.product_id IS NOT {id_a_buscar}
GROUP BY items.product_id
ORDER BY Emparejamientos DESC
LIMIT 5
    '''
    mas_veces = pd.read_sql_query(query, con)
    mas_veces["producto_inicial"] = id_a_buscar
    paquetizacion = paquetizacion.append(mas_veces)

# Hacer un Inner Join para identificar cada producto inicial con sus parejas
#paquetizacion2 = paquetizacion.merge(productos_25[['Ventas', 'Categoria', 'Nombre', 'Marca']], on = 'producto_inicial', how = 'outer')

df = pd.merge(productos_25, paquetizacion, on="producto_inicial")

df.to_csv("paquetizacion.csv", sep="|", index=False)

Parte 6 - Habitos de compra

Ingresos por hora del dia y dia de la semana. Y por categoria y dia de la semana

# DataFrames
ingresos_hora_dia = pd.DataFrame()
ingresos_dia_semana = pd.DataFrame()
ingresos_cat_dia = pd.DataFrame()
ingresos_mes = pd.DataFrame()

# querys
query = '''
SELECT strftime('%H', created_at) Hora,
SUM(price * qty_ordered * (1-discount)) Ingresos
FROM items
GROUP BY Hora
ORDER BY Hora
'''
ingresos_hora_dia = pd.read_sql_query(query, con)

query = '''
SELECT strftime('%m', created_at) Mes,
SUM(price * qty_ordered * (1-discount)) Ingresos
FROM items
GROUP BY Mes
ORDER BY Mes
'''
ingresos_mes = pd.read_sql_query(query, con)

query = '''
SELECT weekday Dia,
SUM(price * qty_ordered * (1-discount)) Ingresos
FROM items
GROUP BY Dia
ORDER BY Dia
'''
ingresos_dia_semana = pd.read_sql_query(query, con)

query = '''
SELECT weekday Dia,
SUM(price * qty_ordered * (1-discount)) Ingresos,
analytic_category Categoria
FROM items
INNER JOIN products ON products.product_id = items.product_id
WHERE Categoria IS NOT NULL
GROUP BY Dia, Categoria
ORDER BY Dia, Ingresos DESC
'''
ingresos_cat_dia = pd.read_sql_query(query, con)

# Guardar en csv
ingresos_hora_dia.to_csv("ingresos_hora_dia.csv", sep="|", index=False)
ingresos_mes.to_csv("ingresos_mes.csv", sep="|", index=False)
ingresos_dia_semana.to_csv("ingresos_dia_semana.csv", sep="|", index=False)
ingresos_cat_dia.to_csv("ingresos_cat_dia.csv", sep="|", index=False)

Clientes que repiten compra

# DataFrame
repiten_compra = pd.DataFrame()

# query
query = '''
SELECT "Veces comprado", COUNT(*) Casos FROM
(
SELECT customer_id, product_id, COUNT(*) "Veces comprado"
FROM items
GROUP BY customer_id, product_id
)
GROUP BY "Veces comprado"
ORDER BY "Veces comprado" DESC
'''
repiten_compra = pd.read_sql_query(query, con)

#repiten_compra.head()

repiten_compra.to_csv("repiten_compra.csv", sep="|", index=False)

Stats medias por pedido

# Crear nuevo dataframe
primera_compra_stats_comp = pd.DataFrame()

query = '''
SELECT
Dia,
SUM(Ingresos) Ingresos,
SUM(Cantidad) Cantidad,
SUM(Beneficio) Beneficio,
AVG("Descuento Medio") "Descuento Medio"
FROM
(
SELECT
Dia,
items.num_order,
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
AVG(discount) "Descuento Medio"
FROM items
INNER JOIN
(
SELECT strftime('%Y-%m-%d', created_at) Dia,
num_order
FROM clientes
WHERE Dia > "2017-06-31"
)
AS aux
ON items.num_order = aux.num_order
GROUP BY items.num_order
ORDER BY Dia
)
GROUP BY Dia
''' 


query = '''
SELECT AVG(Ingresos) "Ingresos Medios", AVG(Cantidad) "Cantidad Media", AVG(Beneficio) "Beneficio Medio",
AVG("Descuento Medio") "Descuento Medio"
FROM (SELECT
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
AVG(discount) "Descuento Medio"
FROM items
GROUP BY num_order
)
'''

'''


(
'''

query = '''
SELECT AVG(Ingresos) "Ingresos Medios", AVG(Cantidad) "Cantidad Media", AVG(Beneficio) "Beneficio Medio",
AVG("Descuento Medio") "Descuento Medio"
FROM
(
SELECT
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
AVG(discount) "Descuento Medio"
FROM
(
SELECT strftime('%Y-%m-%d', created_at) Dia,
num_order
FROM items
WHERE Dia > "2017-06-31"
EXCEPT
SELECT strftime('%Y-%m-%d', created_at) Dia,
num_order
FROM clientes
WHERE Dia > "2017-06-31"
)
AS aux
INNER JOIN items ON aux.num_order = items.num_order
GROUP BY aux.num_order
)
'''

# Guardar los resultados en el dataframe
primera_compra_stats_comp = pd.read_sql_query(query,con)

primera_compra_stats_comp.head()

Parte 7 Marcas propias

Indicadores Marcas Propias - Contadores

Vamos a añadir una nueva tabla a la base de datos que incluya solo los items de marcas propias

items_prop = pd.DataFrame()

# Selecciona los items cuya marca sea de las Marcas Propias
query = '''
SELECT num_order, item_id, created_at, items.product_id, qty_ordered, base_cost, price, discount,
customer_id, name, marca_value, analytic_category
FROM items
INNER JOIN products ON products.product_id = items.product_id
WHERE marca_value="Mifarma Baby"
OR marca_value="Mifarma Daily"
OR marca_value="Tropicania"
OR marca_value="Skinfy"
'''

items_prop = pd.read_sql_query(query, con)

# Poner en una nueva tabla de SQL
items_prop.to_sql('items_prop', con, if_exists='replace', index=False)

# Crear dataframe para guardar datos
dashboard_prop_contadores = pd.DataFrame()

# Query para obtener datos de indicadores de marcas propias, y los de todas las marcas para comparar
query = '''
SELECT * FROM
(
SELECT
strftime('%Y-%m-%d', created_at) Dia,
SUM(price * qty_ordered * (1-discount)) Ingresos,
SUM(qty_ordered) Cantidad,
SUM((price * qty_ordered * (1-discount)) - base_cost) Beneficio,
COUNT (DISTINCT num_order) Pedidos
FROM items_prop
GROUP BY Dia
)
AS sel1
INNER JOIN
(
SELECT 
strftime('%Y-%m-%d', created_at) Dia,
SUM(price * qty_ordered * (1-discount)) "Ingresos totales",
SUM(qty_ordered) "Cantidad total",
SUM((price * qty_ordered * (1-discount)) - base_cost) "Beneficio total",
COUNT (DISTINCT num_order) "Pedidos totales"
FROM items
GROUP BY Dia
)
AS sel2
ON sel1.Dia = sel2.Dia
'''

dashboard_prop_contadores = pd.read_sql_query(query,con)

dashboard_prop_contadores.to_csv("dashboard_prop_contadores.csv", sep="|", index=False)

Indicadores por marca

# Crear dataframe para guardar datos
dashboard_prop_marcas = pd.DataFrame()

# Query para obtener datos de indicadores de marcas propias por marca y dia
query = '''
SELECT
strftime('%Y-%m-%d', created_at) Dia,
marca_value Marca,
SUM(qty_ordered) "Productos Vendidos",
SUM(price * qty_ordered * (1-discount)) Ingresos
FROM items_prop
GROUP BY Dia, Marca
'''

dashboard_prop_marcas = pd.read_sql_query(query,con)

dashboard_prop_marcas.to_csv("dashboard_prop_marcas.csv", sep="|", index=False)

Beneficio marcas propias vs media total

# Querys para obtener beneficio medio de marcas propias vs beneficio medio de todas las marcas
query = '''
SELECT AVG(Beneficio) "Beneficio medio" FROM 
(
SELECT
((price * qty_ordered * (1-discount)) - base_cost) Beneficio
FROM items_prop
)
'''

cursor = con.cursor()
cursor.execute(query)
print("Beneficio medio marcas propias")
print(cursor.fetchall())

query = '''
SELECT AVG(Beneficio) "Beneficio medio" FROM 
(
SELECT
((price * qty_ordered * (1-discount)) - base_cost) Beneficio
FROM items
)
'''

cursor.execute(query)
print("Beneficio medio todas las marcas")
print(cursor.fetchall())

Numero de productos ofertados de marcas propias

# Querys para obtener el n de prductos ofertados de marcas propias
query = '''
SELECT COUNT(DISTINCT product_id)
FROM items_prop
'''

cursor = con.cursor()
cursor.execute(query)
print("Numero de productos de marcas propias")
print(cursor.fetchall())

Parte 8: Cerrar conexion a atida.db

con.close()