Tenant Usage Report

parameter("orgId", type=string, default="d1df41ac-9636-4a36-ada3-6d814b7405e0") # Tenant org ID
require mcp BigQuery

components {
    schema("active_user") {
        user_id: string
        last_active: string # ISO date of most recent activity
        action_count: int   # total_exec_calls from user_block
        mcps_connected: string
        stage: string
        recency: string
    }
    schema("tool_call_breakdown") {
        server: string
        total_calls: int
        errors: int
        error_rate: float # 0–1
    }
    schema("audit_summary_status") {
        total_events: int
        success: int
        failure: int
        error_rate: float
    }
    schema("audit_summary_types") {
        SYSTEM: int
        TOKEN_EXCHANGE: int
        TOOL_CALL: int
        POLICY_DECISION: int
    }
    schema("daily_activity_point") {
        date: string
        total_events: int
        token_exchanges: int
    }
    schema("error_pattern") {
        server: string
        error: string
        count: int
        last_seen?: string
    }
    schema("mcp_server") {
        name: string
        status: string
        last_accessed?: string
        total_calls: int
        total_errors: int
    }
}

session("analyzed_data", target="tenant_report") {
    use mcp BigQuery

    call("execute_sql_readonly") -> tenantData {
        projectId = "barndoor-production"
        query = "SELECT * FROM `barndoor-production.gtm_analytics.tenant_block` WHERE tenant_id = '{{ .params.orgId }}'"
    }

    call("execute_sql_readonly") -> activeUsers {
        projectId = "barndoor-production"
        query = "SELECT user_id, CAST(last_active_date AS STRING) AS last_active, total_exec_calls AS action_count, mcps_connected, stage, recency FROM `barndoor-production.gtm_analytics.user_block` WHERE tenant_id = '{{ .params.orgId }}' ORDER BY last_active_date DESC"
    }

    call("execute_sql_readonly") -> toolCallBreakdown {
        projectId = "barndoor-production"
        query = "SELECT mcp AS server, SUM(call_count) AS total_calls, SUM(error_count) AS errors, SAFE_DIVIDE(SUM(error_count), NULLIF(SUM(call_count), 0)) AS error_rate FROM `barndoor-production.gtm_analytics.fact_tool_call_daily` WHERE tenant_id = '{{ .params.orgId }}' AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY mcp ORDER BY total_calls DESC"
    }

    call("execute_sql_readonly") -> auditStatus {
        projectId = "barndoor-production"
        query = "SELECT COUNT(*) AS total_events, COUNTIF(status = 'success') AS success, COUNTIF(status != 'success') AS failure, SAFE_DIVIDE(COUNTIF(status != 'success'), NULLIF(COUNT(*), 0)) AS error_rate FROM `barndoor-production.gtm_analytics.audit_health_events` WHERE tenant_id = '{{ .params.orgId }}'"
    }

    call("execute_sql_readonly") -> auditTypes {
        projectId = "barndoor-production"
        query = "SELECT COUNTIF(event_type = 'SYSTEM') AS SYSTEM, COUNTIF(event_type = 'TOKEN_EXCHANGE') AS TOKEN_EXCHANGE, COUNTIF(health_event_category = 'tool_call') AS TOOL_CALL, COUNTIF(event_type = 'POLICY_DECISION') AS POLICY_DECISION FROM `barndoor-production.gtm_analytics.audit_health_events` WHERE tenant_id = '{{ .params.orgId }}'"
    }

    call("execute_sql_readonly") -> dailyActivity {
        projectId = "barndoor-production"
        query = "SELECT CAST(event_date AS STRING) AS date, SUM(event_count) AS total_events, COUNTIF(health_event_category = 'oauth_token_exchange') AS token_exchanges FROM `barndoor-production.gtm_analytics.tenant_health_event_daily` WHERE tenant_id = '{{ .params.orgId }}' AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY event_date ORDER BY event_date ASC"
    }

    call("execute_sql_readonly") -> errorPatterns {
        projectId = "barndoor-production"
        query = "SELECT mcp AS server, error_message AS error, COUNT(*) AS count, MAX(CAST(timestamp AS STRING)) AS last_seen FROM `barndoor-production.gtm_analytics.tool_call_errors` WHERE tenant_id = '{{ .params.orgId }}' AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND mcp NOT LIKE 'z[DONOTUSE]%' GROUP BY mcp, error_message ORDER BY count DESC"
    }

    call("execute_sql_readonly") -> mcpServers {
        projectId = "barndoor-production"
        query = "SELECT mcp_name AS name, status, CAST(last_tool_call AS STRING) AS last_accessed, total_tool_calls AS total_calls, total_errors FROM `barndoor-production.gtm_analytics.mcp_block` WHERE tenant_id = '{{ .params.orgId }}' QUALIFY ROW_NUMBER() OVER (PARTITION BY mcp_name ORDER BY CASE LOWER(status) WHEN 'error' THEN 0 WHEN 'active' THEN 1 WHEN 'pending' THEN 2 ELSE 3 END, last_tool_call DESC) = 1 ORDER BY total_tool_calls DESC"
    }

    + BigQuery returned the following data for tenant {{ .params.orgId }}:
      Tenant: {{ .vars.tenantData | json }}
      Active Users: {{ .vars.activeUsers | json }}
      Tool Call Breakdown: {{ .vars.toolCallBreakdown | json }}
      Audit Status: {{ .vars.auditStatus | json }}
      Audit Types: {{ .vars.auditTypes | json }}
      Daily Activity: {{ .vars.dailyActivity | json }}
      Error Patterns: {{ .vars.errorPatterns | json }}
      MCP Servers: {{ .vars.mcpServers | json }}

    - Map all results into the output schema. Use 0 for missing counts, omit optional fields if null.

    schema {
        active_users: $active_user[]
        tool_call_breakdown: $tool_call_breakdown[]
        audit_summary_status: $audit_summary_status
        audit_summary_types: $audit_summary_types
        daily_activity: $daily_activity_point[]
        error_patterns: $error_pattern[]
        mcp_servers: $mcp_server[]
    }
}

session("executive_summary", after="analyzed_data") {
    context "Tenant Report Data: {{ .context.tenant_report | json }}"

    - Write `exec_summary`: a concise 3–5 sentence executive summary of this tenant's
      health for a Customer Success audience. Ground every claim in the data above — do not
      invent anything. Cover:
        - Adoption: number of active users and the mix of lifecycle stages (e.g. how many
          Habitual/Active vs stuck in Setup Incomplete).
        - Usage: overall 30-day tool-call volume and the top MCP servers driving it.
        - Reliability: the tool-call failure rate, plus any notable error patterns or servers
          currently in an Error state.
        - Risk / opportunity: the single most important thing a CSM should act on (e.g. many
          users stuck in setup, a spiking error, or heavy policy denials).
      Be specific with real numbers. If the tenant has essentially no activity, say so plainly.

    schema {
        exec_summary: string
    }
}