Photo by author
After assessing the data, I found that several issues:
1. There is a space in front of the income’s column name
2. There are dollar signs is the values of Income column
3. The “Income” column has 23 missing values
4. Income’s type is string
5. Dt_Customer’s type is string
Since data cleaning is not the main part of this project, let’s move forward to the next step. (You can find the codes on cleaning these issues here)
In this dataset’s Kaggle page, there are some EDA directions that the data publisher suggested following, and I decided to choose the following three questions to explore:
Are there any outliers? How will you wrangle/handle them?Are there any useful variables that you can engineer with the given data?Do you notice any patterns or anomalies in the data? Can you plot them?
Now let’s look at the questions one by one.
1. Are there any outliers? How will you wrangle/handle them?
I used the boxplots to visualize all the numerical features, and it will show the 5 numbers of the data: the lowest number that is not an outlier, Q1(25th percentile), Q2(50th percentile), Q3(75th percentile), and the highest number that is not an outlier.
Photo by author
Many columns have outliers, but most of them seem like natural outliers that came from the population. In contrast, the outliers in Year_birth seem like entry errors since it’s impossible that people born before 1900 still alive. Therefore, I will remove the outliers in Year_birth.
Outliers mean they are below or above 3 standard deviations from the mean.
Photo by author
2. Are there any useful variables that you can engineer with the given data?
After assessing the dataset, I list the new features that I think can be useful for the last analysis. For example, if we know the average month and the day of the week that the average person became a customer, then when we build a campaign on that day or in that month, it might help boost more first-time customers.
Join_month: The month that person became a customer, which can be engineered from “Dt_Customer”Join_weekday: The day of the week that person became a customer, which can be engineered from “Dt_Customer”Minorhome: The total number of minors in their family, which can be acquired by summing up by Kidhome and Teenhome.Total_Mnt: Total amount spent in the last two years, which can be acquired by summing up all the “Mnt”-related columnsTotal_num_purchase: Total number of purchases in the last two years, which can be acquired by summing up all the “Num”-related columnsTotal_accept: Total amount a customer accepted the offer in all the marketing campaigns, which can be acquired by summing up all the “Accepted”-related columns and the “Response” column“AOV”: AOV stands for the average order volume of each customer, which can be engineered by dividing Total_Mnt by Total_num_purchasePhoto by author
3. Do you notice any patterns or anomalies in the data? Can you plot them?
We can use a heatmap to see the correlations between each variable. When it gets bluer, they are more positively correlated, and when it gets redder, they are more negatively correlated.
Photo by author
Findings:
Patterns:
1. High-Income People
— tend to spend more and purchase more.
— tend to visit the company’s website less frequently than other people.
— tend to has few numbers of purchases made with a discount
2. People having kids at home
— tend to spend less and purchase less.
— tend to has a high number of purchases made with a discount
3. People who purchased with high average order volume
— tend to buy more wines and meat products
— tend to make a high number of purchases made using a catalog
— tend not to visit the company’s website.
Anomalies:
1. Intuitively, I’d think the more complaints a customer has, the less they may spend on our store, but the number of complaints in the last two years has almost no correlation with the total amount spent in the last two years. => After further investigating the data, I found that it is because we only have 20 customers who complained in the last two years, but we have 2200 customers in total. So, because of the imbalanced ratio, they don’t correlate. The customer service department in the company has done a wonderful job in the last two years.
In this dataset’s Kaggle page, there are some statistical analysis questions that the data publisher suggested answering, and I decided to choose the following three questions to explore:
What factors are significantly related to the number of store purchases?Your supervisor insists that people who buy gold are more conservative. Therefore, people who spent an above-average amount on gold in the last 2 years would have more in-store purchases. Justify or refute this statement using an appropriate statistical testFish has Omega 3 fatty acids, which are good for the brain. Accordingly, do “Married Ph.D. candidates” have a significant relation with the amount spent on fish?
Now let’s look at the questions one by one.
1. What factors are significantly related to the number of store purchases?
We can use the random forest to predict store purchases and then utilize the model’s feature importance score to rank the factors.
Result:
Mean Absolute Error: 0.78703125
Mean Squared Error: 1.4546007812500001
Root Mean Squared Error: 1.2060683153329252
The range of NumStorePurchases is 13, and the Root Mean Squared Error is only 1.2(less than 10% of the range), which means it is a reliable model.
Now, let’s use random forest’s feature importance score to see which factors most contribute to the NumStorePurchase.
We can now see that the top 7 factors are
1. Average order volume
2. Total amount spent in the last two years
3. Total number of purchases in the last two years
4. Amount spent on wine in the last 2 years
5. Number of purchases made using a catalog
6. Number of visits to company’s web site in the last month
7. Total number of purchases through website in the last two years
However, we can’t tell whether each factor is positively or negatively correlated to the number of store purchases. We can use SHAP to explain it.
There is a famous article by Samuele Mazzanti explaining what SHAP is. Please check out here.
Photo by author
Finding:
The number of store purchases increases with the higher total amount spent(Total_Mnt), higher total purchase amount(Total_num_purchase), higher AOV, and higher amount of wines purchases(MntWines).The number of store purchases decreases with more website visits(NumWebVisitsMonth), a higher number of purchases through the catalog(NumCatalogPurchases), and a higher number of purchases through websites(NumWebPurchases).
Summary: People who mostly shop at stores tend to buy more wines, have a higher average order volume, and shop less through the internet or catalog.
2. Your supervisor insists that people who buy gold are more conservative. Therefore, people who spent an above average amount on gold in the last 2 years would have more in store purchases. Justify or refute this statement using an appropriate statistical test.
To statistically verify this claim, we need to use a correlation test to see if MntGoldProds and NumStorePurchases are positively correlated. First, let’s look at the scatterplot of the two variables.
Photo by author
As we can see, there is a very vague trend that says as MntGoldProds increases, NumStorePurchases also increases. Now, let’s look at the correlation test.
Pearson correlation (r): 0.38326418634704296
Pearson p-value: 3.4668974417790955e-79
We got a Pearson correlation of 0.38 and a p-value of almost zero, which states that they are statistically significant and have a positive correlation. (If the p-value is > 0.05, we will fail to reject the null hypothesis, where they do not correlate.)
3. Fish has Omega 3 fatty acids which are good for the brain. Accordingly, do “Married PhD candidates” have a significant relation with amount spent on fish?
To statistically verify these, I first divide the data into two groups. One is the married Ph.D. group and the rest. And then, we can use a boxplot to visualize these two groups to see if they are different. Lastly, we can use a t-test to test whether their mean is similar.
photo by author
This plot shows that the rest of the customers spent more on fish products as its 50th percentile is higher than the married Ph.D. group. Now, let’s look at the t-test.
T-test p-value: 0.005297012242158541
Since the p-value is less than 0.05, I concluded that we reject the null hypothesis, meaning that their means are different, but the Married Ph.D.’s mean is lower than the rest, as we can see from the graph.
Here are the questions that I’d be exploring using data visualization:
Which marketing campaign is most successful?What does the average customer look like for this company? Which products are performing best?Investigate the differences in the customer characteristics and purchases behaviors between the most successful campaign and the rest.
Now let’s look at the questions one by one.
1. Which marketing campaign is most successful?
Photo by author
Response means the last marketing campaign, which is the most successful one. It performed nearly twice as well as the previous campaigns, except campaign 2.
2. What does the average customer look like for this company? Which products are performing best?
After using .mean(), I found that an average customer…
has an annual income of 52200 dollarshad purchased 49 days agohas an AOV of 26.8 dollarshas spent 605 dollarshas purchased 20 timesbecame a customer in mid-Junebecame a customer on Thursdayspent most on wines(300 dollars) and then meat products(165 dollars)spent least on fruit(26 dollars) and sweet products(27 dollars)
3. Investigate the differences in the customer characteristics and purchases behaviors between the most successful campaign and the rest.
Now that we know the last campaign is the most successful one, we can further investigate the differences in the customer characteristics and purchases behaviors(listed below) between the most successful campaign, the last one, and the rest of the campaigns, campaign 1–5.
Photo by author
The last campaign attracted more valuable customers in terms of AOV, the total amount spent, and the total number of purchases compared to the customers attracted by the previous campaigns.
In terms of product categories, the customers in the last campaign spent nearly two times more money on meat products and wines compared to the customers in the previous campaigns.
Regarding purchasing channels, the customers in the last campaign purchased more evenly through stores, websites, and catalogs, whereas the customers in the previous campaigns mostly purchased through stores and websites.
The customers in the last campaign earned 20% more salary than the customers in the previous campaigns.
Let’s look at the proportion change of each country from the previous campaigns to the most successful campaign.
Photo by author
Spain has relatively more customers (+4%), and India has fewer customers (-3%) attracted to the last campaign.
Let’s review the main goal of this project
I’m a data analyst, and the Chief Marketing Officer has told me that previous marketing campaigns have not been as effective as they were expected to be. I need to analyze the data set to understand this problem and propose data-driven solutions.
To form data-driven solutions, I first summarize all the insights I got from the analytics, then I use those insights to form actionable strategies.
Summaries of insights:
1. The last campaign performed nearly twice as good as the previous campaigns
The last campaign attracted more valuable customers in terms of AOV, the total amount spent, and the total number of purchases, compared to the customers who were attracted by the previous campaigns.
Spain has relatively more customers (+4%) and India has fewer customers (-3%) that were attracted to the last campaign
In terms of product categories, the customers in the last campaign spent nearly two times more money on meat products and wines compared to the customers in the previous campaigns.
In terms of purchasing channels, the customers in the last campaign purchased more evenly through stores, websites, and catalogs, whereas the customers in the previous campaigns mostly purchased through stores and websites.
The customers in the last campaign earned 20% more salary than the customers in the previous campaigns.
2. Most customers purchase through physical stores, where people tend to spend more amount per purchase. The reason might be the customers had more impulsive purchases when they saw other similar products in stores.
3. People having kids at home are less valuable customers as they…
tend to purchase less
tend to has a high number of purchases made with a discount
4. The average customer…
became a customer on Thursdays
became a customer in Mid-June
Actionable Data-Driven Solutions
On Acquisition:
Keep using the same marketing techniques in the last campaign, but with a focus on promoting meat products and winesSpend more marketing budget in Spain, and less in IndiaHave a brand discount day on Thursday or a brand discount month in June to attract new customers
On Increasing revenue:
Have marketing campaigns to convert customers who shop mostly on a website or catalog to in-store purchasers because most in-store purchases have a high average order volume.Build a loyalty program to make high-income customers loyal as long as possible
I also explained the context and the basic information of this dataset in this video, so if you want to see the presentation, please jump to 2:20.
https://www.youtube.com/watch?v=3Nij8BCQ0ig
Thank you for reading to the end! If you are interested in the full code of this project, please check out my Github. Besides, I love feedback. If any part is unclear or should be done better, please reach out to me. Here is my LinkedIn or contact me through my email(yuehhanchen@gmail.com)
