{"queries":[{"id":"call_activity_date_range","compiledQueryString":"WITH\n date_extremes AS (\n SELECT\n MIN(activity_at) AS min_date,\n MAX(activity_at) AS max_date\n FROM\n marketing.close_call_activity\n )\nSELECT\n min_date AS activity_at\nFROM\n date_extremes\nUNION ALL\nSELECT\n max_date\nFROM\n date_extremes","inputQueryString":"WITH\n date_extremes AS (\n SELECT\n MIN(activity_at) AS min_date,\n MAX(activity_at) AS max_date\n FROM\n marketing.close_call_activity\n )\nSELECT\n min_date AS activity_at\nFROM\n date_extremes\nUNION ALL\nSELECT\n max_date\nFROM\n date_extremes","compiled":false,"inline":false},{"id":"call_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n calls_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', c.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || c.user_id) AS user_name,\n c.user_id\n FROM\n marketing.close_call_activity AS c\n LEFT JOIN marketing.close_users AS u ON c.user_id = u.id\n WHERE\n c.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND c.user_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND c.user_id IN (SELECT id FROM active_user_ids))\n OR c.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS call_count\nFROM\n calls_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n calls_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', c.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || c.user_id) AS user_name,\n c.user_id\n FROM\n marketing.close_call_activity AS c\n LEFT JOIN marketing.close_users AS u ON c.user_id = u.id\n WHERE\n c.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND c.user_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND c.user_id IN (SELECT id FROM active_user_ids))\n OR c.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS call_count\nFROM\n calls_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"long_call_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n calls_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', c.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || c.user_id) AS user_name,\n c.user_id\n FROM\n marketing.close_call_activity AS c\n LEFT JOIN marketing.close_users AS u ON c.user_id = u.id\n WHERE\n c.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND c.user_id IS NOT NULL\n AND c.duration > 180\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND c.user_id IN (SELECT id FROM active_user_ids))\n OR c.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS call_count\nFROM\n calls_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n calls_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', c.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || c.user_id) AS user_name,\n c.user_id\n FROM\n marketing.close_call_activity AS c\n LEFT JOIN marketing.close_users AS u ON c.user_id = u.id\n WHERE\n c.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND c.user_id IS NOT NULL\n AND c.duration > 180\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND c.user_id IN (SELECT id FROM active_user_ids))\n OR c.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS call_count\nFROM\n calls_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"first_call_response_time_by_user_and_interval","compiledQueryString":"SELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n CASE\n WHEN l.first_call_created IS NULL THEN 'Not called'\n WHEN l.first_call_created - l.date_created <= INTERVAL 15 MINUTES THEN '<15min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 HOUR THEN '15-60min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 HOURS THEN '1-2hr'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 DAY THEN '2hr-1d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 DAYS THEN '1-2d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 3 DAYS THEN '2-3d'\n ELSE '>3d'\n END AS response_time,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3,\n 4\nORDER BY\n 1,\n 2,\n CASE response_time\n WHEN '<15min' THEN 1\n WHEN '15-60min' THEN 2\n WHEN '1-2hr' THEN 3\n WHEN '2hr-1d' THEN 4\n WHEN '1-2d' THEN 5\n WHEN '2-3d' THEN 6\n WHEN '>3d' THEN 7\n WHEN 'Not called' THEN 8\n END","inputQueryString":"SELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n CASE\n WHEN l.first_call_created IS NULL THEN 'Not called'\n WHEN l.first_call_created - l.date_created <= INTERVAL 15 MINUTES THEN '<15min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 HOUR THEN '15-60min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 HOURS THEN '1-2hr'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 DAY THEN '2hr-1d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 DAYS THEN '1-2d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 3 DAYS THEN '2-3d'\n ELSE '>3d'\n END AS response_time,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3,\n 4\nORDER BY\n 1,\n 2,\n CASE response_time\n WHEN '<15min' THEN 1\n WHEN '15-60min' THEN 2\n WHEN '1-2hr' THEN 3\n WHEN '2hr-1d' THEN 4\n WHEN '1-2d' THEN 5\n WHEN '2-3d' THEN 6\n WHEN '>3d' THEN 7\n WHEN 'Not called' THEN 8\n END","compiled":false,"inline":false},{"id":"call_users","compiledQueryString":"WITH\n user_call_counts AS (\n SELECT\n u.id,\n u.name,\n COUNT(*) AS recent_call_count\n FROM\n marketing.close_call_activity AS c\n JOIN marketing.close_users AS u ON c.user_id = u.id\n WHERE\n c.user_id IS NOT NULL\n AND c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY\n 1,\n 2\n )\nSELECT\n id,\n name,\n recent_call_count >= 1200 AS is_active\nFROM\n user_call_counts\nORDER BY\n CASE WHEN recent_call_count >= 1200 THEN 0 ELSE 1 END,\n name","inputQueryString":"WITH\n user_call_counts AS (\n SELECT\n u.id,\n u.name,\n COUNT(*) AS recent_call_count\n FROM\n marketing.close_call_activity AS c\n JOIN marketing.close_users AS u ON c.user_id = u.id\n WHERE\n c.user_id IS NOT NULL\n AND c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY\n 1,\n 2\n )\nSELECT\n id,\n name,\n recent_call_count >= 1200 AS is_active\nFROM\n user_call_counts\nORDER BY\n CASE WHEN recent_call_count >= 1200 THEN 0 ELSE 1 END,\n name","compiled":false,"inline":false},{"id":"lead_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND l.primary_phone IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND l.primary_phone IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"consultation_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"won_opportunity_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', o.date_won - INTERVAL 8 HOURS) AS interval_date,\n COALESCE(u.name, 'User ' || o.lead_patient_navigator_id) AS user_name,\n o.lead_patient_navigator_id AS user_id,\n COUNT(*) AS won_count\nFROM\n marketing.close_opportunity AS o\n LEFT JOIN marketing.close_users AS u ON o.lead_patient_navigator_id = u.id\nWHERE\n o.status_type = 'won'\n AND o.date_won BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND o.lead_patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND o.lead_patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR o.lead_patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', o.date_won - INTERVAL 8 HOURS) AS interval_date,\n COALESCE(u.name, 'User ' || o.lead_patient_navigator_id) AS user_name,\n o.lead_patient_navigator_id AS user_id,\n COUNT(*) AS won_count\nFROM\n marketing.close_opportunity AS o\n LEFT JOIN marketing.close_users AS u ON o.lead_patient_navigator_id = u.id\nWHERE\n o.status_type = 'won'\n AND o.date_won BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND o.lead_patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND o.lead_patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR o.lead_patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"funnel_metrics_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', f.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || f.patient_navigator_id) AS user_name,\n f.patient_navigator_id AS user_id,\n COUNT(*) AS total_leads,\n COUNT(*) FILTER (WHERE f.has_long_call OR f.has_many_inbound_sms) AS reached_leads,\n COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms) AS limited_convo_leads,\n COUNT(*) FILTER (WHERE f.has_consultation) AS consultation_leads,\n COUNT(*) FILTER (WHERE f.has_won_opportunity) AS won_leads,\n COUNT(*) FILTER (WHERE (f.has_long_call OR f.has_many_inbound_sms) AND f.has_consultation) AS reached_consultation_leads,\n COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms AND f.has_consultation) AS limited_convo_consultation_leads,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE f.has_long_call OR f.has_many_inbound_sms) / NULLIF(COUNT(*), 0),\n 1\n ) AS reach_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE f.has_consultation) / NULLIF(COUNT(*), 0),\n 1\n ) AS lead_to_consultation_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms AND f.has_consultation) / NULLIF(COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms), 0),\n 1\n ) AS limited_convo_lead_to_consultation_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE (f.has_long_call OR f.has_many_inbound_sms) AND f.has_consultation) / NULLIF(COUNT(*) FILTER (WHERE f.has_long_call OR f.has_many_inbound_sms), 0),\n 1\n ) AS reached_lead_to_consultation_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE f.has_won_opportunity) / NULLIF(COUNT(*) FILTER (WHERE f.has_consultation), 0),\n 1\n ) AS consultation_to_won_rate\nFROM\n marketing.close_lead_funnel_metrics AS f\n LEFT JOIN marketing.close_users AS u ON f.patient_navigator_id = u.id\nWHERE\n f.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND f.patient_navigator_id IS NOT NULL\n AND f.primary_phone IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND f.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR f.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', f.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || f.patient_navigator_id) AS user_name,\n f.patient_navigator_id AS user_id,\n COUNT(*) AS total_leads,\n COUNT(*) FILTER (WHERE f.has_long_call OR f.has_many_inbound_sms) AS reached_leads,\n COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms) AS limited_convo_leads,\n COUNT(*) FILTER (WHERE f.has_consultation) AS consultation_leads,\n COUNT(*) FILTER (WHERE f.has_won_opportunity) AS won_leads,\n COUNT(*) FILTER (WHERE (f.has_long_call OR f.has_many_inbound_sms) AND f.has_consultation) AS reached_consultation_leads,\n COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms AND f.has_consultation) AS limited_convo_consultation_leads,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE f.has_long_call OR f.has_many_inbound_sms) / NULLIF(COUNT(*), 0),\n 1\n ) AS reach_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE f.has_consultation) / NULLIF(COUNT(*), 0),\n 1\n ) AS lead_to_consultation_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms AND f.has_consultation) / NULLIF(COUNT(*) FILTER (WHERE NOT f.has_long_call AND NOT f.has_many_inbound_sms), 0),\n 1\n ) AS limited_convo_lead_to_consultation_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE (f.has_long_call OR f.has_many_inbound_sms) AND f.has_consultation) / NULLIF(COUNT(*) FILTER (WHERE f.has_long_call OR f.has_many_inbound_sms), 0),\n 1\n ) AS reached_lead_to_consultation_rate,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE f.has_won_opportunity) / NULLIF(COUNT(*) FILTER (WHERE f.has_consultation), 0),\n 1\n ) AS consultation_to_won_rate\nFROM\n marketing.close_lead_funnel_metrics AS f\n LEFT JOIN marketing.close_users AS u ON f.patient_navigator_id = u.id\nWHERE\n f.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND f.patient_navigator_id IS NOT NULL\n AND f.primary_phone IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND f.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR f.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"pn_date_range","compiledQueryString":"SELECT\n DATE_TRUNC('month', CURRENT_DATE - INTERVAL 3 MONTHS) AS activity_at\nUNION ALL\nSELECT\n CURRENT_DATE","inputQueryString":"SELECT\n DATE_TRUNC('month', CURRENT_DATE - INTERVAL 3 MONTHS) AS activity_at\nUNION ALL\nSELECT\n CURRENT_DATE","compiled":false,"inline":false},{"id":"consultation_self_serve_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n -- Self-serve leads: those with a Lead Qualification created by Bot Bariendo\n self_serve_lead_ids AS (\n SELECT DISTINCT lead_id\n FROM marketing.close_lead_qualifications\n WHERE created_by = 'user_S9UKyuxgqqdZ2bAPCBiiF6Ef9c5s5RyQTkIuLxfEek0'\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n JOIN self_serve_lead_ids AS ss ON cc.lead_id = ss.lead_id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n -- Self-serve leads: those with a Lead Qualification created by Bot Bariendo\n self_serve_lead_ids AS (\n SELECT DISTINCT lead_id\n FROM marketing.close_lead_qualifications\n WHERE created_by = 'user_S9UKyuxgqqdZ2bAPCBiiF6Ef9c5s5RyQTkIuLxfEek0'\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n JOIN self_serve_lead_ids AS ss ON cc.lead_id = ss.lead_id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"consultation_non_self_serve_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n -- Self-serve leads: those with a Lead Qualification created by Bot Bariendo\n self_serve_lead_ids AS (\n SELECT DISTINCT lead_id\n FROM marketing.close_lead_qualifications\n WHERE created_by = 'user_S9UKyuxgqqdZ2bAPCBiiF6Ef9c5s5RyQTkIuLxfEek0'\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n LEFT JOIN self_serve_lead_ids AS ss ON cc.lead_id = ss.lead_id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND ss.lead_id IS NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n -- Self-serve leads: those with a Lead Qualification created by Bot Bariendo\n self_serve_lead_ids AS (\n SELECT DISTINCT lead_id\n FROM marketing.close_lead_qualifications\n WHERE created_by = 'user_S9UKyuxgqqdZ2bAPCBiiF6Ef9c5s5RyQTkIuLxfEek0'\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n LEFT JOIN self_serve_lead_ids AS ss ON cc.lead_id = ss.lead_id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND ss.lead_id IS NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"consultation_to_won_rate_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n -- Won leads (at least one won opportunity)\n leads_with_won_opportunity AS (\n SELECT DISTINCT lead_id\n FROM marketing.close_opportunity\n WHERE status_type = 'won'\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count,\n COUNT(*) FILTER (WHERE lwo.lead_id IS NOT NULL) AS won_count,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE lwo.lead_id IS NOT NULL) / NULLIF(COUNT(*), 0),\n 1\n ) AS consultation_to_won_rate\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\n LEFT JOIN leads_with_won_opportunity AS lwo ON cc.lead_id = lwo.lead_id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n -- Won leads (at least one won opportunity)\n leads_with_won_opportunity AS (\n SELECT DISTINCT lead_id\n FROM marketing.close_opportunity\n WHERE status_type = 'won'\n )\nSELECT\n date_trunc('${inputs.interval}', cc.activity_at) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n COUNT(*) AS consultation_count,\n COUNT(*) FILTER (WHERE lwo.lead_id IS NOT NULL) AS won_count,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE lwo.lead_id IS NOT NULL) / NULLIF(COUNT(*), 0),\n 1\n ) AS consultation_to_won_rate\nFROM\n marketing.close_clinical_consultations AS cc\n JOIN marketing.close_lead AS l ON cc.lead_id = l.id\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\n LEFT JOIN leads_with_won_opportunity AS lwo ON cc.lead_id = lwo.lead_id\nWHERE\n cc.activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (SELECT id FROM active_user_ids))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1,\n 2","compiled":false,"inline":false},{"id":"outbound_call_success_rate_by_dow_and_hour","compiledQueryString":"WITH\n calls_pt AS (\n SELECT\n -- Convert UTC to Pacific time with DST:\n -- DST is active (UTC-7) from 2nd Sun of March 02:00 to 1st Sun of November 02:00\n -- Otherwise standard time (UTC-8)\n activity_at - CASE\n WHEN\n -- After 2nd Sunday of March at 10:00 UTC (= 02:00 PST -> 03:00 PDT)\n activity_at >= date_trunc('month', make_date(year(activity_at), 3, 1))\n + ((7 - dayofweek(make_date(year(activity_at), 3, 1)) + 0) % 7 + 7) * INTERVAL 1 DAY\n + INTERVAL 10 HOURS\n -- Before 1st Sunday of November at 09:00 UTC (= 02:00 PDT -> 01:00 PST)\n AND activity_at < date_trunc('month', make_date(year(activity_at), 11, 1))\n + ((7 - dayofweek(make_date(year(activity_at), 11, 1)) + 0) % 7) * INTERVAL 1 DAY\n + INTERVAL 9 HOURS\n THEN INTERVAL 7 HOURS\n ELSE INTERVAL 8 HOURS\n END AS activity_at_pt,\n duration\n FROM\n marketing.close_call_activity\n WHERE\n activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND direction = 'outbound'\n AND user_id IS NOT NULL\n )\nSELECT\n CASE dayofweek(activity_at_pt)\n WHEN 0 THEN '0 Sun'\n WHEN 1 THEN '1 Mon'\n WHEN 2 THEN '2 Tue'\n WHEN 3 THEN '3 Wed'\n WHEN 4 THEN '4 Thu'\n WHEN 5 THEN '5 Fri'\n WHEN 6 THEN '6 Sat'\n END AS day_of_week,\n hour(activity_at_pt) AS hour_sort,\n LPAD(CAST(hour(activity_at_pt) AS VARCHAR), 2, '0') || ':00' AS hour_of_day,\n COUNT(*) AS total_calls,\n COUNT(*) FILTER (WHERE duration > 180) AS long_calls,\n ROUND(100.0 * COUNT(*) FILTER (WHERE duration > 180) / NULLIF(COUNT(*), 0), 1) AS long_call_rate\nFROM\n calls_pt\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n total_calls >= 20\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n calls_pt AS (\n SELECT\n -- Convert UTC to Pacific time with DST:\n -- DST is active (UTC-7) from 2nd Sun of March 02:00 to 1st Sun of November 02:00\n -- Otherwise standard time (UTC-8)\n activity_at - CASE\n WHEN\n -- After 2nd Sunday of March at 10:00 UTC (= 02:00 PST -> 03:00 PDT)\n activity_at >= date_trunc('month', make_date(year(activity_at), 3, 1))\n + ((7 - dayofweek(make_date(year(activity_at), 3, 1)) + 0) % 7 + 7) * INTERVAL 1 DAY\n + INTERVAL 10 HOURS\n -- Before 1st Sunday of November at 09:00 UTC (= 02:00 PDT -> 01:00 PST)\n AND activity_at < date_trunc('month', make_date(year(activity_at), 11, 1))\n + ((7 - dayofweek(make_date(year(activity_at), 11, 1)) + 0) % 7) * INTERVAL 1 DAY\n + INTERVAL 9 HOURS\n THEN INTERVAL 7 HOURS\n ELSE INTERVAL 8 HOURS\n END AS activity_at_pt,\n duration\n FROM\n marketing.close_call_activity\n WHERE\n activity_at BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND direction = 'outbound'\n AND user_id IS NOT NULL\n )\nSELECT\n CASE dayofweek(activity_at_pt)\n WHEN 0 THEN '0 Sun'\n WHEN 1 THEN '1 Mon'\n WHEN 2 THEN '2 Tue'\n WHEN 3 THEN '3 Wed'\n WHEN 4 THEN '4 Thu'\n WHEN 5 THEN '5 Fri'\n WHEN 6 THEN '6 Sat'\n END AS day_of_week,\n hour(activity_at_pt) AS hour_sort,\n LPAD(CAST(hour(activity_at_pt) AS VARCHAR), 2, '0') || ':00' AS hour_of_day,\n COUNT(*) AS total_calls,\n COUNT(*) FILTER (WHERE duration > 180) AS long_calls,\n ROUND(100.0 * COUNT(*) FILTER (WHERE duration > 180) / NULLIF(COUNT(*), 0), 1) AS long_call_rate\nFROM\n calls_pt\nGROUP BY\n 1,\n 2,\n 3\nHAVING\n total_calls >= 20\nORDER BY\n 1,\n 2","compiled":false,"inline":true},{"id":"calls_within_15min","compiledQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, lead_count\nFROM (SELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n CASE\n WHEN l.first_call_created IS NULL THEN 'Not called'\n WHEN l.first_call_created - l.date_created <= INTERVAL 15 MINUTES THEN '<15min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 HOUR THEN '15-60min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 HOURS THEN '1-2hr'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 DAY THEN '2hr-1d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 DAYS THEN '1-2d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 3 DAYS THEN '2-3d'\n ELSE '>3d'\n END AS response_time,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3,\n 4\nORDER BY\n 1,\n 2,\n CASE response_time\n WHEN '<15min' THEN 1\n WHEN '15-60min' THEN 2\n WHEN '1-2hr' THEN 3\n WHEN '2hr-1d' THEN 4\n WHEN '1-2d' THEN 5\n WHEN '2-3d' THEN 6\n WHEN '>3d' THEN 7\n WHEN 'Not called' THEN 8\n END)\nWHERE response_time = '<15min'\nORDER BY interval_date, TRIM(user_name)","inputQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, lead_count\nFROM ${first_call_response_time_by_user_and_interval}\nWHERE response_time = '<15min'\nORDER BY interval_date, TRIM(user_name)","compiled":true,"inline":true},{"id":"calls_within_1hour","compiledQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, SUM(lead_count) AS lead_count\nFROM (SELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n CASE\n WHEN l.first_call_created IS NULL THEN 'Not called'\n WHEN l.first_call_created - l.date_created <= INTERVAL 15 MINUTES THEN '<15min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 HOUR THEN '15-60min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 HOURS THEN '1-2hr'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 DAY THEN '2hr-1d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 DAYS THEN '1-2d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 3 DAYS THEN '2-3d'\n ELSE '>3d'\n END AS response_time,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3,\n 4\nORDER BY\n 1,\n 2,\n CASE response_time\n WHEN '<15min' THEN 1\n WHEN '15-60min' THEN 2\n WHEN '1-2hr' THEN 3\n WHEN '2hr-1d' THEN 4\n WHEN '1-2d' THEN 5\n WHEN '2-3d' THEN 6\n WHEN '>3d' THEN 7\n WHEN 'Not called' THEN 8\n END)\nWHERE response_time IN ('<15min', '15-60min')\nGROUP BY interval_date, TRIM(user_name), user_id\nORDER BY interval_date, TRIM(user_name)","inputQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, SUM(lead_count) AS lead_count\nFROM ${first_call_response_time_by_user_and_interval}\nWHERE response_time IN ('<15min', '15-60min')\nGROUP BY interval_date, TRIM(user_name), user_id\nORDER BY interval_date, TRIM(user_name)","compiled":true,"inline":true},{"id":"calls_within_8hours","compiledQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, SUM(lead_count) AS lead_count\nFROM (SELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n CASE\n WHEN l.first_call_created IS NULL THEN 'Not called'\n WHEN l.first_call_created - l.date_created <= INTERVAL 15 MINUTES THEN '<15min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 HOUR THEN '15-60min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 HOURS THEN '1-2hr'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 DAY THEN '2hr-1d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 DAYS THEN '1-2d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 3 DAYS THEN '2-3d'\n ELSE '>3d'\n END AS response_time,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3,\n 4\nORDER BY\n 1,\n 2,\n CASE response_time\n WHEN '<15min' THEN 1\n WHEN '15-60min' THEN 2\n WHEN '1-2hr' THEN 3\n WHEN '2hr-1d' THEN 4\n WHEN '1-2d' THEN 5\n WHEN '2-3d' THEN 6\n WHEN '>3d' THEN 7\n WHEN 'Not called' THEN 8\n END)\nWHERE response_time IN ('<15min', '15-60min', '1-2hr', '2hr-1d')\nGROUP BY interval_date, TRIM(user_name), user_id\nORDER BY interval_date, TRIM(user_name)","inputQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, SUM(lead_count) AS lead_count\nFROM ${first_call_response_time_by_user_and_interval}\nWHERE response_time IN ('<15min', '15-60min', '1-2hr', '2hr-1d')\nGROUP BY interval_date, TRIM(user_name), user_id\nORDER BY interval_date, TRIM(user_name)","compiled":true,"inline":true},{"id":"never_called","compiledQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, lead_count\nFROM (SELECT\n date_trunc('${inputs.interval}', l.date_created) AS interval_date,\n COALESCE(u.name, 'User ' || l.patient_navigator_id) AS user_name,\n l.patient_navigator_id AS user_id,\n CASE\n WHEN l.first_call_created IS NULL THEN 'Not called'\n WHEN l.first_call_created - l.date_created <= INTERVAL 15 MINUTES THEN '<15min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 HOUR THEN '15-60min'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 HOURS THEN '1-2hr'\n WHEN l.first_call_created - l.date_created <= INTERVAL 1 DAY THEN '2hr-1d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 2 DAYS THEN '1-2d'\n WHEN l.first_call_created - l.date_created <= INTERVAL 3 DAYS THEN '2-3d'\n ELSE '>3d'\n END AS response_time,\n COUNT(*) AS lead_count\nFROM\n marketing.close_lead AS l\n LEFT JOIN marketing.close_users AS u ON l.patient_navigator_id = u.id\nWHERE\n l.date_created BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND l.patient_navigator_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND l.patient_navigator_id IN (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ))\n OR l.patient_navigator_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3,\n 4\nORDER BY\n 1,\n 2,\n CASE response_time\n WHEN '<15min' THEN 1\n WHEN '15-60min' THEN 2\n WHEN '1-2hr' THEN 3\n WHEN '2hr-1d' THEN 4\n WHEN '1-2d' THEN 5\n WHEN '2-3d' THEN 6\n WHEN '>3d' THEN 7\n WHEN 'Not called' THEN 8\n END)\nWHERE response_time = 'Not called'\nORDER BY interval_date, TRIM(user_name)","inputQueryString":"SELECT interval_date, TRIM(user_name) AS user_name, user_id, lead_count\nFROM ${first_call_response_time_by_user_and_interval}\nWHERE response_time = 'Not called'\nORDER BY interval_date, TRIM(user_name)","compiled":true,"inline":true},{"id":"sms_outbound_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n sms_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', s.date_sent) AS interval_date,\n COALESCE(u.name, 'User ' || s.user_id) AS user_name,\n s.user_id\n FROM\n marketing.close_sms_activity AS s\n LEFT JOIN marketing.close_users AS u ON s.user_id = u.id\n WHERE\n s.date_sent BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND s.user_id IS NOT NULL\n AND s.direction = 'outbound'\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND s.user_id IN (SELECT id FROM active_user_ids))\n OR s.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS sms_count\nFROM\n sms_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n sms_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', s.date_sent) AS interval_date,\n COALESCE(u.name, 'User ' || s.user_id) AS user_name,\n s.user_id\n FROM\n marketing.close_sms_activity AS s\n LEFT JOIN marketing.close_users AS u ON s.user_id = u.id\n WHERE\n s.date_sent BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND s.user_id IS NOT NULL\n AND s.direction = 'outbound'\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND s.user_id IN (SELECT id FROM active_user_ids))\n OR s.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS sms_count\nFROM\n sms_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","compiled":false,"inline":true},{"id":"sms_inbound_count_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n sms_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', COALESCE(s.date_sent, s.date_created)) AS interval_date,\n COALESCE(u.name, 'User ' || s.user_id) AS user_name,\n s.user_id\n FROM\n marketing.close_sms_activity AS s\n LEFT JOIN marketing.close_users AS u ON s.user_id = u.id\n WHERE\n COALESCE(s.date_sent, s.date_created) BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND s.direction = 'inbound'\n AND s.user_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND s.user_id IN (SELECT id FROM active_user_ids))\n OR s.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS sms_count\nFROM\n sms_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n sms_with_interval AS (\n SELECT\n date_trunc('${inputs.interval}', COALESCE(s.date_sent, s.date_created)) AS interval_date,\n COALESCE(u.name, 'User ' || s.user_id) AS user_name,\n s.user_id\n FROM\n marketing.close_sms_activity AS s\n LEFT JOIN marketing.close_users AS u ON s.user_id = u.id\n WHERE\n COALESCE(s.date_sent, s.date_created) BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND s.direction = 'inbound'\n AND s.user_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND s.user_id IN (SELECT id FROM active_user_ids))\n OR s.user_id = '${inputs.selected_user.value}'\n )\n )\nSELECT\n interval_date,\n user_name,\n user_id,\n COUNT(*) AS sms_count\nFROM\n sms_with_interval\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","compiled":false,"inline":true},{"id":"sms_response_rate_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n inbound AS (\n SELECT\n lead_id,\n user_id,\n COALESCE(date_sent, date_created) AS inbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'inbound'\n AND user_id IS NOT NULL\n AND COALESCE(date_sent, date_created) BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n ),\n outbound AS (\n SELECT\n lead_id,\n user_id,\n date_sent AS outbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'outbound'\n AND user_id IS NOT NULL\n AND status != 'error'\n ),\n -- Mark each inbound as replied (any outbound by same PN on same lead after the inbound)\n inbound_with_reply AS (\n SELECT\n i.user_id,\n i.inbound_at,\n MAX(CASE WHEN o.outbound_at > i.inbound_at THEN 1 ELSE 0 END) AS was_replied\n FROM\n inbound AS i\n LEFT JOIN outbound AS o ON i.lead_id = o.lead_id\n AND i.user_id = o.user_id\n GROUP BY\n i.user_id,\n i.inbound_at\n )\nSELECT\n date_trunc('${inputs.interval}', iwr.inbound_at) AS interval_date,\n COALESCE(u.name, 'User ' || iwr.user_id) AS user_name,\n iwr.user_id,\n COUNT(*) AS inbound_count,\n SUM(iwr.was_replied) AS replied_count,\n ROUND(100.0 * SUM(iwr.was_replied) / NULLIF(COUNT(*), 0), 1) AS response_rate\nFROM\n inbound_with_reply AS iwr\n LEFT JOIN marketing.close_users AS u ON iwr.user_id = u.id\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND iwr.user_id IN (SELECT id FROM active_user_ids))\n OR iwr.user_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n inbound AS (\n SELECT\n lead_id,\n user_id,\n COALESCE(date_sent, date_created) AS inbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'inbound'\n AND user_id IS NOT NULL\n AND COALESCE(date_sent, date_created) BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n ),\n outbound AS (\n SELECT\n lead_id,\n user_id,\n date_sent AS outbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'outbound'\n AND user_id IS NOT NULL\n AND status != 'error'\n ),\n -- Mark each inbound as replied (any outbound by same PN on same lead after the inbound)\n inbound_with_reply AS (\n SELECT\n i.user_id,\n i.inbound_at,\n MAX(CASE WHEN o.outbound_at > i.inbound_at THEN 1 ELSE 0 END) AS was_replied\n FROM\n inbound AS i\n LEFT JOIN outbound AS o ON i.lead_id = o.lead_id\n AND i.user_id = o.user_id\n GROUP BY\n i.user_id,\n i.inbound_at\n )\nSELECT\n date_trunc('${inputs.interval}', iwr.inbound_at) AS interval_date,\n COALESCE(u.name, 'User ' || iwr.user_id) AS user_name,\n iwr.user_id,\n COUNT(*) AS inbound_count,\n SUM(iwr.was_replied) AS replied_count,\n ROUND(100.0 * SUM(iwr.was_replied) / NULLIF(COUNT(*), 0), 1) AS response_rate\nFROM\n inbound_with_reply AS iwr\n LEFT JOIN marketing.close_users AS u ON iwr.user_id = u.id\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND iwr.user_id IN (SELECT id FROM active_user_ids))\n OR iwr.user_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","compiled":false,"inline":true},{"id":"sms_reply_time_by_user_and_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n inbound AS (\n SELECT\n lead_id,\n user_id,\n COALESCE(date_sent, date_created) AS inbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'inbound'\n AND user_id IS NOT NULL\n AND COALESCE(date_sent, date_created) BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n ),\n outbound AS (\n SELECT\n lead_id,\n user_id,\n date_sent AS outbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'outbound'\n AND user_id IS NOT NULL\n AND status != 'error'\n ),\n -- For each inbound, find the first outbound reply on the same lead by the same PN\n reply_times AS (\n SELECT\n i.user_id,\n i.inbound_at,\n MIN(o.outbound_at) AS first_reply_at\n FROM\n inbound AS i\n JOIN outbound AS o ON i.lead_id = o.lead_id\n AND i.user_id = o.user_id\n AND o.outbound_at > i.inbound_at\n AND o.outbound_at <= i.inbound_at + INTERVAL 24 HOURS\n GROUP BY\n i.user_id,\n i.inbound_at\n )\nSELECT\n date_trunc('${inputs.interval}', r.inbound_at) AS interval_date,\n COALESCE(u.name, 'User ' || r.user_id) AS user_name,\n r.user_id,\n ROUND(\n AVG(EPOCH(r.first_reply_at) - EPOCH(r.inbound_at)) / 60.0,\n 1\n ) AS avg_reply_time_minutes\nFROM\n reply_times AS r\n LEFT JOIN marketing.close_users AS u ON r.user_id = u.id\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND r.user_id IN (SELECT id FROM active_user_ids))\n OR r.user_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n ),\n inbound AS (\n SELECT\n lead_id,\n user_id,\n COALESCE(date_sent, date_created) AS inbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'inbound'\n AND user_id IS NOT NULL\n AND COALESCE(date_sent, date_created) BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n ),\n outbound AS (\n SELECT\n lead_id,\n user_id,\n date_sent AS outbound_at\n FROM\n marketing.close_sms_activity\n WHERE\n direction = 'outbound'\n AND user_id IS NOT NULL\n AND status != 'error'\n ),\n -- For each inbound, find the first outbound reply on the same lead by the same PN\n reply_times AS (\n SELECT\n i.user_id,\n i.inbound_at,\n MIN(o.outbound_at) AS first_reply_at\n FROM\n inbound AS i\n JOIN outbound AS o ON i.lead_id = o.lead_id\n AND i.user_id = o.user_id\n AND o.outbound_at > i.inbound_at\n AND o.outbound_at <= i.inbound_at + INTERVAL 24 HOURS\n GROUP BY\n i.user_id,\n i.inbound_at\n )\nSELECT\n date_trunc('${inputs.interval}', r.inbound_at) AS interval_date,\n COALESCE(u.name, 'User ' || r.user_id) AS user_name,\n r.user_id,\n ROUND(\n AVG(EPOCH(r.first_reply_at) - EPOCH(r.inbound_at)) / 60.0,\n 1\n ) AS avg_reply_time_minutes\nFROM\n reply_times AS r\n LEFT JOIN marketing.close_users AS u ON r.user_id = u.id\nWHERE\n interval_date >= DATE '${inputs.date_range.start}'\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND r.user_id IN (SELECT id FROM active_user_ids))\n OR r.user_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1,\n 2,\n 3\nORDER BY\n 1,\n 2","compiled":false,"inline":true},{"id":"sms_error_rate_by_interval","compiledQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', s.date_sent) AS interval_date,\n COUNT(*) AS total_sms,\n COUNT(*) FILTER (WHERE s.status = 'error') AS error_sms,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE s.status = 'error') / COUNT(*),\n 2\n ) AS error_rate\nFROM\n marketing.close_sms_activity AS s\nWHERE\n s.date_sent BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND s.direction = 'outbound'\n AND s.user_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND s.user_id IN (SELECT id FROM active_user_ids))\n OR s.user_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1","inputQueryString":"WITH\n active_user_ids AS (\n SELECT id FROM marketing.close_users WHERE id IN (\n SELECT DISTINCT c.user_id\n FROM marketing.close_call_activity AS c\n WHERE c.activity_at >= CURRENT_DATE - INTERVAL 12 WEEKS\n GROUP BY c.user_id\n HAVING COUNT(*) >= 1200\n )\n )\nSELECT\n date_trunc('${inputs.interval}', s.date_sent) AS interval_date,\n COUNT(*) AS total_sms,\n COUNT(*) FILTER (WHERE s.status = 'error') AS error_sms,\n ROUND(\n 100.0 * COUNT(*) FILTER (WHERE s.status = 'error') / COUNT(*),\n 2\n ) AS error_rate\nFROM\n marketing.close_sms_activity AS s\nWHERE\n s.date_sent BETWEEN DATE '${inputs.date_range.start}' AND (DATE '${inputs.date_range.end}' + 1)\n AND s.direction = 'outbound'\n AND s.user_id IS NOT NULL\n AND (\n '${inputs.selected_user.value}' = '%'\n OR ('${inputs.selected_user.value}' = 'active' AND s.user_id IN (SELECT id FROM active_user_ids))\n OR s.user_id = '${inputs.selected_user.value}'\n )\nGROUP BY\n 1\nHAVING\n interval_date >= DATE '${inputs.date_range.start}'\nORDER BY\n 1","compiled":false,"inline":true}]}

PN Performance

PN Performance

Summary - Funnel View

Summary - Conversion View

Call Activity by User

Long Calls (>3 min) by User

First Call Response Time by User

SMS Activity by User