This chapter’s objectives are:

• 10.1- Describe the considerations when importing, updating, transferring, and mass deleting data (e.g., CSV files, field matching, matching types, record IDs, external IDs, duplicate records).
• 10.2- Given a scenario, identify tools and use cases for managing data a (e.g., dataloader, data import wizard).
• 10.3- Describe the capabilities and implications of the data validation tools.
• 10.4- Describe the different ways to back up data (e.g., weekly data export service, exports, dataloader).

10.1- Describe the considerations when importing, updating, transferring, and mass deleting data (e.g., CSV files, field matching, matching types, record IDs, external IDs, duplicate records)

Check this excellent Video series: http://salesforce.vidyard.com/watch/ARIjWm2qrDkJVJxEhReFug
http://sforce.co/dataimport

Record ID:

• Each Record in Salesforce has a unique ID (its primary key within Salesforce). 2 versions:
  • The 15 character ID (case sensitive), which is displayed in the user interface.
  • The 18 character ID, which is returned via the API by default. The 18 character ID is a combination of the 15 character ID with 3 characters added to ensure that it is unique on a case insensitive basis (this is to support legacy applications). for example, The API (e.g. Data Loader) will accept 15 or 18 character IDs, but will return 18 character IDs by default
• All Record in Salesforce have an owner. If you omit it in an import, then the user doing the import becomes the owner.
• Each child record has Parent Id. Should be specified in the import file. Import fails if omitted.
• Opportunity needs OwnerID and AccountID.

Recycle Bin:

• Deleted records are stored in your organization’s recycle bin for 15 days before they are permanently erased.
• The number of records your recycle bin can store is limited to 25 times your storage limit in MB.
• If this limit is exceeded, then Salesforce automatically purges the oldest records in the recycle bin if they have been in the recycle bin for at least two hours.

Before Importing or Updating:

• Check every Object and its fields:
• Export existing Records to see the format
• Access this Wizard: Administer | Data Management | Data Export
  • Choose the Objects that you want to export
  • Export will be completed in background, you get email when done
  • You can download the Zip file within 48 hours, then deleted automatically
• You can also export with Data Loader
• With Data Loader, no need for email address when inserting. Only needed are the mandatory fields. Also no need for Name, only First Name and Last Name. Name will be generated from these 2.

Before Importing or Updating TIPS:

• Sort and Filter: when you export any excel or CSV from any source (Salesforce Data loader, Google, LinkedIn), use Excel Sort and Filter to go through the records and clean them (delete duplicates, and keep 1 country naming)
• VLookup: matches Salesforce user ID to record owner. Or parent record ID to child record.
  • For example, you have a list of new contacts to be inserted to Salesforce and assigned to accounts.
  • Export the contact Object with: ID, ACCOUNTID and name fields (first csv)
  • Export the account Object with IS and account name fields (second csv)
  • Now get the account name for each conatct and match them to the first csv (make sure to use the first column as VLOOKUP works on the first column only)
  • User the VLOOKUP function to fill the ACCOUNTID column in the first csv
  • Use DL to update records using the csv 1 on the contact object

Export Contact Object with below fields	Export Account Object ID and name	VLOOKUP
Add the account name in English	Make sure the first column is the common field.	Now we need to fill the 3rd column highlighted in yellow with the ID of the account from the second table in the middle. =VLOOKUP(A1,accID.csv!$A$1:$B$79,2,FALSE) Match the first cell A1 against the entire table ($A$1:$B$79) in accID.csv and return the value of the second column.
Need to fill the Account ID with the right Account ID	We have the Account name and its matching ID which we will fill on the ACCOUNTID field of the Contact Object and import it	Now when you fill the ACCOUNTID, you can update the record of these contacts with DL. Use 5 records as a test first!!

Another Example: import accounts with owner ID field that should be userID.

• Format Cells in imported csv: make sure that phone numbers are entered correctly Also Zip codes the leading zeros are hidden. Format Cell in Special – Zip Code to get the leading zeros.
• Find and Replace: replace a value with another in the CSV: for example, the phone number you eed it to be 7 digits without any space and dash. (replace dash and space with nothing)
• Concatenate: used to combine text from different cells into 1. For example: area code + phone, 2 address fields into 1, combine username with email suffix. Formula is: CONCATENATE(CELL1,CELL2).
• Text to Columns: Separates text in 1 field, into 2 different fields. For example name, you want it first name and last name separated.
• Save as CSV: Salesforce uses CSV, it does not support Excel formula and formats, so all formulas should be converted to values. CSV supports 1 sheet only in 1 file.
• Always Import a small sample first!
• You can also use the Mass Delete to delete Records: choose the Object, then the criteria for the records
• Turn off any Workflow or Validation rule (records not matching the rule will fail to import) before any Import!!!
• Backup: Use the Data Export – included in EE, PE need to purchase it separately
  • Available in LE and Classic.
  • You can also use the Data Loader Export and Export All
• Steps before Import:
  • Clean Data: example duplicate companies will ruin reporting efficiency: delete duplicate, spelling, naming conventions, fill missing data in fields
  • Prepare Import File: match fields – shortcut is exporting using DL and use the fields already matched – make sure you have Record Owner ID in each record!! If object has parent add parentID!
  • Prepare Salesforce: create custom fields, fill the picklist values in picklist fields, create eternal IDs
  • After importing: make sure to maintain best practice for clean data!!

10.2- Given a scenario, identify tools and use cases for managing data a (e.g., dataloader, data import wizard)

Import Wizards: 2 Import Wizards in Salesforce

1 – Import Accounts & Contacts

• Accept pre-formatted data from common contact data sources (Outlook, Gmail, LinkedIn…)
• Up to 50,000 records can be imported each session
• Step 1 – Exporting Data from source app (outlook, linkedin, gmail…):
  • A sample account file should have: name, address, email, phone
  • A sample contact file: contact name, email, phone, title…
  • It is important to know the source fields because you should map them to Salesforce fields
  • Exporting data from source depends on the application itself. Gmail: it should be CSV!
  • If your only option is Excel files, you need to save it as CSV
  • After exporting, check the file and clean it by using Filter on the top and make it match the Salesforce contacts and Accoutns fields as much as you can

    

  • Remember some fields should be manually mapped into Salesforce
  • Note that an email is MANADATOY to import the record
• Step 2 – Importing that CSV file into Salesforce:
  • Click You Name | My Settings | Import | Import Accounts and Contacts
  • Click on:
  • This will open the export page that lets you choose the source company and CSV
  • Select the CSV, click Upload
  • The Wizard will check the file and gives 3 tabs: mapped fields, unmapped fields and Do not Import Fields.
  • It tries to map fields. IT will ask you to map the other fields.
  • All Unmapped fields should be mapped or Do Not Import. Also check all mapped fields, there might be miss-mapping! Re-map these if any.
  • Click Begin Mapping to start. You will get an email once done!
  • The account in the CSV will also be created

    
    

2 – Data Import Wizard

• This is a general-use wizard and can import records into the following objects: Accounts, Contacts, Leads, Solutions, Custom Objects
• Note that many standard objects are not supported by several of the wizards. For instance, you cannot mass transfer opportunities (although the account transfer wizard will allow you transfer opportunities related to an account).
• Before importing, Export existing Records to see the format (check above)
• IMP: During Import, you can specify Assignment Rules, campaigns (in case of importing leads), trigger Workflow Rules and Processes? (default is OFF)
• Access this Wizard: Administer | Data Management | Data Import Wizard

3- Other Data Manipulation Wizards available through the user interface

• Mass Transfer Records: transfer Records within Account and Leads standard Objects and custom Objects from User A to B. You can also specify extra filters. To mass transfer Opportunities, get an app from lab called Mass Update Wizard
• Mass Delete Records: for standards objects only. Excludes opportunities. Accounts and Leads and some others. No Opportunities.
• Mass Transfer Approval Requests: to manage multiple pending approval requests
• Mass Reassign Account Teams
  
• Mass Reassign Opportunity Teams
  
• Mass Update Addresses: update the country/territory or state/province field on all records with a standard address field

Data Loader:

• The Data Loader is used to manipulate Salesforce data via the API, and can load up to 5 million records per job. It helps you:
  • Import data into Salesforce objects.
  • Extract (Export) data from database objects.
  • Delete data by exporting the ID fields for the data you wish to delete and using that source to specify deletions
• It can run in both GUI and Command Line
  • GUI—When you use the user interface, you work interactively to specify the configuration parameters, CSV files used for import and export, and the field mappings that map the field names in your import file with the field names in Salesforce.
  • Command line (Windows only)—When you use the command line, you specify the configuration, data sources, mappings, and actions in files. This enables you to set up Data Loader for automated processing.
• Data Loader supports all objects, including custom objects
• The Data Loader requires the use of the Force.com API. If your Salesforce edition allows the use of the API (Enterprise, Unlimited and Developer Editions),
• You can download the Data Loader from: Setup | Administer | Data Management 
• When you install it, it asks for JRE. Make sure to download it and install it.

  

• Check the Data Loader PDF User Guide.
• To use the Data Loader, you need the “API Enabled” permission.
• When you login, make sure to append your security token to the password, as this is API access
• Get your Token in My Settings | Personal page
• Also, make sure to specify the Login URL:
  • For Production environment (or Developer Edition environment), use https://login.salesforce.com/ and
  • For Sandbox environment, use https://test.salesforce.com/. With Sandbox, make sure to use the Sandbox Username as opposed to the Production username.
• When you launch it, you can:
  • Export: Extracts records from Salesforce; excludes records in your recycle bin.
    • This way, you know the Object structure
    • When you Export a SOQL (Salesforce Object Query Language) query is generated
  • Export All: Extracts records from Salesforce; includes records in your recycle bin.
  • Insert: Creates new records.
    • Add new Records to the CSV object
    • No need to give the ID. Salesforce will generate and give ID
    • No need for email address. Only needed are the mandatory fields.
    • No need for Name, only First Name and Last Name. Name will be generated from these 2.
  • Update: Updates existing records. The Salesforce ID of each record must be defined in the source file.
  • Upsert: Updates an existing record if present; if record not present, creates a new record. Matching can be based on Salesforce ID or external IDs.
    • Update + Insert
  • Delete: Deletes records. The Salesforce ID must be defined within the source file.
  • Hard Delete: Permanently deletes; records are not moved to the recycle bin. The Salesforce ID must be defined within the source file.
• Important Notes to consider:
  • Data Loader recognizes 1 field only as the street address. You must combine them into 1.
  • Data Loader matches on Record ID only. If you have any other duplicate field, it won’t see it!
  • Make sure to turn off Workflow as Data Loader will fire any Workflow that matches the rule criteria. For example, if the Admin should receive email for every new Contact, then 50 emails sent if you add 50 by Data Loader!
  • Data Loader deals with 1 Object at a time.

Wizards vs. Data Loader

• You need the API to use the Data Loader that is available with these editions: Enterprise, Unlimited and Developer, or a cost for lower editions). Professional Edition organizations admin can enable Data Loader API for up to 2 weeks by calling support.
• The wizards offer options that are not available via the Data Loader (e.g. the ability to specify which lead assignment rule to process, or to disable workflow rules when loading data — workflow rules will always be triggered by the Data Loader).
• The permissions are different.  Access to the wizards are driven by specific permissions (e.g. ‘Import Personal Contacts’ and ‘Import Leads’ permissions on profile/permission set), while Data Loader permissions are driven by the global ‘API Enabled’ permission on profile/permission set.
• The import wizards can load files with up to 50,000 records per job.  Transfer wizards can transfer up to 250 records at a time.  The Data Loader, however, can process up to 5 million records per job.  Therefore, if you need to process large volumes of data (e.g. create 250k accounts or transfer 10k accounts), use the Data Loader.
• An administrator can save mapping using the Data Loader and even schedule jobs on a local server

  

Other Data Manipulation Tools:

In addition to the Data Import Wizard and the Data Loader that were discussed in the previous lecture, there are other Data Management tools available for se with Salesforce.

• Force.com Workbench (Web, Free): Allows you to explore and manipulate data, metadata, and perform a variety of other functions. Built by Salesforce.com (may not be officially supported).
  
• DataLoader.io – This is a web based data loading solution that has free and paid options available. You can use this browser based tool much like the locally installed Data Loader.
• Talend – This is a robust data loading tool that is open-source (free). Many organizations use Talend to schedule data loads from other systems, into Salesforce, to perform system integrations.
• Jitterbit – This is a popular data loading tool that is available via the AppExchange.

10.3- Describe the capabilities and implications of the data validation tools

Validation Rules:

• Validation rules are used to conditionally prevent a record from being saved,
• Validation Rules are used to enforce data quality.
• Validation Rules are case sensitive
• They are applied on the Object level. To access them, go to the Object (standard or Custom), then go to Validation Rules section
• Validation rules will impact API usage, web-to-lead, and web-to-case submissions. Make sure to structure your validation rules so that they will not unintentionally interfere with these operations (such as rejecting a web-to-lead submission or Data Loader). In some scenarios, you may need to disable validation rules when importing or updating data, and reinstate them afterwards.
• Too many validation rules can be frustrating from a user interface perspective, as the error is not presented until after the user attempts to save the record.  Make fields required when possible (as they are indicated as required in the user interface), and employ other usability features as appropriate (for instance, create a page layout section for information required when an opportunity is lost if more than 1 field is captured).
• Some formula arguments (such as VLOOKUP) can only be used in validation rules.
• Validation Rules will trigger all the time: API, Integration, even if field is hidden

Validation Rules examples:

• https://help.salesforce.com/HTViewHelpDoc?id=fields_useful_field_validation_formulas.htm

Lookup Filters:

• Lookup filters can be applied to custom relationship fields to ensure that related data meets certain criteria (e.g. campaigns can only be associated with “Approved” venues).
• A lookup filter limits which records can be associated within an object relationship.
• For example, when selecting a venue (parent) for a campaign (child), users should only be able to select approved venues. You configure the lookup filter on the “venue” lookup relationship field in the “campaign” Object.
• Go to the filed in question, go to “Lookup Filter” and fill it accordingly
• Now when users try to choose a value that is not approved, they get error message.

  

Dirty Data and Data Quality:

• Dirty data is defined as inaccurate, incomplete or erroneous data, especially in a computer system or database:
  • Duplicate records (e.g. 2 leads with the same information, etc.)
  • Incomplete records (e.g. a lead without an email address or phone number)
  • Inaccurate records (e.g. an opportunity with an inaccurate close date)
• Data quality refers to the usability and accuracy of data: Completely de-duplicated, properly formatted, populated accurate data is considered clean, and of high quality
• Maintaining Good Data Quality: The standard tools used to create records (import wizards, Data Loader, web-to-lead) are not designed to thoroughly manage data quality – so you must take action to maintain your data!
  • Make sure your import files are clean (removed of duplicates, properly formatted, etc.) prior to importing data. Admittedly, this is not always possible or practical.
  • Use the leads object to store lower quality or unverified data. Only leads that are qualified and of high data quality should be converted to accounts and contacts.
  • Train users to search for existing leads/contacts prior to creating a new lead/contact.
  • Use Data.com Duplicate Management (free as of Spring ’15) or third party tools to prevent duplicate records from being created.
  • Use required fields (either via field configuration or page layout), validation rules (look at the REGEX function for complex formatting requirements, such as phone numbers), filtered lookups, and other tools and features to ensure data is entered completely and formatted properly.
  • Use Data.com Clean (additional license fee as of Spring ’15), another third party tool, or manually cleanse existing dirty data.
• Manually Cleansing Duplicate Records:
  • Lead: Find Duplicates (Button)
  • Accounts: Merge Accounts (from the account tab)
  • Contacts: Merge contacts button (from an account record)
• Data.com Duplicate Management: option available by data,com to identify duplicate records through:
  • Matching Rules specify which fields are evaluated to determine if a duplicate is detected (e.g. First Name, Last Name, Email). You can leverage the standard rules for some objects (lead, contact, account), or create your own rules if you want to create your own logic or reference custom objects. Matching rules can specify fuzzy or exact field matches.
  • Duplicate Rules allow the administrator to specify the matching rule(s) (above) that should be evaluated when a record is created or modified, and what should occur as a result (allow or block the action).
• Data Quality Tools (Third Party): free and paid tools like: Demand Tools, People Import, Dupe Blocker, etc…

Promote and retain good data quality within Salesforce:

Maintaining good data quality is an art. Broad strokes include:

• 1) Understanding your data.
• 2) Preventing poor quality data from being created.
• 3) Cleansing existing poor quality data.
  

10.4- Describe the different ways to back up data (e.g., weekly data export service, exports, dataloader)

Data export allows an organization to export their entire Salesforce database (all records and attachments) into a single archive file. This export can be run once every 6 days manually, or scheduled to run automatically on a weekly or monthly basis (depending on your edition).

• Access it in Administer | Data Management | Data Export
• Make a manual backup (once every 6 days), or schedule backup
• Developer Edition schedule Backup is 1 per month.
• Production is 1 per week (no Sandbox support)
• Salesforce creates a zip archive of CSV files and emails the user who scheduled the export when it’s ready. Exports complete as soon as possible, however we can’t guarantee the date and time of completion.
• Large exports are broken up into multiple files. Follow the link in the email or click Data Export to download the zip file. Zip files are deleted 48 hours after the email is sent.
• Backup Data Export Considerations
  • No Sandbox Support: you can request, but it won’t be processed
  • File Size Considerations: A single .csv file can hold up to approximately 150 MB of data. If an object contains more than 150 MB of data, multiple .csv files are created for the object.
  • Each .zip archive file contains one or more .csv files and can be to 512 MB (approximately). The export generates multiple .zip files if the total size of exported data is greater than 512 MB.

Other Noes

Implementing State and Country Picklists

• Before enabling it, you enter the country and state as text field
• Access it in Administer | Data Management | State and Country Picklists
• 3 steps before enabling it in your organization:
  • Configure it: select which countries to be included and you can edit (edit country name and add/remove states). Example, I picked 5 countries and added 2 states in for UAE: Dubai and Abu Dhabi.
  • Scan the organization for state and country data in records and reference data. You’ll receive two emails when the scan is complete: one regarding affected address data and one regarding affected customizations. Click the links in the emails to see how your data and customizations are affected, and confirm to change the records, for example, USA make it United States of America.
  • Convert the data as per your changes
  • Turn on state and country/territory picklists
  • You can rescan when needed

  

Enabling Audit Fields

The Audit Fields feature allows an administrator to populate the values in CreatedBy, CreatedDate, LastModifiedBy, and LastModifiedDate fields. Values in these fields typically cannot be specified (including by a system administrator). Therefore enabling Audit Fields is a vital part of many data migration strategies. Sometime, you need to set values for fields that aren’t normally editable, for example, when migrating data to Salesforce. In that situation, it’s often preferable to replace the Salesforce record creation date with the date on which the record was created in the original system. Or when working with a record that has an inactive owner, you often want to change ownership of the record to an active owner. To enable it: Customize | User Interface | Enable “Set Audit Fields upon Record Creation” and “Update Records with Inactive Owners” User Permissions (Description Link Salesforce).