Requirements: Marketing Cloud, Ad Studio, Content Builder, and Automation Studio
Bonus: CloudPages and Email Studio
Code requirements: SQL, AMPScript, SSJS, oh my!
This is an automation that I was very excited to finally piece together. Shout out to Traversy Media and Adam Spriggs for putting solutions out into the universe that I relied on heavily while piecing together the following solution.
The problem I was attempting to solve: I have a client that has multiple office locations. They are running Facebook ads that use a lead collect form to gather the names, email addresses, and zip codes of potential clients. We have dynamic content blocks built with maps and contact information for each office. We want to be able to send a welcome email to new leads with the location of their nearest office. They also do not have Salesforce, so I could not leverage the Geolocation Custom Field through Marketing Cloud Connect.
Here is the full automation:
1. Query the campaign-specific data extension into a Master Leads data extension. This is going to be the one manual process in all of this because a new lead capture in Ad Studio creates a new data extension that is then populated by new leads. Whenever you add a new campaign in Ad Studio, you are going to have to add the new data extension to the first step of the automation. You should also take the time to remove old campaigns that are no longer running.
Run this query as an update with Lead Id as the primary key. Additionally, you can keep the Master Leads data extension to a smaller size by only capturing leads from the last day and setting data retention on the destination data extension to a short period, like one or two days.
I am also going to join the new leads with a master list that I am maintaining of all leads in my database. I am doing this because I want to get as much address information as possible for an incoming lead. If you have an audience that you are targeting in Ad Studio, and that audience exists in your marketing cloud universe with a full address, and your lead capture form is only asking for a zip code, you might as well try to incorporate as much address information as you can into the address we are going to target with the API.
Sample query:
In addition to the above fields, the destination data extension should also have the following fields:
Latitude (set a default of 0)
Longitude (set a default of 0)
XAxis
YAxis
ZAxis
2. Remove # signs from addresses. While you can manually type an address that includes a # into a call to Google's Geocode API, the AMPScript below has issues concatenating the URL when there is a # in the address. The # might come up when someone enters an apartment, suite or mailbox number, so it's best to just get rid of it now. The below code is a little overkill - the odds of someone putting a # in the City, State or Zip field is very slim, but there's no reason to not get rid of any potential typos.
I replaced # signs with just a space. You could also replace it with Apt, Ste, the word "number," or whatever you want. However, the Google Geocode API does not need to be well-formatted, so inserting a space is adequate.
3. Sign up for Google's API. You will need to sign up using a google login and start a new project. The "Getting Started" section on that page will walk you through the steps to get up and rolling. Once you have an API key, you are ready for the scripting step.
You probably want to start this step by creating a CloudPage (if you have them enabled in your account). A CloudPage will allow you to test your code much quicker than where the code will ultimately live, which is in an HTML content block.
Using AMPScript, you are going to run new leads through a loop. To start, target the new leads that have not yet been through the Geocode API with a LookupRows, using Latitude and Longitude = 0. You could probably just use one of these, but it would be possible for someone to be located at 0 latitude OR 0 longitude. What about 0 for both? Here's a link so you can easily go down that rabbit hole...
The reason we are grabbing only leads with a 0 in those fields (which was populated by the default settings on the data extension), is to minimize the volume of leads that are processed on each run. This is so the script doesn't time out and also to keep down the costs of making API calls. Google's Geocode API allows $200 per month free of API calls. If you avoid hitting that number, then you don't have to pay for this automation. From my limited experience, if you are a small business generating social leads you will never get anywhere near $200 per month.
Back to the code. I am going to skip over the basics of the counter. I recommend a subscription to The AMPScript Guide for great code examples.
After processing the rows obtained from the lookup, I added an if statement that only uses an address that I captured from the Master Leads List if it matches the zip code that was provided by the lead via Facebook. I did this to give priority to the latest data point, which would be what they filled the form out with.
You then just Concat the URL for Google's Geocode API with the address and your Google API key. Then, you use the AMPScript HTTPGet to call the API. This is the easiest API call you will ever make.
After this, we have to shift to Server Side Javascript (SSJS) because AMPScript cannot parse the API result. Using SSJS, you have to grab the @getRequest variable from the AMPScript section, parse the response and insert them into variables, which will then be used by the next section of AMPScript.
I am only capturing latitude and longitude from the response, but there is a lot more information that you can grab from the response if you want properly formatted addresses, city, state, etc.
Finally, update the data extension with the information you just pulled from the response and continue the loop. The loop will process for each line in the data extension.
4. Run this code on a CloudPage by selecting Schedule / Publish. You do not need to actually publish this page, the code will run in the preview screen. You can check the data extension to see if it has been updated, or add a few lines of HTML before the NEXT @counter to print the results. Delete this bit before moving to the HTML block:
]%%
Lat: %%=v(@lat)=%%<br> Long: %%=v(@lng)=%%<br> rowsUpdated: %%=v(@rowsUpdated)=%%<br><br>
%%[
If all goes well, copy this code over to an HTML block in Content Builder and note the ID of the content block.
5. Call the HTML block from Automation Studio using SSJS. You cannot put AMPScipt into a Script activity in Automation Studio (huge annoying bummer), so you have to call an HTML content block using SSJS. Drop a Script Activity onto your Automation canvass and add the following script with your content block ID number.
6. This step and the next step could be combined into one, but for the sake of not wanting my head to explode when I look at my SQL code, I am splitting it into two processes.
We are going to calculate the x-axis, y-axis, and z-axis from the latitude and longitude and update the leads data extension. This is going to give us a three-dimensional point in space, rather than two sides of a triangle, which is what latitude and longitude provide. For further clarification, sit in on a high school geometry class.
7. This final step assumes you have already applied the above processes to a data extension that holds all of your offices/stores/etc. The list of locations you are going to compare your leads to should also have x-axis, y-axis, and z-axis.
The below SQL will calculate the distance between each lead to each office/store/etc and return only the shortest distance, thanks to the rank field.
Populate a new data extension with this information. You can now use this data extension to enter a Welcome Journey that has location-specific information.
Comments