Analysis for e-commerce shop

How to evaluate the current e-commerce performance and predict demand?

The purpose of this analysis was to evaluate the current performance of an e-commerce store, identify any trends, and forecast future sales. The data set, consisting of 523 variables from June to September, was obtained from Kaggle.

To prepare the data, I used MS Excel and Power Query to review and modify column formats and create a table. Since the data was from an open-source platform, I made no exclusions, such as missing values or confusing color labels.

Results of analysis

Using a combination of Pivot Tables and formulas (including INDEX, MODE, MATCH, MIN, COUNTIF, and AVERAGE), I identified the best and worst selling SKU and described them by color. The best-selling SKU was 799 in Dark Blue, while the worst-selling SKU was 439 in Rust. The analysis also revealed an average order value of $278.02.

I also analyzed the sales data by determining which weekday had the highest sales from June to September. To do this, I used a combination of the SUMPRODUCT and WEEKDAY formulas, and then applied conditional formatting. I created a column (column A) that extracted the weekday from the main Date_time column to help with the analysis. 

As a result, Tuesday was found to be the day with the highest sales, with a total of $24,112.00, while Wednesday had the lowest sales, with a total of $15,891.00.

To identify trends in the data, I created a Pivot Table with the rows representing the months and the values being the sum of total sales. The data was then visualized using a line chart and trend line.

As it is clearly seen on the graph, the e-commerce store had a linear trend, with a small increase in August which can be explained by the back-to-school and work situation.

In order to make predictions, I used the TREND formula and calculated the accuracy of the prediction using the ABS formula. To double-check my prediction, I decided to start it from September 30th so that I could observe any deviation.

Afterwards, I visualized the actual results with the predicted ones to see if the trend had changed. As it is clearly seen on the graph, the e-commerce store still had a linear trend with a minor spike in December, which can be explained by the upcoming holidays.

Recommendations

 

  1. Stock with best-selling SKU and consider expanding the portfolio:
  • Identify the complementary products that can be offered along with the best-selling SKU, based on customer preferences and buying patterns.
  • Conduct market research to assess the demand for the complementary products and their potential profitability.
  • Determine the optimal pricing strategy for the complementary products based on the market research and competitive analysis.
  • Test the new products in the market to gauge customer response before investing heavily in inventory.
  1. Observe how quantity of sold SKU varies from month to month to identify pattern and set the correct MOQ:
  • Analyze the historical sales data to identify the patterns in the quantity of SKU sold in different months.
  • Set the minimum order quantity (MOQ) for each SKU based on the historical sales data and projected demand for each month.
  • Re-evaluate the MOQ on a regular basis to ensure that it is aligned with the changing customer demand and market conditions.
  • Use inventory management software to track the sales and inventory levels to ensure that the MOQ is being met and inventory is being replenished on time.