The Smartphone Sales Analysis.

image from unsplash

I decided to explore a comprehensive dataset from a fictional retail outlet. The dataset from Kaggle contains information about transactions over a fiscal year, covering product types, quantities, prices, payment methods, and more. I used Excel and Power BI to perform data cleaning, analysis, and visualization and discovered some interesting insights into smartphone accessory sales.

In this article, I will share my process and findings. Some of the objectives that I aimed to achieve were:

• Identify the best-performing products and product categories in terms of sales revenue.
  - Analyze the sales trends over time to understand seasonal patterns and peak periods.
  - Understand the distribution of payment types.
• Identify trends in customer payment preferences and adjust payment options to align with customer needs.
  - Identify products that are selling fast and tailor marketing strategies accordingly.

After downloading the dataset from Kaggle ( see dataset), my initial step in preparing the data for analysis involved importing it into Microsoft Excel. The goal was to ensure the dataset was clean, organized, and ready to answer the business questions at hand.

Upon loading the dataset into Excel, my first focus was to comprehend each field and its significance in addressing specific business inquiries. To facilitate the data-cleaning process, I systematically filtered the columns, cross-referencing the column names with their contents. This ensured that columns designated for prices indeed contained relevant numerical data.

I also conducted checks for duplicates, misspelled words, and blank entries. Detecting irregularities in the date columns, I observed that Excel was interpreting some dates as text due to formatting issues. In response, I standardized the date format by replacing hyphens with slashes and utilized Excel’s “Text to Columns” functionality to convert text-formatted dates into the appropriate date format. Additionally, the “DATE” function was employed to further ensure uniformity and accuracy.

Addressing blanks, I encountered a singular blank cell in the “Product No” column. A straightforward resolution involved referencing similar products and populating the blank cell with the appropriate details.

With these meticulous data cleaning steps completed, the dataset emerged refined and well-prepared for subsequent processing and analysis. This attention to detail in data cleaning lays a solid foundation for extracting meaningful insights and drawing informed conclusions from the dataset.

uncleaned data with disorganised date column

For the data processing phase, I transitioned my dataset to Power BI, drawn by its robust visualization capabilities. Utilizing the Transformation tab during the import process, I seamlessly integrated Power Query into my workflow for additional data refinement.

Within Power Query, I initiated further data processing steps to enhance the dataset’s utility. One notable transformation involved the creation of new columns, such as the “Profit” column. This column was strategically engineered as the difference between the “Amount” and “Revenue” columns, the latter being derived from the “Price” and “Quantity” columns. This thoughtful column creation aimed to unlock deeper insights and facilitate a more comprehensive analysis.

Having executed these transformations, I seamlessly closed and applied them within Power BI Desktop. This transition set the stage for an enriched dataset, poised for in-depth analysis and dynamic visualizations.

Upon closing and applying these transformations within Power BI Desktop, the dataset loaded into the platform, marking the initiation of a more enriched and analytically potent dataset. Subsequently, I embarked on creating a pivotal element for time-based analysis — a new table named “Calendar.” Leveraging the calendar auto function, this table became the designated date table for my analysis.

With the foundational elements in place, I established a link between my sales table and the new date table within the data model. This linkage set the stage for a seamless integration of temporal elements into the analysis. The subsequent phase unfolded as I delved into the realms of analysis and visualization, empowered by the combined capabilities of Power BI and the structured dataset.

This strategic approach not only facilitated a seamless transition from data processing to analysis but also laid the groundwork for dynamic and insightful visualizations, enriching the overall data-driven narrative.

After a thorough analysis of the data, several key insights have emerged, paving the way for valuable recommendations as a data analyst:

• The total revenue for all the fiscal years recorded was 119.85 million, the total profit was 117.47 million, the total products sold was 6.42 million, and the total amount (the transaction value) was 123.64 million.
• The fiscal year 2021–2022 had the highest revenue with 44 million, while the fiscal year 2022–2023 had the lowest with 2 million. However, I noticed that some months were missing in some years, which could have affected the results. If this was a real dataset, I would have contacted the company to get more information about this issue.
• The third quarter was the most profitable part of the fiscal years, with a revenue of 46.94 million, while the first quarter was the least profitable, with a revenue of 16.89 million.
• The product type that generated the most revenue was mobile, with 112.13 million, followed by accessory, with 7.72 million.
• The top five best-selling mobile products were midrange phones (39 million), flagship phones (37 million), budget phones (18 million), luxury phone (10 million), and premium midrange phones (9 million).
• The most preferred payment method by the customers was mobile payment, followed by debit, credit, and cash.

Based on these insights, I can give the following recommendations as a data analyst:

- The company should focus more on the mobile product category, as it has the highest demand and profitability. They should also invest more in the midrange and flagship phones, as they are the most popular among the customers.
- The company should also explore the reasons behind the low revenue in the fiscal year 2022–2023 and the first quarter of each year. They should try to identify the factors that affect sales performance and devise strategies to improve them.
- The company should also leverage the mobile payment method, as it is the most convenient and preferred by the customers. They should also offer incentives or discounts for using this method to increase customer loyalty and satisfaction.

Click HERE to interact with the dashboard. Thank you for reading.