Monday, 25 January 2010

A DQ Firewall to protect you and your sales force

I see a Data Quality firewall as a tactical approach to Data Quality, typically driven by a departmental need to ensure that data is fit for the purpose that it is intended. A good practical example would be within a Commissions department. The Commissions department is responsible for payment of commissions to the sales force, perhaps on a monthly basis, dictated by rules relating to sales and employee eligibility.

I.e. An employee should be employed full-time, have made more than 50 sales in the current month, and have had a high level of customer satisfaction feedback.

If an employee is eligible, a payment will be calculated based upon which products they have sold. Some products will pay higher commission than others, and the total amount owed will be paid during the monthly payment run, as per their salary.

Why the need for a firewall?

As you can see from the above example, we have the need for data that is:

1. Timely (we have received all data when we expected to receive it)
2. Complete (data contains everything needed to calculate a commissions payment)
3. Accurate (data represents true values)

These Data Quality dimensions must be met to expectation in order to ensure that all commission payments for employees are correct.

Legal Action

An article found on the Information Week website in December 2008 stated that Sprint, the wireless telecoms carrier is facing a class-action lawsuit over allegations that it shafted employees of commissions totaling more than $5 million.

It turns out that employees from their retail stores were not receiving full commission on products sold. This was due to failed integration between Sprint & Nexus systems following a merger of the two companies in 2005, 3 years previous.

Practical example of a DQ Firewall

The Sprint issue (and impending court case) could have been avoided if an automated data quality firewall had been implemented around the system used to calculate commission payments. The below diagram helps to explain how the process could operate:

(Larger diagram:

Source data may contain data such as:

  • Sales (items actually sold by employees)
  • Mystery Shopper scores (a 3rd party company rating service/sales process)
  • Referrals (any referrals made by sales staff)
  • Customer Satisfaction (survey scores from customer satisfaction)

Firstly, data would typically be loaded into some form of data warehouse. The system used to calculate commissions would then take this information into a staging area, where any required manipulation/aggregation would occur. Once staging tables have been populated, the data would be loaded into the database that is used to calculate commission payments. Commission Payments would then be derived from this data and the business rules applied during the process.

You will notice at the process contains 3 checkpoints where we ask the question:

“Is data fit?”

Checkpoint 1: Have we received all the data that we expected?

And, is it in the format we expect, containing accurate values?

For example, a Commissions analyst would typically know which products we pay commission upon. Therefore we should monitor data received per product. Implementing Lower & Upper control limits based upon expectations would be a good way to add a RAG status to each product.
  • Why have we received 0 sales data for this product?
  • Historically we have received between 10,000-15,000 sales of this product per month, however, this month we only have data for 500 sales, why is this?
  • We seem to have duplicate rows for some sales – is this correct?
  • Typically we sell £25,000 of this product monthly, but this month we’ve only sold £3,000, why is this?
  • We’ve received data for products that don’t seem to match our products reference data. Why is this?
Pro-active analysis such as the examples above, asking questions relating to data prior to kicking off the process that calculates commission payments will ensure that the process is run correctly, first time. It will allow detection of data anomalies, missing data feeds, and duplication that may have potentially gone undetected and caused inaccuracies in payment calculation.

Checkpoint 2: Has all data successfully navigated staging?

When staging areas are used, we need to ensure that data has correctly been transferred to the expected tables within the destination database. Simple volume & value checks may typically suffice, ensuring that the correct volume of sales have entered the system used, as well as the correct value of these sales.

Further checks relating to pattern matching to ensure conformity, or referential integrity checks could also be implemented at this stage.

Checkpoint 3: Have commission payments been calculated as expected?

This final checkpoint allows us to verify that the commission calculations have been completed as expected, prior to sending payments to employee bank accounts.

For instance, if you know that your sales force consists of 7,659 employees, you should see that 7,659 commission calculations occurred.

Through experience you may know that employees average a payment of £500 per month. If you see employees with a £10,000 payment you may wish to validate this prior to the payment hitting their bank account. Clawing back an incorrect payment could cause a lack of trust in the process.

At this stage you may also want to check eligibility criteria. For instance, has a commission payment been calculated for a part-time employee, or an employee on maternity leave?

Beyond the firewall

A data quality firewall can be a good case study to demonstrate the benefit of data quality initiatives within your organisation. This case study could be used as a springboard to gain support & funding to implement similar practice in other parts of the organisation, or to build a case for more strategic, organisation-wide data quality initiatives.


Post a Comment