Online Retail Store Analysis

Tableau Dashboard

About this project

Business Problem:

An online retail store has hired you as a consultant to review its data and provide insights that would be valuable to the CEO and CMO of the business. The business has been performing well and the management wants to analyze what the major contributing factors are to the revenue so they can strategically plan for next year.

The leadership is interested in viewing the metrics from both an operations and marketing perspective. Management also intends to expand the business and is interested in seeking guidance into areas that are performing well so they can keep a clear focus on what’s working. They would also like to view different metrics based on the demographic information that is available in the data.

The CEO and CMO have recently met to finalize the requirements and would like you to provide them with some analysis and visuals that would help answer their questions. Both, executives are interested in viewing and understanding how they can use the data to make more meaningful decisions. You would need to provide insights that they can use to create the expansion strategy. The executives want to analyze the trends and the breakdown by different categories so that they have clarity on how the revenue is being generated and what are the main factors affecting the online store.

Research Question:

We'll need to draft a set of questions that each business leader could ask and want to know the answers to foreseeing the inquiries of both business leaders, anticipating their interests and essential insights.

Questions of interest to the CEO and the CMO:

The CEO of the retail store is interested in viewing the time series of the revenue data for the year 2011 only. He would like to view granular data by looking into revenue for each month. The CEO is interested in viewing the seasonal trends and wants to dig deeper into why these trends occur. This analysis will be helpful for the CEO to forecast for the next year.
The CMO is interested in viewing the top 10 countries which are generating the highest revenue. Additionally, the CMO is also interested in viewing the quantity sold along with the revenue generated.
The CMO of the online retail store wants to view the information on the top 10 customers by revenue. He is interested in a visual that shows the greatest revenue-generating customer at the start and gradually declines to the lower revenue-generating customers. The CMO wants to target the higher revenue-generating customers and ensure that they remain satisfied with their products.
The CEO is looking to gain insights into the demand for their products. He wants to look at all countries and see which regions have the greatest demand for their products. Once the CEO gets an idea of the regions that have high demand, he will initiate an expansion strategy which will allow the company to target these areas and generate more business from these regions. He wants to view the entire data on a single view without the need to scroll or hover over the data points to identify the demand.

Data cleaning Before we can begin the analysis, make sure that the data is cleaned properly. We have noticed that the data contains some returns to the store which are provided in negative quantities and there are unit prices that were input in error. We will need to perform the following steps to clean this data:

Create a check that the quantity should not be below 1 unit
Create a check that the Unit price should not be below $0

Data Preparation A calculated field with the name Revenue has to be created such that: Revenue = (Unit Price * Quantity)

Process:

For the first inquiry, we would need to create a line chart and place the invoice date on the x-axis and the revenue on the y-axis. To calculate the revenue, you would need to multiply Quantity by Unit Price. Once the revenue field is calculated, we can use it to view the trend of revenue.
To attempt the second question, we would need to create a dual-axis chart. There would be a bar and a circle for each country which would represent the revenue and quantity for each region respectively. And, make sure that we add the filter to only show the top 10 countries by revenue.
We would need to create a column chart or a vertical bar chart where each bar would represent the revenue generated by the customers. We would need to add a filter to only display the top 10 customers. The customers will be identified from the field “Customer ID”. Make sure that the customers who do not have any customer IDs are excluded from the visual. Finally, sort the customers in descending order based on the total revenue generated.
We would need to create a map chart here as the map chart would allow the CEO to view the entire map of the world and it will highlight each country and show the total number of units sold or the name of the country. Make sure that the name of the country or the total units sold is visible for each country.

Conclusion:

1. Seasonal Trend Analysis:

• The analysis shows that there are some months of the year where exceptional growth is witnessed. The data shows that the revenue in the first 8 months is fairly constant as the average revenue generated for these 8 months is around $685k.

• The increase in revenue starts in September, where the revenue increases by 40% over the previous month. This trend continued till November when it reached 1.5 million USD, the highest during the entire year.

• The data is incomplete for December, therefore, no conclusion can be drawn from it, unfortunately. This analysis shows that the retail store sales are impacted by the seasonality which usually occurs in the last 4 months of the year.

2. Revenue Generation from Top Countries and Customers:

• This data does not include the UK as the country already has high demand and we’ve been told you’re more focused on the countries where demand can be increased. The analysis shows that countries such as the Netherlands, Ireland, Germany, and France have high volumes of units bought and revenue generated. It is suggested that these countries should be focused on to ensure that measures are taken to capture these markets even more.

• The Netherlands stands out as the highest revenue-generating country, aligning with its high quantity sold.

• A single customer accounts for the highest revenue, emphasizing the importance of retaining and satisfying high-value customers.

• The data shows that there is not much of a difference between the purchases made by the top 10 customers. The highest revenue-generating customer only purchased 17% more than the 2nd highest which shows that the business is not relying only on a few customers to generate the revenue. This shows that the bargaining power of customers is low and the business is in a good position.

3. Regional Demand Insights:

• It can be seen that apart from the UK, countries such as the Netherlands, Ireland, Germany, France, and Australia are generating high revenue and the company should invest more in these areas to increase demand for products. The map also shows that most of the sales are only in the European region with very few in the American region. Africa and Asia do not have any demand for the products, along with Russia. Consider expanding marketing efforts or exploring partnerships to tap into other regions.