Applied Water & Sewer Rate Data and Research Co-Op

Project Overview: The Annual Water & Sewer Retail Rate Survey

The MWRA Advisory Board publishes an Annual Water & Sewer Retail Rate Survey that compares water and sewer bills across Massachusetts communities and select communities across the United States. The report helps policymakers, municipal officials, researchers, and residents understand how retail water and sewer costs vary from place to place.

This publication is one of the Advisory Board’s most requested and widely used reports, and it is published online for public use.

As a co-op working with our team, your primary project will be helping us build the Non-MWRA Communities section of the report, along with selected national comparisons.

This means collecting, verifying, organizing, and analyzing real municipal rate data. The work requires careful research, strong attention to detail, and thoughtful problem solving.

This project is for people who like solving messy data problems

The Water & Sewer Retail Rate Survey is built from information that is often inconsistent, scattered across municipal websites, and presented in many different formats.

Some communities publish clear rate sheets. Others embed rates inside meeting packets, budget documents, or departmental webpages. Some rate structures are simple. Others include multiple tiers, base charges, and different billing schedules.

Because of this, the project rewards people who enjoy:

• digging through documents
• verifying information
• building structured data from messy sources
• solving small analytical puzzles
• improving processes over time

If you enjoy turning messy real-world information into something accurate and structured, you will likely enjoy this project.

What the project actually involves

The Advisory Board collects retail water and sewer rate information for:

• Every Massachusetts community with a public water and/or sewer system that is not part of MWRA, and
• Select national communities outside Massachusetts used for comparison.

Finding the correct rate information is not always straightforward.

Sometimes a community publishes a clear rate sheet online. Other times the information may appear in:

• PDF documents
• Word documents
• municipal meeting packets
• budget documents
• departmental webpages

In some cases, the information is not easily available online at all. When that happens, we contact the appropriate person in the community to request it.

The right contact person is not always obvious. Depending on the community, it may be:

• a Department of Public Works director
• someone in the water department
• someone in the finance or treasury office
• another municipal staff member responsible for utility billing

Part of the work is identifying who the appropriate contact is and obtaining the correct information.

Verifying the data matters

Finding a rate sheet is only the first step.

A key part of the project is confirming that the information is current.

For example, the survey cycle may require rates for FY27 (July 2026 – June 2027). However, communities update their rates at different times. Some update them in July, others later in the fiscal year, and some communities do not change rates every year.

That means the work requires careful verification:

• Is this rate sheet from the correct fiscal year?
• Has a newer rate been adopted?
• Are water and sewer rates listed together or separately?
• Are there base fees or additional charges that must be included?

Accuracy is critical because the final report is used by real communities and published publicly.

Rate structures are not standardized

Water and sewer rates are structured differently in nearly every community.

For example, a community might have:

• a base service charge
• volumetric usage charges
• two usage tiers
• three or more usage tiers
• separate water and sewer structures
• monthly billing
• quarterly billing
• other billing schedules

Because of this, the calculations used for each community are not identical.

What we do with the data

Once the rate information is collected and verified, we calculate an estimated annual household water and/or sewer bill.

For the survey, we assume a standard annual water usage of 120 HCF.

Using that assumption, we apply each community’s rate structure to determine the estimated annual cost.

For example:

• If a community bills quarterly, we calculate one quarter’s bill based on the appropriate usage and rate tiers, then multiply by four.
• If a community bills monthly, we adjust the calculation accordingly.
• If base charges exist, they must be included.

These calculations are currently entered and built manually in Excel, because every community’s rate structure is different.

Where creativity and initiative come in

The required work is straightforward: collect the data, verify it, organize it, and calculate the annual bills needed for the report.

However, if you have technical skills that could improve this process, we want to hear about them.

Many applicants come from backgrounds in data science, analytics, or computer science and have experience with tools such as:

• Python
• Pandas
• NumPy
• SQL
• Jupyter notebooks
• Power BI or Tableau
• web scraping tools
• ETL pipelines
• automation scripts
• data validation frameworks

We do not currently rely on many of these tools in our workflow. Much of the process is still manual.

That means there may be opportunities to experiment with improvements.

For example, you might ask:

• Could Python or Pandas help organize or process rate data more efficiently?
• Could a script help locate rate sheets across municipal websites?
• Could web scraping help identify municipal contacts for outreach?
• Could we automate parts of the validation process?
• Could Excel workflows be redesigned to reduce errors?
• Could dashboards help visualize and review results?

None of this is required to succeed in the role. Our first priority is producing the report accurately and on time.

But if you have ideas for improving how the work gets done, we want to hear about them.

What success looks like in this role

A successful co-op will help meaningfully advance the Advisory Board’s Annual Water & Sewer Retail Rate Survey.

Success typically includes:

Accurate data collection and verification
Locating current rate information and confirming it reflects the correct fiscal year.

Careful calculations
Applying each community’s rate structure to the survey’s standard usage assumption and calculating annual bills accurately.

Clear documentation
Recording sources and assumptions so the data can be verified later.

Consistent progress
Managing deadlines and steadily moving communities from “unverified” to “complete.”

Curiosity and initiative
Investigating inconsistencies and suggesting improvements where appropriate.

Collaboration
Working with the Research and Policy Analyst and Communications Insight and Engagement Lead while participating in team meetings and project discussions.

Before your interview (optional challenge)

If you enjoy tackling technical or analytical challenges, consider the following question before your interview.

You do not need to solve this to be considered for the role.

However, if you have ideas, we would be interested in hearing them.

Challenge prompt:

Given that municipal rate sheets appear in many formats (PDFs, webpages, spreadsheets, meeting packets, etc.) and that rate structures vary widely:

How might you improve or partially automate parts of this process?

For example:

• locating rate sheets
• identifying municipal contacts
• extracting rate data from documents
• organizing or validating collected data
• improving calculation workflows

You might think about tools such as Python, data scraping, automation scripts, or other methods you are familiar with.

If you have ideas, bring them to the interview and talk with us about them.

We are always interested in learning about new approaches.