25.12.2019

Google Sheets and Call Center Reports

How to Bridge Google Sheets and Call Center Reports in Oki-Toki, How to Extract Necessary Data from Oki-Toki Reports.

Google Sheets and Call Center Reports

In our call center reports “Call Log”, “Phone List”, “Task List” and “IVR report”, the feature to download additional data (about the contact, order, etc.) was added

 

By checking this checkbox, the user-transmitted additional parameters of the task will be downloaded into a separate column”Data”.

The next task facing the user is how to extract the necessary information from this report column.

So, you have entered the contact data and after the call made an export to the Google sheet file, below you will find several cases that can be solved with the help of Google sheet.

Case 1. Filtering, searching and replacing data (for beginners)

Firstly, you can create a copy from a sample through the File – Create copy menu.¹¹We can filter any data in the reports by any criteria, using the Filter tool for this.¹¹With the Filter tool, you can quickly sift out unnecessary information, leaving only what you need.¹¹For example: some contact center reports have special characters, such as “*” (asterisk). It represents in our system the party that ended the conversation, this information is not needed for the reports and it would, of course, be better to remove it.¹¹For this, we use the Find and Replace tool. Press Ctrl+H (the tool menu will open), enter the value you need to find, in our case the symbol (*).¹¹Leave the “Replace with” field empty if you want to delete this symbol, if you need to replace it with something else, this is the place to specify.¹¹After, select where to find the value, additional parameters (tick checkbox), click “Find” and then “Replace all” and “Done”. This method can be used to clean not only reports, but for example a database before importing, if our numbers are stored in the “somehow” format (for example +3 8 (000) 111-22-33).¹¹Important: If you have a lot of garbage in your data, apply “Find and Replace” several times. For the phone number remove spaces, an opening parenthesis, a closing parenthesis, a hyphen and a plus sign. Total: 5 times. Important: If something didn’t work out – you can undo the changes. Great, we got rid of the unnecessary and the report became more organized, and we can also quickly sift through the necessary information using filters! For most cases this may be enough, but let’s consider the next case. All the steps are shown in the screenshots: [smartslider3 slider=”17″]

Case 2. Advanced Search

What if we need to find and filter all the customer numbers related to a specific region or communication agent, for example [phone number starting with 380] into separate call center reports?

For this task, we can utilize the search function (Ctrl+F) and start inputting the region’s number, and the browser will present all matching numbers. This is fine, but we won’t be able to collect these lines into a separate report. Therefore, we shall be employing a tool known as the Slicer.

In the Data menu, select Slicer. A settings panel would appear on the right, where we will choose the column with the phone numbers (Side B, for instance).

Furthermore, within the desired Slicer settings, we’ll sift through the necessary data. We’ll click on the filter, where we’ll have two filtering options: “by value” or “by condition”. What interests us is “by value”.

Before we start searching, we’ll click “Clear” to remove highlighted contacts. In the search bar, we’ll insert the first 4-5 digits of the number, where the country prefix and agent or city code are located. We’ll then choose “Select All” and click on “OK”.

In case you have several prefixes, you’ll open the filter again after your first selection and add a new prefix, and then again select “Select All” and “OK”. This way we’ll generate a report for specific agent/regional numbers.

All the steps are depicted in the screenshots below:

[smartslider3 slider=”18″]

Case 3. Searching for additional data in the “Data” field using regular expressions

We’ve already mentioned the capability of importing and exporting supplementary data into contact center reports. They are all exported to a separate field – “Data”, but since all of the data are in one column, you need to extract the required data beforehand.

So, let’s say we transmitted the “Customer Name” and “Lead ID” in addition to the number, let’s look at examples of how to get this information from the report.

For this, we use formulas. The formula for working with regular expressions will come to our aid to find the Name_Surname in the string: .=REGEXEXTRACT(M3; “[а-яА-Я_ІЇії]+”). In its first part, we specify where to search – the data cell (M3), and in the second – the conditions for search [а-яА-Я_ІЇії]+ . In this way, we search for all letters of the Ukrainian and Russian alphabet, (in upper and lower case), as well as special characters which are used for separation.

We create a formula in the adjacent column and drag it to the end of the report. We get the client’s name in a separate column as a result.

We also need to do the same to search for our client’s “lead ID”. The formula would look like this: =REGEXEXTRACT(M3; “[0-9]+”) or =REGEXEXTRACT(M3; “leadid=[0-9]+”). Similarly, we specify the cell and search parameters – all numbers and, if necessary, special characters contained in your lead ID. We end up with a separate column with the necessary information.information (don’t forget to drag the formula across the entire range).

for = REGEXEXTRACT(M3; “leadid=[0-9]+”) will look as in the last screenshot.

All steps are shown in the screenshots:

 

 

 

 

 

Conclusion

The reviewed cases 1 and 2 are known to most users who frequently work with tables, and case 3 can already seem something from the field of programming (which is not far from the truth, as regular expressions are used in different programming languages, often for searching for information under certain conditions).

Google sheet is a versatile tool and its correct usage can reduce the time spent on data processing, but incorrect usage can spend a lot more time to create call center reports with numerous inaccuracies. Hence, study and use the right strategies. 🙂

It is possible to import tasks for dialer in Oki-Toki in a simplified manner – only numbers, but for serious projects this is not enough: numbers with information about the order, client, and the state of the business process.

Additional information about Call Center reports can be found here.

Currently, there are several import tools: through the new interface or API.

Rate the news:

Read also

photo
Friday July 21st, 2023 Everything you need to know about SIP numbers: How to buy and where to use

How to choose sip telephony? If you are looking for a sip provider for the first time, then this article will take you through the most popular providers on the market.

Learn More
photo
Sunday May 5th, 2019 Oki-Toki News Digest for April 2019

Oki-Toki Updates for April 2019: Import Optimization, Address Book in the New Agent Workspace, Blacklist for Outgoing Calls, Integration with AMOCRM.

Learn More