We start by getting the current number of customers within the current context and store this number in the __Customers variable. We only want the number of unique customers, so we use the VALUES function to return the unique values from the Customer column in R06_Table and then we use COUNTROWS to count the number of unique values returned.
Next, we want to calculate the average frequency of purchases from our customer or customers. To do this, we first need to calculate the minimum and maximum purchase dates within the context. We store these values in the __MaxDate and __MinDate variables. Next, we calculate the number of years over which purchases were made and store this in the __Years variable. We check whether or not all purchases were made in the same year and if so, set the number of years to 1.
Otherwise, we calculate the number of years between __MaxDate and __MinDate. We can now calculate the average purchase frequency and store this in the __AveragePurchaseFrequency variable. Since each row in the R06_Table source table represents a purchase, we simply need to use COUNTROWS to return the number of rows within the context in R06_Table and then we divide that value by the number of years over which our source data spans. This provides us with the frequency of purchases over the date range spanned in our source data (purchases per year). However, we want the number of purchases within a single year. Thus, we divide our value by the number of years in your source data again. We now have __AveragePurchaseFrequency as the average amount of purchases within one year's time.
We can now calculate the average value of purchases and store this as __AveragePurchaseValue. Next, we can calculate the average customer value per year by multiplying the average value of purchases, __AveragePurchaseValue, by the frequency per year, __AveragePurchaseFrequency. This provides us with the average customer value per year, __AverageCustomerValue.
In order to calculate the lifetime value of a customer, we need to know how many years that customer will be a customer of the business. In the absence of decades of historical information, we can use the customer churn rate to calculate this. In this recipe, we have specified a static churn rate of .2 or 20% per year in the Yearly Churn Rate measure. For an example of how to calculate churn rate, see the Analyzing customer churn rate recipe in this chapter. Since the churn rate defines the number of customers lost within a given period of time, in this case per year, then the average customer lifetime, __AverageCustomerLifespan, is simply the reciprocal of the churn rate.
We can now calculate the CLV by multiplying the average customer value per year, __AverageCustomerValue, by the average customer lifetime in years, __AverageCustomerLifespan, and then dividing this value by the number of customers in the context, __Customers.