This project analyzes declining profits for a bicycle wholesaler by leveraging SQL for data manipulation and Tableau for data visualization. Using advanced data analytics techniques, the project uncovers the drivers behind profit decline from 2005 to 2008. The focus is on data cleaning, sales trends analysis, and customer behavior. The insights derived from this analysis provide strategic recommendations to improve cost control, boost customer retention, and enhance marketing efforts. By optimizing data-driven decisions, businesses can significantly improve their profitability and operational efficiency.
The objective of this data analytics project is to identify key factors contributing to the profit decline between 2005 and 2008. Through data cleaning, SQL queries, and data visualization in Tableau, this analysis uncovers actionable insights into cost inefficiencies, customer behavior, and regional performance. The goal is to provide data-driven recommendations for improving profitability and driving business success by optimizing sales strategies, cost control, and marketing efforts. This approach ensures businesses can implement targeted solutions to enhance financial performance.
This analysis tracks sales, profit, and orders over months and years, using advanced SQL queries and data visualization in Tableau. It helps businesses understand how these key metrics fluctuate over time, identifying periods of high and low performance ( You can read more about how data analytics helps your business on my Blog). By visualizing these changes, businesses can make data-driven decisions to optimize profitability and improve operational efficiency. This approach provides clear insights into trends, enabling timely actions for maximizing growth opportunities while addressing performance issues.
SELECT
YEAR(OrderDate) AS Year,
MONTH(OrderDate) AS Month,
ROUND(SUM((UnitPrice * OrderQty) - (UnitPrice*UnitPriceDiscount*OrderQty)),2) AS Total_Sales,
ROUND(SUM(((UnitPrice - (UnitPrice * UnitPriceDiscount) - Unit_Cost) * OrderQty) - Unit_Freight_Cost),2) AS Total_Profit,
SUM(OrderQty) AS Total_Orders
FROM
sales
GROUP BY
YEAR(OrderDate), MONTH(OrderDate)
ORDER BY
Year, Month;
This SQL code is intended to track sales, profit, and orders over time, providing extensive information about monthly and annual patterns. Businesses can readily detect growth patterns or opportunities for improvement by computing percentage changes in each measure over the previous year. It's perfect for firms looking to improve their performance by examining swings in important financial metrics. This data-driven approach provides decision-makers with actionable information to help improve profitability and sales tactics.
WITH YearlyAggregates AS (
SELECT
YEAR(OrderDate) AS Year,
MONTH(OrderDate) AS Month,
ROUND(SUM((UnitPrice * OrderQty) - (UnitPrice * UnitPriceDiscount * OrderQty)), 2) AS Total_Sales,
ROUND(SUM(((UnitPrice - (UnitPrice * UnitPriceDiscount) - Unit_Cost) * OrderQty) - Unit_Freight_Cost), 2) AS Total_Profit,
SUM(OrderQty) AS Total_Orders
FROM
sales
GROUP BY
YEAR(OrderDate), MONTH(OrderDate)
)
SELECT
Year,
Month,
Total_Sales,
Total_Profit,
Total_Orders,
-- Calculate the percentage difference in Total Sales from the same month in the previous year
ROUND(
(Total_Sales - LAG(Total_Sales) OVER (PARTITION BY Month ORDER BY Year)) /
LAG(Total_Sales) OVER (PARTITION BY Month ORDER BY Year) * 100, 2
) AS Percent_Change_Sales,
-- Calculate the percentage difference in Total Profit from the same month in the previous year
ROUND(
(Total_Profit - LAG(Total_Profit) OVER (PARTITION BY Month ORDER BY Year)) /
LAG(Total_Profit) OVER (PARTITION BY Month ORDER BY Year) * 100, 2
) AS Percent_Change_Profit,
-- Calculate the percentage difference in Total Orders from the same month in the previous year
ROUND(
(Total_Orders - LAG(Total_Orders) OVER (PARTITION BY Month ORDER BY Year)) /
LAG(Total_Orders) OVER (PARTITION BY Month ORDER BY Year) * 100, 2
) AS Percent_Change_Orders
FROM
YearlyAggregates
ORDER BY
Year, Month;
This SQL query calculates total sales, profit, and order volume by sales region, assisting organizations in identifying their best-performing locations. By categorizing data by location and calculating total sales and profit, this analysis identifies the most profitable areas and enables targeted decision-making. Sorting the findings by total revenue in descending order reveals where the company is thriving and where additional marketing or operational efforts are needed to drive development. Ideal for optimizing regional plans and increasing profits.
SELECT
Sales_Region,
SUM((UnitPrice * OrderQty) - (UnitPrice*UnitPriceDiscount*OrderQty)) AS Total_Sales,
SUM(((UnitPrice - Unit_Cost) * OrderQty) - Unit_Freight_Cost) AS Total_Profit,
SUM(OrderQty) AS Total_Orders
FROM
sales
GROUP BY
Sales_Region
ORDER BY
Total_Sales DESC;
This SQL query computes total sales for each client and groups the results by customer name. It enables organizations to identify their top consumers based on sales volume, which is useful for segmenting high-value customers for tailored marketing or loyalty programs. By rounding the sales values and sorting the results in decreasing order, this query clearly identifies the most significant customers in terms of revenue creation, allowing data-driven strategies to maximise client retention and growth.
SELECT
Customer_Name,
ROUND(SUM((UnitPrice * OrderQty) - (UnitPrice * UnitPriceDiscount * OrderQty)),2) AS Total_Sales
FROM
sales
GROUP BY
Customer_Name
ORDER BY
Total_Sales DESC;
If you wish to see the full MySQL code click HERE.
SQL code used in this data analytics project, which includes advanced techniques for data cleansing, modification, and performance tracking. Discover how SQL may provide valuable insights into sales, consumer behavior, and regional success. This code is critical for stakeholders trying to optimize their data-driven initiatives with precision.
