Analytics

SELECT
  date_trunc('month', "public"."rasa_session"."timestamp") AS "first_seen",
  count(*) AS "count"
FROM "public"."rasa_session"
GROUP BY 1
ORDER BY 1 ASC

SELECT
  "public"."rasa_sender"."channel" AS "channel",
  "public"."rasa_sender"."first_seen" AS "timestamp",
  count(distinct "public"."rasa_sender"."sender_key") AS "count"
FROM "public"."rasa_sender"
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC

WITH "sessions" AS (
    SELECT
        DISTINCT ON ("public"."rasa_event"."session_id") "public"."rasa_event"."session_id",
        "public"."rasa_event"."timestamp" AS "timestamp",
        (
            CASE
                WHEN "public"."rasa_bot_message"."template_name" = 'utter_ask_name'
                THEN 1 ELSE 0
            END
        ) AS "is_abandoned"
    FROM "public"."rasa_event"
    INNER JOIN "public"."rasa_bot_message"
      ON "public"."rasa_event"."id" = "public"."rasa_bot_message"."event_id"
    WHERE "public"."rasa_event"."event_type" = 'bot'
    ORDER BY 1, 2 DESC
)
SELECT
  date_trunc('month', "timestamp") AS "timestamp",
  SUM("is_abandoned")::float / count(*) AS "abandonment_rate"
FROM "sessions"
GROUP BY 1
ORDER BY 1 ASC

SELECT rasa_flow_status.flow_identifier, COUNT(DISTINCT rasa_flow_status.session_id) AS "count"
FROM rasa_flow_status
# we only want the top 5 user defined flows, not the built-in flow usage
WHERE rasa_flow_status.flow_identifier NOT LIKE 'pattern_%' AND rasa_flow_status.inserted_at >= NOW() AT TIME ZONE 'UTC' - INTERVAL '7 days'
GROUP BY 1
ORDER BY 2 DESC, 1 ASC
LIMIT 5;

WITH "sessions" AS (
    SELECT
        rasa_llm_command.session_id AS "session_id",
        date_trunc('month', rasa_llm_command.inserted_at) AS "timestamp",
        (
          CASE rasa_llm_command.command
            WHEN 'human handoff'
            THEN 1 ELSE 0
          END
        ) AS "has_handoff_to_support"
    FROM rasa_llm_command
),
"sessions_with_handoff" AS (
    SELECT
      "session_id",
      "timestamp",
      SUM("has_handoff_to_support") AS "has_handoff_to_support"
    FROM "sessions"
    GROUP BY 1, 2
)
SELECT
  "timestamp",
  100.0 * SUM("has_handoff_to_support") / count(*) AS "escalation_rate"
FROM "sessions_with_handoff"
GROUP BY 1
ORDER BY 1 ASC;

WITH "completed_sessions" AS (
    SELECT COUNT(DISTINCT rasa_flow_status.session_id) as "completed_count"
    FROM rasa_flow_status
    WHERE rasa_flow_status.flow_identifier = 'transfer_money' AND rasa_flow_status.flow_status = 'completed'
)

SELECT 100 * (SELECT "completed_sessions". "completed_count" FROM "completed_sessions") / NULLIF(COUNT(DISTINCT rasa_flow_status.SESSION_ID), 0) as "resolution_rate_percentage"
FROM rasa_flow_status
WHERE rasa_flow_status.flow_identifier = 'transfer_money' AND rasa_flow_status.flow_status = 'started';

WITH "interrupted_sessions" AS (
    SELECT COUNT(DISTINCT rasa_flow_status.session_id) as "interrupted_count"
    FROM rasa_flow_status
    WHERE rasa_flow_status.flow_identifier = 'setup_recurrent_payment' AND rasa_flow_status.flow_status = 'interrupted'
)

SELECT 100 * (SELECT "interrupted_sessions". "interrupted_count" FROM "interrupted_sessions") / NULLIF(COUNT(DISTINCT rasa_flow_status.SESSION_ID), 0) as "resumption_rate_percentage"
FROM rasa_flow_status
WHERE rasa_flow_status.flow_identifier = 'setup_recurrent_payment' AND rasa_flow_status.flow_status = 'started';

WITH "resumed_sessions" AS (
    SELECT COUNT(DISTINCT rasa_flow_status.session_id) as "resumed_count"
    FROM rasa_flow_status
    WHERE rasa_flow_status.flow_identifier = 'book_restaurant' AND rasa_flow_status.flow_status = 'resumed'
)

SELECT 100 * (SELECT "resumed_sessions". "resumed_count" FROM "resumed_sessions") / NULLIF(COUNT(DISTINCT rasa_flow_status.SESSION_ID), 0) as "resumption_rate_percentage"
FROM rasa_flow_status
WHERE rasa_flow_status.flow_identifier = 'book_restaurant' AND rasa_flow_status.flow_status = 'interrupted';

SELECT DISTINCT t1.flow_step_id, COUNT(DISTINCT t1.session_id) AS "session_count"
FROM rasa_dialogue_stack_frame t1
JOIN rasa_flow_status t2 ON t1.session_id = t2.session_id
WHERE t1.active_flow_identifier = 'setup_recurrent_payment' AND t2.flow_status NOT IN ('cancelled', 'completed')
GROUP BY 1
ORDER BY 1;

SELECT
    COUNT(DISTINCT CASE WHEN active_flow_identifier='replace_card' THEN session_id ELSE null END) as "replace_card_starts",
    COUNT(DISTINCT CASE WHEN active_flow_identifier='replace_eligible_card' THEN session_id ELSE null END) as "replace_eligible_card_starts"
FROM rasa_dialogue_stack_frame;