Continue in 2 seconds

Data Warehousing in the Utility Industry

Published
  • June 01 1999, 1:00am EDT

Few industries have experienced so much flux, so rapidly, as telecommunications. Marketing must work to retain customers aggressively wooed by the competition: 20-25 percent of long-distance customers change carriers annually. IT must proactively manage unpredictable network traffic volumes from new services such as the Internet and wireless. And the finance group must ensure profitability while revenues are squeezed by globalization, merger mania and a blurring of boundaries between traditional fixed line, cable, mobile and satellite communications.

Luckily, telcos already possess a wellspring of business intelligence: the call detail record (CDR). Using data warehousing and data mining techniques, telcos can gain useful, timely business insight to help the business become more customer oriented and help managers make stronger, better-informed decisions about marketing, billing and network management.

Leveraging the Call Detail Record

Analogous to bank transaction records and airline reservation records, the CDR is generated at the beginning of a customer interaction and then modified at subsequent steps in the business data flow. The path of the CDR through the telco can be conceived as the CDR value chain. At each stage, the CDR represents a rich source of business intelligence that can benefit various organizations within the telco, including IT, sales, marketing and finance (see CDR Value Chain sidebar).

Business Applications

By collecting data at different stages of the CDR life cycle, telcos can improve processes related to networks, marketing and pricing. It is important to warehouse the CDR at the correct stage of its life cycle for a particular business application. The following are popular CDR business intelligence applications.

Root Cause Analysis and Service Level Measurement
Warehouse Content:
Switch CDR, enhanced with information from the SS7 network

Telcos can take advantage of network-performance information to optimize the network and improve quality of service. Certain network performance statistics, such as the ratio of failed calls to completed calls, are often mandated by cross-organizational contracts or by industry regulators. To measure network performance, telcos can analyze CDRs at the switch, the only time when the CDR includes information about failed calls. With a data warehouse of switch CDRs enhanced with information from the SS7 network, telcos can:

  • Analyze why calls failed to connect.
  • Compare actual calls completed to promised service level agreements.
  • Calculate success ratios for inter-carrier handoff of calls.
  • Measure network flow for the purpose of capacity planning and individual trunk sizing.
  • Measure network efficiency over time.
  • Measure operator efficiency over time, such as average call response time.
  • Analyze system outage trends, generally associated with failing network components.
  • Notify network operations personnel in real time when call failures exceed a certain level.

Fraud Detection
Warehouse Content:
Switch CDR or Enhanced Switch CDR

Telephone fraud is estimated to cost $4-6 billion annually in the United States alone, and global losses might amount to $12-18 billion.1 Data warehousing provides telcos with two techniques for combating fraud. In both techniques, the telco first uses data mining techniques to build caller profiles including time and distance, call patterns, credit limits and called parties. Then the telco compares actual call data against the caller profile.

In real-time fraud detection, the telco monitors the length of calls in progress – before the CDR arrives at the switch. If the call duration is longer than usual for a particular caller, the real-time fraud solution relays the call detail to the fraud department, which can contact the customer to authorize the call or suspend the phone number.

In subscription fraud detection, the telco monitors abnormal calling patterns and compares them to the caller profile to detect possible fraudulent behavior. The database of enhanced switch CDRs provides more timely subscription fraud detection because it is updated every one to five days, compared to 30 - 60 days for the database of billed CDRs.

Call Behavior Analysis
Warehouse Content:
Rated CDR or Enhanced Switch CDR

Telcos are the sole owners of their customers' calling information, making that information a valuable source of marketing intelligence. Telcos can analyze calling-pattern information to predict future calling patterns for individual customers or groups of customers. With this information, the marketing group can improve product planning, improve customer recruitment and retention, and develop specialized rate plans to optimize revenue per calling minute – for example, increased rates during peak calling periods or for calls between city pairs that have a large amount of call traffic. The IT group can also determine if network capacity upgrades are needed by analyzing calling patterns, by region, city pair and other geographic patterns or in response to events such as unusual weather and civic events. Another important, measurable property to cellular operators is the "hot spot" cell locations for initiating and receiving cellular calls.

Targeted Marketing
Warehouse Content:
Rated CDR

The marketing group can take advantage of a data warehouse of rated CDRs to generate lists of ideal prospects for targeted marketing campaigns, promotions, bundling of products and related marketing activities. Typically, the marketing group extracts caller list files – either flat files or a simple operational data store (ODS) – from the data warehouse and provides them for the telemarketing group. The marketing group can also use the sales and marketing data warehouse/mart to measure the increase in calling volumes as a result of a campaign.

Customer Relationship Management
Warehouse Content:
Billed CDR (Preferred) or Rated CDR

Using data mining techniques, the marketing group can determine the most and least profitable customers and markets. Premium customers can be targeted by advertising and promotions – for example, rewarded with bulk discounts for making a certain volume of calls. Similarly, unprofitable customers can be charged higher fees, regulations permitting, to make them more profitable. Billed CDRs are also useful for identifying cross- selling opportunities when a customer has only one of several services that are usually purchased together.

Financial Analysis
Warehouse Content:
Billed CDR

Financial analysis hones in on revenue patterns, such as the most profitable days, time periods and city pairs, and the most profitable locations for pay telephones. It enables the telco to analyze CDRs in conjunction with rates and expenses to discover optimal financial yield. To calculate the revenue per call based on the network infrastructure used for the call, the telco can use pre-switch CDRs rather than billed CDRs.

Churn Analysis
Warehouse Content:
Rated CDR or Billed CDR

Churn is an expensive problem for telcos. Average churn rate is between 20 and 25 percent and acquisition costs of new customers average $300 for wireline and $500 for wireless services.2 To understand the reasons that customers defect to competitors, marketing analysts can use data mining techniques to build normal usage patterns for individual customers and groupings of customers. These results are used to predict future customer behavior and to flag customers whose calling patterns, such as making substantially fewer calls, often predict churn. Data warehousing applications can also discover patterns that suggest which customers will tend to remain loyal, if retained, and which are likely to churn again. The applications can also be used to survey for customers who appear and disappear in regular historical patterns (every six to nine months). The telco can then spend its marketing dollars to retain the customers who fit the profile for imminent churn but are likely to remain loyal if offered enticements.

Best Practices

The rewards of data warehousing in the telecommunications industry are well documented. Considering that the average cost of retaining a customer is $50 per year, compared to $300 to $500 for attracting a new customer, the payback period for a data warehouse is estimated at two years.3 By following best practices in their data warehousing initiatives, telcos can maximize their benefits and decrease costs and risk.

  • Select the optimum location for the data warehouse (s) – switch, mediator, rating engine or billing system – depending on the CDR content you want to retain and analyze.
  • Be cautious about comparing CDRs collected at various stages in the CDR value chain. Data is removed and added at each stage, which can result in financially inconsistent comparisons. For example, the sum of rated CDRs reveals total revenue opportunity, while the sum of billed CDRs reveals actual gross revenue after discounts and adjustments.
  • Implement policies for data cleansing and standardization. A data warehouse can become the most accurate, complete source of information for downstream data marts, OLAP cubes and databases where further data synthesis and analysis can be performed.
  • Choose a data warehousing platform suited to the demands of the network location. For example, a platform deployed at the switch must accommodate a very high rate of arrival, while a platform billing CDR analysis should support potentially large numbers of concurrent queries

The intense competitive pressures within the telecommunications industry will only increase with time, forcing telcos to extract more business value from their existing systems and processes to survive and thrive. By building one or more data warehouses of CDRs, telcos can exploit an existing resource for quantifiable business advantages. These include optimizing the network, identifying and retaining the most profitable customers, identifying new markets and developing the responsive, nimble infrastructure required to enter certain new markets.

CDR Value Chain

The content of the CDR is different at each stage of the CDR life cycle. Data extracted from the switch, mediator device, rating engine or billing system yields a variety of valuable information. Generally, as the CDRs proceed away from the switch level:

  • Timeliness of the data decreases,
  • Completeness and accuracy of the data increases (at least for revenue calls),
  • Business value of the data increases, and
  • Number of data records decreases and the length of the records may increase.

Phase 1: Switch. Every call – traditional or cellular – passes through one or more switches, which assemble the original CDR. When enhanced with data from the SS7 network, the switch CDR contains the calling number, destination phone number, billing phone number, geographic coordinates of the switch, time of call, minutes of usage (MOU) and the type of call (voice or data). This is generally the only stage in the CDR life cycle that includes information about incomplete calls, which is useful for network performance monitoring.
Switch CDR Applications: Network optimization, monitoring of service level agreements (SLAs) from partners, early fraud detection, accurate wholesale billing.

Phase 2: Mediator device. The mediator device collects switch CDRs, converts them to a common data format and distributes the ones associated with completed calls to the appropriate rating engine after adding core bill plan information. Newer mediators retain information about incomplete calls, storing it separately.
Mediated CDR applications: Analysis of incomplete calls (newer mediators only).

Phase 3: Rating Engine. Rating engines apply a base price to the CDR, taking into account rate plans, location charges, location-specific usage charges and special services charges. The information in the rated CDR does not represent the final rating that customers see on their bills because the billing system applies various discounts that cannot be determined until the end of a billing cycle.
Rated CDR applications: Call behavior analysis; sales and marketing applications using most timely data.

Phase 4: Billing System. The billing system adjusts the rated CDR record with corrected payment information to create one billable call record. It adds discounts and adjustments, and might also add surcharges for operator assisted calls and conference calls. Billed CDRs provide a view of the revenue stream generated by customers, sales campaigns and sales organizations. They should not be considered financially complete because they often are not generated until the next month's billing cycle and might not include all externally applied discounts and adjustments from other carriers. For the same reason, they are not an appropriate source for "system of record" financial information.
Billed CDR applications: Financial; sales and marketing using most accurate data.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access