100

A practical UK procurement data workflow for commercial intelligence

Want a UK procurement workflow that turns notices, awards, suppliers, documents, and spend into commercial signals? Start with the procurement process anchor: `ocid`, then scope to procedures under the Procurement Act 2023 using `tender_legalBasis_scheme = UKPGA`, and use that set of `ocid` values to filter every child sheet through `main_ocid`[1][2].

  • bi
🧵 1/6

The join logic is simple but strict. Use `main_ocid` to connect child sheets back to the main sheet, then use `[parent_sheet]_id` for nested tables like `awards_suppliers.awards_id = awards.id` and `contracts_documents.contracts_id = contracts.id`[3].

🧵 2/6

For supplier intelligence, join `contracts` to `awards_suppliers` with `main_ocid` and `contracts.awardID = awards_suppliers.awards_id`, then link `parties` rows where roles contain supplier. Aggregate back to one row per contract, because a contract can have multiple suppliers. For procurement method, go back to the main sheet because contracts do not carry it directly[4][5].

🧵 3/6

For timelines and value, the guide says `date` on the main sheet is the latest update date, but `contracts.dateSigned` or the first notice publication date may be better depending on the metric. It also points to combining notice document sheets by `noticeType`, and to contract value by method while excluding framework agreements[6][7][8].

🧵 4/6

Practical KPI stack: direct-award share means the proportion of direct award procedures; single-bid lots means the proportion of lots receiving only one bid; SME participation means the proportion of contracts awarded to SMEs by method. The guide does not give a full bidder-count formula, and it warns that below-threshold supplier IDs are often missing, so exact supplier-level spend splits are limited[9][10][11][12][13][14].

🧵 5/6

Use this as a dashboard checklist: scope to UKPGA records, join notices to awards to contracts to parties, add documents and spend fields, then track direct-award share, single-bid lots, and SME participation for incumbent mapping, deadline triage, and buyer performance review. No workflow-specific image was verified, so the dashboard screenshot is the closest visual fit[15][16][17][18][19][20][21][22].

🧵 6/6

Related Content From The Pandipedia