Download - 7 Step Data Cleanse: Salesforce Hygiene
7 Step Data Cleanse
Your lovely presenters
2
Marc Baizman
@mbaizman
Nonprofit technology coach, Salesforce and Google Apps, improv and sketch comedy
performer, unrepentant nerd. (Speaks Japanese)
Ehren Foss
@ehrenfoss
Salesforce.com data wrangler, developer, gamer, hotkey afficianado, outdoor
enthusiast(Lefty)
Photohere
Photohere
Wait…what kind of cleanse?
http://commons.wikimedia.org/wiki/File:Master_Cleanse_refrigerator.jpg
Stop me if you’ve heard this one…
“I’m not sure this report is showing us the right information.”
“After this campaign, we’ll update our donor data.”
“We need to import ALL of our historical data.”
“I wish people would enter the right information into the system.”
“We can get an intern to clean this up.”
Symptoms
Symptoms
Symptoms
Doesn’t match Finance!
Dear <<FirstName>>,
We would like to thank you for your generous gift of $NULL. This will help us give NaN rescued cats to starving children.
Sincerely,
Error Division by Zero
Why?! Make the pain stop, please!
Dirty
data!
No Culture of “Clean
Data”
Little or poor
training
No automated prevention
systems No data hygiene policies
Historical data
import
Time for your cleanse!
1. Strategy
2. Accountability
3. Data Quality Reports/Dashboards
4. Automation: Validation and Workflow Rules
5. Help Your Users
6. Objects and Fields
7. Apps
1: StrategyIt’s as easy as one, two, five
Strategy• How does data serve your mission?
• Does data jeopardize your mission?– What should always/never happen?
• A good strategy means:– A culture of good data
– Practices & process survive staff turnover
– Tools, objects, and fields change – but data stays clean
Baby StepsToday: Sit down with intern, explain data
rules, document rules for next time
This week: Create validation rule
This month: Review reports with your E.D.
This year: Decrease duplicates by 90%. Mailing files should take no more than an hour to prepare.
This decade: Make sure data is never a barrier to growth. 50,000 more meals served, 10,000 duplicates removed
2: AccountabilityWe have met the enemy, and they are us
Accountability: A Clean Data Culture
• Who is responsible for data?– Board and Leadership
• Support the culture, drive data priorities, give rewards & accolades
– IT / Salesforce staff • Integrations, data sources.
Training and “data ambassadors.”
– Directors• Responsible for team’s data
– Staff• Fundraisers, interns, accounting:
responsible for data they own or touch.
Accountability Tactics
• Appoint a “data czar” (coach)
• Public dashboards / reports
• Topic in regular staff meetings
• Leadership: “If it's not in Salesforce it doesn't exist.”
• Section in annual report / board reports
• “Data Day” : all staff works on data cleanup
• Identify champions & coaches
Rewards & Punishments
• Wall of fame / wall of shame
• Data Rockstar / Data Dunce
• Competition and/or collaboration
• Show me the $$– Amazon / Starbucks cards
– PTO…
– Bonuses
Adoption Dashboards
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000004gHhLEAU
3: Reports & DashboardsOooo… shiny!
Data Quality Reports/Dashboards
Missing DATA?
http://www.flickr.com/photos/smallbrainfield/381731208/
Create “Missing Data” Reports!
http://www.flickr.com/photos/katiethebeau/8387139427/
Data Quality Reports – regulars!
Cross Filters are your friends!
Use the “without” filter when looking for missing info
http://www.salesforce.com/us/developer/docs/workbook_analytics/workbook_analytics.pdf
Schedule Reports and Dashboards!
What are your “must have” fields?Try using images in formula fields!
http://assets.salesforce.com/pdf/getting_started_with_images_v1.pdf
Anything owned by inactive users?
INACTIVE
Mass Transfer Tool
https://na11.salesforce.com/ui/setup/own/BulkTransferPage
Data Quality Analysis Dashboards
https://appexchange.salesforce.com/listingDetail?listingId=a0N300000016cshEAA
4: AutomationWe welcome our new validation and workflow rule overlords
Validation RULES!• Before a record is saved:
1. Check for bad things
2. Inform the user what’s wrong
• Automatically! Works for integrations too
Error: Invalid Data
Review all error messages below to correct your data
You must solicit user feedback about your validation rules
Validation Rule ideas• Dates
– End before Start
– Too far in the future / past
• Conditionally required fields – Per record type
– Per status or picklist option
• At least…– 2 letters in First Name, Last Name
– $5 for Donation value
Internship Validation Rule: Job End Date can’t be more than 1 year in the future!
Validation Rule tips…• Combine with formula fields for more
powerful cross-object validation
• Check old data after you create a new rule!– Bad data will remain unless record is edited
– Keep your report for this, re-use periodically to double check
• Let your data guide you – Don’t go rule crazy
• Don’t reinvent the wheel. Ideas:– http://login.salesforce.com/help/doc/en/fie
lds_useful_field_validation_formulas.htm
Validation Goldilocks
Too many validation rules?
No data entered, user rebellion
Not enough required fields / validation rules?
Bad data
Just right
Less new bad data
Happy users deem you: “Data Hero”
Automation: Workflow Rules: It’s like having your own robot!
What can they do?
Update fields
Create tasks
Send emails
Send outbound messages
Workflow Rule Process
Create Rule Criteria Action Activate Adopt Profit!
Send Email?
Upd
ate
Field
?
On Edit?
2 W
eeks
Lat
er?
Docum
ent!
Trai
ning
Test
ing
Create Workflow Rule
Field Update
Email Alert
Task
Some Data Quality workflow ideas!
Update Opportunity Name to “Account – Donation Type –
Date”
Update a custom date field whenever the Owner is
Changed.
Create a task to review a Contact if it hasn’t been modified in over a year.
Send an email if someone enters a donation missing
some key info.
https://help.salesforce.com/HTViewHelpDoc?id=workflow_examples.htm&language=en_US
Workflow Gotchas – Consistent Processes?
http://www.flickr.com/photos/rakka/420157350/
Workflow Gotchas – Documentation?
http://www.flickr.com/photos/nzdave/491411288/
Workflow Gotchas – Test first!
http://www.flickr.com/photos/gywst/1426287043/
Workflow gotchas: Last in line!
http://www.flickr.com/photos/sarae/2465725950/
5: Help Your UsersHelp your users help you to help themselves
Help text (almost) EVERYWHERE!
Help people at the point of entering info. Afterwards…it’s too late.
Simplify! Relentlessly remove crap!
Crap
Crap
Crap
Rename standard objects and fields
Organize Report/Dashboard Folders
GOOD BAD
Create Screencasts!• Record once, use multiple times
– While it’s still fresh in your mind!
• Jing– http://www.techsmith.com/jing.html
• Camtasia– http://www.techsmith.com/camtasia.html
• Screenr– http://www.screenr.com/
• ScreenSteps– http://www.bluemangolearning.com/screensteps/
6: Data ModelFields of Dreams
Data Model Changes
Simplify simplify simplify
• Delete records you don’t use
• Delete objects you don’t use
• Delete fields you don’t use
• Hide what you don’t use but can’t delete
Then, let Salesforce do the work for you
• Convert field types for maximum data cleaning benefit
Objects• Remove (seldom) used tabs for users
– And related lists
• Check relationships– To and Fro
– 100%? 99%? 2% filled in?
– Filter by Record Type
• Records owned by inactive users?
• Records un-modified for 1+ years
• Records not related to anything
Fields
Remove unused / under used fields (< 5% filled in)
Adding one? Take one away!
Use Date, Date/Time, Email, Phone, Percent, and URL
Field Type Changes• Textareas? Only if absolutely
necessary
• Few unique values in a text field? – Convert to picklists / checkboxes
• Multi-select Picklists– Great for creating reporting headaches. – Try checkboxes instead?
7: AppsJust apps. Nothing snarky.
Apps! Diagnostics & Utilities
• CloudFixer• Diagnostic report of common
problems (and their solutions) for Salesforce, NPSP, Common Ground
• https://cloudfixer.co
• FieldTrip• Standard and custom field usage• https://
appexchange.salesforce.com/listingDetail?listingId=a0N30000003HSXEEA4
Apps! Diagnostics & Utilities
• Easy Describe• View and extract object metadata• http://www.etherios.com/products/easydescri
be
• Grid Buddy• https://appexchange.salesforce.com/listingDetail?listingI
d=a0N30000003IkInEAK
• Data entry & editing across objects, en masse!
• Dupe Blocker• http://www.crmfusion.com/dupeblocker
Apps! ETL / Heavy Lifting• Demand Tools
– http://www.crmfusion.com/demandtools/
– Duplicate formulas and much much more…
• Apsona– http://apsona.com/pages/sfdc/index.html
• Jitterbit– http://www.jitterbit.com/salesforce/data-load
er
• Apex Data Loader / LexiLoader– Setup -> Admin Setup -> Data Management
-> Data Loader
Business Intelligence
Fancy toys to play with when your data is all clean!
• Birst – http://www.birst.com/
• Good Data – http://www.gooddata.com/
• Crystal Reports
How did that feel?
1. Strategy
2. Accountability
3. Data Quality Reports/Dashboards
4. Automation: Validation and Workflow Rules
5. Helping Our Users
6. Data Model
7. Apps for cleaning
Contact us! We can help• Ehren Foss / CloudFixer
– https://cloudfixer.co
– @ehrenfoss
• Marc Baizman / MCG Training– http://mcgtraining.com
– @mbaizman
We Love Feedback• How was the webinar?
• Which area do you think is most important for you?
• What is clean data worth to you?