Flipkart Sales Analysis

Overview

For a sales manager, it is not easy to have much visibility of the sales process/ progress to take further decisions on the benefit of the company from a purchase’s dataset.

Therefore, Data Visualization of the result helps the sales manager understand how the purchased data is being interpreted to determine further decisions in terms of sales.

I am provided with a dataset consisting of 20,000 purchases from PromptCloud in a CSV file from which I have to create a Dashboard for quick analysis so that the sales manager could make further decisions on the benefit of the company.

I created an easy-to-understand dashboard from the dataset answering the following questions:

1. Total brands, Total sales and Total revenue

2. What is the progress of the sales by month?

3. How much revenue is generated by the months?

4. Is there any trend associated with the retail price and discounted price over the months?

5. What are some of the target customers that spend the most money on Flipkart?

6. What are the top-performing products and brands?

7. Which brands are offering the highest discount?

8. A thorough analysis of product ratings:

   • What is the proportion of 5 star rated products to the total products?

   • How many numbers of products are sold for different types of ratings?

Tools

• Power BI

• Power Query

Data Preparation

In the data, I have provided a purchases table that consists of columns like uniq_id, timestamp, product name, retail price and discounted price, and more. Many columns contain very precise and detailed information about each product that was ordered.

However, as detailed as the columns are, some columns do not have all the cells filled in with these useful values, so I removed all the blank rows and anomalies from the data. I created two columns, Order date and Order year by parse of timestamp column. I also deleted some columns (product specification, product description, image, product URL) which I don’t need for analysis as per requirement.

Data Modelling

I created one more table “Brand Data” in which one column is brand and another one is unit sold that contains the information of sold units for a particular brand, both columns were created by using the DAX function. I added one more calculated column “Discount percentage” in the main table “Flipkart sales data model”.

I created six measures Total brand, Total Revenue, Total sales, Product with rating, Product- Non-Rated and Product-5 Rated by using the DAX function.

DAX functions for the measures:

• Total Brand

Total Brand = COUNT('Brand Data'[Brand])

• Total Sales

Total Sales = COUNT('Flipkart_Sales_Data-Model'[product_name])

• Total Revenue

Total Revenue = SUM('Flipkart_Sales_Data-Model'[discounted_price])

• Product - Non Rated

Product-Non Rated = CALCULATE(COUNT('Flipkart_Sales_Data-Model'[overall_rating]),FILTER('Flipkart_Sales_Data-Model','Flipkart_Sales_Data-Model'[overall_rating]="No rating available"))

• Product with rating

Product with rating = [Total Sales]-[Product-Non Rated]

• Product- 5 Rated

Product-5 Rated = CALCULATE(COUNT('Flipkart_Sales_Data-Model'[overall_rating]),FILTER('Flipkart_Sales_Data-Model','Flipkart_Sales_Data-Model'[overall_rating]="5"))

DAX functions for the calculated columns:

• Brand

Brand = DISTINCT('Flipkart_Sales_Data-Model'[brand ])

• Unit sold

unit sold = CALCULATE(COUNT('Flipkart_Sales_Data-Model'[brand]),FILTER('Flipkart_Sales_Data-Model','Flipkart_Sales_Data-Model'[brand]= EARLIER('Flipkart_Sales_Data-Model'[brand])))

• Discount Percentage

Discount percentage = ('Flipkart_Sales_Data-Model'[retail_price]-'Flipkart_Sales_Data-Model'[discounted_price])/'Flipkart_Sales_Data-Model'[retail_price]

Results

1. Total sales (product Sold) is 11,169 with 3007 brands and 26.198 M generated revenue in the span of 2 years.

2. Sales by months: We observed the highest sales in December, January and March. Link to viz

3. Revenue By month: Maximum revenue generated in January followed by December and March. Link to viz

4. Trend associated with retail prices and discounted prices (sale price): We observed that in December, January and march the price had spiked up. In other words, the difference between the retail price and the discounted price was big. It means big discounts were in that month. This is also the reason for the maximum revenue generated in that particular month. LInk to viz

5. We found out that the top 10 customers are spending the highest money on Flipkart. The Flipkart accounts associated with these customer IDs are our areas of focus. we can also see that the highest amount of money spend is 120,000 (0.12 M) and the lowest is 60,000 (0.06 M) in the top 10 customers. Link to viz

6. The most popular product category among customers is 'Jewellery', while the most popular brand is 'Allure Auto'. The other top-performing product category and brands can be seen in the donut chart. Link to viz

7. The stacked column chart has returned a very insightful result into the brands offering the highest discounts. Some of these brands are 'FashBlush', 'Bling', 'Soulful Threads ', 'Instella', and more. And the highest discount is 95.27% offered by the FlashBlush brand. Link to viz

8. A thorough analysis of product ratings:

• The Funnel chart has made it very easy for us to visualize the different levels. We observe that out of 11,169 products, only 774 have product ratings and only 287 products have 5-star ratings. 2.6% of total products have 5-star ratings.

• The waterfall chart is the simplest way to visualize data. We observe that 5-star products have the highest count, followed by 4-star, 1 star, 3-star, and 2-star. Link to viz

Conclusion

We have found the answers to all the questions require for sales analysis. One big thing is that provided dataset consists of 20,000 purchases details but after data preparation, we used 11,169 clean data points for analysis. It means 8831 data were invalid or with incomplete details.

According to the results, we saw that December and January are the two months in which major sales occurred and also the highest revenue generated in that month, therefore one possible reason can be the festive seasons 'Christmas' and 'New year' and brands are also offering the highest discounts at that time. This can be the area of further analysis.