The BigQuery portfolio reporting pattern.
For PE-backed multi-brand portfolios, BigQuery + Looker as the reporting layer is now the operational standard. Why platform-native dashboards stitched into a deck don't satisfy PE-portfolio reporting requirements, and what the BigQuery + Looker + NL2SQL stack actually delivers.
What goes into BigQuery
For a Partnered-Health-shaped engagement (60+ clinics, multi-brand portfolio, PE-backed), the BigQuery dataset consolidates:
- Google Ads accounts (multiple, one per brand or one per programme, patient acquisition + GP recruitment)
- Meta Business Manager (multiple ad accounts, multiple brand pages)
- LinkedIn paid (where applicable)
- GA4 (cross-domain across multi-brand domains)
- Klaviyo / HubSpot / Salesforce CRM event data
- Booking platform event data (HotDoc, Best Practice, Cliniko for healthcare)
- Xero invoicing data (for ad-spend pass-through verification)
All piped to BigQuery. All refreshed daily. All queryable independently of platform-native attribution windows.
What gets visualised in Looker
Per-brand performance
Brand-level patient bookings (healthcare), B2C orders (consumer), tier-1 enquiries (industrial). Trended over time, comparable across brands within the portfolio.
Channel-level attribution
Meta + Google + LinkedIn + organic search + organic social + email + direct + referral. Attribution-window discipline appropriate to each channel. iOS-14-resilient + Enhanced Conversions integration.
Cohort retention
Patient retention by clinic by service line by initial booking source (healthcare). B2C cohort retention by acquisition channel by AOV tier (consumer). Industrial B2B client tenure cohorts (industrial). Long-term value per acquisition channel.
Ad spend pass-through verification
Auditable verification that ad spend on the dashboard matches platform-native spend reports, and matches Xero invoice line items. Transparent commercials, transparent reporting. PE-portfolio CFOs reconcile against this layer.
Anomaly detection
Vertex AI anomaly detection on ad-account spend velocity. Alerts on suspicious cost-per-acquisition shifts, spend-velocity surges, conversion-rate drops. Caught before they become invoices to dispute.
NL2SQL self-service
"How many GP appointments did we book in Brisbane last month vs prior month?", asked in plain English by a portfolio manager, answered against BigQuery in seconds. The system writes the SQL, runs it, returns the answer with a 1-line explanation.
Self-service for non-technical portfolio managers. Removes the agency-as-data-team-bottleneck pattern that slows down portfolio operations.
Why most agencies don't run this layer
Three reasons:
- BigQuery + Vertex AI engineering is a discipline most agencies don't have
- PE-portfolio engagements where the layer is justified are a small share of most agencies' books
- Platform-native dashboards stitched into a deck satisfies most non-PE clients
Read more:
PE-portfolio CMO needing serious reporting?
Forty-five-minute call. We'll demo the BigQuery + Looker + NL2SQL stack on an anonymised sample portfolio.
Book a discovery call