You’ve grown your independent Shopify store to more than $500k per year, with a marketing engine spanning Facebook, AdWords, and Display Ads. You have a budget of more than $200 / day for ads.
But in the back of your mind, you’re worrying if cost per sale can be improved. You know that the only way to grow sustainably is to have good cost per sale.
So you hired a consultant or an agency to get it lower.
But even after the audits and campaigns, the results have been volatile and inconsistent.
Now you’re frustrated. How can you ever scale with bad cost per sale?
But there’s more to ROI than just cost per sale.
There’s another part to the ROI equation –the average order value (AOV) or customer lifetime value (CLTV).
Here’s the typical formula that you’ll see around the internet:
If you divide it with the number of orders, then this is what you’ll get:
So how do you improve AOV?
Improving that is a matter of increasing the basket size. This is how Amazon makes their numbers work –they always attempt to increase their basket size using the “Frequently Bought Together” feature (see below).
This is what marketers call “upselling” or “cross-selling”. Here’s Ezra Firestone’s take on it:
“If you have a business driven by the ad model, you MUST understand upsells, cross sells… it’s what’s allowed us to grow to $1.5million/month.”” (via Unofficial Shopify Podcast)
The next question then is “what are the right products to bundle?”
To make that decision, you need to do Market Basket Analysis.
In this article, you’ll learn how to do that using the R programming language.
Don’t worry, while doing a bit of programming sounds intimidating, the great thing about R is that there are a lot of packages, which makes it easy for analysts and marketers alike to just “plug and play”.
I’ll help you do this –step-by-step, from installation to analysis.
How Final Execution Will Look Like
By the end of this article, you should be able to decide what products to use for your upsell funnel, which should look a lot like the image below (if you’re using One Click Upsell or CartHook Funnel):
Image Source: CartHook Funnel
Alternatively, the insights from this analysis should equip you with how you should structure your Facebook Carousell ad:
If you go through the steps below, the final output will be rows of bundled ideas, each with a specific probability of success for each one (under the “confidence” column):
Now here’s the approach we will take:
- How to Set Up R
- How to Equip R to Help You Increase Average Order Value
- Tidy Up your Shopify Data to Make it Compatible with R
- Organize your Shopify Data to Make it Compatible with ARules
- How to Run the Analysis to Find Bundle Ideas for Higher AOV & Customer Lifetime Value
Follow Along with Mock Data
If you think that you need some mock data to help you feel that “you’re getting it right”, download the mock data that I used for the package here. If you do use my package, then you can skip to Phase IV.
Phase I: Covering the Bases – How to Set Up R
Go to https://cran.r-project.org/ and download the correct binary file for your operating system.
For Windows, use “base”:
For Mac, use use the “.pkg” file:
Once you’ve installed it, open it up. You should see an interface that looks like this:
Phase II: How to Equip R to Help You Increase Average Order Value
Now let’s install the tools required to run association rules analysis.
Go to Packages & Data, then click on Package Installer.
Then type in “arules” and click “get list”. Once this has been generated, select “arules” then click “install selected”.
The second package that we need to install is “gdata”. Do the same package installation procedure as the one we did for arules.
Now your R setup is complete. Let’s reformat your Shopify data to make sure it’s fit for use.
Phase III: Tidy Up your Shopify Data to Make it Compatible with R
First, go to the “Orders” section in the left panel of your Shopify dashboard. Click on “Orders” again then click “Export”.
Select “All Orders” and “CSV for Excel, Numbers, and other spreadsheet programs”. Click on “Export Orders”. This will arrive in your email in around 10 to 15 minutes.
Once you’ve opened the file, you’ll notice a huge amount of data. You don’t need every single point of data here, so let’s clean it up.
All you need to have are two columns:
- A column which contains an identifier for each orders (such as an order number)
- The products which were bought or sold
In my spreadsheet, this means that they correspond to “Name” and “LineItem Name” (as seen below).
After you’ve cleaned up your data, it should look a lot like this. I changed the column names into one word to make it easier for me to run commands in R later on. I suggest you do the same for your data (I would suggest TransID and ProductName for now).
Phase IV: Organize your Shopify Data to Make it Compatible with ARules
Now it’s time for us to get our hands dirty.
Open up R.
The first thing that we need to do is to change the working directory or the directory where we will get our shopify data.
Just go to Misc > Change Working Directory.
Since my data is in my Downloads folder, I just used that for our example.
After updating the working directory, the next step is to load the libraries. In our case, that’s ARules.
To load up ARules, you just need to run this command:
There will be some red text that will appear. If you see “attaching package: ‘arules'”, you’re fine.
After running that command, you need to upload your Shopify data.
To do that, run this command:
This reads the CSV file and assigns it to a myData variable.
Now run this command so you can get an overview of data.
As you will see, there are multiple instances of each Transaction ID. That’s because Shopify keeps each product separate, which leads to multiple Transaction IDs.
In order to analyze each transaction (or basket) properly, you’ll need to aggregate duplicate Transaction IDs into one.
This is how you can aggregate it:
If you find this confusing, just use this template:
aggregatedData <- split(myData$<ColumnNameOfProductList>, myData$<ColumnNameOfUniqueID>)
Now let’s see if the data has been aggregated properly. Run this command to see the first 5 data points of the data set:
You should see something like this:
Finally, let’s coerce or convert the data into a format that can be accepted by ARules:
Now run summary(txns) and you’ll see what the most sold items are. Here are my results using the mock data:
Just from this summary table, we already have some insights from our 26k+ row mock data:
- The most frequently purchased items are Isopropyl Alcohol, Clotrimazole, and Nitrogen
- Our largest order contained 28 products
- 46% of our orders have 2 or more products
Now let’s move to the fun part –finding bundle ideas that improve ROI!
Phase V: Find Pockets of Gold with ARules-Based Market Basket Analysis for higher Average Order Value & Customer Lifetime Value
We’ve spent quite a bit of time cleaning and converting data.
Now it’s time to run the algorithm.
You should be excited.
Type in this command (you can copy then paste this if you’ve been following along):
Rules ← apriori(txns,parameter=list(supp=0.00025,conf=0.4,target=”rules”,minlen=2))
A successful run of the command means that it has written 1 or more rules (i.e. bundles). Using the code above, I was able to get 27.
If you want to see more bundles, decrease the “supp” number. If you want to see less bundles, increase the “supp” number.
Finally, run this in order to get the probabilities of success for a selected bundle:
This is what you’ll get with the mock data:
As you can see, I have pointed out bundle ideas that give a 61%, 77%, 66%, and a 100% chance of success.
Once you’ve identified the bundles that make the most sense to you, install an upsell app like One Click Upsell and CartHook to start getting more out of each customer.
For maximum benefits, I would also use an A/B testing software like Optimizely and VWO so you can cycle through the various recommendations and see what works best.
Improving the ROI doesn’t mean that you should cut your cost per sale.
Oftentimes, the easier approach to increase ROI is to increase Average Order Value.
What we’ve shown you is a statistical approach towards that goal.