Best Practices for Data Quality

1
Best Practices for Data Quality

• Salesforce.com Customer Success
• March 2009

2
Agenda

• Business Driver
• Best Practices Overview
• Importance of Data Quality
• Data Quality Management
• Data Culture, Analyze, Plan, Standardize, Clean
  Enrich, Integrate Automate, Maintain
• Tools and Resources
• Additional Information Data Considerations
• De-duping, Merging, Migration, Integrations
  Mapping, Reporting, IDs

3
Business Driver

• All organizations buy a CRM tool to derive clear
  quantitative metrics on their business. Having
  bad data causes user frustration, poor adoption,
  and may lead to bad decisions due to inaccurate
  reports/metrics. The drive to have accurate data
  for an organization is critical since it can
  provide better and accurate visibility to
  increase revenue, reduce costs, increase customer
  profitability, and usage. It is important to
  understand Data Quality Management best practices
  using Salesforce.

4
Best Practices Overview

• Every successful implementation of Salesforce
  should have accurate data quality as a CRM goal.
  This is the key in generating the right metrics
  and truly understanding your customer. This
  presentation touches on all of the aspects of
  creating and maintaining good data quality.

5
Importance of Data QualityPitfalls of Bad Data

• Inaccurate report metrics
• Bad information wastes users time and effort
• Marketing wastes money and effort pursuing bad
  prospects
• Understanding your customer is impossible
• IT wastes time sifting through information and
  trying to make sense of it
• Operations has difficulty reconciling data
  against financial and other backend information
• User get frustrated, you lose valuable buy-in and
  adoption
• Analysts rate bad data as one of the top 3
  reasons for CRM failure

6
Importance of Data QualityThe Cost of Bad Data

75 of commercial businesses believe that they
are losing as much as 73 of revenue due to poor
data quality
75 of respondents

Experian - QAS U.S. Business Losing Revenue
Through Poorly Managed Customer Data

41 of respondents

• Poor data quality costs U.S. businesses more than
  600 billion annually

Data Warehousing Institute.
7
Data Quality Management Best Practices
8
Data Quality Management Best Practices

• Data Culture
• Analyze
• Plan
• Standardize, Clean Enrich
• Integrate Automate
• Maintain

9
Installing a Culture of Data Quality
1
2
3
Introduction Anything goes, adoption before data
integrity
Adaptation Recognize usage trends, Adapt
standards to reality
Standardization Train to common  best
practices 
Automation Make everybodys job easier, and make
the company more efficient
4
5
6
Reward / Repression Reinforce best
practices, with a carrot AND a stick
Integration Build tools to help multi department
tasks / processes
10
Analyze Data Profiling

• Understand your data sources
• Where is everything coming from
• Understand your datas weaknesses
• Rate your data consider completeness, accuracy,
  validity, relevance, integrity, level of
  standardization and duplication
• Pinpoint your problems and find ways of improving
  this
• Understand your mapping and usage of data
• Entity Level Mapping (Account, Opportunity,
  Contact)
• Field Level Mapping (state, city etc)
• Dont duplicate information between entities

11
Data Quality Analysis Example Phone Numbers
Not valid
Not complete
Not standardized
12
Plan Data Quality Management Strategy

• Create your Data Quality Plan
• Identify and Prioritize Goals
• Define Reports and Dashboards
• Find Sponsors and Owners
• Establish Budget
• Select Tools (i.e. for De-Duplication)
• Commit Resources
• Create Communication Plan
• Provide Rewards and Disincentives

13
Standardize, Clean Enrich
2
4
5
3
Standardize
Cleanse
Enrich (Optional)
De-dupe
Validate
Names
Company Name Address
Identify, Match Score
Load to Sandbox
Find Replace
acme incorp.- Acme Inc
J. Smith, John Smith 80
Hot ? HighCold ? Low
Hierarchy Data
Naming Conventions
Addresses
Merge
Validate Modify
Acme Inc HQ Acme UK
J. Smith, John Smith - John Smith
US, U.S, U.S.A - USA
Acme-Widgets-453
Data Transformation
Demographics
Postal Standards
Re-parent Child Records
Load to Production
Mergers, acquisitions, spin-offs
Account Division, Opportunity, Contact
Archiving Filtering
14
Standardize

• Create naming conventions and data standards and
  train all users
• Enforce standards with validation rules and
  pick-lists
• Implement procedures to standardize data before
  mass-importing
• Examples
• Accounts names Inc vs. Incorp., INC,
  incorporated Ltd vs LTD, Limited
• Opportunity names i.e. Name Product Acme
  250 Tschotchkes
• Country/State use validation to standardize TX
  vs Texas, USA vs. U.S.
• Postal Code use validation rules for proper
  format in US/CAN xxxxx-xxxx
• Contact info use pick lists for roles, titles,
  department Marketing vd. Mktg

Look for useful validation rules in Help
Training!
15
Cleanse

• Cleanse your Data
• Correct inaccuracies and inconsistencies
• Find and replace bad or missing data
• Remove or merge duplicates
• Leverage all users to fix data (its their data)
• Archive irrelevant and old data
• Leverage automated routines/tools
• Routinely reconcile Salesforce data against other
  data points/systems
• Prioritize your data control process
• Fix high visibility/usage information first
  (duplicates, addresses, emails)
• Fix business specific information next
  (opportunity types, stages etc)
• Remove duplicate fields (dont repeat account
  info on contact)
• Remove irrelevant fields

16
Enrich Data Augmentation

• Add missing information from 3rd party sources
• Phone, emails, address info, executive contact
  information,
• Company demographics, i.e. SIC, Industry,
  Revenue, Employees, Company Overview,
  Competitors, Fiscal Year
• Understand what data would provide additional
  value
• Poll your sales and marketing users and see what
  is needed
• Add internally available account intelligence
• Order history
• Purchasing Pattern
• Up-sell opportunity, i.e. products not yet owned

17
Integrate

• Understand your Masters
• Account Master (Unique ID stored on all other
  systems)
• Product Master
• Avoid stale and bad information from spreading
• Integrated solutions make it easier for users and
  more reliable for customers
• Create links or integrated apps to avoid
  duplicates in many systems
• Use and monitor review dates for key objects,
  i.e. account plans
• Archive or flag old/irrelevant data, i.e.
  contacts not updated in last x months
• Use workflow/approval processes before updating
  key fields
• Create a true 360 view of your customer
• Link order entry, fulfillment apps to
  Salesforce.com
• Make some information read only
• Use processes like case submission to update
  account master information

18
Five paths to integration success
A comprehensive family of technologies built on
top of the Force.com Web Services API
3
4
5
2
Integration Partners
Salesforce AppExchange
Developer Toolkits
19
Automate

• Salesforce.com partners can help!
• Leverage 3rd parties such as DB, Hoovers and
  others to periodically import and automatically
  update account records
• Inside Scoop or other partners to augment and
  cleanse information
• Workflow can help!
• Emails requesting missing information
  automatically sent to owner when a record is
  incomplete
• Force.com can help!
• Generate your own alerts through the API
• Script adds missing information
• Script updates erroneous information
• Create integration points
• Account Master/Product Master/Address Masters
• Address Cleansing
• Keep Relationships automated

20
Data Management ApplicationsForce.com
Appexchange app considerations list not all
encompassing
4
21
Data Quality Management Best PracticesNative
tools for managing data quality
Web-to-X
Excel Connector
Data Loader
Analyze and cleanse data
Leverage tools to prevent duplicates before
passing to Salesforce real-time
Import data from various file sources
Features
Data Quality Analytics
Use reports and dashboards to measure data quality
Use Validation Rules and Workflow
22
Maintain your Data
Safeguard your cleansed data and prevent future
deterioration
Train
Enforce
Monitor

• User Training
• Naming Conventions
• Address Conventions
• Dupe. Prevention Process
• Data Importing Policies

• Required Fields
• Default Values
• Data Validation Rules
• Workflow Field Updates
• Web-to-Lead Restrictions

• Data Quality Dashboards
• Data Quality Reassessment
• AppExchange Tools

Data quality decays rapidly enterprises should
follow a methodology that includes regular
measurement of data quality with goals for
improvement deployment of process improvements
technology

23
Maintain Data Quality Train and Communicate

• Users are trained that data integrity is a
  collective responsibility
• Users are trained on how data will be used
  (establish reasons for why data needs to be clean
  and accurate)
• Communicate data quality goals and progress
  updates
• Communicate policies and procedures
• Data is always changing so Data Quality processes
  are on-ongoing!

24
Maintain Data Quality Enforce

• Make sure Data Ownership and Sharing is accurate
• Critical to keep data in the right peoples hands
• Designate i.e. super user or geography lead to
  own regional data quality
• Make sure your hierarchy, groups, teams etc are
  kept up to date
• Proactively have meetings with management and
  stakeholders to understand org changes
• Define your CRUD rights on each profile
• Give users access rights to only the information
  they should have

25
Maintain Data Quality Monitor

• Use Reports and Dashboards to monitor and
  identify erroneous/missing data
• Data Quality owners spot check and monitor data
  on a regular basis
• Create Alerts and workflow to monitor data
• Define centralized processes for mass loads
• Implement Procedures and Policies
• Enlist everyone and hold them accountable
• Exception reports run monthly to find incomplete
  records or records with incorrect pick list
  values

26
Improvement Checklist

• Do you understand what data you have in
  Salesforce?
• Where is it coming from? What is wrong? What is
  the business impact?
• Have you cleaned your data?
• Identify data owners, ensure permissions are up
  to date (CRUD)
• Remove duplicates (manually and through tools or
  partners)
• Have you integrated and automated your data?
• Do your applications tie together?
• Are you using workflow for notifications? Are
  validation rules in place?
• Have you augmented your data?
• Have you added information to help your sales
  users?
• Do you monitor your data?
• Get the reports, dashboards and automation in
  place to monitor the health of your data
• Do you have a good data quality culture?
• Is everyone trained and contributing to your data
  quality? Do users trust the data?

27
Tools Resources

• AppExchange - Data Quality tools and offerings
• Data Quality Analysis Dashboards
• Integration Data Management
• Data Cleansing
• De-duplication Tools - Search term Data
  Quality
• Salesforce.com Data Tools
• Apex Data Loader and Excel Connector
• Dreamforce Data Quality Sessions
• Data, Data Everywhere
• No More Bad Data
• Wrangle Data Pump up the Configuration
• Turning Around your Data Quality Dilemma,
• Data Data Data Start your Spring Cleaning Now
• Salesforce Professional Services
• Data Quality Assessment and Cleansing Solutions

28
Thank You
29
Additional Information
30
Data Considerations

• Addressing duplicate records
• There will most likely be overlapping/duplicate
  data
• De-dupe either before or after you import the
  data from one system into the other
• Prior to importing into master account
• Export both data sets, merge into one and
  identify duplicates
• Merge/delete duplicates, import clean file
• After importing into master account
• Leverage de-dupe tools in salesforce.com
• Leverage de-dupe tools from partners
  (www.salesforce.com/appexchange)
• Use a custom field to flag each records source
  system
• Establish controls and processes to minimize dupe
  creation and to remove dupes on an ongoing basis
• Consider existing integrations and system of
  record for your data
• Develop rules for merging data
• When there are two records for the same entity
  (i.e., Account), which one wins?
• Newest record? Most complete record? Record from
  one of the databases? Most recently updated?
• Determine who will own the records if there are
  duplicates
• Impacts sharing rules, reporting, etc.
• Leverage for data cleansing that will ensue

31
Data Considerations

• Establish plan for migrating data
• Determine when master system becomes live/system
  of record (i.e., stop entering data into other
  system)
• Set date when you will extract all data from the
  system being merged
• How long will the merge take? How will you deal
  with interim data? New data blackout dates?
  Temporary data ID? How will you communicate to
  users?
• Ensure you have a complete copy of both data sets
  before attempting any merging just in case!
• Note if you have not done this type of work
  before, it is challenging.

32
Data Considerations

• Create mapping tables
• Every record in Salesforce is assigned a unique
  18-digit alpha-numeric, case sensitive id by
  salesforce.com
• Relationships between records are established
  based on these IDs (i.e., Activity related to a
  Contact)
• These IDs will change when you import data from
  one system to another, as the system will assign
  it a new ID
• In order to re-create the relationships between
  records (i.e., import Activities and associate to
  the appropriate Contact), you need to create a
  mapping table that will allow you to associate
  the OLD Contact ID with the new one

33
Data Considerations

• Create Mapping Tables (cont.)
• Create a temporary/mapping field on each object
  you will need to map for the old id (i.e., OLD
  ACCOUNT ID, LEGACY ID)
• Export all your data from the instance to be
  retired
• You can do this via the Weekly Export service,
  reports, the API, Excel Connector, AppExchange
  Data Loader or request a one-time full extract
  from customer support
• Dont forget about attachments and Documents!
• Consider dumping these to a file server with a
  unique naming strategy and use Custom Links from
  the salesforce.com objects to access
• When importing the data into the master Account,
  map the Account Id to the OLD ACCOUNT ID field
• You will then be able to export the new Account
  Id, OLD ACCOUNT ID and Account Name to act as
  your mapping table

34
Data Considerations

• Created Dates
• All records imported/migrated will have a Created
  Date to when the import occurs
• To retain original dates, create a custom field
  to import into (i.e., Original Create Date)
• If you are updating via the API, the new 7.0
  version will allow you to set the Created and
  Last Modified Dates http//www.sforce.com/resourc
  es/tn-17.jsp
• Note You must contact Salesforce support to
  enable this feature.
• History Tables
• Stage History for Opportunities / Case History
  for Cases
• Data cannot be migrated into these tables, this
  information must be stored elsewhere if you bring
  it over (Note field is not Reportable, so
  custom field is recommended)
• Unique Ids (system generated)
• Record Ids are unique and cannot be imported
• Imported records are assigned new Id, it is a
  good idea to import the old Id into a custom
  field for mapping purposes
• Features that reference (i.e., Custom Links)
  unique ids of other objects (i.e., a report) must
  also be updated

35
Data Considerations

• Reports
• When reporting on migrated data, date filters
  must take into account standard and custom date
  fields (i.e., Create Date and Original Create
  Date)
• Other filters on existing reports must be
  reviewed to ensure they are still relevant/apply
  to all data
• Record Types (EE/UE only)
• If one of the salesforce.com instances leverages
  record types, all records added from the other
  instance must be assigned a Record Type
• Record Types can be updated through the API, not
  through the import wizard
• Record Type assignment must also be aligned with
  user Profiles

36
Data Considerations

• What if data is inadvertently
• Deleted
• Restore from the Recycle Bin (retained for 30
  days)
• Restore missing data from backups
• Merged
• There is no way to un-merge data
• Clean up/work with merged records, OR
• Delete and restore from back ups
• Imported incorrectly
• Mass transfer (if you can)
• Delete and re-import into proper area
• Consider tagging batches with a custom field
  indicating the load/batch number in case you need
  to reverse

37
Advanced Data Quality