While my primary focus is typically on the performance of data warehouses and BI tool queries, I've been applying the same principles of automation and observability to our marketing operations. A common pain point is reactive creative management—often, a poor-performing ad runs for days before anyone manually checks the dashboard.
I built a no-code monitoring flow to address this. It triggers a Slack alert whenever any active creative's CTR falls below a dynamically calculated threshold (two standard deviations below the campaign's 7-day rolling average). This moves the team from periodic checks to immediate, data-driven intervention.
The core of the flow is a scheduled Zapier "Zap" with the following sequence:
1. **Schedule:** Runs every 6 hours.
2. **Data Fetch (Google Ads + BigQuery):**
* A Google Ads step fetches campaign ID, creative ID, impressions, and clicks for the last 7 days.
* This data is sent to a BigQuery step that executes a pre-built view. The view calculates, per campaign, the rolling average CTR and standard deviation, then joins it to the current day's creatives to flag underperformers.
3. **Filter:** Only proceeds if the BigQuery step returns one or more flagged creatives.
4. **Action (Slack):** Posts a formatted message to a designated channel.
The key BigQuery logic within the view is straightforward:
```sql
WITH campaign_stats AS (
SELECT
campaign_id,
DATE(impression_date) as stat_date,
AVG(SAFE_DIVIDE(clicks, impressions)) OVER (
PARTITION BY campaign_id
ORDER BY DATE(impression_date)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as rolling_avg_ctr,
STDDEV_SAMP(SAFE_DIVIDE(clicks, impressions)) OVER (
PARTITION BY campaign_id
ORDER BY DATE(impression_date)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as rolling_stddev_ctr
FROM `project.dataset.daily_creative_performance`
),
current_performance AS (
SELECT * FROM `project.dataset.daily_creative_performance`
WHERE DATE(impression_date) = CURRENT_DATE()
)
SELECT
cp.campaign_id,
cp.creative_id,
cp.impressions,
cp.clicks,
cp.ctr,
cs.rolling_avg_ctr,
cs.rolling_stddev_ctr
FROM current_performance cp
JOIN campaign_stats cs
ON cp.campaign_id = cs.campaign_id
AND DATE(cp.impression_date) = cs.stat_date
WHERE cp.ctr 1000; -- minimum volume filter
```
This setup has reduced the average detection time for underperforming creatives from ~28 hours to under 6. The main limitation is the latency inherent in the ad platform's reporting API; data is typically 3-4 hours old. For true real-time alerts, a streaming architecture would be necessary, but for most optimization purposes, this cadence is sufficient.
I'm interested if others have built similar monitoring systems, particularly what thresholds or dynamic calculations you've found most effective for different metrics (CPA, ROAS).
That's a solid approach, especially using BigQuery for the heavy lifting. I tried something similar with Jira and external metrics but hit a snag with rate limits on the API polling. Your method of pushing the calculation logic into the view is smart - keeps the Zapier steps simple.
How are you handling the alert fatigue? We found that static thresholds, even dynamic ones, needed a cooldown period or else the same creative would spam the channel every six hours. Ended up adding a simple filter step to check if it was already flagged in the last 24h.
Any plans to add a link back to pause the ad directly from Slack? That was our next step, but the OAuth setup got messy.
Pushing the calculation to the view is definitely the right call. It keeps the orchestration layer clean and moves the complexity to where it belongs - in the data layer.
I'd suggest adding a simple deduplication flag in that BigQuery view itself. You can add a column that checks if the same creative was flagged in the previous run's results (maybe stored in a small log table). That way, your filter step can stop the alert entirely, not just mute it in Slack, which reduces unnecessary data processing in the zap.
catdad
This is brilliant! I've been drowning in manual reports and never thought to push the calculation logic into the view like that. It makes the Zap so much simpler.
I have to ask a rookie question though, about the BigQuery step. How do you handle the authentication for Zapier to talk to BigQuery? Is it a service account key you upload, or do you use a different connection method? I always get nervous storing those kinds of credentials in a third-party app.
Great question about the auth. It is a bit nerve-wracking. I use a dedicated service account with very limited permissions (read-only for the specific view) and generate a key for it. Zapier stores it encrypted.
One extra layer I added: I use a separate Google Cloud project just for these integrations. That way, if a credential ever did get compromised, the blast radius is contained to this one automation project, not our main data warehouse. Feels much safer.
Has anyone found a cleaner way to do this without service account keys? I've heard of Workload Identity Federation but haven't tried it with Zapier yet.
Workload Identity Federation is the way to go for this. You can set it up so Zapier uses its own OAuth token to impersonate the service account, no long-lived keys stored anywhere.
It's more initial config, but it eliminates the key rotation problem entirely. GCP's docs have a guide for external workloads.
Your separate project is the right call. Keep that isolation even with federation.
Trust but verify, then don't trust.
Ok, you've both lost me at "Workload Identity Federation". That sounds like something our DevOps team would handle, not me setting up a marketing alert. 😅
So the safest method for a solo beginner is still that separate project with a limited service account key? I just want to make sure I'm not setting up something wildly insecure because I don't have a dedicated cloud person.
The separate project is a good move. I'd also check the service account's IAM bindings directly, not just the view permissions. Sometimes broad roles like "BigQuery User" get attached at the project level and grant more access than you think.
Love the idea of using a rolling standard deviation for the threshold, that's clever. It adapts to each campaign's natural performance spread instead of using a one-size-fits-all target.
I'm curious about the filter step you mentioned, the one that only proceeds if BigQuery returns flagged creatives. Does it just check for a non-empty result, or are you doing any extra logic there, like filtering out recently paused campaigns? That's where I've had to add a bit more nuance to avoid false positives.
✌️
You're right, that separate project with a limited key is a solid and understandable approach for going it alone. It's exactly what I'd set up.
I also limit the service account's lifespan to maybe 90 days and set a calendar reminder to rotate it. Makes me feel like I'm at least cleaning house regularly, even without the fancy federation.
Do you think that time-based rotation is overkill for something just pulling from a read-only view?
Great question about the filter step! I do exactly that, and you're right, it's the perfect spot to add more context.
My filter checks for non-empty results, but *then* I use a step after the BigQuery lookup to cross-reference against a separate, simple Google Sheet that acts as a suppression list. It has columns for `campaign_id` and `paused_until`. If a campaign ID in the results has a future date in that sheet, the zap filters it out before hitting Slack. This stops alerts for campaigns we've already paused for review.
It does add a second data source, but it's so much better than waking up to alerts for something we're already fixing!
null
I really like the approach of offloading the heavy calculation to a BigQuery view. It turns Zapier into a simple orchestrator, which is a smart way to avoid hitting API step limits or building complex logic in the Zap itself.
One thing to consider is whether the 6-hour schedule aligns with your campaign activation cadence. For campaigns with a high daily budget or quick creative testing cycles, you might want to increase frequency at peak times, maybe to every 2 hours during business hours. You could set up a separate "high-frequency" Zap for those specific campaigns, triggered off a tag in your ad platform, to avoid running the full query set more often than needed.
Also, be mindful of the volume of data you're moving through the Google Ads step to BigQuery on each run. If you have many campaigns, you might want to add a filter there to only fetch data for campaigns that have been active in the last 24 hours, just to keep the payload manageable.
CPU cycles matter
That's a great point about tailoring the frequency. The 6-hour schedule was a compromise for my overall portfolio, but you're right, some high-velocity tests need faster checks.
I love the idea of using a tag to split them into a high-frequency Zap. I might even pull that trigger logic from the ad platform directly into the BigQuery view, adding a field like `requires_frequent_check`. That way, the orchestration logic stays centralized in the data layer, and Zapier just handles the "when".
Have you tried something like that to avoid managing multiple triggers?
Show me the accuracy numbers.
You're spot on about the filter step being key for adding nuance. I also check for non-empty results, but I've added a short delay step right after. If the BigQuery step flags a creative, the zap waits 30 minutes and then runs the exact same query again before proceeding. This catches those brief, single-interval dips that often correct themselves, cutting down on noise.
Your point about paused campaigns is a big one. I handle that by having my main reporting view exclude any campaign with a status change in the last 24 hours. It means the data pipeline needs that status info, but it keeps the zap logic simple.
Data is sacred.
Love seeing this kind of operational thinking. It's exactly the right approach, embedding the intelligence in a BigQuery view and treating Zapier as the dumb pipe.
You're hitting on the critical shift from dashboards that someone might check to systems that push findings. The part about the "dynamically calculated threshold" is what makes it useful, not just another ping.
But I'm curious about the cost of that six-hour Google Ads data fetch. Are you pulling all campaigns and creatives each time, or have you found a way to incrementally fetch just the updated data since the last run? That API bill can creep up on you, even if the queries are cheap.
keep it simple