Prepaid Churn Model With Oracle Data Mining

1. Prepaid Churn ModelWith Oracle Data Mining NecdetDenizHalıcıoğlu deniz.halicioglu@turkcellteknoloji.com.tr September 21, 2010

2. Agenda About Turkcell Technology Churn Prediction Existing Mining System in Turkcell Data Mining with ODM SVM Model Conclusion

3. Agenda About Turkcell Technology Churn Prediction Existing Mining System in Turkcell Data Mining with ODM SVM Model Conclusion

4. AboutTurkcellTechnology Turkcell Technology has more than 15 years of development experience with its solutions applied and proven at leading operators in more than 10 countries. More than 10 years of experience in Turkcell ICT TTECH Center was put into service HC: 255 engineers Focus: Turkcell Group Focus: Turkcell & Telia Sonera Group + Regional Sales HC: 360 engineers 1994 - 2006 2007 2008 2009 Today TTECH was formed with 44 engineers in TÜBİTAK-MAM Technological Free Zone Focus: Turkcell Focus: Turkcell & Telia Sonera Group HC: 321 engineers

5. Areas of Competency From assisting the operation of network resources to improving business oriented intelligence, TTECH’s experts provide an expanding portfolio of packaged and custom solutions for telecom network operators. Network Services & Enablers SIM Asset & Services Management Mobile Marketing Mobile Internet & Multimedia Business Intelligence & Support Systems

6. Turkcell Technology IMS Group • More than 10 years of BI experience in Telecommunications industry • Designed, Built and Running one of the largest data warehouses in telecom industry • Team of morethan 100 highly talented professionals and consultants • Has a proven record of success in BI operations • Flawless operation, providing data for finance and even for NYSE • Early adopter of the new BI trends • Complex Event Processing,Text Mining, etc.

7. Agenda About Turkcell Technology Churn Prediction Existing Mining System in Turkcell Data Mining with ODM SVM Model Conclusion

8. What Makes Churn Prediction So Crutial? Everybody faces the same difficulties… Competition Forming Customer Loyalty High cost of customer acquisition Optimizing budget for customer retention People don’t want to hear any more

9. Basics of Churn Prediction Churn prediction starts with turning an abundance of data into valuable information and continues as a cyclic process. Preprocessing Preparation Mining Information • Define variable pool • Perform mining ETL • Attribute Importance • Normalization • Outlier Detection • Missing Value Cleanup • Build • Test • Apply Data Action

10. Success Criteria

11. Agenda About Turkcell Technology Churn Prediction Existing Mining System in Turkcell Data Mining with ODM SVM Model Conclusion

12. Pain Points About Existing Mining System • Too much manual effort: A new project for every new miningactivity • SAS licensing • Not leading, but lagging the business • Administrative overhead of distributed mining environment • Network overhead • Decoupled process monitoring • Data quality problems E-DWH DM-DWH SAS Server EndUsers

13. Approach in Existing Churn Model

14. Agenda About Turkcell Technology Churn Prediction Existing Mining System in Turkcell Data Mining with ODM SVM Model Conclusion

15. Give a TrytoOracle Data Mining • Motivations • Building an automated mining framework based on our Oracle database experience instead of maintaining manual mining model cycle. • No extra licensing cost (under ULA). • High speed (close to real time) mining with database embedded mining. • Centralized mining activity monitoring & administration.

16. Our Proposal for Data Mining Framework Oracle

17. Choosing Attributes with Attribute Importance --Perform EXPLAIN operation BEGIN DBMS_PREDICTIVE_ANALYTICS.EXPLAIN(data_table_name => 'census_dataset', explain_column_name => 'class', result_table_name => 'census_explain_result'); END; / --View results SELECT*FROM census_explain_result; COLUMN_NAME EXPLANATORY_VALUE RANK -------------- --------------------- • IN_REF_NUMDAYSSINCELASTREFILL.1412009041 • DT_SUB_ACTIVATIONDATE.0282003032 • IN_MNP_PORTINTENURE.0261780933 • NM_SUB_ACTIVATIONREASON.025882544 4 • IN_MNP_TCELL_TENURE.0252798365 . . .

18. Our Top 5 AfterAI

19. Build & ApplytheSVM Model --Perform PREDICT operation DECLARE v_accuracy NUMBER(10,9); BEGIN DBMS_PREDICTIVE_ANALYTICS.PREDICT(accuracy => v_accuracy, data_table_name => 'census_dataset', case_id_column_name => 'person_id', target_column_name => 'class', result_table_name => 'census_predict_result'); DBMS_OUTPUT.PUT_LINE('Accuracy = ' || v_accuracy); END; / --View first 10 predictions SELECT * FROM census_predict_result WHERErownum < 10; PERSON_ID PREDICTION PROBABILITY ---------- ---------- ----------- 2 1 .418787003 7 0 .922977991 8 0 .99869723 9 0 .999999605 10 0 .9999009 5 rows selected.

20. Other Remarks on ODM • No need to perform manual attribute processing in many cases • EDP : Embedded data preparation • ADP : Automatic data preparation • PL/SQL or Java based code generation • SAS to ORACLE model import • Eliminates data Movement • Eliminates data duplication • Preserves security

21. Agenda About Turkcell Technology Churn Prediction Existing Mining System in Turkcell Data Mining with ODM SVM Model Conclusion

22. Creating the Case Table Variable Pool (400 variables) PREPAID and INDIVIDUAL and (ACTIVE or MOC-BARRED) Filtered Variable Pool Historic Churn Table JOIN MONTH(N)=MONTH(N+1) CASE TABLE

23. Building the SVM Model • CASE TABLE • 400 Attributes • Unique Identifier • Target Churn Value ATTRIBUTE IMPORTANCE CASE TABLE (180 ATTRIBUTES) FEB DATA  MAR CHURN MAR DATA  APR CHURN APR DATA  MAY CHURN MAY DATA  JUN CHURN COMBINE DIFFERENT DATASETS BUILD SVM MODEL

24. ODM on Oracle Exadata v2 • Initially we have used a large Solaris (100+ UltraSparc 7 cores and 640 GB memory) box to build our first SVM models: • It took 29 hours to complete model build & apply. • On Exadata this reduces to a few hours. • Mainly due to enormous improvement in data preparation stage.

25. Agenda About Turkcell Technology Churn Prediction Existing Mining System in Turkcell Data Mining with ODM SVM Model Conclusion

26. ToSumUp • Churn prediction over various customer groups is and will be the focus of Turkcell • Embedded data mining with ODM is • Faster • More Robust (due to stability of SVM algorithm) • Easier to automate • Easier to manage

27. Thanks for his contribution Hüsnü Şensoy, VLDB Expert husnu.sensoy@globalmaksimum.com Data & Information Technologies To learn more on SVM theory

28. Turkcell Technology Research and DevelopmentTÜBİTAK MAMTeknolojiSerbest Bölgesi Gebze –Kocaeli TURKEY' : +90 (262) 677 40 007 :+90 (262) 677 40 018 : www.turkcelltech.com THANK YOU!