Thresholds under diurnal volume

Lesson 2 · New Relic alert conditions · why a good threshold at 14:00 is a bad threshold at 09:00

Prerequisite: Lesson 1, the four blocks. This lesson lives entirely in the terms block and the nrql block that feeds it.

The problem, in Shippo's own numbers

Carrier traffic is not flat. Here is one full day of CARRIER_INTEGRATION_RESPONSE in production, bucketed by hour of day in UTC. Read on 2026-08-20.

SELECT count(*) AS total,
       filter(count(*), WHERE numeric(status_code) >= 400) AS errors
FROM Log
WHERE event = 'CARRIER_INTEGRATION_RESPONSE'
  AND environment = 'prod'
FACET hourOf(timestamp)
SINCE 1 day ago LIMIT 24

Total calls per hour

Peak is 1.84× the trough. The error count swings harder still: 113,815 at 09:00 against 263,263 at 14:00, a 2.31× spread.

Error rate per hour, same day

The rate swings 1.32× where the count swings 2.31×. Same system, same day. Only the unit changed.

Why Shippo mostly writes ratios

Of the 1000 alert conditions returned by the New Relic API, 234 use filter() or percentage() to compute a ratio. Fifty-eight of those sit on CARRIER_INTEGRATION_RESPONSE, which carries 86 conditions in total, 63 of them enabled. Fifty-one conditions across the account guard a ratio with a volume floor. This is the shape:

SELECT percentage(count(*), WHERE status != 'success') AS 'Error Rate'
FROM Log
WHERE environment = 'prod'
  AND test != true
  AND service IN ('auth', 'carrier_auth')
  AND event = 'CARRIER_INTEGRATION_RESPONSE'
FACET url

[CFG] Carrier Authentication Requests are Failing More than Usual (id 55396132, policy NOC Policy). Live and enabled, BASELINE, critical 30% and warning 6% over 600 s.

A ratio removes the volume term. That is the whole trick. It does not remove the problem.

The second problem: a ratio is unstable when volume is low

Divide by a small denominator and the result jumps. Three failed calls out of four is a 75% error rate. It is also completely normal noise for a carrier that receives four calls in a window at 04:00.

So every serious Shippo condition guards the ratio with a minimum-volume floor. Shippo spells this two different ways. Learn both, because they behave differently.

Spelling one: floor and clamp_min

SELECT (floor(count(*) / clamp_min(count(*), 50)) * percentage(count(*),
  WHERE status != 'success'
  AND service = 'shipment'
  AND test != true
  AND `shippo.integration.response.error.expected` != true
  AND status_code NOT IN (400, 412)
)) AS failure_rate
FROM Log
WHERE environment = 'prod'
  AND event = 'CARRIER_INTEGRATION_RESPONSE'
  AND service = 'shipment'
  AND test != true
  AND integration = 'canada_post'

[SHIPMENT] Canada Post Carrier Integration Error Rate (tf) (id 65685767, policy Carrier Monitoring). Live and enabled, critical ABOVE 15 for 1200 s, AT_LEAST_ONCE.

Read the guard on its own. Let n be the number of events in the window.

Below 50 events the whole expression collapses to zero. It emits a number, and that number is quiet. This matters for what happens next in the signal block.

Spelling two: IF(count(*) >= 50, ...)

SELECT IF(count(*) >= 50,
  filter(count(*), WHERE status != 'success' AND status_code >= 500
    AND (`shippo.integration.response.error.message` LIKE '%SERVICE.UNAVAILABLE.ERROR%'
      OR `shippo.integration.response.error.message` LIKE '%SYSTEM.UNAVAILABLE.EXCEPTION%'))
  * 100.0 / count(*)) AS '5xx Errors'
FROM Log
WHERE environment = 'prod' AND test != true
  AND event = 'CARRIER_INTEGRATION_RESPONSE'
  AND service = 'shipment'
  AND url LIKE '%apis.fedex.com/rate/v1/comprehensiverates/quotes%'
  AND caller NOT LIKE '%grpc_service%'
  AND integration = 'fedex'

[CCAP] FedEx REST Rating - 5xx errors (carrier upstream) (id 66242857, policy Carrier Capabilities (CCAP)). Live and enabled, warning ABOVE 25 for 1800 s, ALL.

Same intent, same floor of 50. Different output below the floor. The one-argument IF has no else branch, so it returns nothing, not zero.

Choosing the aggregation window

The floor is a count per window, so the window size and the floor are one decision, not two. A 50-event floor over 60 s is a much harder bar than a 50-event floor over 1800 s.

Shippo's window choices across the 1000 sampled conditions:

The rule that follows from the diurnal data: size the window so the trough hour still clears your floor. Check it before you ship, on the quietest hour, for the specific carrier you are alerting on.

SELECT count(*) FROM Log
WHERE event = 'CARRIER_INTEGRATION_RESPONSE'
  AND environment = 'prod'
  AND integration = 'canada_post'
  AND service = 'shipment'
TIMESERIES 20 minutes
SINCE 1 day ago

If that series dips below 50 overnight, a 1200 s window with a 50-event floor goes blind overnight. That may be the correct trade. It must be a decision, not an accident.

A live trap found while writing this lesson

status_code is a mixed-type attribute. New Relic's keyset() reports it as both string and numeric on this event. Some rows carry a literal '5XX' sentinel.

SELECT count(*) AS total,
       filter(count(*), WHERE status_code >= 500)    AS ge500,
       filter(count(*), WHERE status_code LIKE '5%') AS like5
FROM Log
WHERE event = 'CARRIER_INTEGRATION_RESPONSE' AND environment = 'prod'
  AND status_code = '5XX'
SINCE 60 minutes ago

-- ge500: 0        like5: ~1,000

The numeric comparison matches none of them. All three of [CCAP] FedEx REST Rating - 5xx errors (carrier upstream), [CCAP] FedEx REST Rating - 5xx burst (Shippo origin) and [CCAP] FedEx REST Rating - 5xx errors (Shippo origin) filter on status_code >= 500. Inside their exact filters, 1,393 FedEx rating failures a day are counted as zero. Since all three alert on a percentage, the numerator loses those events while the denominator keeps them, so the reported rate is pushed down twice.

These are not legacy conditions. They were rewritten on 20 August, with the sliding-window and paired-condition thinking from this course applied to them, and the bug was carried straight through the rewrite. Re-checked the same day: still broken. Written up in docs/2026-08-13-fedex-5xx-string-blind-spot.md.

The sentinel is not a FedEx quirk either. UPS produces 33,519 '5XX' rows a day, against FedEx's 4,629. No UPS condition uses the numeric pattern today, which is luck rather than design.

Method note

Put every comparison you are testing inside one query, as separate filter() columns. Two separately issued queries cover two different time windows, and the difference in their totals will look like a semantic difference. That mistake produced a wrong conclusion about test != true while writing this lesson. The single-query check disproved it: test != true and test != true OR test IS NULL return identical counts, so != already includes NULL here.

Check yourself

Read this next

Primary source: New Relic docs, Set thresholds for an alert condition. Read the sections on threshold types and on aggregation windows.

Secondary, for the NRQL functions used above: NRQL syntax, clauses and functions See percentage(), filter(), clamp_min(), and IF().