Best Practices for Data Quality

Salesforce.com Customer Success March 2009

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

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.

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.

Importance of Data Quality

Pitfalls 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

Importance of Data Quality

The Cost of Bad Data

75% of respondents

75% of commercial businesses believe that they are losing as much as 73% of revenue due to poor data quality
Experian - QAS U.S. Business Losing Revenue Through Poorly Managed Customer Data

41% of Poor data quality costs U.S. businesses more than respondents $600 billion annually

Data Warehousing Institute.

Data Quality Management Best Practices

Data Quality Management Best Practices

Data Culture Analyze

Plan
Standardize, Clean & Enrich Integrate & Automate Maintain

Installing a Culture of Data Quality

1
Introduction
Anything goes, adoption before data integrity

2
Adaptation
Recognize usage trends, Adapt standards to reality

3
Standardization
Train to common best practices

6
Automation
Make everybodys job easier, and make the company more efficient

5
Integration
Build tools to help multi department tasks / processes Reinforce best practices, with a carrot AND a stick

4
Reward / Repression

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

Data Quality Analysis Example: Phone Numbers

Not valid

Not complete

Not standardized

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

Standardize, Clean & Enrich

1
Standardize
Names
acme incorp.-> Acme Inc

2
Cleanse
Find & Replace
Hot High Cold Low

3
Enrich (Optional)
Company Name & Address

4
De-dupe
Identify, Match & Score
J. Smith, John Smith 80%

5
Validate
Load to Sandbox

Addresses
US, U.S, U.S.A -> USA

Naming Conventions
Acme-Widgets-453

Hierarchy Data
Acme Inc HQ Acme UK

Merge
J. Smith, John Smith -> John Smith

Validate & Modify

Postal Standards

Data Transformation
Mergers, acquisitions, spin-offs

Demographics

Re-parent Child Records

Account: Division, Opportunity, Contact

Load to Production

Archiving & Filtering

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!

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

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

Integrate
Acct Master based on lifecycle

Accounts Quick Arrow View Central Siebel

Product SAP Oracle (Custom)

Pricing SAP

Data Warehouse ???

Leads/Oppty

Catapult IMI Volume View Central

Understand your Masters

Integration Tools

Internet

EAI/Middleware Tibco, WebMethods (Alcatel) BizTalk

ETL Assorted

Standards based Integration SOA/Web Services XML

Account Master (Unique ID stored on all other systems)

Product Master

Internet

Internet

SFA Data Enrichment

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

: Five paths to integration success

A comprehensive family of technologies built on top of the Force.com Web Services API

1
Salesforce AppExchange

2
Native Desktop Connectors

3
Native ERP Connectors

4
Integration Partners

5
Developer Toolkits

Automate
Salesforce.com partners can help!
Leverage 3rd parties such as D&B, 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

Data Management Applications

Force.com Appexchange app considerations list not all encompassing

Low Complexity

Medium Complexity

High Complexity

Composite Apps
Enterprise Mash-ups Rich user interface Scontrol

Application Integration
Real-time integration
Multi-step integration Human workflow

Data Integration
Data migration Data replication Bulk Data Transfers

Data Cleansing
Data de-duplication Data assessment

Data Quality Management Best Practices

Native tools for managing data quality Web-to-X Excel Connector
Analyze and cleanse data

Data Loader

Leverage tools to prevent duplicates before passing to Salesforce real-time

Import data from various file sources

Features

Data Quality Analytics

Use Validation Rules and Workflow

Use reports and dashboards to measure data quality

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

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!

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

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

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?

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

Thank You

Additional Information

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

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.

Data Considerations
Create mapping tables
Every record in Salesforce is assigned a unique 18-digit alphanumeric, 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

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

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/resources/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

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

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

Advanced Data Quality