Omnichannel Customer Support Analysis with Coefficient

Last Modified: August 21, 2023 - 4 min read

Julian Alvarado

Ever felt overwhelmed by the complexity of analyzing disparate customer support channels? You’re not alone.

Many businesses grapple with the daunting task of sifting through vast amounts of data and systems to understand ticket distribution and prioritization.

But what if there was a more efficient way to analyze omnichannel customer support that would simplify the process, save time, and provide valuable insights faster?

In this blog, we’ll demonstrate how to use Coefficient to analyze customer support channels from Zendesk by blending CRM data from HubSpot, amplifying your ability to respond promptly and effectively to customer needs.

You’ll see how you can easily calculate the percentage distribution of ticket volume across different channels, prioritize tickets based on severity and monthly recurring revenue, and more–right within your spreadsheet.

Guide: Analyze Omni Channel Customer Support in Google Sheets

Coefficient is a free Google Sheets add-on that allows you to connect your business systems to Google Sheets and sync real-time data into your spreadsheet.

To install Coefficient, first, open Google Sheets.

Click Extensions in the top ribbon and select ‘Add-ons’ -> ‘Get add-ons.’

install coefficient before you start your omnichannel analysis

Input “Coefficient” in the Google Workspace Marketplace search menu and select the Coefficient app.

type in coefficient in the google workspace marketplace

Click ‘Allow’ to grant Coefficient access to your Google account.  

grant coefficient permission to access you google account

Wait a few moments for the install to complete, then return to your spreadsheet menu.

Click on Extensions -> Coefficient -> Launch.

launch coefficient from the menu

Coefficient will open on the right side of your spreadsheet.

Start by importing your live Zendesk data into Google Sheets.

Click ‘Import from…’

select import from to choose your data source

Scroll down and select Zendesk as your data source.

select zendesk as your data source

Import your data from Zendesk (click here for a full tutorial).

Now that your data is in your spreadsheet, we’ll build a pivot table to calculate the percentage distribution of ticket volume across different channels using Coefficient’s GPT Copilot.

Return to the Coefficient menu, and select ‘GPT Copilot.’

select gpt copilot to begin

Click ‘Pivot Builder.’

select pivot builder from the copilot menu

Select the data range for analysis and describe the pivot you want to build: “for each channel type calculate the percentage of column of each channel type.”

Click ‘Build.’

describe the pivot you want to build

Click ‘Existing sheet’ to insert your pivot table in an empty cell in your current tab.

insert your pivot table into your existing sheet

Click ‘Insert’ and your new pivot table will automatically populate your spreadsheet.

your pivot table you populate your spreadsheet

After creating the pivot table, we enhance the data by importing the monthly recurring revenue from HubSpot.

Next, we’ll create another pivot table to calculate ticket volume by response time and channel, repeating the same process above.

Select the entire table as your data range and describe the new pivot table: “Total tickets by response time and channel.”

build a new pivot table to represent tickets by response time and channel

Insert the new pivot table into the existing sheet in a blank cell beneath your first pivot table.

insert the new pivot into the spreadsheet in an empty cell

Your new table will populate your tab just like before.

your new pivot will populate under the first one

Next, we’ll enrich our analysis by integrating data from HubSpot. This will allow us to understand monthly recurring revenue, which we can then use to prioritize tickets more effectively.

Return to the Coefficient menu -> ‘Import from…’ -> Hubspot.

Coefficient Excel Google Sheets Connectors
314,000 Pros Sync Live Data from Their Business Systems into Spreadsheet

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

import data from hubspot next

Select ‘From Objects & Fields.’

select from objects and fields

Choose ‘Contact List…’ as your object.

select contact list as your object

Select ‘Static list’ from the dropdown and click ‘Next.’

select static list as your object

Choose ‘Contact ID’ and ‘Event Revenue’ as your fields and name your import.

name your import

Click ‘Import.’ 

wait for your data to import from hubspot

Your data will now appear in a separate tab.

your data will appear as a separate tab

Finally, we’ll use the Coefficient GPT Copilot Formula Builder to add the monthly recurring revenue data inline with our Zendesk data.

Return to the Coefficient menu and select ‘GPT Copilot.’

return to the menu and select gpt copilot

Select ‘Formula Builder.’

select the formula builder

Describe the formula you want to build in the text box and click ‘Build.’

describe the formula you want to build

Copy the formula and paste it in the empty cell below MRR.

past the formula into the tab

Create a new column titled ‘Priority.’

Next, use the formula builder to prioritize tickets based on severity and monthly recurring menu.

use the formula builder again

Just as before, copy the output and paste it into the empty cell below ‘Priority.’

paste the formula into the cell

Finally, we will create a filter.

create a filter

This will allow us to sort our omnichannel customer service data in descending order.

sort the filter in descending order

Prefer a visual guide? Check out the full video below!

 

Streamline Zendesk Customer Support Analysis with Coefficient + Google Sheets

Coefficient makes analyzing omnichannel customer support easier than ever before, leveraging Sheets and GPT to help you gain valuable insights into your customer support channels to improve your customer service.

Ready to get started? Install Coefficient for free today.

Exported with Wordable

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Julian Alvarado Content Marketing
Julian is a dynamic B2B marketer with 8+ years of experience creating full-funnel marketing journeys, leveraging an analytical background in biological sciences to examine customer needs.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies