DEV Community

Aadil Lone
Aadil Lone

Posted on

Turning Escalation Data into Strategic Decisions with Gemini in BigQuery

#googlecloud #bigquery #genai #ai

Gen AI Academy APAC — Track 2 (Turn Business Data into Strategic Decisions)

Why this one, and why it builds on Track 1

For Track 1, I built an agent that classifies individual customer service escalations one at a time. That's useful for reviewing a single case — but as a team lead managing 25 agents, the real question I ask most weeks isn't about one case. It's "what's actually going wrong across all of them, and what should I do about it?"

That's a data question, not a single-case question — which made it a natural fit for Track 2's focus: using Gemini directly inside BigQuery to go from raw data to an actual recommendation, without needing a separate app or notebook.

What I built

  1. Loaded 70 real-shaped escalation cases into BigQuery — same dataset structure as Track 1 (case ID, summary, agent, channel).
  2. Classified every case with Gemini, directly in SQL, using AI.GENERATE(): 
SELECT case_id, agent_id, channel, summary,
  AI.GENERATE(
    prompt => CONCAT(
      'Classify this customer service escalation into exactly one of: ',
      'policy_misapplication, process_gap, communication_breakdown, ',
      'system_limitation, training_gap, customer_expectation_mismatch. ',
      'Respond with only the category name. Case: ', summary
    ),
    connection_id => 'us-central1.cx_gemini_conn',
    endpoint => 'gemini-2.5-flash'
  ).result AS root_cause
FROM `cx_insights.escalations`
Enter fullscreen mode Exit fullscreen mode

No separate agent deployment needed for this part — the classification happens where the data already lives.

  1. Fed the aggregated results back into Gemini to generate an actual strategic recommendation, not just a chart: 
SELECT AI.GENERATE(
  prompt => CONCAT(
    'You are a customer service operations analyst. Based on this ',
    'root cause breakdown across 70 escalations, write a 3-4 sentence ',
    'strategic recommendation for a team lead managing 25 agents. ',
    'Be specific and actionable. Data: ',
    (SELECT STRING_AGG(FORMAT('%s: %d cases', root_cause, cnt), ', ')
     FROM (SELECT root_cause, COUNT(*) AS cnt
           FROM `cx_insights.escalations_classified`
           GROUP BY root_cause))
  ),
  connection_id => 'us-central1.cx_gemini_conn',
  endpoint => 'gemini-2.5-flash'
).result AS strategic_recommendation
Enter fullscreen mode Exit fullscreen mode

The result, generated directly from the data — no manual analysis in between:

"The primary drivers of escalations are training gaps (20 cases) and system limitations (15 cases). The team lead should prioritize a comprehensive review and enhancement of agent training, focusing on areas directly impacted by these gaps, perhaps through targeted workshops or updated knowledge base articles. Simultaneously, they must collaborate with relevant departments to identify and address the most impactful system limitations, providing agents with temporary workarounds and clear communication regarding ongoing resolutions."

That's genuinely close to the kind of recommendation I'd write myself after a manual review — except it took seconds instead of an hour of reading through cases individually.

What stood out

  • AI.GENERATE inside plain SQL is a low ceremony way to add reasoning to data you already have. No separate service, no agent framework — just a function call in a query.
  • The two-step pattern (classify → aggregate → summarize) is more useful than either step alone. Per-case classification is Track-1-style detail; the aggregated recommendation is what actually changes what a team lead does Monday morning.
  • Connection setup (BigQuery ↔ Vertex AI) was the fiddly part, similar to the IAM friction from Track 1's Cloud Run deployment — worth budgeting time for on a fresh project.

Try it / see the queries

Source project: https://github.com/aadilmajeedlone/escalation-root-cause-agent (same repo as Track 1 — the BigQuery queries and dataset setup are documented alongside the agent code)

What's next

Combining both tracks: have the Track 1 agent write directly into this BigQuery table as escalations come in, so the strategic recommendation query always reflects the current week rather than a static export — a live dashboard for the team, not a one-off report.

Built for the Google Cloud Gen AI Academy APAC program, in partnership with Hack2Skill.

Top comments (0)