I see a lot of teams relying on weekly dashboards to catch campaign issues. That's too slow. By the time you spot a dip in conversions or a spike in CPA, you've burned budget.
This guide is for setting up automated, custom alerts in your BI tool to catch anomalies in near-real-time. I'm assuming you have a clean, modeled marketing dataset. If you don't, fix that first—no alert logic can save bad data.
**Core components you need to define:**
* **Metric & Granularity:** What are you guarding? Daily spend? Hourly conversion rate? Weekly ROAS? Be specific.
* **Baseline Logic:** Don't use a simple average. Use a rolling window (e.g., last 14 days, excluding yesterday) to account for trends and day-of-week effects.
* **Thresholds:** Define what constitutes an anomaly. I use a statistical control limit (mean +/- 2 standard deviations) for metric deviations and a hard percentage change (e.g., spend increases by 25%) for budget alerts.
* **Alert Destination:** Slack, email, or a dedicated incident log. Ensure it routes to the person who can act.
**Implementation steps in Looker/Tableau:**
1. Build your core metric exploration with the required granularity and date range.
2. Create a calculated field for your baseline. In Looker, this is a `table_calculation`. For a 14-day rolling average excluding current day, you'll need to use `offset()` functions.
3. Create another calculated field for the deviation (e.g., `(current_value - baseline) / baseline`).
4. Apply a filter to the visualization where `absolute_value(deviation) > your_threshold`.
5. Schedule this exploration as a daily or hourly dashboard delivery, or use the platform's alerting feature (Looker's "Send Alerts", Tableau's "Subscription" with filters).
**Common pitfalls:**
* **Ignoring data freshness:** Your alert is only as good as your pipeline. If your ETL runs at 5 AM, don't set an alert for 4 AM.
* **Alert fatigue:** Tune your thresholds. If you get 10 alerts a day, you'll ignore all of them. Start strict, then loosen.
* **Lacking context:** An alert should include the campaign ID, date, metric, and the deviation percentage. "ROAS dropped 40% for campaign_123 on 2023-10-26" is usable. "Anomaly detected" is not.
Test this for a week on a non-critical campaign. Compare the alerts to actual known issues. If it doesn't catch real problems or mostly generates false positives, refine your baseline logic.
Garbage in, garbage out.
This is super helpful, thanks for writing it up. I'm still pretty new to this side of things in my role. When you say > a clean, modeled marketing dataset, what are the typical gotchas you've seen there? Is it mostly about data freshness from the source platforms, or schema changes breaking the pipelines?
Also, on the statistical control limits, do you find 2 standard deviations works well for most marketing metrics? I've read that can be noisy for newer campaigns with less historical data.
Learning by breaking
Good questions. On the dataset part, I've seen schema changes be the biggest headache, especially when a platform like Google Ads adds a new field and your ingestion script breaks or just drops it silently. Freshness is huge too - if your data's a day old, your 'real-time' alert is already late.
For the 2 standard deviations, it can be noisy on new campaigns for sure. One thing I've heard others do is switch to a percentage-based threshold for the first 30 days, like a 30% drop from the prior day, until there's enough history for stats.
Still learning.