Speaker 1: Hi, in this episode, we're going to focus on customer segmentation. We are going to focus on finding the best and worst group of customers for a marketing campaign. So let's get started with this episode. So management has a request. Management wants to rank and group customers. They don't really know how, so that's where our expertise comes in. Their additional requests are identify good and bad segments, and who are the most valuable customers. So the segmentation technique that we're going to use, we're going to rank and group customers based on recency, frequency, and monetary total of their transactions to identify the best customers and perform targeted marketing campaigns. And that's called a RFM model, recency, frequency, and monetary. So what are the tools we're going to use? We're going to use Excel and PowerPoint. And then we're going to use functions percent rank. And we're going to use a pivot table. And that's all we're going to need for this task. So let's dive into Excel. So let's dive right into the data set. What we have here is an e-commerce data set. We have the invoice number, the stock code, the description of the product, the quantity, invoice date, unit price, customer ID, and country. Now remember the one thing that we want to be able to do is create a RFM model, which is recency, frequency, and monetary. So that means we need to get the total value or total amount spent. So we can create a amount spent by the customer just by going over and typing in amount spent. And that's just going to be the quantity times the unit price. So we're going to say equal, highlight the unit price, which is F2 there as the first value. And we're going to multiply that by D2, click Enter, And you can just click that little plus sign, and that will populate all of the other ones. Now what we can do is we want to start aggregating everything according to the customer ID, because this customer ID is going to be that customer identifier which allows us to do the aggregations that we want. So this is going to be the most important column for aggregating our data with a pivot table. So what we can do to create a pivot table is we just click anywhere into that data. You don't need to highlight it all. Go up to pivot table. And then you can see what happens is it encapsulates all the data in that range. And we're just gonna click new worksheet there cause I wanna have everything clean. We have our data tab and let's just double click that name and call it pivot. Now that we have our pivot table, and if we click in there, we can start populating the table that we want. Now, we want everything grouped along the customer ID, so that's going to be our rows. Once we have our row of customer IDs, what we want to be able to do is go get our columns that we're interested in. Now, we want to be able to look at recency, frequency, and monetary. So we know we created that column, which was amount spent. We can pull that down in values, and that's going to automatically sum. And that's going to give us the total value that was spent by each customer ID. Then what we want to do is go over and get our invoice, which it shouldn't really matter because the customer ID and invoice are populated along the same row. So we want to count the number of customer invoices. So now we can count that and that could be our frequency how many times they have bought something. And now what we want to do is go and get our date which is here and we want to not get that date we want to make sure we get the max date. So we go to value and then we go to max and now you can see what we have there. Now we have this pivot table that we can work with. So I am just going to copy all of this and then I am going to create another tab and this is going to call our analysis tab and I am going to highlight that cell go over to paste so paste and I just want to paste the values and then I have this table that I can start working with for recency frequency and and monetary now we don't have the recency column all we have is the max date and we see that we need to change this data type into another type of data type so let's go to short date and that's going to give us the max date because we were able to change that over. So now we can start calling this our monetary column right and you don't need to change these but I like changing them and then we can call this our frequency column and then we can call this what we don't have just yet it's still the max date. Now we can see that this data set spans from 2010 to 2000 about 2012. So I'm gonna create a max date. This is a max purchase date so it's a match as an invoice date. Now I'm using your data you can use today but obviously this is an older data set so I am going to just create a date here so I'm just going to call that current date and I'm going to call that 12-1-2011 and let's do the 31st for that and what I could do also is go here and we could find the max date and and use that, but I think 12, and let's just get the max date anyway of our max invoice date. So if I go here and I highlight all of that, I'm just curious to see what that max date is, 12-9. So we can use either one of these. Let's use 12-9 for ours, and I'm just going to copy that, and then I'm going to paste it again as values. and then what I'm going to do is copy and paste values again and obviously this row needs to be a date so I'm going back to short date so I'm changing all that to date and because I have two of the same values when I highlight and I click this little plus sign it's just going to produce the same date. Now we can see that we have two date columns Now I'm going to show you a function that you may not be able to see very easily in the archive functions in Excel and it's called dated diff. So now we can make our recency column and we can go with dated and you can see it's not there because it's not a function that is shown. So we're going to use a dated Diff function and you can see it kind of pops up there and it's now we can click into it and you can see warning Excel provides data diff function in order to support older workbooks so it's not an up-to-date function but you can see what it takes there we take a start date and we take an end date and then we take the unit of measure which we can indicate here so I'll be sad when they get rid of of this because I like it but let's use this function. So what we want to do is we want to use our start date which is our max invoice date which would be the last purchase date right. Then we want to use our current date and then we want to say what unit of measure we want. Let's use days so I am going to produce day And click enter so you can see we have all our parameters in our function or arguments in our function We click The button and we can see it's been 325 days so if I click this down and you can see that matches up to That time this is when the year started and this is when the year ends So if I click that down, you can see that it's only been two days from this max date. If this is our current date of 12-9. So now we have our monetary frequency and recency columns. So now we can start creating our score and identifying the customers that we want to target. Now that we have all our essential columns in place, we want to be able to create a score for each one of our RFM variables, which are recency, your frequency and your monetary value. I'm going to start with monetary here. So I'm going to create that column and we're going to call that our monetary score. And what we can do is utilize the percent rank formula, which is going going to take all the percentages of those columns. So what is the percent that value represents in that column and then ranks them. So you can type in equals and start writing percent. And then you can see we have two values here, percent rank and percent rank inc and inc. The ones we want to be able to use are the two here. And inclusive is going to include the 0 and 1 value. And then exclusive is going to exclude the 0 and 1. So it's going to take the percentages that value represents within that column and then rank it. And we'll be able to create a score that way. So let's use percent rank INC, which is going to include 0 and 1. Then I'm going to pass the array, which are all the values here. So I'll just highlight that. Then the value that I want to discover is the X value here so the X is going to represent B2 and Then you have a significance column which will allow you to have the number of zeros Decimal points behind the particular zero so if you put one it's going to be one decimal place so and I'll hit enter and you can see what happens is that's going to be zero and we can see why because a monetary value is zero now we need to remember to lock the array because it's not going to change so I'm going to press f4 to lock in those cells and then I am going to be able to copy it down so So the one thing you can see here, it's a percent value here. But what we want to do is maybe multiply this by 10. So we just get an even number there. And if I go and copy that down, now we can see that we've been given that value. So let's do the same for the other columns. So now we have monetary. We're going to do frequency. And we should call that our frequency score. We're going to use a percent rank. INC. We are going to cover the array. We are going to lock that array with F4. then we are going to highlight the value that we want to evaluate which is C2. We're going to use a significance of 1 to get that one decimal place but remember we want to multiply this by 10 just so we have an even number. We hit enter and then we copy that down and then we can do the last one but now we Now we need to evaluate what that value is doing, which is recency. So we don't want to highlight the highest value, we want to highlight the lowest value. So what we can do here is start with the same formula, percent rank INC, and we can do the same steps for our recency. Go all the way down, lock the array, function 4, then use F2, significance of 1, and hit enter and also multiply that by 10. But I'll show you the problem here. You can see the recency here is 325 days. Obviously That's not a very recent value. So what we want to be able to do is we want to subtract one from this value to flip it around and We close this off in parentheses to execute that operation first and now we can see That has gone down To and you can see now a value that is 10 is going to be given the highest value So let's name that recency score and now we have all three of our scores So now that we have this Monetary frequency and recency score what we can do is we can create a filter and Then we can say we only want to look at our customers at eight nine and ten for those scores for monetary Frequency and recency to get our top customers and Then we'll be left with a certain number of customers that Only present eight nine and ten for those numbers and we can see just by highlighting a particular column That we have a lot less customer base now we can also what we can do is remove that filter and say okay let's create an RMF score based on the sum across those columns and we can then use a RFM score in the The same method that we have done before with our percent rank, IMC, we capture our array, we lock the cell, and just as we did before, we focus on that particular cell, J2, and then we give it a significance of 1, multiply it by 10. And now we can see that RFM score for each one of our variables, which would be recency, frequency, and monetary. Now you may want to even go one step further as creating a lookup table so we can name these different customers. So let's create our lookup table with our customer types. Let's give our RFM score here and then we'll do the customer segment here and what we want to be able to do is give our top customers a score and we'll give them the 10s the 9s and the 8s and we can just click that down oops let me move that back click that down and then we're gonna call seven the the sixes and the fives let's call call those our loyal customers now this is arbitrary you can call it what you want and this may this is an addition to just being able to filter and now we can do four and our three and our two and I think I'm gonna call these at risk or maybe need attention at risk need attention I'll just click that down and then for our 1 and our 0 immediate attention now these may be need to be kind of configured a little bit better but this is just show you how to use a different type of segmentation naming segment a segment names and then what we're gonna do is just do a very quick V lookup so V lookup we wanna just be able to look up this value where we want to find it we want to find it in here and then what we want to do we want to return the second column and then we want exact match so we use false and we want to be able to lock that range because it's not going to change so we're going to F4 for that range for our lookup. And it looks like we got this one in there. So let's remove those dollar signs. And you can see that customer is named immediate attention. And then we just click that down. Now that we have that, we can always do one step further and produce a visual. So I'm going to insert a pivot chart so I can count these to see how much they are. I'm gonna do that on a new worksheet. I'm gonna take the segment name into the information here. So I take segment name into our axis and then count. And then we will have this segment. We do have a blank there, so I'm gonna filter that out. Then I wanna be able to see this as a different chart, so I'll go to design, change chart type, and I'm gonna use a pie chart. Then once I have that, let's add some data labels. I definitely wanna see that count as a percentage, So I go click into values, show value as calculation. We want a percent of that column total, hit enter. I am going to look at more options here. And I actually want to use the category name also. And then I want those outside. So now I have just a quick visual that I can use and change the design here. so I can use this in my presentation. So now that we've done our analysis, we're presented with the task of communicating our results to our management. So we wanna remind them that we use a RFM model that is gonna be based on recency, frequency, and revenue. And we were able to identify five major segments for targeting. We know that 18% of our customer base is a top customer base, which brings in $7,200. And they are purchasing about eight times in our evaluation period. And they have a recency of 10 days on average. Then we are looking at an at-risk and an immediate attention segment. And what I did is I combined these two together, which would be at risk and the immediate attention, which as you see is the lion's share of our data with 51%. And I just combined their metrics and took the average. So on average, they're spending $353 and have only purchased three times in our evaluation period and have a recency of 150 day. So my recommendation would be to create a targeted campaign to reengage our outrisk. Customers with incentives and further analyze our top customer and loyal customer segments to maximize earnings. So what is driving those customers or what products are they interested in? And maybe we can incentivize our lower in customer base. So I hope that helped you. And please like and subscribe and leave any questions. And if you also want to see this model being created in Python, please let me know in the comments section below. Thank you.