# Zendesk Support Trends to Google Sheets

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

Source: https://cotera.co/solutions/support/zendesk-support-trends-google-sheets

---

- **Team:** Support
- **Tools:** Zendesk, Google Sheets
- **Difficulty:** easy
- **Setup time:** 5 min
- **Time saved:** 2 hrs/week

## How it works
1. **Categorize Tickets** — Group by issue type, priority, and channel automatically
2. **Calculate Metrics** — CSAT scores, resolution times, and SLA breach rates
3. **Spot Trends** — Compare week-over-week to catch spikes early
4. **Log to Sheets** — Append summary rows so your charts auto-update

## Example requests
- 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

## Prompt

```markdown
## 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 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 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]
```

## Related reading
- [AI Customer Support Shopify](https://cotera.co/articles/ai-customer-support-shopify.md)
- [Best AI Customer Support Tools](https://cotera.co/articles/best-ai-customer-support-tools.md)
- [Slack Bot Customer Support Guide](https://cotera.co/articles/slack-bot-customer-support-guide.md)