Download CV

Sales Analysis

About this project

In this project we are exploring trends in different regions and finding out where our products are selling best worldwide. Using these insights to improve our strategy and make the most of our international sales.

Dataset was obtained from an organization provided to showcase peer’s skills in analytics.

It consists of 185951 rows and 11 columns.

Objectives

The objectives of your sales analysis dashboard with a map visualization can be broken down into two main categories:

1. Gain Insights:

  • Identify Sales Trends: Analyze sales performance across different regions and identify trends or patterns. This could involve spotting areas with significant growth or decline.
  • Pinpoint Top Performers: Quickly recognize the regions or territories contributing the most to your sales. This can help you understand where to focus your resources and efforts.
  • Uncover Regional Differences: Compare sales performance metrics across regions to understand what strategies work best in different locations.

2. Drive Action:

  • Optimize Sales Strategy: Based on the insights gained from the map visualization, you can refine your sales strategy to maximize performance in different regions.
  • Allocate Resources Effectively: The data can guide decisions about resource allocation, ensuring you invest in the regions with the highest potential for growth.
  • Set Targeted Goals: Knowing your top-performing regions allows you to set targeted and achievable sales goals for each area.

Key Metrices and Visualizations:

Profit Margin

Revenue

Sales Quantity

Weekly Sales Distribution

Sales by Month

Top 5 Products by Sales Count

Top 5 Cities by Sale

Top 5 Best Selling Products

ETL (Extract, Transform & Load)

Data Cleaning:

The given dataset id rich in every context though , the larger the dataset larger the chances of its being raw.

After performing ETL , its time to transform this raw data into useful data. A short overview of the data is given in the following ://add a video of the dataset

Order column was in a text data type so to covert it to dat and time stamp, I splitted the column in to 3 columns using delimiter of space and got 3 new copy columns of the original column.

Original Column before Split:

undefined

After Split :

undefined

Oder Date.1 is duplicated twice to extract the exact month and day of the given date. First duplicated column Order Date.1-Copy is named as Month order and the following duplicated column Order Date.1- Copy.1 is named as Day order. Note that it was an important step to convert the Original date column into month and day order to get the desired and exact month number and day number of the following date:

undefined

After renaming the columns and extracting the Month Order and Day Order by Transforming them, the resulted columns will look like an informative columns which shows the month number and the day number of that respective DMY week.

undefined

Since the story of the presenting data doesn’t stop there , and it will not be effective to represent the month and day like this , in integer format it is necessary to show them in text format as there Month name like 12:December and 0: Saturday, in this dataset the day of week starts from Saturday.

These are the steps applied during the ETL process:

undefined

Lets dive deeper into how visualizations transforming the information given in the tabular form into meaningful insights.

1. Weekly Sales Distribution by Weekday:

undefined

When I tried to visualize the weekly sales , Day Order by Sales , it showed that visualize in a non chronological form which depicts no utmost information about the sales we get on a week basis. To overcome this error , a calculated measure is formed using DAX holding the title of “Day Name” , it is created by sorted the Day Order column in a “dddd” format so that we could have the accurate names of days according to their placement , like this:

undefined

Now we can easily see that in which day of the week we have what number of sales and it is showing that Monday have the most sales among the days of the week overall. So it shows another important things while working with date orders , one should keep these steps in order to drive the accurate insight, first to extract day order from the Order Date column and then to show the names of the days make a new calculated column which ill follow the format of Day order column.

2.Sales by Month:

This visual gives the information about the trends of sales over month. I used Line chart to show the information. To visualize this I sorted the Month Column form the Date Hierarchy of the Order Date with Month Order Column , created in the Power Query. Its another important point to note while working with the Date orders.

undefined

The line graph shows a trend of increasing sales over the twelve months. Sales start at around 2 million and increase steadily to nearly 4.6 million by the end of the year. There is a slight dip in sales in June and July, but overall the trend is positive.

3. Top 5 Products by Sales

Tree map is used to represent the contribution of the products in the sales. To represent the top best selling products I used the filter pane and adjust it into advance filter and Top N , the number of products set to 5 with respect to Sales(value), which means its going to show the only top 5 products which made the most of the sales. With the help of the Filter it can easily depict which product have made the most of the sales and can be helpful in the upcoming business plans. It can help the managers to see the level of their products and can decide if that product should be more produced or not.

undefined

The above graph clearly showing that the USB-C Charging Cable is making the most of the sales and Wired Headphones are making the at the 5th in the top 5 with 18.88K sales.

4. Top 5 Cities by Sales:

When there’s a word of city we can easily see that its only going to work with Geographical Maps. To represent the cities which makes the most of the sales, geo map is used and to show the top cities in the world which have the most sales of the products , I used the advance filtering of Top N and N is set to 5 which is going to show the only top 5 cities among the city column. This strategy will also be helpful among the managers , they can easily see which cities are contributing the most in the sales, deciding whether they should continue their market campaign there or not.

undefined

So using this map it can be easily depicted that we have top states contributing in making the most sales are all from the USA .So its a green light for the managers to never stop their campaign in USA.

5. Top 5 Best Selling Products

For businesses it is very important to track their sales and the quantity of the order. If a manager knows what products is being selling the most and what’s its contribution towards the Profit of the business , then it will be beneficial for them to have record of them whether this product should be good fit for their business or not. On the other hand its good to have a knowledge that which product is being getting used the most and in which quantity they have been ordered , the increase in quantity order means that product is in more demand in their market and is selling the most.

To show which products are selling the most in the market, stacked bar chart is being used. Cutting to the chase, advance filter is much in need to figure put which are our top best selling products by value of Quantity Ordered.

undefined

So this makes it easy as pie to understand there are top 5 products are being ordered in much quantity which shows their high demand in the market and their contribution to the Sales (can be seen in “Top 5 Products by Sales” visual. In these AAA Batteries (4-pack) is being ordered the most above 30K times , then AA batteries (4 Pack), USB-C Charging Cable, Lightning Charging Cable and Wired Headphones are all those whose quantities of ordering is being ranges from 20K-30k.

6. Cards:

undefined

6.1: Profit Margin:

Profit margin is a key financial metric used to assess a company’s profitability. It essentially measures the percentage of revenue that remains after accounting for all the costs associated with producing and selling goods or services.

Since there was not any particular field was given which have a calculated Profit Margin earned from the business. To calculate the Profit Margin we need to first calculate the Total Costs and Total Sales business made.

Total Cost measure is generated by the calculating the total sum of ‘Price Each’ of the products from the Sales Data. Same process will be followed for the Measure of Total Sales which will be generated by calculating the total sum of the ‘Sales’ from the Sales Data.

Now we can calculate the Profit Margin with the help of newly generated Measure Column

Profit Margin=( Total Sales – Total Cost/ Total Sales ) * 100

So by using this formula we can create another measure named as Profit Margin and its sum can be shown as the total Profit Margin business made by selling the products.

6.2 Revenue:

The total amount of income a company generates from sales.

The card displays the total revenue generated by summing up all the sales generated by the business is 34.5 Million

6.3 Sales Quantity:

Sales Quantity can be obtained by the sum of the total Quantity Ordered. The card shows that total sales quantity is 209K.

7: Slicers:

undefined

These slicers have been used throughout the report to filter the data , so that beside the top categories of the products or city or month and day , one can easily see the data they want to see by using these slicers.

Conclusion: Unveiling Insights, Empowering Growth

In the dynamic landscape of modern business, data reigns supreme as the compass guiding strategic decisions and fueling sustainable growth. Through meticulous analysis of the sales data, we’ve uncovered a wealth of insights that illuminate the path forward for our organization.

This project successfully draws the meaningful insights with the help of great visuals like

  • Profit Margins serve as the bedrock of our financial health, guiding us towards prudent resource allocation and operational efficiency.
  • Revenue not only signifies our fiscal prowess but also serves as a testament to the effectiveness of our sales strategies and market positioning.
  • Sales Quantity unveils the heartbeat of our product demand, laying the groundwork for informed inventory management and production forecasts.
  • Weekly Sales Distribution uncovers the rhythm of consumer behavior, empowering us to tailor our operations and marketing efforts to sync with market dynamics.
  • Sales by Month reveal the cadence of seasonal trends, offering foresight into future challenges and opportunities.
  • Top 5 Products by Sales Count and Top 5 Cities by Sale illuminate the stars in our constellation, guiding us towards the brightest avenues for growth and expansion.

As we reflect on our analysis, one thing is clear: data guides us to success. With these insights, we’re ready to reach new heights, make informed decisions, and drive growth. In this data-driven era, our journey continues, fueled by curiosity and the limitless potential of our data.

Additional project images

Cities by Sale filter
Best Selling product filter
Products by Sales filter
Profit margin DAX
DAX for Total Cost
DAX for Total Sales