What Data to Extract From a Utility Bill: A Field-by-Field Schema

This schema contains 40 fields in five groups: identity, dates, usage, money, and derived values. Account number, total due, and usage may be enough for payment processing. Analysis also needs the service period, unit, meter or service identifier, current-period charges, and source document.

Use the table below as a starting point, then remove fields your use case does not need.

Grain: what one row represents

Choose the row grain before naming columns.

One row = one meter, one commodity, one service period, on one bill.

A bill can contain electric and gas service, several meters, or more than one rate period. One row per PDF cannot represent those combinations without nested columns or repeated groups. Keep a bill_id on every row so the meter-level records can still be reconciled to the source invoice.

Identity

These fields answer “which physical thing at which address does this consumption belong to.” Combining them into one generic identifier breaks portfolio-level reporting when an account or meter changes.

The account number identifies a billing relationship. It can cover one service or several. It may change when the customer or billing arrangement changes, and it can stay the same through a meter replacement.

The meter number identifies the device. When the utility replaces that meter, the new device normally has a different number. A time series keyed only on meter number will then split one service location into two records.

A location- or service-level identifier may be called a point of delivery ID, Service Agreement ID, Service Account Number, ESI ID, or premise ID. Its definition is utility-specific. PG&E prints a 10-digit Service Agreement ID in its charge detail; SCE distinguishes its 10-digit Service Account Number from its 12-digit Customer Account Number; Xcel prints a 9-digit Premise Number in Electricity Service Details. When the utility documents one of these as a service-level identifier, store it and map it to your internal site ID. The full breakdown is in account number vs meter number vs service ID.

The formats above are documented by PG&E, SCE, and Xcel Energy. Treat them as current examples, not cross-utility rules.

Rate schedule helps explain a cost change when usage stays similar. Current examples include PG&E B-19, SCE TOU-GS-3, Georgia Power TOU-GSD-17, National Grid Massachusetts G-2, and Con Edison SC 9 Rate I. Capture the code exactly as printed. Do not add or remove punctuation during extraction; the stored value should match the utility’s tariff documents.

Dates

Four date concepts appear on bills:

• Service period start and end. The window the consumption actually happened in. Printed as “Service Period”, “Billing Period”, “Service From/To”, or just “From / To” depending on the utility.
• Read dates. The dates associated with meter readings. They may match the service period, differ at a boundary, or be estimated rather than observed.
• Bill date (also “Statement Date”, “Invoice Date”). When the utility generated the document.
• Due date. Accounts payable cares. Analysis does not.

Use the service period for consumption analysis. A bill dated April 3 may cover usage from March, so grouping by bill date shifts the data into the wrong month. ENERGY STAR Portfolio Manager likewise asks for start and end dates for each meter entry rather than a statement date.

Store dates as ISO YYYY-MM-DD. US utilities print MM/DD/YYYY, occasionally 03/14/25, and Con Edison writes month names out. Normalize on ingest.

Usage

Consumption plus its unit, and the unit is not optional metadata. A bare number is unusable.

Electric is easy: kWh, sometimes with demand in kW alongside it.

Gas is not. CCF is one hundred cubic feet. MCF is one thousand cubic feet. A therm is 100,000 BTU, which is a heat unit, not a volume unit, so converting between them requires a heat-content factor that the utility prints on the bill and that changes with gas composition. It generally sits a little above 1.0 therm per CCF. Some bills print CCF and therms both; DTE bills gas in Ccf and Mcf; National Grid in Massachusetts bills in therms directly. Do not hardcode 1.0.

Water is worse, because the same physical unit has two abbreviations. CCF and HCF both mean one hundred cubic feet, which is 748 gallons. LADWP prints HCF, Seattle Public Utilities prints CCF, and plenty of Texas and southeastern utilities bill in kGal (one thousand gallons). Three different labels, three different magnitudes, one column.

Store the unit in the same row as the quantity. Keeping the original quantity and unit also makes later conversions visible and reversible.

Read type matters. An estimate followed by a correction can produce a low month and a high month even when underlying consumption was steady. A read_type field helps distinguish that billing pattern from an operational change.

For commercial electric service, capture demand and whether the figure is measured or billed. Some tariffs use a demand ratchet based on earlier peaks, so billed demand can exceed the current period’s measured demand. The lookback period and percentage are tariff-specific. Capture power factor when it appears or when the tariff uses it in billing.

Money

Total amount due is a payment figure. It may include the prior balance, fees, credits, and adjustments, so it should not automatically be treated as the current period’s utility cost.

What you need is the split. In a deregulated market, the bill separates supply (also “generation”, “energy”, “basic service”) from delivery (also “distribution”, “transmission”, “delivery services”). National Grid prints them as two clearly labeled sections. PG&E splits generation from delivery and then adds Power Charge Indifference Adjustment (PCIA) and franchise fee surcharges on the delivery side. Under a community choice aggregator, the generation charge belongs to a third party and appears as a pass-through line, or arrives as a separate invoice entirely.

Keep supply and delivery separate when the bill provides that split. In competitive markets, supply may be contracted separately; delivery remains with the local utility. In regulated markets, the distinction still helps explain a rate change even when the customer cannot choose a supplier. A total-only extraction loses that detail.

Capture taxes, fees, and late charges separately from both. Credits and adjustments should be a signed field, negative for credits. Bills print credits as $45.20-, (45.20), or 45.20 CR depending on the vendor’s billing system, and all three need to land as -45.20.

Derived fields: compute them consistently

Bills may print average daily usage or a cost per unit. Preserve those values only if you need to audit the bill’s presentation. For analysis, recompute them from the underlying fields so the whole dataset uses the same formula and rounding:

days_in_period    = service_period_end - service_period_start
average_daily_usage = usage_quantity / days_in_period
blended_rate      = current_charges / usage_quantity
load_factor       = usage_quantity / (demand_quantity * days_in_period * 24)

Pick a days_in_period convention and document it. End minus start gives 31 for a March 14 to April 14 period; inclusive counting gives 32. Compare the result with the bill’s printed day count and keep the chosen convention consistent.

The schema

Illustrative worked example

The following National Grid-shaped commercial bill is fictional. Names, identifiers, and amounts are invented; the calculations reconcile.

{
  "bill_id": "ng-2025-04-000418",
  "utility_name": "National Grid",
  "account_number": "0231-4408-712",
  "invoice_number": "88420176",
  "service_address": "412 Bridge St, Lowell, MA 01852",
  "point_of_delivery_id": "51004402281",
  "meter_number": "0084213556",
  "rate_schedule": "G-2",
  "commodity": "electric",
  "service_period_start": "2025-03-14",
  "service_period_end": "2025-04-14",
  "read_date_previous": "2025-03-14",
  "read_date_current": "2025-04-14",
  "bill_date": "2025-04-17",
  "due_date": "2025-05-08",
  "usage_quantity": 24880.0,
  "usage_unit": "kWh",
  "usage_unit_raw": "KWH",
  "read_type": "actual",
  "demand_quantity": 78.4,
  "demand_unit": "kW",
  "demand_basis": "billed",
  "power_factor": null,
  "currency": "USD",
  "total_amount_due": 5472.14,
  "current_charges": 5472.14,
  "prior_balance": 0.0,
  "supply_charges": 3528.98,
  "delivery_charges": 1943.16,
  "taxes": 0.0,
  "fees": 0.0,
  "late_charges": 0.0,
  "credits_adjustments": 0.0,
  "days_in_period": 31,
  "average_daily_usage": 802.5806,
  "blended_rate": 0.219941,
  "load_factor": 0.4265,
  "source_file_name": "lowell-bridge-st-2025-04.pdf",
  "source_page": 1
}

Supply plus delivery equals current charges and total due because the prior balance is zero. Load factor is 24,880 divided by (78.4 × 31 × 24), or 42.7%. Interpreting that value requires the site’s operating schedule and a longer history; the example only shows why the demand field is needed.

Keep the raw string

For anything ambiguous, store the parsed value and the literal text next to it. usage_unit_raw, account_number_raw, rate_schedule_raw, and a raw date string are the ones that earn their keep.

The reason is that parse failures are silent. If a water bill prints “HCF” and your normalizer maps it to CCF, that is correct, and you want the audit trail. If it prints “units” and your normalizer guesses CCF, that is a coin flip, and six months later when the numbers look off by a factor of 7.48 the raw string is the only thing that lets you find out what happened without re-opening 400 PDFs. Same for 45.20 CR versus (45.20) versus 45.20-. Same for a rate code that got OCR’d as B-l9.

This is useful with model-based extraction because a plausible normalized value can hide an ambiguous source label. That failure mode and several others are covered in why AI utility bill extraction fails. A few raw-text columns can save a later trip back to the PDFs.

Fields not worth extracting

Skip a usage-history bar chart when it has no numeric labels or clear scale. Estimating values from bar heights produces data you cannot reconcile to the individual bills. Build history from the bill entries instead.

Also skip “Amount Enclosed” from the remittance stub. It is blank or a duplicate of total due. And skip the printed average daily usage, for the rounding reason above.

Prior payments received is a judgment call. If you are reconciling AP, capture it. If you are analyzing energy, it is noise.