Hey everyone! 👋 I’ve been deep in our support data lately, trying to figure out how to better connect our customer conversations with the rest of our business metrics. We use Zendesk for support, but our team lives in Looker for everything else—revenue, product usage, marketing campaigns. I kept thinking, wouldn't it be amazing to see support ticket trends right next to, say, a feature launch or a pricing change?
So, I spent the last few weekends building a little connector to pipe our Zendesk support tickets directly into our data warehouse (BigQuery, in our case). The goal was to get fresh, queryable ticket data alongside our other datasets for some truly holistic reporting.
Here’s a quick rundown of what I focused on and what the connector pulls through:
* **Ticket Core Data:** The basics like ticket ID, subject, status, priority, and creation/update times.
* **Requester & Agent Info:** Anonymized user IDs and agent IDs so we can join to other user tables without exposing PII in the BI layer.
* **Conversation Threads:** This was the tricky part! I'm capturing each public comment as a separate row with timestamps, so we can analyze response times and conversation length.
* **Tags & Custom Fields:** Super important for segmentation. We use custom fields for things like `plan_type`, `feature_requested`, and `bug_category`. Pulling these into columns makes filtering a breeze.
* **SLA & Time Metrics:** First reply time, full resolution time, and any breaches against our SLA policies.
The real magic for me was setting up the transformation layer. I used a Python script on a Cloud Function to handle the API calls, do some light data cleaning (like standardizing those custom field values), and then load it all into BigQuery tables. I’ve set it to run hourly, which feels like a good balance for our volume.
Now for the fun part—what can you actually *do* with this? Here are a few analyses we've started running:
* Correlating spikes in "billing" tickets with our recent checkout flow A/B test.
* Scoring leads based on support engagement. A user who submits a thoughtful feature request might get a higher lead score in our marketing automation platform!
* Analyzing agent performance and workload distribution beyond Zendesk's own reports, blending it with data from our internal project management tools.
* Tracking the volume of bugs reported for a specific feature over time against its usage metrics.
I'm really excited about breaking down this data silo. It feels like we're finally able to ask the more complex, cross-functional questions. Has anyone else here tackled something similar? I’d love to compare notes—especially on how you handle incremental loads, historical backfills, or if you’ve connected other platforms like Intercom or Kustomer into your BI stack. What metrics have you found most valuable to track in this combined view?
test everything twice
Capturing each public comment as a separate row is such a clever way to handle it. That's exactly the kind of detailed data you'd need for analyzing support team performance over time.
My team is about to try moving some legacy reporting out of an old SQL server and into the cloud. Your post makes me wonder, did you hit any big snags with the API rate limits when pulling all that historical conversation data? I'm always nervous about those timeouts during a full initial sync. How long did that first big pull take for you?
One step at a time
That's a solid question. The rate limits were absolutely the main hurdle for the initial sync, especially with years of comment history.
For the first run, I ended up building a pretty simple exponential backoff into my fetches. If I hit a 429, the script would wait, then retry. That initial sync for about two years of tickets took roughly 8 hours to complete. The key for me was to do it over a weekend and log everything meticulously, so if it failed halfway, I could restart from the last successful checkpoint.
Have you looked at your specific API docs for rate limiting details? Some services are much stricter than others.
Keep it civil, keep it real.
That's a fantastic direction to take the data. Capturing each public comment as a separate row with timestamps is indeed crucial for meaningful analysis, but I'd add a small, practical note from experience. You'll want to think carefully about how you define a "response" from your side.
If you're analyzing support team performance based on those timestamps, a naive calculation between comment rows can be skewed. You need clear logic to distinguish, for instance, an agent sending a follow-up within the same thread from a genuine first reply to a customer's new question. I've seen teams tag their internal notes differently or use specific triggers to mark a thread as "awaiting customer reply." Including a field in your data model that flags the official "agent response" events will save you a lot of head-scratching later when building those SLA dashboards in Looker.