Challenge
A media-sales operator running photo and ride-media concessions inside major theme parks across the UK and Benelux billed its park partners on revenue-share contracts with ratcheted tiers — the split changes as cumulative season revenue crosses thresholds. Sales data lived in per-venue systems: photo points of sale, pre-order platforms, digital delivery, and unattended vending. Invoices were assembled by hand, tier positions were tracked in spreadsheets, and finance had no single daily view of where each contract stood.
Approach
I designed and ran the analytics backbone as a long-term engagement:
- A SQL Server warehouse with conformed dimensions and three production fact pipelines — photo sales, pre-orders, and digital sales — fed by GCP cloud-function ingestion from venue systems and an unattended-vending payment feed.
- Running-total contract models that compute each partner’s position against its ratchet tiers daily, so finance sees the split before the invoice, not after.
- Automated partner invoicing through SSRS, generated straight from the warehouse — the same numbers the dashboards show.
- Workforce ETL joining scheduling and payroll systems into the same warehouse, reconciling over a million timesheet rows for labour-cost-per-venue reporting.
- Executive dashboards in Domo for daily revenue, running totals, and season pacing across all venues and countries.
Result
- Partner invoicing moved from manual assembly to a warehouse-driven pipeline — every invoice traceable to fact-level data.
- Contract-tier positions visible daily across revenue-share agreements approaching £2M a year.
- One reporting stack covers three countries of venues: same definitions, same numbers, from ride photo to boardroom.
- The warehouse absorbed new feeds (vending payments, digital delivery, workforce) without redesign — new sources become new facts, not new systems.