Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

List of Contents

1. Introduction
2. Prerequisites
3. Data Cleaning and Transformation
4. Data Analysis
5. Data presentation and dashboard development
6. Findings and recommendations
7. Conclusion

Introduction

Excel is widely known for its ability to organize data and perform numerous calculations using its extensive range of formulas and functions. However, it can also be used to extract meaningful analytical insights from data, helping drive informed decisions within an organization. This article provides a step-by-step guide on carrying out product analysis using Excel. By the end of this article, you will have developed an interactive dashboard and extracted meaningful findings from the provided dataset.

prerequisites

1. Install Excel
2. Basic Excel knowledge
3. Download the raw CSV file from GitHub: raw_excel_jumia_dataset.csv

Data Cleaning and Transformation

Before analysis, the data must be cleaned and transformed to align with the format and business requirements. The most commonly used tool for cleaning and transformation is Power Query. The following are the steps that we will carry out to ensure that our dataset is clean and ready for analysis:
1. Creating an Excel table
2. Removing duplicate and missing values
3. Handling numerical value formats
4. Enriching the dataset

1. Creating an Excel table

2. Removing duplicate and missing values

Inside the Power Query window, navigate to Home > Remove Rows > Remove Duplicates to delete repeating rows. Initially, our data contained 115 rows, but after deleting the duplicates, we are left with 109 rows. Therefore, 6 duplicate rows have automatically been identified and deleted. This change is then recorded, as with all other changes that we will perform later on, in the Applied Steps panel on the right-hand side of the window.

For our data, we are going to replace blanks with 0 for the reviews column only. To do so, select the reviews column, then navigate to Home > Transform > Replace values. A pop-up window will appear where you will provide the value to be replaced and the one replacing it. In our case, null(which indicates blanks in Power Query) and 0, respectively.

3. Handling numerical value formats

The next step is cleaning the numerical values in our dataset. This includes removing currency prefixes in our price columns, handling ranges, removing the 'out of 5' statement in the ratings column, and removing negative values in the reviews column.

To remove currency prefixes, we will use the Replace Values feature. After selecting the price columns and clicking on the Replace Values feature, fill the "value to find" field with 'KSh ', and the "Replace field" should be left blank.

Similarly, we can remove the "out of 5" statement in the ratings column to remain with the numeric ratings. Change the data type of the column to decimal after modifying.

The number of reviews should be absolute values (0 and above). Given that our reviews column contains some negative values, we will need to make some modifications. This can be done by selecting the column and then clicking Transform > Number Column > Scientific > Absolute Value. This will convert all the negative values to positive.

Range can be handled in different ways depending on your analytical needs and their effect on your analysis. In our case, we will split the price columns into separate maximum and minimum price columns, then create an average column that will serve as the representative price for our analysis. Since the range appears in only a few columns, averaging the prices will have a limited impact on our analysis.

To create a minimum current price column, we will use the Add Column > Column from examples > From Selection feature. Once the "Add Column From Examples" window opens, select the current price column and navigate to the row containing the range. In the new column, insert the first value from the range (before the delimiter), then press Enter. The corresponding values for the other rows will be auto-filled. Click OK and rename the column accordingly (Minimum current price).

For the maximum current price column, repeat the same steps as above. However, since the values extracted are after the delimiter, values in the new column that correspond to rows without ranges will be blank. To ensure that the column retains the values in current price for rows that do not have price ranges, we will create a conditional column to capture this logic. We will use the Add Column > Conditional column feature. After creating the conditional column, delete the column with the blank maximum prices.

To create the average current price column, navigate to Add Column > Custom Column and insert the following formula:

([Minimum current price] + [Maximum current price]) / 2

Repeat the above steps to handle range values in the old price column.
The following represents how the columns should look:

4. Enriching the dataset

For our analysis, we are going to add 4 categorical columns:

• Rating (Poor below 3, Average from 3 to 4, Good from 4 to 4.5, Excellent above 4.5)
• Discount (Low below 20%, Medium 20% to 40%, High above 40%)
• Price (Affordable 1000 to 3000, mid range above 3000, budget below 1000)
• Engagement (low below 10, moderate 10 to 30, high above 30).

To create these columns, we will use the Add column > General > conditional column feature. For each column, we will be able to provide the relevant conditions and the expected values.

After completing all the transformations, click the Home > Close and load icon to apply the changes to the table.

Data Analysis

Our table is now clean and ready for analysis. For our analysis, we will carry out the following tasks:

• Obtain some general descriptive statistics like averages, maximums, minimums, and totals.
• Find the top 10 products based on discount, ratings, and reviews.
• Find the bottom 5 products based on discount, ratings, and reviews.
• Correlation analysis investigating the relationships between reviews and discount, reviews and ratings, and ratings and reviews.

Excel's pivot tables make it easy for analysts to obtain various statistics from a dataset. To create a pivot table from the transformed table, navigate to Insert > Pivot table, choose the table range, select a new worksheet to create the table on a new worksheet, then click OK. A new worksheet will open containing a pivot table.

Descriptive statistics

Here we will extract some general statistics from our dataset: Total products, Average prices, average ratings, average discount, and max and min prices.
To be able to obtain these statistics from the pivot table, drag and drop the relevant columns to the Values field in the pivot table fields list (located on the right-hand side of the worksheet). You can also change to use rows rather than columns to present your data, or change the automatically generated names for your values.

To filter the top-10 products, create another pivot table from the clean table and insert it into a different part of the same worksheet as the previous pivot table. Drag and drop the filtering column (discount, ratings, or reviews) into the Values field, place the products column in the Rows field, and then filter the products using the top-10 option. Use different pivot tables for each filtering column.

The same approach can be used to filter the bottom 5 products.

Correlation Analysis

This involves creating scatter plots in order to determine the relationship between variables within the dataset. Since pivot tables do not support the creation of scatter plots, a helper table referencing a pivot table will be required.
For our analysis, we will check the correlation between reviews and discount, reviews and ratings, and ratings and reviews.
The first step is creating a pivot table that contains the columns that will be used in the analysis. By using the XLOOKUP function or directly referencing the pivot table cells, create a helper table that will be used for analysis.

Data Presentation and Dashboard Development

After carrying out the analysis, the next step is creating a dashboard. The dashboard will include KPIs, a product performance section, trends, and slicers.
To create a KPI, we will use a combination of the rounded rectangular shape and text box, which can be found in Insert> Illustrations> Shapes

For our dashboard, we will include the following KPIs: Total products, average price, average discount, average rating, and average reviews.

The Product performance section will include the top 10 products by discount, ratings, and reviews. Rather than just providing a list we will use the clustered bar chart. Select the data to represent and then navigate to insert > charts > bar.

The trends section includes the scatter plots we created during analysis.

Slicers are interactive filter buttons that filter data from your pivot tables. To create a slicer, navigate to your worksheet containing pivot tables, select one of the tables, then go to Insert> Filter > Slicer and a slicer configuration window will pop up. Select the columns that you would want to use to filter your dashboard(in our case, ratings category, reviews category, price category, and engagements) and click OK.

To make sure that the slicers can be used on all the pivot tables, right-click the slicer created and click on Report connections, which will open up a list of pivot tables. Select all the pivot tables that are associated with your analysis, then click OK.

All these sections put together form a comprehensive interactive dashboard

Findings and Recommendations

After cleaning, analyzing, and developing the dashboard, the following are some findings and recommendations that can be derived:

Findings

1. Reviews have a very weak positive relationship with discount (r=0.03). Consequently, a very tiny fraction of discounts affects reviews (R² = 0.00069).
2. Ratings have a weak positive relationship with pricing (r=0.06). A small fraction of pricing affects reviews (R²= 0.0032).
3. Some of the highly discounted products have poor ratings.
4. Most of the highly discounted products have very low engagement.
5. Products have both high engagement and excellent ratings.

Recommendations

1. Discounts should not be used as the primary engagement strategy.
2. Pricing should be slightly considered alongside other factors to improve customer satisfaction.
3. The pricing strategies for products with high discounts but poor ratings should be reviewed, and other factors, such as product quality, should be taken into consideration when trying to improve customer satisfaction.
4. Avoid further discount increases and consider other strategies to improve the popularity of products with high discounts and low engagement.
5. Investigate what makes products with high engagement and excellent ratings popular with customers and apply the findings to other related products

Conclusion

In addition to Excel being a remarkable tool when it comes to working with spreadsheets and data organization in general, it can be leveraged to carry out end-to-end analytical projects on large datasets. By using Power Query in Excel, analysts can clean and transform data to suit their needs; pivot tables serve as useful tools when it comes to analyzing table data, and the myriad of charts offered in Excel, plus the different shapes and slicers, are used to create comprehensive interactive dashboards without the need to adopt third-party analytical tools.