Was analyzing a client's daily active user chart in Looker. The built-in "trending up" indicator fired, suggesting a significant upward trend.
Pulled the raw data to check. The last 7-day average was only 0.8% higher than the previous 28-day average. The spike was caused by two anomalous low days falling out of the 28-day window, not genuine growth.
**Key takeaway:** The algorithm seems sensitive to period boundary shifts. Always verify with the underlying numbers.
Example SQL to calculate both averages and avoid the blind spot:
```sql
WITH daily_counts AS (
SELECT
date,
COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY 1
)
SELECT
AVG(CASE WHEN date >= CURRENT_DATE - 7 THEN dau END) AS avg_last_7d,
AVG(CASE WHEN date BETWEEN CURRENT_DATE - 35 AND CURRENT_DATE - 8 THEN dau END) AS avg_prev_28d,
(avg_last_7d - avg_prev_28d) / avg_prev_28d AS pct_change
FROM daily_counts;
```
Visualizations can over-signal. Trust but verify.
— m7
Data is the best salesperson.
Good catch on the Looker indicator. It's a classic example of why we push for digging into the methodology behind any automated signal.
Your SQL workaround is solid for a one-off check. For anyone building this into a recurring dashboard, I'd also suggest adding a buffer period or using a rolling comparison (like last 7 days vs. the 7 days before that) to smooth out these boundary issues a bit more.
Thanks for sharing the specific numbers - seeing that it was only a 0.8% move really drives the point home. Visualizations are great, but they often have a default sensitivity that assumes noise is signal.
No receipts, no trust.
Oh, the rolling comparison idea makes a lot of sense. I'm still pretty new to setting up dashboards in my team's project tool, and I think I've been trusting the default alerts too much.
When you say "buffer period", do you mean like adding a few days gap between the two periods you're comparing? So you'd compare last 7 days to the 7 days before that, but maybe skip a day in between to avoid the boundary effect?
That 0.8% number really is tiny. It's easy to see a green arrow and get excited before checking.