Billing Data: It May Not Be as Daunting as You Think

Whenever a municipality considers a water rate change, they’re faced with the daunting task of compiling and analyzing billing data. Whether doing a rate analysis in-house or hiring a consultant, pulling at least a year’s worth of billing data can feel unavoidable. And as any water manager who has ever done it will tell you, compiling that data can be both difficult and time consuming – and that’s time a busy manager can ill-afford to spare.  

But despite what that consultant charging tens of thousands of dollars might tell you – the one asking for the data but unable or unwilling to help you compile it  detailed billing data actually isn’t always necessary. Summarized data will sometimes be enough. And even when more detailed data is required, compiling that data doesn’t have to be a monumental task. Let’s take a closer look at what billing data is, when and why it’s needed, how you can collect it, and how Waterworth can help make the process easier for you.  

 
What is billing data?   

Your billing data provides insight into who your customers are and how they use water. For unmetered communities charging a flat rate, the data might simply list all your customersidentifying the customer class and amounts charged for each. If your community is metered, billing data will contain much greater detail, including how much each customer consumes and when they consume it, as well as breaking down the fixed and variable portions of their charges. 

The data can be in a summarized form, using aggregated information, that’s usually more suitable for a higher-level analysis. Summarized billing data can tell us something but has limited use in detailed analysisHowever, if that’s all you have available, Waterworth may still be able to work with it. We’ll come back to that in a bit. 

The data can also come in a detailed, invoice-by-invoice form that includes many fields, such as: 

  • Customer category: Single Family; Multifamily; Industrial, Commercial, and Institutional (ICI); Irrigation; Wholesale; Inside/Outside city limits;  
  • Period of use; 
  • Meter size; 
  • Single Family Equivalents (sometimes also known as Equivalent Domestic Units, or Equivalent Residential Units);
  • Variable and fixed charges. 

And when we say ‘invoice-by-invoice’, we mean that there is a row of data for every invoice, for every account. For example, if a community has 1,000 service connections and invoices monthly, that would equal 12,000 rows of data.  

 

When do you need detailed billing data?  

Billing data is necessary for detailed water use analysis; for example, if you’re looking for year-over-year trends in residential per-capita demand, then you’ll need several years’ worth of quite detailed billing data. 

If you want to complete a detailed cost of service analysis so you can equitably allocate costs between customer categories according to the demands they place on the system, then you’ll need at least one years’ worth of detailed billing data. 

If you want to make a rate change that’s more complex than a simple, across-the-board change, say, for example, you want to adjust the upper tier of a volume rate and not the base rates, or say you want to adjust rates for Residential but not Commercial customers, then, depending on the complexity of your rate structure, you may need detailed billing data. But you may not. The same is true if you want to change the rate structure itself. 

The general rule of thumb is that if simplified data isn’t enough for you to easily work out the math in Excel and instead you feel you’re relying on guesswork or gut feeling to answer questions like: 

  • “How will residential customers be affected if we increase the fixed portion of their bill but reduce the usage rate?” or, 
  • “How will an increase to just our top tier impact ICI customers?” or, 
  • “Will this rate change meet revenue goals?” 

Then you probably need detailed billing data.  

 

Bonus tip to save you a lot of time: First make sure you know what you’re trying to accomplish so you can understand what degree of detail is required. That understanding will allow you to avoid the effort of compiling detailed billing data if it’s not needed. Be wary of consultants who will talk you into doing this (so they can produce expensive looking reports to justify their high cost) when it’s quite possible that it’s not necessary to accomplish your goals. 

 

What makes working with detailed billing data so challenging?  

First, there is no standardization on how this information is stored in billing software databases. These software programs were designed to facilitate and keep track of invoicing and were never designed to facilitate the types of analysis required when analyzing usage trends and rate structure changes. 

Second, billing software companies will often have differing configurations for each of their clients, making it difficult to develop a standardized report to produce a detailed billing data set. Each organization would typically need its own custom report. 

Even if the billing data can be compiled into an Excel workbook for analysis, as the datasets get larger, the complexity and sheer volume of data can push the software beyond its limits. For instance, a small community with population of 20,000, that invoices monthly, could find themselves struggling with upwards of 100,000 rows of data in Excel. And that’s for just one year. This size of file can easily cause Excel to fall on its knees, requiring higher performance tools such as a purpose-built software that uses a database. 

 

So how do you compile detailed billing data?  

There are a few options for compiling the data you need. However, each comes with its own challenges. 

  1. Export as much as you can from your billing software, then fine-tune the data in Excel (i.e. remove duplicate records, remove unnecessary fields, etc.).
    • Challenge: Time consuming and labor-intensive.
  2. Ask your billing software vendor to create a custom report. 
    • Challenge: Sometimes a software company will create the report you need, sometimes they won’t. (And even when they will, they may charge you extra for it.) 
  3. Ask your IT department to pull the data from your database. 
    • Challenge: Billing software database may be proprietary; IT folks may be overprotective of databases (sometimes with good reason) and can be reluctant.

       

How can Waterworth make it easier?

Above all else, we take the time to understand what you’re trying to accomplish and determine the level of detail needed from there. For straightforward rate changes where complexity is not that great, such as an increase to a base rate without touching the variable portions, Waterworth can often work with easy-to-produce reports straight out of your billing software, saving you the time and effort of compiling a full data set. 

For those times where you’ll need a full, detailed dataset, we help you along the way to compile the information, onboard it into Waterworth, and work through the built-in analysis tools with you to confirm the integrity of that data. If your corporate policy allows it, and your IT department is onboard, you can even send us a copy of your database and we can compile the detailed billing dataset for you. 

Once we have your data, we’ll complete a billing reconciliation step by programming in your rate structure, applying it to the billing dataset, and comparing what Waterworth calculates for billing with the actual billed amounts provided in the billing data set. We have confidence in the data set and interpretation of your rate structure only when we can reproduce those same charges with minimal discrepancy; typically, billing adjustments and other subtle nuances will prevent the comparison from being exactly the same, but anything within 2% is considered good 

Larger discrepancies usually indicate a problem with the data or rate structure, and we’ll work with you to troubleshoot the issue and iterate on the data until the reconciliation hits that 2% range. Once the integrity of the data has been verified, you can be confident in the accuracy of Waterworth’s Rate Design and Analysis Tools going forward. And you won’t have to fight with Microsoft Excel. 

 

But is personal information protected? 

There’s one question that always comes up when a utility is considering sharing billing data with a third party: how will our customers personal data (names, phone numbers, addresses,etc.)be protected?  

The answer is simple – that information isn’t necessary and should be omitted for Waterworth’s analysis – don’t even send it to us. Even if it isn’t removed, Waterworth ignores those fields when we onboard the billing data and so personal info will never make it into the Waterworth system.  

 

Recommended strategy  

Just thinking about the time and effort involved in compiling billing data can be overwhelming. But it doesn’t have to be.  

First, determine what data you need, and the level of detail required to accomplish your objective. Then decide which option for obtaining the data will be the most efficient and cost-effective: 

  1. Export the data from your billing software yourself, then organize it in Excel.  
  1. Ask someone else to compile the data for you, i.e. ask your software vendor for a custom report or ask your IT department to pull the data for you. 
  2. Ask Waterworth – we can help! 

Detailed billing data isn’t typically needed all that often. And when it is, we’ll make it as easy as possible for you to get us what we need 

Share on email
Share by Email
Share on linkedin
Share on LinkedIn
Share on twitter
Share on Twitter
Share on facebook
Share on Facebook

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

COVID-19 Financial Impact Modeling

Forecast impacts to revenue

3-months free access to Waterworth