Call center reports such as “Call Log”, “Phone List”, “Task List” and “IVR Report” now include the option to export additional data (about the contact, order, etc.)
When this checkbox is selected, the extra task parameters passed by the user will be exported into a separate “Data” column.
The next task the user faces is to extract the needed information from this report column.
So, you have entered the contact data and after the call campaign, you exported it to a Google Sheet file; below you will find several cases that can be solved using Google Sheets tools.
Case 1. Data filtering, searching, and replacement (for beginners)
First, you can create a copy of the sample through the File – Make a copy menu.
Any data in reports can be filtered by any criterion using the Filter tool.
The Filter tool allows you to quickly remove unnecessary information, leaving only what you need.
For example: some call-center reports include special characters such as “*” (asterisk). It shows which side ended the conversation in our system, but this information is not needed for reports and, of course, it is 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 where you specify it.
Then choose where to search for the value, additional parameters (check the boxes), click “Find” and then “Replace all” and “Done”. This way you can clean not only reports but also, for example, a database before import if your numbers are stored in a “messy” format (for example, +3 8 (000) 111-22-33).
Important: If you have a lot of junk in the data, use “Find and replace” several times. For a phone number, you need to remove spaces, the opening parenthesis, the closing parenthesis, the hyphen, and the + sign. In total, 5 times.
Important: If something went wrong, you can undo the changes.
Great, we got rid of the unnecessary parts, and the report became more organized. We can also quickly filter the necessary information using filters! For most cases, this may be enough, but let’s look at the next case.
Case 2. Advanced search
What if we need to find and filter all customer numbers that belong to a specific region/mobile network operator (for example, 380xxxxxxxxx) into separate call center reports?
For this, we can use search (Ctrl+F) and start typing the region code, and the browser will show all matching numbers. This is good, but it won’t let us highlight these rows into a separate report.
We will use a tool such as Slicer.
In the Data menu, choose Slicer. The settings panel will appear on the right, where we select the phone number column (for example, Side B).
Next, in the settings of the needed Slicer, we will search for the required data.
Click the filter. There are two filter options to choose from: “by value” or “by condition”.
We are interested in “by value”. Before searching, click “Clear” to remove the selected contacts. In the search field, enter the first 4-5 digits of the number that contain the country prefix and the operator/city code. Then choose “Select all” and click “OK”.
If you have several prefixes, after the first selection, open the filter again and add a new prefix, then again “Select all” and “OK”. You will get a report for specific operator numbers/regions.
Case 3. Searching for additional data in the “Data” field using regular expressions
Above, we already mentioned the possibility of importing and exporting extra data in call-center reports. All of it is exported into a separate field — “Data.” However, since all the data is in one column, the needed information must be extracted first.
So, let’s say we passed not only the number, but also the “Client Name” and “lead ID”; let’s look at examples of how to extract this information from the report.
For this, we use formulas. To search for Name_Surname in a string, a formula for working with regular expressions will help us: .=REGEXEXTRACT(M3; “[a-zA-Z_ІЇії]+”). In the first part, we specify where to search — the cell with the data (M3). In the second, we specify the search conditions [a-zA-Z_ІЇії]+. In this way, we search for all letters of the Ukrainian and Russian alphabets (in uppercase and lowercase), as well as special symbols used as separators.
Create the formula in the adjacent column and drag it to the end of the report. As a result, you will get the client name in a separate column.
We also need to do this to search for the client’s “lead ID”. The formula will look like this: =REGEXEXTRACT(M3; “[0-9]+”) or =REGEXEXTRACT(M3; “leadid=[0-9]+”). Similarly, specify the cell and the search parameters — all digits and, if needed, any special symbols contained in your lead ID. As a result, you will get a separate column with the required information (don’t forget that the formula must be dragged across the entire range).
The formula =REGEXEXTRACT(M3; “leadid=[0-9]+”) will look like the last screenshot.
All steps are shown in the screenshots:
Conclusion
Cases 1 and 2 discussed are familiar to most users who often work with spreadsheets, while Case 3 may already seem like something from the field of programming (which is not far from the truth, since regular expressions are used in various programming languages, often to search for information under specific conditions).
Google Sheets is a versatile tool. Using it correctly can reduce the time spent processing data, while using it incorrectly can waste much more time trying to create call center reports with many inaccuracies. So, learn it and use the right approaches. 🙂
Importing tasks for otomatik arama into Oki-Toki can be simplified — only numbers. However, for serious projects this is not enough: numbers with information about the order, the client, and the status of the business process are needed.
Read more about call center Reports here.
There are currently several import tools: via the new interface or API.
