Customer Health Report

system("You are a CS and DevOps analyst generating a customer health report.")

parameter("tenant_id", type=string, title="Tenant ID")
parameter("customer_name", type=string, title="Customer Name")

require mcp BigQuery

components {
    schema("ServerStats") {
        mcp_name: string
        status: string
        total_tool_calls: int
        total_errors: int
        error_rate: float
        active_users: int
        distinct_tools_called: int
        active_days: int
        first_call: string
        last_call: string
        days_since_last_call: int
        connected_but_never_called: bool
    }

    schema("DailyRollup") {
        event_date: string
        total_calls: int
        errors: int
        error_rate: float
        active_tenants: int
    }

    schema("TopServers") {
        mcp_server: string
        total_calls: int
        errors: int
        error_rate: float
        active_days: int
    }

    schema("ToolStats") {
        mcp_server: string
        tool_name: string
        total_calls: int
        errors: int
        error_rate: float
    }

    schema("UserActivity") {
        user_id: string
        total_exec_calls: int
        last_active_date: string
        stage: string
        recency: string
    }

    schema("ErrorPattern") {
        mcp_server: string
        tool_name: string
        error_message: string
        occurrences: int
        last_seen: string
    }
}

session("server_breakdown") {
    use mcp BigQuery

    + Run this BigQuery SQL to get per-server stats for {{ .params.customer_name }} (tenant {{ .params.tenant_id }}):
```sql
      SELECT
        mcp_name,
        status,
        total_tool_calls,
        total_errors,
        SAFE_DIVIDE(total_errors, NULLIF(total_tool_calls, 0)) AS error_rate,
        active_users,
        distinct_tools_called,
        active_days,
        CAST(first_tool_call AS STRING) AS first_call,
        CAST(last_tool_call AS STRING)  AS last_call,
        days_since_last_call,
        connected_but_never_called
      FROM `barndoor-production.gtm_analytics.mcp_block`
      WHERE tenant_id = '{{ .params.tenant_id }}'
      ORDER BY total_tool_calls DESC;
```

    - Map results into the schema.

    schema $ServerStats[]
}

session("daily_rollup", after="server_breakdown") {
    use mcp BigQuery

    + Run this BigQuery SQL to get the 30-day daily call trend for {{ .params.customer_name }}:
```sql
      SELECT
        CAST(event_date AS STRING) AS event_date,
        SUM(call_count)            AS total_calls,
        SUM(error_count)           AS errors,
        SAFE_DIVIDE(SUM(error_count), NULLIF(SUM(call_count), 0)) AS error_rate,
        COUNT(DISTINCT tenant_id)  AS active_tenants
      FROM `barndoor-production.gtm_analytics.fact_tool_call_daily`
      WHERE tenant_id = '{{ .params.tenant_id }}'
        AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      GROUP BY event_date
      ORDER BY event_date ASC;
```

    - Map results.

    schema $DailyRollup[]
}

session("top_servers", after="daily_rollup") {
    use mcp BigQuery

    + Run this BigQuery SQL to get the top servers by volume for {{ .params.customer_name }} over the last 30 days:
```sql
      SELECT
        mcp                        AS mcp_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,
        COUNT(DISTINCT event_date) AS active_days
      FROM `barndoor-production.gtm_analytics.fact_tool_call_daily`
      WHERE tenant_id = '{{ .params.tenant_id }}'
        AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      GROUP BY mcp
      ORDER BY total_calls DESC
      LIMIT 10;
```

    - Map results.

    schema $TopServers[]
}

session("tool_breakdown", after="top_servers") {
    use mcp BigQuery

    + Run this BigQuery SQL to get tool-level usage for {{ .params.customer_name }}:
```sql
      SELECT
        mcp        AS mcp_server,
        tool_name,
        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.tenant_id }}'
        AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      GROUP BY mcp, tool_name
      ORDER BY total_calls DESC;
```

    - Map results.

    schema $ToolStats[]
}

session("user_activity", after="tool_breakdown") {
    use mcp BigQuery

    + Run this BigQuery SQL to get user-level activity for {{ .params.customer_name }}:
```sql
      SELECT
        user_id,
        total_exec_calls,
        CAST(last_active_date AS STRING) AS last_active_date,
        stage,
        recency
      FROM `barndoor-production.gtm_analytics.user_block`
      WHERE tenant_id = '{{ .params.tenant_id }}'
        AND total_exec_calls > 0
      ORDER BY total_exec_calls DESC
      LIMIT 50;
```

    - Map results.

    schema $UserActivity[]
}

session("error_patterns", after="user_activity") {
    use mcp BigQuery

    + Run this BigQuery SQL to get error patterns for {{ .params.customer_name }}:
```sql
      SELECT
        mcp        AS mcp_server,
        tool_name,
        error_message,
        COUNT(*)                       AS occurrences,
        MAX(CAST(timestamp AS STRING)) AS last_seen
      FROM `barndoor-production.gtm_analytics.tool_call_errors`
      WHERE tenant_id = '{{ .params.tenant_id }}'
        AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      GROUP BY mcp, tool_name, error_message
      ORDER BY occurrences DESC;
```

    - Map results.

    schema $ErrorPattern[]
}

session("executive_summary", after="error_patterns") {
    context "Servers: {{ .context.server_breakdown | json }}\nDaily Rollup: {{ .context.daily_rollup | json }}\nTop Servers: {{ .context.top_servers | json }}\nTools: {{ .context.tool_breakdown | json }}\nUsers: {{ .context.user_activity | json }}\nErrors: {{ .context.error_patterns | json }}"

    - You are a Customer Success lead preparing an executive health report for {{ .params.customer_name }}.
      Based on the data above, synthesize a report covering: adoption risk, single-user dependency, dormant server inventory, policy gaps, and concrete next steps for the CS and account teams.

    schema {
        overview: string
        risk_flags: string[]
        adoption_gaps: string[]
        action_items: string[]
        recommended_asks_for_champion: string[]
    }