Download
1 / 30
300 likes | 400 Views
Telecom Analytics – by Arindam Guptaray. 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
E N D
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@yahoo.com LinkedIn: http://www.linkedin.com/in/agriit
Few words about You!. . . • Name • Background – Engineering, Arts, Commerce • Work Experience • Stream – Analytics, MBA • Expectations from the program • Expectations from this Class
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, it’s 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 don’t know. • A great analyst will be able to answer the follow up question: • “So what?”
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 => STRONG ANALYSIS CAPABILITY
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.
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
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.
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.
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.
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
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
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.
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
Cell Information . . . • INFORMATION ABOUT THE CELL TOWER USED FOR THE CALL. THE KEY FIELDS ARE: • LATITUDE • LONGITUDE • COVERAGE RADIUS
Payment Information . . . • KEY FIELDS ARE: • ACCOUNT NUMBER • PAYMENT HISTORY • PAYMENT TYPE (CASH, CHECK, CREDIT CARD, BANK ACCOUNT) • PAYMENT DATE • DUE DATE • PAYMENT STATUS
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
Assignment (20 marks) . . . • Analyze the sample phone bill and present the key observations. • Slide 1: Overview • Tools Used • Preprocessing • Problems Encountered • Overview 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.
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.
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 $201m $234m $803m $ SPEND 34% 29% 11% 25% 1% 100% % SPEND 601 268 218 4 22 89 # PARENT SUPPLIERS $2.5M - $5M > $5M $1M - $5M $100K - $1M < $100K TOTAL
MARKET SUB-SECTOR SECTOR DETAILS : I.T. PRODUCTS & SERVICES INFORMATION TECHNOLOGY (UNMATCHED) SOFTWARE APPLICATION DEVELOPMENT & MANAGEMENT SERVICES MANAGED NETWORK PRODUCTS & SERVICES (TELECOMMUNICATIONS)
