Imagine that you’re deciding whether to enrol on Amazon’s Subscribe and Save service. It’s definitely a popular program but how will you know whether it’s having a beneficial impact? In theory it will increase your repeat purchase rate.
Amazon provides a bespoke report but it only shows how many people are signing up and how long they subscribe for. The problem is, they could be people that were already repeat purchasing your product.
To assess whether more people are repeat purchasing you need to know what your current repeat purchase rate is per product.
In this article I’ll walk through the steps you need to take to calculate your Amazon customer repeat purchase rate. I’ll also show you how to calculate this at a product level so you can see which products are more frequently repurchased.
Get strapped in. We’re going to take your Amazon marketing to a new level!
For the purposes of this exercise we are defining repeat purchase rate as the number of times that a customer purchased a product and made a subsequent purchase of the same product at a later date.
If you are considering enrolling on the subscribe and save program then you need to understand current repeat purchase behaviour to set a baseline.
Comparing this baseline against the repeat purchase rate AFTER you’ve started selling through Subscribe and Save will inform whether it has been a profitable move for you.
Knowing which products are more likely to be repurchased will help you to decide which products are suitable for Subscribe and Save.
It will also help you to run a smaller test before rolling out across your product range.
I’m sure there are other uses for repeat purchase data. Why not drop your ideas into a comment at the end of the article to share your wisdom!
Now the benefits of knowing product repeat purchase rates are clear let’s start by sourcing the raw data.
Note: There are some pretty funky formulas in this guide. Don’t be intimidated by them. Follow the guide step by step. You’ll find it easier if you use the exact same cells and column headings as we do.
To find your repeat data, all you need is one report from your Seller Central dashboard. Please note that what follows is designed for FBA sellers. The principles are the same for other types of sellers but you may need to reorganise your sales data to get it into a similar structure as what follows.
The raw data is stored here;
Reports > Fulfilment By Amazon > Amazon Fulfilled Shipments
We use this specific FBA report because it provides some key data points that other reports don’t.
Make sure you export the correct report otherwise you won’t be able to follow this guide.
Export the data.
Annoyingly it’s one month at a time. Consolidate the transactions into one file and you’re ready to get started.
You will need to export as much historical data as possible
So, now you have your consolidated data let’s begin to work out your repeat purchase rate.
This guide visually demonstrates how to identify repeat customer orders for individual SKUs from Amazon FBA data. This data has been modified to anonymise customer data. The recipient name data has been changed to a random customer code identifier. When performing this yourself it will show as a name rather than a slightly hard to read string of code!
I have used Google Sheets to do this analysis, Excel will of course work fine ( if not better) for this task as well. This analysis can also be broadly done with a pivot table, but I found to generate a completely thorough, easily read and detailed view this method worked better.
Once you have your combined data set it is time to get manipulating the data to extract the repeat customer info we are after
Add a new column after column AF called “SKU&Recipient name”, and input the formula =N2&Y2 and copy it down the whole column. This will create record of all SKU/Customer combos and allow us to see when customers have bought the same SKU more than once (without yet knowing if this in separate orders)
Add a new column (Called “Count Left”) next to the previously created one, and use the formula =countif(AG:AG,AG2) to generate a list of how many times our SKU/Recipient combos have been generated
We now create another column to concatenate SKU, Recipient and Shipment ID. Name it SKU&Recipient&ShipmentID The purpose is to identify if customer SKU purchases that were from the same shipment or were distinct orders. The formula in this case is, =N2&Y2&C2
Again we add a new CountIf column =countif(AI:AI,AI2). We will use this later to identify unique shipments.
Add filtering to the data, and then use this to remove all values equal to one in the SKU&recipient column AG. We do this because if this value is equal to 1 then there has been no repeat purchase
Now we create another column to the right of AJ “Count/Count”, which we will use to divide our two countif columns together “=AJ2/AH2”. The purpose of this is to filter out any shipments that contained more than one of the same SKU, and where a customer made no other purchases at a later date.
If the value of this column is equal to one, then it is a single purchase with multiple orders of the same SKU and not a repeat purchase. We can use filtering to remove these values, by removing all equal to one as shown below
We now have what we are after, a list of customer&sku&shipmentids that refer to repeat customer SKU order sequences in column AI. We can copy this column and paste values only (crtl shift v) into a separate sheet
We have our list of unique identifiers now, and we use an index match function to populate a data table from the original tab to create a useful data table.
We can add whatever columns we think are useful, below I have added the SKUs associated with the repeats and also the customer first names (redacted)
The formula used =index(‘All raw data’!A:AW,MATCH(A2,’All raw data’!AI:AI,0),14).
Change ‘All raw data’ to the name of the sheet you are using, or change your sheet name to ‘All raw data’ for this to work.
The number 14 in this formula refers to the 14th column in the All raw data worksheet, adjust this value to bring up different columns accordingly.
For example, Buyer Name is column L so we change 14 to 12.
Although the table we have is useful for other purposes, we use the unique function on our buyers name =unique(C:C) to create a list of individual customer names who have repeat orders in column D, which we can count up to arrive a total of repeat customers.
In this instance it is 72.
We now want the total value of customer/sku order instances. We take this from our ‘All raw data’ worksheet, using =unique(‘All raw data’!AG:AG) in Column F.
We then in column G add the function =COUNTA(F2:F9000) to get a total. Finally in column N we divide E2/G2 to get our reorder repeat rate.
In this instance we can say, over the date range in the data 2.10% of customers re-ordered the same product on a subsequent date. Now you can see how this helps to benchmark Subscribe and Save performance.
Once you launch Subscribe and Save (and give it enough time for people to re-order) you can run the same report to see whether the repeat purchase rate has increased.
We aren’t done yet though! As we can now find out the frequency with which SKUs are being reordered. Very useful info for deciding which products to choose for any Subscribe and Save plans.
In column O =index(B:B,MATCH(D2,C:C,0)) Column B will be showing SKUs, Column C showing Names, and Column D showing Unique names. Paste the formula down to match the column D length.
Cell P2= =countif(‘All raw data’!N:N,O2)
Cell Q2= =countif(O:O,O2)
Cell R2= =Q2/P2
Using a pivot table this data ( in columns O to R) can be summarised neatly as shown below. Simply highlight the columns O to R and select Pivot table from the data drop down. Add re-order SKUs as the row field, and Average of the other fields in the value section as shown below
So there we have it. Both the account repeat purchase rate and the product repeat purchase rate.
By enrolling on subscribe and save you should expect to see these rates improve. Don’t forget that subscribers get between 5%-15% discount for subscribing. You’ll need to see a significant improvement in repeat purchase rate and sales volumes to account for the lower sales price.
The analysis should put you in a much stronger position to assess the effectiveness of Subscribe and Save and give you pointers towards where performance can be improved.
Knowing your account and product repeat purchase rate may also benefit you in other ways. We’re always keen to find out what interesting things people are learning from their data. Why not share your thoughts in the comments section below.