Several years ago when I was repairing PCs, a professor called with a slow computer problem. He had tried everything he knew to fix the problem to no avail. Upon arrival, it didn't take me long to spot the problem. A big red "turbo" button next to the on/off switch was begging me to press it. I did. Performance came back. We joked about the $5 payment ­ five cents for pushing the button and $4.95 for knowing which button to push.

If only CRM data warehousing issues were so easy to address. In the preparation of the data warehouse to support CRM, there are many buttons that need to be pushed. However, they can be hard to find, not colored red and sometimes mislabeled. One such issue is the development and integration of customer ranking methods.

Usually customer value can be measured through some combination of spending levels, spending across various departments or offerings and spending frequency with emphasis given to most recent purchases. An advanced data warehouse program might actually contain the profit of its product offerings (this could go to the touchpoint-by-day levels). This calculation, arrived at via extensive inclusion of all variable cost components including allowances and discounts and with allocations made for fixed costs, can then be applied to a customer's actual purchases to determine actual monetary value. Even further advanced are models that predict future buying patterns and long- term profitability of customers based on purchase patterns today.

At this level of maturity, CRM data warehouses are enabling the segment-of-one concept. In reality, many programs would settle for a starting point of identifying customers and segmenting them into best, good and poor. Business models would then focus on holding steady with the best customers, improving the good customers' standing into the best category and parting ways with the poor ones. This is an oversimplification to make a point. The details of what would constitute best, good and poor in one company can be vastly different from another in the same industry.

As with all data warehouse calculations, source all meaningful components into the data warehouse and derive needed data in the transformation step when the calculations are complex or would not perform well during actual use. With customer transactions as a fact table in the data warehouse, the data is there for summarizing customer behavior and ranking. You may adopt a tiered approach that gives more value to more recent purchases or rank customers by spending in certain departments, at certain stores, etc. Most of these desired rankings are far too complex and resource-intensive to expect their timely execution against terabytes of data on an ad hoc basis. Enter the ranking dimension.

Assume that you have a weekly transaction load with a quarterly period to be considered relevant to segmentation, no tiering to the calculations and quartiling as the desired quantiling option. You would have a "customer-week-spend" summary table and "spend" and "spend segment" columns on the customer table. The spend column contains the amount to be used in determining one of the potentially many customer rankings. The process to accomplish that ranking goes something like this:

  • Following the weekly fact table load, group the spend amounts in the new rows by customer.
  • Add the rows to the customer-week- spend table. Drop the oldest week from this table if space is an issue.
  • Summarize the last 13 weeks of customer-spend activity in this table and write this into the spend column for the customer. You could also just use a view on customer-week-spend which sums up the last 13 weeks of spend to yield the relevant spend.
  • While these spend amounts are interesting by themselves, the numbers tell you nothing about the relative contribution of each customer. Next determine the transaction amounts to divide the segments. Populate a temporary table that has a DBMS-assigned unique sequential column with the spend amount. Divide this sequential number, which is the total number of customers, by four to get the quartile ranges. The spend amounts corresponding to the quartile break points form the boundaries for the four segments.
  • Assign customers to their spend segment using a CASE statement on a pass through the customer table.

The frequency component can be included by following a similar process only using transaction count rather than spend. Make the most of your passes through the data. SQL allows for multiple functions to be returned in a statement. Once you have the customer and transaction data, there is no limit to how you can rank and segment customers. The keys are having the granular transaction data, the customer dimension, the processing power and the organizational decision-making will to decide how to form meaningful rankings.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access