{"queries":[{"id":"key_metrics_comparison","compiledQueryString":"-- Same-day comparison: first N days of current month vs first N days of previous month.\n-- as_of = yesterday because the activity pipeline lands data with a 1-day lag,\n-- so today's row isn't loaded yet — comparing through CURRENT_DATE would short the\n-- current period by a day vs the previous period.\nWITH day_of_month AS (\n SELECT EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as current_day\n),\ncurrent_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n),\nprevious_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights, day_of_month d\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH)\n AND activity_date < DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH) + CAST(d.current_day AS INTEGER) * INTERVAL '1' DAY\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n 'Active Report Users (MARU)' as metric,\n c.maru as current_period,\n p.maru as previous_period,\n c.maru - p.maru as difference,\n ROUND((c.maru - p.maru) * 1.0 / NULLIF(p.maru, 0), 3) as pct_change,\n 1 as sort_order\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT\n 'Report Views (MRV)' as metric,\n c.mrv as current_period,\n p.mrv as previous_period,\n c.mrv - p.mrv as difference,\n ROUND((c.mrv - p.mrv) * 1.0 / NULLIF(p.mrv, 0), 3) as pct_change,\n 2 as sort_order\nFROM current_mtd c, previous_mtd p\nORDER BY sort_order","inputQueryString":"-- Same-day comparison: first N days of current month vs first N days of previous month.\n-- as_of = yesterday because the activity pipeline lands data with a 1-day lag,\n-- so today's row isn't loaded yet — comparing through CURRENT_DATE would short the\n-- current period by a day vs the previous period.\nWITH day_of_month AS (\n SELECT EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as current_day\n),\ncurrent_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n),\nprevious_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights, day_of_month d\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH)\n AND activity_date < DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH) + CAST(d.current_day AS INTEGER) * INTERVAL '1' DAY\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n 'Active Report Users (MARU)' as metric,\n c.maru as current_period,\n p.maru as previous_period,\n c.maru - p.maru as difference,\n ROUND((c.maru - p.maru) * 1.0 / NULLIF(p.maru, 0), 3) as pct_change,\n 1 as sort_order\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT\n 'Report Views (MRV)' as metric,\n c.mrv as current_period,\n p.mrv as previous_period,\n c.mrv - p.mrv as difference,\n ROUND((c.mrv - p.mrv) * 1.0 / NULLIF(p.mrv, 0), 3) as pct_change,\n 2 as sort_order\nFROM current_mtd c, previous_mtd p\nORDER BY sort_order","compiled":false,"inline":true},{"id":"comparison_period","compiledQueryString":"-- For display labels (1-day load lag, see key_metrics_comparison)\nSELECT\n EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as days_elapsed,\n STRFTIME(DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY)), '%b %Y') as current_month_label,\n STRFTIME(DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH), '%b %Y') as previous_month_label","inputQueryString":"-- For display labels (1-day load lag, see key_metrics_comparison)\nSELECT\n EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as days_elapsed,\n STRFTIME(DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY)), '%b %Y') as current_month_label,\n STRFTIME(DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH), '%b %Y') as previous_month_label","compiled":false,"inline":true},{"id":"supporting_metrics","compiledQueryString":"-- Same-day comparison for supporting metrics\nWITH day_of_month AS (\n SELECT EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as current_day\n),\nreport_catalog_metrics AS (\n SELECT \n (SELECT COUNT(DISTINCT report_id) FROM powerbi_analytics.mart_report_metrics) as total_catalog_reports,\n (SELECT COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN report_key END) \n FROM powerbi_analytics.fact_activity_insights \n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')) as reports_actively_used\n),\ncurrent_mtd AS (\n SELECT\n COUNT(DISTINCT user_email) as unique_users,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv,\n COUNT(CASE WHEN activity = 'ExportReport' THEN 1 END) as exports,\n COUNT(DISTINCT CASE WHEN activity = 'ExportReport' THEN user_email END) as users_who_export\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n),\nprevious_mtd AS (\n SELECT\n COUNT(DISTINCT user_email) as unique_users,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv,\n COUNT(CASE WHEN activity = 'ExportReport' THEN 1 END) as exports,\n COUNT(DISTINCT CASE WHEN activity = 'ExportReport' THEN user_email END) as users_who_export\n FROM powerbi_analytics.fact_activity_insights, day_of_month d\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH)\n AND activity_date < DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH) + CAST(d.current_day AS INTEGER) * INTERVAL '1' DAY\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n 'Avg Views per Active User' as metric,\n ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) as current_period,\n ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1) as previous_period,\n ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) - ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1) as difference,\n ROUND((ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) - ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1)) * 1.0 / NULLIF(ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1), 0), 3) as pct_change,\n 1 as sort_order\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Unique Users', CAST(c.unique_users AS DOUBLE), CAST(p.unique_users AS DOUBLE),\n CAST(c.unique_users - p.unique_users AS DOUBLE),\n ROUND((c.unique_users - p.unique_users) * 1.0 / NULLIF(p.unique_users, 0), 3), 2\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Report Exports', CAST(c.exports AS DOUBLE), CAST(p.exports AS DOUBLE),\n CAST(c.exports - p.exports AS DOUBLE),\n ROUND((c.exports - p.exports) * 1.0 / NULLIF(p.exports, 0), 3), 3\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Users Who Export', CAST(c.users_who_export AS DOUBLE), CAST(p.users_who_export AS DOUBLE),\n CAST(c.users_who_export - p.users_who_export AS DOUBLE),\n ROUND((c.users_who_export - p.users_who_export) * 1.0 / NULLIF(p.users_who_export, 0), 3), 4\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Total Reports (Catalog)', CAST(rc.total_catalog_reports AS DOUBLE), CAST(rc.total_catalog_reports AS DOUBLE), 0.0, 0, 5\nFROM report_catalog_metrics rc\nUNION ALL\nSELECT 'Accessed Reports (User Engagement)', CAST(rc.reports_actively_used AS DOUBLE), CAST(rc.reports_actively_used AS DOUBLE), 0.0, 0, 6\nFROM report_catalog_metrics rc\nUNION ALL\nSELECT 'Report Usage Rate',\n ROUND(rc.reports_actively_used * 100.0 / NULLIF(rc.total_catalog_reports, 0), 1),\n ROUND(rc.reports_actively_used * 100.0 / NULLIF(rc.total_catalog_reports, 0), 1), 0.0, 0, 7\nFROM report_catalog_metrics rc\nORDER BY sort_order","inputQueryString":"-- Same-day comparison for supporting metrics\nWITH day_of_month AS (\n SELECT EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as current_day\n),\nreport_catalog_metrics AS (\n SELECT \n (SELECT COUNT(DISTINCT report_id) FROM powerbi_analytics.mart_report_metrics) as total_catalog_reports,\n (SELECT COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN report_key END) \n FROM powerbi_analytics.fact_activity_insights \n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')) as reports_actively_used\n),\ncurrent_mtd AS (\n SELECT\n COUNT(DISTINCT user_email) as unique_users,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv,\n COUNT(CASE WHEN activity = 'ExportReport' THEN 1 END) as exports,\n COUNT(DISTINCT CASE WHEN activity = 'ExportReport' THEN user_email END) as users_who_export\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n),\nprevious_mtd AS (\n SELECT\n COUNT(DISTINCT user_email) as unique_users,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv,\n COUNT(CASE WHEN activity = 'ExportReport' THEN 1 END) as exports,\n COUNT(DISTINCT CASE WHEN activity = 'ExportReport' THEN user_email END) as users_who_export\n FROM powerbi_analytics.fact_activity_insights, day_of_month d\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH)\n AND activity_date < DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH) + CAST(d.current_day AS INTEGER) * INTERVAL '1' DAY\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n 'Avg Views per Active User' as metric,\n ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) as current_period,\n ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1) as previous_period,\n ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) - ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1) as difference,\n ROUND((ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) - ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1)) * 1.0 / NULLIF(ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1), 0), 3) as pct_change,\n 1 as sort_order\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Unique Users', CAST(c.unique_users AS DOUBLE), CAST(p.unique_users AS DOUBLE),\n CAST(c.unique_users - p.unique_users AS DOUBLE),\n ROUND((c.unique_users - p.unique_users) * 1.0 / NULLIF(p.unique_users, 0), 3), 2\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Report Exports', CAST(c.exports AS DOUBLE), CAST(p.exports AS DOUBLE),\n CAST(c.exports - p.exports AS DOUBLE),\n ROUND((c.exports - p.exports) * 1.0 / NULLIF(p.exports, 0), 3), 3\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Users Who Export', CAST(c.users_who_export AS DOUBLE), CAST(p.users_who_export AS DOUBLE),\n CAST(c.users_who_export - p.users_who_export AS DOUBLE),\n ROUND((c.users_who_export - p.users_who_export) * 1.0 / NULLIF(p.users_who_export, 0), 3), 4\nFROM current_mtd c, previous_mtd p\nUNION ALL\nSELECT 'Total Reports (Catalog)', CAST(rc.total_catalog_reports AS DOUBLE), CAST(rc.total_catalog_reports AS DOUBLE), 0.0, 0, 5\nFROM report_catalog_metrics rc\nUNION ALL\nSELECT 'Accessed Reports (User Engagement)', CAST(rc.reports_actively_used AS DOUBLE), CAST(rc.reports_actively_used AS DOUBLE), 0.0, 0, 6\nFROM report_catalog_metrics rc\nUNION ALL\nSELECT 'Report Usage Rate',\n ROUND(rc.reports_actively_used * 100.0 / NULLIF(rc.total_catalog_reports, 0), 1),\n ROUND(rc.reports_actively_used * 100.0 / NULLIF(rc.total_catalog_reports, 0), 1), 0.0, 0, 7\nFROM report_catalog_metrics rc\nORDER BY sort_order","compiled":false,"inline":true},{"id":"key_insights_stats","compiledQueryString":"-- Calculate statistics for Key Insights section (month-to-date)\nWITH report_catalog_metrics AS (\n SELECT \n (SELECT COUNT(DISTINCT report_id) FROM powerbi_analytics.mart_report_metrics) as total_catalog_reports,\n (SELECT COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN report_key END) \n FROM powerbi_analytics.fact_activity_insights \n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')) as reports_actively_used\n),\ncurrent_mtd_stats AS (\n SELECT\n COUNT(DISTINCT user_email) as total_users,\n COUNT(DISTINCT CASE WHEN activity = 'ExportReport' THEN user_email END) as users_who_export,\n COUNT(CASE WHEN activity = 'ExportReport' THEN 1 END) as total_exports\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n ROUND(cms.users_who_export * 100.0 / NULLIF(cms.total_users, 0), 0) as pct_users_who_export,\n ROUND(cms.total_exports * 1.0 / NULLIF(cms.users_who_export, 0), 1) as avg_exports_per_exporting_user,\n ROUND(rc.reports_actively_used * 100.0 / NULLIF(rc.total_catalog_reports, 0), 0) as pct_reports_actively_used\nFROM current_mtd_stats cms, report_catalog_metrics rc","inputQueryString":"-- Calculate statistics for Key Insights section (month-to-date)\nWITH report_catalog_metrics AS (\n SELECT \n (SELECT COUNT(DISTINCT report_id) FROM powerbi_analytics.mart_report_metrics) as total_catalog_reports,\n (SELECT COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN report_key END) \n FROM powerbi_analytics.fact_activity_insights \n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')) as reports_actively_used\n),\ncurrent_mtd_stats AS (\n SELECT\n COUNT(DISTINCT user_email) as total_users,\n COUNT(DISTINCT CASE WHEN activity = 'ExportReport' THEN user_email END) as users_who_export,\n COUNT(CASE WHEN activity = 'ExportReport' THEN 1 END) as total_exports\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n ROUND(cms.users_who_export * 100.0 / NULLIF(cms.total_users, 0), 0) as pct_users_who_export,\n ROUND(cms.total_exports * 1.0 / NULLIF(cms.users_who_export, 0), 1) as avg_exports_per_exporting_user,\n ROUND(rc.reports_actively_used * 100.0 / NULLIF(rc.total_catalog_reports, 0), 0) as pct_reports_actively_used\nFROM current_mtd_stats cms, report_catalog_metrics rc","compiled":false,"inline":true},{"id":"maru_mrv_trend","compiledQueryString":"SELECT\n DATE_TRUNC('month', activity_date) as month,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv,\n AVG(COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END)) OVER () as avg_maru\nFROM powerbi_analytics.fact_activity_insights\nWHERE activity_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11' MONTH)\n AND user_type NOT IN ('2', '6') -- Exclude admin and system users\nGROUP BY DATE_TRUNC('month', activity_date)\nORDER BY month","inputQueryString":"SELECT\n DATE_TRUNC('month', activity_date) as month,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv,\n AVG(COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END)) OVER () as avg_maru\nFROM powerbi_analytics.fact_activity_insights\nWHERE activity_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11' MONTH)\n AND user_type NOT IN ('2', '6') -- Exclude admin and system users\nGROUP BY DATE_TRUNC('month', activity_date)\nORDER BY month","compiled":false,"inline":true},{"id":"priority_metrics_insights","compiledQueryString":"-- Same-day comparison insights for Priority Metrics\nWITH day_of_month AS (\n SELECT EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as current_day\n),\ncurrent_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n),\nprevious_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights, day_of_month d\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH)\n AND activity_date < DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH) + CAST(d.current_day AS INTEGER) * INTERVAL '1' DAY\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n c.maru as current_maru,\n p.maru as previous_maru,\n c.maru - p.maru as maru_change,\n ROUND((c.maru - p.maru) * 100.0 / NULLIF(p.maru, 0), 1) as maru_pct_change,\n CASE \n WHEN c.maru > p.maru THEN 'growing'\n WHEN c.maru < p.maru THEN 'declining'\n ELSE 'stable'\n END as maru_trend,\n c.mrv as current_mrv,\n p.mrv as previous_mrv,\n c.mrv - p.mrv as mrv_change,\n ROUND((c.mrv - p.mrv) * 100.0 / NULLIF(p.mrv, 0), 1) as mrv_pct_change,\n CASE \n WHEN c.mrv > p.mrv THEN 'growing'\n WHEN c.mrv < p.mrv THEN 'declining'\n ELSE 'stable'\n END as mrv_trend,\n ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) as current_views_per_user,\n ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1) as previous_views_per_user,\n CASE \n WHEN c.maru > p.maru AND c.mrv > p.mrv THEN 'Platform adoption is increasing - both user base and activity are growing compared to the same period last month'\n WHEN c.maru < p.maru THEN 'User base is shrinking compared to the same period last month - may need to investigate adoption barriers'\n WHEN c.mrv < p.mrv THEN 'Activity declining despite stable users compared to the same period last month - check content relevance'\n ELSE 'Platform performance is stable compared to the same period last month'\n END as overall_interpretation\nFROM current_mtd c, previous_mtd p","inputQueryString":"-- Same-day comparison insights for Priority Metrics\nWITH day_of_month AS (\n SELECT EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1' DAY)) as current_day\n),\ncurrent_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY))\n AND activity_date <= (CURRENT_DATE - INTERVAL '1' DAY)\n AND user_type NOT IN ('2', '6')\n),\nprevious_mtd AS (\n SELECT\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights, day_of_month d\n WHERE activity_date >= DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH)\n AND activity_date < DATE_TRUNC('month', (CURRENT_DATE - INTERVAL '1' DAY) - INTERVAL '1' MONTH) + CAST(d.current_day AS INTEGER) * INTERVAL '1' DAY\n AND user_type NOT IN ('2', '6')\n)\nSELECT\n c.maru as current_maru,\n p.maru as previous_maru,\n c.maru - p.maru as maru_change,\n ROUND((c.maru - p.maru) * 100.0 / NULLIF(p.maru, 0), 1) as maru_pct_change,\n CASE \n WHEN c.maru > p.maru THEN 'growing'\n WHEN c.maru < p.maru THEN 'declining'\n ELSE 'stable'\n END as maru_trend,\n c.mrv as current_mrv,\n p.mrv as previous_mrv,\n c.mrv - p.mrv as mrv_change,\n ROUND((c.mrv - p.mrv) * 100.0 / NULLIF(p.mrv, 0), 1) as mrv_pct_change,\n CASE \n WHEN c.mrv > p.mrv THEN 'growing'\n WHEN c.mrv < p.mrv THEN 'declining'\n ELSE 'stable'\n END as mrv_trend,\n ROUND(CAST(c.mrv AS DOUBLE) / NULLIF(c.maru, 0), 1) as current_views_per_user,\n ROUND(CAST(p.mrv AS DOUBLE) / NULLIF(p.maru, 0), 1) as previous_views_per_user,\n CASE \n WHEN c.maru > p.maru AND c.mrv > p.mrv THEN 'Platform adoption is increasing - both user base and activity are growing compared to the same period last month'\n WHEN c.maru < p.maru THEN 'User base is shrinking compared to the same period last month - may need to investigate adoption barriers'\n WHEN c.mrv < p.mrv THEN 'Activity declining despite stable users compared to the same period last month - check content relevance'\n ELSE 'Platform performance is stable compared to the same period last month'\n END as overall_interpretation\nFROM current_mtd c, previous_mtd p","compiled":false,"inline":true},{"id":"trend_insights","compiledQueryString":"-- Calculate insights for year-to-date trend with pre-formatted messages\nWITH trend_data AS (\n SELECT\n DATE_TRUNC('month', activity_date) as month,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11' MONTH)\n GROUP BY DATE_TRUNC('month', activity_date)\n),\nstats AS (\n SELECT\n MAX(maru) as max_maru,\n MIN(maru) as min_maru,\n ROUND(AVG(maru), 0) as avg_maru,\n MAX(mrv) as max_mrv,\n MIN(mrv) as min_mrv,\n ROUND(AVG(mrv), 0) as avg_mrv\n FROM trend_data\n),\nfirst_last AS (\n SELECT \n MIN(month) as first_month,\n MAX(month) as last_month,\n (SELECT maru FROM trend_data WHERE month = (SELECT MIN(month) FROM trend_data)) as first_maru,\n (SELECT maru FROM trend_data WHERE month = (SELECT MAX(month) FROM trend_data)) as last_maru,\n (SELECT mrv FROM trend_data WHERE month = (SELECT MIN(month) FROM trend_data)) as first_mrv,\n (SELECT mrv FROM trend_data WHERE month = (SELECT MAX(month) FROM trend_data)) as last_mrv\n FROM trend_data\n)\nSELECT\n -- MARU metrics\n s.avg_maru,\n s.max_maru,\n s.min_maru,\n fl.last_maru as current_maru,\n CASE \n WHEN fl.last_maru > s.avg_maru THEN 'above'\n WHEN fl.last_maru < s.avg_maru THEN 'below'\n ELSE 'at'\n END as maru_vs_average,\n ROUND((fl.last_maru - s.avg_maru) * 100.0 / NULLIF(s.avg_maru, 0), 1) as maru_deviation_pct,\n -- Overall trend\n ROUND((fl.last_maru - fl.first_maru) * 100.0 / NULLIF(fl.first_maru, 0), 1) as maru_overall_growth_pct,\n CASE \n WHEN fl.last_maru > fl.first_maru THEN 'upward'\n WHEN fl.last_maru < fl.first_maru THEN 'downward'\n ELSE 'flat'\n END as overall_trend_direction,\n -- MRV metrics\n s.avg_mrv,\n fl.last_mrv as current_mrv,\n ROUND((fl.last_mrv - s.avg_mrv) * 100.0 / NULLIF(s.avg_mrv, 0), 1) as mrv_deviation_pct,\n -- Dates\n STRFTIME(fl.first_month, '%b %Y') as period_start,\n STRFTIME(fl.last_month, '%b %Y') as period_end,\n -- Pre-formatted insight message\n CASE \n WHEN fl.last_maru > fl.first_maru AND fl.last_maru > s.avg_maru THEN 'Strong adoption momentum - user base is growing and performing above historical average'\n WHEN fl.last_maru < fl.first_maru THEN 'Declining trend year-to-date - investigate user retention and platform value'\n WHEN fl.last_maru < s.avg_maru THEN 'Current performance below average - recent slowdown in adoption'\n ELSE 'Stable platform performance with consistent user engagement'\n END as key_takeaway\nFROM stats s, first_last fl","inputQueryString":"-- Calculate insights for year-to-date trend with pre-formatted messages\nWITH trend_data AS (\n SELECT\n DATE_TRUNC('month', activity_date) as month,\n COUNT(DISTINCT CASE WHEN activity IN ('ViewReport', 'PrintReport', 'ExportReport', 'DownloadReport', 'AnalyzeInExcel', 'RunEmailSubscription') THEN user_email END) as maru,\n COUNT(CASE WHEN activity = 'ViewReport' THEN 1 END) as mrv\n FROM powerbi_analytics.fact_activity_insights\n WHERE activity_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11' MONTH)\n GROUP BY DATE_TRUNC('month', activity_date)\n),\nstats AS (\n SELECT\n MAX(maru) as max_maru,\n MIN(maru) as min_maru,\n ROUND(AVG(maru), 0) as avg_maru,\n MAX(mrv) as max_mrv,\n MIN(mrv) as min_mrv,\n ROUND(AVG(mrv), 0) as avg_mrv\n FROM trend_data\n),\nfirst_last AS (\n SELECT \n MIN(month) as first_month,\n MAX(month) as last_month,\n (SELECT maru FROM trend_data WHERE month = (SELECT MIN(month) FROM trend_data)) as first_maru,\n (SELECT maru FROM trend_data WHERE month = (SELECT MAX(month) FROM trend_data)) as last_maru,\n (SELECT mrv FROM trend_data WHERE month = (SELECT MIN(month) FROM trend_data)) as first_mrv,\n (SELECT mrv FROM trend_data WHERE month = (SELECT MAX(month) FROM trend_data)) as last_mrv\n FROM trend_data\n)\nSELECT\n -- MARU metrics\n s.avg_maru,\n s.max_maru,\n s.min_maru,\n fl.last_maru as current_maru,\n CASE \n WHEN fl.last_maru > s.avg_maru THEN 'above'\n WHEN fl.last_maru < s.avg_maru THEN 'below'\n ELSE 'at'\n END as maru_vs_average,\n ROUND((fl.last_maru - s.avg_maru) * 100.0 / NULLIF(s.avg_maru, 0), 1) as maru_deviation_pct,\n -- Overall trend\n ROUND((fl.last_maru - fl.first_maru) * 100.0 / NULLIF(fl.first_maru, 0), 1) as maru_overall_growth_pct,\n CASE \n WHEN fl.last_maru > fl.first_maru THEN 'upward'\n WHEN fl.last_maru < fl.first_maru THEN 'downward'\n ELSE 'flat'\n END as overall_trend_direction,\n -- MRV metrics\n s.avg_mrv,\n fl.last_mrv as current_mrv,\n ROUND((fl.last_mrv - s.avg_mrv) * 100.0 / NULLIF(s.avg_mrv, 0), 1) as mrv_deviation_pct,\n -- Dates\n STRFTIME(fl.first_month, '%b %Y') as period_start,\n STRFTIME(fl.last_month, '%b %Y') as period_end,\n -- Pre-formatted insight message\n CASE \n WHEN fl.last_maru > fl.first_maru AND fl.last_maru > s.avg_maru THEN 'Strong adoption momentum - user base is growing and performing above historical average'\n WHEN fl.last_maru < fl.first_maru THEN 'Declining trend year-to-date - investigate user retention and platform value'\n WHEN fl.last_maru < s.avg_maru THEN 'Current performance below average - recent slowdown in adoption'\n ELSE 'Stable platform performance with consistent user engagement'\n END as key_takeaway\nFROM stats s, first_last fl","compiled":false,"inline":true}]}

Key Metrics

📊 Key Metrics

🎯 Priority Metrics

📊 Supporting Metrics

📈 MARU & MRV Trends