Business Intelligence Blog from arcplan

Customer Analytics: See the Truth with Data Visualization – Part III


I'm wrapping up my series on customer analytics and their associated visualizations with a discussion of 3 more metrics you should track on your customer dashboards – customer lifetime value, accounts receivable, and bookings & backlog – and why these simple visualizations are effective because they extend the reach of data to more people in the organization.

Track Customer Lifetime Value with a Stacked Bar Chart

One of the most underutilized metrics is customer lifetime value (CLTV), which sits at the intersection of sales, marketing and finance. CLTV calculates the historic value of a customer over time and helps establish how much customer relationships are worth – and therefore how much money can be spent to acquire them.

It's conventional wisdom that it costs more to acquire new customers than it does to maintain existing business. For many companies, customers become more profitable over time. It costs less to pick up the phone and sell additional licenses to an existing software customer than it does to fly a sales person to meet with a prospect for the first time. It costs less to run loyalty programs to keep customers happy than it does to sponsor a trade show to meet new prospects. But the costs of these programs, all of which are necessary for running a business, have to be taken into account when using the CLTV metric. Customer lifetime value shouldn't be something that only finance teams look at. Sales and marketing professionals must use it to ensure that their acquisition program costs are in line with CLTV.

There are many ways to calculate customer lifetime value, but the simplest is:

(Average Value of a Sale) x (Number of Repeat Transactions) x (Average Retention Time for a Typical Customer)

You can calculate this information by business unit or region, or you can calculate it on an individual customer basis as we have in the first graph below. The filter enables users to select which customer's CLTV they'd like to see.

We find that our customers in finance roles prefer to list customer lifetime values in a table, while those in sales and marketing prefer bar graph visualizations. This is a graph that extends financial information beyond the finance team so it becomes useful to more people in the organization.

In this stacked bar chart below, you see a visualization of the sales to cost of sale ratio, and the ever-increasing value of an individual customer over time – data that's hugely important to sales and marketing folks, but data that they might never see unless it was graphed and put on their dashboard:

Customer Lifetime Value arcplan

Another way to graph CLTV data is by marketing channel. Below is a bar graph showing the source of marketing leads who turned into customers and their average lifetime value. This graph is useful for determining the marketing programs that should keep running and those that should cease based on the kind of customers they bring in:

Customer Lifetime Value arcplan_2

Monitor Accounts Receivable with a Simple Bar Graph

This next graph visualizes the age trial balance or accounts receivable. In other words these are the customers who owe the organization money and haven't paid their bills. This information is normally not graphed because finance teams are trained to understand tables of numbers. But what if you visualized the data, as we have below, to extend it beyond the reach of only finance people? The fact is that customer-facing employees, like those in sales, are the most directly affected by deadbeat customers (they don't get their commissions), and they can most directly affect the situation going forward by pricing future sales differently. A sales person who has visibility into the accounts that are behind on payments can price the next deals for those accounts higher or require different payment terms.

Below is a simple bar graph depicting current accounts receivable, AR between 30 and 60 days, 60 and 90 days, and those over 90 days. The chart for our A segment of customers (our best customers, if you remember how we segmented our customer list in Part I of this series) shows positive results for the most part, with the majority of customers current on their payments. The graph for the C group paints a very different picture, with many customer falling way behind on their payments. The C chart gives an at-a-glance indication of a serious trend. Drilling down into the graph gives us a detailed list of the accounts receivable data. Accounting should be notified about the significant number of open receivables and expect non-payment for 11 accounts. The account managers might want to call the non-paying companies and make a note of revised payment terms for deals going forward with them.


Follow Bookings & Backlog with a Stacked Bar Chart

Another visualization that makes finance data available to a wider audience is a Bookings & Backlog stacked bar chart. We can view committed booked business as well as known backlogs by category in this graph. Categories of backlog may include delivery, manufacturing, or supply issues. We should try to solve the A customer group's backlog problems as soon as possible, since these are our best customers. We can probably rearrange deliveries for C customers and commit some of them to the A customer group. As I said in my first post in this series, it’s best to try to keep all customers happy, but when that’s not possible, smart businesses try to keep their best customers happiest and happy first.

Bookings Backlog_arcplan

Click to enlarge

Every business has A, B and C customers, where A customers produce the majority of the profits and pay their bills in a timely manner. Identifying a set of customer analytics that defines these customer groups and helps numerous departments stay on top of them is a great step toward getting a 360 degree view of your customers. You can emulate the graphs I've presented to understand who your best customers are, how those customers are performing, and what customer issues need attention from sales, marketing, customer support and finance.

Better visibility into customer data can achieved with dashboard solutions like arcplan (where the visualizations in this series came from). If you'd like to learn more about the topic of customer analytics, check out our on-demand webinar, A Visual Guide to Customer Analytics.

More information is also available in Parts I & II of this blog series.

Dwight deVera

About Dwight deVera

I'm Senior VP responsible for Solutions Delivery at arcplan in North America. I also present on a lot of arcplan webinars, so you can sign up to hear me - the events listing on our website is located here: You can also follow me on Twitter: @dwightdevera.
Comments (0) Trackbacks (0)

No comments yet.

Leave a comment

No trackbacks yet.