############################################ ANÁLISIS DE VENTAS ############################################
#1. Queremos saber cuales son las ventas por año y por mes en términos brutos y luego el margen absoluto.
SELECT
Year(created_at) AS Año,
MONTH(created_at) AS Mes,
FORMAT(SUM(items_purchased*price_usd),2,'es_ESP') AS Ventas,
FORMAT(SUM(items_purchased*(price_usd-cogs_usd)),2,'es_ESP') AS Margen_absoluto
FROM ositofeliz.orders
GROUP BY Año, Mes
ORDER BY Año, Mes;
#2. ¿Cuales son las ventas brutas medias de cada mes y año, devuelve los TOP 10? ¿Que puedes observar?
SELECT
Year(created_at) AS Año,
MONTH(created_at) AS Mes,
FORMAT(SUM(items_purchased*price_usd),2,'es_ESP') AS Ventas,
FORMAT(AVG(items_purchased*price_usd),2,'es_ESP') AS Pedido_avg
FROM ositofeliz.orders
GROUP BY Año, Mes
ORDER BY Pedido_avg DESC, Año, Mes
LIMIT 10;
#En este caso el valor que nos da en "Pedido_avg" es lo que se vende en promedio en cada pedido
#3. ¿Cuál es el producto que mas vende en términos monetarios (Ventas brutas)?
SELECT
p.product_name,
SUM(o.items_purchased*o.price_usd) AS Ventas_brutas,
FORMAT(SUM(o.items_purchased*o.price_usd),2,'es_ESP') AS Ventas_brutas_formateado
FROM ositofeliz.orders o
LEFT JOIN ositofeliz.order_items oi ON o.order_id = oi.order_id
LEFT JOIN ositofeliz.products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY Ventas_brutas DESC
LIMIT 1;
#4. ¿Cuál es el producto que deja más margen?
SELECT
p.product_name,
SUM(o.items_purchased*(o.price_usd-o.cogs_usd)) AS Margen_absoluto,
FORMAT(SUM(o.items_purchased*(o.price_usd-o.cogs_usd)),2,'es_ESP') AS Margen_absoluto_formateado
FROM ositofeliz.orders o
LEFT JOIN ositofeliz.order_items oi ON o.order_id = oi.order_id
LEFT JOIN ositofeliz.products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY Margen_absoluto DESC
LIMIT 1;
#5. ¿Podemos saber cúal es la fecha de lanzamiento de cada producto?
#En este caso vamos a considerar como fecha de lanzamiento a la fecha en que se realiza la primera venta de cada producto
SELECT
p.product_name,
DATE(MIN(o.created_at)) fecha_venta
FROM ositofeliz.orders o
LEFT JOIN ositofeliz.order_items oi ON o.order_id = oi.order_id
LEFT JOIN ositofeliz.products p ON oi.product_id = p.product_id
GROUP BY p.product_name;
#6. Calcula las ventas brutas por año asi como el margen numérico y porcentual de cada producto y ordénalo por producto.
SELECT
p.product_name,
Year(o.created_at) AS Año,
FORMAT(SUM(o.items_purchased*o.price_usd),2,'es_ESP') AS Ventas_brutas,
FORMAT(SUM(o.items_purchased*(o.price_usd-o.cogs_usd)),2,'es_ESP') AS Margen_absoluto,
ROUND((SUM(o.items_purchased*(o.price_usd-o.cogs_usd)))/(SUM(o.items_purchased*o.price_usd)),2) AS Margen_porcentual,
CONCAT(ROUND(((SUM(o.items_purchased*(o.price_usd-o.cogs_usd)))/(SUM(o.items_purchased*o.price_usd)))*100,2),'%') AS Margen_porcentual_100
FROM ositofeliz.orders o
LEFT JOIN ositofeliz.order_items oi ON o.order_id = oi.order_id
LEFT JOIN ositofeliz.products p ON oi.product_id = p.product_id
GROUP BY p.product_name, Año
ORDER BY p.product_name, Año;
#7. ¿Cuáles son los meses con mayor venta bruta, devuelve los TOP 3?
SELECT
CONCAT(YEAR(created_at),"/",MONTH(created_at)) AS Año_Mes,
FORMAT(SUM(items_purchased*price_usd),2,'es_ESP') AS Ventas_bruta
FROM ositofeliz.orders
GROUP BY Año_Mes
ORDER BY SUM(items_purchased*price_usd) DESC
LIMIT 3;
####################################### ANÁLISIS DE TRAFICO WEB ############################################
#8. ¿Cuales son los ads(anuncios) o contenidos que han atraído más sesiones?
SELECT
utm_content,
FORMAT(COUNT(website_session_id),0,'es_ESP') AS cantidad_sesiones
FROM ositofeliz.website_sessions
GROUP BY utm_content
ORDER BY COUNT(website_session_id) DESC;
#9. Es lo mismo sesiones que usuarios?¿Cuál es la cantidad de usuarios individuales?
SELECT
COUNT(DISTINCT user_id) AS cantidad_usuarios,
COUNT(DISTINCT website_session_id) AS cantidad_sesiones
FROM ositofeliz.website_sessions;
#10. ¿Y por source o fuente? Cantidad de usuarios y sesiones?
SELECT
utm_source,
COUNT(DISTINCT user_id) AS cantidad_usuarios,
COUNT(DISTINCT website_session_id) AS cantidad_sesiones
FROM ositofeliz.website_sessions
GROUP BY utm_source;
#11. ¿Cúales son las sources o fuentes que han dado más ventas?
SELECT
utm_source,
FORMAT(SUM(items_purchased*price_usd),2,'es_ESP') AS Ventas_brutas
FROM ositofeliz.website_sessions ws
LEFT JOIN ositofeliz.orders o ON ws.website_session_id = o.website_session_id
GROUP BY utm_source
ORDER BY SUM(items_purchased*price_usd) DESC;
#12. ¿Cúales son los meses que han atraido más tráfico?
SELECT
CONCAT(YEAR(created_at),"/",MONTH(created_at)) AS Año_Mes,
FORMAT(COUNT(website_session_id),0,'es_ESP') AS Cantidad_sesiones
FROM ositofeliz.website_sessions
GROUP BY Año_Mes
ORDER BY COUNT(website_session_id) DESC;
#13. Ya que vimos el mes que ha tenido más trafico, podrías ver de ese mes la cantidad de sesiones que han venido por movil y la cantidad
# que han venido por ordenador?
SELECT
CONCAT(YEAR(created_at),"/",MONTH(created_at)) AS Año_Mes,
device_type,
FORMAT(COUNT(website_session_id),0,'es_ESP') AS Cantidad_sesiones
FROM ositofeliz.website_sessions
GROUP BY Año_Mes, device_type
HAVING Año_Mes = '2012/11'
ORDER BY COUNT(website_session_id) DESC;
#14. ¿Qué campañas son las que han dado más margen por productos?
SELECT
ws.utm_campaign,
p.product_name,
FORMAT(SUM(o.items_purchased*(o.price_usd-o.cogs_usd)),2,'es_ESP') AS Margen_absoluto
FROM ositofeliz.website_sessions ws
INNER JOIN ositofeliz.orders o ON ws.website_session_id = o.website_session_id
INNER JOIN ositofeliz.products p ON o.primary_product_id = p.product_id
GROUP BY ws.utm_campaign, p.product_name
ORDER BY SUM(o.items_purchased*(o.price_usd-o.cogs_usd)) DESC