This article throws light upon the six steps to be followed to develop a customer-related database. The steps are: 1. Define the Database Functions 2. Define the Information Requirements 3. Identify the Information Sources 4. Select the Database Technology and Hardware Platform 5. Populate the Database 6. Maintain the Database. 
Step # 1. Define the Database Functions:
Databases support the four forms of CRM – strategic, operational, analytical and collaborative. Strategic CRM needs data about markets, market offerings, customers, channels, competitors, performance and potential to be able to identify which customers to target for customer acquisition, retention and development, and what to offer them.
Collaborative CRM implementations generally use the operational and analytical data as described below, so that partners in distribution channels can align their efforts to serve end-customers. Customer-related database is necessary for both operational and analytical CRM purposes. Operational CRM uses customer-related database to help in the everyday running of the business.
For example:
i. a telecoms customer service representative (CSR) needs to access a customer record when she receives a telephone query
ii. a hotel receptionist needs access to a guest’s history so that she can reserve the preferred type of room – smoking or non-smoking, standard or deluxe
iii. a salesperson needs to check a customer’s payment history to find out whether the account has reached the maximum credit limit.
iii. Analytical CRM uses customer-related database to support the marketing, sales and service decisions that aim to enhance the value created for and from customers. For example:
iv. the telecoms company might want to target a retention offer to customers who are signalling an intention to switch to a different supplier
v. the hotel company might want to promote a weekend break to customers who have indicated their complete delight in previous customer satisfaction surveys
vi. a sales manager might want to compute his sales representatives ‘customer profitability, given the level of service that is being provided. Customer-related database are typically organized into two subsets, reflecting these operational and analytical purposes.
Operational data resides in an OLTP (online transaction processing) database, and analytical data resides in an OLAP (online analytical processing)database. The information in the OLAP database is normally a summarized, restructured, extract of the OLTP database, sufficient toper form the analytical tasks. The analytical database might also draw in data from a number of internal and external sources.
OLTP data needs to be very accurate and up to date. When a customer calls a contact center o enquire about an invoice, it is no use the CSR telling the customer what the average invoice is for a customer in her postcode. The customer wants personal, accurate, contemporary, information. OLAP data bases can perform well with less current data.
Step # 2. Define the Information Requirements:
The people best placed to answer the question’ what information is needed?’ are those who interact or communicate with customers for sales, marketing and service purposes, and those who have to make strategic CRM decisions.
A direct marketer who is planning an e-mail campaign might want to know open and click-through rates, and click-to-open rates (CTOR) for previous e-campaigns, broken down by target market, offer and execution. She would also want to know e-mail addresses, e-mail preferences (html or plain text), and preferred salutation (first name ?Mr? Ms?).
Operational and analytical needs like these help define the contents of customer-related databases. Senior managers reviewing your company’s strategic CRM decisions will require a completely different set of information. They may want to know the following.
How is the market segmented? Who are our current customers? What do they buy? Who else do they buy from? What are our customers ‘ requirements, expectations and preferences across all components of the value proposition, including product, service, channel and communication? With the advent of packaged CRM applications, much of the database design work has been done by the software vendors.
The availability of industry-specific CRM applications, with their corresponding industry specific data models, allows for a much closer fit with a company’s data needs. Where there is a good fit out of the box, the data base design process for both operational and analytical CRM applications becomes one of implementing exceptions that have been overlooked by the generic industry model.
Some CRM vendors have also built in the extract, transform and load processes to move information from OLTP to OLAP databases although it is highly likely that a client will need to modify and customize the standard processes.
Customer information fields:
Most CRM software has predefined fields in different modules, whether for sales, marketing or service applications. For example, in a sales application, a number of fields (columns) of information about customers are common: contact data, contact history, transactional history, current pipeline, future opportunities, products and communication preferences.
Contact data:
Who is the main contact (name) and who else (other names) is involved in buying decisions? What are their roles? Who are the decision-makers, buyers, influencers, initiators and gatekeepers? What are the customer’s invoice addresses, delivery addresses, phone numbers, fax numbers, e- mail addresses, street addresses and postal addresses?
Contact history:
Who has communicated with the customer, when, about what, in which medium and with what outcome?
Transactional history:
What has the customer bought and when? What has been offered to the customer, but not been purchased?
Current pipeline:
What opportunities are currently in the sales pipeline? What is the value of each opportunity? What is the probability of closing? Is there a10 per cent, 20 per cent … 90 per cent chance of making a sale? Some CRM applications enable sales people to allocate red, amber or green signals to opportunities according to the probability of success.
Opportunities:
Whereas ‘ transactional history ‘ looks backwards, ‘ opportunity ‘ looks forwards. This is where opportunities that have not yet been opened or discussed are recorded.
Products:
What products does the customer have? When were these products purchased, and when are they due for renewal? Have there been any service issues related to these products in the past?
Communication preferences:
What is the preferred medium of communication – mail, telephone, email, face-to-face, etc.? If it is e-mail, is plain text or html preferred? What is the preferred salutation? And the preferred contact time and location? Customers may prefer you to contact them by phone for some communications (e.g. an urgent product recall), by mail for others (e.g.invoicing), by e-mail (e.g. for advice about special offers) and face-to face for other reasons (e.g. news about new products).
These preferences can change over time. When a customer’s preferences are used during customer communications, it is evidence that the company is responsive to customer expectations. Many companies allow customers to opt in to, or out of, different forms of communication. Customers may prefer to adjust their own preferences. Amazon(dot)com, for example, allows customers to opt to receive e-mail about six different types of content: terms and conditions of shopping at Amazon; new products; research surveys; magazine subscription renewal notices; information about and from Amazon’s partners and special offers.
Step # 3. Identify the Information Sources:
Information for customer-related databases can be sourced internally or externally. Prior to building the database it is necessary to aud it the company to find out what data are available. Internal data are the foundation of most CRM programmes, though the amount of information available about customers depends on the degree of contact that the company has with the customer. Some companies sell through partners, agents and distributors and have little knowledge about the demand chain beyond their immediate contact. Internal data can be found in various functional areas.
i. Marketing might have data on market size, market segmentation, customer profiles, customer acquisition channels, marketing campaign records, product registrations and requests for product information.
ii. Sales might have records on customer purchasing history including regency, frequency and monetary value, buyers ‘ names and contact details, account number, SIC code, important buying criteria, terms of trade such as discounts and payment period, potential customers(prospects), responses to proposals, competitor products and pricing, and customer requirements and preferences.
iii. Customer service might have records of service histories, service requirements, customer satisfaction levels, customer complaints, resolved and unresolved issues, enquiries, and loyalty programme membership and status.
i. Finance may have data on credit ratings, accounts receivable and payment histories.
ii. Your webmaster may have click-stream data.
Enhancing the data:
External data can be used to enhance the internal data and can be imported from a number of sources including market research companies and marketing database companies. The business intelligence company Claritas, for example, offers clients access to their Behaviour bank and
Lifestyle Selector databases:
These databases are populated with data obtained from many millions of returned questionnaires. Experian, another intelligence company, provides geo-demographic data to its clients. External data can be classified into three groups:
1. compiled list data
2. census data
3. modelled data.
1. Compiled list data:
Compiled list data are individual level data assembled by list bureaux or list vendors. They build their lists from a variety of personal, household and business sources. They might use local or council tax records, questionnaire response data, warranty card registrations or businesses’ published annual reports. Lists can be purchased outright or rented for a period of time and a defined number of uses. Once the list or its permitted use has expired, it must be removed from the database.
If you were a retailer thinking of diversifying from leisurewear into dancewear and had little relevant customer data of your own, you might be interested in buying or renting data from an external source.
Data could have been compiled by the bureau or vendor from a variety of sources, such as:
i. memberships of dance schools
ii. student enrolments on dance courses at school and college
iii. recent purchasers of dance equipment lifestyle questionnaire respondents who cite dance as an interest
iv. subscribers to dance magazines
v. purchasers of tickets for dance and musical theatre.
2. Census data:
Census data are obtained from government census records. In different parts of the world, different information is available. Some censuses are unreliable; others do not make much data available for nongovernmental use. In the USA, where the census is conducted every ten years, you cannot obtain census data at the household level, but you can at a more aggregated geo demographic level, such as zip code, census tract and block group.
Census tracts are subdivisions of counties. Block groups are subdivisions of census tracts, the boundaries of which are generally streets. In the USA there are about 225 000 block groups, with an average of over 1000 persons per group.
Census data available at geo demographic level includes:
a. median income
b. average household size
c. average home value
d. average monthly mortgage
e. percentage ethnic breakdown
f. marital status
g. percentage college educated.
For the UK census there are 155 000 enumeration districts, each comprising about 150 households and ten postcodes. The enumeration district is the basis for much geo demographic data. Individual-level data are better predictors of behaviour than aggregated geo demographic data. However, in the absence of individual-level data, census data may be the only option for enhancing your internal data. For example, a car reseller could use census data about median income and average household size to predict who might be prospects for a purchase promotion.
3. Modelled data:
Modelled data are generated by third parties from data that they assemble from a variety of sources. You buy processed, rather than raw, data from these sources. Often they have performed clustering routines on the data. For example, Claritas has developed a customer classification scheme called PRIZM.
In Great Britain, PRIZM describes the lifestyles of people living in a particular postcode. Every postcode is assigned to one of 72 different clusters on the basis of their responses to a variety of lifestyle and demographic questions. Eighty per cent of the data used in the clustering process is less than three years old.
Step # 4. Select the Database Technology and Hardware Platform:
Customer-related database can be stored in a database in a number of different ways:
1. Hierarchical
2. Network
3. Relational.
1. Hierarchical database:
Hierarchical and network databases were the most common form between the 1960s and 1980s. The hierarchical database is the oldest form and not well suited to most CRM applications. You can imagine the hierarchical model as an organization chart or family tree, in which a child can have only one parent, but a parent can have many children.
The only way to get access to the lower levels is to start at the top and work downwards. When data is stored in hierarchical format, you may end up working through several layers of higher-level data before getting to the data you need.
Product databases are generally hierarchical. A major product category will be subdivided repeatedly until all forms of the product have their own record. To extend the family tree metaphor, the network database allows children to have one, none or more than one parent. Before the network database had the chance to become popular, the relational database superseded it, eventually becoming an ANSI standard in 1971.
2. Relational databases:
Relational databases are now the standard architecture for CRM applications Relational databases store data in two dimensional tables comprised of rows and columns. Relational databases have one or more fields that provide a unique form of identification for each record. This is called the primary key. For sales databases, each customer is generally assigned a unique number which appears in the first column.
Therefore, each row has a unique number. Companies also have other databases for marketing, service, inventory, payments and so on. The customer’s unique identifying number enables linkages to be made between the various tabases. Let’s imagine you are a customer of an online retailer. You buy a book and supply the retailer with your name, address, preferred delivery choice and credit-card details.
A record is created for you on the ‘ Customer ‘ database, with a unique identifying number. An ‘ Orders received ‘ database records your purchase and preferred delivery choice .An ‘ Inventory ‘ database records that there has been a reduction in the stock of the item you ordered. This may trigger a re-ordering process when inventory reaches a critical level.
A ‘ Payment ‘ database records your payment by credit-card. There will be one-to-many linkages between your customer record and these other databases. With the advent of enterprise suites from vendors such as Oracle and SAP, all of these databases may reside in the one system and be pre-integrated.
The choice of hardware platform is influenced by several conditions:
1. The size of the databases. Even standard desktop PCs are capable of storing huge amounts of customer data. However, they are not designed for this data to be shared easily between several users.
2. Existing technology. Most companies will already have technology that lends itself to database applications.
3. The number and location of users. Many CRM applications are quite simple, but in an increasingly global marketplace the hardware may need very careful specification and periodic review. For example, the hardware might need to enable a geographically dispersed, multilingual, user group to access data for both analytical and operational purposes.
3. Relational database management system (RDBMS):
A relational database management system can be defined as follows: An RDBMS is a software programme that allows users to create, update and administer a relational database. There are a number of relational database management systems available from technology firms that are well suited to CRM applications. Leading RDBMS products are Oracle, DB2 from IBM, and Microsoft’s SQL Server. Most RDBMS products use SQL to access, update and query the database.
The selection of the CRM database can be done in parallel with the next step in this process, selection of CRM applications. Modern database applications come together with their own database schema, which predetermines the tables and columns in the database structure. Each CRM vendor then supports a specified list of database technologies, for example, Oracle or SQL server. Indeed, it is possible to buy an entire platform, consisting of integrated hardware, operating system (OS), database and CRM applications. Leading platforms include UNIX, Microsoft and IBM.
The UNIX platform offers a number of hardware/OS/database options, such as Hewlett- Packard hardware, Digital UNIX operating system and Oracle database. The IBM platform employs AS/400 hardware, OS/400operating system and DB2/400 database. Microsoft NT servers are becoming more popular for CRM applications, due to the ease with which they can be scaled and expanded.
Step # 5. Populate the Database:
Having decided what information is needed and the database and hardware requirements, the next task is to obtain the data and enter it onto the database. CRM applications need data that are appropriately accurate.
We use the ‘ appropriately ‘ because the level of accuracy depends upon the function of the database. Operational CRM applications generally need more accurate and contemporary data than analytical applications. You may have personally experienced the results of poor quality data. Perhaps you have received a mailed invitation to become a do nor to a charity, to which you already donate direct from your salary.
This could have happened when a prospecting list that has been bought by the charity was not been checked against current donor lists. Perhaps you have been addressed as Mrs. although you prefer Ms. This is caused because the company has either not obtained or not acted or checked your communication preferences. One of the biggest issues with customer data is not so much incorrect data as missing data. Many organizations find it difficult to obtain even basic customer data, such as e-mail addresses and preferences.
The main steps in ensuring that the database is populated with appropriately accurate data are as follows:
1. Source the data
2. Verify the data
3. Validate the data
4. De-duplicate the data
5. Merge and purge data from two or more sources.
Sourcing:
organizations must develop explicit processes to obtain information from customers, such as on initial sign-up or when concluding a service call. Organizations cannot rely on customer goodwill; data must be collected whenever interaction occurs.
Verification:
this task is conducted to ensure that the data has been entered exactly as found in the original source. This can be a very labour intensive process since it generally involves keying the data in twice with the computer programmed to flag mismatches. An alternative is to check visually that the data entered match the data at the primary source.
Validation: this is concerned with checking the accuracy of the data that are entered. There are a number of common inaccuracies, many associated with name and address fields: misspelt names, incorrect titles, inappropriate salutations. A number of processes can improve data accuracy.
i. range validation: does an entry lie outside the possible range for a field?
ii. missing values: the computer can check for values that are missing in any column.
iii. check against external sources: you could check postcodes against an authoritative external listing from the mail authorities.
De-duplication:
also known as de-duping. Customers become aware that their details appear more than once on a database when they receive identical communications from a company. This might occur when external data is not cross-checked against internal data, when two or more internal lists are used for the mailing or when customers have more than one address on a database. There may be sound cost reasons for this (de-duplication does cost money), but from the customer’ s perspective it can look wasteful and unprofessional. De-duplication software is available to help in the process.
The de-duplication process needs to be alert to the possibility of two types of error:
1. Removing a record that should be retained. For example, if a property is divided into unnumbered apartments and you have transactions with more than one resident, then it would be a mistake to assume duplication and delete records. Similarly, you may have more than one customer in a household, bearing the same family name or initials.
2. Retaining a record that should be removed. For example, you may have separate records for a customer under different titles such as Mr and Dr.
Merge and purge also known as merge-purge , this is a process that is performed when two or more databases are merged. This might happen when an external database is merged to an internal database, when two internal databases are merged (e.g. marketing and customer service databases), or when two external lists are bought and merged for a particular purpose such as a campaign. There can be significant costs savings for marketing campaigns when duplications are purged from the combined lists.
Step # 6. Maintain the Database:
Customer databases need to be updated to keep them useful.
Consider the following statistics:
a. 19% of managing directors change jobs in any year
b. 8% of businesses relocate in any year
c. in the UK, 5% of postcodes change in an average year
d. in western economies about 1.2% of the population dies each year
e. in the USA, over 40 million people change addresses each year.
It does not take long for databases to degrade. Companies can maintain data integrity in a number of ways.
1. Ensure that data from all new transactions, campaigns and communications is inserted into the database immediately. You will need to develop rules and ensure that they are applied.
2. Regularly de-duplicate databases.
3. Audit a subset of the files every year. Measure the amount of degradation. Identify the source of degradation: is it a particular data source or field?
4. Purge customers who have been inactive for a certain period of time. For frequently bought products, the dormant time period might be six months or less. For products with a longer repeat purchase cycle, the period will be longer. It is not always clear what a suitable dormancy period is. Some credit-card users, for example, may have different cards in different currencies. Inactivity for a year only indicates that the owner has not travelled to a country in the previous year. The owner may make several trips in the coming year.
5. Drip-feed the database. Every time there is a customer contact there is an opportunity to add new or verify existing data.
6. Get customers to update their own records. When Amazon customers buy online, they need to confirm or update invoice and delivery details. Remove customers ‘ records when they request this.
8. Insert decoy records. If the database is managed by an external agency, you might want to check the effectiveness of the agency’s performance by inserting a few dummy records into the database. If the agency fails to spot the dummies, you may have a problem with their service standards users with administrative rights can update records. Database updating and maintenance is also enabled by database query language.
Common languages are SQL (Structured Query Language) and QBE (Query By Example). Maintenance queries available in SQL include UPDATE, INSERT and DELETE commands. You can use the commands to update your customer- related database. INSERT, for example, adds a new record to the database.