README.md

KPMG-Data-Analysis-using-Excel

Cleaned and standardized customer, transaction, and demographic data, ensuring consistency and accuracy for analysis. I conducted segmentation by wealth, gender, and industry, identifying key customer groups and behavioral trends. I analyzed sales trends, product performance, and customer purchase patterns, providing actionable insights into revenue drivers. Additionally, I estimated potential revenue and calculated customer lifetime value (CLV) to highlight high-value customer segments.

TITLE:
KPMG Data Analysis using Excel KPMG-dataset
PROJECT OVERVIEW:
This project aims to analyze customer demographics, transactions, and new customer data to provide insights into business performance and customer behavior. The project consists of six tasks that involve data cleaning, data analysis, and visualization using Excel.

FINDINGS:

Task 1: Data Cleaning
Objective: Prepare the datasets for analysis by cleaning and correcting any inconsistencies.

1. Customer Address Data:
   i. Remove any duplicate records.
  ii. Ensure all state names are correctly formatted.
2. Customer Demographic Data:
   i. Identify and correct any erroneous data entries (e.g., invalid characters in default).
  ii. Standardize the format for missing data entries.
 iii. Correct any anomalies in gender representation.
3. Transaction Data:
   i. Ensure that transaction_date is in a consistent date format.
  ii. Remove any records with missing or incomplete information.
4. New Customer Data:
   i. Standardize address formatting.
  ii. Ensure consistent gender representation.
 iii. Correct any anomalies in job_title and job_industry_category.

Task 2: Customer Segmentation
Objective: Segment customers based on demographic and transaction data to identify key customer groups.

1. Segmentation by Wealth Segment:
   i. Show the number of customers in each wealth_segment.
  ii. Calculate the average tenure for each wealth_segment.
2. Segmentation by Gender:
   i. Showing the number of customers by gender.
  ii. Calculate the average past_3_years_bike_related_purchases for each gender.
3. Segmentation by Job Industry:
   i. Showing the number of customers in each job_industry_category.
  ii. Analyze the distribution of wealth_segment within each industry.

Task 3: Transaction Analysis
Objective: Analyze transaction data to identify trends and patterns.

1. Sales Trend Analysis:
   i. Create a chart showing the total sales per month.
  ii. Identify any seasonal trends or significant spikes in sales.
2. Product Performance Analysis:
   i Show the total sales for each brand.
  ii. Calculate the total sales and average list_price for each product_line.
3. Customer Purchase Behavior:
   i. Identify the top 10 customers based on total transaction value.
  ii. Calculate the average number of purchases per customer.

Task 4: New Customer Insights
Objective: Analyze the new customer dataset to provide insights into potential new customer behavior and value.

1. New Customer Demographics:
   i. Show the distribution of new customers by wealth_segment and job_industry_category.
  ii. Calculate the average past_3_years_bike_related_purchases for new customers.
2. New Customer Location Analysis:
   i. Create a map or chart showing the distribution of new customers by state.
  ii. Analyze the correlation between property_valuation and customer wealth_segment.
3. Potential Revenue from New Customers:
   i.Estimate potential revenue based on past_3_years_bike_related_purchases and value.

Task 5: Customer Lifetime Value (CLV) Analysis
Objective: Calculate and analyze the customer lifetime value to identify the most valuable customers.

1. CLV Calculation:
   i. Use the formula
  ii. Calculate CLV for each customer using transaction data.

📦 CLV Formula Breakdown

Formula
CLV = (Average Purchase Value × Purchase Frequency) × Customer Lifespan

---

Average Purchase Value (APV):

• This is the average amount of money a customer spends in a single purchase.
• Formula: APV = Total Revenue / Number of Purchases
  • Total Revenue is the sum of all revenues generated from all purchases. You can get the total revenue from transactional data.
  • Number of Purchases is the total count of all transactions of that customer.

---

Purchase Frequency (PF):

• This is the average number of times a customer makes a purchase in a given period.
• Formula: PF = Total Number of Transactions / Number of Unique Customers

---

Customer Lifespan (CL):

• This represents the average number of years a customer remains active.
• In your dataset, this is represented by the tenure column in the Customer Demographic dataset.

2. Segment CLV Analysis:
   i. Show average CLV by wealth_segment.
  ii. Analyze the relationship between CLV and customer demographics (e.g., gender, job industry).

Task 6: Executive Summary and Recommendations
Objective: Summarize findings and provide actionable recommendations for business strategies.

1. Summary of Key Insights:
   i. Highlight key findings from customer segmentation, transaction analysis, new customer insights, and CLV analysis.
2. Recommendations:
   i. Provide recommendations for marketing strategies targeting high-value customer segments.
  ii. Suggest potential areas for business expansion based on new customer location analysis.
 iii. Recommend improvements in product offerings based on transaction analysis.