One feature branch, 3,853 insertions across 20 files, one merge. That commit added five new analytics surfaces to my sports SaaS admin: a Package Report, a Conversion Report, a Renewal Report, a Conversion-Renewal Report, and a Sales-Lifetime view. The catch is that the admin they live in is a long-running classic-ASP app running on IIS over MSSQL. No React, no BI tool bolted on the side, no separate analytics service. Just more ASP pages, written to the same conventions as the pages that shipped when the app launched.

This is fine. You can grow a real business-intelligence surface inside legacy ASP and keep it maintainable, on two conditions: commit to one page pattern, and stop treating your review SQL as throwaway query-window snippets.

The shape of a report in classic ASP

Every report here is the same three-file shape, and that consistency is the whole trick.

There is a data endpoint that runs the query and returns rows. There is a client-side script, around 580 lines, that fetches from the endpoint, builds the table, wires the drill-downs, and handles the filter UI. And there is a CSS source file for the layout. The page wrapper is thin: it pulls in the includes and hands off to the client.

All of it follows one convention I lean on hard across this admin: the page-object pattern. Each page is a Page.prototype object with its init, its event handlers, and its render methods hanging off one prototype. It is plain old object-oriented JavaScript, nothing fancy, but it means every report in the suite reads the same way. If you have touched one, you can find your way around the next one in about thirty seconds. New report? Copy the shape, swap the query and the columns.

That uniformity makes “add another analytics page” a more bounded, predictable task instead of a research project. The shared pattern reduces the wiring decisions each new report has to revisit. The biggest enemy of a legacy codebase is five different ways to do the same thing, each invented by whoever was tired that week. One pattern, enforced, is worth more than any framework.

The SQL filter gauntlet nobody warns you about

The fun part of reporting is never the SQL syntax. It is figuring out which rows are actually real.

Revenue and expiry analytics here run over the customers table joined to the org-structure table. The naive version of “how many active paid accounts do we have” is a COUNT(*) with a WHERE Expires > GETDATE(). That number is wrong, and it is wrong in ways that will make you look foolish in a meeting.

Every report has to walk the same gauntlet of exclusions:

  • Exclude trial accounts (a trial flag on the customer row). Trials inflate every count and none of them are revenue.
  • Exclude internal test groups (a test-group flag). I have a pile of test orgs I made over the years, and they are indistinguishable from real customers unless you filter them out explicitly.
  • Exclude non-active org-structure nodes. The join is to org structure, and only nodes with active status are live customers. Inactive ones are deleted, suspended, or half-migrated.
  • And only then, Expires > today for the “currently paid” cut.

Miss any one of those and the conversion or renewal figure can be materially wrong. This is the real work of internal reporting: not the query syntax, but the definitions. What counts as a customer, what counts as paid, what counts as active. Those four filters are the institutional knowledge that the SQL encodes, and they belong in every query that touches this data.

Which is exactly why I stopped throwing the queries away.

Treat your review SQL as a checked-in artifact

The thing I am most glad I did on this branch: a hand-written review query committed to the repo right next to the report code.

It answers a specific question I needed for the renewal analysis: accounts that expired in a given window AND have at least one paid transaction. It uses a correlated subquery to pull the last_paid_before_expires date per account, so I can see who lapsed despite having paid us before. That is the population that matters for renewal outreach, and it is fiddly SQL with the same trial/test/active gauntlet baked in.

Here is the part that took me too many years to internalize. That query is not scratch work. It is the definition of “lapsed paying customer” for this business, written in the one language that cannot hand-wave. If it lives in a query window on my desktop, it dies when I close the tab, and the next person (me, in four months) reinvents it slightly differently and gets a slightly different number. If it lives in the repo, it is version-controlled, diffable, re-runnable, and reviewable. When someone asks “wait, how did you count that,” the answer is a file path, not my memory.

Checked-in review SQL turns “I ran a query once and got a number” into “here is the exact, reproducible definition that produced the number.” For internal analytics, where the whole value is trusting the number, that is the difference between a report people act on and a report people quietly second-guess.

I now treat every non-trivial analytics query the same way. If I had to think about the filter gauntlet to write it, it gets checked in.

Production data is corrupt and your report has to survive it

Years of production mean years of edge cases that no constraint caught at the time.

The org-structure table has corrupt rows. Nodes with broken parent references, nodes with priority values that should never exist, nodes that are half-deleted in a way the schema permits but the application logic does not expect. In day-to-day use these are invisible, because the normal pages either never touch them or fail silently on one record. A report does not have that luxury. A report aggregates across everything, so a corrupt node is now in your SUM, and your endpoint either crashes or returns a garbage total.

So the report endpoint threads a defensive corruption-repair routine through its processing. It is not glamorous. It detects the known-bad shapes and repairs or skips them before they hit the aggregation, so a single bad row cannot take down the whole report or silently poison the math. This is the unspoken tax on reporting over old data: a meaningful chunk of the code is not computing the answer, it is surviving the inputs. Budget for it. On a profitable old app, the corrupt rows are not going anywhere, so the report has to.

Why one big feature branch, not a hotfix train

A note on the release shape, because it is a deliberate choice. This admin ships through direct branch merges and a build server, no pull requests, and on a normal day I cut tightly scoped hotfix versions, one ticket per tag, so a rollback is a single-ticket rollback.

This was not that. Five net-new pages, zero changes to existing production behavior, nothing that an existing customer flow could regress. So it rode a feature branch into develop and shipped on the next minor version. The granularity rule is about blast radius: hotfix-per-ticket when you are touching live behavior and a bad merge could break checkout, one feature branch when the work is purely additive and the worst case is “a new admin page renders wrong.” Match the release unit to the blast radius, not to a habit. A rule that ignores what the change can actually break is just superstition.