Developed Comprehensive Sales Dashboard Using Power BI and Machine Learning | by Gustiyan Islahuzaman | Jun, 2024

In at present’s data-driven enterprise atmosphere, the usage of knowledge evaluation and visualisation instruments is essential to gaining insights and making knowledgeable selections. To raised perceive gross sales patterns and enhance profitability for my telecommunications retailer ‘Gareng Cell’, I developed a complete gross sales dashboard utilizing Energy BI. My retailer specialises in promoting a wide range of merchandise, together with loans, knowledge packages, cell phone equipment, lamps, electrical home equipment and extra. This challenge concerned analysing gross sales knowledge from December 2023 to Could 2024, which was sourced from the POS utility ‘BukuWarung’.

The first purpose of this dashboard is to establish traits and patterns in gross sales, decide probably the most worthwhile merchandise, and uncover the height months and days for gross sales efficiency. By doing so, the dashboard goals to supply actionable insights that may assist drive strategic enterprise selections and increase general profitability.

The challenge started with the problem of reworking and cleansing uncooked knowledge obtained in PDF format. Utilizing Energy Question, I transformed the info to Excel, addressing points reminiscent of lacking product labels via the applying of machine studying methods, particularly a Random Forest classifier in Python. As soon as the info was cleaned and preprocessed, I designed and developed an interactive and user-friendly dashboard in Energy BI.

Key options of the dashboard embrace:

• Key Efficiency Indicators (KPIs): Metrics reminiscent of revenue, gross sales, revenue share and transaction quantity to trace general efficiency.
• Prime 3 Manufacturers and Classes: Visualizations highlighting the main manufacturers and product classes.
• Development Evaluation: Graphs and charts to watch gross sales traits over time.
• Detailed Views: In-depth evaluation of particular person manufacturers, classes, and merchandise.
• Interactive Slicer and Filters: Choices to modify between completely different metrics (revenue, gross sales, transactions) and apply filters for months, classes, and types.

This dashboard not solely gives a transparent and complete view of gross sales knowledge, but additionally serves as a invaluable software for making data-driven selections to enhance the shop’s profitability. Via this challenge, I demonstrated my potential to combine knowledge transformation, machine studying, and visualisation methods to create a strong analytical software for my telecommunications retailer.

Gareng Cell is a telecommunications retailer that gives a variety of merchandise together with credit score, knowledge packages, cell phone equipment, lamps, electrical home equipment, and extra. To achieve deeper insights into our gross sales efficiency and drive enterprise development, I launched into a challenge to create a gross sales dashboard utilizing Energy BI. This dashboard goals to investigate gross sales knowledge from December 2023 to Could 2024.

The information for this challenge was sourced from our POS utility, BukuWarung, which supplied the info in PDF format. Remodeling and cleansing this knowledge was the primary main step. Utilizing Energy Question, I transformed the PDF knowledge into Excel format, addressing points reminiscent of inconsistent knowledge entries and lacking product labels.

One vital problem was the lacking product labels for some days. To resolve this, I utilized machine studying methods, particularly a Random Forest classifier applied in Python, to foretell and classify the lacking labels precisely. This ensured that our knowledge was full and dependable for additional evaluation.

With the cleaned and remodeled knowledge, I proceeded to develop an interactive and user-friendly dashboard in Energy BI. This dashboard not solely visualizes key efficiency metrics reminiscent of revenue, gross sales, and transaction volumes but additionally gives detailed insights into top-performing manufacturers and classes, gross sales traits, and particular person product efficiency. Interactive options like metric slicers and filter menus improve the usability of the dashboard, permitting customers to discover the info from varied views.

General, this challenge integrates knowledge transformation, machine studying, and superior knowledge visualization to create a strong software for understanding gross sales patterns and bettering the profitability of Gareng Cell.

The first aims of this challenge have been to develop a complete and insightful gross sales dashboard for Gareng Cell utilizing Energy BI. The precise targets have been:

1. Analyze Gross sales Patterns:
   – Determine traits and patterns in gross sales knowledge from December 2023 to Could 2024.
   – Decide peak gross sales durations, together with the best revenue months and days.
2. Enhance Profitability:
   – Perceive which merchandise, manufacturers, and classes contribute most to the shop’s revenue.
   – Present actionable insights to assist make knowledgeable enterprise selections geared toward boosting profitability.
3. Improve Information High quality:
   – Rework and clear uncooked knowledge sourced from the BukuWarung POS utility.
   – Deal with lacking product labels via machine studying methods to make sure knowledge accuracy and reliability.
4. Create Consumer-Pleasant Dashboards:
   – Develop an interactive and intuitive dashboard in Energy BI that visualizes key efficiency indicators (KPIs) reminiscent of revenue, gross sales, and transaction quantity.
   – Embody options like metric slicers and filter menus to permit customers to discover the info from varied views, together with by month, class, and model.
5. Present Detailed Insights:
   – Supply detailed views of top-performing manufacturers and classes, general gross sales traits, and particular person product efficiency.
   – Allow customers to drill down into the info for extra granular evaluation and higher understanding of gross sales dynamics.

By attaining these aims, the challenge goals to equip Gareng Cell with a strong software for data-driven decision-making and strategic planning, in the end driving enterprise development and enhancing profitability.

Remodeling and cleansing the uncooked gross sales knowledge obtained from the BukuWarung POS utility was an important step in guaranteeing the accuracy and reliability of our evaluation. The method concerned a number of key duties:

1. Information Extraction and Format Conversion:
The gross sales knowledge was initially supplied in PDF format, which required conversion to a extra manageable format for evaluation. Utilizing Energy Question in Excel, I extracted the info from the PDF information and transformed it into Excel format. You may preview the uncooked knowledge here.

2. Dealing with Lacking Values:
Upon inspecting the info, I recognized situations the place sure fields, reminiscent of product labels, have been lacking. To handle this, I applied methods to deal with lacking values, guaranteeing that the dataset was full and constant.

3. Standardizing Information Codecs:
Inconsistent knowledge codecs throughout completely different entries posed a problem in the course of the cleansing course of. I standardized knowledge codecs reminiscent of dates, product names, and transaction quantities to make sure uniformity and facilitate correct evaluation.

4. Addressing Lacking Product Labels:
One vital difficulty encountered was lacking product labels for sure entries. To beat this problem, I utilized Python programming language in Google Colab to implement a Random Forest classifier. I supplied a full tutorial for predicting product labels utilizing this classifier in this Google Colab notebook. This concerned coaching the classifier on current knowledge with recognized labels and utilizing it to foretell labels for entries with lacking data.

5. Creating New Tables for Product and Model with Picture URLs:
Along with cleansing the gross sales knowledge, I additionally created new tables to retailer product and model data together with their respective picture URLs. This allowed for enhanced visualization capabilities within the Energy BI dashboard, enriching the person expertise.

6. High quality Assurance and Validation:
All through the info preparation and cleansing course of, I applied rigorous high quality assurance measures to validate the accuracy and integrity of the info. This concerned cross-referencing knowledge entries, conducting spot checks, and verifying towards recognized benchmarks to make sure knowledge high quality.

By meticulously getting ready and cleansing the gross sales knowledge, we ensured that the following evaluation and visualization within the Energy BI dashboard could be primarily based on dependable and reliable data. This course of laid the inspiration for deriving significant insights and making knowledgeable enterprise selections to drive profitability for Gareng Cell.

Creating an efficient and user-friendly dashboard in Energy BI was a essential facet of this challenge. The dashboard aimed to supply complete insights into gross sales efficiency and facilitate data-driven decision-making. Right here’s an outline of the important thing steps concerned in dashboard growth:

1. Design Planning:
Earlier than diving into dashboard creation, I fastidiously deliberate the design format and construction. This concerned figuring out which key efficiency indicators (KPIs), visualizations, and interactive options could be included to finest meet the challenge aims.

2. Information Mannequin Creation:
With the cleaned and preprocessed knowledge, I created a strong knowledge mannequin in Energy BI. This concerned importing the cleaned gross sales knowledge together with the newly created tables for product and model data with picture URLs. I established relationships between the tables to allow seamless knowledge integration and evaluation.

3. Visualization Design:
I designed a wide range of visualizations to characterize key gross sales metrics and traits successfully. These visualizations included:

• Key Efficiency Indicators (KPI): Visible representations of vital metrics reminiscent of revenue, gross sales, revenue share, product and transaction quantity.
• Prime Manufacturers and Classes: Card visible with model pictures and bar charts showcasing the top-performing manufacturers and product classes.
• Development Evaluation: Column charts illustrating gross sales traits over time, reminiscent of month-to-month gross sales traits.
• Detailed Views: Tables or matrices offering detailed data on particular person merchandise, manufacturers, or classes.
• Product Photos: Integration of product pictures utilizing the picture URLs saved within the knowledge mannequin, enhancing the visible enchantment of the dashboard.

4. Interactive Options:
To reinforce person interplay and exploration of the info, I included interactive options reminiscent of:

• Metric Slicers: Choices to modify between completely different metrics (e.g., revenue, gross sales, transactions) dynamically.
• Filter Menus: Dropdown menus or slicers permitting customers to filter knowledge by month, class, model, and different related dimensions.
• Drill-Throughs: Functionality to drill down into particular knowledge factors for extra detailed evaluation.
• Bookmarks: Utilized bookmarks to point out details about the visible, permitting customers to avoid wasting particular views or states of the dashboard for simple reference or comparability.

By following these steps, I efficiently developed a complete and visually partaking dashboard in Energy BI that gives invaluable insights into gross sales efficiency for Gareng Cell. The dashboard serves as a strong software for decision-makers to grasp gross sales traits, establish alternatives for development, and drive strategic enterprise selections.

I’ll present a step-by-step information on creating the dashboard in Energy BI, together with importing the cleaned knowledge and designing the visualizations. Screenshots will illustrate key steps, and I’ll spotlight the interactive options that permit customers to modify between metrics and apply filters.

1. Import Cleaned Information:

• Open Energy BI Desktop and click on on “Get Information” to import the cleaned knowledge from Excel.

• Choose the Excel file containing the cleaned gross sales knowledge and cargo it into Energy BI.

2. Create Information Mannequin:

• Within the “Information” view, set up relationships between tables (e.g., gross sales knowledge, product data) primarily based on frequent fields.
• Be certain that the info mannequin is well-structured and optimized for evaluation.

3. Design Visualizations:

• Navigate to the “Report” view to begin designing visualizations for the dashboard.
• Select applicable visualizations reminiscent of bar charts, line charts, and tables to characterize key metrics and traits.
• Embody visualizations for KPIs, prime manufacturers, classes, and development evaluation.

4. Add Interactive Options:

• Incorporate metric slicers to permit customers to modify between completely different metrics dynamically (e.g., revenue, gross sales, transactions).
• Embody filter menus or slicers for customers to filter knowledge by month, class, model, and different related dimensions.
• Implement drill-throughs to allow customers to discover particular knowledge factors in additional element.
• Implement bookmarks to point out or cover visuals and make it extra interactive and have detailed data.

By following these steps and leveraging Energy BI’s capabilities, you may create a complete and interactive gross sales dashboard that gives invaluable insights to drive enterprise selections successfully.

The evaluation of the gross sales knowledge from Gareng Cell, a telecommunications retailer promoting credit score, knowledge packages, cell phone equipment, lamps, electrical home equipment, and extra, supplied a number of invaluable insights that may inform strategic enterprise selections and assist enhance profitability.

Key Metrics:

• Complete Gross sales: Rp69,478,428
• Complete Revenue: Rp9,134,490
• Revenue Proportion: 13.15%
• Complete Transactions: 2513
• Complete Revenue: 223

Month-to-month Efficiency:

• Highest Gross sales and Revenue Month: April 2024
• Revenue for April 2024: Rp1,846,935

Weekly Efficiency:

• Highest Weekly Efficiency: Second week of every month sometimes confirmed the best each day transactions and revenue.

Prime Manufacturers by Revenue:

1. Indosat
2. XL
3. Freefire

Prime Merchandise by Revenue:

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

Prime Classes by Revenue:

1. Kouta (Information Packages)
2. Voucher Video games
3. E-Pockets

Each day Revenue Tendencies:

From the detailed each day revenue knowledge, particular days with notable earnings have been recognized:
– December 2023: Excessive earnings on the seventh (Rp82,610), eleventh (Rp70,238), twenty third (Rp72,288).
– January 2024: Excessive earnings on the 2nd (Rp73,001), thirteenth (Rp79,051), nineteenth (Rp73,973).
– February 2024: Excessive earnings on the 2nd (Rp72,394), ninth (Rp69,517), twenty second (Rp78,557).
– March 2024: Excessive earnings on the eleventh (Rp84,704), thirtieth (Rp78,355), thirty first (Rp59,161).
– April 2024: Excessive earnings on the ninth (Rp130,921), tenth (Rp129,945), fifteenth (Rp87,728).
– Could 2024: Excessive earnings on the eleventh (Rp80,929), twenty fifth (Rp82,850), thirty first (Rp77,412).

1. Peak Gross sales and Earnings: Figuring out April 2024 as the height month for each gross sales and revenue gives a chance to investigate the components contributing to this success, reminiscent of promotions, market circumstances, or new product launches.
2. Weekly Tendencies: The constant excessive efficiency in the course of the second week of every month suggests potential patterns in buyer shopping for habits, presumably linked to mid-month wage cycles or particular advertising campaigns.
3. Prime Performing Manufacturers and Merchandise: Specializing in prime manufacturers like Indosat, XL, and Freefire, in addition to standard merchandise, might help in optimizing stock and advertising methods to spice up gross sales additional.
4. Class Insights: The desire for knowledge packages, sport vouchers, and e-wallet providers displays a development in the direction of digital consumption, indicating the place to focus future gross sales efforts and promotions.
5. Each day Tendencies: Analyzing each day revenue traits helps in understanding buyer buy behaviors on particular days, which might be leveraged for focused advertising and promotions.

These insights, derived from the Energy BI dashboard, are essential for making knowledgeable selections that may result in higher enterprise efficiency and profitability. The detailed evaluation and interactive options of the dashboard present a complete understanding of gross sales patterns and buyer preferences, enabling strategic changes and optimizations.