Setting up your support ticket data in Sheets
Okay, before GPT can analyze anything, we need the data to actually be usable. I know this sounds obvious, but I’ve spent more time than I want to admit scrolling through garbage-formatted CSVs from Zendesk exports where every support ticket had its own oddities — like merged cells, nested HTML in comments, and identical timestamps across completely different tickets ¯\_(ツ)_/¯
In general, start with a Sheet that looks something like this:
| Ticket ID | Created Date | Subject | Description | Status | Agent |
|———–|————–|—————————-|——————————-|——–|———|
| 123456 | 3/10/2024 | Login problem | User can’t login, error 403 | Open | Hannah |
| 123457 | 3/10/2024 | App crashes on launch | Happens on Android v12 only | Open | Marcus |
| 123458 | 3/11/2024 | Add team member not working | Invite email not sent | Closed | Hannah |
Keep these fields consistent:
– One row per ticket
– No merged cells (GPT hates them)
– Avoid formatted text (like bold or bullet points) inside the cells — some GPT wrappers struggle with RTF
Bonus tip: if your Description column includes multi-paragraph replies or screenshots-as-HTML, script-clean those first with a Sheets formula or via Apps Script to reduce noise. GPT will hallucinate less if you feed it cleaner input.
Writing prompts that GPT can actually handle
You’d think just saying “analyze these for common problems” would work. It doesn’t. I’ve tried that. GPT gets confused by vague context unless you wrap it in a painfully specific prompt. And sometimes even that fails if OpenAI’s model is in a weird mood that day 😛
Here’s one that consistently worked for me using GPT-4:
> “You are a customer support analyst. You have received support ticket data in a spreadsheet. Each row represents a ticket, and the ticket subject and description are provided. Please categorize each ticket into one of the following issues: login, billing, mobile app, bugs, feature requests, other. Add a new column with the category. Explain in bullet points your reasoning for 3 randomly selected tickets to verify accuracy.”
Drop that prompt directly into ChatGPT if you’re using their Advanced Data Analysis tool, or through a GPT-integrated Sheets extension like GPT for Sheets (gptforwork.com is a solid one, though not always free).
If you have thousands of rows, chunk it: 50–100 at a time. That avoids context truncation.
Dataset too large? Shorten your descriptions by summarizing them with =GPTDESCRIBE(A1:A100,”short summary in 10 words”). Yes, layering GPT on GPT sounds cursed, but it mostly works 🙂
Using formulas to send data chunks to GPT
If you’re using the GPT for Sheets plugin, here’s what I’ve been using lately:
“`excel
=GPT(“Classify the support issue: ” & A2 & “. Choose from: login, billing, bugs, features, app, other.”)
“`
That assumes your support ticket subject is in A2. You can drag this down for the whole column.
Important: If GPT is giving you blank cells or errors, double-check these:
– Your API key is still valid (I’ve had mine randomly unpair overnight)
– Quotation marks are ASCII (curly quotes throw silent errors)
– You’re not hitting context limits (avoid really long concatenations)
If you want to debug weird behavior, I recommend creating a column called “GPT Raw Output” and feeding the prompt with no concatenation first. That lets you isolate whether it’s the formula or the model misfiring.
Validating GPT output downstream in new columns
One thing I do (because I do not trust GPT 😅) is add a manual check column next to the category GPT returns. Literally just a dropdown with options like:
– Correct
– Wrong Category
– Nonsense
Here’s what that looks like in Sheets:
| Ticket ID | Subject | GPT Category | Human Check |
|———–|———–|————–|—————–|
| 123456 | Login bug | login | Correct |
| 123457 | Crash | bugs | Wrong Category |
| 123458 | Invite | feature req | Correct |
You can then sort or filter only by “Wrong Category” to fine-tune your prompts. Or even retrain the prompt to be more accurate for edge cases you noticed.
I’ve built a simple conditional formatting rule so cells marked “Wrong Category” or “Nonsense” turn red. Makes unexpected patterns stand out quickly — like misclassifications that always happen when the subject includes the word “Android.”
Exporting results as summaries and charts
Once GPT has labeled everything nicely, you can finally do something with it 😅 I usually want:
– A breakdown of how many tickets are in each category
– Charts showing trends by day or agent
– Some automatic summary I can drop into Slack or email
Use a pivot table for the first one:
In Sheets: go to Insert → Pivot Table
– Rows: GPT Category
– Values: COUNT of Ticket ID
That’ll give you something like:
| Category | Count |
|—————-|——-|
| login | 24 |
| bugs | 51 |
| billing | 18 |
| feature req | 10 |
Want a chart? Highlight the pivot table and Insert → Chart. Bar chart works best here.
And for that summary I usually just build a formula like:
“`
=JOIN(CHAR(10),”Categories tracked:”,JOIN(CHAR(10),UNIQUE(B2:B100)))
“`
Replace B2:B100 with your GPT labeled column range. That way I get a copy-and-pasteable block like:
Categories tracked:
login
bugs
billing
feature req
And yeah, that’s usually what I send up the chain whenever someone asks
“what are people complaining about most lately?” 😂