Datafying User Feedback (ft. Spreadsheets Rock!)

Starting Off

Following up on my last post about our journey building an automatic ticket translation system, this time I’d like to share how we moved a massive amount of ticket data into a well-organized spreadsheet. This connects to what I talked about previously, so I highly recommend checking out that previous post first! 😉

Since this process has been refined for just as long as—if not much longer than—the automatic ticket translation, there’s a lot to talk about. I’ll walk you step-by-step through our goals, the process, and the final results.

Is This Really the Best Way?

Back when we were monitoring and handling tickets strictly within Zendesk views, our group ran into a few pain points.

First, it was hard to get a bird's-eye view. To analyze commonalities or group multiple tickets together, we had to go through the clunky process of creating temporary tags and updating every related ticket. (Sounds time-consuming, right..?) Even if we did manage to group similar tickets this way, the view was limited to 10 columns per screen, and the body text only showed the very beginning, meaning we still had the hassle of clicking into each ticket one by one.

Second, these issues made it difficult to quantify data. At DelightRoom, we frequently reference ticket data to gauge the severity of user pain points. The problem arose when we missed an issue during initial monitoring, only to realize it was actually a bug after seeing similar tickets later. Because it was hard to accurately track down those past records, those early tickets never made it into the group. Ultimately, we ended up relying on gut feeling rather than hard data for certain issues, which made it tough to communicate just how much friction a specific problem was causing.

Third, Zendesk's search system was (somewhat) inconvenient. Searching for specific field values was easy, but finding instances where a specific word was mentioned in the body was really hard. Remember in the ticket translation post when I mentioned how tough it was to spot trends when hundreds of pieces of feedback poured in over a single day? Translating them laid the groundwork for spotting trends, but actually searching for words within that text to get the results we wanted was a huge pain.

For example, let's say we're searching for tickets containing the word "battery." In this case, we should also be able to search for related words like "power" or "batteries," but union searches weren't possible. Even if we got a list of tickets containing exactly "battery," we still had to click into each one individually to verify if it was the result we actually wanted.

Faced with this, and being someone who loves organizing data in Excel, I came up with a potential solution: "It would be amazing if we could just see all the ticket contents at a glance in a spreadsheet." I wasn't entirely sure if it was possible, but just like when we built the translation system, I started by experimenting on a small scale. 💪

Meeting the Google Sheets API

Since traditional Excel files are hard to share, I decided to use Google Sheets, which allows multiple people to collaborate and view changes in real-time. I started searching to see if I could manipulate a spreadsheet and paste data using a Python script, and I found out they offer the Google Sheets API, which works perfectly with Python. With my heart pounding, I sent off a very simple API request to insert a value I wanted into a specific cell.

After going through the initial steps of enabling the Sheets API and setting up user authentication, I just stared at my screen as the script magically popped the value right into the cell! Having already dabbled with the Zendesk API during my automated translation attempts, I got really excited thinking, "We might actually be able to automatically move the ticket data we want to see right into a spreadsheet!"

Fleshing Out the Script

01 What Values Should We Fetch?

I mentioned earlier that Zendesk views hold a to~n of information. When I first wrote the script, I only had a broad goal of getting a general overview of the tickets, so I decided to just throw everything in (?) and remove the unnecessary bits later. Over time, as we discussed within the team whether certain values were actually needed, things naturally got cleaned up.

On top of that, we processed the data formats to make them easier to read on the sheet. For instance, we standardized the varying capitalizations of device manufacturer names. We also converted Android OS versions from API levels into the SDK versions we commonly use day-to-day. And we even created a new column for the app's minor versions, which were previously hard to distinguish in the Google Play Store Console or Zendesk. Now, we can easily break down feedback by minor updates. 🎉

02 We Can't Have Duplicate Rows!

Now that we had a solid grasp on what information to pull, I realized that since we monitor tickets daily, we couldn't afford to have duplicate rows in the sheet. In other words, we needed to make sure we were only fetching tickets created since the last update. To do this, I decided to use the ticket's unique ID value as the reference point.

Initially, before pulling the ticket info into the sheet, I sorted them in reverse ID order so that the last ticket ID would sit in the topmost cell. The script would then read that cell's value and only update the sheet with tickets that came after it. But as you might guess, if I forgot to sort the tickets beforehand, the update would go wrong. Whenever that happened, I had to waste time pulling up the sheet's Version History, reverting to the previous state, and running the script all over again. 😞

As an improvement, I set it up to scan the entire ID column and automatically grab the maximum value. Now, instead of me having to sort the sheet every single time before running the script, the smart script figures it out on its own and only updates with the latest tickets.

03 Fetching the Translated Content

The next crucial step was accurately pulling the translated text from multilingual tickets. As I mentioned in the previous post, the location of the translated content varied slightly depending on the ticket type. Since the content translated by our automated script was stored in internal notes, I had to use a completely different API from the one I was already using.

Fortunately, while writing the ticket translation script, I had become somewhat of an expert (?) at parsing language information. So, I grabbed the language values for each incoming ticket channel and ensured the script fetched the correctly translated content. English and Korean kept their original text, while other languages pulled the translated values. Just in case, I created separate 본문 원문 and 번역된 본문 columns so we could still reference the original text if needed.

As a result, we can quickly grasp the context just by skimming down the Translated Body column, and thanks to the fairly accurate translations, searching for specific keywords has become a total breeze. (Hooray for spreadsheet filter functions! 🎉)

04 Data is All About Visualization! The Dashboard

I previously mentioned that one of the downsides of monitoring within Zendesk was how hard it was to quantify ticket data. Since spreadsheets give us the freedom to use pivot tables and create charts much more easily than Zendesk, the path to quantification was f.i.n.a.l.l.y open.

We created pivot tables based on various criteria like ticket type, OS, app version, and content, and generated charts from them. While it might not look as flashy as dedicated charting software, it still lets us change dates and versions to view the data we want at a glance. As a result, we were able to quickly and visually catch onto recent trends—things like, "Oh, bug tickets have spiked lately compared to normal," or "Complaints about this feature are going up." 🥔

Now, if someone asks, "How many tickets are we getting about this issue?", we can answer with much more confidence—providing exact averages and recent trends! We've made it far easier to align the POM group's understanding of user sentiment with the rest of the Delighters.

How My Workday Starts Now...

Once the script stabilized and became part of our regular workflow, my day started with running it.

First, I run the translation script, followed by a script that recognizes specific phrases to attach keyword tags (I haven't explained this one yet!), and finally, the script that moves all that information into the sheet. With this sequence, whether we get 100 or 300 tickets a day, I can grasp the information at a glance and monitor things rapidly. Ticket monitoring that used to take hours can now be knocked out in as little as 20–30 minutes. It’s been a turning point that boosted both our work efficiency and the quality of our output.

Dreaming Bigger and Bolder

I end up modifying and updating the ticket parsing script even more often than the translation script, constantly adding new data points. We're experimenting with different ways to make it more readable and to sort priorities faster. Furthermore, since the results of monitoring tickets tie directly into how we respond to them, we're treating this as a marathon, imagining the big picture from initial monitoring all the way to resolution.

Right now, we're trying out two main things.
First, to streamline the monitoring process, we're recognizing specific expressions (phrases, words, etc.) based on the translated ticket content to automatically categorize issues. This would drastically cut down the human resources still required for manually reading and classifying everything. Then, I'd just need to skim through and double-check that everything was categorized correctly, right? It would also speed up our trend spotting when urgent issues arise. Currently, I've just got a sample script set up for very simple and clear-cut topics, but we plan to expand on it.

Regarding responses, we're working on a way to quickly pull up frequently used explanations. Explaining how to use features inside the app is something we have to do in lots of replies, and rather than typing it out every time, it's better to use one well-crafted format. The goal is to tailor the reply specifically to the user while having solid boilerplate content prepared so we aren't relying on memory in the moment. We're trying to improve response efficiency by speeding up how quickly we can grab these formats. That way, we can quickly send friendly, detailed explanations to every single user!

Wrapping Up

When you hear "Customer Support," what kind of work comes to mind? I know it’s often treated as a simple, repetitive job, but at DelightRoom, we place high value on our POM group, who are at the front lines with our users. Because of that, we're constantly trying various methods to optimize our workflow and draw better insights from our users. If we had just thought, "All we have to do is reply to inquiries!", we would never have dreamed of making all these different attempts.

For the POM group, who constantly thinks about how to creatively satisfy and assist our users, this journey has taught us a lot. It was an attempt to boost the productivity of our team—and ultimately the whole company—by putting the right tools, like scripts, in the right places. I’m really happy with both the process and the results, as it feels like we’ve achieved the outcomes we were hoping for.

Even though I've written two posts back-to-back about automation, there is still so much more to tell you about the POM group's work. I'll be back when I have more interesting topics to share. The POM group connects the users and the product! Please keep cheering us on and watching us grow 😎

Wondering what kind of place this company is where the POM group works so hard? 👉 Learn more about DelightRoom