Telecom Analytics

1
Telecom Analytics by Arindam Guptaray
2
Few words about me . . .

• B. TECH FROM IIT KHARAGPUR.
• MBA (FINANCE) FROM UNIV. OF MINNESOTA, CARLSON
  SCHOOL.
• HAVE WORKED IN ANALYTICS FOR 10 YEARS
• 7 YEARS OF TELECOM ANALYTICS WITH PROVIDERS
  LIKE
• AIRTEL
• IDEA
• TELEFONICA
• ETISALAT
• HAVE WORKED IN SPEND ANALYTICS FOR 3 YEARS

Email guptaray_at_yahoo.com LinkedIn
http//www.linkedin.com/in/agriit
3
Few words about You!. . .

• Name
• Background Engineering, Arts, Commerce
• Work Experience
• Stream Analytics, MBA
• Expectations from the program
• Expectations from this Class

4
(No Transcript)
5
Agenda
DATE TOPICS
21st Jan STUDENT INTRODUCTIONS ANALYTICS WHAT DOES IT MEAN WHY TELECOM ANALYTICS DATA SOURCES FOR TELECOM ANALYTICS ASSIGNMENT (20 MARKS)
29th Jan STUDENT PRESENTATIONS TELECOM FRAUD ANALYTICS
3rd Feb TELECOM CHURN ANALYTICS TELECOM CAMPAIGN ANALYTICS PROJECT (40 MARKS) (R, MATLAB, SAS, etc.)
6
What is Analytics . . .
7
What is Analytics . . .

• Not everything that can be counted counts, and
  not everything that counts can be counted.
• - Albert Einstein
• Analytics is like the Game of Bridge. You can
  learn the rules of Bridge from a text book but
  when you are actually playing, its a totally
  different ball game.
• - Arindam Guptaray

• Analytics can be Deterministic or Probabilistic.
• In real life you will never get clean data.
• An analyst should be able to tell you something
  about your data that you dont know.
• A great analyst will be able to answer the follow
  up question
• So what?

8
Must have skills in Analytics . . .

• ABILITY TO DREAM IN EXCEL
• TALK IN STATISTICS
• WALK IN POWER POINT
• KNOW HOW TO VISUALIZE THE DATA
• FALL IN LOVE WITH DATA WAREHOUSING, DATA MINING
• PLAY WITH DATA USING SQL, R, MATLAB
• THE MOST IMPORTANT SKILL gt STRONG ANALYSIS
  CAPABILITY

9
Why Telecom Analytics . . .
10
Why Telecom Analytics . . .

• COMPETITION
• AVAILABILITY OF INTERLINKED DATA SOURCES
• DEMANDING CUSTOMER
• GOVERNMENT REGULATIONS
• AVAILABILITY OF HARD-WARE FOR ANALYTICS
• HIGH VOLUME OF DATA MAKES IT SUITABLE TO USE
  STATISTICAL CONCEPTS.

11
Why Telecom Analytics . . .
12
Post Paid Billing Process . . .
13
Pre Paid Billing Process . . .
14
Data Sources for Telecom Analytics . . .

• SWITCH CDR
• IN CDR
• RATED CDR (POSTPAID)
• SUBSCRIBERS INFORMATION (PREPAID, POSTPAID)
• SUBSCRIBER SERVICES
• RATE PLANS
• DIAL DIGIT LOOKUP
• TOWER LOCATIONS INFORMATION
• CRM DATABASES
• PAYMENT INFORMATION

15
Switch Call Data Record ( Switch CDR) . . .

• For every mobile transaction a record is
  generated in Switch. These are called Switch
  Call Data Records or Switch Call Detail Records
  (CDR). The key fields of a Switch CDR are
• Called Number The number receiving the Call.
• Calling Number The number initiating the Call.
• (A number is called MSIDN Mobile Subscriber
  Integrated Services Digital Network-Number)
• IMSI (International Mobile Subscriber Identity)
  The subscriber SIM card number.
• IMEI (International Mobile Station Equipment
  Identity) The unique identifier for your mobile
  phone.
• Cell ID The unique ID of the cell tower
  transmitting the call.
• Call Status Success, Failure, Missed.
• Call Time The time of the call.
• Call Duration Duration of the call.

16
IN Call Data Record ( IN CDR) . . .
FOR EVERY PRE-PAID TRANSACTION A CDR IS
GENERATED IN IN. THE KEY FIELDS OF AN IN CDR
ARE CALLED NUMBER, CALLING NUMBER, DURATION,
CALL TIME. RATE PLAN THE RATE PLAN TO WHICH
THE SUBSCRIBER BELONGS. CHARGE AMOUNT
CHARGED TO THE SUBSCRIBER. RECHARGE AMOUNT
AMOUNT RECHARGED. DEDICATED BALANCE CAPTURES
FREE TALK-TIME, SMSS, DATA USAGE, ETC.
17
Rated Call Data Record ( Rated CDR) . . .

• ALL OF THE SWITCH POST PAID CDRS ARE PASSED TO A
  RATING ENGINE WHERE THESE ARE RATED BASED ON THE
  SUBSCRIBERS RATE PLAN.
• CALLED NUMBER, CALLING NUMBER, DURATION, CALL
  TIME.
• RATE PLAN THE RATE PLAN TO WHICH THE SUBSCRIBER
  BELONGS.
• CHARGE AMOUNT CHARGED TO THE SUBSCRIBER.

18
Subscriber Information . . .

• POSTPAID AND PREPAID SUBSCRIBERS ARE USUALLY
  STORED SEPARATELY. THESE ARE THE KEY FIELDS.
• MSISDN
• IMSI
• FIRST USE DATE
• LAST USE DATE
• ACTIVE IN HLR (HOME LOCATION REGISTER)
• ACTIVATION DATE
• DEACTIVATION DATE
• ACCOUNT NUMBER

19
Subscriber Services . . .

• CONTAINS THE SERVICES THAT SUBSCRIBERS SUBSCRIBE
  TO. FOR EXAMPLE
• UNLIMITED OFF PEAK SMS
• 2 GB OF DATA USAGE
• FREE ROAMING
• INTERNATIONAL CALLING

• THESE ARE THE KEY FIELDS
• MSISDN
• IMSI
• SERVICE NAME
• ACTIVATION DATE OF THE SERVICE
• DE-ACTIVATION DATE OF THE SERVICE

20
Rate Plans . . .

• THE RATE PLAN TABLE CONTAINS INFORMATION ABOUT A
  CALL WILL BE CHARGED. THE KEY FIELDS ARE
• DIAL DIGIT CODE DETERMINE THE TYPE OF THE CALL,
  DESTINATION COUNTRY.
• CHARGES PER UNIT USAGE
• PROVIDER OF THE OTHER PARTY INVOLVED IN THE CALL
• TYPE OF HANDSET OF THE OTHER PARTY (LANDLINE,
  MOBILE)
• START TIME(HOUR) AND END TIME(HOUR) WHEN THE RATE
  IS APPLICABLE.

21
Dial Digit Lookup

• The telecom provider needs to determine the
  following
• Whether the call is a Local, STD or ISD call.
• Provider for the other party involved in the
  call.
• For countries where number portability is allowed
  there are services available from where you can
  determine the provider. This is not a direct data
  source. The key fields that are available in
  this table
• Dial Digit Code
• Country of other party
• Provider of the other party

22
Cell Information . . .

• INFORMATION ABOUT THE CELL TOWER USED FOR THE
  CALL. THE KEY FIELDS ARE
• LATITUDE
• LONGITUDE
• COVERAGE RADIUS

23
Payment Information . . .

• KEY FIELDS ARE
• ACCOUNT NUMBER
• PAYMENT HISTORY
• PAYMENT TYPE (CASH, CHECK, CREDIT CARD, BANK
  ACCOUNT)
• PAYMENT DATE
• DUE DATE
• PAYMENT STATUS

24
CRM Database. . .

• THE CRM DATABASE CONTAINS DEMOGRAPHIC, GEOGRAPHIC
  AND OTHER FINANCIAL INFORMATION ABOUT THE
  CUSTOMER. SOME OF THE ATTRIBUTES OF A CRM RECORD
  ARE
• ACCOUNT NUMBER
• GENDER
• DATE OF BIRTH
• MONTHLY INCOME
• WORK NUMBER
• HOME NUMBER
• ADDRESS

25
Assignment (20 marks) . . .
Analyze the sample phone bill and present the key
observations. Slide 1 Overview Tools
Used Preprocessing Problems Encountered Overvie
w of the analysis Slide 2 Descriptive Analysis
Time of the call Peak, OffPeak Data Usage SMS
Usage Called Numbers Called Destinations Cost of
calls, data usage. Type of Usage (Roaming, Local,
International Roaming) etc Slide 3 Review the
current plan to determine how well the plan is
tailored for this person.
26
Assignment Cont (20 marks) . . .

• Slide 4
• Suggest a real (preferred, can be non Vodafone)
  plan that best suits this person. Or justify why
  the current plan is best by comparing with 3
  other plans.
• Slide 5
• Try predicting the following with justification
• Gender
• Approximate Age
• Kind of phone
• Any other information
• Distribution of marks
• Slide 1 2
• Slide 2,3,4 3
• Slide 5 5 (will be on a curve)
• Overall Presentation 4
• You will need to make a 10 mins presentation. You
  will be penalized 2 marks per min if the
  presentation exceeds 12 mins or is less than 8
  mins.

27
Sample Slides
28
SUPPLIER ANALYSIS TOP 20 SUPPLIERS
TOP 20 SUPPLIERS BY SPEND (LAST 12 MONTHS)
WHAT IS THE NUMBER OF SUPPLIERS OF TOTAL IN
EACH SPEND BUCKET (LAST 12 MONTHS)?
86m
271m
10m
234m
201m
803m
SPEND
34
29
1
11
25
100
SPEND
601
268
218
4
22
89
PARENT SUPPLIERS
gt 5M
2.5M - 5M
1M - 5M
lt 100K
100K - 1M
TOTAL
29
MARKET SECTOR OVERVIEW
MARKET SECTOR 2012.Q1 2012.Q2 2012.Q3 2012.Q4
HUMAN RESOURCES AND PROFESSIONAL SERVICES 150.18m 124.83m 136.34m 148.61m
I.T. PRODUCTS SERVICES 24.53m 30.26m 20.71m 18.32m
PROPERTY MANAGEMENT 16.57m 14.31m 19.77m 13.90m
ENVIRONMENTAL PRODUCTS AND SERVICES 12.55m 7.20m 9.63m 15.22m
FINANCE SERVICES 5.64m 7.58m 4.89m 4.94m
TRAVEL AND MEETING PRODUCTS AND SERVICES 2.64m 1.88m 2.29m 1.80m
COMPLIANCE PRODUCTS AND SERVICES 1.74m 1.97m 1.32m 1.37m
INDIRECT PROCUREMENT (UNMATCHED) .45m .42m .44m .36m
30
MARKET SUB-SECTOR SECTOR DETAILS I.T. PRODUCTS
SERVICES
INFORMATION TECHNOLOGY (UNMATCHED)
SOFTWARE
APPLICATION DEVELOPMENT MANAGEMENT SERVICES
MANAGED NETWORK PRODUCTS SERVICES
(TELECOMMUNICATIONS)