User Convo Summary

User Conversation Summary

Summarize a user's agent mode history from their email address.

Schema config

The bq queries below use the default logical mapping: project YOUR_GCP_PROJECT, core_user_facts (dataset prod) and agent_mode_messages_ugc (dataset prod_ugc). Replace them with your physical names from config/schema.json (see SCHEMA.md).

Step 1: Look Up user_id

bq query --use_legacy_sql=false --format=csv \
  "SELECT user_id FROM \`YOUR_GCP_PROJECT.prod.core_user_facts\`
   WHERE user_email = '[USER_EMAIL]' LIMIT 1"

Step 2: Fetch Recent Conversations

bq query --use_legacy_sql=false --format=csv \
  "SELECT conversation_id, message_type, timestamp_utc, msg_content
   FROM `YOUR_GCP_PROJECT.prod_ugc.agent_mode_messages_ugc`
   WHERE user_id = '[USER_ID]'
   ORDER BY timestamp_utc DESC
   LIMIT 500"

Key message_type values:

• user — the user's prompt (text at msg_content JSON path $.query)
• assistant — what the agent responded/did

Step 3: Analyze

• Extract user messages (prompt via JSON_EXTRACT_SCALAR(msg_content, '$.query')) to understand what the user asked
• Extract assistant messages to see what the agent did
• Group by conversation_id to understand conversation flow
• Look for patterns in topics, projects, and workflows

Step 4: Write Summary

Write 1–2 paragraphs capturing:

• Primary use cases and topics
• Key tools/workflows leveraged
• Engagement patterns (conversation length, frequency, iterative vs. one-shot)
• Notable preferences or recurring patterns