EDA of Black Friday Sales

This exploratory data analysis covers analysis of a single day sales data for a fictitious e-Commerce company.

Executive Summary

This report aims to present key findings based on XYZ company's sales data for a single day Black Friday Sale. Through this analysis, we can find insights about product sales performance, our user demographics and behavior, and how we can optimize for the next sales campaign using the insights gained from this event. The site-wide Black Friday sales campaign earned XYZ company a total of $50.98M in Sales in three different regions. The single day sale successfully sold 6M products online, with an average spend of $92.68 per user. The top-selling category is Small Appliances, which comprises 37.48% of the total sales. Based on the data, the spike in sales in Small Appliances can be correlated to young professionals buying Small Appliances and who just moved to a new geographical region (+/-1 year of being a resident). The data shows young professionals spent more, with the 26-35 and 36-45 age groups being the highest spenders during the sale. Men spent more compared to women, with 76.72% of men participating in the Black Friday Sale, versus 23.28% of women.

Problem Statement

XYZ Company wants to understand their customer base and their buying patterns. With no experience in conducting a Black Friday Sale, XYZ company ran their first Black Friday Sales without adjusting the current inventory and minimal marketing campaign.

Analysis Objectives

This business analysis intends to answer questions including:

What product category sold the most?

Which product category had the least sales?

Which user demographic spent the most during the sale?

What other insights can we get from the Black Friday Sale?

How can we augment our sales for the next sale campaign based on this event?

Key Performance Indicators

The KPIs for this analysis are the total sales for the day, the number of units of product sold, the count of items in listing purchased, and average spend per user.

Apart from KPIs, we are also looking at total sales per product category, max and median spending of users, and user demographics. The factors we are looking at for our user demographics include gender, age, years of residency in the region, and occupation.

While the data available for this particular study is only for a single day of sales, these metrics can then be compared to historical sales data when no sales promotion or sales campaigns are being conducted.

Methodology

Data Source

The data source for this analysis is a .csv file from XYZ's sales database. A star schema is created to identify certain categorical data.

Star Schema to identify categorical data in the sales data (train_v2).

Data Preparation and Cleaning

The data was cleaned and checked for inconsistencies including capitalization, spacing, spelling, and data types. Inconsistent data were cleaned and incomplete data removed. The data was cleaned and transformed using Power BI's Power Query to check for data type, column quality, column distribution, and column profile for a broad check up of the data quality. SQL was not necessary in this case as the .csv file is in a local storage and not stored in a cloud or networked server.

The data available had a noticeable uneven binning for age groups that could not be re-categorized. The raw data did not specify actual users' ages, and instead labeled ages only within the given ranges (e.g. 18-25, 46-50, 55+, etc.). For the sake of clarification, it is worth iterating that the bins for age group can not be adjusted or reclassified due to the raw data entries.

Analytical Approach

Using Power BI's DAX calculations, values were aggregated. The following formulas were used:

Average Spend per User = CALCULATE(SUM([Purchase_Total])/COUNT(train_v2[User_ID]))

Most product ordered = CALCULATE(DISTINCTCOUNT('train_v2'[Product_ID]))

Key Findings & Insights

Overview

High-level analysis of sales for all product categories.

Segmented Analysis by Product Category

Arts & Crafts

Automotive Category

Baby Category

Computers & Digital Services

Food & Pantry Category

Health Supplements Category

Home and Kitchen Category

Industrial Supplies Category

Kid's Clothing

Men's Fashion

Personal Care

Pet Supplies

Small Appliances

Sports & Outdoors Category

Tools & Home Improvement

Toys & Games

Travel Category

Video Games Category

Women's Fashion

Download the exported PDF version of the PowerBI visualizations here.

Analysis and Interpretation

What product category sold the most?

The Small Appliances category sold the most with an impressive $19.11M total sales, which is 37.48% of the total sales for XYZ company. While the Small Appliances has the most sales, users spent the most on Pet Supplies, averaging $196.79 spend per user. This is impressive for the category considering that there are only 25 products listed for the category.

Which product category had the least sales?

The Men's Fashion Category severely underperformed with a total sales of

$593.78 total sales. This is despite having sold 17k units of product. The average user spend is below $1.

Which user demographic spent the most during the sale?

Men spent more than women across all product categories, with 76.72% of overall sales coming from the male users. The product categories where more than 40% of buyers were women are in Tools & Home Improvement (42.79%), Travel (40.88%) respectively. Almost 90% of buyers in the Video Game category were men.

The 26-35 age group outspent all age groups across all categories. This is followed by the 18-25 and 36-45 age groups, both closely ties in spending across almost all categories.

Residents of Metro Manila outspend all other regions across all product categories. This remains true regardless of the number of years of residence of the users in the region.

What other insights can we get from the Black Friday Sale?

Several interesting insights can be gained from the data.

In the data, we can see that both men and women are spending more on their pets and hobbies than kid's clothing or baby products. The Pet Supplies category has the highest average spend per user at $196.79 with $1M in total sales, followed by Digital Goods & Services with an average of $163.70 and $609k in total sales, and Arts & Crafts with an average of $158.43 spend per user and $3.24M in total sales. These figures overshadow spending on Baby products with $9.54k total sales and average of $3.74 spend per user and Kid's clothing at $1.45M in sales and $147.70 average spend per user.

It is also worth noting that both men and women hardly shopped for clothes during the Black Friday Sale, as the average spend for both Men's Fashion and Women's Fashion are $0.37 and $29.77 respectively. This insight also rings true for the total spending for both categories, as Women's Fashion only had $93k in total sales, and $593 total sales for Men's Fashion-- the laggard across all categories.

Gen Z (13-28 year olds) and Millennials (29-44 year olds) spend the most out of all the age groups. In our segmentation, these two age demographics fall under the 18-25 years, 26-35 years, and 36-45 years old groups. These three groups have consistently spent the most among all other age groups in all product categories. Video Games and Women's Fashion have seen a notable spike in spending for the 51-55 year olds age group.

People who have only lived in their region for a year typically spent more during the sale. Further study could be done to understand if there is a correlation between the years of residence in the city and the high average spending per user in Home & Kitchen, Small Appliances, and Tools & Home Improvement categories.

Food products bring in stable sales. Being the second top selling category, Food products brought in $9.42M in total sales. With 2M units of products sold and second largest total amount in sales, we can infer that majority of the users bought food product items during the sales promotion.

Automotive product sales totaled $8.55M, the third category with the most sales and third with the most quantity of units sold. It is the product category with the largest inventory.

How can we augment our sales for the next sale campaign based on this event?

Improving inventory

We can rebalance the number of product offerings we have in our inventory. For example, Automotive products have more than 1,000 listed products, but only ranked third in sales. Industrial Supplies have 254 listed products but only brought in $1.1M in sales. Whereas Pet Supplies only have 25 products listed yet brought in $1M in sales. Further analysis is needed which products sell the most that the company can re-stock more, add a variety of, or totally phase out for poor sales performance. We also see this in the Home & Kitchen category where there are only 2 products listed but brought in $63k in sales, with the 4th highest average spend per user. Expanding the inventory and variety of products in these categories could improve sales in the next campaign.

Bundling of products

We can move more products and increase sales by bundling together items that are often bought together. This also means a faster churn rate in the inventory and increase profits to be invested in other aspects of the business.

Cater to the most active user demographics

From our analysis, we see Gen-Z and Millennials as the highest spenders and most active users of the e-Commerce platform. Further analysis should be studied about their buying behavior, interests, and what products they are likely to buy. Marketing campaigns could be tailored to reflect their buying habits and interests.

Conclusion

XYZ's Black Friday sales totaled $50.98M, with the Small Appliances category contributing to 37.48% of the sales. Age groups 18-25, 26-35, and 36-45 are the top three segments who bought the most during the sale. On average, the male buyers spent more than female buyers across all product categories. Metro Manila residents are also the biggest spenders during the sale out of all three regions.

Several recommendations are given to improve sales for the next campaign. These include improving the product inventory, product bundling and catering to the most active user demographics.