Customer Analysis Part III: Purchase Analytics

This is part three of a multi-part series. Part one, segmentation and clustering, can be found here. Part two, classification, is here.

This post contains incomplete code snippets. The full code for this section can be found in the repo: https://github.com/jamesdeluk/data-science/blob/main/Projects/customer-analysis/ca3-purchases.ipynb

UPDATE 2024-11-26: During part four I noticed I had made a mistake in the Product Analysis section - I’d accidentally used a subset of the dataset when doing an analysis. Given part four is exclusively about product and brand analysis, I have moved the now-corrected section to that post.

Intro

Welcome to part three! Now I’ll integrate some purchase data. I have a CSV where each record represents a time a customer visited a store. Each record has the unique visit ID, along with the customer ID, which aligns with the customer ID we’ve been playing around with in parts one and two.

Exploring the data

The first thing is to merge these two datasets (renaming the Customer ID column to match the style of the others, using underscores instead of spaces), so we can link the visit to the cluster:

df_raw = purchases.merge(customers, left_on='Customer ID', right_index=True).rename(columns={'Customer ID':'Customer_ID'})

What does this look like? I’ll ignore the columns we don’t care about, those we used for clustering, such as Age and Sex:

And a describe():

There were 58693 different visits in total, across 730 days, so presumably two full years - although note the final record is 726, so the data is not entirely in date order. Incidence is a binary value, recording if a purchase was made on that visit. There are five brands, relating to prices and promotions _1 to _5; they look to be priced in order, from 1 the cheapest to 5 the most expensive. Brand 0 links with incidence 0, implying no purchase. This feels like a good opportunity to one-hot encode, using panda’s get_dummies:

df = df.join(pd.get_dummies(df['Brand'], prefix='Brand', prefix_sep='_').astype(int))

As we’re here, let’s calculate some stats. Revenue is price multiplied by quantity, so we can do that for each row by looping through each newly made brand column, creating a temp DataFrame with the revenue, then merging it into the existing DataFrame. We need to use fillna(0) to avoid NaNs:

for brand in [1, 2, 3, 4, 5]:
    temp = df[df['Brand'] == brand]
    temp = temp.assign(**{f'Revenue_Brand_{brand}': temp[f'Price_{brand}'] * temp['Quantity']})
    col = f'Revenue_Brand_{brand}'
    temp = temp[[col]]
    df = df.merge(temp, left_index=True, right_index=True, how='outer')
    df[col] = df[col].fillna(0)

And calculate total revenue by adding them up - yes, right now, each row only has one revenue figure, but later on, when we start grouping, this might be useful:

df['Total_Revenue'] = (df['Revenue_Brand_1']+df['Revenue_Brand_2']+df['Revenue_Brand_3']+df['Revenue_Brand_4']+df['Revenue_Brand_5'])

Lastly, Last_Inc_Brand and Last_Inc_Quantity I don’t care about, so I’ll remove them. I also reordered some of the other columns. As an example, the first row looks like:

Customer analysis

The first thing I want to analyse are the customers. Who are our top customers? What are their demographics? How often do they visit? How much do they buy? What do they buy?

I’ll show you my code, then talk you through my process:

df_customers = df[['Customer_ID','Cluster']].groupby('Customer_ID').mean().astype(int)
df_visits = df[['Customer_ID','Incidence']].groupby('Customer_ID').count().rename(columns={'Incidence':'Store_Visits'})
df_pinstances = df[['Customer_ID','Incidence']].groupby('Customer_ID').sum().rename(columns={'Incidence':'Purchase_Instances'})
df_pcount = df[['Customer_ID','Quantity']].groupby('Customer_ID').sum().rename(columns={'Quantity':'Purchases_Count'})
df_trevenue = df[['Customer_ID','Total_Revenue']].groupby('Customer_ID').sum()
df_customer_habits = df_customers.merge(df_visits, left_on='Customer_ID', right_on='Customer_ID')
df_customer_habits = df_customer_habits.merge(df_pinstances, left_on='Customer_ID', right_on='Customer_ID')
df_customer_habits = df_customer_habits.merge(df_pcount, left_on='Customer_ID', right_on='Customer_ID')
df_customer_habits = df_customer_habits.merge(df_trevenue, left_on='Customer_ID', right_on='Customer_ID')
df_customer_habits['Conversion_Rate'] = round(df_customer_habits['Purchase_Instances'] / df_customer_habits['Store_Visits'],2)
df_customer_habits['Visit_Yield'] = round(df_customer_habits['Purchases_Count'] / df_customer_habits['Store_Visits'],2)
df_customer_habits['Basket_Size'] = round(df_customer_habits['Purchases_Count'] / df_customer_habits['Purchase_Instances'],2)
df_customer_habits.sort_values('Total_Revenue', ascending=False)

We want details per customer, so all of these are grouped by Customer_ID. First, I created a DataFrame of the customer IDs and their clusters. Next, one of the count of Incidence - that is, the number of store visits for that customer (as each customer ID is linked to multiple visit IDs). Next, one for the sum of incidences - given 1 refers to a sale, this is a count of the actual times they purchased, which I named Purchase_Instances. Next, Quantity - how many items they purchased, which I called Purchases_Count. Finally, let’s also get the total revenue calculated earlier. I merged all these DataFrames into a single df_customer_habits.

From here, I added some new calculated columns: Conversion_Rate (times they purchased divided by the number of store visits), Visit_Yield (number of purchases divided by number of store visits), and Basket_Size (number of purchases divided by the number of times they purchased). Finally, sort by Total_Revenue. Also describe(). We get:

There are 500 customers in this dataset, so although our customer dataset has records for 2000 customers, only a quarter of them were recorded as having made store visits. All clusters are represented. There’s a big range in customer behaviours, from 10 items purchased to over 700, a few dozen visits to a few hundred. It looks like it’s quite positively skewed too, based on the percentile figures.

Let’s do some plots. Given the Seaborn pairplot wasn’t possible in part two, I was determined to use it here. You might need to open it in a new tab or zoom in, but we can get the info we need:

Lots here. Given the common axes, the charts to the upper right of the top-left to bottom-right diagonal are mirrored versions of those on the lower left. Looking at the diagonal, the distribution plots - as anticipated, most distributions are positively skewed. It seems that although we only have a quarter of the customers, the cluster ratio is similar to that of the overall clustering (see part one), which is good for when we come to cluster analysis later. There is typically a reasonable positive correlation between the different features, although conversion rate vs basket size seems to be less correlated. Let’s get some numbers for the correlations:

Unsurprisingly, most are positively correlated, the biggest between revenue and number of purchases (the more things you buy, the more you spend). This suggests most products are similarly priced though, as widely different price ranges could weaken the correlation (i.e. buying 1 item for £100 vs 100 items for £1). Interestingly, basket size actually has a negative correlation with store visits and purchase instances. However, it’s minor, so it might not be reliable. Cluster seems to have no real correlation - which is expected, given it’s a non-ordinal categorical number!

Going back to the pairs plot, there’s also something outs that jumps out- while most points are closely grouped, there are a few major outliers in most plots. Who are they? We can get them quite easily by viewing the dataset, sorted from high to low, e.g.:

df_customer_habits.sort_values('Store_Visits', ascending=False)

Sorted by visits, purchase instances, purchases, revenues, and conversion rate, it’s always the same three: 100000185, 100001255, and 100001218. These were actually displayed above when we first created this DataFrame, but as a reminder:

They each have over 300 visits, over 300 purchase instances (giving a conversion rate of 91-92%), and ~700 purchases (giving an average visit yield of just under 2). We like these people! I’ll call them our top customers. Going back to our original demographics data, we can get a better understanding of who they are:

df_raw[df_raw['Customer_ID'].isin([100001218,100000185,100001255])].groupby('Customer_ID')[['Sex','Marital status','Age','Education','Income','Occupation','Settlement size']].mean().astype(int)

All pretty average folk - mid-education, mid-occupation, mid or upper-mid incomes, mid or large cities. All single men. One bloke is older, and earning a little more, explaining his different cluster. Perhaps they live near the stores, which might explain their frequent visits. Something to investigate. And why do they spend so much - do they buy more expensive brands? We can check this, as we have their brand data:

df[df['Customer_ID'].isin([100001218,100000185,100001255])][['Customer_ID','Brand_1','Brand_2','Brand_3','Brand_4','Brand_5']].groupby('Customer_ID').mean().round(3)

As a reminder, the brands range from 1 to 5, with the higher number typically costing more. So yes, our top spending (and, also, top visiting) customers buy the more expensive brand the vast majority of the time. Note the rows in the table above don’t add up to 1 because I’ve removed “brand 0”, which is when no purchase was made - although we can see they buy something nearly every time they visit.

Interestingly, when it comes to visit yield and basket size, we get different customers, with a clear top four: 100000397, 100000914, 100000497, and 100000625. This is primarily because, while most of their stats are high (albeit not as high as our three top customers), they visited the store fewer times than average:

And their demographics?

And their brand preferences?

Some similarities, some big differences, within the group. We have men and women, married and unmarried, educated and not, very old and very young (at least relative to our dataset). However, they’re mostly high earners in big cities in managerial jobs. Perhaps they’re too busy to do frequent visits, so do less frequent but bigger shops? If that’s the case, should we incentivise them to come more often, or to purchase even more each visit? Would a home delivery service suit them? They typically buy premium brands, but at a lower rate than our top customers - interesting, given our top customers have lower incomes. That makes me wonder - is there a relationship between brand and income?

df_raw[df_raw['Incidence']==1][['Brand','Income']].groupby('Brand').mean().astype(int)

I added the count of customers to the top of each bar. There’s a bit of a link, with the three cheaper brands being favoured by those with lower incomes, and the more expensive two favoured by those with higher incomes. However, brand 4 attracts the top-earners more than brand 5, and brand 2 attracts the lowest-earnings (slightly) more than brands 1 and 2. What could be done to shift the high earners from 4 to 5, and, by extension, increase our bottom line? Looking at the total count on the top of each bar, most customers overall favour brand 5 (~1/3 of all purchases), closely followed by 2; brand 3 is not popular. Is it worth cutting brand 3 completely? Before going down this parth, we’d need to consider how customer behaviour would change - would they be more likely to buy brand 4 instead, resulting in more revenue, or brand 2, resulting in less? Profit margins also need to be factored in.

Cluster analysis

Let’s do everything we’ve done above, but group by clusters, to see how similar they each are. That was the entire point of parts one and two anyway - be able to target large swathes of our customers as well as individually. I took parts of the customer DataFrame, grouped by cluster, taking the sum(), and merged it with parts of another cluster-grouped customer DataFrame, taking the mean(). I renamed the clusters to the names from part one (although, to be precise, the below average careers were mostly married women, and the above average careers were mostly unmarried men):

I then scaled and heatmapped them:

Most customers fall into the Average cluster, as we already knew. Unsurprisingly, then, they have the highest total number of store visits, highest purchase instances, and highest purchase counts. However, those with the above average careers have the highest mean number of store visits (i.e. average per person) - although the difference is actually only ~1%. The old successful men cluster make the most purchasing visits per person, the most purchases per person, and have the highest mean conversion rate, visit yield, and basket size. Our below average career-ers, per person, have the lowest stats - which isn’t particularly surprising, as people with less money typically spend less (although when they do visit, they do buy more than the average and above average groups, perhaps because they’re more time-poor so want to maximise their visits).

Let’s get some bar graphs going. I wanted to add lined representing the standard deviations, so, similar to above, I took a cluster-grouped customer DataFrame, but this time I used .std. We get:

As we saw in the stats, the average number of store visits is pretty similar. However, the number of times a customer purchases from a store during a visit does vary; the more successful purchase more, whereas the less successful are more likely to visit and do some window shopping without actually buying anything. However, the standard deviations are large, especially for the average and above average groups, suggesting there’s a higher range of behaviours within the cluster group - that is, some customers tend to buy most times they visit, others tend to visit a lot without buying. This trend continues with the purchasing counts; so, while our below average purchase less frequently overall, the customers within the group behave more consistently than the other groups.

What about brand choice by cluster? Group and take the means:

df_cluster_brandpreference = df[df['Incidence']==1].groupby('Cluster')[['Brand_1','Brand_2','Brand_3','Brand_4','Brand_5']].mean().round(3)
df_cluster_brandpreference.index = cluster_names
sns.heatmap(df_cluster_brandpreference, annot=True, cmap='coolwarm', cbar=False)

I didn’t scale the data used for this heatmap because I didn’t feel it necessary. There are some definite standouts - the below average career cluster loves brand 2, and older successful men love brand 4. This reminds me of our income to brand chart above - brands 2 and 4 were the most popular by number of customers, and this shows us that, within certain clusters, they get over half the love. The most premium product, brand 5, is mostly liked by average and above average customers - perhaps this is signalling, showing off that they can buy expensive products, whereas the older successful men simply want a high-quality product, and, understandably, the less well-off customers want a cheaper product? Brand 3 is the least loved overall, closely followed by brand 1; based on this, maybe the company should drop those lines? We’d already considered dropping brand 3. Again, it depends on profitability and how the customers would respond to the change, but it’s something to consider going forward. Unfortunately we don’t have profit data, but we have revenue - I reused the code above, but took the revenue columns instead of the brand ones, and instead of taking the mean value, I took the sum, to get:

That is quite surprising, based on what we’ve see before Brand 5 brings in far more revenue than the others, mostly from the average and above average groups - then again, it does have the highest price, so doesn’t need such high sales volume. It looks like brand 2 brings in more revenue than brand 4. Again, though, 1 and 3 look less good. And what about total revenue per cluster?

df_revenue_by_cluster = df[df['Incidence']==1].groupby('Cluster')[['Total_Revenue']].sum().astype(int).rename(columns={'Total_Revenue':'Total Revenue'})
df_revenue_by_cluster.index = cluster_names
sns.barplot(x=df_revenue_by_cluster.index, y=df_revenue_by_cluster['Total Revenue'], errorbar=None, palette="Set1")

The old successful men bring in the least revenue overall, even though they visit the most and make a purchase almost every visit - although, given they’re only 6% of our customers, they make up a larger chunk than expected. Above average brought in more than below average, but average brought in most overall - then again, it is the biggest cluster, with 41% of the customers. The next thing to check, then, is the average revenue per customer:

df_revenue_by_cluster = df_revenue_by_cluster.merge(df_cluster_habits[['# Customers','% Customers']], left_index=True, right_index=True)
df_revenue_by_cluster['% Customers'] = df_revenue_by_cluster['% Customers']*100
df_revenue_by_cluster['Average Revenue per Customer'] = df_revenue_by_cluster['Total Revenue'] / (df_revenue_by_cluster['# Customers'])

So although our old successful men bring in the least revenue overall, they bring in the most revenue per person. More of those please!

Temporal analysis

And now for something completely different. Way back in the beginning, I noticed there was a column that we haven’t looked at so far - Day. Let’s do a little digging. There are 730 days, or two lots of 365 days; let’s assume it’s two years in order. I started with grouping, dropping, and rounding (we’ll need both the sum and the mean):

df_byday_sum = df.groupby('Day').sum().drop(['ID','Customer_ID'], axis=1).round(2).copy()
df_byday_mean = df.groupby('Day').mean().drop(['ID','Customer_ID'], axis=1).round(2).copy()

I then did some plots - the number of visits that resulted in a sale, and the quantity of items sold - using the sum data:

I’ve roughly split each x-axis mark into three months. All we really see is huge variation, although it looks like it started quite quietly (based on the lower values at time 0 for all three plots) and grew for the first three months, after which the mean remained roughly constant. Maybe it was a newly-opened store? I could do some time series forecasting, like I did in my solar PV project here, but I won’t. Yet.

I also plotted Brand, Price and Promotion (sum, mean, mean, respectively):

Note that, excluding the binary promotions charts, the scales are all different. I considered using common y-axis limits, but that would make some of them hard to see. I’ve vertically-aligned the sales/price/promotion for easier comparison. I also tried overlapping charts, but they’re a bit too busy so hard to interpret:

What can we see? Firstly, the bigger price drops look to aligns with the promotions, although even without the promotions, the prices fluctuate. In fact, the prices fluctuate a lot more than I expected. Brand 1 started with a high price (1.6), but after the first six weeks or so, never increased to that price again - and even dropped to 1.1. Brand 2’s price is mostly at about 1.85, sometimes for weeks in a row, before having dips. Both of these had regular promotions - they were on promotion almost as much as they were. Brand 3 started around 2.00, and grew over the two years, finally reaching almost 2.15. It seems to have the fewest number of promotions. The price of brand 4 also grew slightly over the period, from about 2.1 to 2.3, with a middling number of promotions. Brand 5’s price started much higher, 2.7, and grew to 2.8 - being almost double brand 1 goes some way to explain why it brought in so much revenue. Like brand 3, brand 5 had few promotions.

The number of sales per brand doesn’t look to have as high a correlation to price as I’d expect - for example, the big price dip for brand 5 around day 180 didn’t lead to an equally-large increase in sales, and the brand’s highest volume day, around day 85, came before the price drop around day 92. Sometimes they are linked though - the price drop for brand 1 around day 120 does look to cause an increase in sales. This suggests more than just price influences the sales for all the brands.

I’ll look into the relationship between sales, price, and promotion more later, but first, let’s do a bit more temporal analysis.

As mentioned before, I had two years’ worth of data. I compressed the two years into one, taking the average of the two relevant days (i.e. day 1 and day 366), to see if there were any more obvious seasonal trends than we saw from the two-year plot… But there wasn’t. It also removed the ability to see long-term price changes, such as for brand 3, so I opted to disregard that path of inquiry and not include it here.

What about an even smaller scale? We can convert the day number into the day of the week by dividing Day by 7 and taking the remainder. We don’t know if Day 1 is a Monday, Tuesday, Wednesday, etc, but at least we can get 7 days of the week, in order. Are there any weekly trends?

Not really. I was hoping we’d see a pattern which would give insight into which day is which, such as a dip on Sunday (due to, for example, reduced trading hours) after a jump on Saturday (days off work), but no such luck. The popularity of a specific brand, their prices, or the existence of promotions, doesn’t look to have any patterns, nor does which day each cluster shops. Sad.

Product analysis

Now moved to part four

Conclusion

I think that will do for now! Next time I’ll go deeper, introducing some regression models. What are our main actionable insights from this purchase analytics phase?

1. Older men with successful careers bring in the most revenue per person, but they make up a small amount of our customers. How can we attract more of them? Or could we go to them, open stores in areas with people matching their demographics?
2. We have three customers who visit, buy, and spend far more than all the others. Who are these people? We need to learn more about them, as we want more people like them - especially as they’re not in our older successful men group. Potentially invite them for interviews, or send them surveys?
3. Below-average customers provide significantly less value to the store than other customer groups, yet are our second-largest cluster. They behave consistently, and favour brand 2, so targeted promotions could boost sales, especially given brand 2 is sensitive to promotions.

Until next time!