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

Forty fields, in five groups: identity (who and where), dates (which window), usage (how much and in what unit), money (split by charge type, not just the total), and derived values (computed by you, never read off the bill). If you extract only account number, total amount due, and usage, you have a payment record. You do not have data you can analyze.

The schema below is what a workable minimum looks like. Copy it.

Grain: what one row represents

Decide this before anything else, because it determines whether the rest of the schema holds together.

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

A single Con Edison invoice can carry electric and gas for the same address. A single National Grid commercial account can cover four meters. A PG&E bill can bill one service agreement across two rate periods after a mid-cycle tariff change. Flatten all of that into one row per PDF and you will be re-deriving the split forever. Keep a bill_id on every row so you can still reconcile the row set back to the invoice total.

Identity

These fields answer “which physical thing at which address does this consumption belong to.” People conflate them constantly, and the conflation is what breaks portfolio-level reporting.

The account number is a billing relationship. It can cover twelve buildings. It changes when ownership changes, and it does not change when the meter is swapped.

The meter number is a serial number on a physical device. It changes when the utility replaces the meter, which happens without warning and mid-portfolio during AMI rollouts. Trending usage keyed on meter number produces a phantom site every time a meter gets swapped.

The point of delivery ID goes by a different name at nearly every utility: PG&E calls it a Service Agreement ID, SCE issues a 10-digit Service Account Number distinct from its 12-digit Customer Account Number, ERCOT territory uses an ESI ID, and PJM-area utilities use POD ID. It is the stable one. It identifies the delivery point regardless of who owns the building or which meter is bolted to the wall. If a utility prints one, store it, and key your time series on it. The full breakdown is in account number vs meter number vs service ID.

Premise ID is separate again. Xcel Energy uses a 9-digit Premise Number, which appears on page 2 of the bill under Electricity Service Details rather than up front with the account number.

Rate schedule is the identity field most people skip, and it is the one that explains a cost jump when usage did not move. Capture it exactly as printed, because the naming conventions do not rhyme: PG&E writes B-19 and B-10S, SCE writes TOU-GS-3, Georgia Power writes TOU-GSD-17, National Grid’s Massachusetts business tariff is G-2, and Con Edison uses a space rather than a hyphen in SC 2 and SC 9 Rate I. Normalizing all of those into one house style feels tidy and destroys your ability to match a code back to a published tariff sheet.

Dates

Four date concepts, routinely mislabeled:

• 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. When the meter was physically read. Usually identical to the service period, sometimes off by a day or two, and on estimated reads they are fiction.
• Bill date (also “Statement Date”, “Invoice Date”). When the utility generated the document.
• Due date. Accounts payable cares. Analysis does not.

Service period is the only one that belongs on the x-axis of anything. Bill date is what people use instead, because it is easier to find and always present, and it is wrong: a bill dated April 3 covers March. Assign that usage to April and every heating season shifts by a month. If you are pushing data into ENERGY STAR Portfolio Manager, it rejects the bill date entirely and wants start and end.

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.

The rule that follows: never store a quantity without storing its unit in the same row, and never convert on ingest. Convert in the query layer where the conversion is visible and reversible.

Read type matters more than its size suggests. Utilities mark reads Actual or Estimated, and an estimated read followed by a true-up produces one artificially flat month and one artificially huge one. Both are real charges. Neither is real consumption for that period. Without a read_type flag you cannot tell that pattern from an actual operational change.

For commercial electric, also capture demand (kW), and whether that demand figure is measured or billed. Many industrial tariffs apply a ratchet where billed demand is a percentage of the highest demand set in the preceding eleven months, so billed demand can exceed anything that happened this month. Power factor belongs here too, since tariffs that penalize below 0.90 or 0.95 make it a cost driver rather than a curiosity.

Money

Total amount due is the number everyone extracts and the least analytically useful one on the page. It contains prior balance, late fees, and whatever adjustment the utility applied for a billing error six months ago. It is a remittance figure.

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 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.

The split is the whole analysis. Supply is the part you can shop or contract for. Delivery is the regulated part you cannot. A blended $/kWh that moved from $0.19 to $0.22 tells you nothing; supply moving from $0.11 to $0.14 while delivery held flat tells you your contract rolled to a variable rate. Total-only extraction throws that away permanently, and you cannot recover it later without re-processing every PDF.

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, do not extract

Bills print an average daily usage and sometimes a cost per kWh. Do not extract them.

The utility rounded those to two or three digits for display, and the rounding is not consistent across vendors or across a vendor’s own bill formats. Pull the printed figure into your data and you have imported someone else’s rounding error into a number you will later multiply by twelve months and a portfolio of sites. Compute from the fields you already have:

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 write it down. End minus start gives 31 for a March 14 to April 14 period, which matches what most utilities print as billing days. Inclusive counting gives 32 and will quietly disagree with the bill forever.

The schema

Worked example

A National Grid commercial electric bill, one meter, March 14 to April 14, 2025:

{
  "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 equals total due, since prior balance is zero. Load factor is 24,880 divided by (78.4 × 31 × 24), or 42.7%, which is a normal-looking profile for a light manufacturing site. That last number is the one that tells you whether a demand-response conversation is worth having, and it exists only because demand was captured.

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 matters more with model-based extraction than with template parsing, because a model will confidently return a plausible unit rather than failing loudly. That failure mode and several others are covered in why AI utility bill extraction fails. Raw strings are the cheapest defense: a few text columns against having to reprocess your archive.

Fields not worth extracting

The 13-month usage history bar chart. Every major utility prints one, and people try to OCR it because it looks like free historical data. It is rendered as a graphic with no numeric labels on most formats, the axis scale is unstated, and the values are the utility’s own restated history, which does not always match what the individual bills said after adjustments. You will spend more effort on the chart than on the rest of the bill and get numbers you cannot reconcile. Get history by processing the actual bills.

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.