Retail data holds immense potential to drive informed decisions, optimize operations, and accelerate revenue growth. In this project, I leveraged Power Query and Power BI to transform a multi-year dataset (2009–2011) into actionable insights, shedding light on customer behavior, sales trends, and product performance. Here’s how the project unfolded.
Data Preparation and Cleaning
The first step was to organize the dataset, which included two separate queries for 2009–2010 and 2010–2011. Using Power BI's “Append Queries” feature, I combined the data into a unified dataset, setting the foundation for analysis. To ensure data accuracy and relevance, I did the following; • Removed duplicates and excluded invalid entries (e.g., sales with zero or negative prices and quantities). • Cleaned text fields by trimming unnecessary spaces. • Split the invoice dates into separate columns for year, month, and quarter to enable time-based analysis. • Assigned appropriate data types for each column, ensuring seamless integration and visualization. This meticulous cleaning process transformed a cluttered dataset into a reliable source for meaningful insights.
Analyzing Sales Trends
To analyze sales trend with the clean data obtained, I focused on three key dimensions: time, seasonality, and performance over years Using calculated measures, I determined that the total sales for the three years amounted to £17.74 million. Quarterly Trends: The fourth quarter consistently performed the strongest, generating £6.5 million across the three years, with November crowned as the month of retail magic, boasting £2.3 million in sales. Yearly Performance: 2010 emerged as the top performer, contributing £8.7 million in sales. Visualizations, including KPI cards, bar charts, and column charts, were used to clearly illustrate these patterns, making it easier for stakeholders to identify seasonal peaks and trends.
Customer Segmentation and Insights
The next phase focused on the heartbeat of retail. The customers were segmented using the RFM analysis. By analyzing purchase patterns, I identified high-value customers, new customers, repeat buyers, occasional buyers, at risk customers and their geographic distribution. Using a tree map, I highlighted the top 10 customers by sales value. The majority (six) were based in the United Kingdom, which was the dominant market overall.
Customer segmentation using RFM Analysis: RFM (recency, frequency and monetary) analysis showed that 24.4% of customers were repeat buyers (accounting for 6.31% of purchases and $731600 in sales), 75.58% of customers were one-time buyers (accounting for 93.69% of purchases and 17M in sales). The high value customers (Champions, Loyal, promising, can’t lose them and potential loyalists) contributed $14.7M in sales. The seasonal shoppers (Need Attention, About to sleep, Hibernating Customers and At Risk) contributed $2.3M in sales.
Geographic Distribution: The UK led in repeat purchases, with 2,000 repeat customers making over 48,400 purchases. France and Germany also showed potential for growth, despite having fewer repeat buyers. These insights were visualized using pie charts, and tree maps, providing a clear view of customer dynamics.
Top Performing Products
An important part of the project was identifying high-performing and underperforming products using tables and bar charts. This analysis revealed the stars of the retail catalog. • Best-Selling Products: The top three performing products across the three-year period was Regency Cake Stand Tier 3, White Hanging Heart T-Light Holder and paper craft, little birdie. While the Regency Cake Stand Tier 3 generated the highest sales value of £286,486 from 24,914 purchases, the White Hanging Heart T-Light Holder and Paper Craft, Little Birdie sold significantly more units—93,640 and 80,995 respectively—contributing £252,073 and £168,470 in sales. • Underperformers: On the other hand, some products, such as pink heart Christmas decoration and set 12 coloring pencils doily, recorded negligible sales. This insight provided a clear direction for optimizing inventory and focusing on high-performing products.
Mapping Revenue Across Borders
Finally, I turned to the world map, uncovering geographical sales trends to determine market performance and growth opportunities. The United Kingdom dominated, contributing £14.7 million to total sales, followed by EIRE, the Netherlands, Germany, and France. Growth Trends: France stood out as a market with potential, showing consistent growth in both sales value and purchase count between 2010 and 2011. In contrast, the UK and EIRE showed declines, suggesting opportunities for targeted marketing and customer engagement strategies.
Delivering Business Value
This project was more than just an exercise in data analysis—it was a journey into the heart of retail. By cleaning the data, identifying trends, and visualizing insights, I painted a vivid picture of customer behavior, operational inefficiencies, and growth opportunities. With these insights, retail stakeholders could:
- Target repeat buyers with loyalty programs to boost retention and consistent revenue.
- Focus on high-performing products while discontinuing those with minimal demand to optimize inventory and reduce costs.
- Leverage seasonal trends for strategic campaigns, especially during Q4 and November.
- Invest in growth markets like France and Germany, while re-engaging declining markets like the UK and EIRE.
Conclusion
By leveraging Power Query and Power BI, this project turned raw data into actionable insights, enabling data-driven decisions to optimize operations and drive growth. It demonstrated the transformative power of data analytics in unlocking value and identifying opportunities in the competitive retail landscape.