Example usecases

-- Step 1: Aggregate daily interaction metrics by status
WITH daily_metrics AS (
  SELECT
    -- Convert UTC timestamp to local timezone (UTC+2) before extracting date
    -- This ensures dates align with business hours in the local timezone
    DATE(TIMESTAMP_ADD(created_at, INTERVAL 2 HOUR)) AS interaction_date,
    
    -- Count all interactions for the day
    COUNT(*) AS total_interactions,
    
    -- BigQuery COUNTIF: counts rows where condition is true (more efficient than SUM(CASE))
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred,
    
    -- Calculate transfer rate as percentage
    -- Division in BigQuery automatically handles float conversion
    ROUND(COUNTIF(status = 'transferred') / COUNT(*) * 100, 1) AS transfer_rate_pct
    
  FROM interactions
  WHERE 
    owner_user_id = 'enter chatbotID here'  -- Filter to specific chatbot ID
    -- DATE_SUB: BigQuery function to subtract days from current date
    -- CURRENT_DATE() returns today's date (compared at midnight UTC when used with timestamps)
    AND created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  
  GROUP BY interaction_date
)

-- Step 2: Return final results ordered by most recent first
SELECT
  interaction_date,
  total_interactions,
  deflected,
  transferred,
  transfer_rate_pct
FROM daily_metrics
ORDER BY interaction_date DESC

-- Step 1: Aggregate daily interaction metrics by date and channel
WITH daily_channel_metrics AS (
  SELECT
    -- Convert UTC timestamp to local timezone (UTC+2) before extracting date
    -- This ensures dates align with business hours in the local timezone
    DATE(TIMESTAMP_ADD(created_at, INTERVAL 2 HOUR)) AS interaction_date,
    
    -- Group by channel type to compare performance across channels
    channel_id,
    
    -- Count all interactions for this date/channel combination
    COUNT(*) AS total_interactions,
    
    -- BigQuery COUNTIF: counts rows where condition is true (more efficient than SUM(CASE))
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred,
    
    -- Calculate transfer rate as percentage
    -- Division in BigQuery automatically handles float conversion
    ROUND(COUNTIF(status = 'transferred') / COUNT(*) * 100, 1) AS transfer_rate_pct
    
  FROM interactions
  WHERE 
    owner_user_id = 'enter chatbotID here'  -- Filter to specific chatbot
    -- DATE_SUB: BigQuery function to subtract days from current date
    AND created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  
  -- GROUP BY both dimensions to get one row per date/channel combination
  GROUP BY interaction_date, channel_id
)

-- Step 2: Return final results ordered by date (newest first), then channel
SELECT
  interaction_date,
  channel_id,
  total_interactions,
  deflected,
  transferred,
  transfer_rate_pct
FROM daily_channel_metrics
ORDER BY 
  interaction_date DESC,  -- Most recent dates first
  channel_id            -- Alphabetical channel order within each date

-- Step 1: Flatten the started_processes array to create one row per flow
-- UNNEST is a BigQuery-specific operator that expands array fields into rows
WITH interaction_flows AS (
  SELECT
    i.interaction_id,
    i.status,
    process AS flow  -- Each element from started_processes array becomes a separate row
  FROM interactions i,
  -- UNNEST: Converts array into rows (like a lateral join in other SQL dialects)
  -- If an interaction has 3 flows, it creates 3 rows with the same interaction_id
  UNNEST(started_processes) AS process
  WHERE 
    i.owner_user_id = 'here enter chatbotID'  -- Filter to specific chatbot
    -- TIMESTAMP(): Convert DATE to TIMESTAMP for comparison with created_at
    AND i.created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
),

-- Step 2: Aggregate metrics by flow
flow_metrics AS (
  SELECT
    flow,
    
    -- COUNT(DISTINCT) is crucial here because UNNEST can create duplicate interaction_ids
    -- if the same interaction started the same flow multiple times
    COUNT(DISTINCT interaction_id) AS total_interactions,
    
    -- COUNTIF counts rows where condition is true
    -- Note: This counts rows, not unique interactions (one interaction can have multiple status rows)
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred,
    
    -- Transfer rate calculation uses DISTINCT count in denominator
    -- to get accurate percentage (interactions, not flow instances)
    ROUND(COUNTIF(status = 'transferred') / COUNT(DISTINCT interaction_id) * 100, 1) AS transfer_rate_pct
    
  FROM interaction_flows
  GROUP BY flow
)

-- Step 3: Return results ordered by volume (highest traffic flows first)
SELECT
  flow,
  total_interactions,
  deflected,
  transferred,
  transfer_rate_pct
FROM flow_metrics
ORDER BY total_interactions DESC

-- Step 1: Flatten the used_knowledge_sources array to create one row per source
-- UNNEST is a BigQuery-specific operator that expands array fields into rows
WITH interaction_knowledge AS (
  SELECT
    i.interaction_id,
    i.status,
    knowledge_source  -- Each element from used_knowledge_sources array becomes a separate row
  FROM interactions i,
  -- UNNEST: Converts array into rows (like a lateral join in other SQL dialects)
  -- If an interaction uses 3 sources, it creates 3 rows with the same interaction_id
  UNNEST(used_knowledge_sources) AS knowledge_source
  WHERE 
    i.owner_user_id = 'here enter chatbotID'  -- Filter to specific chatbot
    -- DATE_SUB: BigQuery function to subtract days from current date
    AND i.created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
),

-- Step 2: Aggregate metrics by knowledge source
knowledge_metrics AS (
  SELECT
    knowledge_source,
    
    -- COUNT(DISTINCT) is crucial here because UNNEST can create duplicate interaction_ids
    -- if the same interaction used the same knowledge source multiple times
    COUNT(DISTINCT interaction_id) AS total_interactions,
    
    -- COUNTIF counts rows where condition is true
    -- Note: This counts rows, not unique interactions (one interaction can have multiple status rows)
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred,
    
    -- Transfer rate calculation uses DISTINCT count in denominator
    -- to get accurate percentage (interactions, not knowledge source instances)
    ROUND(COUNTIF(status = 'transferred') / COUNT(DISTINCT interaction_id) * 100, 1) AS transfer_rate_pct
    
  FROM interaction_knowledge
  GROUP BY knowledge_source
)

-- Step 3: Return results ordered by volume (most-used knowledge sources first)
SELECT
  knowledge_source,
  total_interactions,
  deflected,
  transferred,
  transfer_rate_pct
FROM knowledge_metrics
ORDER BY total_interactions DESC

-- Step 1: Identify standalone inbox tickets
-- These are tickets where only human_agent interacted (no AI Agent handoff)
WITH standalone_tickets AS (
  SELECT 
    ticket_id
  FROM interactions
  GROUP BY ticket_id
  HAVING 
    COUNT(DISTINCT owner) = 1       -- Only one type of owner
    AND MAX(owner) = 'human_agent'  -- And that owner is human_agent
),

-- Step 2: Count tickets created per day
created_daily AS (
  SELECT
    -- Convert UTC to local timezone (UTC+2) before extracting date
    DATE(TIMESTAMP_ADD(created_at, INTERVAL 2 HOUR)) AS ticket_date,
    COUNT(DISTINCT ticket_id) AS tickets_created
  FROM tickets
  WHERE 
    ticket_id IN (SELECT ticket_id FROM standalone_tickets)
    -- DATE_SUB: BigQuery function to subtract days from current date
    AND created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  GROUP BY ticket_date
),

-- Step 3: Count tickets solved (closed) per day
solved_daily AS (
  SELECT
    DATE(TIMESTAMP_ADD(updated_at, INTERVAL 2 HOUR)) AS solved_date,
    COUNT(DISTINCT ticket_id) AS tickets_solved
  FROM interactions
  WHERE 
    status = 'closed'
    AND owner = 'human_agent'
    AND ticket_id IN (SELECT ticket_id FROM standalone_tickets)
    AND updated_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  GROUP BY solved_date
)

-- Step 4: Combine created and solved metrics by date
-- Using FULL OUTER JOIN to include days where tickets were only created or only solved
SELECT
  -- COALESCE handles NULLs from FULL OUTER JOIN - takes first non-NULL value
  COALESCE(c.ticket_date, s.solved_date) AS date,
  COALESCE(c.tickets_created, 0) AS tickets_created,
  COALESCE(s.tickets_solved, 0) AS tickets_solved
FROM created_daily c
FULL OUTER JOIN solved_daily s 
  ON c.ticket_date = s.solved_date
ORDER BY date DESC

-- Step 1: Aggregate interaction metrics by contact reason
WITH contact_reason_metrics AS (
  SELECT
    -- BigQuery STRUCT field access: final_contact_reason is a struct with nested fields
    -- Use dot notation to access the 'name' field within the struct
    final_contact_reason.name AS contact_reason,
    
    -- Count all interactions for this contact reason
    COUNT(*) AS total_interactions,
    
    -- BigQuery COUNTIF: counts rows where condition is true (cleaner than SUM(CASE))
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred,
    
    -- Calculate transfer rate as percentage
    -- High transfer rate = AI struggled with this contact reason
    ROUND(COUNTIF(status = 'transferred') / COUNT(*) * 100, 1) AS transfer_rate_pct
    
  FROM interactions
  WHERE 
    owner = 'ai_agent'  -- Only AI Agent interactions (exclude human_agent)
    -- String literal date automatically converted to TIMESTAMP for comparison
    -- BigQuery allows '2026-01-01' instead of requiring TIMESTAMP('2026-01-01')
    AND created_at >= '2026-01-01'
  
  GROUP BY contact_reason
)

-- Step 2: Return results ordered by transfer rate (highest first)
-- This surfaces the contact reasons where AI Agent struggles most
SELECT
  contact_reason,
  total_interactions,
  deflected,
  transferred,
  transfer_rate_pct
FROM contact_reason_metrics
-- ORDER BY transfer_rate_pct DESC prioritizes improvement opportunities
-- Contact reasons with high transfer rates should be reviewed first
ORDER BY transfer_rate_pct DESC

-- Step 1: Calculate monthly aggregations with all metrics
WITH monthly_metrics AS (
  SELECT
    -- BigQuery DATE_TRUNC: Truncates date to the start of the month (1st day)
    -- Returns YYYY-MM-01 for grouping all interactions in the same month
    DATE_TRUNC(DATE(created_at), MONTH) AS month_start_date,
    
    -- BigQuery FORMAT_DATE: Formats date as string using strftime-like patterns
    -- '%Y-%m' produces format like '2025-01' (useful for display/exports)
    FORMAT_DATE('%Y-%m', DATE(created_at)) AS year_month,
    
    -- EXTRACT(MONTH): Returns month number (1-12)
    EXTRACT(MONTH FROM created_at) AS month_number,
    
    -- FORMAT_DATE('%B'): Returns full month name ('January', 'February', etc.)
    -- Useful for human-readable reports
    FORMAT_DATE('%B', DATE(created_at)) AS month_name,
    
    -- Extract year for context (useful when viewing multi-year trends)
    EXTRACT(YEAR FROM created_at) AS year,
    
    -- Total interaction count for the month
    COUNT(*) AS total_interactions,
    
    -- BigQuery COUNTIF: Conditional counting (more efficient than SUM(CASE))
    COUNTIF(owner = 'ai_agent') AS ai_agent_interactions,
    COUNTIF(owner = 'human_agent') AS human_agent_interactions,
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred,
    
    -- Deflection rate: percentage of AI Agent interactions that were successfully deflected
    -- NULLIF prevents division by zero when there are no AI Agent interactions
    ROUND(
      COUNTIF(status = 'deflected') / NULLIF(COUNTIF(owner = 'ai_agent'), 0) * 100, 
      1
    ) AS deflection_rate_pct,
    
    -- Average CSAT score for the month
    ROUND(AVG(quality_csat_score), 2) AS avg_csat_score
    
  FROM interactions
  WHERE 
    -- String literal date automatically converted to TIMESTAMP
    created_at >= '2025-01-01'
  
  -- GROUP BY all non-aggregated columns
  GROUP BY 
    month_start_date,
    year_month,
    month_number,
    month_name,
    year
)

-- Step 2: Return monthly metrics ordered by most recent first
SELECT
  month_start_date,
  year_month,
  month_number,
  month_name,
  year,
  total_interactions,
  ai_agent_interactions,
  human_agent_interactions,
  deflected,
  transferred,
  deflection_rate_pct,
  avg_csat_score
FROM monthly_metrics
ORDER BY month_start_date DESC

-- Step 1: Extract AI Agent interactions with contact reason
-- These represent the initial AI interaction before transfer to human
WITH ai_interactions AS (
  SELECT
    ticket_id,
    -- BigQuery STRUCT field access: final_contact_reason is a nested struct
    -- Use dot notation to extract the 'name' field
    final_contact_reason.name AS contact_reason
  FROM interactions
  WHERE owner = 'ai_agent'
),

-- Step 2: Extract human agent interactions with performance metrics
-- These represent the human agent interaction after AI handoff
human_interactions AS (
  SELECT
    ticket_id,
    -- Convert milliseconds to seconds for readability
    -- BigQuery automatically handles integer division with type promotion
    statistics_queue_waiting_service_time_millis / 1000 AS queue_wait_seconds,
    
    -- SLA metrics: time-based targets for response and completion
    statistics_sla_first_response_time AS first_response_sla,
    statistics_sla_completion_time AS completion_sla,
    
    -- Queue assignment for routing analysis
    assigned_queue_id,
    
    created_at  -- Keep for filtering
  FROM interactions
  WHERE 
    owner = 'human_agent'
    -- String literal date automatically converted to TIMESTAMP
    AND created_at >= '2026-01-01'
),

-- Step 3: Join AI and human interactions to analyze handoff flow
-- Self-join pattern: same table joined to itself on ticket_id
handoff_metrics AS (
  SELECT
    h.ticket_id,
    a.contact_reason,              -- From AI interaction: why customer contacted
    h.queue_wait_seconds,          -- From human interaction: how long customer waited
    h.first_response_sla,          -- SLA for first human response
    h.completion_sla,              -- SLA for ticket completion
    h.assigned_queue_id            -- Which queue handled the ticket
  FROM human_interactions h
  -- INNER JOIN: Only tickets that have both AI and human interactions
  -- This isolates handoff scenarios (AI transferred to human)
  JOIN ai_interactions a
    ON h.ticket_id = a.ticket_id
)

-- Step 4: Return handoff analysis ordered by longest wait times
-- Longest waits appear first to identify queue bottlenecks
SELECT
  ticket_id,
  contact_reason,
  queue_wait_seconds,
  first_response_sla,
  completion_sla,
  assigned_queue_id
FROM handoff_metrics
-- ORDER BY DESC: Prioritize tickets with longest wait times
-- These represent the worst customer experience and potential process issues
ORDER BY queue_wait_seconds DESC

-- Step 1: Extract AI Agent interactions with contact reason
WITH ai_agent_interactions AS (
  SELECT
    ticket_id,
    -- BigQuery STRUCT field access: final_contact_reason is a nested struct
    -- Use dot notation to extract the 'name' field
    final_contact_reason.name AS contact_reason,
    status,  -- 'deflected' or 'transferred'
    created_at
  FROM interactions
  WHERE 
    owner = 'ai_agent'  -- Only AI Agent interactions
    -- String literal date automatically converted to TIMESTAMP
    AND created_at >= '2026-01-01'
),

-- Step 2: Join with tickets to get channel information
-- This enriches interaction data with ticket-level attributes
channel_contact_data AS (
  SELECT
    t.channel_id,           -- Communication channel: chat, email, etc.
    a.contact_reason,         -- Why customer contacted support
    a.status                  -- Interaction outcome
  FROM ai_agent_interactions a
  -- INNER JOIN: Links interactions to their parent tickets
  -- Ticket provides context like channel_id not available in interaction record
  JOIN tickets t 
    ON a.ticket_id = t.ticket_id
),

-- Step 3: Aggregate metrics by channel and contact reason
-- Creates a matrix showing performance for each channel/reason combination
channel_contact_metrics AS (
  SELECT
    channel_id,
    contact_reason,
    
    -- Total AI Agent interactions for this channel/contact reason combination
    COUNT(*) AS ai_interactions,
    
    -- BigQuery COUNTIF: conditional counting (cleaner than SUM(CASE))
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred
    
  FROM channel_contact_data
  -- GROUP BY multiple dimensions: creates one row per unique combination
  -- Results show performance breakdown by both channel AND contact reason
  GROUP BY channel_id, contact_reason
)

-- Step 4: Return results grouped by channel, ordered by volume
SELECT
  channel_id,
  contact_reason,
  ai_interactions,
  deflected,
  transferred
FROM channel_contact_metrics
-- Multi-column ORDER BY: first group by channel, then sort by volume within each channel
-- This shows which contact reasons are most common in each channel
ORDER BY 
  channel_id,           -- Group channels together
  ai_interactions DESC    -- Highest volume contact reasons first within each channel

-- Step 1: Filter to AI Agent interactions only
WITH ai_agent_interactions AS (
  SELECT
    interaction_id,
    -- Array field: used_knowledge_sources contains multiple knowledge base entries
    -- Each entry is a STRUCT with fields like 'name', 'id', etc.
    used_knowledge_sources,
    quality_sentiment,  -- Customer sentiment: POSITIVE, NEGATIVE, NEUTRAL
    status              -- Interaction outcome: deflected or transferred
  FROM interactions
  WHERE 
    owner = 'ai_agent'  -- Only AI Agent interactions
    -- String literal date automatically converted to TIMESTAMP
    AND created_at >= '2026-01-01'
),

-- Step 2: Flatten knowledge sources array into individual rows
-- UNNEST transforms array into separate rows (one row per array element)
interaction_knowledge_sources AS (
  SELECT
    i.interaction_id,
    -- BigQuery STRUCT field access: ks is a struct, extract 'name' field
    -- UNNEST aliases each array element as 'ks' (knowledge source struct)
    ks.name AS knowledge_source,
    i.quality_sentiment,
    i.status
  FROM ai_agent_interactions i,
  -- BigQuery UNNEST: Expands array into rows (implicit CROSS JOIN)
  -- If interaction uses 3 knowledge sources, this creates 3 rows
  UNNEST(i.used_knowledge_sources) AS ks
),

-- Step 3: Aggregate metrics by knowledge source
knowledge_source_metrics AS (
  SELECT
    knowledge_source,
    
    -- COUNT(*) counts rows, not unique interactions
    -- Since UNNEST creates multiple rows per interaction, this counts usage instances
    COUNT(*) AS times_used,
    
    -- BigQuery COUNTIF: conditional counting (more efficient than SUM(CASE))
    COUNTIF(quality_sentiment = 'POSITIVE') AS positive,
    COUNTIF(quality_sentiment = 'NEGATIVE') AS negative,
    COUNTIF(status = 'deflected') AS deflected,
    COUNTIF(status = 'transferred') AS transferred
    
  FROM interaction_knowledge_sources
  GROUP BY knowledge_source
)

-- Step 4: Return results ordered by usage frequency
SELECT
  knowledge_source,
  times_used,
  positive,
  negative,
  deflected,
  transferred
FROM knowledge_source_metrics
-- ORDER BY DESC: Most frequently used knowledge sources first
-- High usage + low deflection = opportunity to improve that knowledge base
ORDER BY times_used DESC

-- Step 1: Filter to AI Agent interactions within time range
WITH ai_agent_interactions AS (
  SELECT
    interaction_id,
    -- BigQuery STRUCT field access: final_contact_reason is a nested struct
    -- Use dot notation to extract the 'name' field
    final_contact_reason.name AS contact_reason,
    
    -- Quality metrics for feedback analysis
    quality_thumbs_down_count,   -- Number of thumbs down received
    quality_sentiment,            -- Overall sentiment: POSITIVE, NEGATIVE, NEUTRAL
    
    -- BigQuery ARRAY fields: These contain multiple values per interaction
    -- Not unnested here - returned as arrays for full context view
    recognized_intents,          -- Array of detected customer intents
    started_processes,           -- Array of workflows/flows triggered
    used_knowledge_sources,      -- Array of knowledge base articles referenced
    
    status,                      -- Interaction outcome: deflected or transferred
    created_at
  FROM interactions
  WHERE 
    owner = 'ai_agent'  -- Only AI Agent interactions (exclude human_agent)
    -- String literal date automatically converted to TIMESTAMP
    AND created_at >= '2026-01-01'
),

-- Step 2: Filter to interactions with negative feedback
-- Isolates problematic interactions for root cause analysis
negative_feedback_interactions AS (
  SELECT
    interaction_id,
    contact_reason,
    quality_thumbs_down_count,
    quality_sentiment,
    recognized_intents,
    started_processes,
    used_knowledge_sources,
    status,
    created_at
  FROM ai_agent_interactions
  WHERE 
    -- Greater than 0: At least one thumbs down received
    -- These represent customer dissatisfaction and need investigation
    quality_thumbs_down_count > 0
)

-- Step 3: Return problematic interactions for analysis
SELECT
  interaction_id,
  contact_reason,
  quality_thumbs_down_count,
  quality_sentiment,
  recognized_intents,
  started_processes,
  used_knowledge_sources,
  status
FROM negative_feedback_interactions
-- ORDER BY DESC: Most recent problems first
-- Recent issues may indicate new bugs or configuration changes
ORDER BY created_at DESC