Create MV for Pageviews by Device/Browser & Filter Non-Human Traffic

[erickgonzalez]

Description

The Pageviews by Device & Browser dashboard is currently querying the events table directly. This creates two issues:
• Performance risk as volume grows (no pre-aggregation layer).
• Inability to properly filter non-human interactions (bots/crawlers).

Recent tests confirmed that multiple crawlers and bots are generating pageview and content impression events. These are currently reflected in dashboards, which distorts metrics.

This task includes:
1. Creating a dedicated Materialized View (MV or RMV) for Pageviews by Device/Browser.
2. Introducing a reliable filter for human interaction only.
3. Ensuring dashboards at the UI level display only human metrics.
4. Providing a migration strategy to move historical data to the new MV/RMV without breaking existing dashboards.
5. Ensuring no regression in current analytics behavior.

The goal is to improve performance, data accuracy, and scalability without disrupting existing reporting.

Acceptance Criteria

• A new MV or RMV is created for Pageviews by Device/Browser that avoids direct queries to the events table.
• The MV filters events using a defined “human interaction” rule (e.g., bot detection flag, user agent classification, or equivalent).
• UI dashboards display only human interaction metrics.
• Historical data is migrated or backfilled into the new MV without data loss.
• Existing dashboards continue to function correctly (no regression).

Priority

High

Additional Context

https://gist.github.com/erickgonzalez/0dd76b1c0c37112834978857d0c45db2