INTRODUCTION

In this data analysis report, I delve into a business’s sales and revenue data over a two-year period (2019 and 2020). The goal is to extract valuable insights to help optimize business strategies, identify growth opportunities, and understand customer behavior.

Problem Statement

My analysis seeks to address key questions pertaining to our business’s performance, customer behavior, and product trends. These questions include:

1. Earnings on New Products in 2020: I begin by identifying and calculating earnings on new products introduced in 2020.
2. Product with the Biggest Increase in 2020: I pinpoint the product with the most significant sales increase in 2020 compared to 2019.
3. ABC Analysis and Product Categorization: I conduct an ABC analysis to categorize products based on their sales contribution.
4. Customer Revenue Growth: I analyze customer revenue growth in 2020, identifying trends in customer behavior.
5. RFM Analysis (Recency, Frequency, Monetary Value): I perform an RFM analysis, segmenting customers based on recency, frequency, and monetary value.
6. Seller’s Income by Month and Seasonality: I analyze the seller’s monthly income to uncover seasonality patterns.

Data Analyst Procedure Report

Data Cleaning and Data Exploration

In this section, I’ll walk you through the process of data cleaning and exploration, solving various problems, and deriving insights from our analysis.

Problem 1: How Much Did the Seller Earn on the New Products in 2020? To address this question, I began by creating a temporary table (#TempData) that combined data from two tables: “business” and “business2.” This consolidated dataset allowed for a unified analysis of the sales data.

-Step 1: I filtered the data for the year 2020 and stored it in another temporary table (#Data2020). — Step 2: Next, I identified new products introduced in 2020 by comparing them with the products sold in 2019. — Step 3: I calculated the earnings for these new products and stored the results in the temporary table #Newproductearning.

Run with Microsoft SQL Server Management Studio

Problem 2: Find the Product with the Biggest Increase in 2020 Compared to 2019?

Solution: To determine the product with the most significant increase in sales between 2019 and 2020, I first calculated the total earnings from new products in 2020. — Step 1: I then summarized the total sales for each product in both years. — Step 2: By calculating the gain (difference) between sales in 2020 and 2019, I identified the product with the highest increase in sales.

Run with Microsoft SQL Server Management Studio

Problem 3: Conduct an ABC Analysis and Calculate the Number of Products in Groups ABC for 2 Years

Step 1: To perform an ABC analysis, I calculated the total sales for each unique product across two years (2019 and 2020). — Step 2: I calculated cumulative sales and percentages to categorize products into A, B, and C based on the Pareto principle. — Step 3: Finally, I determined the number of products in each category (A, B, C) for two years.

Run with Microsoft SQL Server Management Studio

Problem 4: Analyse Customer Revenue Growth in 2020

I analyzed customer revenue growth by categorizing customers based on their total sales in 2019 and 2020. I identified customers with positive, negative, or no change in revenue.

Problem 5: Conduct an RFM (Recency, Frequency, and Monetary Value) Analysis The RFM analysis segment examines the recency, frequency, and monetary value of customers. I categorized customers into segments based on these metrics and provided a summary of customer counts in each segment.

Run with Microsoft SQL Server Management Studio

Problem 6: Check the Seller’s Income by Month and Identify Seasonality

I examined the seller’s income by month to identify any seasonality in the sales data. I categorized monthly income as above average, below average, or average based on the comparison with the average monthly income.

Run with Microsoft SQL Server Management Studio

Recommendations Based on the analysis, I can draw several recommendations to enhance business operations:

1. Focus on promoting and optimizing the sales of new products introduced in 2020 to capitalize on their potential.
2. Pay special attention to the product with the highest increase in sales in 2020, as it may serve as a growth driver.
3. Use the ABC analysis to tailor marketing and inventory management strategies for products in different categories (A, B, C).
4. Develop targeted marketing strategies to boost revenue growth for customers with negative or no change in revenue.
5. Utilize the RFM analysis to create personalized marketing campaigns for different customer segments.
6. Monitor income by month to identify seasonality and plan marketing and inventory strategies accordingly.

Conclusion

The increase in the seller’s income results from various interconnected factors. A combination of popular products, seasonal trends, effective promotions, and aligned market dynamics contributed to this growth. A holistic understanding of these factors equips the business to capitalize on similar opportunities in the future and sustain the upward trajectory of income.