💰Análisis Financiero & Tráfico Web - Ecommerce💰

############################################ 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? -- No. Sesiones es la cantidad de veces que se ingresa a una página web, no importa el usuario. 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