A data analytics project using Excel to analyze KPMG’s customer and transaction data. Key tasks include data cleaning, customer segmentation, sales trend analysis, new customer insights, and customer lifetime value (CLV) calculations to uncover trends and valuable insights for business decisions.
Tasks and Features
Task 1: Data Cleaning Objective: Prepare datasets for analysis by identifying and correcting inconsistencies. -Customer Address Data:Remove duplicate records,Ensure consistent state name formatting. -Customer Demographic Data:Correct erroneous data entries (e.g., remove invalid characters),Standardize formats for missing data entries,Address anomalies in gender representation. -Transaction Data:Standardize transaction_date format. -Remove incomplete or missing data records. -New Customer Data:Standardize address formats,Ensure consistent gender representation.
Task 2: Customer Segmentation Objective: Segment customers based on demographic and transaction data to identify key groups. Segmentation by Wealth Segment:Display customer count by wealth_segment,Calculate average tenure per wealth segment. Segmentation by Gender:Show customer count by gender,Calculate average past_3_years_bike_related_purchases for each gender. Segmentation by Job Industry:Display customer count by job_industry_category,Analyze wealth segment distribution within each industry.
Task 3: Transaction Analysis Objective: Analyze transaction data to uncover trends and insights. -Sales Trend Analysis:Generate a monthly total sales chart,Identify seasonal trends or spikes in sales. -Product Performance Analysis:Show total sales for each brand,Calculate total sales and average list_price for each product_line. -Customer Purchase Behavior:Identify the top 10 customers based on transaction value,Calculate average purchase frequency per customer.
Task 4: New Customer Insights Objective: Analyze new customer data for potential behavior and value insights. -New Customer Demographics:Display new customer distribution by wealth_segment and job_industry_category, Calculate average past_3_years_bike_related_purchases for new customers. -New Customer Location Analysis:Map distribution of new customers by state,Analyze correlation between property_valuation and customer wealth_segment. -Potential Revenue from New Customers:Estimate potential revenue based on past bike-related purchases and value.
Task 5: Customer Lifetime Value (CLV) Analysis Objective: Calculate and analyze CLV to highlight valuable customers. -CLV Calculation:Calculate CLV for each customer based on transaction data. -Segment CLV Analysis:Calculate average CLV by wealth_segment. -Analyze the relationship between CLV and customer demographics (e.g., gender, job industry).
Usage -Data Preparation:Begin by cleaning each dataset according to the steps outlined in Task 1. C-ustomer Segmentation:Use demographic and transaction data to classify customer segments (Task 2). -Transaction Analysis:Generate sales and product insights based on transaction records (Task 3). -New Customer Insights:Assess the potential impact and value of new customers (Task 4). -Customer Lifetime Value:Calculate CLV and analyze it by segment to determine valuable customers (Task 5).