Explore customer demographics, purchasing behaviour, product preferences and marketing campaign performance. Build an interactive dashboard that turns customer data into actionable business insights.
Understand the business problem before beginning your analysis.
A retail business wants to understand its customers and evaluate the effectiveness of its marketing campaigns. The company has collected customer demographic information, product spending, purchase channel activity and campaign response data.
Your task is to analyse this dataset and develop a dashboard that helps management identify valuable customer segments, understand product preferences, compare purchasing channels and evaluate campaign effectiveness.
Download the source file and load it into your preferred analytics tool.
Birth year, education, marital status, income and household composition.
Product spending, purchase frequency and activity across sales channels.
Previous campaign acceptance, latest campaign response and complaints.
Understand the meaning of each field before building calculations.
| Field | Description | Category |
|---|---|---|
ID | Unique customer identifier. | Customer |
Year_Birth | Customer's year of birth. | Demographics |
Education | Customer's education level. | Demographics |
Marital_Status | Customer's marital status. | Demographics |
Income | Customer's annual income. The source column contains surrounding spaces in its header. | Demographics |
Kidhome | Number of children in the household. | Household |
Teenhome | Number of teenagers in the household. | Household |
Dt_Customer | Date when the customer enrolled or joined the business. | Customer |
Recency | Number of days since the customer's last purchase. | Engagement |
MntWines | Customer spending on wine products. | Product Spending |
MntFruits | Customer spending on fruit products. | Product Spending |
MntMeatProducts | Customer spending on meat products. | Product Spending |
MntFishProducts | Customer spending on fish products. | Product Spending |
MntSweetProducts | Customer spending on sweet products. | Product Spending |
MntGoldProds | Customer spending on gold products. | Product Spending |
NumDealsPurchases | Number of purchases made using a discount or deal. | Purchases |
NumWebPurchases | Number of purchases made through the website. | Purchases |
NumCatalogPurchases | Number of purchases made through the catalogue. | Purchases |
NumStorePurchases | Number of purchases made in physical stores. | Purchases |
NumWebVisitsMonth | Number of website visits during the month. | Engagement |
AcceptedCmp1 | Whether the customer accepted campaign 1. | Campaigns |
AcceptedCmp2 | Whether the customer accepted campaign 2. | Campaigns |
AcceptedCmp3 | Whether the customer accepted campaign 3. | Campaigns |
AcceptedCmp4 | Whether the customer accepted campaign 4. | Campaigns |
AcceptedCmp5 | Whether the customer accepted campaign 5. | Campaigns |
Response | Whether the customer responded to the latest campaign. | Campaigns |
Complain | Whether the customer registered a complaint. | Customer Service |
Country | Country code associated with the customer. | Geography |
Income with spaces. Trim column names during import so that
it becomes Income. Remove currency symbols and commas from
income values and convert the field to a numeric data type.
Create the following KPI cards to summarise business performance.
Count of unique customer IDs.
ID.Sum of spending across all six product categories.
Mnt fields.Average total product spending per customer.
Average annual income of customers.
Percentage of customers who responded to the latest campaign.
Total acceptances across the five previous campaigns.
Average number of days since the last purchase.
Recency.Total recorded purchases across the four purchase fields.
Percentage of customers who registered a complaint.
Create the following charts to identify patterns and explain customer behaviour.
Show the number of customers in each education category.
Compare total spending across the six product categories.
Compare the number of customers who accepted each previous campaign.
Show how purchases are distributed across deals, web, catalogue and physical stores.
Investigate whether customers with higher income tend to spend more.
Visualise the geographic distribution of customers.
Compare campaign responders and non-responders across marital status groups.
Show how recently customers made their last purchase.
Create these calculated fields to support your analysis.
Calculate the total amount spent by each customer across all product categories.
[MntWines] +
[MntFruits] +
[MntMeatProducts] +
[MntFishProducts] +
[MntSweetProducts] +
[MntGoldProds]Calculate the total number of purchases made by each customer.
[NumDealsPurchases] +
[NumWebPurchases] +
[NumCatalogPurchases] +
[NumStorePurchases]Calculate how many of the five previous campaigns each customer accepted.
[AcceptedCmp1] +
[AcceptedCmp2] +
[AcceptedCmp3] +
[AcceptedCmp4] +
[AcceptedCmp5]Convert the response indicator into readable categories.
IF [Response] = 1 THEN "Responded"
ELSE "Did Not Respond"
ENDGo beyond the basic charts and investigate deeper business questions.
Divide customers into Low, Medium and High spending groups using their Total Customer Spending.
Group customers into income bands and compare the latest campaign response rate for each group.
Analyse the relationship between monthly website visits and the number of web purchases.
Use Kidhome and Teenhome to calculate household children and teenagers, then compare spending patterns.
Check these issues before interpreting your results.
Combine your KPIs and visualisations into one well-structured dashboard.
Use this structure as a starting point for your final dashboard.
Your project will be evaluated on the following areas.
| Evaluation Area | What We Expect | Weight |
|---|---|---|
| Data Cleaning | Correct data types, missing values and data quality checks. | 15% |
| KPI Calculations | Accurate calculations and meaningful business metrics. | 20% |
| Visualisations | Appropriate chart selection, labels and formatting. | 25% |
| Dashboard Design | Layout, readability, consistency and interactivity. | 20% |
| Business Insights | Relevant observations supported by the data. | 20% |
Use this checklist to track your progress as you complete the assignment.
Your checklist progress is saved in this browser on this device.
© 2021 edSlash. All Rights Reserved.