Use this link to share with your colleagues:
How to Create "Distance from" Reports: https://help.patronmanager.com/a/2093658
Wouldn't it be great if you could run a report to show all Accounts or all Contacts within a certain distance from a location for a mailing or a targeted offer? Or maybe you want to run a report to see how far patrons travel to attend your events?
With a little setup and formulas, you can do just that! In this article, we'll show you:
- How to turn on geocodes for addresses
- How to create a report type to use geocodes
- How to create formulas in reports to check distances
Let's get started!
1. Turn on geocodes
You'll only need to do this step once! After this has been done in your account, you won't have to do this again.
1.1. Go to Setup
1.2. Find and select Data Integration Rules
1.3. Click into the rule(s) you wish to enable
You can select:
- Geocodes for Account Billing Address: If you want to use the Account's Billing Address as the address to measure
- Geocodes for Account Shipping Address: If you want to use the Account's Shipping Address as the address to measure
- Geocodes for Contact Mailing Address: If you want to look at the Contact's address to measure
Ignore "Geocodes for Lead Address" because Leads aren't used in PatronManager!
1.4. Click Activate
1.5. If prompted, click OK
2. Create a Report Type for geocodes
You'll only need to do this step once! After this has been done in your account, you won't have to do this again.
Once you've turned on geocodes, you'll need to make them available on reports via a Report Type.
Here we'll show you how to create a new report type that can use geocodes, but if you have a custom report type based on Accounts or Contacts, you can add the different latitude and longitude fields for addresses to your custom report types!
The fields you'll want to add (based on what you selected earlier) are:
- Billing Latitude (if you enabled "Geocodes for Account Billing Address")
- Shipping Latitude (if you enabled "Geocodes for Account Shipping Address")
- Mailing Latitude (if you enabled "Geocodes for Contact Mailing Address")
2.1. Go to Setup
2.2. Find and select Report Types
2.3. Click New Custom Report Type
2.4. Set up the Report Type
- Primary Object: Select Accounts
- Display Label: Give the report type a recognizable name, like "Accounts & Contacts with Geocodes"
- API Name: This automatically fills
- Description: Enter a description to help others know what this report is for
- Store in Category: Other Reports
- Status: Deployed
- Next
2.5. Click to relate another object
2.6. Find and select Contacts
2.7. Save
3. Create distance formulas in reports
Now that geocodes are available in your Report Type, you can use them in reports!
We'll use a row-level formula for this!
3.1. From the Reports tab, click New Report
3.2. Find and select the Report Type you created
3.3. Start Report
3.5. In the Outline panel, adjust the columns and groupings to what you want to see
3.6. Still in the Outline pane, expand the Columns options
3.7. Add Row-Level Formula
3.8. Create the Row-Level Formula Column
- Column Name: Give the column a descriptive name, like "Distance from Billing Address to Venue"
- Description: Further describe the column, like "Miles from the Account Billing Address to the Monarch Theatre"
- Formula Output Type: Number
- Decimal Points: Set as specific as you wish; defaults to 2
3.9. Create the Formula
To make the Formula, we'll need to gather the latitude and longitude of the address you wish to use as the constant. This is most likely the location of an event, your venue, etc.
3.9.1. Go to Google Maps
Here's a link: https://www.google.com/maps
3.9.2. Enter the address you wish to use
3.9.3. In your browser's URL bar, grab the latitude and longitude
This appears in the URL after the at sign (@) and is separated by a comma (,).
In the screenshot above, the latitude and longitude is: 40.7611217,-73.9707703
The exact formula you'll use depends on the address field for the Account or Contact you wish to use, and we have copy-and-pasteable formulas below:
- To use the Account Billing Address:
DISTANCE(GEOLOCATION(Account.BillingLatitude,Account.BillingLongitude), GEOLOCATION(INPUT LOCATION LATITUDE AND LONGITUDE), 'mi') - To use the Account Shipping Address:
DISTANCE(GEOLOCATION(Account.ShippingLatitude,Account.ShippingLongitude), GEOLOCATION(INPUT LOCATION LATITUDE AND LONGITUDE), 'mi') - To use the Contact Mailing Address:
DISTANCE(GEOLOCATION(Contact.MailingLatitude,Contact.MailingLongitude), GEOLOCATION(INPUT LOCATION LATITUDE AND LONGITUDE), 'mi')
In all of the formulas above, replace "INPUT LOCATION LATITUDE AND LONGITUDE" with the location of your event or venue's latitude and longitude.
3.10. Apply
A new column will appear in your report showing the distance in miles from the address in PatronManager and the address you selected
Don't forget to Save & Run the report once you're done!