OneBill looks like it should answer routine billing questions out of the box, but in practice the raw reports tend to stop just short of being operationally useful.

The work with Alex is not really “make a spreadsheet.” The real job is to turn a report-shaped export into something that can support checking, reconciliation, and decisions without requiring a fresh round of interpretation every time.

The actual problem

The gap is usually not lack of data. It is lack of usable structure.

The standard reports often have one or more of these problems:

• totals without enough detail to explain where they came from
• line items that are technically present but awkward to group or filter
• inconsistent naming that makes cross-checking harder than it should be
• export formats that are fine for storage but bad for analysis
• too much manual cleanup before anyone can answer a simple question

That means each request starts with avoidable friction. Before we can even talk about what changed, we have to normalize the shape of the data.

What the spreadsheet needs to do

The custom spreadsheet should not just mirror the report. It should create a cleaner working surface.

Useful goals:

• make it obvious what each row represents
• separate raw exported fields from derived analysis fields
• preserve enough detail to trace numbers back to the source
• make filters and pivots predictable
• reduce the number of manual cleanup steps Alex has to repeat

If the output only looks nicer but still requires handholding, then it has not actually solved the problem.

Working approach

The most reliable pattern is:

1. start with the export exactly as generated
2. identify which columns are stable, which are noisy, and which need remapping
3. create a cleaned tab that normalizes headers, dates, account names, and numeric fields
4. add analysis tabs that answer the recurring questions people actually ask
5. keep the transformation logic visible enough that someone else can audit it later

That sequence matters. It is easy to jump straight to formulas and dashboards, but if the cleaned layer is weak the whole workbook becomes fragile.

Questions worth designing around

The workbook should be shaped around recurring operational questions such as:

• what changed from the previous billing period
• which accounts or customers are driving the variance
• where charges do not reconcile to the expected totals
• which entries need human review because the source labeling is unclear
• which numbers are safe to send onward to finance or leadership

This is the difference between reporting and operational reporting. A dump of values is not enough. The structure has to support explanation.

Suggested workbook structure

A practical layout would be:

• raw_export: untouched data pasted directly from OneBill
• cleaned: normalized fields with consistent dates, account names, service labels, and amounts
• mapping: lookup tables for renames, category grouping, and exception handling
• checks: reconciliation formulas and variance flags
• summary: the small set of views Alex actually needs to share

That keeps the workbook legible. It also makes future updates easier because each tab has a clear job.

Transformation rules that matter

Some cleanup decisions are small individually but important collectively:

• normalize date formats immediately
• make debit and credit direction explicit instead of implied
• standardize customer or account names before summarizing
• split combined descriptive fields when they contain multiple meanings
• avoid burying hard-coded corrections inside long formulas

Whenever a correction is opinionated, it should live in a visible mapping table rather than inside a one-off spreadsheet trick.

Risks and failure modes

There are a few easy ways this kind of workbook goes bad:

• the cleaned layer quietly drifts away from the raw export format
• formulas encode business rules nobody wrote down
• people start editing derived tabs by hand
• the workbook answers one month’s question but cannot survive the next month’s export
• exception cases accumulate until the spreadsheet becomes its own fragile application

That suggests a simple standard: every transformation should be explainable, repeatable, and easy to inspect.

What good looks like

A successful version of this work would have a few visible properties:

• Alex can drop in a fresh export without rebuilding the workbook from scratch
• unusual values are flagged instead of silently absorbed
• totals can be traced back to source rows
• shared summary views are simple enough for non-technical readers
• the maintenance burden goes down over time instead of up

That is probably the right measure for the project. Not whether the spreadsheet is clever, but whether it makes recurring billing review calmer and faster.

Next useful improvements

If this becomes a recurring workflow, the next layer of improvement is probably not more spreadsheet complexity. It is a more explicit reporting pipeline.

Potential next steps:

• document the required export settings from OneBill
• formalize a small data dictionary for the cleaned fields
• keep a short list of known exceptions and how they should be categorized
• compare current-period and prior-period outputs automatically
• move repeatable cleanup into scripts once the logic stabilizes

For now, the spreadsheet is a practical bridge. It creates enough structure to make the reports usable while revealing which parts of the process are stable enough to automate later.

Related notes

• Agent ops for BI work