Rating: Logic

AG Projects RTC Platforms (Accounting Guides)

This document describes the rating logic for a CDR

CDRTool provides on-the-fly rating of CDRs generated by the platform.

A Call Detail Record (CDR) is one record from the radius radacct table. The CDR contains all information related to a session, its duration, the calling and called party and media information. The rating engine calculates the price of the session. The calculation is done once and its results are saved in the CDR table for later use. It is possible to re-calculated the prices at a later time for example when having to change the tariffs.

Based on exceptions, different rates may be applied per:

1. Caller party (SIP account)
2. Caller domain
3. Source IP address

Rating is applied only after the call has ended. CDRTool considers that a call has ended when there is a stop time. In case of missing BYEs, CDRTool relies on the fact that MediaProxy will update the CDR with the proper stop time information based on the last moment the media stream passed through the media proxy.

The rates are linked with profiles corresponding with different time of the day, day of the week or holidays. For rating calls, which span multiple profiles, the right rate is selected and applied for the call duration within each profile. Each customer may be assigned its own dedicated rating plans destination id and names. Chained profiles are possible to enable exception based rating. Multiple customers may share a common rate list, while some destinations may be rated differently, only the differences must be provisioned. Multiple time zones are supported for multiple billing parties hosted on the same platform.

Different customers can have different rating plans. A rating plan is a unique combination of holidays, day of week, time of day, destination ids, and associated costs.

The following steps are performed to rate a CDR:

1. Determination of the billing party
2. Determination of the destination id
3. Determination of the costs

The steps are described in detail below.

Determination of the billing party

To be able to calculate the Price for a call the rating engine must determine whose rating plan to use.

The rating engine does this by performing a match against entries in the billing_customers table for the the BillingPartyId field of the the CDR (radacct.UserName for radius based datasources) in the following order:

1. SIP account user@domain
2. SIP domain of the SIP account
3. Source IP of the session
4. Default (when none of the above matches)

The first match is considered to be the billing party for which the rating plan is determined.

The rating plan is further derived from the profiles associated with the entry found in the billing_customers.

Determination of the destination id

The rating engine identifies the destination id, which has associated rates depending on day of week and time of day.

The destination id is for example a country prefix like '31' for the Netherlands. This destination id has prices associated with it so all calls to the Netherlands will have prices associated with prefix '31'.

The destination id is derived from the logical destination the SIP session has been routed to. In SIP headers and Radius records there are multiple places that contain information related to the destination. Some of them are generated by the SIP User-Agents (hence cannot be trusted and must not be used for accounting purposes) and others are generated by the SIP Proxy configured by the operator and are suitable for accounting purposes.

The rating engine considers the destination to be the first non-empty CDR field in this order:

1. CanonicalURI (the destination after all lookups inside the SIP Proxy)
2. SipTranslatedRequestURI (the Request URI as presented by the SIP UA)
3. CalledStationId (the content of the To header, used as a last resort)

The CanonicalURI is the preferred because is reliable information generated by the operator based on the logic configured in the SIP Proxy and the subscriber cannot control it. OpenSIPS must be configured to send the CanonicalURI Radius attribute when creating the radius accounting START record. Instructions for this are available on the CDRTool site.

The destination id is then calculated based on the longest match of this destination field in the billing_destinations table.

The longest match for the chosen destination field is performed by the E164_class, which by default uses E164_Europe that defines an European numbering plan. It assumes the destinations start with a zero for a national call and with double zero for an international call. See cdr_generic.php for the actual logic.

If your dialing plan is different, you must use other provided class like E164_US or create a custom class and point to it in global.inc for each datasource as follows:

'E164_class'=>'MyE164Class',

For example the pre-defined E164_US class from cdr_generic.php matches the
American dialing plan.

Determination of the costs

The following steps are taken to determine the cost for the calls based on the destination id and the billing party determined at the previous steps.

1. Lookup the billing profiles in cdrtool.billing_customers table in the following order: subscriber,domain,gateway (based on $this->dayofweek), as explained in "Determination of the billing party" above:
   • profile_name1 matches week days [1-5]
   • profile_name1_alt matches week days [1-5] if no rates for profile_name1 are found
   • profile_name2 matches week-ends [6-0]
   • profile_name2_alt matches week-ends [6-0] if no rates for profile_name2 are found
   • profile_name2 matches also holidays from billing_holidays table The week starts with 0 (Sunday) and ends with 6 (Saturday). This step determines which rates should be applied based on the day of the week when the call started.
2. Using the profile_name found, lookup the rate_name based on $this->hourofday in cdrtool.billing_profiles table. If no rate_name is found for the given profiles a second set of profiles are used, profile_name1_alt and profile_name2_alt.
   • the day may be split in maximum 4 periods
   • the days starts with hour 0 and ends with hour 24
   • rate_name1 defines the first interval after hour 0
   • rate_name2 defines the first interval after rate_name1
   • rate_name3 defines the first interval after rate_name2
   • rate_name4 defines the first interval after rate_name3 When the hour matches an interval use the rate_nameX found to lookup the rate in billing_rates, if no record is found use the rate called default. This step determines which rate should be applied for the time of day when the call started.
3. Lookup in the cdrtool.billing_rates table the record having same name found at point 2 having billing_rates.destination = destination id and billing_rates.application = application type found in the steps above.
   • return an array with all the rating values and the duration rated. No rate will be returned if no destination id is found. Make sure each possible destination has a 'corresponding id' and name in the destinations table. This step determines the costs within the current time span associated with the time of day and destination id. If the call duration exceeds this time span (that is a new interval for which another rate applies is reached), step 4 is performed.
4. If the duration rated at point 3 is less than total call duration, apply point 3 again for the remaining call duration in the next profile. A maximum of 10 spans (different rates depending of time of day, day of the week) can be calculated using this mechanism. After 10 spans, the engine bails out to avoid loops caused by invalid tables provisioning.
5. Calculate the total call Price based on its duration and connection fees.
   
   In global.inc there are several variables that affect how the price is calculated. These settings are global per CDRTool installation but some can be overwritten with per customer values in the billing_customers table.

§$RatingEngine = array(
    "priceDenominator"        => 10000, // Rates units (global setting)
    "priceDecimalDigits"      => 4,     // Decimal information (global setting)
    "minimumDurationCharged"  => 0,     // Rate a minimum of X seconds (per customer)
    "minimumDuration"         => 0,     // Minimum duration to rate, if call duration is shorter the price is zero (per customer)
    "durationPeriodRated"     => 60     // Rate is per 60 seconds (global setting)
    "trafficSizeRated"        => 1024,  // Default we rate per 1 MB (global setting)
    "reportMissingRates"      => 0      // Send emails to administrator in case of missing rates
);

Pricing formula:

if min_duration then
    minimumDurationCharged = min_duration
else if minimumDurationCharged set in global inc
    use minimumDurationCharged from global.inc
else
    minimumDurationCharged = call duration

if increment then
    durationForRating = round to the next increment
else
    durationForRating = call duration

if durationForRating >= minimumDurationCharged then
    Price = connectCost/priceDenominator+
    durationRate*durationForRating/durationPeriodRated/priceDenominator
else
    Price = 0

ENUM discounts

The rating engine can apply a discount associated with the ENUM top level domain that returned the final destination.

Price = Price - Price * ENUM discount / 100

To apply ENUM based discounts, the ENUM TLD must be saved with each CDR and the TLDs with their corespondent discounts must be provisioned in the Rating tables section. See ENUM TLD discounts section for more information.

Purchasing price

A second price called Price in is calculated using the same formula but based on connectCostIn and durationRateIn values. It can be used to match the purchasing price and calculate the margin between purchasing and selling prices. The information about both prices is stored in the RateInfo field of the CDR. The values for connectCostIn and durationRateIn must be provisioned in the billing_rates and billing_rates_history tables using the web interface or by importing csv files.

6. Save the calculated Price, billing party and destination id for each call in the CDR table. Having the price stored in the database, it is possible to build statistics to display consolidated revenues per country code, network or subscriber.

Rating files

To generate billable CDRs from the SIP traffic that goes through the platform you must provision the rating plans in CDRTool software that is responsible for CDR mediation and rating.

Before provisioning CDRTool, is important that you understand how the rating engine works. The modus operandi of the rating engine is described in RATING.txt

After you read and understand the document, you will need to generate the following files:

1. destinations.csv
2. customers.csv
3. billing_profiles.csv
4. billing_rates.csv

The file formats are described in 'Importing and exporting of rating files' section of the RATING.txt document.

Samples files can be found in the CDRTool software archive available at: http://download.ag-projects.com/CDRTool/sample_rating_files/

The files must then be uploaded in CDRTool rating engine, once the software is operational on your platform.

The operator is responsable for creating the files. The initial provisioning is performed by AG Projects.

The content of the rating tables can be edited in the web interface provided by CDRTool application.

Importing and exporting of rating files

There are different data files needed for rating. The data files are imported into their corresponding MySQL tables. The files must be uploaded to /var/spool/cdrtool directory. To load the files into the database run the following command:

/var/www/CDRTool/scripts/importRatingTables.php

The import script knows to import the files only once so you may dump several files there and safely run the import script from cron. The import script detects whether each file have been imported by building a unique key out of the filename and the hash of the file content. So you may use the same filenames as long as the content differs and viceversa. If the import file has changed any records, the rating engine is automatically instructed to reload the changes.

Sample csv files are found in the setup directory. The CSV field order is described in setup/*.csv sample files. The first element on each line specifies the operation will be performed with the current record. The operation can be 2 (update/insert), 1 (insert) or 3 (delete).

The updates are performed based on a unique key present in each table:

billing_customers     - cust_idx      (gateway,domain,subscriber)
destinations          - cust_dest_idx (gateway,domain,subscriber,dest_id)
billing_profiles      - profile_idx   (name)
billing_rates         - rate_idx      (name,destination,application)
billing_rates_history - rate_idx      (name,destination,application,startDate,endDate)

The content of the rating tables can be exported in the Rating tables page.

The import script detects the type of file to import based on its filename.

The filename must comply with the following naming convention:

1. Must start with the name of the table without the billing_
2. May optionally contain extra characters after the name
3. Must end with .csv extension

Examples:

• rates.csv or rates20061201.csv will be loaded into the rates table
• profiles.csv or profiles20061201.csv will be loaded into the profiles table
• destinations200601.csv will be loaded in the destinations table
• ratesHistory200801.csv will be loaded in the rates_history table

Do not use billing_ prefix in front of the file name.

It is advisable to name the files in a consistent manner like tableYYYYMMDD.csv

The results of the import operation is logged in the database and can be viewed in the Log section of the web interface and the syslog.

Renormalizing CDRs

Sometime is useful to be able to change the rates for calls that have been already normalized and rated, for example after changing the rating tables you wish to apply the changes for the previous month for a customer.

To re-rate the CDRs do the following:

1. Change the current rates by using cvs files/WEB interface or add rates valid for specific dates/destinations in the rates_history table
2. Re-normalize the calls to be re-rated by either selecting ReNormalize check-box in the search screen or by changing the Normalized field in the CDR MySQL table (e.g. radacct):
   1. Re-rate calls for this month (2004-12) SIP domain example.com:
      
      UPDATE radacct set Normalized = '0' where Realm = 'example.com' and AcctStartTime >= '2004-12-01'
   2. Re-rate calls for SIP subscriber sip01@example.com:
      
      UPDATE radacct set Normalized = '0' where UserName = 'sip01@example.com'
   3. Apply rating again using command:
      
      /var/www/CDRTool/scripts/normalize.php

Notes

Renormalization process does not affect the balance of prepaid users. Prepaid is a real time un-reversible process, it goes in one direction. The prepaid balance is changed only by placing a call or adding credit to it. There are several reasons for this:

• The balance before and after each CDR is not known to be able to roll it back at a later time
• Re-rating correctly is mathematically not possible for prepaid users that have calls in progress
• If the prices are higher than previously debited and end up with a negative balance, the software cannot force the user to pay more retroactively

Next Steps

Continue by:

• learning about Prepaid; or
• learning about Rating: Assignment; or
• go back to CDR Description.