Tracking Customer Churn Rate to Increase Profitability: Utilizing Sets in SAP Business Objects to View Client Account Activity

Customer lifetime value (CLV) is a critical metric for all organizations. Four out of five companies calculate it, but few companies are able to tie CLV to its impact on revenue and growth—only 15% say they can do this. Only 20% of companies report that everyone in the organization who needs to understand customer lifetime value actually does.

While it costs five times as much to attract a new customer as it does to keep an existing one, 44% of
companies have a greater focus on customer acquisition versus 18% that focus on retention.

The probability of selling to an existing customer is 60% to 70%, while you’re only 5% to 20% likely to sell to a new prospect. Yet, how many meetings are about getting new customers or at best about growth, but with no discussion on customer retention? Increasing customer retention rates by 5% increases profits anywhere from 25% to 95%!

So, in the Business Intelligence/IT world, how can I track this “churn rate” to benchmark my company’s current state, track its progress and build a growth plan and marketing campaign that targets client retention as appropriate?

Where Have All My Customers Gone? Tracking Customer Churn

Tracking customer activity isn’t always as easy as a year over year comparison. Are some customer buying patterns cyclical? Are some seasonal? Have some customers made purchases one year, not purchased for a period of time and then come back? Are there trends like this in your business that need further analysis? How do you pull that information from your data?

Utilizing Sets in SAP BI4.2 SP3

Using a Temporal Set within Business Objects can help you identify customers who have made purchases in the past, but haven’t purchased again during a determined period of time. In the example below, you will find a temporal set using the customer key as the subject, a year calendar, and an order date object from the Business Objects universe.  The timeframe the orders in the demo database fall within are 2013 – 2017, so I named the temporal set ‘Customer Orders 2013 – 2017’

After the set is built, you can see the counts by year of total customers (Members), those that made a purchase the previous year, but not the current year (Leavers) and those that made a purchase within the current year, but not the prior year (Joiners). Click here for larger version

What Can These Queries Tell Me About My Company?

The temporal set makes it possible to retrieve a group of “Leavers” from any one year.  “Leavers” is a group who has purchased and are familiar with your products/services and therefore have a higher probability of sales conversion than a cold lead. This data could be used for a marketing campaign, for instance. A direct email, text or letter to this group for a special sale, event or other call to action could prove fruitful.  At a minimum, you’ve got a list of warmer leads to contact to regain their business.  The group can then be tracked to see if they become “Joiners” during the campaign dates, allowing you to track the effectiveness of the efforts.

How Can I Use Business Objects Sets to Grow My Business?

A marketing campaign built on this platform might look something like this:

  • Create a list of customers who had an order in a timeframe; but not in the next timeframe, the “Leavers”.
  • Contact them with your direct marketing campaign.
  • At the end of the campaign, check to see if they made a purchase during the campaign time period, becoming one of the “Joiners”.

Here is an example of customers who had an order in 2014, but not in 2015 (Leavers) and how many of them made a purchase in 2017, but not in 2016, becoming “Joiners”.  The query panel below is extracting customer and promotional information, along with the order quantity and sale amount.  The temporal customer key set we built above is used in the query filter.  The filters are set to look for customers who joined in 2017, but left in 2015.  The last condition is so that we only see sales for these customers in 2017 or greater. Click here for larger version

After running the query, we can see the customers and if there was a promotion associated with their sale. Click here for larger version

How Can I Create a Report Showing Client Churn?

To create this report/query from scratch would require you to write a query with several sub queries.  You would first need to find all of the customers who made a purchase in 2014, then exclude from this list the Customers who made a purchase in 2015.  You would also need to retrieve a list of customers who made a purchase in 2017, but not in 2016.  You would then need to use both lists as a condition within your query.

What Else Can I Do with Sets?

Other use cases for sets might involve retrieving a list of top products and seeing how these performed month over month, or whether the list changed during this time period and how drastically.  Or you could review customer segmentation and how the different demographics of a customer intersect.  Since sets use a sub query to retrieve the results, it can also replace the use of an InList condition in your query where you’re retrieving the list from another query in the report.  These types of queries are routinely beset with issues such as the InList being too long, over a certain number or the SQL statement being too large, occupying too much memory for the optimizer to process it. The use of sets within Business Objects can be a very powerful tool for analyzing customer churn and segmentation, as well as product placement, giving you a much better snapshot of your business. For more information on how to create these sets or other questions on this topic, please contact Michael Schwenker at Michael.Schwenker@

See more Business Intelligence insights or get future articles sent right to your inbox

increase your knowledge
increase your knowledge
By |2018-09-02T17:39:47+00:00July 25th, 2017|BI, Business Intelligence, Kingfisher, Sets|0 Comments

About the Author:

Mike is an accomplished Senior Business Intelligence and Data Warehousing consultant with Consultancy by Kingfisher. His background in large scale Retail Data Warehousing and Analytics projects provide a perfect blend of technical and functional experience and allows him to delve into a company’s data and extract meaningful, actionable intelligence. When he’s not coaching or watching rugby, he can be reached at