The Data Exchange process lets you import current and historic billing reads using CSV-format spreadsheet files that include the columns and rows described in this file.
Bringing historic billing read data into AquaCUE lets you see user consumption data that pre-dates your use of AquaCUE. Similarly, current and historic billing data give facility customers direct access to their current water consumption data and billing histories through AquaCUE Express.
Current and historic billing read data could reside in Badger Meter Read Center (RC) software, a SCADA system or a customer information system (CIS).
Use the information on this page to better understand the terms and information called for in the Data Exchange Billing Meter Reads Specification.
Use the steps below to bring historic billing reads into AquaCUE:
Before proceeding:
- Verify that your facility has an AquaCUE account and that your billing and other customer information systems have been synchronized with AquaCUE
- Verify that the Location IDs, Account IDs, Service Point IDs and Meter IDs for the meters whose billing read histories you want to import exist in AquaCUE or the reads will be rejected
- Note that you cannot use this procedure to pass historic billing reads for accounts and services that are no longer active.
- Download the Billing Meter Reads Specification.
- Open the file using a spreadsheet application such as Microsoft® Excel®.
- Use the details contained in the ADE Meter Reads Specification to guide you through the process of mapping rows of data from RC and your billing system to columns named exactly as they appear in the specification.Note: Supply one read per row in the spreadsheet. For example, 13 monthly billing reads for a given service configuration require 13 rows.
- Save the file in CSV format.
- Login to AquaCUE.
- From Assets>System Sync>Import, drop the file onto the Drop Zone.
- Approve the import.
Understanding the terms in the Billing Meter Reads Specification
The Billing Meter Reads Specification describes the CSV-format spreadsheet file columns and rows needed to bring current and historic billing read data into AquaCUE. Like other Data Exchange files, the column names must exactly match the Column Headings shown.
Use the details in the table below to better understand the information called for in the specification.
IDs
The first five columns consist of IDs, the first four of which should already be in AquaCUE or the meter read history file you are importing will be rejected.
The fifth ID, called Bill_ID, acts like an invoice number, making it easier to lookup transactions. Bill_IDs must be unique to the given service configuration. Bill_IDs are not required, but if you submit one Meter Reads file without a Bill_ID and then subsequently submit a Meter Reads file that includes a Bill_ID, two records will be created for the same read data. This lets you, for example, get separate reports for reads that were collected manually during a given time period and the reads that were used when billing the customer.
Columns marked with an astericks (*) are required.
| Column Heading | Column Description | What You Should Do |
| Account_ID* | Identifier of the Account used for billing purposes. | Copy exactly the Account_ID as it appears in AquaCUE for the given service configuration. |
| Location_ID* | Unique identifier for the location where the service is being provided | Copy exactly the Location_ID as it appears in AquaCUE for the given service configuration. |
| Service_Point_ID* | An identifier used to distinguish between multiple service hookups at the same Location. The combination of Location ID and Service Point ID represents a single point to which a meter is to be attached. | Copy exactly the Service_Point_ID as it appears in AquaCUE for the given service configuration. |
| Meter_ID* | Identifier of the meter. It must be unique and is required. | Copy exactly the Meter_ID as it appears in AquaCUE for the given service configuration. |
| Bill_ID* | Identifier for the bill/statement. It must be unique for the given combination of Account_ID, Location_ID, Service_Point_ID, and Meter_ID | Enter the invoice or billing record number associated with the given billing read. |
The next two columns contain details about each service configuration’s metering technology. This information should already be in AquaCUE.
| Column Heading | Column Description | What You Should Do |
| Register_Number |
Identifies whether a meter is a single or compound meter. Leave blank or enter 0 for single-register meters. Enter 0 or L to identify the low-flow register of a compound meter. Enter 2 or H to identify the high-flow register of a compound meter. For compound meters, the Location_ID, Service_Point_ID, and Meter_ID are expected to be the same for all registers. |
Copy and paste this information as it appears in AquaCUE for the given service configuration. |
| Billing_Unit_Of_Measure* | Description of the unit of the bill measurement. One of: GAL, CUBIC_FEET, CF, CCF, KGAL, CUBIC_METER, LITER, IMP, OIL_BARREL, FLUID_BARREL, or AF. | Copy and paste this information as it appears in AquaCUE for the given service configuration. |
The last seven columns are for capturing data pertaining to the start and end dates of a billing cycle, the reads collected at the beginning and end of that period, the amount of water used, the date the bill was issued and the accounting date.
Billing_Accounting_Date is for facilities that bill customers in a different month than consumption occurred. For example, when bills are sent one month after consumption data is recorded.
| Column Heading | Column Description | What You Should Do |
| Billing_Cycle_Start_Date | Date YYYY-MM-DD denoting the start of the billing period provided. | Enter the state date the billing period. |
| Billing_Cycle_End_Date | Date YYYY-MM-DD denoting the end of the billing period provided. | Enter the end date of the billing period. |
| Billing_Cycle_Start_Read* | Meter read at the start of billing cycle in register unit of measure. | Enter the number of the meter reading at the start of the given billing cycle. Be sure to use the register unit of measure. |
| Billing_Cycle_End_Read* | Meter read at the end of billing cycle in register unit of measure. | Enter the number of the meter reading at the end of the given billing cycle. Be sure to use the register unit of measure. |
| Billing_Cycle_ Consumption* |
Billing units of water consumed during the billing cycle. | Enter the amount of water used between the start and end of the billing period. In cases where a meter has rolled over, you must provide a number that takes rollover into account. |
| Billing_Date | Date YYYY-MM-DD denoting the date the bill is printed. | Enter the date when the bills were printed/issued for the given billing period. |
| Billing_Accounting_Date | Date YYYY-MM-DD denoting a normalized accounting calendar date, which may be different from the period during which the consumption took place. | Enter the account billing date for the given billing period. |
Amending Bills using an ADE Meter Reads File
It is common practice for facilities to amend water bills to revise consumption details. To use an ADE file to amend a bill:
- Create an ADE Meter Reads file for the service configuration you want to amend. Include at a minimum the following columns and information:
• Account_ID
• Location_ID
• Service_Point_ID
• Meter_ID
• Bill_ID
• Billing_Unit_of_Measure
• Billing_Cycle_End_Date
• Billing_Cycle_Start_Read
• Billing_Cycle_End_Read
• Billing_Cycle_Consumption - Revise the values in these fields as desired:
• Billing_Cycle_End_Date
• Billing_Cycle_Start_Read
• Billing_Cycle_End_Read
• Billing_Cycle_Consumption
Note: While updating the Billing_Cycle_Start_Read and Billing_Cycle_End_Read fields is not required, leaving them blank results in the previous values being removed from the system. - Save the file.
- Import it by dropping it into the Drop Zone of the Assets>System Sync>Import module.
- Accept the import.
