CRM Depends on Data Warehouse
Piecing together information about customers from multiple systems for every analysis or marketing campaign creates inefficiencies and bottlenecks. Centrally storing a single, comprehensive view of customers can reduce demands on information technology and improve the efficiency of marketing and customer communication processes.
My last article described the importance of crafting a customer strategy prior to focusing on CRM technology. Once the strategy is in place, CRM cannot take place effectively without an investment in technology to access customer information. The foundation for CRM efforts is the customer data warehouse.
Yet studies on data warehousing that have been released during the past two months raise questions regarding the success of data warehouse projects. A study of 1,600 companies released in March by The Data Warehousing Institute, Seattle, found that 41 percent of respondents said they are experiencing difficulties with their implementation and 42 percent found that the data warehouse implementation met their expectations.
Another finding in this study caused greater concern: Only 13 percent of respondents track data warehousing return on investment and less than 40 percent said they plan to begin tracking ROI. As more companies seek to measure the return on their CRM spending, the pressure to implement data warehouses on time and within budget will increase.
If data warehouse implementation projects are expensive and have a high risk of going wrong, why would I recommend that any firm undertake such a project now?
A data warehouse is essential for a business to have consistently clean, reliable customer-level data that is continually accessible enterprisewide. The key is to plan and manage the warehouse implementation with risk and return in mind. Below is an outline of 10 steps to implement a data warehouse effectively.
The first three steps are combined into a define-the-project phase. This phase should take six to 12 weeks, depending on the complexity of a firm's customer data and the number of technical and business users interviewed to develop the business requirements. The time to complete it depends on the amount of time the project team and business users can devote to interviews. The deliverables for this phase of building a data warehouse are a working project team, a clear list of business requirements and a conceptual data model.
Assemble the team. The success of a data warehouse depends on a technical and business partnership that endures long after the construction of the warehouse is completed. The project implementation will require a team versed in technology, analysis and marketing and may include current and future users of customer data. The team must be large enough to include the appropriate knowledge and small enough to meet frequently and make decisions quickly.
Gather business requirements. The first order of business for the project team is to collect system requirements specific to each business unit that will use the warehouse. Business requirements are simple statements of what the system is expected to do. Each statement implicitly begins with "the system shall ... " and describes a discrete detail of a business need.
After interviewing the sales department, the sales requirements may include the statement, "provide summary reporting of the top 10 products sold each day/week/month," along with 20 or even 50 additional requirements for the sales function. Including every customer point of contact in the requirements-gathering process ensures long-term support for the data warehouse.
Define technical requirements. How each business unit plans to use the data warehouse will shape decisions about hardware and software, architecture and interfaces. Questions such as who will use the system, what level of technical or analytical expertise the user group possesses and what types of remote connections will be needed require consideration during this step.
At the end of this first phase, the project team can complete a conceptual data model that illustrates what tables will exist in the warehouse and how the tables typically will be related. In a banking environment, the conceptual data model may show a table for each product type (checking account and credit card account, for example). These account tables will be related to the customer table, as each customer may have one or more accounts.
The next three steps in building a data warehouse make up the understand-the- data phase. This phase may take another six to 10 weeks, depending on the volume and complexity of the customer data as well as what information is available about the data. The deliverables for this phase are collectively referred to as metadata, literally "data about the data."
Identify data requirements. In this step of the process, the project team converts the business and functional requirements gathered in the first phase into a logical data model. A logical data model is a picture showing all the tables in the data warehouse, all the data elements in each table and the relationships among the tables.
Several software packages are available that simplify producing logical data models. The time-consuming process in this phase is the review of the data model with the business users interviewed in the first phase. Though this may be a tedious step, it is important to gain approval and understanding from the business and technical users of the warehouse.
Create data maps. The data-mapping step of the implementation charts the movement of data from the source system, through data processing and conditioning, to its location in the data warehouse. This step also describes how data are extracted, transformed and loaded into the data warehouse. The result of this step will be a physical data model, showing how each element will be stored in the data warehouse.
Develop the data dictionary. Finally, the project team will develop a data dictionary -- the reference guide for designers, builders and users of the data warehouse. Data definitions are presented for each element, detailing exactly what the field represents using definitions that are common across systems, channels and time.
The final four steps in the process cover the execute-the-plan phase. In most projects, the two planning phases take about two-thirds of the project time, while executing the plan takes about one-third. The time to complete this phase will depend on how rapidly the firm is able to make decisions regarding technology and how willing the firm is to put the appropriate resources toward the data warehouse build. The deliverable for this phase is a populated, accessible data warehouse.
Determine whether to use outside support. Companies have several support options to choose from for the development and maintenance of their data warehouse. Consultants can handle design, development, software/hardware selection and implementation and can coordinate vendor relationships. Service bureaus can house your warehouse on their hardware on a contract basis, though access to your data may be limited.
Your firm needs to assess the support it needs from third parties based on the size and scalability of your warehouse needs, your available short-term and long-term resources and your budget.
Decide on software and hardware. Each firm will need to choose hardware and software from several combinations to meet the needs uncovered in the planning phases. Software packages to review include database servers, ETL tools and processors.
Database servers support data transport, querying and table management. Many are provided by large, well-known companies. Tools for extracting, transforming and loading data transfer the data from source systems to the data warehouse and may clean or reformat data on the way.
Perform individual warehouse build. During the first several builds of the warehouse, expect to fine-tune the production process. The production process is the automated building of the warehouse on a regular (nightly/weekly/monthly) basis.
In this step, the project team should celebrate successes and communicate setbacks to a wider audience in preparation for regular warehouse communications.
Develop a production calendar. After the first several builds of the warehouse, the project team can create a production calendar to let users know when and with what frequency data will be available for querying and reporting. The production calendar should also be widely shared.
Completing the customer warehouse merely places customer data into a box. Next, your firm must select and implement the tools that will allow you to extract and use customer information for analysis and marketing. The completed warehouse is the foundation for CRM, so celebrate its successful implementation and begin designing and building the rest of the CRM infrastructure to realize the return on your investment.