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

Jumia, a prominent e-commerce platform, accumulates a vast amount of data daily, encompassing product ratings, prices, discounts, and customer reviews. However, the true value of this data becomes apparent when it is judiciously processed and analyzed to identify trends that can guide strategic decisions. To examine the connection between pricing, discounts, and customer ratings and reviews, I examined a dataset containing 115 raw Jumia product listings.

My objective was to convert this raw dataset into a polished, interactive Excel dashboard, which leaders and managers could leverage to make informed business decisions.

To begin, I initiated the process by cleaning the data and transforming it into a well-structured Excel Table named tblProducts. This approach enabled me to reference columns by name, streamlining the analysis process. Subsequently, I applied three IF-based formulas to identify rows that required closer examination. These formulas evaluated specific criteria, such as discount levels, price discrepancies, and rating quality.

For instance, the discount check formula categorized discounts as low, medium, or high based on predefined thresholds. Similarly, the price check formula highlighted cases where the current price exceeded the initial price, indicating a potential data entry error. Additionally, I integrated a rating-quality tier to distinguish poor, average, and excellent ratings.

To summarize the key performance indicators (KPIs), I employed various Excel functions to calculate metrics such as the maximum value, average, and count of different metrics. For example, the total product count was obtained using the ROWS formula applied to tblProducts[Product], while the average discount and rating were derived using the AVERAGE function on respective columns.

Furthermore, I determined the total review volume by summing the values in the Review column and identified the most and least expensive products using the MAX and MIN functions.

In terms of visualizing the data relationships, I incorporated graphs and calculated Pearson correlations to assess the associations between various table parameters. These visual aids included scatter plots comparing discount versus reviews, rating versus reviews, and price versus rating. Moreover, pivot tables provided a dynamic means of exploring the data by rating mix, discount mix, price vs. rating, and top-10 lists based on rating, discount, and reviews.

Interactive slicers enabled users to filter the data according to rating category, discount category, and price category, enhancing the dashboard's interactivity.

Upon concluding the analysis, I observed several noteworthy insights. Firstly, the discount versus review correlation of approximately -0.15 suggests that substantial discounts do not necessarily lead to an increase in customer reviews, indicating that sellers should not solely rely on markdowns to boost review counts. Secondly, highly rated products tend to attract slightly more customer reviews, revealing a slightly positive correlation.

Lastly, expensive products generally receive higher ratings than cheaper alternatives, reflecting a mild positive correlation.

In summary, this case study illustrates the process of transforming raw product data into a meaningful, interactive Excel dashboard. By meticulously cleaning, analyzing, and visualizing the data, decision-makers can gain valuable insights into pricing, discount strategies, and customer review patterns. These findings can inform data-driven pricing decisions, promotional campaigns, and overall e-commerce strategy, ultimately contributing to enhanced business performance and customer satisfaction.