EXPLORATORY DATA ANALYSIS OF GLOBAL SUPERSTORE USING TABLEAU
1. Let’s delve into the story of Global Superstore, the company that brings a unique touch to its narrative
Global Superstore, headquartered in the vibrant city of New York, stands out as a worldwide digital retailer with an extensive array of products. Striving to serve as the ultimate destination for customers, the superstore caters to a diverse clientele from 147 different countries. Customers have the luxury of exploring a vast collection that surpasses 10,000 products. This diverse selection falls into three primary categories: office supplies (think staples), furniture (including comfy chairs), and technology (featuring the latest smartphones).
In your role as a Data Analyst, you have been enlisted to support Global Superstore in unraveling valuable insights from its dataset. These insights will play a crucial role in guiding management towards informed decisions, ultimately enhancing the overall performance and profitability of the superstore
2. Task to Perform
1a. What are the three countries that generated the highest total profit for Global Superstore in 2014?
1b. For each of these three countries, find the three products with the highest total profit. Specifically, what are the products’ names and the total profit for each product?
2. Identify the 3 subcategories with the highest average shipping cost in the United States
3 a). Assess Nigeria’s profitability (i.e., total profit) for 2014. How does it compare to other African countries?
b) What factors might be responsible for Nigeria’s poor performance? You might want to
investigate shipping costs and the average discount as potential root causes.
4a) Identify the product subcategory that is the least profitable in Southeast Asia.
Note: For this question, assume that Southeast Asia comprises Cambodia,
Indonesia, Malaysia, Myanmar (Burma), the Philippines, Singapore, Thailand, and
Vietnam.
b) Is there a specific country i n Southeast Asia where Global Superstore should stop
offering the subcategory identified in 4a?
5a) Which city is the least profitable (in terms of average profit) in the United States? For
this analysis, discard the cities with less than 10 Orders.
b) Why is this city’s average profit so low?
6a) Which product subcategory has the highest average profit in Australia?
7a)Which customer returned items and what segment do they belong
b)Who are the most valuable customers and what do they purchase?
3. Methodology for Sales Dashboard Analysis.
For this project, we will be using Tableau for cleaning, analysis, and Visualization.
3.1 EXTRACTION, TRANSFORM AND LOADING (ETL)
- Extraction includes gathering information from different places like databases, applications, or external systems.
- Transformation involves tidying up, organizing, and enhancing the data we’ve gathered. This includes making sure everything is in a standard format, dealing with any missing information, and combining or sorting the data to make it better and more reliable.
- Loading includes putting the changed data into a designated database or data warehouse. Now, the loaded data is arranged and formatted for easy and effective searching and analysis.
3.1.1 EXTRACTION
Getting the data starts with fetching information from different places like databases, applications, or external systems.
Welup Digital got their hands on the dataset when it was extracted from the Global Superstore corporate database for a specific task. For this project, we utilized a dataset from the Global Superstore company. This dataset includes twenty-four (24) sets of sales records from the Orders table, Return tables, and people tables, respectively.
4 Data Dictionary
- 4.1 Orders Table : This table has twenty-four (24) columns and a multiple unique character (Customer key, orders id and region) that serves as a unique identifier for every customer. allocated to a specific client and orders taken by customers and their region inside a company or system. In a database or customer management system, this unique label — an alphanumeric code or number — serves to distinguish one customer from another.
4.2 Return Table:
This table has three (3) columns: Returned, Order ID, and Market. The “Returned” column indicates whether an item has been returned or not. The “Order ID” is like a unique ID assigned to each item you buy from the store in their system or database. Think of it as a unique code or number that acts like a special label, helping to tell one product category apart from another.
4.3 People Table:
This table is made up of two (2) columns: “Person” and “Region.” The “Region” column can be connected to another table (the orders table) to reveal which customers are most frequently choosing their products. It’s like a way of finding out who really likes what they offer.
5. Data Modelling.
- Data modeling is a systematic method of representing and structuring data to meet specific business requirements. It involves creating and showing abstract representations of the relationships between entities, and attributes within a dataset to ensure efficient organization, retrieval, and management of information.
- Model View: You’ll find this feature on the right side of the screen, and it’s where you can work on modeling your data using the Manage Relationship tools. Managing Relationships is essentially a way for tables in your dataset to communicate with each other, based on shared elements like a unique identifier. This identifier can either be a primary key or a foreign key. For example, the “Order ID” is linked to the return table because it serves as a primary key in that table. Similarly, the “Region” in the people table is connected to the order table, forming a relationship between them.
- Building a relationship between the datasets in the dataset table using their unique identifiers to form a useful analysis for the company.
Data was further visualized in the tableau . The analysis focused on key metrics and KPIs, and delve deeper into the attributes.
These Metrics includes
- Total Discount: The total discount amount metric shows the combined value of all discounts given on every customer’s order. It gives us a glimpse into the overall savings or special deals offered to customers. In the past three years, Global Superstore provided a total discount of $17,000.
- Total Profit : Total profits are essentially what we earn from our business after subtracting all the expenses from our total revenue. It gives a clear picture of how well Global Superstore is doing in the market and how efficiently it’s operating. Over the years, Global Superstore has earned a total profit of $1,467,500.00.
- Total Sales: This is a key performance indicator (KPI) that the sales department at Global Superstore relies on to monitor the overall revenue generated from sales during a specific time frame. It’s also known as the total revenue and serves as a valuable gauge of Global Superstore’s business well-being. The total sales at Global Superstore amount to $12,600,000.00.
- Return Quantity : The quantity is influenced by two primary factors: the refunds issued by Global Superstore to its customers and the demand from customers. Global Superstore had to refund a total of 178,310 items to customers.
- Total shipping Cost: The total shipping cost represents the overall amount charged to the customer for order delivery. This includes both the shipping rate and the expenses associated with handling products from the Global Superstore warehouse to the loading dock. In total, Global Superstore faced a shipping cost of $1,400,000.00.
- Count of order: This refers to the sales that Global Superstore completes with customers within a specific timeframe. During this period, Global Superstore successfully delivered 52,000 items to the majority of its customers.
We also had a look at the demographics and attributes of company’s Sales
1a. What are the three countries that generated the highest total profit for Global Superstore in 2014?
1b. For each of these three countries, find the three products with the highest total profit. Specifically, what are the products’ names and the total profit for each product?
2. Identify the 3 subcategories with the highest average shipping cost in the United States
3 a). Assess Nigeria’s profitability (i.e., total profit) for 2014. How does it compare to other African countries?
b) What factors might be responsible for Nigeria’s poor performance? You might want to
investigate shipping costs and the average discount as potential root causes.
4a) Identify the product subcategory that is the least profitable in Southeast Asia.
Note: For this question, assume that Southeast Asia comprises Cambodia,
Indonesia, Malaysia, Myanmar (Burma), the Philippines, Singapore, Thailand, and
Vietnam.
b) Is there a specific country i n Southeast Asia where Global Superstore should stop
offering the subcategory identified in 4a?
5a) Which city is the least profitable (in terms of average profit) in the United States? For
this analysis, discard the cities with less than 10 Orders.
b) Why is this city’s average profit so low?
6a) Which product subcategory has the highest average profit in Australia?
7a)Which customer returned items and what segment do they belong
b)Who are the most valuable customers and what do they purchase?
6. INSIGHT
6.1 What are the three countries that generated the highest total profit for Global Superstore in 2014?
The top three countries that contributed the most to the total profit of Global Superstore are the United States of America, India, and China. In 2014, the United States led with a profit of $93,508, benefiting from its proximity to the manufacturer. Following closely is India, with a profit of $48,808 in the same year, and then China, with a profit of $46,794 in 2014
6.2 For each of these three countries, find the three products with the highest total profit. Specifically, what are the products’ names and the total profit for each product?:
The United States of America: we are delving deep to understand why it boasts the highest profit and which products contributed most significantly to this achievement on a global scale. One standout product is the Canon image class 2200 advanced copier, which raked in a remarkable profit of $15,680 in the U.S., making it the highest-grossing product. Following closely is the Hewlett Packard LaserJet 3310 copier, contributing a total profit of $3,624, and then the GBC DocuBind TL300 Electric Binding System, adding $1,911 to the cumulative profit. These combined profits contributed to the United States’ impressive total profit of $93,508 in 2014.
India : India secures the position as the second-highest total profit-generating country, with three standout products contributing to a total profit of $48,808. Leading the pack is the Sauder Classic Bookcase, Traditional, which emerged as the highest-grossing product in the country, bringing in a profit of $2,420. Following closely is the Cisco Smart Phone with Caller ID, contributing a total profit of $1,609, and the Hamilton Beach Refrigerator, Red, rounding out the top three products with a total profit of $1,440
China: China holds the position as the third-highest profit-generating country, with a combination of products contributing to the overall top product sales and a total profit of $46,794. The leading product in terms of total profit is the Sauder Classic Bookcase, Metal, bringing in $1,463. Following closely is the Bush Classic Bookcase, Mobile, with a profit of $1,221, and the HP Copy Machine, Color, contributing a profit of $1,196, among others.
6.3 Identify the 3 subcategories with the highest average shipping cost in the United States?
When we examine the money spent on shipping costs across various sub-categories for each shipping mode, we find three (3) sub-categories that stand out with the highest average shipping costs. First and foremost is the Table. Tables incur the highest average shipping cost, totaling $22,314 annually. Following closely is the Bookcase, which trails the table with the second-highest average cost of $11,681. Lastly, Copiers also demand a considerable amount in shipping costs, with the highest average shipping cost of $11,240.
6.4 Assess Nigeria’s profitability (i.e., total profit) for 2014. How does it compare to other African countries?
When I looked into the profitability of Nigeria, it became evident that the country didn’t register any profit in 2014; instead, it showed a negative profit of -$23,286. This is in stark contrast to other African countries like Morocco, which achieved a profit of $8,444, South Africa with a profit of $9,363, and Egypt with a profit of $6,493. This raises a significant question: why did Nigeria experience such a notable decline in performance in 2014?
6.5 What factors might be responsible for Nigeria’s poor performance? You might want to investigate shipping costs and the average discount as potential root causes.
Nigeria’s poor performance can be attributed to three key factors. Firstly, the shipping costs incurred in bringing items to Nigeria are significantly high, influenced by the exchange rate, which impacts the shipping expenses from Global Store headquarters to the various branches in Nigeria. To illustrate, consider the shipment of 77 appliances, incurring a whopping $834 in shipping costs — an exorbitant figure contributing to Nigeria’s struggle with profitability.
Moreover, Nigeria faces challenges in product sales, often resorting to offering substantial discounts to customers. The average discount rate stands at 0.7, which is excessively high, especially when combined with the already elevated shipping costs. This practice further hampers Nigeria’s profitability.
Lastly, the financial burden of maintaining 31 branches, including salaries and upkeep, also plays a role in affecting the overall profitability of Global Superstore’s branches in Nigeria. These three factors collectively contribute to the poor performance observed in Nigeria’s profitability.
6.6 Identify the product subcategory that is the least profitable in Southeast Asia.
Note: For this question, assume that Southeast Asia comprises Cambodia,
Indonesia, Malaysia, Myanmar (Burma), the Philippines, Singapore, Thailand, and
Vietnam.
In the Southeast region, there are three product sub-categories that are experiencing a lack of profitability. This trend extends to several countries, including Cambodia, Indonesia, Malaysia, Myanmar (Burma), the Philippines, Singapore, Thailand, and Vietnam. The specific sub-categories facing profitability challenges in these countries are tables, accessories, and supplies. These sub-categories consistently show a lack of profitability across the mentioned nations.
6.7 Is there a specific country in Southeast Asia where Global Superstore should stop
There’s a particular country in Southeast Asia to which Global Superstore should halt shipping certain product categories. This decision stems from the fact that the mentioned country is generating the least profit in specific product subcategories. For instance, in the tables product subcategory, Indonesia is incurring a loss of -$10,680. Similarly, on supplies, Indonesia is experiencing a loss of -$2,518, and on accessories, there’s a loss of -$4,795. It’s advisable for Global Superstore to cease shipping products to this country, given the lack of profitability in these specific product subcategories.
6.8 Which city is the least profitable (in terms of average profit) in the United States? For this analysis, discard the cities with less than 10 Orders?
Among the cities in the United States, Lancaster stands out as the least profitable. Lancaster records a negative average profit of -$157.4. The primary factors contributing to this low average profit include consistently high average discounts, a low number of items sold to customers, low sales figures, and elevated shipping costs. Unfortunately, these conditions result in Lancaster not generating any profit for Global Superstore.
6.9 Which product subcategory has the highest average profit in Australia?
In Australia, the product subcategory with the most substantial average profit is appliances, boasting an average profit of $139. Following closely is copiers with an average profit of $105, and phones come next with an average profit of $98.
6.10 Which customer returned items and what segment do they belong?
Anna Andreadi had the highest number of returns in the consumer segment, with a total of 127 items. In the corporate segment, she returned 83 items, and in the home office segment, the return count was 56 items.
6.11 Who are the most valuable customers and what do they purchase?
Our most valued customers include Tamara Chand, Hunter Lopez, Fred Hopkins, Tom Ashbrook, Christopher Conant, Natalie Fritzler, Jane Waco, Greg Tran, and Bart Watters. Let’s explore their purchases:
Tamara Chand acquired the Canon ImageClass 2200 Advanced Copier for $17.05k, the Nokia Smart Phone with Caller ID for $1.02k, and the HON Executive Leather Armchair Adjustable for $0.45k.
Hunter Lopez made purchases of the Canon ImageClass 2200 Advanced Copier for $10.50k and the Samsung Smart Phone Cordless for $4.47k.
Fred Hopkins opted for the Apple Smart Phone, Full Size for $5.26k, the Cisco Smart Phone Full Size for $3.47k, the Samsung Smart Phone Cordless for $2.56k, and the HON Executive Leather Armchair, Adjustable for $0.42k, among other items.
7. Recommendation
- Discover creative marketing channels and approaches to connect with undiscovered groups of customers. This might involve engaging in social media campaigns, collaborating with influencers, or organizing experiential events to spark excitement and capture interest.
- Improve how we categorize our customers to precisely address the needs and preferences of those in less successful areas. Customize our product choices, promotions, and communication approaches accordingly.
- If a country is not performing well, Global Superstore should consider exiting that market. Instead, focus on providing items based on customer requests, with customers covering the shipping costs. This approach will minimize the company’s expenditures and contribute to increased revenue.
- Global Superstore should recognize and reward the staff working in the most profitable countries. This initiative will not only motivate the high-performing countries but also provide an opportunity for those in less successful countries to improve and meet their expectations.
- Our cherished customers, who consistently choose our services and return for more due to the excellent service they receive, should be recognized and rewarded at the year’s end. It’s essential to show appreciation through awards or incentives for their loyalty and continued support.
- A thorough review of Indonesia is necessary, and consideration should be given to refraining from shipping product subcategories that exhibit poor performance. This is crucial as they are causing a loss in revenue, negatively impacting the company.
- Engage proactively with customers in regions where performance is lacking. Gathering insights into their concerns and preferences will enable us to address specific issues and enhance the overall customer experience.
