Walmart Sales Performance Evaluation Dashboard
PowerBI Assignment Project
Data analysis & visualizations in PowerBI
I created this dashboard as part of my final study case project for the fourth module of the Refocus Indonesia data analytics course, focusing on PowerBI. The dashboard contains various interactive charts and graphs that display valuable insights derived from key business metrics such as profit margins, gross merchandise value, average order value, and customer behavior, which significantly impact sales performance.
It is important to note that this dashboard is intended for marketing managers to review sales performance history and promptly identify trends and patterns for evaluation. This will enable them to quickly improve and optimize sales strategies in the future. The sales history exploration within the dashboard can be performed by utilizing the available year filter. You can access the dataset used here.
PROCESS
Once the dataset is downloaded, you can simply open it in PowerBI as the file format is .pbix. The dataset consists of 5 tables: DimCustomer, DimDate, DimProduct, Supermarket Data, and tDimDate. The dataset has already been cleaned, so there is no further cleaning or transformation required. You can directly create a relatively simple data model from it.
Create relationships by connecting the fields as follows:
1. OrderDate (Supermarket Data) to Date (tDimDate); many-to-one relationship.
2. CustomerKey (Supermarket Data) to CustomerKey (DimCustomer); many-to-one relationship.
3. ProductKey (Supermarket Data) to ProductKey (DimProduct); many-to-one relationship.
ANALYZE
So, this dashboard focuses on the sales performance history from 2010 (start from July 2010) to 2014 (January & February). First and foremost, I will evaluate the sales performance using the following metrics:
- Gross Merchandise Value (GMV)
- Net Profit Margin
- GMV Year-over-Year%
- GMV Month-over-Month%
The measures that have been created are translated into visualizations & charts as follows:
- The overall GMV metric is $29.36 million.
- The Net Profit Margin ratio to GMV is 33.15%.
- Starting from 2011, the year-over-year sales growth is 0.16%.
- There is a positive sales growth in the month of June, amounting to 33.15%.
Next, I will provide measures related to customer behavior and their contribution to the business metrics:
- Average Order Value (AOV)
"Measure for number of orders (Nr of Orders):"
Nr of Orders = COUNT('Supermarket Data'[SalesOrderNumber])
- Average Order Per User (ARPU)
"Measure for number of customers (Nr of Customers):"
Nr of Customers = DISTINCTCOUNT('Supermarket Data'[CustomerKey])
- Average Customer Lifespan (ACL)
"Measure for Active Member Customer:"
Active Member = DISTINCTCOUNT('Supermarket Data'[CustomerKey])
"Measure for Active Member Customer on the Previous Month:"
Active Member Previous Month = CALCULATE([Active Member], PREVIOUSMONTH(tDimDate[Date]))"Measure for Churn Rate:"
Churn Rate =
VAR BeginningCust = [Active Member Previous Month]
var EndingCust = [Active Member]
var DiffCust = BeginningCust - EndingCust
RETURN
IF(DIVIDE(DiffCust,BeginningCust)<> Blank(), DIVIDE(DiffCust,BeginningCust), 0)
- Average Purchase Frequency (APF)
- Customer Lifetime Value (CLV)
As a note, it should be mentioned that the KPIs derived from various customer-related business metrics vary each year, and they are displayed in the form of cards. Therefore, there is a year filter available to explore the performance history. The display below is only for the year 2011:
The KPI cards provide a concise overview of the performance metrics related to customers for the specific year, allowing for easy monitoring and analysis of their contributions.
To design the layout and template for this dashboard, I used Adobe Illustrator, and the final result is as follows:
INSIGHTS
Overall, after exploring and examining the available business metrics, it can be concluded that the year 2013 had the relatively best performance compared to previous years in terms of sales performance. However, the customer contribution performance was relatively lower compared to previous years. Further analysis and exploration can be conducted using the provided year filter for more in-depth insights and evaluation.
RECOMMENDATIONS
- Investigate the decline in customer contribution: Analyze the factors that led to the relatively lower customer contribution in 2013 compared to previous years. Identify areas where improvements can be made, such as customer retention and increasing average order values. Implement targeted marketing and customer engagement strategies to boost customer loyalty and increase their contribution to the business.
- Conduct a detailed analysis of customer behavior: Utilize the available customer segmentation data to gain a deeper understanding of customer preferences, needs, and shopping behaviors. This information can guide personalized marketing initiatives and help tailor products, offers, and experiences to specific customer segments, ultimately driving higher customer engagement and sales.
- Continuously monitor and evaluate performance: Regularly track and assess key performance indicators (KPIs) related to sales, customer acquisition, retention, and profitability. Set specific targets for each KPI and measure progress over time. This will enable timely identification of trends, patterns, and areas that require improvement, allowing for proactive adjustments to sales and marketing strategies.
Check out the repository here : https://github.com/rionovriandra/Walmart-performance-dashboard
Visit my website : https://rionovriandra.github.io/
BI Portfolio : https://www.novypro.com/profile_projects/rionov
Check out my other blogs : https://medium.com/@riogeorge.n
LinkedIn : https://www.linkedin.com/in/rionovriandra/
