Speaker 1: In this video we'll be looking at how you can use segmentation analysis to go beyond the basic business intelligence processes to dive deeper into your data and gain better insights into your customers, subscribers or users. Segmentation may sound quite fancy but it is in fact fairly simple to employ and you don't need to have any special data science skills or tools. Let's dive in. Hello and welcome to Vitamin BI bringing you business intelligence for beginners and beyond. My name is Adam and on this channel I help you do more with data so if you're new here do think about subscribing and hit that bell so you don't miss any uploads. We'll be discussing what segmentation is, why it's used and then I'll show you how to conduct segmentation using a technique known as RFM analysis. Something that can be done using just Excel. So I'll show you how to do that but also if you stick around until the end I'll show you how you can use Google's free BI tool Data Studio to create a more basic version of RFM. Let's get started. So the basic premise of segmentation analysis is to divide up for our purposes our customers into different buckets or segments based on their behaviors and other criteria. When you've got tens or even hundreds of thousands of customers you can't really analyze them individually. You need some way to categorize and group them. By using data related to how customers interact with your business you can score each one for different criteria and build up a detailed multivariate profile. It's important to use multiple metrics or criteria because by using just one you don't get an accurate picture of that customer. Let me explain what I mean. I'll give you an example of two separate customers. The first has made orders with your business totaling $2,000. The second only $1,000. Now tell me in your opinion and based on this single piece of information which customer would you say is more valuable to your business? The first right? Okay so now let me tell you that the first customer made one single order over two years ago and the second customer has made four orders over the past 12 months. Now based on these three different pieces of information, total value, number and recency of orders, which customer would you say is more valuable? The second right? You can now see how using multiple criteria you can build up a better picture of your customers. This example I just gave you is what RFM analysis does. RFM stands for recency, frequency and monetary. It scores the recency, frequency and monetary value of customer orders. This is the practical example we'll be doing together in a minute. So that's what segmentation is. Scoring customers for multiple variables and grouping them together based on their scores. As you can imagine there are many reasons why you'd want to use segmentation analysis in a business scenario. But the three main ones are to increase sales, to improve marketing and increase customer retention. The first two really go hand-in-hand and speak to the origins of RFM analysis. It was a technique first used to increase the efficiency of direct mail campaigns. You know actual physical pieces of promotional mail you get through the letterbox. RFM analysis allowed the marketers to segment their thousands of customers into those who are more likely to convert and purchase based on their previous behavior. Thus reducing the printing and mailing costs of their campaigns and increasing conversion rates and sales. Obviously direct mail is far less common than it used to be with the advent of email marketing. But segmentation can of course be used for that as well. Another very useful way to employ segmentation is with a subscription based business model. As it can help to increase customer retention and reduce churn. Again by using the RFM model you can predict when a customer might cancel their subscription. For example if a customer hasn't logged into their account for a while, recency, they've opened up lots of tickets with the support team, frequency, suggesting they're having problems and their account has a higher than average number of users or MRR, monetary, then this customer is more likely to cancel the one who logs in every day and perhaps only contacted support once. When you know a customer might cancel you can proactively jump in and offer further support and onboarding. After all it's commonly stated that it costs a business on average five times more to acquire a new customer than it does to retain an existing one. Meaning that RFM analysis can really help make your business more profitable. So those are the three main benefits of using segmentation. Shall we look at how it's done? Okay let's jump onto my computer and I'll guide you through it. So here we are in Excel with some sales data. If you'd like to download this data and follow along I'll leave a link in the description. As I said I'll show you how to do this in Excel first and then we'll see a simpler version using Google's Data Studio. So don't worry if you don't manage to follow this first example all the way through. We're going to take this data and in two steps conduct RFM analysis on it to segment all our customers. The first step is to aggregate this data so that we have it at a customer level. Currently it's at an order level. The way to aggregate the data in Excel is to use a pivot table. If you don't know what this is or how to use one check out this video in the top right hand corner. I've gone ahead and created the pivot table so let's look at what I did. In rows I've put customer ID and in values I've put order date and asked for the maximum date. Order ID and asked for the count and for monetary value I've put sales and asked for average. You can use average or sum for the total value of orders as well it's up to you. When you add your max date you may need to format it as a date by going to format cells and choosing a date format. Once your data looks like this select all and copy it. Then in a new sheet go to paste special values and we'll just reduce the decimals on average sales. You don't have to but it just looks neater to me. You'll notice that the date has reverted back to a number but don't worry about this. The next step is to assign a value or bucket to each of the customers for the three columns. You can create as many or as few buckets as you like but the method we're going to be using in Excel assigns a percent rank for each value based on where it ranks between the minimum and maximum values in the column. We'll then format this value to be an integer between 0 and 9 giving us 10 buckets in total. The Excel function we're going to use is called percent rank dot EXC. So equals percent rank dot EXC, open bracket, then select the column containing all the values, comma, the value we're looking for a rank for and then the third condition of the formula is the significance or the number of decimals in this case. This will give us a value between 0.0 and 0.9. So in order to get an integer we'll just multiply it by 10. Once we've done this we can just fill it down and across for the other columns. As column headers I'll put R, F and M. The way we segment our customers now is to add filters to our three columns and filter based on the values we want. To create a segment of what we might call our best customers we could select the highest value 9 in each of the three columns. But because we've got less than 800 rows in our table these top values in all three columns would only return a handful of rows. So instead we could select 7, 8 and 9. Once we've applied our three filters we can see that only 40 out of 790 customers remain. Those would be considered as our best customers. We could even name them best customers. Then you could go ahead and categorize the rest. For example you could identify frequent small purchases, those with high frequency and low average order value and so on and so forth. Now you can target customers with specific upsells that are best suited to them. You get the idea. So that was a way of conducting RFM analysis in Excel. If you want to have an interactive dashboard that lets you identify segments we can do so using Google Data Studio. You would need to aggregate the data like before as the first step but instead of going through the scoring with the percent rank function we'd need to calculate a number of days between the most recent order and today's date. Easy to do with the date diff function. Just type equals date diff open parenthesis the cell with the max date comma today with open and close parentheses comma D in double quotes which asks Excel to return the difference as a number of days. This is old data so the numbers are going to be larger than you'd expect. Then just fill this down. In order to load it into Data Studio we'll need to save it as a CSV file and then simply load it into Data Studio using the upload file connector. Then in a new report you would build a table putting custom ID in dimensions and the RFM columns into metrics. Then you add three slider controls one for each of the RFM metrics. Now you can use these sliders to filter the data a bit like we did before except we use the actual values instead of the scores we generated in Excel. You can then export the results to either Excel or CSV. If you found this video useful then please do like and share and don't forget to subscribe and turn on notifications so you never miss an upload. If you've got any ideas for other content you'd like to see just put it in the comments below. Thanks so much for watching I've been Adam Finer and until the next time, stay BI curious.