1. 1 Introduction to Customer Relationship Management
A complete CRM can be divided into three parts: operational CRM, collaborative CRM and analytical CRM. Operational CRM is the most basic functional system in CRM, which provides the whole CRM process management function, mainly providing automation of customer-centered business processes such as marketing, sales, service and support. Collaborative CRM takes customer service center as the main form and computer telephone integration as the core, which enables customers to interact with enterprises faster and more effectively through telephone, fax, e-mail and website.
Analytical CRM is to build a customer-centric data warehouse by integrating customer-related data stored in operational CRM, collaborative CRM, other enterprise application systems and external data sources, and obtain a consistent view of customer data within the enterprise. Based on integrated customer data, it obtains knowledge about customers by means of query and report analysis, OLAP analysis and data mining, so as to provide customers with personalized products and services, improve customer satisfaction and loyalty, and maximize customers' lifelong value. This paper mainly aims at analytical CRM.
1.2 the necessity of applying data warehouse in CRM
Data warehouse is the central link of CRM, even the soul of CRM. It stores all kinds of data inside and outside the enterprise, and organizes these source data into a consistent customer information base that changes with time to optimize the analysis to the maximum extent. Through OLAF analysis and data mining, we can find hidden rules in a large number of customer information and provide support for enterprises to make business decisions. On the other hand, it effectively separates the business platform of CRM from the analysis platform, so that the business database can focus on transaction processing, which not only improves the efficiency of transaction processing, but also optimizes the ability of analysis and processing.
In the traditional enterprise transaction processing system department, each department keeps a part of data according to its own transaction processing needs, and the relationship between each module is not close. Although some customer information can be obtained from these systems, it is far from meeting the demand. For example, a typical analysis of customer behavior usually requires more daily cumulative data reflecting historical changes for effective analysis. The traditional teaching database system is difficult to do this (whether from the perspective of data storage or data integration). Therefore, the introduction of data warehouse is inevitable.
1.3 framework of analytical CRM
It is the basic task of analytical CRM to introduce data warehouse technology into the management and organization of customer information, that is, to establish a customer information data mining warehouse for CRM application system and realize the integration and unification of customer information from various segmentation applications inside and outside the enterprise. As shown in figure 1, it is the framework of analytical CRM. Among them, the customer information data warehouse is the core of analytical CRM, and its main task is to extract data from OLTP system, convert the extracted data into a unified format, and load it into the data warehouse environment (the above three steps are called ETL, namely extraction, conversion, loading, extraction, conversion and loading), and manage and maintain the data in the data warehouse. Finally, through OLAP analysis and data mining of these data, enterprise managers can get a lot of valuable information, so as to better serve customers.
When building the data warehouse, the extensible data warehouse architecture is adopted here, that is, the middle layer includes two types of databases: one is the basic data warehouse containing multiple topics; Another subordinate data mart on a specific topic. As shown in figure 1, 4,000 data marts are designed according to the four themes in the data warehouse. Using extensible architecture can shorten the construction period of data warehouse, reduce the cost, and avoid the problems of establishing data mart directly without data warehouse, poor scalability and difficulty in keeping synchronization among multiple teaching materials fairs.
2 Design of customer information data warehouse
The first step in designing a customer information data warehouse is to establish a theme. Theme is an abstract concept, which is the object of synthesizing, classifying and analyzing the data in enterprise information system at a higher level. The design of data warehouse should start with the data in the running environment and determine the theme of data warehouse according to the actual needs of decision support. According to the function of analytical CRM, customer information data warehouse includes four themes: customer development, customer purchase, product and marketing. Among them, the customer purchase theme mainly analyzes the customer's purchase behavior from different angles, such as the relationship between the customer's purchase behavior and the customer's background information, mainly including the customer's education level, income level, age, gender, married or not, etc. In the customer information data warehouse model, the design is divided into three steps, and the key is the conceptual model, logical model and physical model design. Aiming at the online bookstore, taking the customer purchase theme as an example, a complete design scheme of customer information data warehouse model is given.
2. 1 conceptual model design
The purpose of conceptual model design in data warehouse design is to determine the subject-oriented information envelope. As a universal, consistent and compact conceptual model design tool, information package diagram can clearly reflect the needs of users and the various elements and their relationships needed to realize this demand. The package graph consists of name, dimension, category and measure, where category represents the hierarchical structure of dimension.
The schematic diagram of the theme information package purchased by customers in the online bookstore customer information data warehouse is shown in Figure 2. Among them, there are three ways to classify books, the first two are more common, and the other is to classify books according to their existing forms, which are divided into ordinary books, Vbook and Ebook. Ordinary books are traditional paper books, e-books refer to e-books based on computers and networks, and V-books are new carriers of multimedia presentations, training and business exchanges, with audio and video functions, such as lectures by experts in various fields, teaching and examination training courses. With the popularization of computers and the development of networks, e-books and Vbook are increasingly favored by readers.
2.2 Logical model design
The logical model of data warehouse generally includes star model and snowflake model. Star model is a data organization form of OLAP-oriented multidimensional data model based on relational database. It consists of fact tables and multiple dimension tables. By using fact tables including topics and multidimensional tables containing irregular descriptions of facts to perform decision support queries, the query performance is higher than that of highly standardized design structures.
Although the snowflake model is more consistent with the standardized design structure than the star model, it increases the complexity of the query and reduces the performance of the query. Therefore, the star model is adopted here.
The establishment of star model should be based on the envelope in conceptual model, and the package graph should be transformed into star model. The specific method is as follows: put the measurement entity in the package diagram at the center of the star model, and put the dimension entity in the package diagram at the periphery of the measurement entity. Logical model of customer purchase theme in customer information data warehouse.
2.3 Physical model design
Physical model is the storage form and organization form of teaching materials in data warehouse. To design a physical model, the structure of fact table and dimension table should be determined on the basis of star model or snowflake model; Clarify the data fields, data types, associated fields and index structures of the two; Determine the storage structure of the cube in the data warehouse, such as physical access mode, data storage structure, data storage location and whether the storage allocation is partitioned. When designing the physical model, I/O access time, space utilization and maintenance cost should be considered.
At present, most data warehouses are based on relational databases, and the storage of basic data and final data is managed by database systems. In the design of this data warehouse, MSSQLServer2000 and its component analysis server are selected as the management system of database and data warehouse. Logically, the data warehouse is multidimensional, but in physical storage, there are three ways to store its multidimensional data sets: Relational Online Analytical Processing (ROLAP), Multidimensional Online Analytical Processing (MOLAP) and Hybrid Online Analytical Processing (HOLAP).
In this data warehouse, the storage mode of cubes is HOLAP, that is, the basic data is stored in the original relational database and aggregated and stored in the multidimensional structure on the analysis server. This can not only avoid data duplication, but also improve the query performance (because aggregation is stored in a multidimensional dataset), which will have a great impact on performance only when detailed data is frequently accessed.
3 reality
For online bookstore, the realization of this data warehouse is based on MSSQLServer2000 platform. The ETL process of importing source data into data warehouse is realized through DTS service and VBScript in SQLServer. Establish a cube through AnalysisServices, realize OLAP operation, and support Multidimensional Expression (MDX) query. By automatically constructing MDX statements, OLAP operations such as scrolling, drilling, slicing, dicing and rotating are realized.
The customer information data warehouse * * * contains four topics: customer development, customer purchase, products and marketing, and OLAP analysis examples of customer purchase topics. Among them, users can analyze five measurement indicators: customer's purchase quantity, amount, cost, profit and average unit price from five dimensions: customer's location, age, gender, marital status, occupation, annual income, membership star rating, book I classification by content, book I classification by publishing house and book I classification by existing form and time.
In addition, by using the PivotTable service provided by AnalysisServices, users can develop their own front-end data presentation programs in VB or other languages, or directly use existing tools, such as Excel and Access in MSOffice suite, to realize the data presentation function of multidimensional data sets, so as to conveniently obtain various statistical reports and analysis charts. Using Excel to show the profit analysis of different kinds of books purchased by customers of different ages in 2005.