If you are not familiar with the RFM model (Recency, Frequency, and Monetary) and how it can help drive customer segmentation, we recommend you read How to Leverage the RFM Model to Drive Customer Segmentation before you read this post.
This post tackles what your team needs to know to implement an RFM model to help drive your Salesforce Marketing Cloud (SFMC) strategy and customer segmentation. Continue reading to learn:
- How to leverage existing customer and transaction data to build your RFM model
- The basic SQL skills necessary for this undertaking and how your team can learn them with free and online resources
- How to calculate RFM scores
- How to sync Google BigQuery with your Salesforce Marketing Cloud instance
Building an RFM Model: Leverage Your Data
Step one in creating your RFM model is to figure out what data exists inside of SFMC. Does your organization already store transaction data in SFMC? If not, what platform does that data live in and which group (internally) owns that platform? It’s likely that your company has a wealth of data about your customers and their purchase history that isn’t currently available in SFMC.
Figure out what data is needed for your team’s segmentation strategy, who owns that data, and then work with those teams to determine how that data can be made available in SFMC. Your team may need a one-time batch import, or a daily or weekly automated import may be necessary to stay up to date.
During this process, make sure only data points necessary for your segmentation strategy are imported into SFMC. Also ensure the data is properly cleaned, with any inaccurate or duplicate records removed. Depending on the resources available, a tool like Google BigQuery might be the right solution to combine disparate datasets into a single file that contains only what your team will need in SFMC (bit.ly/bigqueryinfo).
Building Your RFM Model
Now that the data your team needs is available in SFMC, it’s time to start building your RFM model. This is where having some basic Structured Query Language (SQL) skills are necessary. SQL is a language that’s used to communicate with relational databases.
If you’re not familiar with SQL, you can think of all of your lists and data extensions inside SFMC as tables in a relational database. The segmentation and filtering tools you’re familiar with in SFMC are basically creating and executing SQL queries based on your inputs. The same logic can be used when creating groups and filters to write much more powerful raw SQL queries in Automation Studio. Here are our top three favorite SQL learning resources:
- Kahn Academy (bit.ly/Khanasql)
- Solo Learn – SQL Fundamentals (bit.ly/solosql)
- Mode Analytics (bit.ly/modeinfo)
If you’re not familiar with SQL, this may sound intimidating but the basics of SQL can be learned in a day or two. Learning SQL will unlock a ton of powerful new possibilities for your team when it comes to segmentation and reporting in SFMC. What is often most efficient and effective for many organizations is to bring in an outside party to help set up an RFM model initially and then have at least two members of your own team take basic SQL training.
Calculate Your Scores
To calculate an individual’s Recency, Frequency, and Monetary scores, you’ll need to write a query that compares each customer’s purchase behavior to that of your overall customer base, and assign a score based on which quintile they land in for each dimension.
There is no one-size-fits all RFM model, so consider the nature of your business when determining the weightings for each of the three variables. For example, if your customers tend to make multiple purchases within a single day, you may want to consider scoring Frequency based on the total number of days a purchase was made, instead of the total number of transactions, to get a more accurate idea of how often a customer is purchasing over a given time period.
An example of where you may want to tweak your Frequency scoring is the hospitality industry. For example, if Jon stays at the XYZ Hotel three times in the month of June, and each stay is one night, then his Frequency score would be 3. However, if Greg also stays at the XYZ Hotel three times in June but for five days each stay, he’ll also have a Frequency score of 3.
In this instance, it might make more sense to focus on the total number of days that a customer stayed in the hotel instead of simply the number of transactions. Most likely, your team will want to communicate with a guest who has stayed 15 days in the past month slightly different than a guest who has spent only 3 nights. Decide what makes the most sense for your business and model your Frequency scoring accordingly.
Syncing Marketing Cloud Data with BigQuery
With all of your data available in SFMC, you can get surprisingly far with some clever design of staging data extensions and basic SQL knowledge. We’ve successfully run daily queries on data extensions with millions of records to calculate RFM scores, so your team isn’t likely to run into any limitations with small to midsize data sets. Building your queries inside SFMC can actually be easier if you are new to SQL. By chaining together multiple basic queries instead of using more complicated joins and other resource-intensive functions, you can keep each step in your process simple and easily understandable. Making changes will also be easier and less error-prone when you don’t have to decipher large chunks of SQL.
Alternatively, leveraging a tool like BigQuery combined with SFMC is best when you have larger datasets to sift through. SFMC is not a data warehouse and shouldn’t be used as one. In more advanced cases, your team should leverage a tool like BigQuery to manipulate your data before bringing it into SFMC. Not to mention, SFMC requires you to create a data extension to hold the results of every query, so sometimes it can be time-consuming to explore data in SFMC.
BigQuery allows your team to work quickly and get sophisticated with queries and calculations, and send back to SFMC only what you need for segmentation and personalization purposes in your campaigns and journeys.
Google BigQuery
If you decide that a tool like BigQuery makes sense for your organization, there are a few options for keeping the two systems in sync:
- Manual exports work for one-time syncs. Note: if you need to update SFMC regularly, it will be time-consuming and potentially error-prone.
- Tools like Stitch can help you sync Data Extensions with Google Cloud Storage and BigQuery. Stitch is useful if you don’t have the technical resources to keep systems in sync yourself, but the sync can sometimes break without warning (bit.ly/stitchinfo).
- If you do have a developer(s) on your team, you can easily use the SFMC API and custom scripts to build exactly what you need to keep data moving between platforms.
Wrap Up
Hopefully, this article has provided you some actionable insight for you and your team on how to build an RFM model for customer segmentation to power your SFMC strategy.
Have questions about how to build your RFM model or how to leverage your data across platforms? Sign up for our newsletter! We send out a monthly recap of our latest Salesforce Marketing Cloud content, including articles on security best practices, actionable insight on Marketing Cloud optimization for enterprises, and more.
