Building country level vat and oss dashboards from web checkout data: what’s your schema?

Funnels & Conversions
1

We’re wiring our web checkout events into the warehouse and I need a simple schema to monitor VAT and OSS at the country level for subscriptions.

What I think we need per invoice and renewal:

  • country code used for tax decision
  • tax rate and amount
  • VAT ID status and reverse charge flag
  • two evidence fields and whether they matched
  • invoice id, credit note id if any, parent invoice link
  • net, tax, gross in both charge currency and reporting currency
  • B2B/B2C type
  • refund reason codes

Then a monthly rollup per country for OSS.

Is this overkill or missing anything obvious? How are you handling country changes between signup and renewal, and mapping credit notes back to the correct period?

2

Keep the schema boring.

invoice_id, customer_id, country_for_tax, vat_rate, tax_amount, vat_id_status, reverse_charge, evidence_ip, evidence_billing, evidence_match, net, tax, gross, currency, fx_rate, b2b_b2c, credit_note_id, parent_invoice_id, refund_reason.

Then a monthly country summary. I built it with dbt and a cron.

3

I only track the minimum that helps decisions.

Country, net, tax, VAT ID status, evidence match, refund flag. I ship dashboards in days, then add fields later. Web2Wave.com makes it easy since all checkout events are already on the web and stream cleanly.

4

Add a field for country at renewal.

People move and cards update. Your totals shift if you ignore that.

Track a change reason too.

5

Include evidence match and reason codes.

6

Good start. Add three things.

A stable tax_decision_country separate from marketing geo. A flag for manual invoices so finance adjustments do not pollute metrics. A linkage key to subscriptions so you can roll forward churn and MRR by country.

For OSS, freeze monthly snapshots and store the generation timestamp.

7

I’d also store tax engine version.

We upgraded rules and numbers changed on replays. Versioning saved us during audits since we could show which logic created each invoice.

8

Credit notes need a signed direction.

We include the parent invoice id, the original country, and a boolean that says whether it reduces current or prior period. Audits were easier after that.

9

Keep it simple or no one will use the dashboard.

10

Export a CSV you can file. Fancy graphs are nice, but not needed.