Data Analysis Study Case:Insights on Global Superstore’s Sales and Customers [Excel]
In this story I want to make a Data Analysis for Sales and getting Insights on Global Superstore’s Sales and Customers based on public dataset using Ms. Excel. Here I am using Global Super Store Data, Global Super Store is a data set which has around 50000 values and 14 Variables/Columns. Its a customer centric data set , which has the data of all the orders that have been placed through different vendors and markets , starting from the year 2011 till 2015.
Global Superstore Background
Global Superstore is an online retailer of office equipment, office furniture, and office supplies. Global Superstore accepts orders online and ships to anywhere in the world.
Data Dictionary
Category & Sub-Category Abbreviations
Global Superstore Data Preview
Data Additional Explanation
- The unit price is not discounted yet.
- One row is not unique order. One row is one Order ID — Product ID combination.
Guide Questions
- What is the movement of the profits per market of Global Superstore over the last few years and Identify markets that Global Superstore should preserve or prioritize for improvement.
- From the markets targeted for improvement, identify regions or countries that have low profits and what might be done to improve Global Superstore Profits.
- Which products are the most profitable, which are least and what strategy can be employed to maximize Global Superstore profits.
Steps for Data Preparation:
- Count profit column from Total Sales-(Total Sales*Discount)
- Make label is the row has profit (1) or no profit (0)
- Make unique order column from concatenating Order ID — Product ID
- Split location column into : Province, Region and Country
- Split Product ID into : Category, Sub Category and ID Product Number
Graphics and Insights
The movement of the profits per market of Global Superstore over the last few years.
Total profit and total unique order are correlated, and Global Superstore have increasing profit and order year by year, and for the market segments we got the most profit or orders are from Consumer Market segments it has more than 1M profits from +- 26k orders.
Recommendation : Consumer Market Segments become our first target market and for our profit growth it is increasing year by year that means we has a good movements and we should have more stable or increasing growth like this for more years coming.
Regions and Countries profit Analysis
United States has the highest profit by having $523,550.98 for last 4 years, meanwhile Turkey has the lowest profit by having -$62,672.24 ( it has no profit, but loss )
Recommendation : Unites States become our first target market we should do more selling in US because there we got more profits and we should do consider more about our product distribution in Turkey.
- From all 147 Countries in Global Store order receipt history there are 119 Countries has >70% average of profit and 28 countries has <70% average of profit, 8 of them has 0% profit.
- Zimbabwe has negatives profit -$3,960.59 from 190 Total purchase had no profit at all, it means that Zimbabwe has more than 100 Total Order (190 Order Quantity).
- Turkey has the lowest profit (negative profit) -$62,672.24 from 3024 Total purchase and from 3024 Total purchase it has 4% or 58 from 3028 Total purchase get profit.
- Armena and Equatorial Guinea has lowest total Purchase quantity but they still got profit, all purchase made 100% profits and has > $40 profits.
- I made table filtered for any country has negative profit and <50% are not profit and it shows 23 countries but there are different sort between the countries that has sorted negative profit and the average of profit from lowest to highest, ex : — Tajikistan has 0 Purchases got profit and its total profits has -$111.86 that (> -$1,000.00) not in 10 lowest countries has negative profits
- Countries has 0% profits among all the purchase : Tajikistan, United Arab Emirates, Uganda, Turkmenistan, Yemen, Zimbabwe, Kazakhstan, Lithuania and Nigeria.
- 10 sorted Lowest Profits country : Honduras, South Korea, Zimbabwe, Pakistan, Kazakhstan, Sweden, Lithuania, Netherlands, Nigeria, Turkey.
- There are 8 Countries has <10 Purchases quantity are Macedonia, Bahrain, Swaziland, South Sudan, Eritrea, Burundi, Armenia, Equatorial Guinea.
Recommendation :
- It’s a good thing that Global Superstore still has more than 70% Average of profit but there is still a country with a negative or 0% profit and the total purchase not always in line with the total profit. There are a country has a negative profit but has a lot purchase and still have a average profit percentage or a country with a profit has a minimum total order and average percentage of profit.
- For Zimbabwe and Turkey which has a negative profit but those 2 Country has a quite large or total purchase we can consider to change the price or the discount for the products so that we will not have any negative profit but still have a large amount of total purchase.
- For Armena and Equatorial we should do more promotion for our products because from a profit it has a profitable country but has a minimum amount of total purchase.
- We can consider more about pricing in this countries : Tajikistan, United Arab Emirates, Uganda, Turkmenistan, Yemen, Zimbabwe, Kazakhstan, Lithuania, Nigeria, Honduras, South Korea, Pakistan, Sweden, Netherlands, Turkey.
- We should a mass promotion to increase our total purchase in this countries : Macedonia, Bahrain, Swaziland, South Sudan, Eritrea, Burundi, Armenia, Equatorial Guinea.
Product Profit Analysis
The most profitable products is Technology products, followed by Office Supplies and the last is Furniture. But if we see the gap between the sum of profit and the average of profit Furniture has a lower average of profits per total purchase and Office Supplies has more gap between the average and sum of profit.
Recommendation :
- Technology products become our first target market because it has a highest profits and also a good percentage of average profits from all the purchases.
- Followed by Office supplies and also Furniture has a good number also.
- For Furniture it has a lower percentage of profit it means we can consider more about the pricing for Furniture products.
-5 most profitable by sub-products are Copiers, chairs, bookcases, appliances and storage (1 Tech product, 2 Furniture and 2 Office Supplies)
- 5 lowest profit sub-products are Tables, Envelopes, Supplies, Labels and Fasteners (1 Furniture and 4 Office Supplies)
- 5 most profitable (average of profits per total purchase) are Paper, Accessories, labels, phones, art, and envelopes.
- 5 most purchased products are : Binders, Storage, Art and Paper.
- 5 lowest purchased products are : Bookcases, Copiers, Appliances, Machines and Tables.
Recommendation :
- We should consider to sell this products more : Copiers, chairs, bookcases, appliances and storage. And lower the production or consider the pricing for this products : Tables, Envelopes, Supplies, Labels and Fasteners.
- These products has a great calculation of pricing : Paper, Accessories, labels, phones, art, and envelopes.
- Do more promotion for these products : Bookcases, Copiers, Appliances, Machines and Tables.
