I completely agree with the suggestion to handle deduplication within the view. Storing state in a log table for this is a classic pattern, but it's important to consider that it turns your view into a slowly changing dimension of sorts, which can introduce complexity around data freshness and maintenance.
An alternative I've used is to calculate a hash of the identifying fields and the alert condition itself within the same view, then have the zap store the last seen hash from the previous run in its own memory via a Zapier storage step. The filter then compares new hashes against that stored list. This keeps the data layer stateless and pushes the session-specific deduplication logic to the orchestrator, which I find cleaner for replayability and testing.
Your point about reducing unnecessary processing is key, though. A log table approach is more robust if the zap fails and needs to re-run from a previous step, as the state is persisted independently of the zap's execution.
Your data is only as good as your pipeline.
I've used that exact same two standard deviation trick for monitoring pipeline failures. The key for me was building the BigQuery view to also exclude any creative with less than, say, 1000 impressions in the lookback period. Otherwise, you get wild percentage swings on low-volume tests that trigger false alarms.
Have you run into that with new creative variants?
Ship fast, measure faster.
I agree on principle, but adding a deduplication flag to the view creates a stateful dependency on that log table. That can break if you need to backfill or test the view in isolation.
I'd keep the view stateless and handle deduplication in the zap's filter step by comparing against a small, external suppression table. It's one more lookup, but the data stays cleaner and more portable.
Nice approach! I've found that 7-day window works great for stable campaigns, but can be slow to catch a nosedive on a new creative. I sometimes add a separate, tighter check for anything less than 48 hours old that uses a simpler threshold, like 50% below the campaign average. It adds a second alert type, but you catch the real disasters faster.
That's a smart way to handle the new creative blind spot. I was wondering about the same lag with a 7-day average.
Do you run both checks in the same BigQuery view, or do you manage them as separate zaps entirely? I'm trying to picture if adding the condition for "creative age < 48 hours" directly in the view would get too messy.