import pandas as pd
import sqlite3
# Conectar a database de SQLite
con = sqlite3.connect('atida.db')
Vamos a obtener los parƔmetros:
Posteriormente hacemos un analisis mas detallado incluyendo los mismos parametros pero desglosados por dia y categoria.
Las categorias son:
# 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)
# 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)
Primero vamos a obtener una lista con todos los clientes incluyendo:
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)
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.
# 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)
Vamos a estudiar la Primera Compra de los Clientes Nuevos incluyendo:
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
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:
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:
Los datos geograficos de esta seccion no van a estar relacionados con el tiempo
# 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)
# 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)
# 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)
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="|")
# 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="|")
# 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="|")
Vamos a calcular:
# 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)
# 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)
# 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 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)
# 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)
# 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)
# 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()
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)
# 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)
# 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())
# 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())
con.close()