®

IBM Software Group

Case Study for Telecommunications

TDW Reference Implementation

David Cope
EDW Architect – Asia Pacific

© 2007 IBM Corporation

 IBM Software Group

Data Warehouse Vision

“…to enable Customer Relationship Management

(CRM) across the client organization and reduce
operating costs. The Solution becomes an integral
component to the formation of the customer knowledge
database to support CRM front office applications and
business intelligence functions. The ultimate goal is to
form a closed loop integrated environment between
operational and analytical CRM to facilitate customer
relationship management..”

2
 IBM Software Group

Project Structure

Project Sponsor: Chief Operating Officer

Key User Community: Customer Base Analytics Group

Project covers the following divisions:

Sales & Marketing

International & Wholesale

Finance

Product Development

Information Technology

3
 IBM Software Group

Why TDW and IBM?

Several vendors and associated products were evaluated

Teradata, Microsoft, Oracle and IBM

TDW offered the highest degree of flexibility and scalability

Complete solution with capability to expand with business

TDW provided a strong fit to the organization, with over 90% of the business
and data requirements being met out-of-the-box

Open technology approach – looking for an open scalable solution rather than a
proprietary architecture / technology

IBM services had a proven ability in data warehouse implementation, and it was
believed that the success of the project lay heavily on a robust and sound
industry model, together with the delivery team’s proven ability to implement

4
 IBM Software Group

TDWM embraces different views of the business to

support various analysis requirements
Example – how a single user view can be interpreted in TDWM:

Churned
Involved Party-1; Customer Involved Party-1;
Account-1; Customer Account-2; Customer

Churn
Win-Back
Service Instance-1;
Product-1; Plan-1 Acquisition

Migrate Price
Service Instance-2; Plan-1 to Price
Product-2; Plan-1 Plan-2
Cross-sell Product-2
Service Instance-2;
Product-2; Plan-2
Time
t0 t1 t2 t3 t4
Prospect
Customer

5
 IBM Software Group

Project Objectives and Phasing

Project Objectives

Implement a Data Warehouse Infrastructure to deliver business intelligence
capabilities and Closed Loop CRM Capabilities

Establish a customer knowledge environment, addressing the existing
business & technical issues through an integrated and reliable platform
(Cost, Security, Integrity, Access, Tools, etc.)

A fully secured environment that safeguards assets.

Implementation Phasing

Phase 1 – Integrated Data Warehouse Infrastructure

Phase 2 – Business Intelligence Applications

Phase 2A – Campaign Management Solution

Phase 2B – Data Mining Solution

6
 IBM Software Group

Implementation Phases

Phase 1 Phase 2
Established the core Data Deployed a new Campaign
Warehouse using the Telecom Management System and integrated
Data Warehouse Model (TDWM) Data Mining environment with the
to immediately support flexible, ad Data Warehouse Infrastructure to
hoc query analytical capabilities support their campaign operations
within the environment. and analytics.
Com plai nts Analys is Dim ensio ns Pr oduc t

Com plai nts Analys is Dim ensio ns

BST: m1 Scope
Com plai nts Analys is Dim ensio ns Pr oduc t
Pr oduc t

Chan
Busin ess Un geitSer
Ge ovice
gr aphy
Pr oduc t Access Ser vice
Fina ncial En gi neer in g
Tr adi ng Ser vices
Supp or t Ser vices
Busin ess Un it Ge o gr aphy Nor th
Nor th
West
East
Sout h
BSTs
TDWM
Line o f Busi ness West Tr ust
East Retail
Sout h Com m er cial
Busin ess Un it Ge o gr aphy Nor th Whol esale
West Gr ou p Ser vices
Line o f Busi ness East Tr ust
Sout h Retail
Cust om er Mar ket Se gmCom en t m er cial

Logical
Whol esale
Line o f Busi ness Tr ust
Gr ou p Ser vices
Retail
Com m er cial
Cust om er Mar ket Se gm en tesale
Whol
Gr ou p Ser vices
Meas ur es Num ber o f C om plai nts
AverLar
agege
Res po nse
C om m er tim
ciale
Cust om er Mar ket Se gm en t
Sm all/m edium Com m er cial
High Net W or t h In divi dua ls
Indivi d uals
Meas ur es Num ber o f C om plai nts
Aver age Res po nseChar ities
tim e

Meas ur es Num ber o f C om plai nts

Aver age Res po nse tim e

Physical
Campaign
Management and
BST: Reports and Graphs
Data Mining Apps

Provisioning,
billing and Data Warehouse Database BST: Reports and Graphs

OSS/BSS apps.
Static & Ad-
hoc Reporting
7
 IBM Software Group

Business Requirement Analysis

Adopting the Telecommunications Data Warehouse Business Solution
Templates (BST) to guide the investigation and scope the requirements

Sales
Sales and
and Marketing
Marketing Churn
Churn Management
Management Operations
Operations and
and Finance
Finance
Campaign Contract
ContractRenewal
Renewal Analysis
Analysis Customer
CustomerBilling
BillingAnalysis
Analysis
CampaignAnalysis
Analysis
Cross Customer Churn Analysis
Customer Churn Analysis Financial
Financial ManagementAnalysis
Management Analysis
CrossSell
Sell Analysis
Analysis
Customer NLD/IDD
NLD/IDDDefection
DefectionAnalysis
Analysis Income
IncomeAnalysis
Analysis
CustomerAcquisition
AcquisitionAnalysis
Analysis
Data Package Sales Analysis
Data Package Sales Analysis
Number
NumberPortability
PortabilityAnalysis
Analysis Product
Retail Transaction Analysis Product Lifecycle
Lifecycle
Retail Transaction Analysis Management
Sales
SalesChannel
Channel Analysis
Analysis Management
Product
ProductProfitability
ProfitabilityAnalysis
Analysis
Postpaid
PostpaidRevenue
RevenueAnalysis
Analysis
Usage Prepaid
PrepaidRevenue
RevenueAnalysis
Usage Profiling
Profiling Analysis
Content
ContentUsage
UsageAnalysis
Analysis
E-Commerce CRM
CRM && Segmentation
Segmentation
E-CommerceAnalysis
Analysis Service
Service Quality
QualityManagement
Management
Inbound Commercial
Commercial Customer
CustomerFinancial
Financial Analysis
Analysis
InboundRoamer
RoamerUsage
UsageAnalysis
Analysis CSR
CSRPerformance
PerformanceAnalysis
Analysis
Outbound Customer
CustomerArrangement
ArrangementAnalysis
Analysis
OutboundRoaming
RoamingAnalysis
Analysis Service
ServiceOrder
OrderProcessing
ProcessingAnalysis
Analysis
Pre-rated Customer
CustomerLifetime
LifetimeValue
ValueAnalysis
Analysis
Pre-ratedCDR
CDRAnalysis
Analysis
SMS/MMS Customer
CustomerProfitability
ProfitabilityAnalysis
Analysis
SMS/MMS UsageAnalysis
Usage Analysis
Wireless Individual
Individual Customer
CustomerFinancial
Financial Analysis
Analysis Credit
Credit Risk
Risk Management
Management
WirelessData
DataUsage
UsageAnalysis
Analysis
Customer Complaints Analysis
Customer Complaints Analysis Credit
Wireless
Wireless Voice UsageAnalysis
Voice Usage Analysis Creditand
andCollections
CollectionsAnalysis
Analysis
Wallet
WalletShare
ShareAnalysis
Analysis Customer
Wireline
WirelineData
DataUsage
UsageAnalysis
Analysis Customer DelinquencyAnalysis
Delinquency Analysis
Wireline
WirelineVoice
VoiceUsage
UsageAnalysis
Analysis Individual Credit Risk Profile
Individual Credit Risk Profile

8
 IBM Software Group

Solution Outline - Requirement Definition Approach

BST’s
Business
Business
Requirements
JAD Top Down Requirements
Report
Sessions to Report
Determine
customise Measure and
multiple Dimension
BST’s Requirements
Information Framework

Workshops Define Critical Information

Elements and assist in the Cross-
identification of candidate Reference to
data sources and change Requirements
data capture specifications Develop the framework for a
cross-functional Enterprise
Information Architecture and
Determine specify common (and
Analysts to Measure and divergent) definitions of
review Dimension business definitions
reports for Requirements
analysis
requirements Bottom Up
Reports

9
 IBM Software Group

Solution Outline - Requirement Analysis Approach

Gap
Gap
Analysis
Analysis
Information Framework Conceptual Data Model Report
Expand into a Report
Subject Area Logical Data Model
Specific Model

Cross-
Reference to
Requirements
and map to Customize the TDWM
Determine Match Data Determine Data Elements TDWM LDM
Measure and LDM to specific
Requirements that cannot be provided requirements
Dimension to Data together with reasons,
Availability Availability recommendations & impacts Define High
Migration Level
Data Discovery Migration Infrastructure
Plan
Plan Design

Data Migration Strategy Infrastructure Design

Determine and Define High
evaluate Level
approaches for Infrastructure
Analysts to review Data Migration Design
source systems for data
quality & availability

10
 IBM Software Group

Requirements: Business and Data

Business Requirements Data Requirements

Support 360ocustomer base analysis

Customer Hierarchy

Complete & consistent view of customers

Customer Profile

Customer Grouping & Segmentation

Customer Value Recognition & Measures
Service Profile

Customer Profiling & Predictive Modeling
Usage Pattern

Customer Lifecycle Management
Spending/Rebate/Payment Pattern

Customer Contact Management
Customer Behavior

Customer Calling & Usage Patterns

Customer Contact & Campaign History

Channel Performance

Roamers Profile

Ex-Customer History

Billed Revenue and Discount Information
Pre-paid SIM Information

Product Hierarchy & Analysis
Revenue/Cost Information

Prospects

Data Integrity Management
Product Hierarchy

Accurate, complete, reliable and timely data

Data Security

Comprehensive Metadata Repository

Query & Analysis Tools

11
 IBM Software Group

Gap Analysis Approach

Activity Level Costing Business Process Scope

(Data Capture and Definition)

Business Requirement
Scope DW
OLAP Adhoc
Information
Mining Discovery Scope

Customer ERP (G/L, A/P,

Demographics A/R, H/R)

Scoping Assumptions
Example Reasons for Gaps

- No OSS/BSS Changes in Phase 1.
Data not Available
- No Business Process Changes in Phase 1
Unstructured Data
- No Data Cleansing in Phase 1
Additional Process Required
- No End-user Application Development or
Unclear Business Process
Re-engineering
Unidentified Data Ownership

12
 IBM Software Group

Gap Analysis Report

Business Process
Process Data GAPS System Process GAPS

Business Application System

Requirement
System Data GAPS

Higher Priority
Lower Priority

Business Impact Measurement
- Breadth of Impact (Org, BU, Team)
- Intensity of Impact (Critical, High, Medium, Low)
- Urgency (<6 Months, < 1 Year, < 2 Years)

13
 IBM Software Group

The DW Solution

A TDWM based Enterprise Data Warehouse

The existing Data Marts were re-engineered into the IBM Telecommunications
Data Warehouse Model and implemented on DB2 DWE, DB2 OLAP Server,
Ascential DataStage, Business Objects and ERwin.

Business Objects Business Solutions

BI Applications Templates (BSTs)
DB2 OLAP Server

Analysis Structures
Telecommunications
DB2 DWE Summary Tables Data Warehouse
Relational Model (TDWM)
System of Record (3NF)

Change Data Capture

(Staging Flat Files) Source Systems and
Ascential DataStage
Legacy OSS, BSS & DSS Interface Architecture
Data Sources

14
 IBM Software Group

DW – Complete Solution View

Campaign Execution and Campaign Creation,

Non-Call Center Campaign Execution and Target
Target Customer Call Center
Touchpoints Information Management Customer Information
Solution
Solution
Interaction with Customers
Customer Contact and Customer Contact and Interaction with Customers
and Prospects through SMS, Creation, Execution and
Campaign Response Campaign Response and Prospects by Phone
e-Mail, Direct Mail, etc. Refinement of Campaigns
Information Information

Customer Campaign
Profile, Creation,
Transaction, Execution,
Score, Customer
Segment & Contact & Customer
Propensity Results Snapshot &
Information Information Scorecard
Information

Integrated Data
Customer Profile and
Data Mining Transactional Summaries Warehouse (IDW) Direct Query Access to all
Analytical
Cube, Mart and underlying
Solution Warehouse Information Environment
Center of Closed Loop CRM Solution Centralized Enterprise
Customer Segmentation, Wide Reporting Solution OLAP, Ad-hoc and Static
Scoring and Propensity Scoring, Segmentation and Reporting Solution
Propensity Results Centralized Store of Integrated Customer
Information to support all CRM solutions

Operational Systems Source Data focused on Customer Profile, Transactional and Interaction Data

Billing Call Center ERP VAS MNP ...

15
 IBM Software Group

Fully Integrated, Closed-Loop, Multi-Channel Solution

Closed-loop Marketing Process

Multi-channels
Creative
Development
Contact
Value
Proposition Target Customers
Definition
Campaign
Execution Campaign Message
SMS
Outbound Call
Program Email
Planning 360oC Shop
Customer Direct Mail
Knowledge IVRS
Response Direct Response Web
Management Call Centre

Inferred Response
Segmentation/
Pre-Analysis Response
Performance
Analysis

16
 IBM Software Group

Closed Loop is complete with Campaign and Mining

Reports on
campaign results

Interaction results fed

back into the system
Interaction
results captured
back to the data
CRM Applications
mart
Channels to contact
customers based on
Marketing department the Target customer
Customer Target
Campaign to enter criteria for list
profile Customer
Data Mart target customer
information List
selection

Summarized / Customer Historical

pre- Interaction customer
calculated Results information
Customer
information
Effective customer segmentations

Key drivers for churn model

Key criteria for acquisition campaign target list
Mining Applications selection
IDW

Characteristics of customers with propensity to
Customer
Segmentation, accept up-sell & cross-sell of varying products
Scoring, etc. under different situations

17
IBM Software Group

DW Solution Example

18
 IBM Software Group

Case Study: Churn Management Application

Business Problem - High Pre-pay Churn and IDD Defection

The wireless operator was facing increasing churn rates due to a price war and the
introduction of Mobile Number Portability (MNP).

The wireless market is saturated with mobile penetration over 100%!

Detailed analysis revealed that churning pre-pay subscribers were either “porting-out”
or using competitor IDD gateways.

Consequently the operator was experiencing high churn rates, reduced market share
and impacts to ARPU.

Working with IBM the Operator developed BI applications to:

Define and measure pre-pay churn rates

Identify the re-sell and/or transfer of pre-pay phones and SIM cards

Define and measure IDD defection by pre-pay users

Develop retention programs targeted at pre-pay users

19
 IBM Software Group

Case Study: Churn Management Application

ANTICIPATE DETECT ACT IMPROVE

Anticipate Detect Act: Improve:

– Churn Scoring: – MNP Porting – Preventive Treatment – Variance Analysis
• MNP Port-outs – IDD Defection – Reactive Treatment – Detection Trigger
• A/C and SIM – Call Behavior – Win-back Treatment Performance
transfers – Handset Changes & – Program Performance
• IDD Defection Transfers
– Service Complaints
– Dropped Calls

Critical Success Factors

Operator developed a comprehensive Churn Management Strategy including Churn
Prediction, Detection and Treatment Programs

This required stable baselines (e.g. customer segments, propensity models), along with
common business definitions across the process (e.g. churned customer)

Operator implemented the solution through a closed-loop approach to continuously
improve churn analytics and subsequent treatment programs

20
 IBM Software Group

Case Study: Churn Management Application

Detect Anticipate
Churn
ChurnScoring
Scoring&&
Score &
Segmentation
Segmentation
Segment

EDW Generate
OSS/BSS
Data Donkeys
Customer ETL Business Travelers
Customer Customer
Account Source Weekend Roamers
Account
Invoice Data Churn Propensity
Invoice Segments
CDR Account Credit Risk Rating
CDR & Scores Update Segments & Scores
Uptake Propensity
Upgrade Propensity

Invoice Usage

Interactions
Churn Analysis
Act Opening # of Accounts
Area Value
Closing # of Accounts
Load Load Behaviour Plan Tenure # of Churned Accounts
Load Leads &
Interactions and Scores IMEI Period Predicted Churn Score
Watch list Actual Churn Score
Interactions Churn Entitlement Balance
Interactions ChurnPrograms
Programs

Proactive/Reactive Churn Prevention & Win-

Win-back Improve

21
 IBM Software Group

Case Study: Churn Management Application

Significantly reduced churn rates and IDD defection.

Operator is collecting useful customer data at all Touch Points to produce
actionable Business Intelligence

Operator remains the most profitable in the market with the highest ARPU and
the lowest port-outs

Operator is up-selling pre-pay users to lower churn, higher value post-pay
‘premium’ brands

Operator is retaining IDD revenue through the managed release of targeted
IDD prefixes for market segments

22
IBM Software Group

23