DISTILL YOUR DATA WITH PIVOT TABLES - VERSATURE'S GOOGLE SHEETS™ ADD-ON

Read time: 3 min 34 s. Unsure of how pivot tables can be useful? Check out one of Versature's use cases!

Versature's Google Sheets™ add-on is a tool that allows you to download your call data into a spreadsheet quickly.  Learn more about the different sheets you can create in the Versature Google Sheets™ Add-On - The Basics article. 

Having all this data at your fingertips within minutes is excellent, approaching a giant data dump can be overwhelming and time-consuming to stifle through. 

There are so many figures you can derive from the "Company CDR's" call data spreadsheet. Every business has different goals and uses for the information you can extrapolate from this report. To give you an idea of how to refine your Company's CDRs data-dump this article will show you how we, at Versature, use our add-on.

Only Office Managers can create Company CDR's. All users can create personal CDR's.  


Caution!

If you create formulas or filters directly to a sheet that has been generated by the Versature add-on, your changes will NOT be saved if the sheet is refreshed.



PIVOT TABLES 

Pivot tables are one of the best tools to use when analysing spreadsheet data. 

  • They allow you to summarize data quickly. 
  • They provide easy refreshes for break-down summaries.
  • They require less clicking and user input than formulas. 
  • They are more accurate than filters. 

Tip!

For a quick tutorial on how to create a pivot table in Google Sheets™ visit their support documentation at https://support.google.com/docs/answer/1272900?co=GENIE.Platform%3DDesktop&hl=en.



VERSATURE USE CASE 

There are two main pivot tables we use daily to track productivity and client care. 

1. Outbound calls + count + talk time 

2. Inbound calls + count + talk time

When creating pivot tables, it's helpful to think of your pivot table through rows OR columns, but not both. 

In this example, we are reading the tables through rows; even though it is the columns that are titled—the table's data is dependant on the users' extensions. Although the title categories are displayed as a row, i.e., "To User," you must read the data horizontally, through the column.



WHAT WE WANT TO KNOW  

After setting a predetermined period of time, our tables are going to offer the following: 

  • Amount of calls made (outbound), in total, and by each agent.
  • Amount of calls received (inbound), in total, and by each agent.
  • Talk time, in total, and by each agent. 
  • Percent of work hours spent on calls by each agent. 

This information will allow for insights into agent productivity.

While the numbers don't necessarily tell the whole story, they can be monitored for trends and create concrete stepping stones for meaningful questions.

Some examples,

  • An agent taking most of the inbound calls, but has the least amount of talk time; this agent could be resolving cases faster, or they could be elevating more calls and handing off cases more readily than other agents.   
  • An agent having significantly lower talk times and call counts; this could be an opportunity to review the agent's behaviour or determine if additional training is required. 


HOW WE GOT THERE 

You must create separate inbound and outbound pivot tables when looking at users extensions.

OUTBOUND PIVOT TABLE 

Our pivot table will include outbound calls + count of start time + duration of the call.

When filtering data based on specific users or extensions, it is recommended to use the "To User" and "From User" rows instead of "To" and "From" because the "To" and "From" values sometimes show the sip domain and sometimes show the caller ID number

Follow these instructions after you have generated a Company CDR sheet with a predetermined time period. 

 


1. ADD ROWS AND VALUES 

  Rows = From User + Type             Values = Start Time + Duration 

Tip!

Unchecking "Show totals" creates an easier to read table.

After the data is defined you will see a nonformatted table that looks like the image below. 

In the "To User" and "Type" columns, there are still data included from all users in the company. To refine your pivot table, you must use filters found in the pivot table editor. 

2. ADD "FROM USER" AND "TYPE" FILTERS

Scroll down to the bottom of the pivot table editor and add a "From User" filter. Once added you can select the users you want to include in the table. 

For this table we only want to see outbound calls, so we also add a "Type" filter. 

Once that is done our table our reformatted table will look like: 

 

Now our outbound call table is done, we'll repeat the same steps to create the inbound pivot table. 

Tips!

To change the style of the table, highlight your table click the Fill Color icon and then Alternating colors to display default styles and options to make custom styles. 

Learn how to change the duration from seconds to minutes.



INBOUND PIVOT TABLE 

Use the same process for the Inbound Pivot Table as you did for the Outbound Pivot Table, only with "To User" for the row and filter so the table shows incoming calls to specified extensions. 

The inbound pivot table will use "To User" rather from "From User" to show calls that extension answered. 

Remember to add a "To User" filter rather than a "From User" at the bottom of your editor.



Now both our pivot tables have been created; we can take it to the next level. Learn how to create an Impact Table with some basic formulas. 


Was this article helpful?

Can’t find what you’re looking for?

Our award-winning customer support team is here for you.

Contact Support