net2phone Canada'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 net2phone Canada 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 net2phone Canada, 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 net2phone Canada 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 analyzing spreadsheet data.
- Allow you to summarize data quickly.
- Provide easy refreshes for break-down summaries.
- Require less clicking and user input than formulas.
- Generally provides more accurate information than solely using 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.
EXAMPLE USE CASE
Call Data Records are typically distilled into two main pivot tables, used to track the daily productivity of our client care teams.
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 dependent on the users' extensions. Although the title categories are displayed as columns, i.e., "To User," you must read the data horizontally through the rows, viewing the user with the extension number 116 with a SUM Duration of 125698 minutes.
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 user 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.
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.
