This article shows how to use the Analytics Edge ‘Web Request’ function to make an API query, using the DataForSEO API as an example (a free trial of the API is avaialble). The same approach can be used for other API’s that use simple authentication schemes.
Overview
The Web Request function provides a number of fields that allow you to ‘build’ a web query, starting with the method (typically Get or Post), the actual URL to be called, any HTTP headers, as well as the content type and a text field for content to be posted. Finally, the function allows you to determine how to parse the results — typical is JSON formatted, where you can also specify the top node to be parsed (more on this later).
Preparation
The first step is figuring out what you want to do. In this example, lets try to get live Google organic SERP results. From the DataForSEO documentation, an example of the API call using ‘curl’ is provided which shows all of the elements needed.
curl --location --request POST "https://api.dataforseo.com/v3/serp/google/organic/live/advanced" \ --header "Authorization: Basic ${cred}" \ --header "Content-Type: application/json" \ --data-raw "[ { "language_code": "en", "location_code": 2840, "keyword": "albert einstein", "calculate_rectangles": true } ]"
The authorization is a special case. Most APIs require some form of authentication, and DataForSEO documentation states that the Authorization header should be of the form
Authorization: Basic login:password
They also state that you should use your API credentials encoded in Base64, but “Instead of ‘login’ and ‘password’ use your credentials from https://app.dataforseo.com/api-dashboard” — the API Login and API Password specified on the Dashboard, NOT the email and password you used to log into the dashboard. For example, mike_sullivan@analyticsedge.com:cecfccb98xxxxxxx converted to Base64 (online converter) becomes bWlrZV9zdWxsaXZhbkBhbmFseXRpY3NlZGdlLmNvbTpjZxxxxxxxx. The full header line then becomes:
Authorization: Basic bWlrZV9zdWxsaXZhbkBhbmFseXRpY3NlZGdlLmNvbTpjZxxxxxxxx
Creating The Web Request
The Method for this request is a Post as per the documentation.
The URL is ‘https://api.dataforseo.com/v3/serp/google/organic/live/advanced’.
In the Headers field, enter the Authorization string as discussed above.
With Post functions, there is usually a content field, and you need to specify the type of content. For this call, the Content Type is ‘application/json’
For the Content, we can adapt what they used in the example or create our own following the documentation. Note that you can split the content across multiple lines if desired or just string it all together into a single line.
That’s all there is to it — submit the Web Request function and get the result!
Working With JSON Results
A common problem with some API queries, like this one, is that the results are provided as a hierarchy, which does not lend it to populating a grid format like an Excel or Google Sheets spreadsheet. Analytics Edge parses the data and assigns a column per element by default, but this is not very useful for analysis or reporting.
Transpose for easier readability
To make it easier to read, we can use an option in the Write To Worksheet function to transpose the rows and columns. When Analytics Edge detects multiple elements at the same level in the hierarchy, it names with the columns with numbers to differentiate them (-1, -2, -3, etc), such as multiple ‘items’ in this example.
Specifiy a top node of interest
In results like this, there are usually a number of ‘header’ type fields we are not really interested in. What is of interest is the ‘items’ groups, where there are multiple entries (-1, -2, etc). It would be best if we could see each entry on a row by iself.
The Web Request wizard lets us filter out just those entries from the hierarchy during parsing.
Flatten hierarchical results
While this results in a much better data extract, there is still an element of hierarchical data — for example, there can be multiple ‘links’ per item as indicated by the links-1, links-2 columns (the data in the image below is still transposed).
To flatten the results, we can use the Arrange function’s Multi-Row Records transformation — it creates a new row for each of the sub-elements (links in this case) and repeats the values in the other columns…flattening the hierarchy into a table.
The results (below, no longer transposed) show the multiple links (rows) for the first ‘item’ while maintaining a tabular result we can use in an analysis or report.
Summary
The Web Request function in Analytics Edge can be used to query or interact with any API that uses a simple authentication method. It can be used with other functions to parse and transform the results, extracting data of interest and formatting for analysis and reporting in your spreadsheet.