Modelling – Sweet mother of fudge it’s powerful

I’m not talking about the kind of modelling that lets you take advantage of naïve hotties on your casting couch, unfortunately. The kind of modelling I’m talking about is business modelling. And what it lacks in hotties on your casting couch, it makes up for in ridiculous amounts of money it pulls from your businesses (for those of you reading who actually HAVE businesses).

Here are the premises that I’m assuming – If these don’t fit your business, this article won’t be useful to you:

1. You have a business
2. Your business is built on repeat business
3. You maintain a database of customers (or clients, or patients, or whatever) and their financial history with your company
4. You want to make more money

Okay. If those don’t fit, move to the next article, because this one will bore you.

There are lots of really simple ways to model, and depending on the data you have available, you can also get into some really esoteric & wacky statistical analysis to show you links between customer behaviour & increased money.

Like most things in life, the simple methods are the ones that have the greatest leverage (IE doing them will make you a LOT of money), while the complex ones give very marginal gains. Or, to put it another way, you only need to worry about the highly complex modelling once you’ve maxed out the very simple models that we’ll be talking about (and very few people have done that). So let’s look at a few simple models.

The first model you want to create is the Lifetime Value of a Client model – It’s the one we’ve all heard about a billion times, but rarely implemented. Again, this is a useless exercise if you don’t have a running concern, but if you’ve got a nice backlog of data, then pull it out and figure out how many visits an average client comes in for, and how much they spend. (Later, you can do separate analysis to determine if people who come in less frequently spend more, or if there’s any indication that there’s a ‘per visit maximum’ people will spend, etc. But for now, just figure out how often they come in, and how much they spend).

Now comes the fun part. You want to dive in DEEP to these numbers. Don’t just accept them at face value. After all, it’s great knowing that when somebody comes in they’ll have an average of 38 visits. But you know that averages are dangerous… So start asking better questions. What is the average visit number if somebody comes in a 2nd time? A 5th time? A 10th time?

What do the visit & spending profiles of people look like based on their age when they start doing business with you? You might find out that somebody who’s over 40 will be 3X as valuable to you as somebody who’s under 25 – that’d be nice to know for when you’re crafting marketing & referral campaigns.

Now, exactly how you get these ever so useful numbers will depend on the format of your database, of course. What I’ve found is that for non-tech geeks like myself, it’s often easiest just to pull all the relevant data you want to look at into a single excel page, and then manipulate from there. Of course, however you want to actually get the data is up to you, but I’m going to give a super low-tech example to walk through.

So for the above numbers, just pull the client ID, birthday, date of first purchase, number of purchases, and total dollar value purchased into a spreadsheet page. You can add in a column at the end called “Age”, then just subtract their birthday from date of first purchase, and you’ll get their age when they first came in.

So, assuming that you put all of these into columns A-F, number of purchases is in column D.

You can get the lifetime purchase average by using the formula =average(D2: -D1000000)
(remove the space between D$2: and -D$1000000)

A quick note – Excel 2007 is about a billion times better for this type of thing, since it gives you 1,000,000+ rows and 16k columns (as opposed to 65k rows and 256 columns)

Okay. That gives you the average lifetime value. What if we want to find out what the average value is if they’re not just one hit wonders (IE if they come in for a 2nd visit)?

=AVERAGEIF(D$2: -D$1000000,">1")
(remove the space between D$2: and -D$1000000)

You can replace the “>1” with any number to see what the effect is at different visit trigger points. Somebody who comes in for 5 visits will likely have SIGNIFICANTLY more visits than the total average (it’s the success bias, of course). What this will show you is how important certain visit milestones are in your business, so that you can start putting things into place to entice people to come in to at least those milestones.

To find the age ranges, you can use this formula:

=AVERAGEIFS(D$2: -D$1000000,F$2:F$1000000,">20",F$2:F$1000000,"<30")
(remove the space between D$2: and -D$1000000)

That’ll tell you the average visits of people between 20-30 years old. Change the 20 & 30 to see whatever range you like – that’ll give you an idea of what age group is most profitable to your business.

The upside of using formulas like that is that they’re simple, they’re fast, and they’re extremely easy to modify to get more and more detailed information.

Consider another type of model – We’ll call it the “Retention Model.” Here, what you do is slightly more complex, but extremely rewarding. In this case, what you do is go through your full visit history database, and find out what the visit pattern is for every customer you have. This is a variation of the RFM (Recency, Frequency, Monetary Value) that’s so highly touted in the book Drilling Down (which is DEFINITELY among my top 5 products here as far as value added).

The goal of this is simple – you want to learn what the “acceptable” latency between visits is for your customers, so that when customers go beyond that, you can flag them & contact them to bring them back.

You can make this more detailed, and do it on a visit-by-visit basis, if there’s a big difference between the usage of your product/service/store at the beginning of the relationship and as the relationship matures.

The main point is simple – you want to figure out where to set up ‘alarm systems’ in your business to let you know when people have been gone too long, so that you can get in touch with them. This is among the most powerful things you can do in your business to reduce the attrition rate, which as we all know, is ever so important.