SQL for Marketing: Querying a Marketing Campaign dataset for Customer Segmentation
And we’re back, baby. Like we never left. Welcome to a new SQL week, lovelies. 🙌🏾👏🏾🤗 Over the SQL weekend, I got my hands on a marketing campaign dataset and decided to gain some insights into the customer base and behaviour. As a marketing analyst, one would want to know who the ‘best’ and ‘worst’ customers and customer groups are, what campaigns got the highest and lowest responses, how customers’ income affects their purchasing power, and so on. These and many more marketing analytics questions will be answered here. Let’s get to it.
There’s no backstory to this, unfortunately. I’ve been working on this for a while and decided to put it out today.
Oh yeah, I forgot to update you on my recent Marketing Analytics and Product Analytics certifications. Hehe 😁. These past few weeks, I’ve acquired over 7 different certificates from DataCamp, Pendo, Simplilearn, and 365DataScience on Marketing Analytics and Product Analytics. For everyone who’s interested in Marketing/Web Analytics, Product Analytics or looking to work in the retail or ecommerce sector and areas dealing with Customer Analytics, these certificates could come in handy. The courses are quite detailed as well. Okay, end of segue. Back to work.
The first thing to do will be to view the dataset we’ll be working on.
Our dataset consists of 2,240 records and 30 fields.
Now to find out the dates of enrolment of the first customer and the last customer.
The first customer joined on the 30th of July, 2012 while the last customer joined on the 29th of June, 2014.
2. Finding the lowest, average and highest income based on education levels.
This is skewed, though, due to the outlier income of 666,666, which could be an error.
3. Total number of complaints.
Overall, we’ve had only 21 complaints. Not bad.
4. Age groups with the most complaints.
As we can see, the most complaints have come from the elderly customers. This could be due to difficulty in navigating through the web and catalog purchase options.
5. Number of customers that accepted the offers in each campaign according to their relationship status.
Results: Cmp1: Partners — 95, Singles — 49; Cmp2: Partners — 19, Singles — 11; Cmp3: Partners — 100, Singles — 63; Cmp4: Partners — 106, Singles — 61; Cmp5: Partners — 110, Singles — 53. Now according to this, customers with partners accepted the campaigns more than customers with no partners. Also, Campaign 2 had the least number of acceptances.
6. What is the total amount spent on products according to customers’ relationship statuses?
7. Now to answer the same question according to age groups.
Customers in the middle age category have the highest purchasing power.
8. Age group with the most discounted purchases.
Again, middle aged customers have made the most discounted purchases.
9. Last days since each customer’s recent purchases.
More than 200 customers haven’t purchased anything for over 90 days. These could be lapsed/churned customers. Though, this should not be ascribed to the early enrolment dates, as the latest customer to purchase enrolled in 2012; meanwhile a few of the newly enrolled 2014 cohort customers haven’t made any purchase for close to a 100 days.
10. Customers with the highest purchasing amount based on number of kids.
The top 15 customers with the highest amounts spent have no kids. Says a lot, doesn’t it? 👀🌝🌝
11. Customers with the highest purchasing amounts based on relationships and income.
Oh wow! People in relationships have the highest purchasing amounts. Hmmm.
12. Ages and age groups of the top spenders. 😎
13. Purchasing styles of the big spenders. 💸
The top spenders prefer store visits, and web purchases are not so popular among them.
14. Discounted deals and number of web visits by the highest purchasing customers.
15. How many campaign offers were accepted by these customers?
16. Details of the Top 10 customers with the highest campaign offer acceptances.
17. Customers with the highest number of web visits.
As we can see, these group of customers are some of the lowest income earners who have significantly low numbers of catalog, store and web purchases and who didn’t accept campaigns. In essence, high number of visits <> high number of purchases.
In conclusion, we found out that the customers with the highest purchasing amounts—i.e. the ‘best’ customers—are customers with partners, no kids, degrees, are youthful, with most favouring store visits over purchases over the web and catalogues. This might have influenced their acceptance of the campaigns, given that the average acceptance rate of these top spenders to campaigns is 2/5.
That will be all for today. Thank you so much for reading this. You could go through my code for this project on my GitHub page here. You could also connect with me on Twitter and LinkedIn, where I write all about Healthcare, Marketing, and Product Analytics. You can check out my last article on Healthcare Analytics here, and do leave lots of claps and a comment for encouragement.
Bye, my lovelies, and have a great SQL week. 🤗
