Why You Need to Pull Your Data OUT of your Membership Management Software (Project X)
First, eyes on the prize.
We’re using data science, user experience design, and content marketing to build the client base of CrossFit X. The goal is to get from the current member count (~280) to 350 paying members.
We’ve got my skills/know-how from working with dozens of individual founders, being a decent coach, building Again Faster, and scaling up Whole Life Challenge (along with $1,000 per month of CrossFit X’s money) to do it.
Our goals are three-fold:
Acquire new clients via a strong digital marketing presence + in-real life sales funnel. I’ll refer to this as “the funnel” or “D+IRL”.
Create a viral incentive for existing clients to refer their friends and family to the gym, putting referrals into the “IRL” portion of our funnel.
Create an incentive for existing clients to upgrade their membership to a higher tier, whether through ongoing commitment or simply by increasing the amount of money they spend each month with CrossFit X.
The first step in this process is NOT to spring into action by —
Messing with the website/brand/logo
Redesigning the prospect onboarding flow/sales process
Redoing the membership pricing structure
Sending emails to the members pitching our new whatever
Rather, we’ve got three immediate tasks to tackle:
Discover what data sources are available to us on current membership composition and behavior AND PULL IT OUT of the software (this will be the subject of this post).
Augment the data we’ve got — quantitative in nature — with qualitative data (the subject of our upcoming Discovery Survey).
Assemble the data into a cohesive whole to allow us to (a) understand what’s happening now, (b) spot opportunities for revenue growth and profit, and (c) drive future marketing and messaging.
This last two points are the most critical — we’re not interested in data for data’s sake. We’re interested in benchmarking where we are as a business so we can identify which levers we’re going to pull in order to grow the business. We’re also interested in the qualitative — how our members think about the business — to better hone our internal and external marketing messages.
Of course, we’ve GOT to get the data out. Unless our membership management software has some serious, badass integrations (with significant time spent by their dev teams on building useful APIs), it’s the most useful to us as simple spreadsheets — which we can then upload to email marketing software, custom audience creation on social, etc.
Toward that end, I began this week by doing a deep-dive exploration of CrossFit X’s member management software, attempting to see what information is available on the business right now.
A Quick Primer on Why You Need to Learn Data Analysis (and the Tool Set)
Throughout Project X, I’ll be using and describing many basic data analysis techniques. You may be tempted to gloss over these sections, especially if you’re a non-practitioner of the data-driven arts. My advice to you? Don’t. Skip. These. Parts. Acquiring the skills to download, assemble, and manipulate your membership/client data into a usable, insightful format will be the first step in making TONS more money (and you’ll instantly gain the superpower of not needing to bitch about your membership management software’s seeming inability to give you the exact report you need, because you’ll be able to build it yourself.)
Toward that end, do the following:
Buy a single-user license for Microsoft Office 365 so you have access to Excel. Do this even if you think you can hedge with Google Sheets or iOS Numbers (Excel is the power tool you need, and you can always upload to Google Drive later if desired).
Bookmark ExcelJet. Dave and Lisa Bruns have assembled a goldmine, my go-to resource when I need solid spreadsheet solutions.
Familiarize yourself with best practices for spreadsheet modeling (download). I learned with Rick Brenner at the Harvard Extension School, and will be putting in some asides throughout the Project to help you with this.
My data dive was successful, due to the fact that CrossFit X’s member management software has liberal sprinklings of “download to .csv” buttons throughout (these are magic). You’ll likely find these same buttons throughout your software, whether MindBody, ZenPlanner, or one of the myriad solutions targeted specifically at the CrossFit community.
Using them, I assembled a single Excel workbook (pro tip: immediately convert your .csv to .xlsx format using Save As) with three main chunks of data (sheets) —
Client Demographic Data: First, Last, Email, Gender, Phone, Birthday, Address, Join Date
Client Purchasing Data: First, Last, Email, Membership Tier, Rate ($) Per Month, Lifetime Value over Timeframe
Client Attendance Data: First, Last, Email, Attendance Total by Month, Monthly Attendance Average over Timeframe
For Purchasing and Attendance data, I limited my timeframe to January 1, 2018 through February 28, 2019, giving me a year’s worth of data. This was a conscious choice — at this point in Project X, I’m less interested in what has happened over the entire history of the business, and more interested in what’s happening now.
You’ll note the redundancy in my construction. I used First, Last, and Email in each of the three tabs in the workbook, making them the first three columns in each sheet. This gives me a crucial ability — when I want to assemble all the data into a single sheet (which we’ll do, enabling later Pivot Table magic), I can use the Excel function VLOOKUP to get it done.
FWIW, creating the above was about an hour of work for me. It will take you longer if you’re new to this stuff — until you get the superpowers below…
Two New Superpowers: VLOOKUP and the Pivot Table
You’ll want to stop reading at this point in the article and head on over to ExcelJet for tutorials on VLOOKUP and Pivot Tables. I employed the heck out of both these tools to get the data outputs I’ll discuss in the next section of this article.
As a sidebar on this sidebar — the best way to learn to use these tools ISN’T to watch these videos and then go have a snack. Rather, you need to actually attempt a VLOOKUP and a Pivot Table on your own data. It’s this interaction that will make sure the lessons stick. So…download a .csv and try a pivot. And if the acquisition of business superpowers is not enough incentive to turn you into a spreadsheet jockey, remember that we’re not messing with data for data’s sake — we’re doing this to make money from what we learn.
Do the following:
Download a .csv from your member management software
Run a Pivot Table to determine something basic against two data points — for example, get a COUNT of current clients in each of your membership offerings, along with the AVERAGE monthly amount paid by clients within each offering.
Once I assembled the data into a single sheet, with Client Demographic, Purchasing, and Attendance data, I ran a series of Pivot Tables against that sheet to determine —
Gender Breakdown: is our client base mostly male? Mostly female? We want to understand this primarily for future marketing efforts. In the back of my mind, I’m already thinking about social media advertising segmentation, and targeting our marketing by gender will be a consideration. In our case, 55% of the membership is female, 45% male.
Location: Which towns/cities do our client live in? This is another must-know for marketing. We’ll bias our advertising as well as any event marketing toward the towns where the bulk of our clients live. In the gym business this may seem like a no-brainer (as you “know” where everyone lives) but in our case, we have appreciable member populations from five different towns — only two of which I would’ve included in our forthcoming marketing if I hadn’t done the work/discovery. Put another way, I would’ve left out 30% of our potential target population going on instinct alone.
Membership Plan Breakdown: Central to our effort to drive upgrades for new and existing members, this information tells us what percentage of the client base is in each of our product offerings, as well at the monthly revenue attributable to each product offering. In the case of CrossFit X, just shy of 60% of clients (by count) are in the highest or second highest tier of offering (essentially an “unlimited” membership), leaving ample room for upgrade and/or new membership type creation – one of our core goals.
Importantly, we now have the data in a format that will allow us to utilize it in several important ways (that wouldn’t be possible if we hadn’t removed the data from membership management software) —
Creating further Data Points: Often overlooked, we’ll use the existing data points to create NEW data points, allowing us to determine important things about our clients. For instance, now that we have attendance by member by month over the entirety of 2018 and the first two months of 2019, we’ll create a “Dedication Factor” for each member by dividing their actual month-by-month attendance into the available monthly attendance (assuming available is 5 times/week). We’ll also create a veteran/newbie data point — if they’ve been at the gym more than 1 year we’ll call them veterans, if not we’ll call them newbies. You’ll see the utility in these new data points once we’ve added qualitative data (members’ attitudes, propensities, self-knowledge) — they’ll allow us to “slice” the data in important ways. For instance, do our veterans think differently than our newbies about the core value proposition of the gym? Do those with “high dedication” have a different inclination to refer friends and family to the gym than those with “low dedication”?
Segmenting members within Email Marketing Software: Usually, gyms (and other small businesses) use the “blast method” of email marketing, i.e. “we’ve got something to say, so we’re just going to say it to everyone.” This ignores the subtleties of effective messaging and new product offers — we’ll want to send (1) the right offer to (2) the right people (as determined by gender, dedication, membership level, qualitative interests/attitudes, etc.) at (3) the right time. Right now this might seem theoretical to you, but as you’ll see when we actually start marketing within CrossFit X, this approach is ESSENTIAL to efficacy.
Targeting new members using Social Media. Instagram, Facebook, and Google created incredibly robust targeting platforms, as you likely know. We’ll use our newly gathered data (like the location of our clients’ residences, their gender) and combine it with data from the next step of Project X (a qualitative Discovery Survey) to develop a very precise targeting algorithm for finding new leads for the gym.
Retargeting existing/lapsed members using Social Media. One of the most powerful features of Instagram and Facebook is the ability to tell the platform precisely who you’d like to see an ad. For instance, we could pull a list of former/inactive members and serve them an ad set specifically designed to get them back in the gym. We could serve our current “low dedication” members with an offer to meet with a coach one-on-one to develop an attendance and accountability plan to keep them coming to the gym.
Note, this is merely part one of our data assembly efforts.
In the next post, I’ll share the principles behind the upcoming Discovery Survey for Project X — a Google Forms-driven effort to learn more about our membership base. Once this Survey is complete (and married to our quantitative data) we’ll be able to serve current membership better, see the gym’s brand through their eyes, and give new life to our marketing, retention, and upgrade efforts.
Got questions? Shoot me an email at jon@jongilson.com (or if you think it will help everyone, post a comment below).
