I recently read an article asserting, that perhaps, just possibly, the customer is not always right. In fact, the less profitable the customer is, the less right they are. OK, so it didn't say it quite that way, but the basic idea was companies should consider customer profitability when setting their customer service goals. Higher levels of customer services should be provided to the more profitable customers, and unprofitable customers (with little hope of future profitability) should be "fired". As with all good management articles, there was a clever little hook to make the technique memorable. This one happened to use a metallurgical metaphor (or is it an analogy?). Your most profitable customers are your Platinum customers. These are your tier 1 customers, interested in advanced service levels, extra support, or other highly profitable value-added services. Your Gold customers are also quite profitable, but not to the extent your Platinum customers are. Next is your Iron customers. They are profitable, but not overly so. If you spend TOO much time and money, they may become…..a lead customer. These are your unprofitable customers; those that demand unreasonable amounts of customer service, continually ask for freebees, bounce checks, and generally make your CFO sad. In addition, Lead customers don't have season tickets to the Padres, luxury boxes, or country club memberships.
I know what you're thinking…"this sounds fantastic, but how can I track customer-focused metrics by profitability segment, and present this information in an executive dashboard using PerformancePoint and the Microsoft BI stack?". I'm glad you asked.
Essentially, we need a customer-focused scorecard with different actual and target values for a given metric in each customer segment. For example, it will not suffice to provide a "Support Calls Per Month" metric in the customer scorecard. We need a Support Calls Per month for Platinum customer, a Support Calls Per Month for Gold Customers, and so on... because we are striving to provide different levels of support based on customer tier.
First of all, as all good armchair-economists do, let me preface this with some assumptions. Lets assume we have a data warehouse…Lets also assume that we do not have the time or resources to provide data feedback into a CRM. If this was the case, it would probably be preferable to calculate per customer profitability in the financial system, implement business logic to categorize profitability ranges into customer profitability tiers, and send this information back to the CRM. Not only would this information then be available in almost real time to support and sales staff, these tiers would be available as members of a Customer Tier dimension... A subject for another blog.
In this case I'm going to use SQL Server Analysis Services to create calculated members in the customer dimension in order to create customer profitability tiers. This calculated member will compute the average of any applicable measure (in this case I will use support calls per month as an example). In PerformancePoint, we then create a Key Performance Indicator using the desired measure (support calls per month) as the value for the metric, and filter the customer dimension for the appropriate customer tier. We drag and drop it into a dashboard, pretty it up, and deploy to our SharePoint 2007 site. The boss is impressed with how quickly this was done and raises his expectations for future projects.
In my toy problem here, I already have a SQL Server Analysis Service cube consisting of sales facts (FactInternetSales) and a customer dimension. Also, information from the support system has already been cleansed, transformed, and loaded into this data warehouse, where it lives quite happily in another fact table in the same cube (FactSupport_Custom).
In SQL Server Business Intelligence Development Studio, I open that cube, go to the calculations tab and create a calculated member for each customer tier:
Note that the parent hierarchy is Customer.Customer, with the parent member [All Customers]. This allows me to later filter the cube by this new "member" in the customer dimension.
The expression for the Platinum customer computes the average of ANY MEASURE included in the query for just those customers with [Internet Gross Profit Margin]>50%:
AVG(FILTER([Customer].[Customer].[All Customers].Children,[Measures].[Internet Gross Profit Margin]>.50))
This is then available as a member in your favorite multi-dimensional analysis tool. The below screenshot shows average profitability for each calculated "member" for 2003. (Note that I've already filtered out the non-calculated customer members)
If I drag my Support Calls Per Month measure in I get:
This tells us that in 2003 the average support calls per month for our Platinum customers is 5.638, 5.529 for our Gold customers, and so on.
Surfacing the information in PerformancePoint
At this point, if you are a back office kind of person, your job is done. Tell the Business Analysts that they can filter the customer dimension by customer tier to create Tier-specific KPIs and go to lunch.
OR….
From PerformancePoint Dashboard Designer, create a new KPI, click on the data mappings for the "Actual" Metric:
Click on Change Source, and select the data source for your cube
Select the desired measure (remember, in this case we will be getting the AVERAGE of any measure we select)
In the Select a Dimension part of the dialog, click on the filter and select the Customer.Customer hierarchy (this is the parent hierarchy that we selected when created our calculate member in a previous step).
In the default column, select the default link and filter by your desired profitability segment:
Repeat for each KPI, set your targets FOR EACH TIER then drag into a scorecard, add the scorecard to a dashboard, and deploy.
Of course, this may not be the ideal solution in many cases. Using Calculated Members in a large cube may have performance implications, and you may not get all the analytics you want out of it for reasons I won't go into here. The point, however, is that scorecards based on customer segments (in this case profitability segments), can be implemented relatively easily, provided your organization has some level of BI maturity.
Go Chargers.