2012-1Q: CDR Rating Tool

A feature that enables operator to rate voice CDR files has been added to the system. It enables operator to rate & import CDR's to the SIMPLer system and create a rich invoices that contain various drill downs of calls made.

Note: Sample final invoice is attached to this page

Feature Pre-Setup:

Feature requires two custom fields to be added to the system:

• • 1. Voice Identifier - this custom field is be used by the system to match CDR rows against accounts in SIMPLer. In majority of cases this will be a source number for the call, but on some rare occasions it might be another unique to the customer call attribute such as trunk identifier etc.

    2. Voice Package - this custom field is used to implement voice 'packages' in the system. Operator may use different billing schemes (i.e. business rates or home rates) depending on the customer type for example. This field is used by SIMPLer to choose the appropriate rules to process the CDR. If left blank all rules will be matched and available for use.

This can be done from 'settings'->'Custom Fields' page in SIMPLer.

In the Custom Customer Fields define the Voice Identifier field and Voice Package field as shown on the screenshot below. The labels must be Voice Identifier and Voice Package. The remaining settings would be up to the operator however Azotel would recommend the setup to be as similar as possible with the one presented on screenshot.

Feature Overview

The CDR Rating Tool enables processing and importing CDR records from external voice systems and importing them into SIMPLer not only in a format where the summary rated amount is given, but it also allows to present a call details section on operator invoices. The ability of presenting a flexible and fully customizable call details section on the end invoice is what makes the system unique and very powerful.

Input File Format

CDR Rating Tool works with CSV (comma separated values) files, though it supports other than comma delimiters. The CSV file must have columns in following order:

• 1. Date/Time - Date/Time of the call - This value will not be used for processing, however it will be available for the call details section on the invoice

  2. Voice Identifier - (Mandatory Field) - The Voice Identifier field will be used to match the call record against an actual customer record in SIMPLer. Customers in SIMPLer must be assigned a Voice Identifier (or set of space separated voice identifiers) to take part in thr CDR ratting process

  3. Origination - (Mandatory Field) Origination number of the call - a regular expression check can be set against the field

  4. Destination - (Mandatory Field) Destination number of the call - a regular expression check can be set against the field

  5. Region - Region of the call - some operator may provide a call region, which is a name for the destination (i.e. Italy or Poland) - a regular expression check can be set against the field

  6. Duration - (Mandatory Field) Duration of the call - it will be available to use in Rating formulas

  7. Prerated Amount -Prerated amount - in some cases the CDR's will come rated - operator can choose to re-rate them or to keep the previous rates

Example CDR File

01-lis-11 8:34;467160000;467160000;0048696940200;Poland - Mobile;2;0,034

01-lis-11 8:44;467160011;467160011;0044922974535;England - Mobile;1;0,09

01-lis-11 8:45;467160000;467160000;33482849;Local Telco;3;0,051

01-lis-11 8:46;467160099;467160099;17214433;Local Telco;1;0,0095

01-lis-11 8:47;467160099;467160099;0048328376283;Poland - Landline;1;0,0095

Customer Account Setup

To take part in CDR rating process customer account in SIMPLer must be set with Voice Identifier attribute. In majority of cases it will be an Origination number of the call, but is some cases it might be something else in the CDR records that is unique to the customer voice connection (i.e. trunk code). The Voice Identifier can be set under the 'Custom Fields' section of modify customer details page.

Voice Package is an optional field that can be used to select only a set of rates that have the Package field specified and equal to this field. In another words it enables operator to create multiple voice packages that will have separate sets of CDR processing rules.

Note: Customer also needs to have an Import based VoIP product added to his recurring subscriptions. Refer to screenshot below for a rough guide on how such a product should be setup. Once such a product is in the system remember to allocate it to each customer that should be billed for the Voice usage.

CDR Processing Rules setup

Before starting to process and rate the CDR's a set of processing rules must be setup. These rules will define all conditions required to process a single CDR entry. Click on 'Voip' tab followed by 'CDR Rating Engine' and 'Manage Processing Rules' links to access the management page.

The CDR processing rules are heavily based on concepts presented below - Azotel strongly suggests to scan through the documentation pages for these before implementing complex formulas:

• 1. the regular expressions concept - provides a flexible means for "matching" various strings of text, such as words, patterns of characters. For more information and detailed examples please refer to: http://en.wikipedia.org/wiki/Regular_expression

  2. perl math - mathematical operators available in Perl. These will be used to create calculation formulas.

Following arithmetic operators are available:

Conditional execution can be defined using following statement:

(( CONDITION ) ? EXPRESSION_TO BE _EXECUTED_IF_MATCHED : EXPRESSION_TO BE _EXECUTED_IF_NOT_MATCHED )

Following logical operators are available:

In CDR Processing formulas operator can use following tokens that will be exchanged with the live content from CDR's:

• • %%DURATION%% - Duration of call as in CDR

  • %%AMOUNT%% - Prerated amount from CDR

Complex examples:

• • %%AMOUNT%% * 1.3 - In this case the rated amount will be equal to the prerated amount multiplied by 1.3

  • %%DURATION%% * 2.8 - In this case the rated amount will be equal to the duration of call multiplied by 2.8

  • ((( %%DURATION%% % 2) > 0) ? int( %%DURATION / 2 ) + 1 : int( %%DURATION / 2 ) ) * 0.2 - In this case the rated amount will be equal to the duration rounded up to units of two multiplied by 0.2

Each of the rules consists of following attributes:

• 1. Source Regexp - CDR records with source number matching this regular expression (and the two remaining regular expression rules) will be processed accordingly to the current processing rule. If left blank - system will not process it and automatically assume it is a source match

  2. Destination Regexp - CDR records with destination number matching this regular expression (and the two remaining regular expression rules) will be processed accordingly to the current processing rule. If left blank - system will not process it match and automatically assume it is a destination match

  3. Region Regexp - CDR records with region number matching this regular expression (and the two remaining regular expression rules) will be processed accordingly to the current processing rule. If left blank - system will not process it and automatically assume it is a region match

  4. Label - defines a processed CDR group - that in turn will be used by the templating system to generate Call Details summary stats for all rates that have the same label. The typical use would be to define a summary call region (i.e. 'National - Landlines' or 'International - Ireland') that can be used in the Call Details for summary details of all calls made to that region

  5. Package - defines a complete set of rates. The CDR record will be processed accordingly to the customers Voice Package attribute set under customers account. Only CDR processing rules where Package attribute matches customers Voice Package field will be used to process the particular CDR record. Typical use would be to provide different rating schemes to customers depending on the deal operator has with them

  6. Formula - Perl Eval based formula that will be used to calculate the rate for the call

  7. Units Formula - Perl Eval based formula that will be used to calculate the units for the call

  8. Detailed - this flag defines an additional attribute with which a rated CDR record will be labeled. It has a use than on the Voice Details template where it can be used to produce 'detailed' CDR output for labeled records while leaving out ones that don't have that flag. Typical use would be to provide detailed output for 'International' calls while providing only a summary voice call details for 'national' calls.

The management pages enable operator to browse, search, view details, add and edit CDR processing rules in a simple manner.

Test Processing Rules tool

Simpler provides a 'TestProcessing Rules' tool where operator can check the rating output when processing predefined CDR details. Typical use would be to test the CDR Processing Rules while defining them

CDR Rating tool

The CDR rating tool is an interface to a rating engine that helps operator process their CDR files and prepare them to upload figures into the SIMPLer billing system. It takes the input CSV file in the format described previously in this guide and rates each of it's rows. Rows are first matched against a customer account in SIMPLer using the Voice Identifier column of the CSV file. Next if a customer account matching the CDR was found system will search for a CDR processing rule that will match the CDR. If multiple rules match the longest, best matching rules will take precedence. The uploaded CSV file may come in different formats, hence there is a choice of 'Column Delimiter' used in a file. Also a number of 'Fraction Digits' can be set prior to processing - this will be used to set the fraction digits to the recommended value when rounding as in some countries it is required to have 3 or more fraction digits in the result. The 'CDR Rating tool' has three output modes:

• • HTML - output used for debugging - it will display a summary processing page with two sections: Processing Errors and Rated Output where operator can debug the errors and preview the total amounts rated (that will be available in the import subscriptions) along with the 'HTML' templated output that can be used on the customer invoices as an addition to them. In another words if the templating is set properly - customer's invoice will contain the HTML output. Templated system is described in more detail further in this guide.

  • CSV Spreadsheet - this is SIMPLer's 'Import External Billing Records' tool compatible CSV output. It can be imported to SIMPLer directly without any changes required. Contains three columns: Invoicing ID, Total Amount, HTML (templated) output

  • XLS Spreadsheet - XLS output of rated content containing three columns: Invoicing ID, Total Amount, HTML (templated) output

Templating System

The templating system for voice details uses a template to generate detailed voice call details as an HTML output that will be used as 'HTML code' option in the 'Import External Billing Records' tool. The 'HTML code' option allows importing some additional HTML code along with the total amount for each imported value. This HTML Code can than be used on the invoice template by adding a <TMPL_VAR NAME=ADDITIONAL_HTML> token to the invoices template code. The Voice Details template is similar to all other templates in the system and can be managed from 'Settings' -> 'Invoice Voice Details' section in SIMPLer.

All the basic examples of use are shown in the default.html example that can be downloaded from the management page. Do get back to Azotel if the examples found there are not sufficient to create your own voice template.

The default template gives examples for a full set of call drill down options operator can choose from to create his own voice details template. Please find the Example output from default template at the end of this guide

Typical Rating Flow

Typical CDR rating process after the SIMPLer system has been setup would take following steps:

1. Prepare the CSV file. Make sure it contains the right columns in correct order. It may require re-ordering the columns in the spreadsheet got from voice vendor. Final format should be similar to example below.

01-lis-11 8:34;467160000;467160000;0048696940200;Poland - Mobile;2;0,034

01-lis-11 8:44;467160011;467160011;0044922974535;England - Mobile;1;0,09

2. Test run the file to limit the number of processing errors. Go to 'VoIP' tab in SIMPLer, than click on 'CDR Rating Engine' position in the menu. Select the CSV file you want to precess, set the 'Delimiter' and 'Fraction Digits' attributes if different than default and finally hit the 'Calculate VoIP Usage' button with the 'HTML' set as the output option.

3. Create CSV file. Go back to the start 'CDR Rating Engine' screen and choose 'CSV Spreadsheet' as output method. Click on 'Calculate VoIP Usage' button and save the file on hard drive.

4. Import the Rated CSV file to the billing system. Go 'Invoices' tab and click on 'Import External Billing Records'

5. Generate recurring invoices. Generate recurring invoices at a date they are usually generated. In some cases this might be straight after the import, other operators would prefer to run the invoicing at a set day of the month.

Note: Sample invoice is attached to this page

Example Output from Default Voice template file

The example output below should give a good overview of what option are available for customizing the Voice Details template. The example starts with an actual use example followed by all drill down options presented one by one.

Presented Above was an example of use.

Below find other Call Drill Down options:

Summary Details

Invoicing ID: VBT1

Units: 17

Amount: 6.20

Time: 23

Calls: 5

Units for Detailed: 9

Amount for Detailed: 3.80

Time for Detailed: 19

Calls for Detailed: 3

Units for Not Detailed: 8

Amount for Not Detailed: 2.40

Time for Not Detailed: 4

Calls for Not Detailed: 2

ALL CDR's

Per Label Drilldowns

Summary Details for Label: International - Poland

Invoicing ID: VBT1

Units: 9

Amount: 3.80

Time: 19

Calls: 3

Units for Detailed: 9

Amount for Detailed: 3.80

Time for Detailed: 19

Calls for Detailed: 3

Units for Not Detailed: 8

Amount for Not Detailed: 0.00

Time for Not Detailed: 4

Calls for Not Detailed: 2

ALL CDR's

Summary Details for Label: Local

Invoicing ID: VBT1

Units: 8

Amount: 2.40

Time: 4

Calls: 2

Units for Detailed: 9

Amount for Detailed: 0.00

Time for Detailed: 19

Calls for Detailed: 3

Units for Not Detailed: 8

Amount for Not Detailed: 2.40

Time for Not Detailed: 4

Calls for Not Detailed: 2

ALL CDR's

Summary Details

Number: 69694000

Label: Local

Invoicing ID: VBT1

Units: 8

Amount: 2.40

Time: 4

Calls: 2

Units for Detailed: 9

Amount for Detailed: 0.00

Time for Detailed: 19

Calls for Detailed: 3

Units for Not Detailed: 8

Amount for Not Detailed: 2.40

Time for Not Detailed: 4

Calls for Not Detailed: 2

ALL CDR's