How I turn engineering questions into data analysis.

I started from the official release, not from a chart.

The source publishes civil-engineering material indices by activity. For several road activities there are paired series — one includes bitumen and the other excludes it. That immediately creates an analytical problem: if somebody says “road materials increased by X%”, which of those legitimate series are they actually using?

I converted the publication into an analysis-ready table.

The raw publication is designed for reading, not querying. Activity names carry analytical meaning inside the text, decimal-comma values need numeric conversion, and some historical values are unavailable. I therefore created separate fields for activity, period, index, pct_yoy and bitumen_treatment.

I did not fill missing historical observations with zero because zero would mean “no movement”; missing means the comparison value is not available.

I paired the two versions of each activity with a SQL self-join.

Once the table was in long format, I could pair the included and excluded versions of the same road activity and calculate the difference directly.

SELECT
    i.activity,
    i.pct_yoy AS incl_bitumen,
    e.pct_yoy AS excl_bitumen,
    ROUND(e.pct_yoy - i.pct_yoy, 1) AS gap_pp
FROM civil_material_index i
JOIN civil_material_index e
  ON e.activity = i.activity
 AND e.period   = i.period
WHERE i.bitumen_treatment = 'included'
  AND e.bitumen_treatment = 'excluded'
  AND i.period = '2025-10'
ORDER BY gap_pp DESC;

The point of the join is not to make the SQL look complicated. It makes the comparison explicit: same activity, same month, only the bitumen treatment changes.

I then checked contribution, not only the biggest percentage move.

A material can have a dramatic percentage increase but a small effect on the total basket if its weight is small. So the second part of the analysis ranks weight × price movement, rather than ranking percentages alone.

SELECT
    product,
    weight,
    pct_yoy,
    ROUND(weight * pct_yoy / 100, 2) AS contribution_pp
FROM construction_input_index
WHERE period = '2025-10'
ORDER BY ABS(contribution_pp) DESC;

This is the practical difference between reporting “the biggest price increase” and explaining “what actually pushed the overall construction-input index”.

I validated the extract before writing the conclusion.

The current validation file checks that all 13 activity rows are present, the October 2025 index is populated, the bitumen treatment field contains only the expected values, and the two known historical comparison gaps remain visible as warnings rather than being silently imputed.

I defined one information-event structure.

RFIs, submittals and deliverables have different workflows, but the reporting questions overlap: when was it created, when was it due, who owns it, is it still open, how old is it, how long did it take to close and which package or discipline is affected?

I separated open ageing from closed cycle time.

An open RFI does not yet have a completed cycle time. For open items I calculate age from the creation date to the reporting cut-off. For closed items I use the elapsed time between creation and closure. Mixing those two measures would make the KPI difficult to interpret.

SELECT
    package,
    COUNT(*) FILTER (
      WHERE record_type='RFI' AND status='Open'
    ) AS open_rfis,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
      ORDER BY CURRENT_DATE-created_date::date
    ) FILTER (
      WHERE record_type='RFI' AND status='Open'
    ) AS median_open_age_days
FROM engineering_information_event
GROUP BY package;

I designed measures that answer operational questions.

The full evidence pack includes the DAX versions of these measures so the reporting logic can be inspected rather than inferred from the chart.

I validated the event structure before analysing performance.

The checks confirm 52,000 rows, 52,000 unique event IDs, only the three expected record types, no missing created/due dates, no negative cycle days and no closed records missing their closure date.

I separated ingestion from publication.

A source being reachable does not mean its new values should immediately replace a trusted dashboard. The pipeline records the raw payload first, then checks the structure and completeness before promoting anything.

I kept classification explainable.

For road/asphalt analysis I retain the matched words that caused the classification. Explicit terms such as asphalt, hot mix, premix, overlay or milling and overlay are stronger evidence than a generic “roadworks” label. Seal/reseal/slurry/chip-seal work is kept separate from hot-mix asphalt.

Professional consulting/design tenders can still belong in civil procurement intelligence, but they do not automatically contribute to the asphalt works-demand signal.

The next validation is classifier precision and recall.

I do not publish an accuracy percentage until there is a manually reviewed validation sample. The planned test is straightforward: manually label a sample of real tender descriptions, compare the human label with the Yatify result, then calculate precision, recall, false positives and false negatives.