If you are a multi-product and/or product + services company how important is it for you to know which products or services are no longer being purchased by customers?  If you are the Product Manager of the declining area it is all about your career. If you have broad responsibilities it is also critical; how else will you know which profit contributors are starting to decline, how bad the situation really is, and when to invest or kill the weakening line?

But before we begin this discussion, let me explain, at a high level, the steps needed to calculate the annual customer churn rate:

  1. Identify all of your customers as of Jan. 1
  2. Identify all of your customers as of Dec. 31
  3. From the second list, eliminate all new customers for the year in question
  4. The second list should be the same or shorter that the Jan.1st list or you did something wrong
  5. The churn rate is the difference in length of both lists divided by the length of the Jan, 1 list, expressed as a percentage.

In addition to looking at your customers as a single group you should also segment them by products and/or services used, geography and financial and strategic importance to your company.  This is because by just looking at continuing purchases you will fail to identify people that have given up on one or more of your products and/or services while still buying others.  Drilling down is always critical to gain insight from your data!

This is where it gets really interesting.  Let’s assume that your company designs, manufactures, sells, and services medical devices like blood analyzers and urine analyzers. If your customers potentially have one of each instrument and you provide service on both products then you have to look at the churn rate for four product lines; blood analyzer product, blood analyzer service, and the same for the urine analyzer.  And if you sell and service in multiple countries or areas (e.g., Americas, EMEA, APAC) the number of possibilities rapidly increase. But using Excel, this does not sound like a daunting task.

The major obstacle to overcome is the integrity of your customer database.  Most companies periodically attempt to cleanse their customer records and eventually give up in frustration.  Common problems include:

  • Duplicate entries – easy to fix
  • Same business listed under different names as a result of mergers, re-naming and data input errors
  • Same products identified with different names or model numbers caused by assigning unique part numbers for variations of the same basic product
  • Different product and service databases caused by not recognizing that revenue comes from customers, not products or services and, hence, not aligning databases with the customer base
  • Missing data because data integrity is not anyone’s responsibility and not viewed as a corporate asset

A short example.  I was once our company’s Vice President of Manufacturing and I wanted to streamline our supply chain by minimizing our number of distributors.  The challenge was that our Engineering department assigned part numbers sequentially and so the same basic part, with different values, had part numbers that did not even provide an inkling of what it was. We had to redo our parts list to include a field for family, e.g., IC, resistor, switch, gear so we could sort and group parts to offer packages to distributors for quoting.  This was a major effort but the ROI was fantastic!

The best way to begin this part of your customer retention strategy is to assess the quality of your customer database.

  • Purge duplicate entries
  • Make sure you have enough information to segment the base in meaningful ways or start adding fields that support you efforts
  • Estimate the accuracy of the basic data. The key word is estimate. Sampling is the magic bullet here.  This table shows the sample size needed to achieve a specific Margin of Error (±) at 95% confidence:


Once you have gotten this far you know how large a hill (not as large as you probably thought) you have to climb so you may as well start your data base cleanup efforts now because procrastinating will only make it worse. Then calculate the customer churn rate for each segment and start the process of creating and implementing individual retention plans for each segment.

This is not glamorous work but you will be amazed at the insight you will be able to bring to the table the next time sales and revenue strategies are discussed.