Making A Good Database Better Presented by: Mark Klein Gwen Borders Atlanta JournalConstitution MaaX

1
Making A Good Database BetterPresented
byMark KleinGwen BordersAtlanta
Journal-ConstitutionMaaX Users Conference -
March 27, 2006
2
Our Delimma

• Choose One
• If it aint broke. dont fix it.
• Vs.
• Is that a light at the end of the tunnel, or am
  I about to get hit by a locomotive?

3
Topics

• Where We Were
• Situation
• Re-design plans
• Challenges
• Where Were Going
• Circulation ROI
• Multi-department usage

4
Situation Original MaaX Context
Where We Were

• Internal development team established Summer 03
• Astech selected for ambitious development
  assignment Spring 04
• Complex mix of 60 files, 10M records, four
  master reference tables
• MaaX database launched in December 04
• Acculturation efforts through February 05
• MaaX Lite Vision broad user community
  simplified data views
• In-house training program 25 MaaX Lite
  graduates
• Sales channel managers envisioned as campaign
  planners
• Multi-department involvement beyond circulation
• Migration to fully integrated sales processes
  using MaaX by Fall
• Big promises for sales efficiencies ROI

5
Situation Realities Sink In
Where We Were

• March through June perspectives change.
• New V. P.
• New sales management
• Slow cultural acceptance (low risk tolerance)
• MaaX applications limited to direct response
• Heavy dependence on Virtual Fields over 100 in
  use
• Proxy for business rules
• Accommodate complex data/file combinations
• Compensate for inherent MaaX software
  Query/Banding limits
• Database stability and nightly processing/refresh
  over-burdened
• IT uneasiness with channel integration
  capabilities
• Show me the money ???

6
Situation Gut Check
Where We Were

• Can nightly refresh processes be shortened ?
• Can the nightly refresh be error free?
• Can we reduce dependence on Virtual Fields ?
• Can we create effective business rules ?
• Is there head-room for overall database growth
  ?
• Can we finish the sales integration process ?
• Telemarketing disposition data alone 10 M
  records
• Need multi-channel sales/retention choreography
• Visibility across channels for individual
  household impact
• BOTTOM LINE DO WE HAVE THE RIGHT DATABASE DESIGN
  ?
• Astech bravely posed this question. thank
  goodness they did.

7
Redesign Action Plan June/July
Where We Were

• Define spectrum of sales communication
  applications
• All roads lead to a household via many channels
• Manage channel timing, sequence, and interval
• Measure effectiveness of variations in these
  variables
• Evaluate alternative database designs
• Identify pros/cons of alternatives vs. existing
  design
• Assess timeline and resource requirements
• Maintain existing database functionality
  throughout

8
Re-design Challenges
Where We Were

• Managing internal perspectives
• Building current database usage - - broadening
  applications
• Motivating core project team I thought we were
  done
• Limiting skepticism Whats wrong with design 1
  ? Confidence in design 2 ?
• Providing tangible early ROI examples
• Push me Pull you relationship with Astech
• Sharing Oracle/SQL knowledge and experiences
• Acknowledging characteristics of SmartFocus
  software design
• Anticipating (and revising) necessary business
  rules
• Timeliness and mutual accountability
• Compressing learning curves without sacrificing
  results

9
Where We Were
1 DM ADDR KEY
PDPE
PDNC
PCAM
PDSP
PLST
2 BLOCK GROUP
D
D
W
D
D
F
U
U
U
U
3 SUB ACCT NO
4 - PHONE
DMMO
5 SUB RRN
SDAE
SDEE
FIPS
4
Y
F
D
D
U
U
H
6 - AC / EX
DMCY
1
1
11
7 BATCH NO COMB
Y
F
PMST
8 ED ZONE
AMST
ACAM
2
DMHI
1
Q
F
6
9 SAM3 ID
D
U
PNPA
Y
F
10 AJC SAM ID
F
Q
DMLD
4
1
11 FIPS ST / CTY
Y
F
10
LADZ
1
S
F
8
SB3S
SBAS
1
1
10
D
D
R
R
1
1
1
1
10
3
3
CPTY
CURG
CSMC
DMSF
SAMA
SABL
SAM3
SUB3
SUBA
SMAS
D
D
D
D
W
W
M
M
M
U
U
U
U
U
U
F
F
F
D
R
3
9
7
SBIL
SCMP
RLGC
SM3S
D
D
M
U
U
F
ECAM
D
R
DMSI
SQUE
SSTP
5
D
U
SEML
SDEL
D
D
U
U
W
U
EDSP
M
F
D
U
SMEM
SDOL
SPRM
D
D
D
D
U
U
U
10
Existing Design Challenges
Where We Were

• No direct link between address, phone and email
  masters
• Inability to include phones or emails where an
  address didnt exist
• Nightly Build Errors
• Increased Build Times
• Virtual Fields dependency on other Virtual Fields
• Query complexity
• Transactional Data
• Duplicity Issues
• Determining the most accurate information when
  multiple subscriptions existed
• Table Volumes
• Maax screen limits
• User Confusion

11
Making The Conversion
Where We Were .Where Were Going

• Accepting the final database design solution
• Is this the best approach to linking multi master
  tables?
• Does this give us full integration?
• Is the end users life simplified?
• Preparing for conversion
• Identify and develop required Business Rules
• Determine ways to merge data to simplify end user
  view
• Testing and quality checks
• Business Rule validation
• Processing Speeds
• Parallel use
• Converting existing processes to new format
• Testing old design versus new design

12
New MaaX At Work
Where Were Going

• Advantages of new design.
• Master Tables that contain all records (with or
  without addresses)
• Address Master ( 3.1 M )
• Phone Master ( 22.7 M )
• Email Master ( 2 M )
• Direct Links between all master files thru
  business rules
• Business Rules / Views
• Determine best guess information for each master
  file
• Use of multiple tables to determine Business Rule
  results
• Creation of new fields from transactional data
• Merging tables (send 60 files, produce 25 tables)
• Reduction in Maax Table views based on security
• Required fields for Campaign selection reside in
  one table

13
Database Design Group 1
Where Were Going
1 ADDR KEY
2 PHONE NUMBER
PCHK
3 EMAIL ADDRESS
PDPE
DMMO
DMLS
CSMC
FIPS
LSR3
LADZ
8
DMHI
PCAM
SB3S
4 fields pulled onto AMST through business rules
DMCY
PDPEA
SUB3
PDNC
DMLD
5 fields pulled onto PHONE through business
rules
PDSP
SABL
ACAM
OMKT
6
SDAE
2
LTRK
SUBA
PMSF
1
SBAS
1
AMST
PMST
6 fields pulled onto SUBA
SSTP
LSRA
7
CPTY
OLQ
DMSF
7 fields pulled onto SUBA through business rules
LPRF
LPRM
5
PNPA
4
1
OCAM
SCAM
SPRM
PLST
ADSP
8 File Merged with PDPE
SCMP
LCPA
SAMA
SMAS
DMSI
SQUE
EMSF
LQUE
SAM3
one-to-one (fields on parent table)
SM3S
SDOL
SDEE
LINA
LDOL
EMST
3
SBIL
CURG
one-to-many
SMEM
SEML
ECAM
MAAX view
EDSP
hidden table
14
Database Design Group 2
Where Were Going
1 ADDR KEY
2 PHONE NUMBER
PCHK
3 EMAIL ADDRESS
PDPE
DMMO
DMLS
CSMC
FIPS
LSR3
LADZ
8
DMHI
PCAM
SB3S
4 fields pulled onto AMST through business rules
DMCY
PDPEA
SUB3
PDNC
DMLD
5 fields pulled onto PHONE through business
rules
PDSP
SABL
ACAM
OMKT
6
SDAE
2
LTRK
SUBA
PMSF
1
SBAS
1
AMST
PMST
6 fields pulled onto SUBA
SSTP
LSRA
7
CPTY
OLQ
DMSF
7 fields pulled onto SUBA through business rules
LPRF
LPRM
5
PNPA
4
1
OCAM
SCAM
SPRM
PLST
ADSP
8 File Merged with PDPE
SCMP
LCPA
SAMA
SMAS
DMSI
SQUE
EMSF
LQUE
SAM3
one-to-one (fields on parent table)
SM3S
SDOL
SDEE
LINA
LDOL
EMST
3
SBIL
CURG
one-to-many
SMEM
SEML
ECAM
MAAX view
EDSP
hidden table
15
Database Design Group 3
Where Were Going
1 ADDR KEY
2 PHONE NUMBER
PCHK
3 EMAIL ADDRESS
PDPE
DMMO
DMLS
CSMC
FIPS
LSR3
LADZ
8
DMHI
PCAM
SB3S
4 fields pulled onto AMST through business rules
DMCY
PDPEA
SUB3
PDNC
DMLD
5 fields pulled onto PHONE through business
rules
PDSP
SABL
ACAM
OMKT
6
SDAE
2
LTRK
SUBA
PMSF
1
SBAS
1
AMST
PMST
6 fields pulled onto SUBA
SSTP
LSRA
7
CPTY
OLQ
DMSF
7 fields pulled onto SUBA through business rules
LPRF
LPRM
5
PNPA
4
1
OCAM
SCAM
SPRM
PLST
ADSP
8 File Merged with PDPE
SCMP
LCPA
SAMA
SMAS
DMSI
SQUE
EMSF
LQUE
SAM3
one-to-one (fields on parent table)
SM3S
SDOL
SDEE
LINA
LDOL
EMST
3
SBIL
CURG
one-to-many
SMEM
SEML
ECAM
MAAX view
EDSP
hidden table
16
New MaaX At Work
Where Were Going
17
New MaaX At Work
Where Were Going

• Re-introducing New MaaX internally
• Applying business rules in circulation
  sales/retention today
• Best Guess Subscriber, Address, Phone and Email
• Reduce many to one confusion
• Target segment with multiple channels
• Prior Transaction History Counts/Totals
• Subscriber Data (ie vacation donation, payment,
  complaints, etc)
• Disposition Data (calling, contact, time of day)
• Preserving relevant history
• Business Rules links look at data age
• Purging of incremental data

18
Circulation ROI Opportunities
Where Were Going

• Measurable ROI contributions in 2005
• Converting direct mail to 3rd class
• Improving creative/response rates
• Targeting subscribers for ad awareness survey
• Providing cross-section of subscribers for market
  survey
• Unsuccessful ROI attempts
• Targeting door-to-door crew sales
• Direct mail as last channel
• Targeting lifestyle clusters
• Intangible ROI Contributions
• Do Not Call dialing history
• Distribution route planning
• Product development/geographic targeting

19
Circulation ROI Opportunities
Where Were Going

• Active initiatives in 2006
• Telemarketing by daypart (last connection)
• E-mail supplementing direct mail
• Multi-cell direct mail creative tests
• Alternate pricing/offer terms
• Targeting billed start programs by market segment
• Planned initiatives in 2006
• Integrated sales across multiple channels
• Direct mail driving kiosk traffic
• Subscriber value model and segmented loyalty
  program
• Value-building messaging
• Bundling opportunities
• Alternative retention touch-point timing

20
Multi-department Usage
Where Were Going

• Expanded MaaX usage desired, but slow to emerge
• Market Research segmentation modeling for the
  market at large
• Advertising consultative selling/client target
  delivery
• Product Development aligning market segments
  with content and distribution opportunities
• Distribution route-level inserting capability
  and overall route structure
• Broader acceptance likely requires dramatic
  cultural shifts
• Consolidated analytical resources (not silos)
• Comprehensive view of analytical tools
• Fully integrated databases and ubiquitous access
• Tangible business successes will accelerate
  expanded use

21
Discussion/QuestionsBe curious always. For
knowledge will not acquire you. You must acquire
it.-- Sudie Back