Data Cleaning for Commercial Utility Billing Databases: Tenant Sub-Metering Data Reconciliation, Service Address and Account Number Standardization, Vacancy Period Billing Adjustments, Meter Reading Anomaly Detection, Rate Schedule Optimization Analysis, and Multi-Property Portfolio Energy Benchmarking Data Normalization
On this page
The Bills Nobody Audits
A commercial property with 30 tenants receives approximately 360 utility bills per year (monthly electric, gas, water, and sewer for the master meters, plus invoices for trash, telecom, and other services). A property management company managing 50 commercial properties receives approximately 18,000 utility bills per year. The bills are paid, filed, and allocated to tenants or charged to operating expenses with minimal review because the volume is too high for manual auditing and the individual bill amounts rarely trigger the threshold that would prompt a closer look.
Inside this unexamined flow of bills, errors accumulate. The electric utility estimated the meter reading for two consecutive months and then read the actual meter in the third month, producing a corrected bill that is allocated to tenants as if it were a single month's consumption, overstating the peak month's cost and understating the two estimated months. A water meter serving a single tenant suite was coded to the wrong tenant in the billing system three years ago, and the wrong tenant has been paying that water bill ever since -- neither tenant has noticed because neither knows what their water consumption should be. A property was placed on a commercial electric rate schedule at service establishment 15 years ago, and the rate schedule has since been superseded by a lower-cost rate that the property qualifies for but was never switched to because the utility does not proactively reassign accounts to lower-cost rates.
For property owners and managers, utility billing data quality directly affects operating expenses, tenant billing accuracy, and energy management decision-making. Inaccurate utility data produces incorrect tenant bills (which create disputes when tenants audit their charges), distorted operating expense reports (which affect property valuations, NOI calculations, and investor reporting), and unreliable energy benchmarking data (which makes it impossible to identify underperforming properties or evaluate the effectiveness of energy efficiency investments).
Tenant Sub-Metering Reconciliation
Commercial properties that sub-meter tenant utility consumption (rather than allocating utility costs pro-rata by square footage) have a reconciliation challenge: the sum of all tenant sub-meter readings should equal the master meter reading (minus common area consumption), but it rarely does. The discrepancy between master meter and sub-meter totals can be caused by meter drift (sub-meters lose accuracy over time, with some meters reading 5-10% high or low after several years of service), unmeasured loads (equipment or systems connected upstream of the sub-meters that consume energy but are not measured by any sub-meter), meter wiring errors (sub-meters connected to the wrong circuits, measuring the wrong tenant's consumption), and timing differences (master meter and sub-meters read on different dates, creating consumption that falls into different billing periods).
Reconciliation requires exporting the master meter billing data and all tenant sub-meter billing data for the same time period, comparing the total sub-meter consumption against the master meter consumption for each billing period, and investigating the cause of discrepancies that exceed a threshold (typically 3-5% of master meter consumption). Upload the master meter and sub-meter export files to Email Extractor to extract account numbers, meter identifiers, and any contact email addresses embedded in the billing data exports. Cross-reference extracted account identifiers against the property management system's account records to verify that every meter in the billing database corresponds to a valid, current tenant account.
Rate Schedule Optimization
Electric utilities offer multiple commercial rate schedules, and the difference in cost between the most expensive and least expensive applicable rate schedule for a given property can be 15-30% of the annual electric bill. A property that qualifies for a time-of-use rate schedule (where electricity costs less during off-peak hours) but is billed on a flat-rate commercial schedule pays more than necessary for every kilowatt-hour consumed during off-peak hours. A property that qualifies for an interruptible or curtailable rate schedule (where the utility can reduce the property's load during peak demand events in exchange for a lower rate) but is billed on a firm service schedule pays a premium for reliability it may not need.
Rate schedule analysis requires extracting the current rate schedule for each utility account (shown on the utility bill as a rate code, tariff code, or schedule designation), looking up the utility's published rate schedules to identify all rate schedules that the account qualifies for based on its demand level, voltage level, and usage pattern, and calculating the annual cost under each qualifying rate schedule using the account's actual 12-month consumption and demand data.
Export 12 months of billing data for each utility account. Upload to Email Extractor to extract account numbers, meter numbers, and utility company contact email addresses from the billing data. Use the extracted account identifiers to match billing data against the property management system's records and to contact utility account representatives about rate schedule reassignment for accounts identified as being on suboptimal rates.
Multi-Property Portfolio Energy Benchmarking Normalization
Commercial real estate portfolio operators need to compare energy performance across properties to identify underperforming buildings that are consuming more energy than comparable properties in the portfolio. The comparison metric is Energy Use Intensity (EUI) -- the total energy consumed by the building per square foot per year (measured in kBtu/sq ft/yr). A lower EUI indicates a more energy-efficient building.
Raw EUI comparisons are misleading because they do not account for the factors that cause energy consumption to vary between buildings regardless of efficiency: climate (a building in Phoenix uses more cooling energy than a building in Portland), building type (a hospital operates 24/7 and uses significantly more energy per square foot than a standard office building), operating hours (a building occupied 12 hours per day uses more energy than a building occupied 8 hours per day), occupancy density (a call center with dense workstation layouts uses more cooling energy than a traditional office with private offices), and vintage (a 1970s building with single-pane windows and uninsulated walls uses more heating and cooling energy than a 2010s building built to current energy code).
Normalizing EUI data for portfolio benchmarking requires adjusting each property's raw EUI for weather (using heating degree days and cooling degree days from the nearest weather station), building type (comparing each property's EUI against the ENERGY STAR Portfolio Manager median EUI for its building type rather than against all properties in the portfolio), and occupancy (adjusting for actual occupancy percentage versus full occupancy). Export utility billing data and building characteristics data for all properties in the portfolio. Clean and standardize the data to ensure consistent units (convert all energy sources to kBtu), consistent time periods (align all accounts to the same 12-month reporting period), and consistent building area measurements.
