Why did this metric drop? A SQL root-cause routine
An alert says revenue is down 30%. Three SQL checks to run, in order, to find out why: freshness, contribution to change, and volume vs rate.
First check the data itself: most sudden drops turn out to be late or missing loads, so run freshness and volume checks before anything else. Then split the metric by each dimension and rank segments by how much of the total change they explain. For the top segment, separate the volume effect from the rate effect to tell lost traffic from lower value per unit. AI explanations can suggest where to look, but verify them with the queries.
An alert that says “revenue is down 30%” is the start of the work. Somebody now has to find out whether it’s real, and if it is, where it came from. I do the same few steps every time, and they’re all SQL.
The examples use an orders table with created_at, region and amount. Swap in your own. All queries are Postgres.
Step 1: is the data even there?
Most drops I’ve chased turned out to be a pipeline problem, not a business problem. A late load, a half-finished backfill, a connector that silently stopped. Check that before you slice anything.
SELECT max(created_at) AS latest_row,
now() - max(created_at) AS lag,
count(*) FILTER (WHERE created_at >= now() - interval '1 day') AS last_24h,
count(*) FILTER (WHERE created_at >= now() - interval '8 days'
AND created_at < now() - interval '7 days') AS same_time_last_week
FROM orders;Two things to read off it. If lag is hours when it’s normally minutes, the table is stale and the “drop” is just missing rows. If last_24h is far below same_time_last_week, the table is fresh but incomplete, which usually means a partial load.
Say a table usually gets ~2,900 rows a day and today shows 1,100 with a lag of 6 hours. You don’t have a revenue problem. You have a load problem, and you should stop here and go fix that.
Comparing against the same time last week matters. Plenty of tables are quiet on Sundays, and a fixed threshold will page you for it.
Step 2: which segment explains the change?
If the data is fresh and complete, the drop is probably real. Now split the metric by one dimension at a time and ask which segment moved the total.
This is a contribution-to-change query. For each segment it takes this period, the baseline period, the delta, and that segment’s share of the total delta.
WITH s AS (
SELECT region,
coalesce(sum(amount) FILTER (WHERE created_at >= now() - interval '7 days'), 0) AS cur,
coalesce(sum(amount) FILTER (WHERE created_at < now() - interval '7 days'), 0) AS base
FROM orders
WHERE created_at >= now() - interval '14 days'
GROUP BY region)
SELECT region, base, cur, cur - base AS delta,
round(100.0 * (cur - base) / NULLIF(sum(cur - base) OVER (), 0), 1) AS share_of_delta
FROM s
ORDER BY abs(cur - base) DESC;I ran it on fake data where one region lost about 30% of its revenue and the others grew a little:
region | base | cur | delta | share_of_delta
--------+--------+--------+--------+----------------
US | 330700 | 235200 | -95500 | 112.8
EU | 330600 | 336050 | 5450 | -6.4
APAC | 330650 | 336000 | 5350 | -6.3The US accounts for 112.8% of the net change, because the other two regions grew and partly hid it. That’s normal when segments move in opposite directions, and it’s useful: a share over 100% tells you something else is moving the other way. Sorting by absolute delta keeps both the culprit and the offsetting segments at the top.
Then repeat with channel, plan, country, product, whatever columns have a handful of distinct values. You’re looking for the dimension where one row dominates. If every dimension shows the drop spread evenly, suspect something global like tracking, a pricing change, or a holiday.
Two cautions. The share column gets unstable when the total delta is near zero, which is why there’s a NULLIF. And segments that exist only in one period need that coalesce, or they vanish from the result.
Step 3: fewer orders, or smaller ones?
Once you know the segment, split its delta into a volume part and a rate part. Revenue is orders times average order value, so a drop is one, the other, or both.
WITH s AS (
SELECT region,
count(*) FILTER (WHERE created_at >= now() - interval '7 days') AS n_cur,
count(*) FILTER (WHERE created_at < now() - interval '7 days') AS n_base,
avg(amount) FILTER (WHERE created_at >= now() - interval '7 days') AS a_cur,
avg(amount) FILTER (WHERE created_at < now() - interval '7 days') AS a_base
FROM orders
WHERE created_at >= now() - interval '14 days'
GROUP BY region)
SELECT region,
round((n_cur - n_base) * a_base) AS volume_effect,
round((a_cur - a_base) * n_cur) AS rate_effect,
round(n_cur * a_cur - n_base * a_base) AS delta
FROM s
ORDER BY delta;The two effects add up exactly to the delta. On my fake data the US row came back with a volume effect of +5,300 and a rate effect of -100,800. Order count was fine; each order was worth less. That points at a pricing or discount change, not a traffic problem, and it changes who you ask next.
The opposite shape, a big volume effect and a rate effect near zero, means people stopped buying or stopped being counted. Go back to step 1 for that segment.
There’s a third case worth knowing about. If every segment’s rate is flat but the total average order value still fell, the mix shifted: more of your orders now come from the cheap segment. Nothing got worse inside any segment, so the fix is a different conversation. Compare each segment’s share of orders across the two periods to spot it.
What arcodash’s AI explanation does with this
arcodash alerts can run a version of steps 2 and 3 for you. Here’s exactly what it does, checked against the code and the docs.
When a monitor fires and your org has an AI provider key set, arcodash:
- Computes a plain statistics line from the monitor’s recorded values (new value against the recent median). This part needs no AI and always goes out.
- Sends the model the monitor’s SQL, your schema and AI profile notes, and the monitor’s last 30 observed values, and asks it to propose up to three read-only queries that would show which segment or time bucket drove the change.
- Checks each proposed query is read-only, runs it against your data source (at most 50 rows each, 6 second timeout), and sends the results back.
- Gets one or two sentences plus a confidence of low, medium or high, and attaches it to the notification and the incident card. The whole thing has a 20 second budget. If it runs out, the alert goes out with the statistics line alone.
It uses your own Gemini, OpenAI or Anthropic key, not one of ours.
The privacy boundary needs stating plainly, because it’s different from our SQL-generation feature. Ask with AI sends table and column names and never result rows. The alert explanation is the exception: the results of those investigation queries do go to your provider, up to about 4 KB of result text in total. The model is told to aggregate, so you’d normally get counts and sums per segment, but that’s an instruction, not something we enforce, so a query could return individual row values. If that’s not acceptable for a source, an admin can turn off “Explain alerts with AI” or the sample-values setting in AI Settings, and alerts fall back to the statistics line. It also only runs for data sources that have an AI profile built. The data handling page has the full table.
What it doesn’t do matters more:
- It doesn’t check freshness first. If your pipeline broke, it may find a “segment” that explains the gap and be confidently wrong. Step 1 is still yours.
- It writes its own queries, so it isn’t running the contribution formula above. It can miss the right dimension, or only find one slice of a multi-cause drop.
- It sees three queries and no follow-ups. A person would chase the US result into plan type and then coupon code. It stops after one round.
- It’s told to say “inconclusive” with low confidence when the results don’t separate the change. Believe that.
I treat the sentence as a starting hypothesis, something that tells me which dimension to open first. It’s useful for saving the first ten minutes. It isn’t an answer, and I wouldn’t paste it into a postmortem without running the queries myself.
Make step 2 a check
The nicest thing you can do for your future self is save the contribution query so it’s already there when something fires. Parameterize the windows, keep the dimension you most often need, and put it next to the monitor. In arcodash that’s a saved query on the same data source as the alert, and you can start free: 14 days, no card. In any other tool it’s a bookmarked file in your repo. Either way, don’t write it for the first time while the dashboard is red.