1 How to manage the TELCO-KPI with Business Intelligence Solutions Juliana LULA(AHMETI) Business Intelligence Section, Plus Communication Albania [email protected] Abstract Nowadays, the telecommunications companies are focusing more and more to business intelligence solutions.Since they want to expand their services based on information per customer, it is very important to have analysis and deeper knowledge provided from their data.To be in the best position, these companies are normally going througha sophisticated customer relationship. They have a lot of information gathered for service networking, subscriber data, billing processed etc. It is a matter of using them in a proper way for gettingcompetitive advantages on information management and decision making process. BI is very helpful in such analysis and can provide easy way of access their enterprise data. This paper presents the current business landscape and an increasingly popular business model that use BI as a solution for their requirements.The key solution for which the companies are looking includes: customer retention, target marketing and campaign management. All of the above solutions require sophisticated analysis. As in other industries, to define how successful is the company and to improve organizational effectiveness, they use kind of measures called KPI. This paper explains the way of mapping some important KPI with the current data of the business. Through KPI monitoring, control and analysis the top managers of the business can have in the fastest way the answer of their questions and can get the right decision in the right moment. This paper illustrates the usage of BI with an example of a data mart. The paper covers the topic of its design and sample of some KPI provided. The data mart provides the platform for Online Analytical Processing (OLAP) analysis. The OLAP analysis can help the telecommunications company to get better insight into its customers’ behavior and improve its pricing strategies and marketing. Keywords:DWH, BI, ETL, Data Mart, KPI Introduction Telecommunication service industry around the world is facing significant challenges from competition, technological evaluation at very short frequencies and never-ending customer demands. Clients are very sensitive and dynamic. They can move from one company to the other because of their satisfaction from the services provided. It is almost 10 times more expensive to get a new customer than to retain one[8].The industry needs to align to the needs of the customer. With the increasing needs and expectations, customers tend to accelerate the competition in the marketplace [8].With better choices and varied options, customers are more inclined to look for better services. From this point of view, it is very hard and also challenging to keep them satisfied. Decision making process in such companies is crucial and it should be fast and accurate. For every mistake on it there is a cost behind and very often this can be irreversible.Systems like data warehouse and Business Intelligence can offer a better place on decision making process. Inmon defines: “A data warehouse [as] a subject-oriented, integrated, time-variant and nonvolatile collection ofdata in support of management’s decision making process.[1]” As per Forrester’s definition: “Business Intelligence is a set of methodologies, processes, architectures, and technologiesthat transform raw data into meaningful and useful information used to enable more effective strategic, tactical decision-making. [13]” Some popular features of BI technologies are reporting, online analytical processing,analytics, business performance management, predictiveanalytics and data mining.The main function of Business intelligence is to enable businesses to do better decision-making. Having a BI on top of a data ware house system is a perfect architecture for a company [6]. TelecommunicationKPIs and BI In a telecommunication operator, BI addresses the requirements of marketing, finance, customer service, sales and engineering departments in the areas of: customer profitability,customer attrition, customer segmentation, campaign analysis, forecasting,data mining. It can helpalso in CRM process. There are three steps to follow for a successful CRM [9]: 1. Identify the most profitable or potentially profitable customers for futureinteraction. 2. Understand their needs and buying patterns 3. Interact with them so as to meet all of their expectations. In this case BI system can be very helpful to provide all needed analysis and information required for the first step of CRM. It can offer an easy way of managing in detail the organization KPIs. Key performance indicators include all measures that are important to the business. Generally they are used to get a value for the unmeasured activities [6]. It is necessary for an organization to at least identify its KPIs. There are a lot of (KPI) that are so important for telecommunication company to measure and improve organizational effectiveness. The main key performance indicator of a telecom field can be categorized as thefollowing: call centre, systems and network performance analysis, capacity planning,revenue and financial analysis, customer satisfaction, quality andusage (airtime), coverage, marketing,service analysis, customer analysis. There are a lot of areas they can base their KPIs. They provide to managers the most important performance information to enable them or their stakeholders to understand the performance level of the organisation. KPIs should clearly link to the strategic objectives of the organisation and therefore help monitor the execution of the business strategy [8]. After identifying the most important KPIs, should start the analysis of mapping each KPI with the current data that company owns. The telecommunication companies are favorably positioned because of their access to large amounts of data[5]. There are primarily two sources in help of KPIs: -Network data that contain thelink status, availability, utilization, network latency, call set-up time, inter-exchange of data at operator peering points etc. -Subscriber data that are related to call and service usage, location, revenues This voluminous data can be a starting point for the operator. Using the right techniques in data extraction, cleansing, warehousing and reporting techniques, one operator can develop innovative solutions to address all the requirements of the market. 3 Case study We can focus on somesimple measures, we can provide by the usage of BI. They are related to customer segmentation. It is quite often that companies need to know their customer segmentation. This information is closely related to their future steps. Customer segmentation is the practice of dividing a customer base into groups of individuals that are similar in specific ways relevant to marketing, such as age, gender, spending habits and other parameters that are relevant to customer loyalty [10]. It is very importantfor marketing the segmentation. It is basically the subdivision of a market into discrete customer groups based on specific criteria [7]. With a segmented customer base and hence known characteristics and behavior patterns of the customers,promotions and products can be designed targeting a particular segment. In this way, it iseasier to measure whether the intended impact is achieved. The intended impact is usually usageincrease which in turn increases revenue generated [11]. Let’s focus on some KPI’s that are related to revenue, usage and segmentation: -ARPU Segmentation(Average Revenue Per User): Average revenue per user or average revenue per unit (ARPU) is an expression of the income generated by a typical subscriber or device per unit time in a telecommunicationsnetwork [2]. The ARPU provides an indication of the effectiveness with which revenue-generating potential is exploited. The value of this KPI per each segment of customers is important for identifying the target group of one promotion and further more to estimate the income generated. Also if analyze the customer segments for some continuous month, can identify the movement from one segment to the other one. The movement to lower segments can be a good indicator for churn analysis [8]. -MOU Segmentation (Minutes of Usage): It displays the average of usage in minutes in a specific time, per segment. This KPI it’s also very useful. If the analyses are related to some specific tariff plans, it can give the impression of how the customers of these tariff plans are behaving in terms of usage. It’s a good indicator that can be used also for marketing promotions offering better services. To manage these KPI, we should first analyze where to find the information needed for feeding the measures. Which is the datasource of information? How to map, transform and load the information for having ready the analysis? The solution of this issue is dimensional data model. The dimensional data model provides a method for making databases simple and understandable. We can imagine of a dimensional database as a database cube of some dimensions where end users can access a part of the database along any of its dimensions. To create a dimensional database, we need a model that lets you visualize the data.A data mart is a subset of a data warehouse [1]. To design it we have to follow the dimensional data model approach.It facilitates fast response to queriesfrom one or more simultaneous users [9]. After analysis of the requirementsrelated to previous KPIs, weshould design the bus Matrix that is an entry point on every DWH/BI development. It is a visual way to display the relation between the factsdimensions and measures.In our case the bus matrix is displayed inthe following table: Table. 1 The Bus matrix table Fact/Dimensions Time Customer Fact X Revenue Fact MOU Fact Tariff Plans X Category X Destination X SegTime X X Segments X X Customer X X X From the below table,we can identify the possible analysis of facts and measuresbased on each dimension. Indeed, dimensions define the axis of investigation of a fact [4]. Once the bus-matrix it setup, should start with logical and physical design of the entities. The database used for this data mart is Oracle. The following are the dimension tables created: - Time, stores the information of calendar. Day is the lowest granularity - Customer, stores the detailed information of customers - Destination, stores the destination of traffic events - Tariff Plan, stores the information of tariff plans applied for charging of traffic events - Categories, stores the information of category: postpaid, prepaid - Segmentation Time, stores the time information with the month being thelowest granularity - Segments Dimension, stores the details of the different segments that is the segment description,lower limit, upper limit and an indicator of what it’s measuring. Generally these segments are predefined list of values for revenue, minutes etc. Usually it is prepared from marketing andshould be load in the system frequently. Fact tables are as following: -Fact of customer, stores dynamic information of customer in terms of usage in destination and revenue generated for each day. - Fact of revenue, stores the revenue segment the customer belong to per each month - Fact of MOU, stores the usage segment the customer belongs to per eachmonth The Measures are: Amount, Duration, Minutes of usage The schema of the data mart is displayed in figure 1. 5 Fig. 1. The schema of data mart One important part of data warehousing is the ETL process.ETLis often a complex combination of process and technology that consumes a significant portion of the data warehouse development [5]. In our case Data stage is used as ETL. With data stage we can design all the jobs that do extraction, transformation and loading of data in data mart. ETL process follows the workflow shown in figure 2. Transform & Load Extract Billing System Stage Area Datamart Fig.2. ETL process workflow After data have been populated the data mart, they can be used by BItools for providing the results for the end user. So the dimensional model presented in figure 3, feeds the OLAP data cubeused for the purpose of analyses and reporting.It is an approach to quickly provide the answer to analytical queries that are dimensional in nature [2]. From this OLAP, we can provide different analysis from one, two or some combination of dimensions. We can very easily answer a lot of business questions and identify the real knowledgein help of CRM. Like: -Analysis 1: What about the customer segmentation revenue? Which are the high usage clients? What is their average revenue (ARPU)? What will be the target of the next promotion? These analyses can be provided by the following OLAP report based on created data mart. 3500 3000 2500 2000 1500 1000 500 0 0-50 50-100 100-200 200-300 300-400 400-500 500-600 600-800 800-1000 1000-1200 1200-1500 1500-2000 2000-2500 2500-3000 3000-4000 Number of Customer per Segment Fig. 3.Customer segmentation by revenue Action 1 From this result we can also have the ARPU calculated per each segment. Once you can identify the customers with high usage, the marketing can put all the efforts to this particular part of customers for their promotions and campaign. So for retention purpose, the company should be focused on this part, as it is the category that generates most of company revenue. -Analysis 2 Which is the target group to be focused for increasing the usage of the network? These analyses can be provided by the following OLAP report based on created data mart. 250 200 150 100 No_of_customer 50 0 Fig. 4. Customer segmentation by min of usage Action 2 For increasing usage of the network should be focused on medium and low customer. These categories are very likely to churn and move to other operators. They can see segmented customers according to revenue generated and minutes of use. Since OLAP can have drill down capacity, it is possible to go in detail for further analysis[12]. Conclusions Most of the telecommunication operators are facinglot of challenges by using BI, driven by the need to extract more intelligence out of their business data. The focus of BI is not only current information but also historical analysis and moreover the predictive analysis. Based on 7 these sophisticated techniques of data analysis these companies can easily setup, monitor and manage their business KPIs. It improves in direct way the decision making process. As a result, more and more companies are investing in the BI and analytics domain to develop innovative solutions that aid with customer satisfaction and retention, revenue leakage prevention. All the telecommunication KPI can be managed by a good BI solution. A better understanding of the customer base leads to personalization of services, products, campaignsand promotions there by enabling the acquisition of customers, retaining the existing onesas well as giving all a better service. With a rather wide customer base, this is not possible unlessthrough segmentation [7], hence the need to go the segmentation way. In the telecom industry today, there exist many choices of products which may not be of muchdifference when it comes to cost to the customer, hence customer retention schemes need to beput in place in order to maintain customers. Segmentation can help in such a way that one is ableto know the most valuable customers and hence accord them preferential treatment in terms ofcustomer service.A good understanding of the customer base through segmentation is an essential foundation foreffective customer strategies. BI therefore does improve profitability in the telecommunication industry and can help management to define strategies, support decision making, search for opportunities, identify problems and substantiate actions. However, this is dependent on the effective implementation of both BI process and BI technologies. It is very important the minimal understanding of BI by all the parties, especially by managementof the company. It is long lasting and expensive project for a company and needs a lot of support by the sponsorship. References [1] W.H. Inmon:“Building the Data Warehouse, 2nd edition, Wiley”, 1996, pp.127-156. [2] J. Han , M. Kamber:“Data Mining: Concepts and Techniques, 2nd edition”, 2006, pp. 234-240. [3] E. Franconi, U. Sattle:“A data warehouse conceptual data model for multidimensional aggregation. In Proc. of the Workshop on Design and Management of Data Warehouses”,1998. [4] A. Abello, J. Samos,F. Saltor:“A Data Warehouse Multidimensional Data Models Classification”, 2000. [5] R. Kimball:“The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses”, 2000. [6] M. George Marakas: “Decision support systems in the 21st century”, 2008. [7]Frost,,S.Sullivan:“Customer Segmentation”,2006, pp.195-198. [8] B. Claude: “Combating Customer Churn. Telecommunications (3)”, Horizon House Publications, 2000. [9] C. Normile: “Business Intelligence for the telecommunications Industry”, 2008. [10] C. Todman: “Designing a Data Warehouse: Supporting Customer Relationship Management, Prentice Hall, New Jersey”, 2000, pp. 72-80. [11] D. Camilovic: “A Call Detail Analysis – Getting Insight into Customer Behavior”, Tenth Annual International Conference of the Global Business and TechnologyAssociation, Madrid, 2008, pp. 183-190. [12]J.Lula,E.Arkaxhiu: “Data warehouse and BI on Business decision making”, International conference: Economic & Social challenges and problems 2010, pp 722-728. [13] Forrester research: “Business Intelligence”http://www.forrester.com. Juliana LULAgraduated at Faculty of Natural Sciences, Department of Computer Science in 2001. She holds an MSc diploma in Informatics and she is in a process of her PhD study. For some years, the focus of her academic study is business intelligence and decisions making system. During 2001-2010 she has been first lecturer in Informatics within the Department of Computer Science in the Faculty of Natural Sciences in Tirana. Currently she is working at Plus Communication Albania as Manager of Business Intelligence Section. She is the author of some articles that are focused on knowledge management, data warehouse and business intelligence.