How to Use Random Samples in Excel for A/B Testing HubSpot Emails

A/B testing emails can be critically important to help understand and improve the results of your email marketing. Unfortunately, for many marketers this functionality isn't native to their email send platforms. Many of our customers who don't have the Enterprise level of HubSpot run into this problem. That doesn't mean that they shouldn't benefit from the functionality of A/B testing, it just takes a little more elbow grease to make it happen. 

What is A/B Split Testing

A/B split testing is a great way to determine the best promotional and marketing strategies for your business. A/B is fairly straight forward: you test a control (version A) again a different version (B) to measure which is the most successful based on the metric you are measuring.you test two versions of the same asset, have it be an email, call-to-action or landing page, and see which one had better results. Most times, the metrics you are measuring are click-through rate and conversion rate.

This method of testing provides insights to what your visitors, prospects, and even customers prefer to experience in your marketing. Therefore, you can hone in what works and optimize further to yield greater more qualified results. 

How to Segment the Cohorts and Lists in Excel: 

1. In HubSpot, segment the entire list of contacts you want to send the email to using Lists and Export that list to Excel
2. Add a column called "Random Number" and enter the formula =RAND into the first row (this will generate a random number). Now drag this formula through the entire list, assigning a random number to each contact.  
3. Unfortunately, you can't filter random numbers in Excel. To work around this, =RAND generates a number between 0 and 1. You can use an IF statement to label cohort A or B. In our case, we have rand in cell A and use the formula - =IF(A2>=0.5, "A","B"). This will label any value less than 0.5 as a B and larger as an A. **Side note: If you run this formula on a cell that is generated through rand, the numbers will change each time you drag the formula, but you can paste special the values from rand and then use the IF statement on that column.
4. Take the percentage of your list you want Version A to be sent to and copy that into a new tab of the sheet titled Cohort A
5. Take the remaining percentage of your list you want Version B to be sent to and copy that into a new tab of the Excel sheet titled Cohort B
6. Save both the Cohort A and Cohort B sheets as .csv files 
7. Re-import both lists into your HubSpot contact database as static lists that can be assigned to both versions of the email and schedule the send! 

How to Use Your New A/B Tests:

There are two primary ways we recommend our clients leverage these A/B tests, that is, depending on the size of the email list the email is being sent to and their historical email marketing conversion rates. Generally speaking, we follow Hiten Shah's rule of thumb that in any sample we need to achieve 100 conversions to achieve statistical significance and we want to ensure that we'll reach that in our test. 

The easiest is to use a 50/50 split of the entire email list where one cohort receives Version A, the other Version B. A slightly more complex but sophisticated method would be to send both Version A and Version B to a smaller percentage of the list, for example 10% each, and then send the winning version to the remaining 80% of the initial list that has yet to receive the email. Just keep in mind that whatever smaller percentage the email is sent to is large enough that your results will be significant enough to draw meaningful conclusions from.