Take this month’s statements from your five biggest providers and try to answer one question. How much did the practice earn in ongoing fees, and from which clients?
One provider sends a spreadsheet with the policy number in the third column. Another sends a PDF. A third splits the same payment across two files. A fourth pays in arrears for last quarter. By the time you’ve copied, retyped and added it all up, it’s Thursday, and you still aren’t sure the total is right.
Each file is correct on its own terms. The trouble starts when you put them side by side.
Same money, different layouts
Every provider builds its statements around its own systems. Columns sit in different places and carry different names. One file shows the client’s ID number, another only a surname and an initial. One lists every policy on its own line, another groups them by product. Some files include reversals and adjustments in the same list as payments, and others send them separately.
None of this is wrong. It just means no two files can be added together as they arrive.
Names and codes that don’t match
The same product can appear under three names across three providers. Your client can be “J. van der Merwe” at one, “Johan Van Der Merwe” at another and a policy number with no name at a third. Each provider also gives your advisers their own broker codes, so the same adviser turns up under a different number on every statement.
Until those are linked, your system can’t tell that four lines belong to one client, or that two codes belong to one adviser. Every report you run on clients or advisers depends on that link being right.
Periods that don’t line up
Then there’s timing. One provider pays monthly for the month just gone. Another pays quarterly in arrears. A third pays on a date that has nothing to do with when the fee was earned. A clawback lands months after the policy lapsed.
If you file income by the date it arrived, your monthly figures jump around for reasons that have nothing to do with your business. You can’t tell a real drop in income from a quarterly payment that simply hasn’t landed yet.
Mapping is the step everything else stands on
The fix is to translate every file into one consistent format before anyone reports on it. Each provider’s columns are matched to one agreed set of fields. Product names are matched to one product list. Clients are matched to one client record, and every adviser code to one adviser. Each payment is tied to the period it was earned in, whenever it arrived.
It’s unglamorous work. It’s also the reason you can trust every report built on top of it. Adviser splits, client profitability, month-on-month comparisons and the income history a buyer will ask for one day all assume the mapping underneath is right. If it’s wrong, every one of those numbers is wrong in the same direction, and nothing on the report tells you.
Doing it once isn’t enough
Providers change their files. They add a column, rename a product, move to a new admin system or start sending a new statement for a new product range. A mapping that was right in January can be wrong by June, and the first you hear of it is a figure that looks odd.
So mapping needs an owner and a routine. Someone checks each provider’s file when it arrives, catches the change and updates the rules before the numbers go out.
What you get when it’s done properly
When every provider’s data sits in one format, questions that used to take a week take minutes. What did each adviser earn this year across all providers? Which clients bring in the most ongoing income? How does this quarter compare with the same quarter last year? You get one answer to each question, and you can trust it.
Your revenue data had the answers all along. Normalising it puts them in one place.
Let someone else do the translating
Commspace captures and standardises statements from the providers you work with and maps them into one consistent format, so your team works from one set of numbers. See how at www.commspace.co.za.
