stn 03 · quantil — client engagement
Forecasting Energy Demand Across Three Planning Horizons
Built the model that turns a production plan into the electricity it will need — per-unit regression over six years of daily data that had never been assembled in one place, serving three planning horizons from one set of fitted models.
sec 01 · brief
The problem
A national oil and gas operator plans production on three clocks at once: an operating plan three months out at monthly resolution, a financial plan three years out at monthly resolution, and a long-range plan ten years out at annual resolution. Each carries an electricity figure beside its production figure, because the fields both buy power and generate their own, and the balance between the two is a capital decision.
Those electricity figures had no model behind them. The data to build one existed, just not in one place. Consumption was metered daily per frontier, one file per month. Autogeneration was daily in a different layout. Production came in three incompatible layouts across three overlapping sources, water was reported monthly while everything else was daily, and nothing maintained the correspondence between the frontiers that meter energy and the units that own the fields. Thirty frontiers had no field assigned at all.
The energy warehouse was the intended source and was rejected on inspection as incomplete and out of date. That set the real shape of the work: before anything could be forecast, six years of raw monthly files had to be reconciled into one daily panel per operating unit.
sec 02 · approach
What I built
A static regression per operating unit, not a time-series forecast. For a unit on a given day, energy is modelled as a function of that day's production — crude, gas, light hydrocarbons, water — and two calendar terms. There are no lagged energy terms. That is the whole trick: every planning document already states the production volumes it assumes, so the model is handed its inputs and never has to roll its own past forward. The horizon lives in the input table rather than in the model, and one set of fitted models serves all three plans.
The cost is an assumption worth naming. With no autoregressive term, the model treats each unit's energy-to-production mapping as stationary across the window. Where that mapping changed — a frontier moved between units, a plant came online — the model has nothing to represent the break except whatever the production variables happen to carry.
Two models per unit, because the plans disagree about water. The operating plan carries no water column; the other two do, and water turned out to carry much of the signal. Four feature sets × three algorithms gives twelve candidates per unit, selected on test MAE, with the best water model and the best no-water model both kept. Each ships beside its own fitted preprocessing pipeline, so inference applies the transformations training fitted, not a reimplementation of them.
sec 03 · method
Method
01
Source reconciliation
Eleven source files, 2018–2024. Energy arrives twice. Consumption is one file per month per frontier: rows carrying an internal ID are the reactive matrix and are dropped, and where a type column exists only active energy is kept. Frontiers are filtered to production, excluding transport and refining. Autogeneration is concatenated across years and filtered to generating frontiers. Both are mapped to operating units through a correspondence table built for the purpose, because frontier, field and unit names disagree across sources and thirty frontiers carry no field. That makes the unit, not the field, the only level everything joins at. Three units meter autogeneration and no purchased power.
energy[u,t] = Σ consumption[f,t] over production frontiers f → u + Σ autogen[g,t] over generating frontiers g → u # consumption: drop reactive-matrix rows; keep type = ACTIVA# dates normalised to %d/%m/%Y, then month → year → all02
Unit conversion and alignment
Production arrives in three layouts. The 2018–2021 daily files needed header normalisation only. The 2022–2024 gross files are in thousands of barrels of oil equivalent per day, so they are scaled by 1,000 and melted wide-to-long. Where the gross files and the consolidated 2020–2024 file overlap, they disagree, and the row-wise maximum is taken. Water is monthly: each month's volume is spread uniformly across its days and converted from cubic metres to barrels. That leaves a stepped daily series, and the step is an artifact of the source's granularity rather than a property of the field.
gross[KBPE/D] × 1000 → BPE/Dprod[u,t] = max(gross[u,t], consolidated[u,t])water[u,t] = water_month[u,m] / days(m) × 6.289 # m³ → bblpanel = water ⋈ production ⋈ energy on (unit, date)03
Outlier removal and interpolation
Two custom scikit-learn transformers, so training and inference apply identical treatment. The first computes a mean and standard deviation per unit per quarter per variable and nulls anything beyond three of them. The window is quarterly rather than global because a six-year series with real regime changes would otherwise have whole true periods flagged. For energy it also nulls exact zeros, since a metered zero is a reporting gap, not a day without power. The second reindexes each unit-quarter onto a continuous daily range and interpolates on the time index. Energy and light hydrocarbons needed the most correction; one unit's energy series had roughly 19.5% of its records replaced, against roughly 5% everywhere else.
q = quarter(t)flag(x) = |x − μ[u,q,v]| > n·σ[u,q,v] n = 3 ∨ (v = energy ∧ x = 0)x ← NaN where flag; reindex(u,q) → daily; interpolate(method="time")04
Features, candidates and selection
Four feature sets per unit: water present or absent, crossed with a cyclical day-of-year encoding present or absent. The encoding maps the calendar onto a circle so that 31 December and 1 January are neighbours rather than 364 units apart. Three algorithms over each feature set: ordinary least squares, random forest and gradient-boosted trees. That is twelve candidates per unit and 120 in all, scored on a held-out test split. The lowest test MAE wins, separately for the water and no-water families, which gives 20 shipped models and 20 shipped pipelines. Every selected water model is a tree ensemble on the full water-plus-calendar feature set.
x[u,t] = [crude, gas, whites, (water), (sin 2π·d/365, cos 2π·d/365)]E[u,t] ≈ f_u(x[u,t]) # no lagged E: static regression F = {±water} × {±calendar} A = {OLS, RF, XGB}f*[u,water] = argmin MAE_test over F_water × Af*[u,nowater] = argmin MAE_test over F_nowater × A05
Evaluation
Four metrics per candidate on train and test: MAE for selection, MAPE for comparison across units whose energy differs by two orders of magnitude, and R² and RMSE for fit. Models were fit and scored daily. Monthly figures are the same daily predictions summed by month and unit, then rescored. They are not a separate monthly fit.
MAE = 1/n Σ |y − ŷ|MAPE = 100/n Σ |y − ŷ| / yRMSE = √(1/n Σ (y − ŷ)²)R² = 1 − Σ(y − ŷ)² / Σ(y − ȳ)² monthly: ŷ[m] = Σ_{t∈m} ŷ[t], then rescore06
Horizon-specific inference
The same fitted models serve every horizon; only the input table changes. Under a year, monthly, from the operating plan, on the no-water models, with water opt-in per unit where the plan supplies it. One to three years, monthly, from the financial plan, on the water models. Ten years, annual, from the long-range plan. Per-project estimation was tested there and abandoned, because individual project volumes sit far below anything in the training data and a tree ensemble cannot extrapolate outside the range it was fitted on. Instead the unit is predicted whole and its energy is apportioned to projects by production share, weighted by each variable's importance in the unit's model. The shares sum to one by construction.
share[p] = Σ_v w[v] · pct[v,p] v ∈ {crude, whites, gas, water}Σ_v w[v] = 1, Σ_p pct[v,p] = 1 ⇒ Σ_p share[p] = 1E[p] = share[p] · Ê[u]07
Delivery and retraining
A web tool ingests Excel or CSV, validates it, and returns two formats. The first is the input with a predicted-kWh column appended. The second is a planning-tool export at vice-presidency level in megawatts, carrying medium, high and low scenarios, where high and low are the prediction plus and minus one standard deviation of that unit's training data. Retraining stays in three notebooks — preprocessing, per-unit training, best-model selection — each serialising models, pipelines and metrics, so the team can inspect the charts behind a selection rather than accept it.
reject rows ≤ 1 | populated < 50% | duplicate column namesrequire FECHA, GERENCIA, CRUDO, GAS, BLANCOS (+ water, field, project)reject NaN | negative after group-by unit | non-numeric | date ≠ yyyy-mm-ddhorizon 1–11 consecutive months | 12–120 months | ≥ 10 consecutive years scenario high, low = ŷ ± σ_u(train)
sec 04 · legend
Instruments
legend · modelling
legend · preprocessing
legend · evaluation
legend · platform
legend · delivery
sec 05 · readings
Results
Models trained
120
10 units × 4 feature sets × 3 algorithms
Models shipped
20
2 per unit — with and without water
Monthly MAPE, with water
0.18 – 1.76 %
10 of 10 units under 2 %
Monthly MAPE, without water
0.64 – 25.51 %
9 of 10 units under 2.4 %
Daily test MAPE, with water
1.30 – 14.67 %
8 of 10 units under 5 %
Daily test MAPE, without water
1.70 – 41.21 %
6 of 10 units under 5 %
Test ÷ train error, with water
≈ 2.7× median
range 1.5× – 48× · 10 units, daily MAPE
Monthly R², with / without water
0.56 – 1.00 / 0.60 – 1.00
9 of 10 at 0.96 or above on both
Selected family
RF 6 · XGB 4 · OLS 0
10 water models, lowest test MAE
plate 01
Water is small in points and large in ratio
BARITE
BASALT
GALENA
GYPSUM
JASPER
OLIVINE
PYRITE
QUARTZ
RUTILE
ZIRCON
Monthly MAPE per unit for the selected water model and the selected no-water model — the two models the tool actually ships. Eight units lose under one percentage point when water goes, which is what makes the short-horizon plan usable at all. The median unit's error still rises about 2.5×, and one unit's rises from 1.75% to 25.51%: water is the only variable that carries its regime change.
source
Selected models, daily predictions summed by month and rescored. The axis stops at 3 to keep nine rows legible; the value past it is pinned and printed. Unit names are pseudonyms; the figures are the real ones.
plate 02
The generalisation gap, and the one unit that memorised
BARITE
BASALT
GALENA
GYPSUM
JASPER
OLIVINE
PYRITE
QUARTZ
RUTILE
ZIRCON
Daily MAPE for each selected water model on held-out days against the days it was fit on. The test error runs a median 2.7× the train error, which is ordinary for tree ensembles. One unit reads 0.07% on train and 3.38% on test, a 48× gap: its boosted model nearly reproduces its training set, and only the test figure is its real error.
source
Selected water models, daily resolution. Test is the solid mark and is named first because it is the result; train is there to measure the gap. The split scheme is not stated in the source, so this gap is a lower bound — see the notes.
plate 03
Daily resolution shows what monthly hides
BARITE
BASALT
GALENA
GYPSUM
JASPER
OLIVINE
PYRITE
QUARTZ
RUTILE
ZIRCON
The water ablation again, scored where the models were actually fit: daily, on held-out days. Monthly sums let daily errors cancel, so the cost of dropping water is larger here — only four units stay within one point, and GALENA nearly doubles from 5.16% to 9.45%. This is the honest picture of the models; plate 01 is the picture of the product.
source
Test split only, selected models. The source's prose says the increase is under one point for most units; its own chart shows four of ten. The chart is used.
plate 04
Nine units are explained; one is not, with or without water
BARITE
BASALT
GALENA
GYPSUM
JASPER
OLIVINE
PYRITE
QUARTZ
RUTILE
ZIRCON
Monthly R² for the same two models per unit: the share of each unit's monthly energy variation the features account for. Nine units sit at 0.96 or above either way. PYRITE sits at 0.56 and 0.60, and water barely moves it, so the missing driver is not water. It also has the lowest daily error on the sheet: a model can track a level closely and still not explain why it moves.
source
Selected models, monthly aggregation as plate 01. Daily R² is not plated: the source's prose and chart name different units, and one chart label is unreadable.
plate 05
What won, per unit
| unit | algorithm | features | test MAPE % | test ÷ train |
|---|---|---|---|---|
| BARITE | XGBoost | water + calendar | 2.73 | 2.4× |
| BASALT | Random forest | water + calendar | 2.19 | 5.1× |
| GALENA | Random forest | water + calendar | 5.16 | 3.1× |
| GYPSUM | Random forest | water + calendar | 2.19 | 2.4× |
| JASPER | XGBoost | water + calendar | 3.38 | 48× |
| OLIVINE | XGBoost | water + calendar | 3.97 | 3.2× |
| PYRITE | Random forest | water + calendar | 1.30 | 2.6× |
| QUARTZ | XGBoost | water + calendar | 4.32 | 1.5× |
| RUTILE | Random forest | water + calendar | 14.67 | 2.7× |
| ZIRCON | Random forest | water + calendar | 3.24 | 2.8× |
The selected water model for each unit: 12 candidates in, one out. Every winner is a tree ensemble on the full feature set; OLS won nowhere, and no feature set without both water and the calendar terms won anywhere. The right-hand columns repeat plate 02 as text.
source
Algorithm and feature set read from each best-model chart's title in the source. The no-water winners are not titled there, so they are not listed.
sec 07 · findings
What the data said
finding · result
Monthly aggregation is where the tool is accurate
Every unit's monthly error with water sits between 0.18% and 1.76%, and nine of ten monthly R² values are 0.97 or above. Monthly is also where the error is flattered: daily over- and under-predictions cancel inside a month, so the daily test figures, 1.30–14.67%, are the honest measure of the models and the monthly ones are the measure of the product. The plans are monthly and annual documents, so the product number is the one the client consumes.
finding · result
Tree ensembles won every unit; the linear baseline never did
All ten selected water models are tree ensembles on the full water-plus-calendar feature set: random forest for six units, XGBoost for four. OLS won nowhere. That is what a non-linear, regime-dependent energy–production relationship looks like from the selection table: a single slope per variable cannot represent a unit whose load steps when a plant comes online. The flip side is that trees do not extrapolate, which is exactly why per-project estimation on the ten-year plan failed.
finding · result
Water is small in points and large in ratio
Dropping water costs eight of ten units under one percentage point of monthly error, which is what makes a usable short-horizon model possible at all, since the operating plan states no water. In relative terms the cost is larger: the median unit's monthly error rises about 2.5×. At daily resolution on held-out days, only four of ten units stay within one point, and GALENA goes from 5.16% to 9.45%.
finding · negative result
One unit's energy changed regime, and only water saw it
RUTILE is the worst unit on every error plate: 1.75% monthly with water and 25.51% without; 14.67% daily test with water and 41.21% without. Its energy runs between roughly 0.4 and 2.15 million kWh a day from 2019 into 2021, then falls in two steps to a flat ~0.07–0.1 million by late 2021. The report attributes the fall to its energy frontiers being redistributed to other units, which makes it a data-model problem, not a modelling one. Two technical readings follow. The no-water model cannot even fit it: train error is 32.29%, so this is underfitting, not overfitting. And MAPE punishes a level collapse: an error that is 1% at 1.5 million kWh is about 20% at 0.07 million, so the post-break years dominate the percentage however well the model tracks them in kWh.
finding · negative result
One unit's training error is near zero
JASPER's water model scores 0.07% daily error on train and 3.38% on test, a 48× gap. Every other unit sits between 1.5× and 5.1×. A boosted ensemble that nearly reproduces its training set has memorised it, and the test figure, not the train one, is the unit's real error. Selecting on test MAE is what kept this from becoming a headline number. The source records no depth or regularisation settings.
finding · negative result
One unit's energy is not explained by its production
PYRITE returns monthly R² 0.56 with water and 0.60 without, the only unit below 0.96 on either, and water barely moves it. So the missing driver is not water. The same unit has the lowest daily test error on the sheet, 1.30%. The two are reconcilable: MAPE is scaled by the level, while R² is scaled by the variance. A unit whose monthly totals barely move has little variance for R² to explain, and a small persistent bias consumes much of it while staying tiny as a percentage. That is a mechanism consistent with the figures, not one the source confirms.
finding · negative result
Sequence models were abandoned before they were tried
The engagement opened intending recurrent forecasting over the energy history. It was dropped on the finding that no unified, regularly updated base integrating production and energy existed to train one on. The work was re-planned around supervised regression tolerant of fragmented, uneven data. The report names building that base as the prerequisite for any advanced method later.
finding · negative result
The warehouse was unusable and the raw files won
The energy data warehouse was the intended source and was rejected on inspection as incomplete and out of date. Six years of per-month raw frontier files were parsed instead, which is most of the first two method stages and most of the engagement's risk.
finding · negative result
The dispatch-level output could not be built
Three output formats were designed and two shipped. The third distributes demand to individual electrical nodes, which requires a field-to-node correspondence. The most recent correspondence file covers 39 fields against 103 in the training data. Most fields could not be mapped, so the format was deferred rather than shipped partially populated.
finding · negative result
The long-horizon allocation is proven but not wired in
The importance-weighted share that apportions a unit's predicted energy to individual projects was validated in analysis. At the close of the MVP it had not been integrated into the tool, so the ten-year path runs at unit level in the delivered product.
sec 08 · forward
Recommendations
01
Consolidate the dispersed sources into one maintained base. The report names this as the prerequisite for sequence models, and it would collapse the first two method stages from most of the engagement into an ingestion step.
02
Re-split on time, not on rows. A forward-chaining split — train on everything before a cutoff, test after it — is the only test that matches how the tool is used, which is to predict dates it has not seen. It would also show whether the test figures on this page survive it.
03
Replace the ±1σ scenarios with prediction intervals. Quantile regression forests or per-unit residual quantiles give a high and low that mean “90% of days fall inside”, which a planner can act on.
04
Re-map the unit whose frontiers moved. Its regime change is an artifact of the correspondence table, so fixing the table is what fixes the model; retraining alone will not.
05
Constrain the memorising model: cap depth or raise the minimum leaf size until its train and test errors are on the same order.
06
Extend the field-to-node correspondence past 39 of 103 fields, and finish wiring the long-horizon allocation into the tool.
sec 09 · notes
Notes
disclosure
The client is described by category and never named. The ten operating units on this page are pseudonyms. Their real internal codes and names, the field names, the individuals, the internal systems and the document locations do not appear here in any form; they were dropped rather than renamed. Every quantitative figure — unit counts, model counts, spans, error and fit — is the real one from the engagement, unchanged.
limits
- The train/test split scheme is not stated. The report says a split was made and not how. If it was a random split over days rather than a temporal one, each test day has training neighbours on both sides. For a series this autocorrelated that flatters every test figure on this page, and plate 02's gap is a lower bound on the real one.
- Models were fit at daily resolution only. The monthly figures are daily predictions aggregated by month and unit, not a monthly re-fit; the report says a re-split was not viable without retraining. They inherit the daily models' behaviour and are not independent of them.
- The high and low scenarios are ±1 standard deviation of the unit's training target, not of the model's error. They describe how much the unit's energy moves, not how wrong the forecast is likely to be.
- Outlier rates per unit are described and never charted, because the source figure carries no data labels and the values could only be recovered by measuring pixels.
- The report's prose disagrees with its own figures in four places: a daily error digit, a count of units under 5%, how many units lose under a point without water, and which units fall below R² 0.9. The figures were taken as authoritative. Daily R² is omitted entirely, because that disagreement is the one no third source can settle.
- The long-horizon project allocation and the dispatch-level output were not delivered in the tool.
- Every energy series drops to zero at the right-hand edge. That is the data cutoff, not a reading, and the zero-as-outlier rule excluded it from training.
- The engagement delivered an MVP. Nothing here is a claim about the system in sustained production.