In our last blog post, we talked about assembling an email that uses location-specific data to personalize an email. We showed how you can use content blocks to offer information for specific store locations based on the subscriber’s address. In this article we will talk about how to use geolocation in email to personalize each mailing. We won’t be discussing how to obtain address information for your subscribers. There are plenty of articles on that subject, and most of the techniques (surveys, special offers, etc.) are easy to come up with. For now, we will assume you already have this information and would like to put it to better use. For our example, we’ll be using the address locations for the recipients, to show information about the nearest store.

Finding Geolocations for Email

You may have the street addresses for your recipients, and the physical locations of your stores, but these won’t do you much good as they are. It is hard to compute anything from a street address. After all, 1945 Polaris Place, North St. Paul, MN is a lot harder to use in a formula than 45 and -93. To find the actual geolocations, you’ll need the geographic coordinates—in other words, the latitude and longitude. This may sound a bit daunting, but obtaining this information is relatively easy. There are several online sites, such as Batch Geocode and GPS Visualizer that can generate latitude and longitude numbers from street addresses automatically. Now you have the geolocation data you need for your email marketing campaign, but you still need to convert that data into nearest store location information.

More Than Maps

There are some web sites that will automatically take your store address list and your subscriber list and convert these into maps, with arrows showing the store locations. This is relatively easy to do thanks to the online map features in Google, Bing, Mapquest, and others, but there are surprisingly few sites that will give you the same information as text, and it’s text we want to enter in our subscriber database. Online maps are fine for website use, where you are free to use Javascript in your design, but email affords us no such luxury. We can create our own maps as images to insert in the email, which is exactly what we did in the previous post with the content blocks, but we’ll still need actual data that identifies which map (or content block) to display for each customer.

If you have an IT department with a programmer available, the formula for finding the nearest store locations based on each of your recipient’s location information is a simple one. For those using SQL Server or MySQL, it is easy to compute because the formulas are built into these relational database management systems. For the IT department using other methods to compute these distances, here’s a web page that lists nearly every programming language method of accomplishing this.

Using Excel

For the company with a limited number of stores and/or a minimal IT department, we’ve created an Excel spreadsheet that computes the closest store based on each customer’s geolocation information. It has an area where you enter your store latitudes and longitudes, and another where you enter the latitudes and longitudes of your recipients. These should be simple copy and paste procedures from the data you’ve already obtained using the geocoding applications. The formulas in the spreadsheet then compute the distances between the stores and the addresses, and compare them to find the closest store.

Aside from any adjustments you need to make for additional stores (our sample is set up for three), the only things you need to know are the latitudes and longitudes for your stores and your subscriber addresses. The best thing about this spreadsheet is that you don’t have to figure out the formula to use it. We’ve already entered the basic trigonometric function for finding the nearest location. You can simply plug in the values and let Excel do the rest. Computing the closest location involves several steps. We have broken these steps down in our Excel spreadsheet to make it easier to understand. Download the Excel spreadsheet here.

Once the values are computed, it’s a simple matter to import this data into a column in our original subscriber list. Now we have values we can work with. We can use this data to match the content block with the recipient, as we demonstrated in the previous post:

We can also use the data directly via mail merge or dynamic content to add individual details to the content, or the subject line (example: “Don’t miss the 40% off sale at our Stonestown store, this weekend”).

All The Data You Want

Of course, you don’t have to create a field containing the nearest store location. You can enter the geolocation data into your recipient demographics and work with it directly. With some email marketing software (such as Symphonie), you can create a custom mail merge tag that will run the mathematical logic to calculate the nearest store on-the-fly. This can be useful if you are computing many different distances, or your IT department can’t provide the resources when you need them.

Also keep in mind that it doesn’t have to be store locations. In our last post, we used similar data to compute the favorite teams for various locations. By creating separate fields for favorite teams we were able to use the data both for dynamic content purposes (example: If recipient lives in San Francisco, show the Giants image) and for merge purposes (Inserting the “favorite team” field in the email content).

Fine-tuning the Data

One thing this technique doesn’t take into account is the fact that some people don’t choose to shop at the store nearest to them. They might not like the location, or the personnel, or maybe they are using a mail drop that is located in another part of town. Gathering actual in-store data requires either a POS system that connects to membership information, or the credit card information obtained during purchases. As a rule, this isn’t that critical. Most people will understand why you showed the closest store. If someone does complain, you can always adjust their nearest store information to reflect their preference.

If you’re collecting address data as part of your email marketing effort, the best way to maximize the usefulness of that data is by adding geolocation data to the mix.