How to Really Track Lifetime Customer Value

Tracking lifetime customer value and measuring the ROI of each of your marketing vehicles is the holy grail of online marketing.  Unfortunately many online marketers don’t know how to actually execute on this vision.  These marketers understand the goal of measuring lifetime customer value, but they take so many shortcuts that their conclusions are suspect.  

Tracking lifetime customer value is more complex than it seems because marketers rely on two disparate systems for customer tracking, and those systems generally don’t talk to each other.  The first tracking system is a web analytics package, Google Analytics being the most common.    The other tracking system is the transactional system (e.g., ecommerce database) that tracks customers and orders.  While the web analytics package holds information about where customers came from, lifetime value of a customer is generally in the transactional system, thus creating the challenge.

Because marketers don’t understand how to make their analytics package interface to their transactional system, they start taking shortcuts.  The most common shortcut is to pull an average lifetime customer value from the transactional system and assume that value holds across all customer segments.  This is a big assumption that often doesn’t hold up when you have access to true lifetime value by customer segment.  The fact of the matter is that some segments spend far more than other segments do so you need to dig deeper.

Another approach marketers sometimes take is to estimate customer value based on the data in Ad Words or Google Analytics (when the e-commerce capabilities are turned on.)  The problem with this approach is that Ad Words uses a 30 day cookie so you only capture customer spend for the first 30 days after they click on an ad.  That is far too little time to get a feel for lifetime value.

There are two basic approaches for accurately tracking lifetime customer value: pass customer source information to your transactional system or extract enough information from your analytics package that you can match it up with your transactional system.  In the first scenario you tag each paid ad campaign with extra data that specifies where a customer came from.  For example, let’s say we are running ads for my blog at http://kennethpmcdonald.blogspot.com/.  When we set up the ads, instead of entering http://kennethpmcdonald.blogspot.com/ for our landing page, we enter http://kennethpmcdonald.blogspot.com/?source=123 where 123 stands for the ad campaign.  We then need the transactional system to capture "?source=123" and associate this information with the appropriate customer.  In other words, when a customer clicks through on that ad, you store “123” in a field in your database for that customer. If you wrote your own transactional system, this generally isn’t a hard change on most systems.  If you are running a packaged transactional/ecommerce system, this approach may or may not work depending on how flexible your system is.

Aside from the integration issues, there are some other pros and cons of this approach.  On the positive side, once you get this system up and running, it is fairly straightforward to run reports that tell you the total amount of revenue by campaign including what customers purchased and when.  The reason for this is that all the segmentation and revenue data is one place – your transactional systems.  However, you don’t have the cost of your campaigns in the transactional systems so you will still need to match that up, but that is generally simple enough that you can do that by hand if you don’t have a ton of campaigns.  

One major issue with this approach is that it works for paid ad campaigns and other approaches where you can control the URL (in order to append "?source=123" information).  However, there are cases like free search where you cannot control the URL.  As such, you cannot measure ROI for all sources with this approach.  While paid ad campaigns are the primary place where we want to know the ROI, it is always nice to know the ROI for SEO efforts and other marketing projects too.

The second approach for tracking lifetime customer value is to extract enough information out of the web analytics system to know which customers came from where.  If you are using Google Analytics, you need to implement the e-commerce capabilities in Google Analytics.  Once this is done, you will be able to run reports in Google Analytics that show transaction IDs by customer source.  For example, can click on the E-Commerce section in Google Analytics and then click on the Transactions report.  From there you can select a segment or use the secondary dimension feature to filter the results.  Viola, you now have a list of transactions by source.  You can export this data from Google and import it into a reporting database for your transactional system where you can see what follow-on purchases were made by the customers in each source.  In other words, Google Analytics tells you that a customer came from a given campaign and placed order 1001.  You now can look in your transactional system and find out that this customer later placed orders 1010 and 1011. 

To export data from Google Analytics it is best to use an automated tool of some sort.  Excellent Analytics is an Excel plugged-in that grabs data from Google Analytics using the Google Analytics API.  It takes a little work to get set up, but it is extremely helpful if you go for this approach.

One of the great things about this second approach is that it works for virtually any customer source.  Want to know how much customers who came from organic search spent?  No problem with this approach.  As a matter of fact, you can make the data as granular as you want.  For example, you can look at users who came in on a specific keyword phrase via organic search and figure out their lifetime value.  Basically the sky is the limit on how much you can slice and dice the customer value data.

There is one major issue to be aware of with this approach  – Google Analytics’ terms of service have the following section:

You will not (and will not allow any third party to) use the Service to track or collect personally identifiable information of Internet users, nor will You (or will You allow any third party to) associate any data gathered from Your website(s) (or such third parties' website(s)) with any personally identifying information from any source as part of Your use (or such third parties' use) of the Service.

I won’t try to be a lawyer, but you could definitely read these terms to say this approach violates the Google terms of service.  On the other hand, one could argue that Google is prominently listing the Transaction ID in their interface and that is a personally identifiable piece of information so aren’t they going against their own terms of service?  In addition, if you are aggregating the data by customer segment instead of looking at it by specific customer, you probably are not violating the spirit of this section.  You will have to make the call on it.  You could always use a different web analytics package if you are concerned about Google’s terms.

Regardless of what approach you take, there is one issue that you will need to think through – customers don’t follow a linear path from one source through your web site.  Customers often times will pass through multiple sources before completing a purchase.  They might click on several paid campaigns, an email campaign, and follow an organic link before making a purchase.  Which source takes credit for the customer?  You will have to figure out what rules apply.  Many of the companies I have worked with generally consider the first source to be the one that “owns” the customer but they will reassign the customer to another source if the customer goes dormant long enough (e.g., no purchases for 6+ months). 

If you follow these approaches to track lifetime customer value, you will find that your decision making is greatly improved.  You now will be able to measure the impact of virtually all your marketing activities in great detail.

There are Jobs in Big Data

Today the New York Times had an article that followed up on my last two posts about the importance of analytics.  OK, the New York Times probably didn't read my last two posts, but let's just say that great minds think alike.

The NYT article today was called, "The Age of Big Data."  The basic thesis was that there is a real need for people with top notch analytical skills.  They cite a McKinsey study that said that the US will need another 140k to 190K workers with deep analytical expertise and 1.5 million more data-literate managers.

The article did focus more on unstructured data (think Twitter instead of an official company database), but the point remains that businesses are becoming more data driven.  Employees with analytical skills will do well in this environment.  My favorite example of the growing importance of data was that Google searches are becoming a more accurate predictor of housing sales than forecasts by real-estate economists!

Given the growing importance of analytics, I do worry a lot about the state of our education system, particularly around math and science.  I recently was looking to cross-train employees on a specific function that required a fair bit of math, geometry to be specific.  I was amazed at how many employees said they didn't want to learn this new skill because they were concerned about the math involved.  We need more passion for math and that needs to start in elementary and middle schools!  In full disclosure, I always loved math and made it all the way through the math program at Dartmouth so I have a side of me that thinks everyone should love math too.

The full New York Times article can be found at http://www.nytimes.com/2012/02/12/sunday-review/big-datas-impact-in-the-world.html?_r=1&ref=technology

Why Every Internet Marketing Professional Needs to Know SQL

My first job after college was with Oracle back in the late 1980’s.  At the time Oracle made all employees go through an incredibly intensive 3 week training course on SQL.  It didn’t matter if you were a developer, a marketing professional, or a receptionist, you were sent to a training class at a hotel for 3 weeks to learn SQL 20 hours a day.  Yes, it was 20 hours a day – Oracle took things seriously.  At the time I remember thinking, “Why in the world do I need to know SQL when I am on the marketing side?”  

Boy was I wrong!  More than 20 years later, I have written thousands and thousands of queries and use SQL in my job almost every day.  I have come to the conclusion that every online marketing professional needs to know SQL.  As many online marketing professionals like to say, “To be in online marketing, you have to be a data geek.”  If you are a data geek, you need to be able to able to collect and analyze data on your own.  Let’s face it, the explosion of the Internet means that the amount of data being captured in SQL databases these days is jaw dropping.  I have seen far too many Internet professionals who don’t know SQL who are literally hostage to IT, waiting for IT to write another query for them.  You won’t be a world-class Internet professional if you have to wait for others for a key part of your job.  

For years I have started my work day almost the same way every day.  I get a variety of reports via email that talk about the health of the business.  They essentially give me a high level picture of the customer funnel.  How many customers came to the site?  How many registered?  How many ordered?  Some of these come from Google Analytics and others come from the transactional systems.  More often than not, the reports highlight issues that require further investigation.  If the data is in Google Analytics, I drill down in Analytics.  If the data came from the transactional systems, I fire up SQL.  Canned reports just don’t do it – I need to be able to explore the data in whatever direction it takes me to answer the questions that have arisen from the previous day’s data.  I often have the answers to my questions by 9am.  If I was waiting on IT or a marketing analyst, it would be hours at best and possibly weeks before I got an answer.  I need to be self-sufficient.

Learning SQL can be intimidating for a marketing person.  A lot of SQL books that you find on developers’ desks are the size of a dictionary and look pretty daunting.  However, the reality is that SQL has become a lot easier for a variety of reasons.  There are a wealth of books that cover SQL for business folks.  Furthermore, the tools have advanced a lot.  If you are using a visual query tool, it really makes building queries much easier for someone just getting started. If someone on the IT side has been nice enough to name the tables and columns clearly that is a big help. Even better, if IT has defined the primary and foreign keys, most query tools will automatically join the tables together for you. 

Here are my tips for a marketing person who wants to learn SQL:

• Buy a good SQL basics book, but make sure you read a little and then try it out with a hands-on session.  Read a little more, try it out.  You get the idea.

•  Microsoft Access is a great tool for getting started.  Access does an incredible job of importing data from Excel which makes it easy to pull in data and get started.  It also is super easy to export data from Access to Excel so you can show others the results of your work. (I still use Access almost daily because of the Excel integration.)  Access is also great because if you do something horribly wrong, the worst thing you can do is cause the CPU on your local machine to peg at 100%.  Finally, Access allows you to build queries visually and see the SQL behind those queries, something that is key for SQL novices. My only caution with Access is that there are some small SQL differences between Access and the database systems that most companies use like Oracle, MySQL, SQL Server, etc.
• When you are ready for a real world project, ask your IT department to set you up with read only access on a reporting server.  You want an environment where you cannot delete any important data and where if you issue a CPU-intensive query, it won’t block customers from using your site.  Before you approach IT, I strongly encourage you to know some of the things that you can do incorrectly in SQL that can suck every ounce of CPU from your server.  Do you know what the following things are: a Cartesian product, doing a full table scan vs. hitting an index, and locking a table?  If you don’t know what those things are, you aren’t ready yet to write SQL against a real world system – keep reading your SQL books.

Knowing SQL has a lot of additional benefits beyond just analytics. For example, I have regularly used it to provide exports to key customers or partners.  I use it to set up complex, tailored email marketing programs.  SQL is everywhere these days!

So while SQL can seem a little intimidating at first, it is a tool that every Internet marketing professional needs to have in their tool-belt if they are to be successful.

Online Marketing Starts and Ends with Analytics

Marketing professionals regularly ask me how they can improve their online marketing skills, and my response is always, "Online marketing starts and ends with analytics."  The whole premise of online marketing is that you can measure the ROI on everything.  No more hand-waiving to your CFO when s/he asks what the return was on a specific campaign - with online marketing you can say EXACTLY what the return was for the spend.

The problem is that I find a lot of online marketing professionals are particularly weak in analytics.  They know a little about Google Analytics or another tool, but they don't really dig into the numbers.  Well, if you are going to tell your CFO what the return was on your campaigns, you (or someone on your team) needs to be a rocket scientist when it comes to analytics.  Everyone wants to jump into all the sexy functional skills like social marketing or email marketing, but if you cannot measure your results, you are missing the point.

I also see a lot of marketing professionals cast about looking for new analytical tools.  However, when it comes down it to, most of them only use 1% of the capability of Google Analytics, and Google Analytics is free!  There is no need to look for something better if you are not really using the tool.  Google Analytics is really an amazing tool when you dig into all the advanced goodies like custom variables, event tracking, advanced segments, profiles, custom reports, the API, etc.

Learning Google Analytics is mostly a mental challenge.  You can set the tool up on any web site in a few minutes so the learning opportunity is right in front of you.  However, really squeezing a lot out of the tool takes some tenacity - the product is deep and wide and takes time to learn.  I recently have been touting a book that I think does a superb job of teaching online marketing professionals what they need to know about Google Analytics; the book is "Advanced Web Metrics with Google Analytics" by Brian Clifton.  The book walks through how to use all the advanced capabilities of Google Analytics in the real world - how you apply the techniques to SEO, paid search, video marketing, etc.  I love that the book is so focused on real world situations and not academic theories.  I am such a fan of the book that I already have pre-ordered the updated edition that comes out in March of this year.  The book is not for beginners, it covers advanced techniques for people who are serious about their analytical skills.

So back to the task of becoming a better online marketing professional.  Analytics is so important, and there is so much to it, that you really have to work analytics first and last.  Start by learning a ton about Google Analytics by reading a book like Brian Clifton's and applying it in the real world.  Then go build up your functional customer acquisition and customer engagement skills (paid search, affiliate marketing, SEO, social marketing, email marketing, etc).  Then when you have built up your customer acquisition / engagement skills,come back to Google Analytics.  Analytics are so important that you need to be reviewing them regularly.  Hey online marketing professionals, if you are not a Google Analytics guru, you are not an online marketing professional!