Welcome to the Sixth Level

Data Quality
Excel ConnectorMaria Pergolino (w slides taken from salesforce.com/community

ADMINISTRATOR DATA PAIN POINTSMass Data ImportImport Activities/Cases in MassMass Updates/Territory RealignmentField ValidationEnsure Opportunity Close Dates are filled inDe-DuplicationCorrect Data LinkingOpportunities are linked to correct AccountsRemoval of Unused and/or Unclean DataActivities from previous years/ Disqualified Leads.

*

*

DATA QUALITY BENEFITSDrive User AdoptionIncrease ProductivityProvide data visibilityAccurate AnalyticsCustomer Satisfaction

*

*

DATA QUALITY BEST PRACTICESSearch before you createStandardize naming conventionsMerge recordsUse the Excel ConnectorSet up data quality dashboardsAdopt partner solutions

*

REMEMBER- GIGO (GARBAGE IN, GARBAGE OUT)Search before you create - Teach users to search before creating records to reduce data duplication. Duplicate data is inevitable, but searching first helps keep it to a manageable level. Standardize naming conventions - Continue to reinforce standardized naming conventions for company names and addresses. For example, is it U.S.A., USA, or US? Is it Acme or Acme LLC? Merge records - Although having duplicate records may be inevitable, having a process to keep on top of duplicates is critical. Within Salesforce CRM, you can merge duplicate Lead, Account, and Contact data with easy-to-use wizards. Use the Excel Connector - The Excel Connector provides bi-directional access to Salesforce CRM from Excel spreadsheets, so you can import records from Excel directly into Salesforce CRM. This tool also lets you perform mass updates, which can be very handy for data-cleansing projects. Although the connector is easy to install and easy to use, you need to be careful anytime you're manipulating large amounts of live data. Set up data quality dashboards - With Salesforce CRM, you can set up such dashboards to highlight when fields are left blank. For example, you can create a dashboard that indicates the number of times the field "main competitor" was left blank. You can also track overdue tasks or the last time a record was updated. Adopt partner solutions - You can use partner solutions from the Force.com AppExchange to help improve data quality. For example, RingLead Declone and DemandTools both automatically de-duplicate your manually created Leads, Web forms, and uploaded lists. *

EXCEL CONNECTOR - DESCRIPTIONCostFree toolLimitsIt is not a supported product. If you have questions, please use the Excel Connector Documentation and the Force.com discussion boards on the topic.Open source excel add-inBi-directional access to Salesforce.com from Excel spreadsheetLeverages sforce Toolkit for Office

*

EXCEL CONNECTOR - DESCRIPTIONPurposeEnabler: Data Management ActivitiesSecurityLogin employs the role and the profile

Drives the visibility and permissionsMeta-dataBased on Partner WSDL Real time and complete representation of the meta-data

*

*Meta-data- you can see the custom fields

EXCEL CONNECTOR IMPORTANT LINKSTo learn how to use the Excel Connector, see the Excel Connector Training. For tips on loading data using the Excel Connector, see How to Upload Data into Salesforce. For more information about the Excel Connector, see the Excel Connector Documentation. For community discussion board on the Excel Connector, see the Dedicated Community Site.

*

IMPORTANT NOTEYOU ARE MANIPULATING YOUR DATA AND CAN NOT UNDO CHANGESTEST IN SANDBOX OR IN DEVELOPER ACCOUNTBACK-UP YOUR DATA

*

DOWNLOADING TOOLDownload the Office ConnectorSetup | Desktop Integration | Connect for OfficeDownload the Excel Connector For Professional Edition For Enterprise Edition

*

USING TOOL SIGNING INSign into the Connector (set the API to 14.0)Change endpoint to test.salesforce.com if using a sandboxAPI Token unless Trusted IPTable Query Wizard

*

*

DEMOUsing Query WizardEditing a cell, Updating dataInserting a new recordQuerying a recordDeleting a record

*

1,048,576 rows by 16,384 columns32,767 characters per cell

65,536 rows by 256 columns32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

EXCEL 2003 vs Excel 2007 Excel 2003Excel 2007

*

*

Whats up with 15-char and 18-char IDs?UI vs APIThe User Interface returns 15-character IDsThe Application Programming Interface returns 18-character IDs

User Interface returns 15-char IDs

*

15-char and 18-char IDs 15-char IDs are case-sensitive, which Excel doesnt distinguish:Excel thinks theyre the same!

These IDs came from a Report

*

15-char and 18-char IDs18-char IDs are case-insensitive, which Excel can distinguish:

Extra characters make the differenceThese IDs came from Data Loader

*

15-char and 18-char IDsUse Excel Connectors FixID() function:

Bottom line: whenever you work with IDs in Excel,
always make sure to use 18-character IDs.

*

*

*

REMEMBER- GIGO (GARBAGE IN, GARBAGE OUT)Search before you create - Teach users to search before creating records to reduce data duplication. Duplicate data is inevitable, but searching first helps keep it to a manageable level. Standardize naming conventions - Continue to reinforce standardized naming conventions for company names and addresses. For example, is it U.S.A., USA, or US? Is it Acme or Acme LLC? Merge records - Although having duplicate records may be inevitable, having a process to keep on top of duplicates is critical. Within Salesforce CRM, you can merge duplicate Lead, Account, and Contact data with easy-to-use wizards. Use the Excel Connector - The Excel Connector provides bi-directional access to Salesforce CRM from Excel spreadsheets, so you can import records from Excel directly into Salesforce CRM. This tool also lets you perform mass updates, which can be very handy for data-cleansing projects. Although the connector is easy to install and easy to use, you need to be careful anytime you're manipulating large amounts of live data. Set up data quality dashboards - With Salesforce CRM, you can set up such dashboards to highlight when fields are left blank. For example, you can create a dashboard that indicates the number of times the field "main competitor" was left blank. You can also track overdue tasks or the last time a record was updated. Adopt partner solutions - You can use partner solutions from the Force.com AppExchange to help improve data quality. For example, RingLead Declone and DemandTools both automatically de-duplicate your manually created Leads, Web forms, and uploaded lists. **Meta-data- you can see the custom fields*

**

**

*

*

*