{"id":2050,"date":"2015-06-12T00:00:00","date_gmt":"2015-06-12T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/getting-to-know-your-customers-better-cohort-analysis-and-rfm-segmentation-in-r\/"},"modified":"2021-05-17T18:33:14","modified_gmt":"2021-05-17T18:33:14","slug":"getting-to-know-your-customers-better-cohort-analysis-and-rfm-segmentation-in-r","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/getting-to-know-your-customers-better-cohort-analysis-and-rfm-segmentation-in-r\/","title":{"rendered":"Getting to know your customers better – cohort analysis and RFM segmentation in R"},"content":{"rendered":"
\n

In this article I \twill demonstrate a couple of \thands-on ways to do a data analysis using  \ta combination of SQL \tServer and R  in order to get some \tuseful knowledge about customers that  can \tbe almost immediately applied to informing business decisions. I will \tdescribe a cohort analysis as a way of analyzing the value of a customer to \ta business, and then dive into a slightly more complex segmentation called \tRFM (Recency, Frequency and Monetary). <\/p>\n

RFM has been a \tvery popular marketing tool for at least 10 years. It provides categories or \tratings that are based on how \tlong ago that a customer made a purchase, how often they purchase, and the \tamount that they spend.<\/p>\n

Keep in mind that \tthese analyses are more widely useful than just for direct retail marketing, \tin that the broad principles can be applied to almost any dataset of \tcustomer behavior that require reports that are simple for business people \tto understand.<\/p>\n

Setting up the data<\/h2>\n

For the purpose \tof this article, and to allow the reader to easily try the examples out, we \twill use MS SQL Server as a data storage engine, the AdventureWorksDW2012<\/strong> \tdatabase and the [dbo].[FactInternetSales] t<\/strong>able. The rest of the analysis \tand the graphical representation will be done in R.  \t<\/p>\n

We will create \tthe following view:<\/p>\n

\tUSE [AdventureWorksDW2012];\n\tGO\n\t \n\tDROP VIEW [dbo].[vCustomer_Data]; \n\tGO\n\t \n\tCREATE VIEW [dbo].[vCustomer_Data]\n\tAS\n\t    WITH    CTE\n\t              AS ( SELECT   fis.[CustomerKey] ,\n\t                            SUM(CONVERT(INT, [SalesAmount])) AS OrderValue ,\n\t                            CONVERT(DATE, [OrderDate]) AS OrderDate\n\t                   FROM     [AdventureWorksDW2012].[dbo].[FactInternetSales] fis\n\t                            JOIN [dbo].[DimCustomer] dc ON fis.[CustomerKey] = dc.[CustomerKey]\n\t                   WHERE    [CurrencyKey] = 100\n\t                   GROUP BY fis.CustomerKey ,\n\t                            OrderDate ,\n\t                            dc.DateFirstPurchase\n\t                 )\n\t    SELECT  *\n\t    FROM    CTE\n\t    WHERE   CTE.OrderDate BETWEEN '2007-01-01'\n\t                          AND     '2008-03-31';\n\t \n\tGO\n<\/pre>\n
\n
Note: for  \tlearning purposes I have restricted the data to 15 months. In reality, you’d  \tprobably need to follow up longer timespans depending on the purpose of the  \tanalysis. <\/p>\n<\/div>\n
The data looks  \tsimilar to this:<\/p>\n
 \t\"2242-imgB.jpg\"<\/p>\n
This is a very  \tcommon data set, which can be found in almost any transactional system. Here  \twe have the following features:<\/p>\n
    \n
  •  Some uniquely-identified entity (whether it is an individual  \t\tcustomer, event type or other), in this case CustomerKey<\/strong><\/li>\n
  •  Some indicator of when the event  \t\toccurred (it could be a timestamp, but not always), in this case  \t\tOrderDate<\/strong><\/li>\n
  •  Some value related to the unique  \t\tentity and the particular event occurrence, in this case  \t\tOrderValue <\/strong><\/li>\n<\/ul>\n
     \tIn this case our  \tdataset contains a fourth column –  \tDateFirstPurchase<\/b>, however in this case it is not really a feature of  \tthe dataset, but it is merely a derived value. <\/p>\n
    Now that we have  \tthe data ready, let’s try and extract some valuable knowledge from it. Let’s  \tdo a preliminary exploration of the data by doing a cohort analysis, to see  \tif there are any patterns in our customers’ behavior. <\/p>\n
    Cohort analysis<\/h2>\n
    Cohort analysis  \tis a broad topic, and it has many variations. Let’s first give some basic  \tdefinitions:<\/p>\n
      \n
    • Cohort<\/strong> – this is  \ta group of people or events who share a common characteristic over time \t\t<\/li>\n
    • Cohort analysis<\/strong> –  \tthis is the study of activity \/ behavior of a particular cohort or a group  \tof them over time (or other iteration).<\/li>\n<\/ul>\n
      In metacode, a  \tcohort analysis is similar to this: ‘take a dataset which has uniquely  \tidentified entity (customer in this case), define unique cohorts by which  \tthe items can be grouped (in this case we will use the year and month of  \tfirst purchase), and follow the behavior of the cohorts over time (in this  \tcase the sum of the OrderValue for each cohort per timeslice)’.<\/p>\n
      It may seem a bit  \tcomplex at first sight: However it is straightforward in practice. In this  \tcase we are asking the questions:<\/p>\n
        \n
      • ‘How do our users behave as they get further away from their  \t\tfirst purchase date?’ <\/li>\n
      • ‘Do they keep buying equally a lot as  \t\tthey did in the first month of their activity, or do they phase out as  \t\ttime passes?’ <\/li>\n
      • ‘Is there a pattern when we compare the  \t\tfirst purchase months of different cohorts?’<\/li>\n<\/ul>\n
         Let’s load the  \tlibraries first:<\/p>\n
        \tlibrary(dplyr)\/span>\n\tlibrary(reshape2)\n\tlibrary(ggplot2)\n\tlibrary(RODBC)\n\t \n\t<\/pre>\n
        Then we will  \tconnect to SQL Server and load the data from the view we created into  \tmemory:<\/p>\n
        \tcn <- odbcDriverConnect(connection=\"Driver={SQL Server};server=localhost;database=AdventureWorksDW2012;trusted_connection=yes;\")\n\t \n\torders <- sqlFetch(cn, 'vCustomer_Data', colnames=FALSE,\n\t                        rows_at_time=1000)\n\t<\/pre>\n
        Then we have to  \tconvert and format the OrderDate<\/b>  \tvariable:<\/p>\n
         orders$OrderDate <- as.Date(orders$OrderDate, format='%Y-%m-%d')<\/pre>\n
         After this, we  \twill create a new dataset in memory. This dataset will have additional  \tfeatures that we’ll need for our graphical representation later on:<\/p>\n
        \tcohort <- orders %>%\n\t  arrange(CustomerKey, OrderDate) %>%\n\t  group_by(CustomerKey) %>%\n\t  mutate(FPD = min(OrderDate), \n\t         CohortMonthly = paste0('Cohort-', format(FPD, format='%Y-%m')),           \n\t         LifetimeMonth = ifelse(OrderDate==FPD, 1, ceiling(as.numeric(OrderDate-FPD)\/30))) %>% \n\t  ungroup()\n<\/pre>\n
        Here is what the  \tcode does: <\/p>\n
          \n
        1. Takes the orders dataset and arranges it  \t\tby CustomerKey<\/strong> and OrderDate (<\/strong>this is  \t\tto make sure the data is in chronological order per user)<\/li>\n
        2. Groups it by Customerkey,<\/strong>  \t\tsince we need data on cohort level<\/li>\n
        3. Creates three new variables (columns) in  \t\tour dataset: FPD<\/strong> (first purchase date),  \t\tCohortMonthly<\/strong>  \t\tand LifetimeMonth<\/strong>.<\/li>\n<\/ol>\n
           Next we need to  \tformat the LifetimeMonth <\/b>variable  \ta bit more:<\/p>\n
          \tcohort$LifetimeMonth <- formatC(cohort$LifetimeMonth, width=2, format='d', flag='0')\n\tcohort$LifetimeMonth <- paste('M', cohort$LifetimeMonth, sep='')\n\t \n<\/pre>\n
          The new dataset  \tlooks like this:<\/p>\n
           \t\"2242-img17.gif\"<\/p>\n
          The next step is  \tto combine and summarize the cohorts:<\/p>\n
          \tcomb.cohorts <- cohort %>%\n\t    group_by(CohortMonthly, LifetimeMonth) %>%\n\t    summarise(Revenue = sum(OrderValue),\n\t              NumberOfCustomers = n_distinct(CustomerKey))\n\t \n\t<\/pre>\n
          In this step we  \talready lose the individual data per user and instead summarize it by cohort  \tand timeframe. <\/p>\n
          Now our data is  \tready for exploration and for creating a visualization:<\/p>\n
          \tggplot(comb.cohorts, aes(x=LifetimeMonth, y=NumberOfCustomers, group=CohortMonthly, color=CohortMonthly)) + geom_line()<\/pre>\n
          Based on our data  \tthe graph looks like this:<\/p>\n
           \t\"2242-img22.jpg\"<\/p>\n
          Here is the  \tanalysis we can do by looking at this graph: on the Y axis we have the  \tNumber of Customers per month and per cohort, and on the X axis we have the  \tcohort months. This means that in this case all first purchase months are  \taligned at the point M01, and then they are followed up further by M02 to  \tM15.<\/p>\n
          Something very  \tobvious is immediately noticeable: there are a few cohorts with a  \tsignificantly stronger first month with over 750 customers making their  \tfirst purchase in a specific month. By looking at the data, we establish  \tthat the periods between 2007-08 and 2007-12 are extremely strong, but they  \tdrop very fast by their second month. Then we have the other group of  \tcohorts, which have about 100 customers purchasing the first month, but they  \tpurchase relatively a lot in their second and third months, before they  \tphase out. Then we can see another fact which is quite interesting to  \texplore further: there is a group of cohorts which get a slight increase in  \tthe purchases in their 8th and 9th months.  \t<\/p>\n
          All this leads to  \tintriguing  business development  \tquestions: why are certain months significantly stronger than others and  \twhat can we do to increase the customer retention? Why do some customers  \treturn in their 8th and 9th months? Did we have a  \tsuccessful marketing campaign then, or was it just the holiday shopping  \tseason? <\/p>\n
          This type of  \tanalysis is very effective from a business development perspective. There  \tare several reasons for this:<\/p>\n
            \n
          • The data knowledge extraction can go really fast, once the  \t\tdata is brought into memory<\/li>\n
          • There is no need for heavy duty  \t\trelational database modelling. In conventional SSAS and SSRS this step  \t\ttakes a very long time, partly due to the fact that the data is  \t\tpersisted on disks and partially because the physical data model has to  \t\tbe rebuilt quite often to match the questions asked.<\/li>\n
          • The iterations between business questions  \t\tand answers are very short, and thus a great amount of work can be  \t\tperformed in a very short time, since the dataset is in memory and all  \t\tit takes is a skilled data analyst and a savvy business development  \t\tspecialist to get together and iterate through all questions the data  \t\traises<\/li>\n<\/ul>\n
            This analysis,  \thowever, is a bit limited because it shows only one side of the customers’  \tbehavior; either the number of customers who purchased and their retention,  \tor the revenue per cohort. Even though this is very useful and gives great  \tinsights, this type of analysis is still one-dimensional. In other words, we  \tcannot clearly explore the dependency between number of customers and their  \tpurchase value. After all, we might have 100 customers purchasing for £1  \teach, vs 10 customers purchasing for £100 each. We would certainly be  \tinterested to target these two groups differently. <\/p>\n
            This is why there  \tis a different approach: RFM segmentation. Let’s look into this next.  \t<\/p>\n
             RFM segmentation  \t<\/h2>\n
             RFM segmentation  \tis a technique used to get to know the customers better and to be able to  \tdivide them into groups which will make marketing targeting more effective  \tand cost-efficient.<\/p>\n
            RFM stands for  \tRecency, Frequency and Monetary and it does exactly this – gives these three  \tdimensions to our customer base based on their transactions (how recently  \tthey purchased, how frequently they purchased and how much money they  \tspent). <\/p>\n
            The premise is  \tthat customers who have bought more recently, purchased more often and have  \tspent more money are more likely to respond to a marketing offering, than  \tcustomers who have purchased less recently, not so often and for very little  \tmoney. RFM analysis can be used to segment and label the customers (active,  \tchurning, churned, never activated) and thus the proper marketing campaign  \tcan be directed to the right group of customers. <\/p>\n
             Defining the terms<\/b><\/p>\n
            The RFM  \tsegmentation is very specific and is dictated by the business model, however  \tit is easily customizable. In general terms, the definitions look like this:<\/p>\n