Visokio website     Downloads     Video tutorials     KnowledgeBase  
Analytics: Recency Frequency Value? - Visokio Forums
Analytics: Recency Frequency Value?
  •     grahamb March 15, 2013 8:46AM
    I am unsure if this function is planned but it would be good if it could be added. A function for automated recency, frequency, value analysis within the software so you can select a transaction table relating to customers' orders and then decide the number of categories per measure - say 5, then generate for each customer three new fields grading recency 5 to 1, frequency 5 to 1 and value 5 to 1. This will then create 125 segmentation groups for targeting. If 3 categories selected, then that will give 27 segments. With this analysis as well as coding each customer record with the RFM bands it would be useful to create a pivot table to show recxency by frequency, and within each cell a bar chart breaking out the value measure
  • 11 Comments
  •     paola March 15, 2013 10:17AM
    This is already possible - you can use functions, that will evaluate the data and update results every time new transactional data is added to the set, also update the recency value, responding to the date change:
    IF(
    DATEDIFF([Date],TODAY,"day")<2,1,
    DATEDIFF([Date],TODAY,"day")<5,2,
    DATEDIFF([Date],TODAY,"day")<10,3,
    DATEDIFF([Date],TODAY,"day")<15,4,
    5)

    The above formula calculates the difference in days between the purchase date [Date] and function TODAY, then allocates the score between 1-5, depending on the result.
    Frequency :
    SUBSET_NONEMPTYCOUNT([ProductID],SUBSET2([ProductID], [CustomerID]))
    This formula counts how many times customer bought certain product. Please be aware that the subset formula will populate each field, so for each record with the same combination of [ProductID][CustomerID] there will be the same result. Duplication can be eliminated through aggregation, by choosing "unique values" for this field.
  •     grahamb March 15, 2013 11:21AM
    Thanks. Is there some code that you have already written that will do the whole process in one go. Not product specific, just working on transaction file to take orders for a time period, set number of categories for each of R, F and V, add fields to customer table, then populate each with a category based on which decile (or band) that customer falls into? More detail...

    RFM analysis is a technique used to identify existing customers who are most likely to respond to a new offer. This technique is commonly used in direct marketing. RFM analysis is based on the following simple theory:

    • The most important factor in identifying customers who are likely to respond to a new offer is recency. Customers who purchased more recently are more likely to purchase again than are customers who purchased further in the past.

    • The second most important factor is frequency. Customers who have made more purchases in the past are more likely to respond than are those who have made fewer purchases.

    • The third most important factor is total amount spent, which is referred to as monetary. Customers who have spent more (in total for all purchases) in the past are more likely to respond than those who have spent less.
    How RFM Analysis Works

    • Customers are assigned a recency score based on date of most recent purchase or time interval since most recent purchase. This score is based on a simple ranking of recency values into a small number of categories. For example, if you use five categories, the customers with the most recent purchase dates receive a recency ranking of 5, and those with purchase dates furthest in the past receive a recency ranking of 1.

    • In a similar fashion, customers are then assigned a frequency ranking, with higher values representing a higher frequency of purchases. For example, in a five category ranking scheme, customers who purchase most often receive a frequency ranking of 5.

    • Finally, customers are ranked by monetary value, with the highest monetary values receiving the highest ranking. Continuing the five-category example, customers who have spent the most would receive a monetary ranking of 5.

    The result is four scores for each customer: recency, frequency, monetary, and combined RFM score, which is simply the three individual scores concatenated into a single value. The "best" customers (those most likely to respond to an offer) are those with the highest combined RFM scores. For example, in a five-category ranking, there is a total of 125 possible combined RFM scores, and the highest combined RFM score is 555.
  •     paola March 15, 2013 12:20PM
    The above formula for recency does exactly that - calculates the days since purchase, then allocates the rank value accordingly. I added the explanation in previous entry.
    Regarding the third value - Total Spend per customer:
    SUBSET_SUM([Value],SUBSET([CustomerID]))

    Similar IF function can be used to allocate values 1-5, according to the total spend.
    Final score concatenation can be done through a simple formula :
    [Rank Recency]+" "+[Rank Frequency]+" "+[Rank Spend]
  •     grahamb March 15, 2013 12:52PM
    OK Paola. Will have a crack, thanks
  •     grahamb March 15, 2013 1:29PM
    Hi Paulo. Is there a simple function to extract the initials from a name field - graham barlow to GB
  •     paola March 15, 2013 2:12PM
    LEFT(text, num_chars) can be used to return the first x characters.
    LEFT([Name],1) - returns just the first character

    To retrieve initials from a field that contains name/surname you can use REGEX expressions, or mix of several text-mining functions.
  •     grahamb March 20, 2013 5:22AM
    Having further explored RFM I think that an automated component could be created as a new tool for the user to more easily define their RFM needs. See attached document for some screen shots from another application.
    Attachments
    RFM.docx 316K
  •     grahamb March 20, 2013 12:22PM
    Updated doc with a few extra screen RFM shots. Note the nested table - which would be good to recreate in Omniscope
    Attachments
    RFM.docx 364K
  •     paola March 22, 2013 7:53AM
    Omniscope incorporates at lot of general-purpose analytics routines through its round-trip integration with the ever-expanding open source R libraries.

    Generally speaking the process for grouping records/customers into a pre-defined number of discrete groupings based on similarity or differences in defined fields is called clustering.

    Here is a video showing Omniscope with its canned R-script for K-means clustering:

    http://tc.visokio.com/videos/?name=RIntegration&title=R+statistics+operation&lang=gb
  •     grahamb March 22, 2013 8:05AM
    Interesting video Paola, and useful to incorporate.

    Are there other R components you will add - things like 2 step and hierarchical cluster, with dendogram output charts, and CHAID type tree analysis? Also regression?
  •     tjbate March 22, 2013 9:53AM
    Graham - We can add any scripts that people can find/supply and validate...let's continue the topic of expanding Omniscope's library of 'canned' R scripts on separate Ideas threads...one thread per script.

    Please start Ideas: R - 2 step and hierarchical cluster?

    etc...

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership