👗Glam Closing Co.

A company that commercializes stylish and trendy clothes.

Requirement

The sales manager requested a report including information on the sales department to analyze the sales performance to make business decisions.

Tools

MySQL to get database.

Word document template to report such information to owners.

Data package

The software to post downloaded transactions to a CSV file.

Database 'GlamClosing' with 3 tables: 'producto_detalle', 'producto_precio, 'ventas'.

Producto_detalle table contains 12 rows with 8 columns (id_producto, precio, nombre_producto, id_categoria, id_segmento, id_estilo, nombre_categoria, nombre_segmento).

Producto_precio table contains 12 rows with 3 columns (id, id_producto, precio).

Ventas table contains 15,095 rows with 7 columns (id_producto, qty, precio, descuento, miembro, id_txn, txn_hora).

Note that 'txn' means transaction.

CODING

Sales Aanalysis

#1. List sales quantities associated with all products SELECT SUM(qty) AS cantidad_total FROM GlamClosing.ventas;

#2. List sales quantities by products SELECT id_producto, SUM(qty) AS cantidad_total FROM GlamClosing.ventas GROPUP BY id_producto;

#3. List total income before discounts of all products sold SELECT FORMAT(SUM(precio*qty),2) AS ventas_brutas FROM GlamClosing.ventas;

#4. List total gross sales by products ID SELECT id_producto, SUM(precio*qty) AS ventas_brutas FROM GlamClosing.ventas GROUP BY by id_producto;

#5. List total net sales by products ID SELECT id_producto, SUM(precio*qty-descuento) AS ventas_netas FROM GlamClosing.ventas GROUP BY id_producto;

#6. List total gross sales and net sales by products ID SELECT id_producto, FORMAT(SUM(precio*qty),2) AS ventas_brutas, FORMAT(SUM(precio*qty-descuento),2) AS promedio_ventas_brutas FROM GlamClosing.ventas GROUP BY id_producto;

#7. List average gross sales before discounts by all products SELECT id_producto, FORMAT(SUM(precio*qty),2) AS ventas_brutas, FORMAT(AVG(precio*qty),2) AS ingreso_medio_ventas_brutas FROM GlamClosing.ventas GROUP BY id_producto;

#8. List gross sales before discounts by product SELECT pd.nombre_producto, FORMAT(SUM(v.precio*v.qty),2) AS ingreso_total, FORMAT(SUM(v.precio*v.qty-v.descuento),2) AS ingreso_neto FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle AS pd ON v.id_producto = pd.id_producto GROUP BY nombre_producto ORDER BY ingreso_neto desc;

#9. List total discounts to all products SELECT FORMAT(SUM(descuento),2) AS descuento_total FROM GlamClosing.ventas;

#10. List total discounts product SELECT pd.nombre_producto, FORMAT(SUM(v.precio*v.qty),2) AS ingreso_total, FORMAT(SUM(v.precio*v.qty-v.descuento),2) AS ingreso_neto, FORMAT(SUM(v.descuento),2) AS descuento_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle AS pd ON v.id_producto = pd.id_producto GROUP BY nombre_producto ORDER BY descuento_total desc;

Transaction Analysis

#1. List unique transactions. SELECT COUNT(DISTINCT(id_txn)) FROM GlamClosing.ventas;

#2. List total sales by transaction SELECT id_txn, FORMAT(SUM(precio*qty),2) AS ventas_brutas FROM GlamClosing.ventas GROUP BY id_txn ORDER BY ventas_brutas;

#3. List quantity of sold product by transaction SELECT id_txn, SUM(qty) AS cantidad_por_producto FROM GlamClosing.ventas GROUP BY id_txn ORDER BY cantidad_por_producto desc;

#4. Sort previous result from higher to lower product quantity SELECT id_txn, SUM(qty) AS cantidad_por_producto FROM GlamClosing.ventas GROUP BY id_txn ORDER BY cantidad_por_producto desc;

#5. List average discount by transaction SELECT id_txn, AVG(descuento) AS promedio_venta_bruta FROM GlamClosing.ventas GROUP BY id_txn ORDER BY promedio_venta_bruta desc;

#6. List average net income by transaction where member'c category = 't' SELECT id_txn, AVG(precio*qty-descuento) AS promedio_venta_neta FROM GlamClosing.ventas WHERE miembro = 't' GROUP BY id_txn ORDER BY promedio_venta_neta desc;

Product Analysis

#1. List top 3 products by total income before discount SELECT pd.nombre_producto, SUM(v.precio*v.qty) AS ventas_brutas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY nombre_producto ORDER BY ventas_brutas desc LIMIT 3;

#2. List total quantity, total income, and total discount of eahc segment of product SELECT pd.nombre_segmento, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas, SUM(v.descuento) AS descuento_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_segmento ORDER BY ventas_netas desc;

#3. List most sold product of each segment SELECT pd.nombre_segmento, pd.nombre_producto, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_segmento, pd.nombre_producto ORDER BY pd.nombre_segmento, ventas_netas desc;

#4. List total quantity, total income and total discount of each category SELECT pd.nombre_categoria, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty) AS ventas_brutas, SUM(v.descuento) AS descuento_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_categoria ORDER BY ventas_brutas desc;

#5. List most sold product of each category SELECT pd.nombre_categoria, pd.nombre_producto, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_categoria, pd.nombre_producto ORDER BY ventas_netas desc LIMIT 2;

#6. List top 5 lowest sold products SELECT pd.nombre_producto, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_producto ORDER BY ventas_netas LIMIT 5;

#7. List quantity sold products where category = 'Mujer' SELECT pd.nombre_categoria, SUM(v.qty) AS cantidad_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto WHERE pd.nombre_categoria = 'Mujer';

Outcomes

Through queries mentioned above was posible to provide a report with information related to sales, discounts, top 3 product, top 3 product with lowest sales, average gross sales and average net sales. That information helped managers to make business decisions.