Calculating The Average Time Between Orders For Each Customer
How do you get repeat customers? If you can convert your 1st time buyers into repeat customers and keep up that momentum. Then you are going to build a sustaining business over the long-term.
Video Transcription Below. Enjoy and don’t forget to leave your comments below.
My name is Duane and hello from beautiful Vancouver. You may remember me from such conference stages as MozCon, BrightonSEO, HeroConf and SMX Advanced.
Your customer data and what each person buys is the most important piece of data that a business can have. Look at Amazon and how the brand obsesses over testing and understanding who is buying and what each person is buying. A few weeks ago we talked about increase your ecommerce store revenue. This time we wanted to look at how often people are buying from you and understanding the data and value of repeat customers. Your business will live or die from repeat customers.
I was on the phone with a new client going through some changes in their account and what we had planned their shopping campaigns. Towards the end of the call, our auto client asked:
How many days between a customers 1st and 2rd purchase? They went on to say they felt it was long but didn’t have the data on it and wanted to understand when they can sent follow up emails.
Defining What You Want To Know
This is not normally a question we get from clients in their first 3 months with us. I told our client I didn’t know the answer but that I could build them a report to look at this over time. To answer this I needed to download data from Shopify for repeat customers and when they made each of their purchases. The reason we needed both of these piece of data was to figure out how many days between a 1st and 2rd purchase. Some other similar questions a client might ask include:
- How do we retain customers?
- How do we increase repeat purchases?
- How can our customer retention be improved?
- How can online stores maximize repeat purchases?
- How can we retain customers & encouraging repeat purchases?
The above questions all revolve around understanding who is buying from your store and how often they are buying. You can even look at what they are buying but this post won’t cover that last bit. Our client is using Shopify, which is a platform many clients we work with use. You can still do the following on Magento and WooCommerce, though you may need to do a few more steps.
Getting Your Data In Shopify
When you login to your clients Shopify store, you are going to need to get repeat customer data as well as all your order data. If you click on Customers in the left hand navigation and then on the following screen, click on Repeat Customers. This will filter out all your customers who have only purchased once. When you import this file into GSheets (or excel), it should have customers first & last name, email address, number of orders, and total money spend. Any other data you can delete as you won’t need it for this report. Call this tab Repeat Customers.
If you did not have the Repeat Customers filter, you can also pick the Filter dropdown beside the search box and select Number of Orders and then greater than and pick a number larger than one. This will filter out customers who have multiple purchases above that number. I.e. if you said greater than 3, you would only see customers who have made at least 4 purchases from your store. This only makes sense if you have thousands, if not orders in the hundreds of thousands or even millions.
The step next is going to Order in your store and downloading all your orders from day 1. Don’t forget to delete any test data you may have in there. Make sure you pick All Orders and CVS when you export your data.
Upload it into a new tab in your GSheets (or excel). Call this tab “All Orders”. The only data we need in this tab is Billing Name, Email and Create At, which is when a customer placed their order. You’ll need to use Text To Columns to separate the date, time and other numbers. You only need to date in the Create At column. Lastly, make sure you sort your date range from your first order at the top to the last order at the bottom. If you don’t do this, you’ll have a harder time figuring out the number of days between orders for each customer.
That is it for data from your Shopify store. Everything else lives in your GSheets (or excel), which we call Repeat Customers – ABC Client Name. We try to standardize all worksheet we use when working with clients. Now you need to build out your template (see below). Some working knowledge of excel and formulas is required beyond this point.
Create a new tab called Merged Customer Data. Label your columns headers as following:
Repeat Customer Email
Days Between Order 1 & 2
Leave Blank (column F)
All Order Emails
Now we just need to populate some columns and fill out a few formulas to make this work. Copy your emails from the Repeat Customer tab and paste those in the Repeat Customer Email (column A). Copy your Email and Create At data into the All Order Emails and Created At (columns H & I), which should be the last two columns on your worksheet.
Now we are left with VLOOKUP formulas, which is how you tell Gsheet to find a piece of data in a large data set. The data you might want could be an email address, order number or amount and even the day of week someone bought a product. We need to place the VLOOKUP formula (see below) in cell B2 and tell Gsheet to look up the email address from A2 in the data rage. The data range is going to be the All Orders data we put in columns H & I above. The VLOOKUP formula tells Gsheet to tell us what date is in Column I.
=VLOOKUP(A2,$H$2:$I$5497,2,0) <– This Goes in Cell B2
What Does This VLOOKUP Mean?
A2 = this is what you need to find in your data set. This doesn’t have to be an email. It could be an order number, someone’s name or day of the week
$H$2:$I$5497 = This is the data range you need Gsheet to look at for the email address above. In our case it’s column H & I. The first column should always be the data you are going to match to cell A2. If you don’t have 5,497 rows then your number would be smaller ( or bigger) depending on your data range.
,2 = This tell Gsheet to look into the second column of the data range for the date. If you didn’t have the All Order Email and Created At columns next to each other. If there was a column of data between them for example, this would be a 3 instead of a 2. That way Gsheet knows to look 3 columns over.
,0 = Tells Gsheet what to do if it’s blank
Once the VLOOKUP formula is in cell B2, drag the formula down to the last row of Repeat Customer Email addresses.
You now know when each repeat customer made their first order with you. That’s important but only half of what we need. We really need to know when each customer made their second order. We are going to do another VLOOKUP for order 2. Before we do that though, we need to build a Helper Column to tell us if this is the 1st or 2rd time a customers email address has appeared in the data range Coulmn H & I. If you ordered your All Order data by the date then the second time an email address appears, should be the second order for that customer.
To Build a Helper Column, you need this formula: =H13&”:”&COUNTIF($H$2:H13,H13)
H13&”:” = This says take the email and the colon and form a key, which will help us find out if this is the 1st or 2rd time an email in our data range appears.
&COUNTIF($H$2:H13,H13) = This says count the data range and tell me if this email appears. Each time the email appears add a 1, 2 or 3..ect after the colon above. If you see an email:2 then you know that’s the second order your customer made. Getting this right is important as we are going to tweak the VLOOKUP formula from above and only have Gsheet look for email addresses that have a email:2 in them for the second order a customer made.
In order to find emails addresses that are associated with a second order, you need the following VLOOKUP in your Order 2 column. This looks familiar but with a tweak combining our two formulas above.
A2 = the customers email address we want to look for.
&”:” = this is the colon we placed in the Helper Columns above.
&2, = this is the number you want to find after the colon above. A &2 means we are looking for the second order a customer made. If we had a &3 here, then we would be looking for any customers that placed a 3rd order with our store.
$G$2:$I$5497 = still the data range we are looking through. This now includes the Helper Column we built above.
,3 = This tell Gsheet to look into the third column of the data range for the date. Since we added the Helper Column above, we now need to go 3 columns over to find our 2rd order for each customer.
,0 = Tells Gsheet what to do if it’s blank
Similar to when we found our 1st order for each customers, drag the above VLOOKUP formula down to the last row of Repeat Customer Email address for the Order 2 column. If you want to find the 3rd order for each customers, then you need to tweak the VLOOKUP formula above by replacing &2 with an &3.
To find the number of days between order 1 and order 2 for a customer, you simple need to tell Gsheet to subtract column C from Column B (i.e. =C3-B3 ) and that is the number of days between each orders.
There will be some outliers in your Days Between 1st & 2rd Order column data. For our client we removed the outlier data and focused on customers who ordered within 90 and 180 days. We looked at the average number of days between an order and are now testing different email sequences. Our auto ecom client has a niche set of products and knows their inventory and SKUs really well. The long term plan is to set up emails based on what each person ordered. If a customer ordered product A then we send them an email for product B and vice versa. The goal is to get customers to make a second order and have it happen sooner.
This goes back to our post on up/cross selling your customers to increase your ecommerce store revenue. This can be done through email or advertising or a combination of both. That is it for this week. See you next time.