intro
Enormous improvements have been made in availability of data this year by 3CX. But this data can be complex to work with for the normal user. Data historically was also made available in csv file format.
For this csv-file data-sharing approach we have made 2 simple to use templates: A free template exists for both Google Sheets and MS Excel.
Get insights into your data with csv-to-report analytics
The two templates have been designed to process the segmented call log, which provides the most information.
This file is in csv format and can be received via email or provided for
by your 3CX administrator upon request.
The templates convert data in an automatic way and present a number of metrics and some charts,
as well as a list of the calls, aggregated to one line per call, that can be filtered.
For the normal user/business a such spreadsheet can contain months worth of data, depending on the volume of calls.
The user would import and add information, up to a point when the data-rowcount is too high.
And start a new file. This helps to avoid having acces to complex databases, cloud solutions and alike.
Keeping it simple.
How to work with the csv file data
Prepare your data:
- Take one csv file, or
- Assemble your csv data by regrouping multiple files into one, and
- Make sure there are no duplicates
- Leave a first line with the headings
- Remove empty lines and remove lines starting with Totals
- Remove also the columns L -Summary- and M -Transcription-
as these are not analized
Excel-csv-converter
Features:
- Date DropDowns tab to filter data
perform a -Refresh All- to update the Pivot Tables. - Detailed technical information on the Report-Data sheet
View=>Hidden Views => Select Report-Data
Colums, the driving Pivot tables, metrics and graphs are detailed
download the Excel template file
Import your data and set the 3 slicers to the right to the value of ONE (1) so all pivot tables will correctly aggregate the data
After Each Action (importing data or changing the date filter), perform a -Refresh All-
The file output update is automatic with the -Refresh All- action
The dropdown list references a list constructed on the Helpers Tab (as there is a helper for the Details tab)
There are special tables for each of the top 10 lists (scoring of equal values is by aplhabetical order)
This file was built with Excel: version 16.99
Google-Sheets-csv-Converter
Duplicate our template and disguard the original.
Set up your copy in your own Drive environment and start working with its duplicate.
Should someting goes wrong start anew with a fresh copy.
Follow this link to the Google template
Principal Google Sheets techniques:
- named ranges
The Data-processing sheet range transformedData contains the row-processing formulas of the input data in, which point to a column range so it does not have to be changed a lot - =QUERY(range,"SELECT ..")
To Select, Aggregate and Sort data ready for use in charts, decomposed into seperate select statements and a centralised setting of the filter - copy/paste row formulas
Unaltered source data is correctly processed row by row by a selection of formulas that can copied downward beyond the current range...