Developed Comprehensive Sales Dashboard Using Power BI and Machine Learning

Introduction

In today’s data-driven business environment, the use of data analysis and visualisation tools is critical to gaining insights and making informed decisions. To better understand sales patterns and improve profitability for my telecommunications store ‘Gareng Cell’, I developed a comprehensive sales dashboard using Power BI. My store specialises in selling a variety of products, including loans, data packages, mobile phone accessories, lamps, electrical appliances and more. This project involved analysing sales data from December 2023 to May 2024, which was sourced from the POS application ‘BukuWarung’.

The primary goal of this dashboard is to identify trends and patterns in sales, determine the most profitable products, and uncover the peak months and days for sales performance. By doing so, the dashboard aims to provide actionable insights that can help drive strategic business decisions and boost overall profitability.

The project began with the challenge of transforming and cleaning raw data obtained in PDF format. Using Power Query, I converted the data to Excel, addressing issues such as missing product labels through the application of machine learning techniques, specifically a Random Forest classifier in Python. Once the data was cleaned and preprocessed, I designed and developed an interactive and user-friendly dashboard in Power BI.

Key features of the dashboard include:

• Key Performance Indicators (KPIs): Metrics such as profit, sales, profit percentage and transaction volume to track overall performance.
• Top 3 Brands and Categories: Visualizations highlighting the leading brands and product categories.
• Trend Analysis: Graphs and charts to observe sales trends over time.
• Detailed Views: In-depth analysis of individual brands, categories, and products.
• Interactive Slicer and Filters: Options to switch between different metrics (profit, sales, transactions) and apply filters for months, categories, and brands.

This dashboard not only provides a clear and comprehensive view of sales data, but also serves as a valuable tool for making data-driven decisions to improve the store’s profitability. Through this project, I demonstrated my ability to integrate data transformation, machine learning, and visualisation techniques to create a powerful analytical tool for my telecommunications store.

Background

Gareng Cell is a telecommunications store that offers a wide range of products including credit, data packages, mobile phone accessories, lamps, electrical appliances, and more. To gain deeper insights into our sales performance and drive business growth, I embarked on a project to create a sales dashboard using Power BI. This dashboard aims to analyze sales data from December 2023 to May 2024.

The data for this project was sourced from our POS application, BukuWarung, which provided the data in PDF format. Transforming and cleaning this data was the first major step. Using Power Query, I converted the PDF data into Excel format, addressing issues such as inconsistent data entries and missing product labels.

One significant challenge was the missing product labels for some days. To resolve this, I utilized machine learning techniques, specifically a Random Forest classifier implemented in Python, to predict and classify the missing labels accurately. This ensured that our data was complete and reliable for further analysis.

With the cleaned and transformed data, I proceeded to develop an interactive and user-friendly dashboard in Power BI. This dashboard not only visualizes key performance metrics such as profit, sales, and transaction volumes but also provides detailed insights into top-performing brands and categories, sales trends, and individual product performance. Interactive features like metric slicers and filter menus enhance the usability of the dashboard, allowing users to explore the data from various perspectives.

Overall, this project integrates data transformation, machine learning, and advanced data visualization to create a powerful tool for understanding sales patterns and improving the profitability of Gareng Cell.

Objectives

The primary objectives of this project were to develop a comprehensive and insightful sales dashboard for Gareng Cell using Power BI. The specific goals were:

1. Analyze Sales Patterns:
   - Identify trends and patterns in sales data from December 2023 to May 2024.
   - Determine peak sales periods, including the highest profit months and days.
2. Improve Profitability:
   - Understand which products, brands, and categories contribute most to the store’s profit.
   - Provide actionable insights to help make informed business decisions aimed at boosting profitability.
3. Enhance Data Quality:
   - Transform and clean raw data sourced from the BukuWarung POS application.
   - Address missing product labels through machine learning techniques to ensure data accuracy and reliability.
4. Create User-Friendly Dashboards:
   - Develop an interactive and intuitive dashboard in Power BI that visualizes key performance indicators (KPIs) such as profit, sales, and transaction volume.
   - Include features like metric slicers and filter menus to allow users to explore the data from various perspectives, including by month, category, and brand.
5. Provide Detailed Insights:
   - Offer detailed views of top-performing brands and categories, overall sales trends, and individual product performance.
   - Enable users to drill down into the data for more granular analysis and better understanding of sales dynamics.

By achieving these objectives, the project aims to equip Gareng Cell with a powerful tool for data-driven decision-making and strategic planning, ultimately driving business growth and enhancing profitability.

Data Preparation and Cleaning

Transforming and cleaning the raw sales data obtained from the BukuWarung POS application was a crucial step in ensuring the accuracy and reliability of our analysis. The process involved several key tasks:

1. Data Extraction and Format Conversion:
The sales data was initially provided in PDF format, which required conversion to a more manageable format for analysis. Using Power Query in Excel, I extracted the data from the PDF files and converted it into Excel format. You can preview the raw data here.

2. Handling Missing Values:
Upon inspecting the data, I identified instances where certain fields, such as product labels, were missing. To address this, I implemented strategies to handle missing values, ensuring that the dataset was complete and consistent.

Transform and Cleaning Data Using Power Query in Excel

3. Standardizing Data Formats:
Inconsistent data formats across different entries posed a challenge during the cleaning process. I standardized data formats such as dates, product names, and transaction amounts to ensure uniformity and facilitate accurate analysis.

Preview data after PDF to Excel transformation

4. Addressing Missing Product Labels:
One significant issue encountered was missing product labels for certain entries. To overcome this challenge, I utilized Python programming language in Google Colab to implement a Random Forest classifier. I provided a full tutorial for predicting product labels using this classifier in this Google Colab notebook. This involved training the classifier on existing data with known labels and using it to predict labels for entries with missing information.

Preview My Google Colab Notebook

5. Creating New Tables for Product and Brand with Image URLs:
In addition to cleaning the sales data, I also created new tables to store product and brand information along with their respective image URLs. This allowed for enhanced visualization capabilities in the Power BI dashboard, enriching the user experience.

Preview The Final Clean Data

6. Quality Assurance and Validation:
Throughout the data preparation and cleaning process, I implemented rigorous quality assurance measures to validate the accuracy and integrity of the data. This involved cross-referencing data entries, conducting spot checks, and verifying against known benchmarks to ensure data quality.

By meticulously preparing and cleaning the sales data, we ensured that the subsequent analysis and visualization in the Power BI dashboard would be based on reliable and trustworthy information. This process laid the foundation for deriving meaningful insights and making informed business decisions to drive profitability for Gareng Cell.

Dashboard Development

Creating an effective and user-friendly dashboard in Power BI was a critical aspect of this project. The dashboard aimed to provide comprehensive insights into sales performance and facilitate data-driven decision-making. Here’s an overview of the key steps involved in dashboard development:

Gareng Cell Dashboard

1. Design Planning:
Before diving into dashboard creation, I carefully planned the design layout and structure. This involved determining which key performance indicators (KPIs), visualizations, and interactive features would be included to best meet the project objectives.

2. Data Model Creation:
With the cleaned and preprocessed data, I created a robust data model in Power BI. This involved importing the cleaned sales data along with the newly created tables for product and brand information with image URLs. I established relationships between the tables to enable seamless data integration and analysis.

3. Visualization Design:
I designed a variety of visualizations to represent key sales metrics and trends effectively. These visualizations included:

• Key Performance Indicators (KPI): Visual representations of important metrics such as profit, sales, profit percentage, product and transaction volume.
• Top Brands and Categories: Card visual with brand images and bar charts showcasing the top-performing brands and product categories.
• Trend Analysis: Column charts illustrating sales trends over time, such as monthly sales trends.
• Detailed Views: Tables or matrices providing detailed information on individual products, brands, or categories.
• Product Images: Integration of product images using the image URLs stored in the data model, enhancing the visual appeal of the dashboard.

4. Interactive Features:
To enhance user interaction and exploration of the data, I incorporated interactive features such as:

• Metric Slicers: Options to switch between different metrics (e.g., profit, sales, transactions) dynamically.
• Filter Menus: Dropdown menus or slicers allowing users to filter data by month, category, brand, and other relevant dimensions.
• Drill-Throughs: Capability to drill down into specific data points for more detailed analysis.
• Bookmarks: Utilized bookmarks to show information about the visual, allowing users to save specific views or states of the dashboard for easy reference or comparison.

By following these steps, I successfully developed a comprehensive and visually engaging dashboard in Power BI that provides valuable insights into sales performance for Gareng Cell. The dashboard serves as a powerful tool for decision-makers to understand sales trends, identify opportunities for growth, and drive strategic business decisions.

Implementation

I will provide a step-by-step guide on creating the dashboard in Power BI, including importing the cleaned data and designing the visualizations. Screenshots will illustrate key steps, and I will highlight the interactive features that allow users to switch between metrics and apply filters.

1. Import Cleaned Data:

• Open Power BI Desktop and click on “Get Data” to import the cleaned data from Excel.

Import The Clean Data

• Select the Excel file containing the cleaned sales data and load it into Power BI.

Load Data

2. Create Data Model:

• In the “Data” view, establish relationships between tables (e.g., sales data, product information) based on common fields.
• Ensure that the data model is well-structured and optimized for analysis.

Data Model

3. Design Visualizations:

• Navigate to the “Report” view to start designing visualizations for the dashboard.
• Choose appropriate visualizations such as bar charts, line charts, and tables to represent key metrics and trends.
• Include visualizations for KPIs, top brands, categories, and trend analysis.

Design Dashboard

4. Add Interactive Features:

• Incorporate metric slicers to allow users to switch between different metrics dynamically (e.g., profit, sales, transactions).
• Include filter menus or slicers for users to filter data by month, category, brand, and other relevant dimensions.
• Implement drill-throughs to enable users to explore specific data points in more detail.
• Implement bookmarks to show or hide visuals and make it more interactive and have detailed information.

Bookmark Information and Filters Menu

By following these steps and leveraging Power BI’s capabilities, you can create a comprehensive and interactive sales dashboard that provides valuable insights to drive business decisions effectively.

Analysis and Insights

The analysis of the sales data from Gareng Cell, a telecommunications store selling credit, data packages, mobile phone accessories, lamps, electrical appliances, and more, provided several valuable insights that can inform strategic business decisions and help improve profitability.

Key Metrics:

• Total Sales: Rp69,478,428
• Total Profit: Rp9,134,490
• Profit Percentage: 13.15%
• Total Transactions: 2513
• Total Profit: 223

Monthly Performance:

• Highest Sales and Profit Month: April 2024
• Profit for April 2024: Rp1,846,935

Weekly Performance:

• Highest Weekly Performance: Second week of each month typically showed the highest daily transactions and profit.

Top Brands by Profit:

1. Indosat
2. XL
3. Freefire

Top Products by Profit:

1. 5 GB XL Unlimited
2. 1 GB Indosat (3 Hari)
3. 1 GB Unlimited Indosat

Top Categories by Profit:

1. Kouta (Data Packages)
2. Voucher Games
3. E-Wallet

Daily Profit Trends:

From the detailed daily profit data, specific days with notable profits were identified:
- December 2023: High profits on the 7th (Rp82,610), 11th (Rp70,238), 23rd (Rp72,288).
- January 2024: High profits on the 2nd (Rp73,001), 13th (Rp79,051), 19th (Rp73,973).
- February 2024: High profits on the 2nd (Rp72,394), 9th (Rp69,517), 22nd (Rp78,557).
- March 2024: High profits on the 11th (Rp84,704), 30th (Rp78,355), 31st (Rp59,161).
- April 2024: High profits on the 9th (Rp130,921), 10th (Rp129,945), 15th (Rp87,728).
- May 2024: High profits on the 11th (Rp80,929), 25th (Rp82,850), 31st (Rp77,412).

Interpretation of Insights:

1. Peak Sales and Profits: Identifying April 2024 as the peak month for both sales and profit provides an opportunity to analyze the factors contributing to this success, such as promotions, market conditions, or new product launches.
2. Weekly Trends: The consistent high performance during the second week of each month suggests potential patterns in customer buying behavior, possibly linked to mid-month salary cycles or specific marketing campaigns.
3. Top Performing Brands and Products: Focusing on top brands like Indosat, XL, and Freefire, as well as popular products, can help in optimizing inventory and marketing strategies to boost sales further.
4. Category Insights: The preference for data packages, game vouchers, and e-wallet services reflects a trend towards digital consumption, indicating where to focus future sales efforts and promotions.
5. Daily Trends: Analyzing daily profit trends helps in understanding customer purchase behaviors on specific days, which can be leveraged for targeted marketing and promotions.

These insights, derived from the Power BI dashboard, are crucial for making informed decisions that can lead to better business performance and profitability. The detailed analysis and interactive features of the dashboard provide a comprehensive understanding of sales patterns and customer preferences, enabling strategic adjustments and optimizations.

Conclusion

This project demonstrated the power of data analysis and visualization in understanding sales patterns and improving business outcomes for Gareng Cell, a telecommunications store. By transforming and cleaning sales data from PDF to Excel, predicting missing product labels using a Random Forest classifier in Python, and developing a comprehensive Power BI dashboard, we were able to derive valuable insights into the store’s performance.

Key takeaways include identifying April 2024 as the highest month for sales and profit, recognizing the second week of each month as a peak period for daily transactions and profit, and highlighting top-performing brands, products, and categories. These insights can guide strategic business decisions and help enhance profitability.

The interactive features of the Power BI dashboard, such as metric slicers, filter menus, and drill-through capabilities, enabled a detailed and dynamic exploration of the data, offering a powerful tool for ongoing business analysis.

Future enhancements will focus on refining the dashboard’s functionality and insights, ensuring it continues to provide actionable intelligence to drive business growth and efficiency.