Automating subscription billing: how quantity reconciliation actually works

What a subscription sync really is

It means an automatic reconciliation between the place where a quantity arises and the place where it is charged. The quantity arises in the portal of the distributor or vendor: a customer adds mailboxes, cancels workplaces, switches product. It is charged on your contract. In many businesses, a spreadsheet and a person sit between the two.

A reconciliation replaces that intermediate step: it reads the quantities from the source, compares them with the contract lines and carries the difference forward. That sounds simple, and it is - except in exactly four places: the key, the timing, the treatment of manual work, and failure.

The key decides everything

The most common and most expensive false assumption is that a product name or an item number is enough to match a source line to a contract line. It is not. The same licence is commercially a different product depending on whether it is billed monthly or annually, and different again if the term differs.

A sound key therefore has four parts: customer, the specific product variant, the term and the billing cycle. Leave out the cycle and the quantity query adds monthly and annual licences into one number. The result is a line carrying the quantity of every cycle and a backdated contract start - the invoice is too high, and the error only surfaces when the customer asks.

• Customer - matched with certainty, not guessed from a name comparison
• Product variant - the specific variant, not the product family; two variants with similar names are two items
• Term - an annual contract and a monthly one are not the same line
• Billing cycle - monthly, annual or something else; it decides whether you invoice once or twelve times

An addition on the 12th - pro rata or not at all

Quantities change in the middle of a period. If you only charge from the next first of the month, you give away part of a month with every addition. Across a year and the whole estate that is no longer a rounding error but a fixed discount on your revenue.

The clean approach separates by billing state: if the current period has already been invoiced, a pro-rata charge arises from the day of the increase - as its own line, so the customer can follow it. If it has not been invoiced yet, the new quantity simply appears on the regular invoice. Two cases, two behaviours, no judgement call.

It also matters at which level an increase is counted. If you only watch the total of a contract line, you miss the addition when another line leaves the group in the same period: the total stays the same and nothing is charged. Counting has to happen at the level of the individual source line.

There is a trap in the first year: a new annual subscription is often billed pro rata in advance up to its renewal date, while the contract itself only starts on that date. If the customer adds licences during this prepaid stretch, the addition lies before the contract start, and a reconciliation that only knows contract periods treats it as "not yet invoiced". The prepaid stretch therefore has to be stored on the line, otherwise every addition waits until the first regular invoice.

Not every source bills to the day. Some vendors invoice the calendar month: the first month is charged pro rata, full months follow. There the contract start belongs on the first of the month, and an additional quantity is charged as a full period on the next invoice. Which rule applies is a property of the source, not of the contract.

Monthly becomes annual: cycle switch on the renewal date

When a customer switches a subscription from monthly to annual at the next renewal, the portal often reports the licences under the new cycle straight away. A naive reconciliation sees a new purchase: it creates a second line and charges the full quantity pro rata from today, on top of the monthly line that keeps running.

The right approach is to recognise the switch for what it is: same variant, same term, only a different cycle. The old line then ends on the day before the renewal date, the new one starts exactly on that day, and no invoice is created until then. Up to the switch date, quantity changes are reported rather than written silently.

This also requires every source to understand the renewal date the same way. One delivers the last day of the term, another the first day of the new one. A single day of offset is enough for planned renewal quantities to miss or for a year to be billed one day short. The reconciliation therefore translates every source into one common meaning.

Manual work has to be allowed to stay manual

Every estate has lines that deliberately differ from the source: a goodwill arrangement, a special price, a quantity frozen for a good reason. A reconciliation that straightens everything out overnight deletes exactly those decisions - and nobody notices, because the number looks plausible afterwards.

So the rule is: a line changed by hand is reported, not overwritten. The difference belongs in the log, not in a silent correction. The same goes for cancellations: the line is set to zero and runs out at the end of the period rather than disappearing retroactively - what was delivered has to stay billable and traceable.

"No invoice" is a decision too

A reconciliation constantly decides that no document is needed: a reduction only affects the next regular invoice, an increase falls into a period not yet invoiced, a subscription was cancelled at its renewal date. These decisions are usually right, and that is exactly why nobody checks them, because nothing is created that anyone could look at.

They only become visible if the reconciliation reports them: one summary per run with customer, quantity before and after, and the reason. The same applies to lines a source suddenly stops reporting. The quantity stays, the line is flagged, and somebody decides whether the subscription is really gone.

What happens when the source goes quiet

Portals are not always reachable, credentials expire, interfaces change. The decisive design error would be to carry on calculating with the last known state: the system then quietly charges quantities nobody has checked, in the worst case for months.

The right behaviour is the opposite: if a source does not answer or its data is stale, the reconciliation for that source pauses and says so. Better an invoice late than an invoice wrong. For the same reason every billing run deserves a preview - see what would be created, then trigger it.

More dangerous than a silent source is one that answers promptly and is still wrong. Some portals report a zero at the start of the month together with a note that the figure is not final yet; others show intermediate states while changes are in progress. A zero marked "pending" is not a quantity but a missing measurement, and for sources with intermediate states a change is only taken over once two consecutive runs report the same value.

As a last safeguard every run first calculates what it would change. If it would touch an unusual number of lines or move the total quantity of a source sharply, it writes nothing, stops and opens a ticket. Release happens by hand, or the hold lifts itself once the source delivers unremarkable data again.

• Detect stale data and pause the run instead of estimating
• Do not take pending counts as zero
• Hold and report large deviations instead of writing them
• A preview before every billing run, showing the changes against last month
• A log per run: what was reconciled, what was skipped, what stood out
• Only one run per source at a time - parallel runs create duplicate lines

Many sources, one contract

A customer buys Microsoft licences through one distributor, email security from a second vendor, backup from a third and something from your own shop on top. To them that is one service they buy from you - so it belongs on one invoice.

Technically that means: every source writes onto the same contract, not onto one per vendor. Each source needs its own mapping screen for item, term and cycle, because every portal names its products differently. What it does not need is a billing path of its own - otherwise the customer receives four invoices and you get four sources of error.

In IDA the quantities come from Crayon Cloud iQ, the ALSO Cloud Marketplace, Cloud Factory, Tarox, the ADN Cloud Marketplace, Hornetsecurity and a connected WooCommerce shop; every source can be switched off individually, and they all run onto the same contract.

Changing distributor without starting over

At some point an estate moves from one distributor to another. For a reconciliation that is delicate: the old source does not report the moved subscription as zero, it simply disappears. The new source finds a line that does not belong to it and, in the worst case, creates a second one, including a pro-rata invoice for a stretch the customer paid long ago.

The clean way is to re-assign instead of re-create: the existing line changes its source and keeps quantity, contract and billing state. Matching is based on evidence (same customer, same vendor variant, same term, same cycle), not on the name. And the line is only moved once the old source no longer reports the variant.

Consumption is not a quantity

Not everything a portal lists as a subscription can be billed like a licence. Consumption-based plans, for Azure for example, have neither a fixed count nor a fixed price: the subscription sits in the portal with quantity one, the amount is different every month. Taken over as a normal line, it would turn into the same fixed amount every month.

Such plans therefore do not belong in the quantity reconciliation. In IDA they appear in the subscription reconciliation in a separate "Consumption" section with customer, plan and start date: visible so none is forgotten, but without a fixed contract line.

Introducing it without risking the estate

A move like this rarely fails on the technology but on the order of steps. What works: measure first, then one source, then the rest.

• Reconcile the estate: portal quantities against billed quantities, customer by customer. The result is uncomfortable and at the same time the justification for everything that follows.
• Automate one source - the one with the most lines - and run it in parallel for a month instead of switching immediately.
• Work through the differences from that month one by one. Every explained difference is a rule nobody had written down.
• Bring the remaining sources across and abolish the spreadsheet. As long as it exists, it will be maintained.