Scorecard Data for Excel Rollout

Source workbook: docs/assets/Peppermint IT 9 Points Revenue and Resource Map Jul26.xlsx

The goal is to provide stable source data for an Excel scorecard workbook on SharePoint. The workbook owner should be able to use Power Query to refresh available CRM/accounting data and then build or maintain the workbook model in Excel. The backend should provide a clear data contract; it should not rebuild the scorecard model or add business modelling that belongs in the workbook.

Current Workbook Observations

The workbook contains several scorecard/dashboard views and input-style sheets:

Profit and Loss is already shaped like a monthly P&L export: account rows grouped under sections such as Hardware, Recurring, Projects, Other Income, COGS, and Operating Expenses, with month columns.
Customer & Sales Analysis expects product/category sales summaries, customer summaries, recurring revenue details, and monthly sales values.
Target Recurring Client Matrix derives target recurring revenue and client counts from costs, revenue mix, and package assumptions.
Scorecard, Scard, and Dashboard contain scorecard formulas and some broken external references / #REF! values. Treat these as workbook model issues, not backend data contract issues.

Implication: first deliver clean query tables that can be loaded into staging sheets or Power Query connections. Do not write directly into scorecard formula areas until the refresh contract has been validated.

Rollout Principle

Split the rollout into two tracks:

Zoho CRM data: expected to be available now from existing middleware report routes, with small contract changes only if the workbook needs additional fields.
Xero P&L data: an initial research task. Validate whether the standard Xero P&L report can be queried and exposed as Xero returns it, without trying to reshape it to match the current workbook.

Data Contracts

Zoho CRM

Existing Basic-auth CSV routes already cover the named data families used by the CRM Power BI model.

Workbook need	Existing route	Notes
Deals	`/crm/deals-report`	Includes deal, company, stage, amount, probability, closing date, business category, plan type, staff count, ACV, owner, created/modified timestamps.
Clients	`/crm/companies-report`	Includes company type, `business_size`, industry, employees, `msp_staff_count`, rating, owner, created/modified timestamps. Do not use `msp_staff_count` for scorecard sizing because it is not currently maintained.
Recurring seats	`/crm/msp-seats-report`	Current active seats only. Includes MSP plan, contact, contact email, contact lead status, billing status, billing dates, owner, created/modified timestamps.
Recurring plan context	`/crm/msp-plans-report`	Current active plans only. Includes company, deal, plan status/type, billing dates, PO user count, active seat count, and total seat count.
Recurring monthly history	`/crm/msp-plan-history-report`, `/crm/msp-seat-history-report`	Reconstructed monthly history from CRM billing start/end ranges. Use this if the workbook needs monthly recurring client/seat trends rather than today's active state.

Client size should be derived from active MSP seats, not from the Account MSP_Staff_Count field. For the current-state scorecard, count active seats by joining current active seats to current active plans and grouping by company_id. For monthly trend views, use the monthly plan/seat history exports instead.

Size bands:

small: active MSP staff count < 8
medium: active MSP staff count 8-22 inclusive
large: active MSP staff count > 22

The Excel-facing contract should prefer these source tables:

scorecard_deals
scorecard_clients
scorecard_client_size
scorecard_msp_plans_current
scorecard_msp_seats_current
scorecard_msp_plan_months
scorecard_msp_seat_months

These names can be Power Query query names even if the HTTP routes keep the existing /crm/*-report names.

Xero P&L

Research question: can the standard Xero P&L report be queried reliably and served to Excel without manually rebuilding Xero's P&L logic?

Xero's Accounting API includes GET /Reports/ProfitAndLoss. In the Custom Connection portal, the selectable scope for this report is accounting.reports.profitandloss.read. The endpoint accepts fromDate, toDate, periods, timeframe, tracking category/option filters, standardLayout, and paymentsOnly, and returns a ReportWithRows structure.

Research needs:

Validate that the Custom Connection app is authorised for accounting.reports.profitandloss.read as well as the current invoice scopes, then confirm the runtime token can call the report endpoint.
Run a read-only request for the required date range using monthly periods and standardLayout=true.
Preserve Xero's report rows, sections, totals, and period labels. Do not remap rows to the workbook's existing P&L tab.
Expose a CSV that faithfully flattens the Xero rows for Excel Power Query.
Keep the rows in Xero order and include enough metadata to rebuild the report presentation in Power Query: row type, section title, row title, cell index, cell value, and period/report titles.
Keep a raw ReportWithRows JSON route optional for diagnostics.

Keep this as a research output until the live report proves that the standard Xero P&L can be queried with the required scope and period parameters.

Authentication

References:

Xero Accounting API reports: https://developer.xero.com/documentation/api/accounting/reports
Xero OpenAPI spec: https://raw.githubusercontent.com/XeroAPI/Xero-OpenAPI/master/xero_accounting.yaml
Existing Xero integration notes: docs/dev/xero_crm_invoice_sync.md

For Excel/Power Query access to backend-exported data, reuse the same Basic auth pattern already used by the Power BI report routes. The backend should own Xero authentication server-side; Excel should call a Peppermint backend route and should not need Xero OAuth credentials.

Current repo state:

Existing CRM report routes use basic_auth_required.
docs/dev/xero_integration.md documents the shared Xero Custom Connection setup and current authorised scopes.
xero_sync/auth.py uses client-credentials auth for Custom Connections without sending a scope parameter; Custom Connection scopes are managed in the Xero Developer portal.
xero_sync/auth.py OAuth authorization-code defaults include transaction/contact/settings scopes, but not accounting.reports.profitandloss.read.
Xero Custom Connections expose accounting.reports.profitandloss.read for Reports/ProfitAndLoss.

Scope added for the P&L proof: accounting.reports.profitandloss.read.

Build Readiness

There are no remaining blocking business questions before building a first Power Query facility and testing it on a spreadsheet copy.

Use these implementation defaults for the first build:

Authentication: reuse the existing backend Basic auth pattern used by Power BI report routes.
Xero credentials: keep Xero Custom Connection credentials server-side.
Xero scopes: use the re-authorised Custom Connection scope set documented in docs/dev/xero_integration.md; the Xero Developer portal must include accounting.reports.profitandloss.read.
Xero P&L output: expose Xero's standard Profit and Loss report as returned by Xero. Build a faithful CSV flattening first because Excel Power Query can consume it directly; keep raw JSON as a later diagnostic or secondary route if useful.
Xero P&L parameters: support explicit fromDate, toDate, periods, timeframe, standardLayout, and paymentsOnly query params. For the first spreadsheet test, use monthly periods with standardLayout=true.
Client size: build scorecard_client_size as a backend CSV route so Excel does not need to duplicate the active-seat counting rule.
Spreadsheet test: use a copy of the current workbook and load data into staging/query tables before wiring any scorecard formulas.

Remaining choices are non-blocking and can be adjusted after the first test:

exact default Xero date range when query params are omitted;
whether to keep both scorecard wrapper routes and the existing raw CRM report routes long term;
whether finance later wants Xero tracking-category filters or segmentation;
where the production workbook lives in SharePoint and who owns scheduled refresh credentials.

Rollout Plan

Phase 1: Confirm Workbook Data Inputs

Owner: workbook/process owner with backend support.

Actions:

Map the current workbook tabs to required source tables:
- Profit and Loss -> Xero P&L research feed.
- Customer & Sales Analysis -> deals, clients, and possibly Xero sales/P&L categories.
- Target Recurring Client Matrix -> recurring plan/seat current and monthly history tables.
Materialise scorecard_client_size as a backend wrapper route for the first version.
Include current recurring-seat data for the first spreadsheet test. Keep the monthly history exports available for trend views once the current-state refresh is working.

Exit criteria:

A field list exists for each Excel Power Query table.
Workbook owner confirms which fields are inputs and which are Excel-derived model outputs.

Phase 2: Publish Zoho Query Contract

Owner: backend.

Actions:

Reuse existing /crm/*-report routes for the first pass.
Add scorecard wrapper routes where they remove duplicated Power Query logic, starting with scorecard_client_size.
Document each query name, endpoint, refresh cadence, key columns, and expected data types.
Validate the routes with a smoke export and compare headers against the field contract.

Exit criteria:

Excel can refresh Zoho source tables from SharePoint using Power Query.
The workbook can join clients, deals, plans, and seats on stable IDs.
Any missing Zoho fields are explicitly logged as contract changes, not handled by ad hoc workbook edits.

Phase 3: Xero P&L Proof of Concept

Owner: backend with finance validation.

Actions:

Confirm the Xero Developer portal Custom Connection scope list includes the report scope, then re-authorise the app in a controlled non-production context before testing.
Add a read-only proof command or script that calls Reports/ProfitAndLoss for a selected date range.
Save a sanitized sample response shape for documentation, not private values.
Build a faithful CSV flattening for Excel Power Query and keep row order, section titles, row type, cell index, and cell values from Xero.
Record gaps:
- runtime token does not include the newly authorised scope,
- raw JSON vs faithful flattened CSV usability,
- period ordering,
- cash vs accrual basis,
- tracking-category requirements if finance later needs segmented views,
- rounding or total handling in the Xero output.

Exit criteria:

Decision recorded: "standard Xero P&L report endpoint is sufficient", "standard report is sufficient but needs faithful flattening", or "must reconstruct from lower-level Xero transactions".
If sufficient, a production endpoint, auth model, and field contract are ready for spreadsheet testing.
If not sufficient, document the minimum alternative source required.

Phase 4: Excel Integration

Owner: workbook owner with backend support.

Actions:

Add Power Query connections to staging tables, not formula areas.
Keep raw/staging tables separate from workbook calculations.
Name queries using the scorecard_* contract names.
Add refresh notes inside the workbook or adjacent documentation:
- required credentials,
- expected refresh cadence,
- known unsupported tabs/formulas,
- owner for data contract changes.
Run refresh from SharePoint and from the workbook owner's desktop Excel to catch credential and privacy-level issues.

Exit criteria:

Workbook refresh succeeds from SharePoint/Excel without manual CSV pasting.
Scorecard tabs still calculate after refresh.
Broken pre-existing formulas are either repaired by the workbook owner or documented as out of scope for the backend rollout.

Phase 5: Operate and Iterate

Owner: process owner with backend support.

Actions:

Treat the workbook as a reporting consumer of stable exported tables.
Review refresh failures as either source contract failures, auth failures, or workbook model failures.
Add backend tests for any new scorecard-specific routes or Xero P&L flattening logic.
Update docs/business_rules/reporting-assumptions.md only after a production reporting semantic is implemented and relied on by process owners.

Exit criteria:

Production refresh path is documented.
Ownership is clear for backend data contract vs workbook modelling.
Any recurring data quality checks are documented.

Risks and Decisions

Risk / decision	Current position
Xero P&L may not contain every segmentation wanted later.	First version should expose the standard Xero P&L as Xero returns it; segmentation can be a later decision.
Current Xero scopes are invoice-oriented.	P&L research needs `accounting.reports.profitandloss.read` in addition to the invoice scopes. Scope changes require Custom Connection portal updates, re-authorisation, and cache refresh.
Excel/Power Query authentication	Reuse existing backend Basic auth for Excel routes. Keep Xero OAuth/Custom Connection credentials server-side.
Existing workbook has broken references.	Treat as workbook model cleanup, separate from data feed rollout.
Client size source	Use active MSP seat counts from a query, not CRM `Accounts.MSP_Staff_Count`. Bands are `small` < 8, `medium` 8-22 inclusive, `large` > 22.
Active recurring seats vs historical recurring seats.	Use current exports for today's state; use history exports for month-by-month scorecard trends.
Workbook should not become the source of truth.	CRM remains source for client/deal/seat metadata. Xero remains source for accounting/P&L data.

Immediate Next Steps

Draft the Excel Power Query names and map them to existing Zoho routes plus the first scorecard wrapper route, scorecard_client_size.
Confirm the Xero Developer portal Custom Connection scope list includes the report scope and re-authorise the app if it changed.
Clear or refresh any stale .xero_token_cache.json and run a token/report smoke check.
Run the standard Xero P&L proof request with accounting.reports.profitandloss.read and decide whether Excel should consume the faithful flattened CSV only or also keep a raw JSON endpoint.
Build the backend routes and test refresh on a copy of the current workbook.