Add API data to cloudpages and emails using ampscript and ssjs

At some point in your adventures in SFMC, you may come across a really good reason to include real-time data on a CloudPage, or send-time data in an email, such as current weather or stock data.

To keep things simple, I am focusing here on data that can be retrieved with a single URL curl API call. These are API calls that can be made literally with just a URL + some parameters. The following example is using stock data.

Finnhub.io provides free and paid API data that can be retrieved with a simple curl API call.

Another example of API data that can be retrieved with a simple curl API call is Google’s geocoding API. I use the Google geocoding API in this example of finding the nearest office location to a new prospect, but it can also be used to gather more information about someone’s location like their intersection or the name of their neighborhood.

Finnhub.io will first ask you to sign up for an API key, which they provide immediately in exchange for your information. For security, I recommend storing this API key in a data extension that you can call from the CloudPage or email. For example, a simple DE named APIKeys would include two fields at minimum:

Service	Key
Finnhub	xx3673x48x6xxxxxxx9x

The next step is to find the API documentation of the service you are trying to access. Most companies that have easy APIs also have really nice public API documentation. Finnbub.io is no exception.

We will start by just retrieving a simple quote. They provide an example right in the documentation:

curl "https://finnhub.io/api/v1/quote?symbol=AAPL&token=xx3673x48x6xxxxxxx9x"

The token shown here is not real. It needs to be replaced with the token that you created in the first step. We will retrieve our API token from the data extension that we created to store our various API keys, and we will turn the stock into a variable while we are at it.

The steps are to simply declare a stock variable, do a lookup to the APIKey data extension, and then Concat them with the URL provided by the API service. We then use the HTTPGet AMPScript function to make the API call.

%%[
VAR @stock, @key, @url, @getRequest

SET @stock = “APPL”
SET @key = Lookup("APIKeys","Key","Service","Finnhub")

SET @url = Concat("https://finnhub.io/api/v1/quote?symbol=" ,@stock, "&token=", @key)

SET @getRequest = HTTPGet(@url,true,0)
]%%

This is all you need to do to retrieve the following information about Apple:

• Open price of the day
• High price of the day
• Low price of the day
• Current price
• Previous close price

However, the data that is returned needs to be parsed. This is what the response actually looks like:

{
"c": 261.74,
"h": 263.31,
"l": 260.68,
"o": 261.07,
"pc": 259.45,
"t": 1582641000 
}

The response is best parsed with SSJS, so we first need to retrieve the AMPScript variable, and then we can parse the response and set a variable @current to be the “c” in the response. These variables can then be displayed on the CloudPage using inline AMPScript.

<script runat="server" language="javascript">
Platform.Load("Core","1");

var response = Variable.GetValue("@getRequest");

var json = Platform.Function.ParseJSON(response);

Variable.SetValue("@current",json.c);
</script>

Current Price= %%=FormatNumber(@current, "C")=%%

Here is another example that uses the Finnhub.io price target API and grabs more values from the response:

%%[
VAR @stock, @key, @url, @getRequest

SET @stock = “APPL”
SET @key = Lookup("APIKeys","Key","Service","Finnhub")

SET @url = Concat("https://finnhub.io/api/v1/stock/price-target?symbol=", @stock, "&token=", @key)

SET @getRequest = HTTPGet(@url,true,0)
]%%

<script runat="server" language="javascript">
Platform.Load("Core","1");

var response = Variable.GetValue("@getRequest");

var json = Platform.Function.ParseJSON(response);

Variable.SetValue("@symbol",json.symbol);
Variable.SetValue("@lastUpdated",json.lastUpdated);
Variable.SetValue("@targetHigh",json.targetHigh);
Variable.SetValue("@targetLow",json.targetLow);
Variable.SetValue("@targetMean",json.targetMean);
Variable.SetValue("@targetMedian",json.targetMedian);
</script>

Symbol = %%=v(@symbol)=%%<br>
Last Updated: %%=FormatDate(@lastUpdated, "s")=%%<br>
Target High= %%=FormatNumber(@targetHigh, "C")=%%<br>
Target Low= %%=FormatNumber(@targetLow, "C")=%%<br>
Target Mean= %%=FormatNumber(@targetMean, "C")=%%<br>
Target Median= %%=FormatNumber(@targetMedian, "C")=%%

The above is most easily used on a CloudPage, as it will call the API when the page is rendered. However, this can also be done in an email, with the following considerations:

• The API call will happen at send-time, so it might be outdated by the time the email is viewed. 
• Running AMPScript and SSJS in an email is probably going to slow down the send.
• Make sure the API can handle the volume you are going to throw at it. APIs tend to have limits. You may be able to increase limits by paying them more.
• You can also parse the response with Guide Template Language (GTL), which may speed up the send. However, I have been told that GTL is unreliable and that if you take this approach you should proceed with caution.