Skip to main content
Lantern Home
 
 
Splunk Lantern

Managing prepaid mobile services

  1. Last updated
  2. Save as PDF

 

In your service analysis role working for a telecommunications provider, you are responsible for ensuring the efficacy of the order-to-activation workflow of prepaid mobile services. You need to be aware of activation errors and potential regulatory violations. At the same time, executives in your company want data on prepaid mobile sales and revenue so they can maintain good marketing strategies. This guide provides a number of searches gathering all kinds of prepaid mobile data, from which you can create dashboards for both of these distinct use cases.

​Data required

  • Prepaid order-to-activation logs from a mobile network service provider

Procedures

  • Splunk recommends that customers look into using data models, report acceleration, or summary indexing when searching across hundreds of GBs of events in a single search. The searches provided here are a good starting point, but depending on your data, search time range, and other factors, more can be done to ensure that they scale appropriately.
  • Your typical telecommunications transactions might include more than four steps, and some commands, parameters, and field names in the searches below may need to be adjusted to match your environment. In addition, to optimize the searches shown below, you should specify an index and a time range when appropriate.
► Stage 1: Search and investigation

Business process error alerts within the order-to-activation workflow

Most telecommunications and service providers have a large number of processes in their order-to-activation workflows, but very few have the ability to monitor transactions as they flow through their various IT systems, largely due to widespread use of custom applications. Having this insight may allow a business to more quickly troubleshoot problems in IT systems tracking such business processes as service activation or inventory fulfillment.

This search looks at a 10-minute window to find errors across an order-to-activation workflow, listed by process. This example gives an error count for orders as well as their associated alerts (listed by specific business process) as the order data flows through the order-to-activation processes.

For this search, your data source must have a transaction status that indicates either success or failure, listed by specific business process. This example displays data for four business processes: billing systems, inventory and fulfillment, service activation, and CRM.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) AS _time 
| fields _time p1* p2* p3* p4* 
| eval p1=case(p1Status=="OK",0,1=1,"1"), p2=case(p2Status=="OK",0,1=1,"1"), p3=case(p3Status=="OK",0,1=1,"1"),p4=case(p4Status=="OK",0,1=1,"1") 
| eventstats sum(p1) AS p1cnt sum(p2) AS p2cnt sum(p3) AS p3cnt sum(p4) AS p4cnt by _time 
| timechart fixedrange=false span=10m sum(p1cnt) AS "Billing" sum(p2cnt) AS "Fullfilment" sum(p3cnt) AS "ServiceActivation" sum(p4cnt) AS "CRM" 
| search  ("Billing" > 0) OR ("Fullfilment" > 0) OR ( "ServiceActivation" > 0 ) OR ( "CRM" > 0)

Provisioning and activation errors with detailed messages

This example gives a service activation process view of errors over time with a more human readable description of the error. With this information, you have the capability to understand where within the service activation process you encounter problems, and what teams to work with in order to resolve these.

For this search, the data source for the transactions needs to have a transaction failure status within the process (in this case, service activation.) Additionally, there should be a error code to human-friendly description mapping that is used to enrich the source data for the final view.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) AS _time 
| search p3Status="FAIL" 
| fields _time p3* customer* 
| lookup prepaid_mobile_errors pMessage AS p3Message 
| stats count values(pDetails) AS "FailureReason" values(p3Message) AS "ErrorMessage" BY _time 
| search count > 0 
| stats sum(count) AS count by FailureReason ErrorMessage 
| where count > 0
► Stage 2: Proactive monitoring

Activation errors due to potential SIM abuse

Due to regulatory requirements or policy, you might have a limitation set on the number of SIMs a user can own on a network. Attempting to activate more will result in an error.

This search provides a report of order and provisioning errors that have occurred based on a threshold of active / registered SIMs in the system. This provides a report for the associated customer details such as their customer identification, number associated with the activation, order or transaction IDs, and where applicable, a location where the transaction was attempted.

Where there exist either legal or internal regulatory and reporting requirements, a report like this can potentially be used to provide more details on the transactions in question. In this example, the carrier has a reporting requirement where users can only own three SIMs on their network. This report shows where users are trying to purchase or activate over the threshold. This kind of report can be automated and warnings sent to the user's email address, phone number, or order process. Finally, if the transaction is in a physical location, an alert may be posted.

This search requires order-to-activation log data from billing, activations, and CMS data. This example, displays failed transactions, locations of the failed transactions, a specific error code (SIM Count Failure), and a lookup to map this error code to a potentially more readable message, along with some additional information around the specific user and the transaction or order IDs that failed.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) AS _time 
| search p3Status="FAIL" 
| fields _time p3* customer* order* 
| lookup prepaid_mobile_errors pMessage AS p3Message 
| search p3Message="ERROR-B993" 
| lookup prepaid_locations orderLocation OUTPUT description 
| stats count values(customerIdNumber) AS customerIdNumber values(orderTracking) AS orderTracking values(pDetails) AS Details list(description) AS Location BY customerID 
| sort - count

Errors within the order-to-activation workflow

This search gives a by-process error count of orders as they flow through an order-to-activation processes over time. Many telecommunications companies and service providers have a large number of processes in their order-to-activation workflows, but not all of them have the ability to monitor transactions as they flow through their various systems, perhaps due to the widespread use of various custom and homegrown applications. Having these insights might allow a business to troubleshoot where a specific problem occurs. For example, knowing if the problem comes from a service activation system or an inventory fulfillment system can be beneficial to customer service.

This search provides a trellis timeline and count for activations by a per-process chart. With this information, you might be able to determine whether an issue exists within a specific order-to-activation process, and fix the issue with minimal impact.

For this search, the data source must have a transaction status that indicates either a success or a failure, listed by business process. This example displays four business processes included as examples: billing systems, inventory and fulfillment, service activation, and CRM. You can include more business processes by expanding the available data sources.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) AS _time 
| fields _time p1* p2* p3* p4* 
| eval p1=case(p1Status=="OK",0,1=1,"1"), p2=case(p2Status=="OK",0,1=1,"1"), p3=case(p3Status=="OK",0,1=1,"1"),p4=case(p4Status=="OK",0,1=1,"1") 
| eventstats sum(p1) AS p1cnt sum(p2) AS p2cnt sum(p3) AS p3cnt sum(p4) AS p4cnt BY _time 
| timechart fixedrange=false span=10m sum(p1cnt) AS "Billing Systems" sum(p2cnt) as "Fullfilment" sum(p3cnt) AS "Service Activation" sum(p4cnt) AS "CRM"
► Stage 3: Operational visibility

End-to-end transactional status overview

This search provides the end-to-end status, over time, of your order-to-activation process. Specifically, this example shows the successful or failed attempts for customers purchasing or renewing services. This search can give insight into how your order-to-activation process is working via visual representations of status. Specifically, such data can allow you to recognize a high number of failures as some form of system outage or a specific problem that needs to be investigated.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) AS _time 
| fields _time transactionStatus 
| timechart span=10m fixedrange=false count BY transactionStatus

New prepaid services by top purchasing countries

This search returns those countries in which your prepaid services are most popular. A visual representation of those countries from which top net-new purchase customers originate, based on their order details. This information can be used to make informed decisions about where to devote more resources marketing campaigns or during which times such campaigns would have the most impact.

For this search, your data source for the transactions must contain customer information. In this example, billing and regulatory requirements require recording either a passport or federal ID for the customer. You can then associate these data points with each transaction and chart the data.

| sourcetype <prepaid_o2a_logs> 
| fields iDCountry orderType 
| search orderType="NEW" 
| eval iDCountry=upper(iDCountry) 
| top iDCountry

Order and provisioning errors with customer ID

This search can provide insight into whether activation or service errors are expected and the associated impact of such errors.

This search require order-to-activation log data from billing, activations, and CMS data. This example integrates failed transactions, locations of such failed transactions, and an error code and a lookup to map this error code to a potentially more readable message, along with some additional information around the user and the transaction or order IDs that failed.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) AS _time 
| search p3Status="FAIL" 
| fields _time p3* customer* order* 
| lookup prepaid_mobile_errors pMessage AS p3Message 
| stats count values(pDetails) AS "Error Details" values(customerIdNumber) AS "Customer ID Number" values(orderTracking) AS "Order Tracking" by customerID 
| sort - count 
| rename count AS Count customerID AS "Customer ID" 
| fields "Customer ID" "Customer ID Number" "Error Details" "Order Tracking" Count

Top-selling services / SKUs within service catalog

This search can help with making informed decisions about the most popular services sold over time. It gives insight into which services your customers purchase based on their order details. Such data can help you understand customer needs and perhaps how various marketing campaigns could drive business.

For this search, your data source for the transactions must contain a successful transaction status and product. Then, enrich the data with a description of each product SKU from a lookup with SKUs mapped to a potentially more readable name.

| sourcetype <prepaid_o2a_logs> 
| fields _time productSKU transactionStatus 
| search transactionStatus="SUCCESS" 
| lookup prepaid_o2a_products productSKU 
| stats count BY description 
| sort - count

Volume of types of orders by time

This search can help you make more informed decisions about what services are sold over time, based on customer order details. Such data can help you understand customer needs and perhaps how various marketing campaigns could drive business.

For this search, your data source for the transactions must contain an ordertype field corresponding to the service description of what is being sold.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) AS _time 
| fields _time orderType orderLocation IdType customerIdNumber 
| search orderType="*" 
| timechart fixedrange=false span=30m count BY orderType
► Stage 4: Business insights

Daily lost revenue for mobile services

This search shows how to associate a monetary value to each transaction and gives an estimated total daily loss in revenue. This can help give an understanding of the costs associated with each transaction and your associated daily revenue for real-time insight into your business.

This search requires order-to-activation log data from billing, activations, and CMS data. This example takes the failed transactions and adds a unit price for the product/SKUs of the product. Then, it summates this data for the time range and uses a single value display to visualize the results.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| lookup prepaid_o2a_products productSKU OUTPUT price 
| fields _time orderLocation transactionStatus price transactionStatus 
| search transactionStatus="FAIL" 
| lookup prepaid_locations orderLocation 
| timechart fixedrange=false span=1d sum(price)

Daily revenue for mobile services

This search shows how to associate a monetary value to each transaction and get an estimated total daily revenue stream. This can help give an understanding of the costs associated with each transaction and your associated daily revenue for real-time insight into your business.

This search requires order-to-activation log data from billing, activations, and CMS data, as well as revenue data for the previous week and a per product price.

| sourcetype <prepaid_o2a_logs> 
| append [ inputlookup prepaid_previous_revenue 
| rename date AS timestamp totalRevenue AS price 
| fields timestamp price ] 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| lookup prepaid_o2a_products productSKU OUTPUT price 
| timechart fixedrange=false sum(price) AS totalSold

Orders by geographic location

This search gives insight to where your activations and services are purchased from. With order-to-activation geomapping of completed orders for your prepaid mobile services, the business might be able to make more informed decisions about where to increase retail capacity and when such increases may have the most impact. For example, you might find that at the airport during arrival times of flights from certain countries there is a large prepaid purchase history and you might want to increase capacity for retail space there.

For this search, your data source for the transactions needs to have a location. Then, enrich that location data with a lookup that contains the latitude and longitude and map this data out with the geostats command.

| sourcetype <prepaid_o2a_logs> 
| fields orderLocation 
| lookup prepaid_locations orderLocation 
| geostats count latfield=lat longfield=lon binspanlat=19 binspanlong=19

Retail locations falling outside of service-level agreements

Most retail outlets measure metrics related to how long a transaction takes to complete and how it relates to customer experience. When customers experience above-average transaction run times, revenue may potentially be lost. Understanding your transaction times at retail locations may help forecast when more manpower is needed.

This search requires order-to-activation log data from billing, activations, and CMS data. This example takes the transactions and adds location data to each event. Then, it calculates a transaction duration based on when a previous transaction is finished and when the current one ends. Next, it filters based on a specified range: transactions lasting between 5 and 25 minutes, inclusive. Finally, it uses timechart to reformat the results in a more readable form.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| fields _time orderLocation orderType productSKU transactionStatus 
| lookup prepaid_locations orderLocation OUTPUT description 
| search description="Store1*" 
| sort 0 _time 
| streamstats window=1 current=false last(_time) AS previousTime 
| eval transactionDuration = ( _time - previousTime ) / 60 
| eval transactionDuration = round(transactionDuration,2) 
| where transactionDuration < 25 
| stats values(transactionDuration) AS transactionDur values(_time) AS startTime values(orderLocation) AS orderLocation values(orderType) AS orderType values(transactionStatus) AS transactionStatus BY description _time 
| where transactionDur > 5 
| rename description AS "Location" transactionDur AS "Duration" 
| fields Location Duration _time startTime 
| timechart fixedrange=false span=1h count list(Duration) AS Duration list(startTime) AS Times list(Location) AS Location 
| convert ctime(Times) timeformat="%Y/%m/%d %H:%M:%S" 
| table _time Location  Times Duration count

Revenue for mobile services sold by location

This search gives an hourly view into your sales at various locations, including storefronts and affiliate sales partners, showing how they are performing in regards to revenue generated. This gives real-time business insight into sales figures and enables informed decisions around lines of business such as location expansion and revenue forecasting.

This search requires order-to-activation log data from billing, activations, and CMS data, plus location data to enrich the results. This example takes the successful transactions, adds a unit price for the product or SKUs sold, and enriches location data. Finally, the results are timecharted based on a one hour span and the location.

| sourcetype <prepaid_o2a_logs> 
| eval _time=strptime(timestamp, "%Y-%m-%d %H:%M:%S") 
| lookup prepaid_o2a_products productSKU OUTPUT price 
| fields _time orderLocation transactionStatus price transactionStatus 
| search transactionStatus="SUCCESS" 
| lookup prepaid_locations orderLocation 
| timechart fixedrange=false span=1h sum(price) BY description

Next steps

The searches in this guide are also included in the Splunk Essentials for Telecommunications the app, which provides more information about how to implement them successfully in your telecom services maturity journey. This is a Splunk Field supported App. If you find it useful, have any feedback for use case additions, or general comments about what you would like in the next releases, contact the field at #telco-media on Splunk-Usergroups Slack or telco@splunk.com.

You might be interested in the following additional telecommunications use cases:

Splunk OnDemand Services: Use these credit-based services for direct access to Splunk technical consultants with a variety of technical services from a pre-defined catalog. Most customers have OnDemand Services per their license support plan. Engage the ODS team at ondemand@splunk.com if you require assistance.