Scope note: these definitions are derived from a three-council sample (Adur & Worthing, Gloucestershire, Sefton). Each English council publishes its own schema, so full coverage of ~317 councils requires a per-council scrape and a per-council column mapping like the ones below. Nothing here should be assumed to generalise beyond the sampled files without inspecting each new council's headers.
Before any variable can be computed across councils, each file must be mapped to a common transaction schema. Sample-specific steps:
adur-june-2026.csv, worthing-june-2026.csv,
adur-worthing-joint-june-2026.csv): strip the mis-encoded £ byte
(Windows-1252 £ read as UTF-8, appears as �) and thousands commas from
Amount; trim leading spaces in Supplier; Department is blank on some
rows (only Procurement_Class_Type populated).Payment Amount is mostly negative (credit-side
convention) — take absolute value / flip sign; a few rows are positive
(e.g. suspense-account items), so flag mixed signs rather than blindly
negating. Header typo Service Devison is in the source.sefton-april-2026.csv has a title line on row 1, headers on
row 2; sefton-march-2026.csv has headers on row 1 — detect header row per
file. Uppercase headers.DD/MM/YYYY dates. Supplier names need case/whitespace/suffix
normalisation ("Ltd"/"Limited") before any cross-council matching.Common transaction fields and where each council carries them:
| Variable | Adur & Worthing (all 3 files) | Gloucestershire | Sefton |
|---|---|---|---|
payment_date |
Posted_Date |
Payment Date |
TRANSACTION DATE |
supplier |
Supplier |
Supplier Name |
SUPPLIER |
amount_gbp |
Amount (strip £/commas) |
Payment Amount (sign-flip) |
AMOUNT |
service_area |
Department (+ DepartmentSubsection; blank on some rows) |
Service Area (+ Service Devison) |
DEPARTMENT |
transaction_id |
Transaction_ID |
Transaction No |
— (none) |
expense_type |
Expense_Type (often blank) / Procurement_Class_Type |
Expense Type (+ Expense Code) |
SUMMARY OF EXPENDITURE (+ ACCOUNT, COST CENTRE) |
capital_revenue |
Capital\Revenue |
Capital / Revenue |
— (none) |
council |
Entity (Adur / Worthing / Joint) |
constant per file | constant per file |
Present in only some councils (kept as optional columns, not part of the common
schema): Adur & Worthing AP_Ac_Postcode (supplier postcode); Gloucestershire
BVA COP, Service Division Code, Charity & Company Number, Industry,
Account Group, Account Name, Comment.
Verified availability: all three councils have date, supplier, amount and a service-area field. Transaction ID and capital/revenue exist only for Adur & Worthing and Gloucestershire. Supplier postcode only for Adur & Worthing; company/charity number and sector classification only for Gloucestershire.
All are computed per council per month (or pooled months) after normalisation. Supplier-level constructs depend on supplier-name normalisation quality; there is no shared supplier ID across councils (only Gloucestershire has an — often blank — company-number column).
supplier_hhi — supplier concentration (Herfindahl–Hirschman).
Formula: sum_i (spend_i / total_spend)^2 over suppliers i.
Inputs: supplier, amount_gbp. Unit: index 0–1 (or 0–10,000).
Interpretation: closer to 1 = spend concentrated in few suppliers; compare
only within like service areas — a county's social-care block contracts will
mechanically concentrate spend.
top10_supplier_share — share of monthly spend going to the 10
largest suppliers. Formula: sum(top-10 supplier spend) / total_spend.
Inputs: supplier, amount_gbp. Unit: proportion 0–1.
Interpretation: robust companion to HHI; high values suggest dependence on a
few contractors (risk / negotiating-position signal, not itself waste).
duplicate_payment_candidates — count (and value) of transaction pairs
with same supplier, same amount_gbp, payment_date within N days
(e.g. 7), and — where available — different transaction_id.
Inputs: supplier, amount_gbp, payment_date, transaction_id.
Unit: count / £. Interpretation: candidates only — recurring identical
invoices (rent, care packages: see the Sefton £511.00/£511.09 same-day pairs)
are legitimate; Sefton has no transaction ID, so its candidates cannot be
de-duplicated against the ledger and will over-count.
price_dispersion_same_supplier — for suppliers matched (by normalised
name) across ≥2 councils, dispersion of per-transaction amounts, e.g.
coefficient of variation or IQR of amount_gbp, per supplier per expense
category. Inputs: matched supplier, amount_gbp, expense_type.
Unit: dimensionless (CV) or £. Interpretation: rough cross-council price
signal; weak without unit quantities (an invoice total is price × unknown
volume), so treat as a screening statistic, not a price index.
agency_staff_spend_share — share of spend to employment/agency-staff
suppliers, identified by supplier-name matching against a maintained list of
known agencies (e.g. Matrix SCM, Reed, Hays, Adecco, Randstad,
Comensura) plus name patterns ("recruitment", "staffing", "locum");
Gloucestershire's Expense Type/Account Group can corroborate.
Formula: spend_to_matched_agency_suppliers / total_spend.
Inputs: supplier, amount_gbp (+ expense_type). Unit: proportion 0–1.
Interpretation: proxy for reliance on temporary staff; undercounts where
agency spend is routed through a neutral-vendor intermediary, and Sefton's
exclusion of staff payments makes its figure non-comparable.
monthly_spend_total / transaction_count — denominators for the
above: sum(amount_gbp) and row count per council-month.
Unit: £ / count. Interpretation: comparable only after documenting each
council's exclusions (see Sefton) and sign/VAT conventions.
Extracted:
Constructed: