Data School Microsoft Excel Capstone Project
Retail Market Analysis and Strategy Optimization
Background
A mid-sized retail company Vista Retail Group aims to increase its market share and improve customer loyalty by better understanding its customers and tailoring its services to meet their needs more effectively.
Objective:
1. Customer Segmentation: Identify different segments within the customer base to target with customized marketing campaigns.
2. Spending Pattern Analysis: Understand the factors that influence customer spending scores.
3. Family Size Consideration: Analyze the impact of family size on spending to plan family-based promotions and events.
Data:
The provided dataset includes customer ID, gender, age, annual income, spending score, profession, work experience, and family size.
Perform the following using the Customer dataset provided.
Data Cleaning and Preparation Tips
1. Identify and Handle Missing Values: How can you identify missing values in the dataset and what strategies would you use to handle them?
2. Duplicate Entries: Are there any duplicate entries in the dataset? If so, how would you identify and deal with them?
3. Data Type Conversion: Analyze the data types of each column. Are there any columns that should be converted to a different data type for better analysis?
Data Analysis Tasks
Task 1: Customer Profiling
● Segmentation by Demographics: Use Excel to categorize customers into different age groups and analyze the average spending score and income within each group.
○ Divide the customers into age groups (e.g., 20-30, 31-40, etc.). Calculate the average spending score for each age group.
■ Click here for hints.
- Analyze annual income and spending scores to classify customers into different market segments (e.g., high income-low spending, low income-high spending).
- Group customers by profession and calculate the average annual income for each profession.
- Utilize Excel's pivot tables to generate insights into the relationship between gender, profession, and spending score.
- Create a new column calculating the ratio of 'Annual Income' to 'Family Size' for each customer.
Task 2: Correlation and Trend Analysis
- Determine if there's a significant correlation between work experience and annual income, as well as between family size and spending score. Click here for hints
- Explore trends and patterns in spending scores across different professions and age groups.
- Calculate the correlation coefficient between 'Spending Score' and 'Annual Income' to determine if there’s a linear relationship.
- Use scatter plots to visualize the correlation and identify any patterns or anomalies.
Task 3: Family Size Impact on Spending
● Create a pivot table to explore the average spending score based on family size.
● Create a chart to visualize spending trends across different family sizes.
Task 4 : Profession-based Insights
● Examine the average income and spending score for each profession.
● Use pivot charts to represent income and spending by profession and interpret these to draw conclusions about professional groups as target markets.
● Identify the top 5 professions with the highest average spending scores.
Deliverables:
1. Insightful Report:
a. A detailed report summarizing the findings from each analytical task, complete with charts and a narrative explaining the insights.
2. Data Visualizations:
a. Create an Excel dashboard provides an interactive overview of key metrics, allowing stakeholders to filter and sort by demographics, income levels, and family sizes.
3. Strategic Recommendations:
a. Based on the analysis, recommend targeted marketing strategies for different customer segments.
4. Presentation:
a. Prepare a PowerPoint presentation summarizing the key findings, visualizations, and strategic recommendations for the retail company's management.
b. Share your dashboard on your social media account and tag techlytics.
Evaluation Criteria:
● Analytical Rigor: Accuracy and thoroughness of the analysis performed.
● Insight Quality: Depth and relevance of the insights generated.
● Actionable Recommendations: Practicality and innovativeness of the marketing strategies proposed.
● Communication: Effectiveness of reporting and presentation in communicating findings and recommendations
Senior lecturer at University. 12+ years tutoring algebra and calculus..