Skip to main content

 

Splunk Lantern

Estimated generated call revenue

You might want to estimate a total daily call revenue stream when doing the following:

Prerequisites 

In order to execute this procedure in your environment, the following data, services, or apps are required:

Example

You work for a large telecommunications provider and use Splunk to monitor the services you provide. You need a search that will associate a monetary value to each successful call and get an estimated total daily revenue stream.

To optimize the search shown below, you should specify a time range.  You may also need to adjust fields to match what is available in your data source. 

  1. Ensure your lookup files are uploaded to your Splunk deployment.
  2. Run the following search:
    | sourcetype=<call detail records>
    | eval _time = start
    | lookup <cdr disposition mapping file> disposition
    | search disposition="ANSWERED" OR billable > 0
    | rex field=dst "(?<dstCountry>\d+)(?=\d{10})"
    | lookup <country code to name mapping file> phoneCode AS dstCountry OUTPUTNEW countryName as dstCountryName ISO2 AS dstCountryCode rateFirstMinute rateMinute
    | fields _time accountcode src billable dstCountryCode rate*
    | convert dur2sec(billable) AS calculatedBillableS
    | eval billableMinutes = calculatedBillableS / 60
    | eval billableMinutes = ceiling(billableMinutes)
    | eval billableMinusOne = billableMinutes - 1
    | eval totalBillable = rateMinute + (billableMinusOne * rateMinute)
    | stats sum(totalBillable) AS CallRevenue

Search explanation

The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.

Splunk Search Explanation
| sourcetype=<call detail records> Search only your telephony data.
| eval _time = start Extract the timestamp and put it in a field called "start".
| lookup <cdr disposition mapping file> disposition

Enrich the search results with clearer descriptions of the dispositions.

If your lookup file does not contain the disposition column, adjust the search to match the names in your lookup.

| search disposition="ANSWERED" OR billable > 0 Search for billable call events, identified with a disposition of ANSWERED or where the billable field has a value greater than 0.
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})"

Extract the destination (dst) country code.

This example assumes that the country code is what precedes a 10 digit number.

| lookup <country code to name mapping file> phoneCode AS dstCountry OUTPUTNEW countryName as dstCountryName ISO2 AS dstCountryCode rateFirstMinute rateMinute

Enrich the results by adding a country name, iso code, and billable rates. based on the dstCountry field extracted.

If the column names in your lookup file differ from those shown here, adjust the search to match the names in your lookup.

| fields _time accountcode src billable dstCountryCode rate* Return only the fields shown.
| convert dur2sec(billable) AS calculatedBillableS Convert the value in the billable field to a numerical value in seconds without replacing the original values.
| eval billableMinutes = calculatedBillableS / 60 Calculate billable minutes by dividing calculated billable seconds by 60.
| eval billableMinutes = ceiling(billableMinutes) Round billable minutes to the next highest integer.
| eval billableMinusOne = billableMinutes - 1 Subtract 1 from the total billable minutes.
| eval totalBillable = rateMinute + (billableMinusOne * rateMinute) Calculate the total billable rate per call
| stats sum(totalBillable) AS CallRevenue Sum and display the total billable revenue.

Result

Use the results of this search to assist in informed decisions about capacity, billing rates, services, and marketing campaigns.