Transcript Optimal Chapter 4

Optimal Database Marketing Drozdenko & Drake, 2002
1
Copyright © 1999 by Ancell School of Business. All Rights Reserved.
Chapter 4
Database Maintenance
and Coding
Optimal Database Marketing Drozdenko & Drake, 2002
2
Objectives
• To evaluate the importance of database
maintenance.
• To examine what aspects of the marketing
database need to be maintained and
updated.
• To consider how records on the database
are coded to monitor marketing programs.
Optimal Database Marketing Drozdenko & Drake, 2002
3
Standard Database Maintenance
Routines
•
•
•
•
De-duping the customer file
“Householding” the customer file
Purging of old customer records
Changes in contact information (address, phone
number, e-mail)
• Address standardization
• Removal of names on databases at consumer’s
request
• Identifying customers with match codes
Optimal Database Marketing Drozdenko & Drake, 2002
4
Database Maintenance, Other Issues…
•
•
•
•
•
•
Merge/purge processing
Coding source and promotional offers
Adding decoy records (salting the customer file)
Identifying credit risks and frauds
Field updating rules
Reporting summary and aggregate level
information
• Database storage and security
• Database maintenance schedules
• Technical aspects of database maintenance
Optimal Database Marketing Drozdenko & Drake, 2002
5
Merge / Purge
• Databases are continually being modified to
reflect changes to customer records.
• Merging refers to the process of combining two
or more lists as occurs with data enhancement.
• Purging refers to the elimination of records for
any of a number of purposes, such as for name
duplication or for non-deliverable address.
Optimal Database Marketing Drozdenko & Drake, 2002
6
De-duping the Customer File
Duplicate names on a marketing database
can occur for a number of reasons:
• Customer’s name and/or address appeared
different on an external list versus the house
file.
• For a particular order, customer used a different
mailing address.
• The direct marketer confused the bill to/ship to
addresses in the system.
Optimal Database Marketing Drozdenko & Drake, 2002
7
Exhibit 4.1
As the percentage of duplicate records increases,
so does the associated expense. The figure shows
the costs of different levels of duplicates in files
of various sizes assuming the cost of mailing is
$.50 for each piece mailed. For example 50,000
X 10% = 5,000 x $.50 = $2,500
% Duplication
5%
10%
15%
20%
Total Number of Records
50,000 Records
100,000 Records
$1,250
$2,500
$2,500
$5,000
$3,750
$7,500
$5,000
$10,000
Optimal Database Marketing Drozdenko & Drake, 2002
1,000,000 Records
$25,000
$50,000
$75,000
$100,000
8
“Householding” the Customer File
• When multiple contacts at one address are
not desirable, as in the case of mailing a
large annual catalog, direct marketers
should identify multiple household accounts
at a single address and thereby obtain
promotional efficiencies and effectiveness.
• Depending on the situation, marketers may
want to keep multiple contacts at the same
address.
Optimal Database Marketing Drozdenko & Drake, 2002
9
Purging of Old Customer Records
Examples of names purged include:
• Inactive for the past 3 years
• No promotions sent to the customer for the
past 3 years
• Unable to be address confirmed for over 2
years
• Lack of external verification
• Deceased
• Non-deliverable (nixie)
Optimal Database Marketing Drozdenko & Drake, 2002
10
Changes in Contact Information
• For mail, a service called National Change
of Address processing (NCOA processing)
helps to eliminate non-deliverables (nixies).
• Incorrect E-mail addresses and telephone
numbers also increase cost and should be
changed or eliminated.
Optimal Database Marketing Drozdenko & Drake, 2002
11
Address Standardization
• By processing your list or file to meet USPS
regulations, you can save money and stabilize postal
rates.
• It also improves matching for purposes of de-duping
or “householding” the customer file.
• The process of “cleaning up” a mailing list is known
as address validation and standardization and is
performed by the use of CASS (USPS Coding
Accuracy Support System) certified software.
Optimal Database Marketing Drozdenko & Drake, 2002
12
Removal of Names from
Databases at Consumer Request
• Some consumers do not want unsolicited
offers from direct marketers.
• The Direct Marketing Association (DMA,
http://www.the-dma.org) maintains a file of
people who requested that their names be
removed from mail, telephone and e-mail
databases.
Optimal Database Marketing Drozdenko & Drake, 2002
13
Removal of Names from
Databases at Consumer Request
• Direct marketers are also required remove
names from databases upon direct request
from mail or phone call recipients.
• Direct marketers must be aware of federal
and state laws regarding contacting
customers and prospects.
• There are fines associated with noncompliance with these regulations.
Optimal Database Marketing Drozdenko & Drake, 2002
14
Coding Records to
Increase Performance
Codes are added to customer records to identify…
•
•
•
•
customers
the source of the record
media
marketing programs
Optimal Database Marketing Drozdenko & Drake, 2002
15
Identifying Customers
With Match Coding
• Duplicate records of the same customer occur.
How can this happen?
• Names can be written different ways in
different databases that are being merged.
• Ronald Drozdenko
• R. Drozdenko
• R. Drozenko
• Dr. Ronald Drozdenko
Optimal Database Marketing Drozdenko & Drake, 2002
16
Identifying Customers
With Match Coding
• Match coding involves taking elements of
the name and address to develop a unique
identifier of an individual
Optimal Database Marketing Drozdenko & Drake, 2002
17
Purposes of Match Code
Why would a direct marketer use match coding?
• Reduces contact costs due to redundancy
• Avoids contacting an individual who
requested that their name be removed
from the list
Optimal Database Marketing Drozdenko & Drake, 2002
18
Example of Match Coding
John Smith
111 Boardwalk
New York, NY 10011
An example match code for John Smith is:
10011SIH111BJ
The match code is derived from the following components of
Smith’s mailing address:
All 5 digits of 5-digit zip code = 10011
1st, 3rd, and 4th letter of surname = SIH
Last 3 digits of street address = 111
1st street character = B
Alpha initial of first name = J
Optimal Database Marketing Drozdenko & Drake, 2002
19
Match Coding Precision
Loose versus Tight
What are the advantages/disadvantages of loose
versus tight matching?
• Tight code matching increases duplicates; loose code
matching reduces the probability of duplicates.
• Tight code matching increases the probability that
legitimate customers will be included; loose code
matching increases the probability that unqualified
customers will be included.
Optimal Database Marketing Drozdenko & Drake, 2002
20
Match Codes
• Match code method will vary based on
organizational needs.
• For some organizations, it is sufficient to code
only by address and last name.
• Further, some organizations may only be
concerned with address if they know it is not a
multiple household dwelling.
• Cost of the mailing is a consideration.
Optimal Database Marketing Drozdenko & Drake, 2002
21
Coding Source and Promotional
Offers
• Marketers code the original source of entry
of a record onto the house file
• Individual records in a database can come
from a number of sources.
Lists purchased by the marketer
Response to advertisements
Cards filled out at retail location
Optimal Database Marketing Drozdenko & Drake, 2002
22
Exhibit 4.3
(Mailing Label from catalog)
BULK RATE
U.S. POSTAGE
PAID
EDDIE BAUER
Key Code
*********************ECRLOT**R-004
LAURA MARTIN
346 KETTLE RD
WOODBURY
CT 06798 -3901
3357231
I.D. Code
853000922
Media Code
E
Optimal Database Marketing Drozdenko & Drake, 2002
23
Source Code
Why does a marketer need to know the source of the
record?
• Source codes are used to evaluate the
performance of the specific way that a record
was acquired.
• Poor performing lists or promotions could be
eliminated or modified.
• Good performing lists or promotions could be
used as models for future list acquisitions or
marketing programs.
Optimal Database Marketing Drozdenko & Drake, 2002
24
Coding Marketing Programs
• Organizations use a number of methods to code
records as to which marketing programs have
been received.
• Creative packages, media, offers, pricing, etc.
can all be coded.
• Records are also identified as being part of
control groups for testing.
Optimal Database Marketing Drozdenko & Drake, 2002
25
Coding Marketing Programs
• Some organizations call this coding
“Key” coding.
• Other organizations have both “Key”
and “Media” codes.
• Still other organizations code source
and program information together.
Optimal Database Marketing Drozdenko & Drake, 2002
26
Key Code
• Each marketing activity should be identified in
the database.
• For example, a company that markets CDs,
videotapes, and books wants to relate each new
mailing, direct response TV campaign, and
magazine insert to an order.
• The Key Code allows the marketer to evaluate
the performance of each marketing program.
Optimal Database Marketing Drozdenko & Drake, 2002
27
Other Codes
Based on organization needs, other types of
codes have been developed.
•
•
•
•
•
Offer
Catalog
Test
Payment
Ordering
Optimal Database Marketing Drozdenko & Drake, 2002
•
•
•
•
•
Statistical Model
Broker ID
Ad
Item
Decile
28
Salting, Decoy Records
• Owners of databases often include
names in the file that are not real
customers.
Optimal Database Marketing Drozdenko & Drake, 2002
29
Salting, Decoy Records
• Check the performance and security of outside
services that are responsible for mailing the
promotional offer or fulfilling orders.
• To monitor delivery of packages for timed
programs.
Optimal Database Marketing Drozdenko & Drake, 2002
30
Salting, Decoy Records
• For companies that rent their lists to other
companies the “decoy” names on the list
can be used to verify adherence to the list
rental agreement (e.g., one-time or limited
use).
• Note: If a customer on a rented list responds
to an offer, he/she can then be added to the
marketer’s database for unlimited use.
Optimal Database Marketing Drozdenko & Drake, 2002
31
Identifying Credit Risks
• National credit services allow for the flagging
or removal from files of an individual who is
considered a poor credit risk.
• Credit evaluation allows promotions to be
more cost effective by restricting offers to
individuals who are likely to pay for products
received.
Optimal Database Marketing Drozdenko & Drake, 2002
32
Identifying Credit Risks
• Highly personalized credit information about
customers is only available to credit grantors
(e.g., 30 day free trial agreements) and not
available to organizations only making offers.
• Credit organization can, however, provide less
personalized credit information based on
modeling derived from aggregates.
Optimal Database Marketing Drozdenko & Drake, 2002
33
Identifying Credit Risks
• Some organizations will develop
internal models to evaluate credit risk.
• These models use existing house data.
• Internal models may include zip level
information.
Optimal Database Marketing Drozdenko & Drake, 2002
34
Field Updating Rules
Every data item (field, variable) on a database
has rules regarding how it will be maintained.
When a promotion is sent, various fields will
be updated:
• The promotion is marked on the file as being
sent for each customer who received it.
• The “date of last promotion” field is updated.
• The “total number of promotions sent” field is
updated.
Optimal Database Marketing Drozdenko & Drake, 2002
35
Field Updating Rules
When payment for product arrives, the
following types of fields will also be updated:
• The field “total dollars paid” will be updated.
• The field “total products paid” will be
updated.
• The field “date of last payment” will be
updated.
Optimal Database Marketing Drozdenko & Drake, 2002
36
Field Updating Rules
• Other fields feeding off of core payment
fields will also be updated.
• For example, the field “Number of products
paid in the last 12 months” will be updated.
Optimal Database Marketing Drozdenko & Drake, 2002
37
Optimal Database Marketing Drozdenko & Drake, 2002
38
Summary Information
In addition to updating individual records, aggregate data
are also calculated and updated by day, week, month, etc.
•
•
•
•
Total mailings
Total purchases
Total orders
Total orders by source,
key, media, etc.
• Cost per order
Optimal Database Marketing Drozdenko & Drake, 2002
• Sales per thousand
pieces mailed
• Profit per thousand
pieces mailed
• Average life-time value
39
Optimal Database Marketing Drozdenko & Drake, 2002
40
Optimal Database Marketing Drozdenko & Drake, 2002
41
Database Storage and Security
• The survival of direct marketing organizations is
dependent on the database. Therefore, it is essential to
protect it.
• Database files should be backed-up regularly.
• Back-up files should be stored in a safe location (often
off-site) to protect from possible damage such as due to
fire and water.
• Access to the file should be limited to essential
personnel.
• Encryption techniques should be considered to protect
the file from unauthorized use.
Optimal Database Marketing Drozdenko & Drake, 2002
42
Optimal Database Marketing Drozdenko & Drake, 2002
43
Database Maintenance
Schedules
• Database maintenance involves a cost. Merging
and purging names, flagging credit risks,
updating file fields, backing-up files, etc.
requires processing and human resource time.
• Therefore, maintenance should be performed at
intervals that minimize maintenance costs while
optimizing marketing efficiency and
effectiveness.
Optimal Database Marketing Drozdenko & Drake, 2002
44
Database Maintenance
Schedules
• Often fields are automatically updated as new
data are entered. This eliminates the need to
perform specific updating procedures.
• However, some maintenance, such as address
updating, should be performed as needed (e.g.,
just prior to mailings).
Optimal Database Marketing Drozdenko & Drake, 2002
45
Optimal Database Marketing Drozdenko & Drake, 2002
46
Some Technical Aspects of Database
Maintenance
• Most relational databases are manipulated with
SQL.
• SQL (pronounced see-quill or S-Q-L) stands for
structured query language.
• Queries are used to extract or count data.
• SQL is a simple set of 36 commands that are
relatively complete.
• Examples of SQL commands are: Select, From,
Where, Order By, Group By, And, Or
Optimal Database Marketing Drozdenko & Drake, 2002
47
Review Questions
1. What are some of the reasons for routinely
maintaining the database?
2. What is meant by merge/purge?
3. What are some of the codes marketers place on
individual records, and how are they used?
4. Why would a marketer put decoy records on a
database?
5. When and how is a database updated? Give
examples of some of the fields that would be
routinely updated.
6. Why is database security important to both
consumers and marketers?
Optimal Database Marketing Drozdenko & Drake, 2002
48