cotera

Zendesk Support Trends to Google Sheets

Turn your ticket queue into a weekly trends report. No more copy-pasting numbers into spreadsheets every Monday.

CSAT trackingTrend analysisWeekly reportingSLA monitoring

The Challenge

Every week, someone on the support team spends two hours pulling ticket data, categorizing issues, computing CSAT averages, and pasting everything into a spreadsheet. Then leadership asks a follow-up question and the whole process starts again. This prompt does all of that in one shot and logs it automatically.

What This Prompt Does

Categorize Tickets

Group by issue type, priority, and channel automatically

Calculate Metrics

CSAT scores, resolution times, and SLA breach rates

Spot Trends

Compare week-over-week to catch spikes early

Log to Sheets

Append summary rows so your charts auto-update

The Prompt

The Prompt

Task

Analyze a batch of Zendesk support tickets to identify trends — top complaint categories, average resolution time, CSAT distribution, and recurring issues. Then log the summary data to a Google Sheets spreadsheet using @Google Sheets/Append Row so your team hasName it "Google Sheets/Append Row so your team has" and call it with @Google Sheets/Append Row so your team has a running weekly report without manual work.

Example: Summarize this week's 200 support tickets and append the trend data to our Support Metrics spreadsheet.

Input

The user will provide:

  1. Ticket data (exported list or time range to analyze)
  2. Google Sheets spreadsheet ID for logging results
  3. Optional: sheet name or tab for the data

Example: "Analyze last week's tickets and log the summary to spreadsheet ID 1abc...xyz on the Weekly Trends tab"

Context

What to Track

Volume metrics:

  • Total tickets opened and resolved
  • Tickets by priority (urgent, high, normal, low)
  • Tickets by channel (email, chat, phone, social)
  • New vs. reopened tickets

Quality metrics:

  • Average first response time
  • Average resolution time
  • CSAT score breakdown (good, bad, no rating)
  • SLA breach count and percentage

Category breakdown:

  • Top 5 issue categories by volume
  • Emerging issues (new categories or spikes)
  • Repeat offenders (same customer, same issue)

Analysis Strategy

  1. Group tickets by category, priority, and status
  2. Calculate averages for resolution time and response time
  3. Compute CSAT distribution if satisfaction data is available
  4. Identify week-over-week changes using @Google Sheets/Read RangeName it "Google Sheets/Read Range" and call it with @Google Sheets/Read Range on prior entries
  5. Append the new week's summary row to the spreadsheet
  6. Flag any anomalies (volume spikes, CSAT drops, SLA breaches)

What Makes a Good Trend Report

  • Compare to the prior week so changes are obvious
  • Highlight anomalies and call out what needs attention
  • Keep the spreadsheet row consistent so charts auto-update
  • Include both raw numbers and percentages

Output

Weekly Support Summary:

Period: [Date range] Total Tickets: [count] ([+/-X% vs. prior week])

Volume Breakdown:

| Priority | Count | % of Total | vs. Last Week | |----------|-------|------------|---------------| | Urgent | [X] | [X%] | [+/-X%] | | High | [X] | [X%] | [+/-X%] | | Normal | [X] | [X%] | [+/-X%] | | Low | [X] | [X%] | [+/-X%] |

Top 5 Issue Categories:

| Category | Tickets | % of Total | Trend | |----------|---------|------------|-------| | [Category 1] | [X] | [X%] | Rising/Stable/Falling |

Performance Metrics:

| Metric | This Week | Last Week | Change | |--------|-----------|-----------|--------| | Avg First Response | [time] | [time] | [+/-] | | Avg Resolution Time | [time] | [time] | [+/-] | | CSAT Score | [X%] | [X%] | [+/-] | | SLA Breach Rate | [X%] | [X%] | [+/-] |

Google Sheets Row Appended:

  • Spreadsheet: [ID]
  • Sheet: [Tab name]
  • Row data: [date, total_tickets, urgent, high, normal, low, avg_response, avg_resolution, csat, sla_breach_rate]

Flags & Recommendations:

  1. [Notable trend or anomaly]
  2. [Action item based on the data]

Example Usage

Try asking:

  • →"Summarize last week's tickets and add to the Support Metrics sheet"
  • →"What were our top 5 issue categories this month? Log it."
  • →"Track CSAT trends for Q1 and append weekly rows to our reporting sheet"