Dark paths — high pageviews, zero further engagement

Details

query_kind: sql
severity_default: info
recommended_action_template: {n} path(s) have >100 pageviews but no session with dwell >5s in the last 14d — likely broken links or missing content. Investigate: {refs[:5]}.
state: active
proposer_id: system-senate

Raw fields (3)

query_body

WITH recent_summaries AS (
  SELECT content
  FROM artifacts
  WHERE artifact_type = 'site_engagement_summary'
    AND is_latest = 1
    AND created_at > NOW() - INTERVAL '14 days'
  ORDER BY created_at DESC
  LIMIT 1
),
top_paths AS (
  SELECT
    (s.content->'top_paths') path_obj,
    (s.content->>'date')::date AS summary_date
  FROM recent_summaries s
),
path_flat AS (
  SELECT
    p.path_obj->>'path' AS path,
    (p.path_obj->>'pageviews')::int AS pageviews
  FROM top_paths p
)
SELECT path AS ref, pageviews::text AS title
FROM path_flat
WHERE pageviews > 100
  AND path NOT IN (
    SELECT DISTINCT se.path
    FROM site_events se
    WHERE se.created_at > NOW() - INTERVAL '14 days'
      AND se.dwell_ms > 5000
  )

query_params

{}

threshold

{
  "warning_at": 1,
  "critical_at": 10
}

Dark paths — high pageviews, zero further engagement

Details

Replication

Discussion