Customer Profiling and
Prospect Targeting Project
RATIONALE
In customer profiling applications, geodemographic data is integrated with internal customer data in
pursuit of a more complete understanding of customer characteristics. This
process of overlaying geodemographic data requires that customer information be
assigned to spatial geographic units. At broader levels of analysis, this
is accomplished by simple table joins using state or ZIP code fields. All these manipulations require
that information in corporate databases be prepared for and incorporated into
business geographics analyses. This assignment involves all these
procedures from the manipulation of corporate data in Microsoft Access through
the spatial analysis of demographic information to the preparation
of reports and presentations communicating the results of geodemographic
analysis.
Better Books Inc.
Better Books Incorporated is a direct
marketer of specialty books in the United States. The firm uses database
marketing techniques to make specialized offerings to target customers by mail,
accepting orders via toll free phone lines, mail or fax. By focusing on
mid to upper income households with above average levels of education, the firm
is able to realize higher prices and margins for its specialty
books.
Faced with increased competition from both
traditional retail and Internet firms, Better Books is seeking to maintain its
profit growth through; 1) selective expansion into markets outside the United
States, 2) increasing its customer base in the United States, and 3)
expanding into traditional book retailing from its current base of two outlets
in the San Francisco area. You are the business geographics consultant retained by the firm to support this
effort. In this assignment, you will merge internal company data with
geodemographic information in a project involving sales analysis and prospect
identification.
Exercise 1: Sales Analysis by State and ZIP Code(25 points)
Better Books wishes to identify
geographic patterns of sales among its existing customers. Specifically,
it wishes to identify states and ZIP codes with large numbers of customers, high
sales volume and high penetration rates. In this exercise, you will use
information selected from an internal Microsoft Access database to identify spatial
patterns in the firm's sales data. You will then use this data to identify the
firm's most productive market for further analysis in the final assignment
- The data files you need for this exercise are
contained in the self-extracting compressed file CustProfData.exe.
Download this file and double click on it from Windows Explorer to
decompress the data.
- Load ArcView, select View. Add a themes to the
view using the files statepop.shp and zip.shp. Review the
tables for each of these themes, noticing that the statepop.shp contains 1996 population
and household data. Load StSales.dbf and ZipSales.dbf
tables to the analysis. These tables contain internal company sales
date derived from queries of the corporate database marketing system. Join them to the appropriate theme
tables. Use the data from the joined tables to produce maps showing
the distribution of customers, orders and sales by state and ZIP code in
BB's market region. What patterns do you see?
- To determine if BB's sales differ from state
population distribution, create a new field (MktPen) in the state theme to measure
market penetration. Define this field as a number with 2 decimal
places. Calculate values for this field by dividing the number
of customers by the number of households and multiplying the result by
100. Be sure to save the resulting table. Display the results from this field in a thematic map of
penetration by state. What patterns do you see?
- You wish to concentrate your attention on the state
with the highest possible sales level which also has an above average
penetration rate. Create charts which help you evaluate states based
on these measures. Based on your analysis of this data, which state
will you select? Explain your decision.
- Following course stylistic
expectations, write a 2-3 page report to Better Books management
in which you;
- describe the analysis you have performed,
- identify the state you believe has the highest sales potential
- explain the rationale for this conclusion, and
- support your reasoning and conclusion with at least two maps from
ArcView and two charts from ArcView or MS Office package you wish to use.
Be sure to include appropriate titles, headings, dates and source
information in all your maps and charts.
- Save your project with the name BBBfml.apr (where
fml are your first, middle and last initials) for use in the next exercise.
Exercise 2: Customer Profiling and Prospect
Targeting (25 points)
Better Books management desires to use the
process of geodemographic customer profiling to understand its customer base
more fully. It also wishes to use this information to identify favorable
prospects for expansion into new geographic areas and greater penetration of its
current markets. The firm has asked you to assess this new segmentation strategy
with a test mailing to households in the state of New York. In this exercise, you will integrate
commmercially purchased demographic data with data from the firm's
corporate database. You will then analyze this dataset with ArcView to
identify three different target groups of prospects and prepare a report listing
these groups for Better Books.
- Load the BBBfml.apr project you created for
the previous assignment.
- Add the theme nyzip.shp to the View
window. This is a base ZIP code map for the state of New York.
Open the Attribute Table for this theme and perform a join with the ZipSales.dbf
table which you have already added to the project.
- You have purchased a set of geodemographic data from
a commercial data source in the form of the file NYZipData.dbf which
is one of the files included in the dataset for this exercise. It contains
1996 information on
population, median age,
median household income, percentage of home ownership and percentage of
adults with some college education for
each ZIP code in New York. Add this table to your ArcView
project and join it with the existing Zip code table (which includes the
sales data added in the previous exercise). Add a new field named Penetration,
defined as a number with 2 decimal places. Use the formula in the previous exercise to calculate market penetration
for each ZIP code. You now have integrated sales and demographic data
for each ZIP code in the selected state.
- In the View window, design some thematic maps
showing the characteristics of the dataset relative to the demographic
variables in the dataset. What patterns do you see?
- In the Tables window, select the records for the ZIP
codes in which you have customers. Calculate and record the mean
values for the following variables in these records: Median Age,
Median Household Income, Percentage Home Ownership, and Percentage of
Persons Over 25 With Some College Education. Select the records for
those ZIP codes in which you have no customers. Repeat the
calculations for these four demographic variables? What differences do you
see? Which two variables best serve to distinguish these groups?
-
Add a field labeled Strategy to this
table. You will use it to assign each ZIP code record an appropriate
strategy based on its sales and demographic characteristics. Specifically,
assign the following strategies ZIP codes with the described
characteristics. In doing so, you will use the Query Builder to select
records with the desired characteristics and the Calculator to assign
corresponding values to the Strategy field. Upon completion, save the
new version of the table.
-
Calculate;
-
the total number of
households to whom you will send the next mailing,
-
the total number of households you
expect to gain as customers as a result of the Penetrate and Expand
strategies.
|
Strategy |
Characteristics of ZIP Codes |
Marketing Action |
|
Expand:
|
No customers present, segmenting variables
above the average values for the entire dataset
|
Mailing to all households
|
|
Penetrate:
|
Customers present, segmenting variables
above average values for the entire dataset, penetration levels below average
|
Mailing to all households
|
|
Maintain:
|
Customers present, not selected for
Penetration strategy (i.e. below average for EITHER penetration level
or segmenting variables)
|
Mailing to households of customers only
|
|
Ignore:
|
No customers present, not selected for Expand strategy
(i.e., at least one segmenting variables
below average values of the entire dataset)
|
No mailing |
- Create a thematic map in the View window which displays the recommended
strategy for each of the ZIP codes in New York.
Prepare it to be included in your report.
- Following course stylistic
expectations, write a 2-3 page report to Better Books management
in which you;
- describe the screening, profiling and strategy selection process you have performed,
- explain the characteristics of the households targeted by each
strategy,
- explain; a) why you think this approach will gain customers and
b) the number of customers you expect to gain, and
- support your reasoning and conclusion with at least two maps and two other appropriate
exhibits from ArcView and any MS Office package you wish to use. Be sure
to include appropriate titles, headings, dates and source information in
all your maps and charts.
The two parts of this
exercise are due together on the date specified in the Course Schedule.
|
