Geocode all the Records
** RATHER UNFORTUNATE UPDATE - NOV 2018**
Did some sleuthing and discovered that geocodes bug out sometimes when there is something like a room number that data.com doesnt like in the street address (even though the address is valid, the google map component in sfdc shows the address properly, and there is a valid city state and zip that could be used).
Ran the gauntlet and got a known issue logged for it…odds of it ever being fixed == low.
So This mostly makes geocodes somewhat less useful. You will need to run a report to see records that have an address but no geocode, to see which ones are being skipped, and then manually tweak them until the data.com gremlins are satisfied with your efforts.
UPDATE JAN 2018
many of the old links dont work, and there is not a lot of documentation i could find. here are a couple of links from the release notes to get started
END UPDATES
A few weeks ago I finally had a use case that could be addressed with one of the features I’ve been most excited about in spring 16 - Automatic geocoding of standard address fields.
Whats so exciting about this feature? Knowing the ‘where’ about your business / nonprofit adds a whole new dimension to your data and is invaluable for things like planning events, doing targeted outreach, and understanding your customers / donors / prospects.
So I was very excited to hear that automatic geocoding was coming.
Because that means no more reports like this
or this
When I sat down and started working on it, I could not find any information about this new feature that I was very sure existed. Finally I logged a case w support and found that it had changed to a closed beta and most of the evidence of its existence had been wiped off the internet.
Fortunately I was able to get into the closed beta. I turned on data.com geocoding clean rules (after testing in a sandbox, of course), and within a few hours I could dig into the ‘where’ of our data. Just like that. Out of the box, as it should be!
But, ootb I could only filter by location using list views. The ‘within’ filter is not available on reports. I reached out in the success community, and learned that I could create a custom formula using the DISTANCE and GEOLOCATION functions and plug in any coordinates I want, and report against that.
So I built a formula field, and now anyone can report on all contacts within X miles of the Eiffel Tower!
Well, that seemed like a good start, but we are a global org with offices in 20+ countries. I don’t really want users requesting a new formula field every time they have some new location they need to report against.
So…whats an admin to do? Well, instead of hard-coding the location values into the ‘distance’ formula, I created a couple of fields on the user object to store the info, and referenced those via the $User global variable.
The $User global variable lets you get at any fields from the ‘running user’ (the user who is logged in and running the report, viewing the record, whatever). Its pretty awesome, and has a lot of button-click use cases.
So I created 3 fields on user. Fields are pretty self explanatory.
a. Custom Location Name (text)
b. Custom Location (geocode)
c. Distance Units (picklist) - values ‘mi’ , ‘km’
Then, I created a field on organization called ‘distance from custom location’, with a formula referencing these fields via $user
DISTANCE(BillingAddress, GEOLOCATION( $User.Location_For_Distance_Reporting__Latitude__s , $User.Location_For_Distance_Reporting__Longitude__s), TEXT($User.Location_Units__c) )
Repeat for any other standard address fields you want to report against, and voila - users can choose a location, and run a report on any organizations or contacts or leads within X miles/km of any location on the planet, such as San Francisco.
With about 500,000 account records, the report takes well less than a minute to run, which is pretty impressive since its calculating the distances in the formula field on the fly.
One caveat - one big drawback with this approach is that users cant share a single report for ‘all contacts within 50 miles of the Eiffel Tower’. The ‘where’ is unique to each user. But, until we can do a filter on ‘within’ in a report itself, this allows users to report on the ‘where’ themselves, without getting admins involved.
So we have made some magic happen, but…users need to be able to set these fields themselves!
You should be able to do that via a flow, but I went for a visualforce page with a custom controller, and will explain more about why and how in part 2 of this post, coming soon someday.