There are a lot of different levers at our disposal to help improve customer retention and spend. The relationship with each customer begins with the very first booking he or she makes, so the intro special is a great place to start.
Knowing how many customers never make it to the second class is a quick way to get a better understanding of how your pricing options are converting. However, this method doesn’t work as well with intro packages that include more than one visit- if customers are using both classes on the BOGO but never return, we want to know that too.
There are a number of ways to compare the success of intro offers to get a clearer picture of how well they are cultivating customers. Below, we describe a few different methods and how to get there using reports in MindBody.
For the purpose of this example, we are going to look at customers who entered between January and June and see what they’ve been up to since.
We’re happy to calculate either version for you- just click the link at the end of the article.
Did they return after 30 days?
Use: Attendance with Revenue Report and First Visit Report
1. Pull the Attendance with Revenue Report across all locations from January 1 through today and export to Excel. We’ll also need the First Visit Report from January through June.
2. Open the First Visit Report. This is going to be our home base. Create a new tab, and paste the entire Attendance with Revenue report into it. Name the tab “Attendance with Revenue.”
3. Heading back to the tab with the First Visit Report, we need to create a new column. In this column, we’ll basically be asking- “Did this person come back after their first 30 days at the studio?” Here’s the formula we need to do that:
Formula: “=COUNTIFS('Attendance with Revenue'!D:D,'First Visit Report'!A2,'Attendance with Revenue'!A:A,">="&('First Visit Report'!C2+30))”
English: Count the instances where the Customer ID on the Attendance with Revenue report matches the Customer ID of this row of the First visit Report, and the date is greater than or equal to 30 days after this client’s first visit.
4. Now that we know what happened to each individual customer, we can roll that up and look for trends. We want to see how many people entered through each category, and how many returned after their first 30 days.
Let’s create a new tab and call it “Summary”- we’ll put our findings here.
5. On the new “Summary” tab, we need to list every single unique category a customer could have entered through (so, not just the first class special- we’d want to include the single drop-in option, ClassPass, Gilt, etc). Rather than retyping every category, here’s a quick shortcut:
Copy column D (the column with the pricing options) from the “First Visit Report” tab and paste it into the first column of your “Summary” tab. Let’s name the column “Pricing Options.” Then, highlight the whole column, and under the header Data click “Remove Duplicates.” Voila! That leaves you with a unique list of pricing options.
6. In the next column on the Summary tab, we’ll count the number of customers who entered through each category.
Formula: ”=COUNTIFS('First Visit Report'!D:D,Summary!A2)”
English: Count the number of times this Pricing Option (in cell A2 of the Summary tab) was used on a customer’s first visit (column D of the “First Visit Report” tab).
7. Finally, we’ll calculate the retention by determining the percentage of the group that entered through each category who returned after 30 days.
Formula: “=COUNTIFS('First Visit Report'!D:D,Summary!A2,'First Visit Report'!G:G,">"&0)/B2”
English: Count the number of people on the First Visit Report who entered through the category highlighted in this row (Cell A2 of the Summary tab) and attended more than 0 times after their first 30 days. Then, divide that number by the total number of people who entered through this category in order to get a percentage.
How much do the customers go on to spend?
Use: Attendance with Revenue Report and First Visit Report
1. The first two steps here are the same as the last process. We’ll start by pulling the Attendance with Revenue Report across all locations from January 1 through today and export to Excel. We’ll also need the First Visit Report from January through June.
2. Open the First Visit Report. This is going to be our home base. Create a new tab, and paste the entire Attendance with Revenue report into it.
3. Our end goal is to calculate the revenue per customer, so we have to make sure to account for ClassPass bookings (if your studio uses ClassPass). A quick way to do this is to create a new column in your Attendance with Revenue report that includes an average price per visit for ClassPass, rather than $0. Often, that’s about half the cost of your ten-pack- but the rates vary widely by studio. In column V, use the formula below:
Formula: “=IF(I2="Classpass",[your ClassPass price],T2)”
English: If this cell in the Pricing Option column says “ClassPass,” then use this price. If not, use the price in the Rev. Per Visit column.
4. Now, we can sum the spend (based on attended classes) of each customer. On the “First Visit Report” tab, name a new column “Spend” and use the formula below:
Formula: ”=SUMIFS('Attendance with Revenue'!V:V,'Attendance with Revenue'!D:D,'First Visit Report'!A2)”
English: Sum the values in column V (our revised spend column in the AWR report, that includes an assumption for the price of ClassPass bookings) where the Client ID matches the one in this row.
5. As we did before, we’ll need to create a new Summary tab with the unique entrance categories (see step #5 above).
6. Finally, we’ll stay on this Summary tab to find the average spend of customers that entered through each category so that we can compare.
Formula: “=AVERAGEIFS('First Visit Report'!H:H,'First Visit Report'!D:D,Summary!A2)”
English: Calculate the average spend for any customers matching the criteria of this entrance category.
Questions on the process above? Email firstname.lastname@example.org, or click the button below for a free evaluation of your entrance categories.
This is the third post in a three-part series on lead generation. To jump to the first post, click here.