Calculated Metrics: How Post-Aggregation Formulas Turn Counts Into Insights

This is part of a series where we're building a DIY journey analytics platform from scratch. If you're just joining, check out the earlier posts to catch up!

In the last post, we built a segment engine that can filter data by sequential patterns, scoped to events, sessions, or people. With metrics, dimensions, and segments in place, we now have all the raw building blocks for analysis. You can count things, break them down by categories, and filter them with sophisticated audience definitions.

There's one more piece we need before we can start doing the fun analysis stuff (interactive tables, visualizations, dashboards, etc.). If you've used any analytics tool, you've probably created calculated metrics before - things like conversion rate or average order value. They're tablestakes functionality. The interesting part for this series is how you build them into a query-time architecture where deduplication, segments, and dimension persistence are already happening in layered SQL.

This is the last analytics component we need before we can move on to the really exciting part - actually analyzing data. And while the concept is straightforward, building it in a way that composes cleanly with segments, dimensions, and the rest of a layered query engine is where things get interesting.

The Problem: Why Can't You Just Write More SQL?

Let's say you've got two standard metrics already defined in your semantic layer:

• Revenue - SUM of the revenue field (with deduplication on order_id)
• Orders - COUNT of events where event_type = 'purchase' (also deduplicated)

You want average order value: Revenue / Orders. Seems easy enough, right?

The challenge is when this math needs to happen. In our layered query architecture, standard metrics get aggregated in Layer 6 - that's where COUNT, SUM, and COUNT DISTINCT happen, grouped by whatever dimensions are in the report. Average order value can't be computed at the row level (before aggregation) because it's a ratio of two aggregated values. And it can't be baked into the GROUP BY because it needs to operate on the results of the GROUP BY.

It has to happen after aggregation - in a wrapper query that takes the aggregated results and applies the formula.

If you're building an analytics tool, this is something you'll need to account for in your query architecture. You can get pretty far with standard metrics (sums, counts, distinct counts), but the first time someone asks for a ratio or a percentage, you need a place to put post-aggregation math. You either hack it into the aggregation layer (which gets messy fast) or you add a new layer specifically for it.

We went with the new layer approach - Layer 7 - and it turned out to be one of the cleaner architectural decisions in the whole system.

How Calculated Metrics Work

A calculated metric in Trevorwithdata has a formula instead of a SQL expression. The formula references other metrics by their ID using {brace} syntax, and the system handles the rest:

{
  "id": "avg_order_value",
  "name": "Average Order Value",
  "type": "calculated",
  "formula": "{revenue} / {orders}",
  "format": { "decimals": 2, "prefix": "$" }
}

Compare that to a standard metric, which has a row-level SQL expression and an aggregation type:

{
  "id": "revenue",
  "name": "Revenue",
  "type": "standard",
  "sql": "revenue",
  "aggregation": "sum",
  "deduplication": { "scope": "person", "dedupField": "order_id", "keepInstance": "first" }
}

The key distinction: standard metrics define what to count at the row level, while calculated metrics define what to do with the counts after aggregation. This separation is important because it keeps each layer focused on one job.

Here are a few more examples of useful calculated metrics:

-- Revenue Per Visitor
{revenue} / {unique_visitors}

-- Pages Per Session
{page_views} / {sessions}

-- Cost Per Click
{ad_spend} / {clicks}

-- Average Items Per Order
{items_sold} / {orders}

Each of these would be painful to compute as a single SQL expression, but they're trivial as post-aggregation formulas. And because the component metrics (revenue, orders, sessions, etc.) already handle their own deduplication and aggregation logic, the calculated metric doesn't need to worry about any of that.

Under the Hood: Layer 7

When a report includes calculated metrics, the query engine adds a wrapper query around the standard aggregation layer. Here's a simplified view of what happens:

-- Layer 7: Calculated metric formulas
SELECT
  marketing_channel,
  page_views,                                  -- standard metric (user requested)
  revenue / NULLIF(orders, 0) as avg_order_value  -- calculated metric formula
FROM (
  -- Layer 6: Standard aggregation
  SELECT
    marketing_channel,
    SUM(page_views) as page_views,             -- user-requested metric
    SUM(revenue) as revenue,                   -- component metric (hidden)
    COUNT(DISTINCT CASE WHEN event_type = 'purchase'
      THEN order_id END) as orders             -- component metric (hidden)
  FROM ...
  GROUP BY marketing_channel
)
ORDER BY marketing_channel

A few things worth noticing:

Component metrics are computed but hidden. The formula {revenue} / {orders} requires both Revenue and Orders to be calculated in Layer 6, even if the user didn't explicitly add them to the report. The engine automatically detects which standard metrics the formula references, adds them as hidden columns in Layer 6, and then only surfaces the final calculated result in Layer 7. The user sees "Average Order Value" in their report, not the intermediate values (unless they also added Revenue or Orders as separate columns).

Division by zero is handled automatically. Every denominator in a formula gets wrapped in NULLIF(col, 0), which returns NULL instead of throwing an error when the denominator is zero. So if a particular marketing channel has zero orders, the average order value shows as null rather than crashing the query. This is one of those small details that matters a lot in practice - edge cases in dimension breakdowns are inevitable, and nobody wants their entire report to fail because one row had a zero.

Layer 7 only exists when needed. If a report only contains standard metrics, the query stops at Layer 6 - no unnecessary wrapper query. This keeps simple reports as efficient as possible.

Formula-Level Segments: The Real Power Move

This is where calculated metrics get really interesting. You can apply segments inside the formula itself using a colon after the metric ID, followed by comma-separated segment IDs.

This is actually how you'd build a proper conversion rate metric. Conversion rate isn't "orders divided by sessions" - it's "sessions where a purchase happened, divided by total sessions." The numerator needs to be filtered to only count converting sessions. With formula-level segments, that looks like this:

-- Conversion Rate (sessions with a purchase / total sessions)
{sessions:sessions_with_purchase} / {sessions} * 100

Where sessions_with_purchase is a session-scoped segment defined as ANY({event_type} = 'purchase'). The numerator counts sessions filtered by that segment, the denominator counts all sessions, and the formula does the division. Clean and correct.

This pattern opens up a lot of possibilities. Want to compare conversion rates across different audiences in the same report?

-- Mobile Conversion Rate
{sessions:sessions_with_purchase,mobile_users} / {sessions:mobile_users} * 100

-- Desktop Conversion Rate
{sessions:sessions_with_purchase,desktop_users} / {sessions:desktop_users} * 100

-- New Visitor Conversion Rate
{sessions:sessions_with_purchase,new_visitors} / {sessions:new_visitors} * 100

Each of these is a separate calculated metric, and they can all appear as columns in the same table - broken down by date, marketing channel, or whatever dimensions you want. The engine expands each segment-metric combination into its own hidden column in Layer 6, applies the segment filters, and then Layer 7 does the math.

Without formula-level segments, you'd need to either run three separate reports with different segment filters, or build something custom with CASE WHEN logic in your SQL. This approach keeps everything in one query and one report.

You can also use formula-level segments for simpler things like share-of-total calculations:

-- Mobile Order Share
{orders:mobile_users} / {orders} * 100

Formula-level segments compose with report-level segments too. If you drag a "US Traffic" segment onto the report and one of your calculated metrics has {orders:mobile_users} in its formula, you get orders from mobile users in the US - both filters apply with AND semantics.

Built-In Functions

Beyond basic arithmetic, formulas support a set of functions for more complex calculations:

-- Round to 2 decimal places
ROUND({revenue} / {orders}, 2)

-- Conditional logic (only compute AOV if there are enough orders)
IF({orders} > 10, {revenue} / {orders}, 0)

-- Statistical functions
MEAN({revenue})          -- Average across all rows in the result
MEDIAN({revenue})        -- Median value across all rows
PERCENTILE({revenue}, 90) -- 90th percentile
STDDEV({revenue})        -- Standard deviation

The math and rounding functions (ROUND, FLOOR, CEIL, ABS, SQRT, POWER, MOD, etc.) work like you'd expect - they operate on each row of the aggregated results.

The statistical functions are a bit different. Functions like MEAN, MEDIAN, and PERCENTILE operate across the rows of your report - not on event or person-level data, but on the aggregated rows that come out of Layer 6. So if your report is "Revenue by Marketing Channel," MEDIAN({revenue}) gives you the median revenue across all marketing channels in the table. This is useful for things like "what percentile is each marketing channel's revenue in?" or "how many standard deviations from the mean is this campaign?"

Live Preview: Test Before You Save

One of the features that makes calculated metrics much more usable in practice is the live preview. When you're building a formula, you can test it against real data without saving anything:

The preview endpoint runs your formula against the last 30 days of data and returns a daily time series - essentially a quick sparkline that shows whether your formula produces reasonable values. It also returns the total across the period and the execution time.

This is genuinely useful for catching mistakes early. If you accidentally write {orders} / {revenue} instead of {revenue} / {orders}, you'll immediately see values like 0.003 instead of $45.00 and realize the numerator and denominator are flipped. If you reference a metric ID that doesn't exist, the validation catches it before you save. And if your formula produces all nulls (maybe every row hits the division-by-zero case), you'll see that in the preview rather than discovering it later in a report.

Wrapping Up

With calculated metrics in place, we now have the complete set of analytics building blocks: metrics and dimensions for translating raw data into business concepts, segments for filtering with sequence and scope awareness, and now calculated metrics for the post-aggregation math that turns counts into actual insights. Conversion rates, averages, ratios, share-of-total, statistical distributions - all broken down by any dimension and filtered by any segment combination.

This means we're finally done building the analytical foundation, and the next posts in the series get to shift from "how do we define and compute analytics components" to "how do we actually use them." Freeform tables, visualizations, dashboards - the stuff that makes all this infrastructure worth building. I'm really looking forward to that part 😄

What I find most satisfying about the architecture so far is how cleanly the layers separate concerns. Standard metrics don't know about calculated metrics. Calculated metrics don't know about deduplication or dimension persistence. Segments don't know about formula-level segment references. Each layer does its job and passes clean results to the next one. When I want to add a new feature to any of these layers, the blast radius is contained - and that's been critical for building this thing incrementally with AI coding tools.

If you want to try building calculated metrics yourself, head over to trevorwithdata.com to sign up for early access. And if you're working on similar problems in your own analytics stack - especially the post-aggregation formula evaluation piece - I'd love to hear how you approached it. Connect with me on LinkedIn!

Trevor Paulsen is a data product professional at UKG and a former product leader of Adobe's Customer Journey Analytics. All views expressed are his own.