How to get your raw data to perform RFM Analysis (Recency, Frequency, Monetary)

Highlighted
New Member
1 0 0

I am posting the following for others who are looking to download raw data to perform a Recency, Frequency, Monetary (RFM) analaysis on their cutomers. RFM analysis helps identify best-to-worst customers to whom targeted campaigns can be utilized to realize additional purchases (best customers who are likely to make another immediate purchase) and lift customers who are not likely to make another purchase via enticement campaigns with or without discounts.

Please note, the following is to help you get the data from Shopify and not a tutorial on the actual RFM analysis.  If you need help with the actual RFM analysis, hit me up... I'm happy to have you pay me to do the work: info at analyticadata dot com.  I've also pointed to a few RFM resources below that I found useful.

By way of background, I am cofounder of Analytica Data Company based in Encinitas, California doing work for Biostrap.com who sells their advanced biometric system using Shopify.

As part of our deliverables, we developed a RFM analysis to identify customers who are likely to make an accessory purchase ~ Biostrap is sold as a system (includes biosensor, wrist strap, shoe pod, charger, cables, and software) or just the Biostrap Set (biosensor + wrist strap + charger + sofware ~ no shoe pod or other accessories). 

Shopify has a black-box RFM analysis built into their reports ~ the worst customers report is currently available for free but the best customers report requires you to have a premium account.

If you wish to conduct your own RFM analysis, access to customer contact and customer orders data is a bit tricky as I discovered with the help of Mike, a super cool Shopify Guru, who happened to stumble across the detailed Orders report that helped give me access to the two separate datasets needed to conduct our RFM analysis.

Following are steps I used to download the two datasets needed to conduct RFM analysis.  The two datasets are your Customer Contact and Orders data sets that must be downloaded separately, then joined together using customer email address as the common variable.

1. Download the customer contact data via the Customers link on the left side of your Shopify admin page and save as *.csv file. 

2. Download Orders data from the Orders menu on the left side of your Shopify admin page and save as *.csv file. There are two types of order downloads available ~ orders and transactions.  The transactions option prvides all the details along with customer email.  You will use customer email in transactions to merge with customer data to run the RFM analysis.

Now that you have both the customer contact and detailed order transactions datasets, you can merge them using email address as the binding variable.  This actual setps depends on your expertise with tools you're comfortable using.

We use open source data analysis tools such as R, Rstudio, and Radiant for our work.  I am sure there is probably a way to merge these datasets and perform that actual RFM analysis in other applications such as Excel, but I won't get into the actual analysis here.  There are plenty of resources on how to perform RFM online and one of my favorites is from the Database Marketing Institute. Another good resource is this tutorial from Putler. A final one is this blog post by Omniconvert with a link to a Google Sheets template.  

Like I said before, Shopify offers their own black-box version of best/worst customers that's good enough to use. However, if you want more detail for your analysis then you need to download the raw data and find someone (like me!) to perform the analysis for you.

Best of luck.  Let me know if you have questions or comments if you found this useful.

0 Likes
Highlighted
Tourist
9 0 1

Thanks for your informative post.

0 Likes