Data, analytics and BI for assistants

Why data is half the product

An assistant produces a stream of evidence about what customers need, where it helps and where it fails. Teams that capture it well improve every week. Teams that don't argue about anecdotes. This module covers the data and business-intelligence side: what to log, how to model it, which metrics to trust, and how to turn findings into action.

What to log

Log structured events, not just raw text. At minimum, capture:

Log versions above all. When a metric moves, the first question is "what changed?" and versions answer it.

A simple data model

One source of truth for metrics, so every team reads the same numbers.

Core tables:

• conversations: one row per conversation, with outcome, route, topic, language, escalation, cost and rating.
• turns: one row per message, with latency, tokens, retrieval and model fields.
• tool_calls: one row per tool call, with status and duration.
• feedback: ratings and comments linked to conversations.

Define metrics once, in a semantic layer or a shared SQL view, so "resolution rate" doesn't mean three different things in three dashboards.

Metrics, defined precisely

Resolution and cost are the two that matter most, and the ones most often miscomputed.

Queries that answer real questions

These examples use standard SQL with Postgres-style date arithmetic. Adjust to your warehouse's dialect.

Resolution proxy and containment by topic, treating a repeat contact within seven days as an unresolved conversation:

WITH convo AS (
  SELECT conversation_id, user_hash, topic, started_at, escalated
  FROM conversations
),
repeats AS (
  SELECT a.conversation_id,
         MAX(CASE WHEN b.conversation_id IS NOT NULL THEN 1 ELSE 0 END) AS repeated
  FROM convo a
  LEFT JOIN convo b
    ON b.user_hash = a.user_hash
   AND b.topic = a.topic
   AND b.started_at > a.started_at
   AND b.started_at <= a.started_at + INTERVAL '7 days'
  GROUP BY a.conversation_id
)
SELECT c.topic,
       COUNT(*) AS conversations,
       AVG(CASE WHEN c.escalated THEN 0.0 ELSE 1.0 END) AS containment,
       AVG(CASE WHEN NOT c.escalated AND r.repeated = 0 THEN 1.0 ELSE 0.0 END) AS resolution_proxy
FROM convo c
JOIN repeats r USING (conversation_id)
GROUP BY c.topic
ORDER BY conversations DESC;

Cost per resolved conversation by route, with human time included:

SELECT route,
       SUM(model_cost + tool_cost + retrieval_cost + human_minutes * 0.75) AS total_cost,
       SUM(CASE WHEN resolved THEN 1 ELSE 0 END) AS resolved_conversations,
       SUM(model_cost + tool_cost + retrieval_cost + human_minutes * 0.75)
         / NULLIF(SUM(CASE WHEN resolved THEN 1 ELSE 0 END), 0) AS cost_per_resolved
FROM conversations
GROUP BY route;

The 0.75 stands for an assumed cost per human-handling minute. Use your real figure.

Dashboards for different audiences

Avoid misleading dashboards: segment by language, channel, topic and complexity; show distributions, not only averages; mark release dates on charts; show sample sizes; and link every chart to an action someone owns.

Discover topics and failure clusters

Free-text conversations contain patterns no dashboard shows. A practical approach:

1. Embed user messages (or conversation summaries) and cluster them.
2. Label clusters with a language model, then have a person review the labels.
3. Rank clusters by volume, failure rate, escalation rate and cost.
4. Act: a cluster with high volume and high failure becomes a content fix, a new flow, a tool or a test.

Always sample real conversations from each important cluster. Models label quickly, and people check whether the labels are right.

Experiments and A/B tests

• Define the metric and a guardrail before starting: for example, improve resolution without raising escalation cost or safety flags.
• Randomize by user or conversation, consistently, and avoid mixing variants within one conversation.
• Estimate the sample size you need. Small differences need many conversations.
• Run long enough to cover weekly cycles and avoid novelty effects.
• Segment results, since a change can help one language or topic and hurt another.
• Pair with offline tests: use your evaluation set to catch regressions before exposure.

Privacy and governance

• Minimize: log what you need, not everything.
• Redact personal data before storing or sharing, and before sending to third-party analytics.
• Limit retention and delete on schedule.
• Control access by role. Few people need raw transcripts.
• Aggregate in reports, and avoid showing small groups that could identify individuals.
• Document where data goes and the rules that apply to you, and ask qualified counsel for legal questions.

Close the loop

Analytics matters only if it changes the product. A weekly rhythm works well: review the dashboard, sample conversations from the worst clusters, create tickets (content, design, tool, test), and check next week whether the metrics moved.