Completions

For franchisor CFOs + FP&A + BI directors

Multi-location KPI reporting your CFO and your franchisees can both trust

Cohort-framed rollup for the corporate view. Per-franchisee drill-down for the operator view. Same underlying data, two trust-aligned audiences — with same-store-sales math handled correctly and LLM-generated executive summaries per period.

By Jay Christopher11 min read

What this gets you

  • Per-location dashboards franchisees actually read — top 5 metrics, daily updated, scoped to their data only.
  • Cohort-framed rollups for the CFO — vintage cohort, size cohort, region cohort, tenure cohort. Real signal, not company-wide averages that bury the variance.
  • Same-store-sales comp handled correctly — only locations with 12+ months tenure, with edge cases (relocations, ownership changes, format conversions) explicitly modeled.
  • Per-franchisee data privacy enforced— each franchisee sees their own data, not their peer franchisee’s. Row-level security at the query layer, not the dashboard.
  • Auto-written executive summary per period — LLM commentary anchored in the cohort math so the narrative tracks the numbers.

Two audiences with contradictory needs on the same data

Multi-location KPI reporting has two audiences. The franchisor CFO wants system-level same-store-sales comp normalized for vintage, size, and region. They do not care which individual franchisee did what; they want to know whether the 36-month-vintage cohort is growing faster than the 12-month-vintage cohort. The franchisees want their own per-location dashboard — same-store comp, conversion, AOV, NPS — but only their own data, not their peers’.

Power BI, Looker, Tableau, Qlik, and Domo all assume one audience and one access model. Serving both audiences inside the same tool requires custom dataset-permission engineering that breaks every quarterly schema change and grows brittler with every new franchisee onboarded. Two parallel deployments — one for corporate, one for operators — drifts on the math the first time someone changes a metric definition in one place and not the other.

The fix is a rollup-and-drill architecture. Each franchisee sees their per-location dashboard against their data only. The franchisor sees the cohort-framed rollup — same-store comp by vintage, region, size, and tenure — and never sees per-individual-franchisee data unless explicitly cleared. Same underlying data layer, two views, two trust models, one math.

The CFO trusts the rollup because the cohort math is canonical. The franchisees trust the per-location dashboard because the numbers match what they see in their own POS, Shopify, and GBP. The system stops hemorrhaging franchisee trust on every dashboard refresh.

What is in market — and what each category leaves to you

The BI canvas is mature. The cohort taxonomy, the same-store-sales math, the per-franchisee scoping, and the executive-summary automation are operator-side wiring on top of whichever BI tool you license.

Enterprise BI — Microsoft Power BI, Tableau, Looker, Qlik, Domo

Excellent visualization, semantic-layer modeling, self-service exploration, and row-level security. They do not natively model the multi-location cohort taxonomy or the same-store-sales math; both are operator-built on top of the platform.

Mid-market BI — Sigma, Mode, Hex, Metabase, Holistics

Lighter footprint, faster to deploy, similar primitives. Same constraints — the cohort taxonomy and per-franchisee access model are still your team to build.

Embedded analytics — Klipfolio, Geckoboard, Databox, Whatagraph

Excellent for franchisee-facing per-location dashboards. The corporate cohort rollup typically sits outside them, in the enterprise BI tool, which reintroduces the drift problem.

Data integration — Improvado, Funnel.io, Supermetrics

Strong on multi-source ingestion. Useful upstream of the cohort layer; not the cohort layer itself.

Internal BI analyst + custom RLS engineering

The status quo at most multi-location operators. Two BI analysts, custom row-level security per franchise, manual executive-summary write-up, $120-300k/year fully loaded — and franchisee complaints about dashboard accuracy on every refresh.

The architecture, end to end

  1. The cohort taxonomy. Vintage (opening year), tenure (months in operation), size (revenue band), region, format, product mix. Each location carries its cohort memberships and they update on a defined cadence as cohorts age in.
  2. The metric tree. North-star metric at the top, driver metrics one level down, operational metrics at the leaves. The CFO view rolls up to the north-star; the franchisee view drills down to the operational leaves.
  3. Same-store-sales math. Only locations with 12+ months tenure at both comparison points. Relocations, ownership changes, format conversions, and partial-month closures each modeled explicitly. Edge cases documented and version-controlled.
  4. Multi-source data integration. POS, Shopify, GBP, GA4, lifecycle, CRM, call analytics, and vertical-specific sources feed into the canonical per-location record. Reconciliation runs T+1.
  5. The rollup-and-drill query architecture. One canonical data layer; two query patterns — rollup (aggregate up to cohort) and drill (project down to per-location). The math is shared so the views never diverge.
  6. Per-franchisee data scoping. Row-level security rules evaluated at query time against the user identity and the location-to-franchisee mapping. A dashboard URL shared by one franchisee will not leak data to another.
  7. Executive KPI dashboard. 5-7 north-star metrics, cohort-framed comp, period-over-period delta, cohort velocity. Monthly cadence for the board view, weekly for the leadership view.
  8. Per-franchisee drill-down dashboard.Top 5 operational metrics for the location, with comp against the franchisee’s own historical baseline and against the relevant cohort. Daily refresh.
  9. LLM-generated executive summary. Period-over-period commentary on cohort movement, anomaly callouts, and forward-look framing — written against the cohort math, not against raw metrics.
  10. Drill-down audit trail. Every cross- tenant query logged. Compliance-defensible record of who saw what when.
  11. Integration with existing BI.Layer-on or replace, depending on the operator’s sunk cost. The cohort layer publishes the same canonical metrics regardless of which BI tool consumes them.
  12. Failure-mode handling. Cohort definition drift, same-store-math edge cases, and franchisee permission leaks each have explicit detection + alert rules. Failures surface before they reach a dashboard.

Frequently asked

What is KPI reporting at a multi-location operator?

KPI reporting at a multi-location operator combines two views on the same underlying data: an executive rollup for the CFO that shows cohort-framed system-level performance (same-store-sales comp by vintage, region, and size) and per-location dashboards for each franchisee that show only their own data. Most BI platforms (Power BI, Looker, Tableau, Qlik, Domo) are designed for one audience at a time; serving both requires the rollup-and-drill architecture underneath.

What is cohort framing in KPI reporting?

Cohort framing groups locations by shared attributes — vintage (opening year), tenure (months in operation), size (revenue band), region, format, product mix — and reports KPIs against the cohort rather than against the company-wide aggregate. A 36-month-vintage cohort growing faster than a 12-month-vintage cohort is a meaningful executive signal; comparing one new location to the company-wide average is not.

How do you calculate same-store sales correctly?

Same-store-sales (also called comp sales or comparable sales) only counts locations open for at least 12 months at both the comparison point and the comparison period. New openings inside the window are excluded; closures are excluded from both sides. The math is a four-line formula most franchisor CFOs get wrong on the edge cases — relocations, ownership changes, format conversions, and partial-month closures each need explicit handling.

How is this different from Power BI, Looker, Tableau, Qlik, or Domo?

Those platforms own visualization, semantic-layer modeling, and self-service exploration. They support row-level security per user. They do not natively model the cohort taxonomy (vintage, tenure, size, region), the same-store-sales math, the per-franchisee scoping for hundreds of tenants, or the LLM-generated executive summary per period. Those are operator-side wiring on top of whichever BI tool you license.

How does per-franchisee data scoping work?

Row-level security rules enforce that each franchisee sees only their own location data. The rule is evaluated at query time against the user identity and the location-to-franchisee mapping, so a dashboard URL shared by one franchisee will not leak data to another franchisee. Corporate users see the full cohort-framed rollup; franchisees see their drill-down. Same underlying data, two trust-aligned views.

Can AI write the executive summary automatically?

Yes — LLM-generated executive summaries now produce defensible CFO-facing commentary on cohort movement, anomaly callouts, and forward-look framing. The cost is roughly $0.10 per cohort per period versus the two-to-four hours of human analyst time it used to take. The LLM writes against the cohort math, not against raw metrics, so the commentary is anchored in the same data the rollup uses.

Hire the agent that runs the rollup

The per-location-rollup-reporting agent owns the cohort taxonomy, the same-store-sales math, the per-franchisee scoping, and the LLM-generated executive summary. It produces the corporate rollup and the franchisee dashboards on the same canonical data layer.

Hire the rollup-reporting agent

We scope on the call and send a private checkout link after.

Related reading: Multi-location reporting · Two-sigma outlier flagging