There are a few options available to us when data is not directly reconcilable. All of which hold benefits and pitfalls – so I’ll let you decide which is best:
1) Data Enrichment – In the majority of cases it will be possible to find a tertiary data set that can enrich your primary source to allow the reconciliation to take place. However it’s not just a simple case of updating one side of the reconciliation and proceeding as standard. Certain decisions need to be made:
- Which data set do you enrich? ie network, billing or both
- How to treat records that cannot be enriched?
- How to treat duplicate enrichments?
- How to perform the reconciliation – it’s technically now a 3 way rec, so should be dealt with accordingly
2) High Level Reconciliation – Even if data is not directly linkable at the individual record level, it is more than likely still possible to reconcile at a grouped level ie total active network customers Vs total billed customers. However, if you perform a reconciliation at this level there are certain risks/issues:
- RA Equilibrium – you lose visibility of the individual issues and just see the net effect ie in its worse case you are overcharging 50% of customers and under charging the other 50%, however due to a high level reconciliation you see the net effect of perfect billing.
- Revenue Loss/Gain – the high level reconciliation shows a tangible error, in this case you can’t fix anything as you don’t have detailed results so you end up having to perform the detailed reconciliation anyway.
To avoid issues the simple trick is to perform the reconciliation multiple times at different grouping levels, it will avoid the equilibrium issue and should enable you to isolate specific issues – thus avoiding an all encompassing detailed reconciliation.
3) Data Source/Process Enhancement – Quite simply, get the original data sources enhanced by their business owners to include a common key. RA isn’t just about finding money, it’s also about ensuring good business process to reduce the risk levels against the company’s revenues. Clearly if there is no direct link between a customers product and their bill then there is an associated risk.
Summary
Ultimately the way I like to deal with these situations is by consolidating the above 3 options. Firstly I would perform the high level reconciliation – this will generally find risks and issues, that will allow you to justify the need for a business process enhancement by the data owners. To assist the data owners with the enhancement I would then perform a one off data enrichment exercise – identifying all records that can be easily updated and isolating the areas at risk. Eventually we end up with a fully cleaned system that can be monitored with ease going forward.
