AdRoll Reporting API to retrieve AdRoll marketing insights.
Hi Everyone, hope you are keeping well. Recently I have posted an article on Getting started with AdRoll API, this article will be a continuation of that. Here I will help you guys in building python logic (module). This python logic will be using AdRoll reporting API to retrieve insights and output them in a Data Frame table.
Note: NextRoll is the parent company of AdRoll. You will be creating a developer account under NextRoll i.e., the API system provided by NextRoll, and can use this API to retrieve data from AdRoll. You can refer to Next Roll API documentation for more details or another use case. To generate access tokens and make the first API call, one point destination is the NextRoll (AdRoll) API get started guide.
Without wasting any time let’s dive in.
First thing first in this article we are going to develop a python module. Which will be retrieving insights from AdRoll accounts using GraphQL Reporting API. GraphQL is a query language for APIs. I know it sounds complicated, but trust me it’s easy.
Why am I focusing on GraphQL? Because it gives us the flexibility to retrieve only the needed data as per our requirement and access multiple resources at once.
To give you a high-level overview. We are going to create a JSON file to store credentials, the main python file which will work as initiator and flow controller, and lastly, will create python modules that will have functions to make API calls.
Table of Contents:
- Create a JSON File to store Cred.
- Get Adroll Advertisable ID (Account ID).
- Building a Python Module to get AdRoll marketing Insights.
- Main Python file for controlled step-wise execution.
1. Storing API credentials in JSON file:
First, create a JSON file to store all authentication-related credentials. Creating a JSON file to store credentials makes it easy to maintain, update and track credentials as needed. Save the JSON file as “adroll_cred.json”.
{
"api_key": "Replace with Client ID",
"api_secret”: "Replace with Client Secret",
"access_token”: "Replace with Access Token",
"refresh_token”: "Replace with Refresh Token",
}
In case you are lost and wondering where to get the above credentials. Refer to AdRoll (NextRoll) API setup guide. In that guide follow all the steps, when you reach step3 you will get a python code to generate an access token and refresh token. Just before that i.e. step2 will help you get api_key and api_secret.
So basically, to get all the above credentials I recommend you to take a look at the above AdRoll API Setup guide. In this article, we just need an access token but a JSON file can also be used to refresh access tokens programmatically.
2. Get AdRoll Account Id:
In order to retrieve AdRoll marking insights using API, we need to pass AdRoll advertisable id (account ID).
There are two ways to get an account Id:
- First, get the account Id from AdRoll URL
- The second way is to use python code to get account id using an API call.
2.1. AdRoll advertisable ID (Account ID) from URL:
Go to AdRoll Login. After login, you will see the dashboard. To get the advertisable ID check the URL, it will look like this (Example: https://app.adroll.com/home/grow?advertisable=UI9O26GOGAABNZXYDCBVYW).
We can easily identify the advertisable ID from the URL i.e “advertisable=UI9O26GOGAABNZXYDCBVYW”.
Note: if you have multiple advertisable then select the one by going to the bottom left corner on the AdRoll dashboard. Note down the account ID and add it to the above JSON file, which will be used by the python code to get marketing insight.
2.2.Python code to get AdRoll advertisable ID (account ID):
If you are looking for a programmatic solution to get all AdRoll advertisable. Then this step is best for you. Below is the code that uses the AdRoll advertisable API to get the list of all advertisable.
#!/usr/bin/python3
# command to run this code $ python3 ./source/get_adroll_accounts.py
import sys
import requests
import json
import pandas as pd
from datetime import datetime, timedelta
if __name__ == '__main__':
try:
timestamp = datetime.strftime(datetime.now(),'%Y-%m-%d : %H:%M')
print("DATE : ",timestamp,"\n")
print("AdRoll Account ID extraction process Started")
#Read the Credentials from the JSON file.
adroll_cred = "./source/adroll_cred.json"
adroll_cred= open(adroll_cred, 'r')
cred_json = json.load(adroll_cred)
access_token = cred_json["access_token"]
#adroll api which will give all the account/advertisables list
api_url = "https://services.adroll.com/api/v1/organization/get_advertisables"
api_call_headers = {'Authorization': 'Bearer ' + access_token}
response = requests.get(api_url, headers=api_call_headers, verify=False)
result = response.json()
account_df = pd.DataFrame(columns=["account_id","account_name","currency"])
accounts_data = result["results"]
for acc in accounts_data:
account_id = acc['eid']
account_name = acc['name']
currency = acc['currency']
tmp_dict = {}
tmp_dict.update({"account_id":account_id})
tmp_dict.update({"account_name":account_name})
tmp_dict.update({"currency":currency})
account_df = account_df.append(tmp_dict,ignore_index = True)
print("\n Account df :\n",account_df)
except:
print("\n *** ERROR *** : ",sys.exc_info())
Save the above code as get_adroll_accounts.py and Use the command python get_adroll_acounts.py to run the code.
This code will output a table with 3 columns – account_id, account_name, and currency_code. We will need account_id for our further processing. So, copy one account ID for which you want to retrieve AdRoll marketing insights.
Now we will add the account_id in our above JSON file. After updating the adroll_cred.json file this is what it looks like.
{
"api_key": "Replace with Client ID",
"api_secret”: "Replace with Client Secret",
"access_token”: "Replace with Access Token",
"refresh_token”: "Replace with Refresh Token",
“account_id”: “Replace with Account ID”
}
3.Get AdRoll Marketing Data:
In this step we will be creating a python module, this module will consist of a function to make marketing API calls with Graph QL. This API returns all the insights (cost, clicks, impressions, etc) regarding AdRoll marketing campaigns, ad groups, and ads.
You can use GraphQL Test for testing and building your QL. To get more details you can visit AdRoll GraphQL Reporting API documentation.
#!/usr/bin/python3
import sys
import io
import pandas as pd
from urllib import parse
from datetime import datetime, timedelta
import json
import requests
#Function for date validation
def date_validation(date_text):
try:
if date_text != datetime.strptime(date_text, '%Y-%m-%d').strftime('%Y-%m-%d'):
raise Exception('Input Date does not match format yyyy-mm-dd ')
else:
return datetime.strptime(date_text,'%Y-%m-%d').date()
except:
raise Exception('date_validation Failed : ',sys.exc_info()[:-1])
def adroll_graphQL(access_token,s_date,e_date,qry_type,account_id):
try:
#calling date validation funtion for start date format check
startDate = date_validation(s_date)
dt = startDate+timedelta(1)
week_number = dt.isocalendar()[1]
#calling date validation funtion for end date format check
endDate = date_validation(e_date)
endDate = endDate + timedelta(1) #add a day to enddate - format for adroll api call to work
api_url = "https://services.adroll.com/reporting/api/v1/query?"
#Graph QL
q_obj = "{advertisable { byEID(advertisable: \""+str(account_id)+"\") { eid name campaigns { eid name channel adgroups { eid name ads { eid name utmCampaign utmSource utmMedium destinationURL metrics(start: \""+str(startDate)+"\", end: \""+str(endDate)+"\", currency: \"USD\") { byDate { impressions clicks cost date} } } } } } } }"
api_call_headers = {'Authorization': 'Bearer ' + access_token}
payload ={"query":q_obj}
response = requests.post(api_url, headers=api_call_headers, data=payload)
if response.status_code != 200:
print("*adroll_graphQL : something went wrong :",response.json)
response.raise_for_status()
else:
result = response.json()
graphQL_data_df = pd.DataFrame()
if result["data"]["advertisable"]:
tmp_dict = {}
ql_detail = result["data"]["advertisable"]["byEID"]
acct_id = ql_detail["eid"]
#read campaign level data
if ql_detail["campaigns"]:
campaigns_detail = result["data"]["advertisable"]["byEID"]["campaigns"]
for cmp_detail in campaigns_detail:
campaign_name = cmp_detail["name"]
campaign_id = cmp_detail["eid"]
#read adgroup level data
adgroups_detail = cmp_detail["adgroups"]
for adgroup in adgroups_detail:
adgroup_name = adgroup["name"]
adgroup_id = adgroup["eid"]
#read ads level data
ads_detail = adgroup["ads"]
for ad in ads_detail:
ad_name = ad["name"]
ad_id = ad["eid"]
utm_campaign = ad["utmCampaign"]
#read by date, for daily bases read first element
metrics = ad["metrics"]["byDate"][0]
ad_impressions = metrics["impressions"]
ad_clicks = metrics["clicks"]
ad_cost = metrics["cost"]
ad_date = metrics["date"]
destinaton_url = ad["destinationURL"]
o = parse.urlparse(destinaton_url)
url = o._replace(query=None).geturl()
tmp_dict["campaign_name"] = campaign_name
tmp_dict["campaign_id"] = campaign_id
tmp_dict["adgroup_name"] = adgroup_name
tmp_dict["adgroup_id"] = adgroup_id
tmp_dict["ad_name"] = ad_name
tmp_dict["ad_id"] = ad_id
tmp_dict["impressions"] = ad_impressions
tmp_dict["clicks"] = ad_clicks
tmp_dict["cost"] = ad_cost
tmp_dict["utm_campaign"] = utm_campaign
tmp_dict["link"] = url
if ad_impressions is not None and ad_cost is not None and ad_clicks is not None:
graphQL_data_df = graphQL_data_df.append(tmp_dict,ignore_index = True)
graphQL_data_df["account_id"] = account_id
graphQL_data_df["utm_source"] = "adroll"
graphQL_data_df["week"] = week_number
#graphQL_data_df["campaign_type"] = None
graphQL_data_df["start_date"] = startDate
graphQL_data_df["end_date"] = graphQL_data_df["start_date"]
return graphQL_data_df
except:
print("\n* adroll_graphQL Failed :",sys.exc_info())
raise
Save the above python code as get_adroll_data.py.
4. Main File – the center point for all execution controls:
Let’s create a main file, which will work as an execution controller i.e will call functions as needed.
#!/usr/local/bin/python3
# command to run this code $ python ./source/adroll_main.py -s 2021-04-26 -e 2021-04-26 -q query_type(week/day)
import getopt
import pandas as pd
import sys
import os.path
import json
from datetime import datetime, timedelta
from get_adroll_data import *
def readfile(argv):
global start_date
global end_date
global qry_type
try:
opts, args = getopt.getopt(argv,"s:e:q:")
except getopt.GetoptError:
sys.exc_info()
for opt, arg in opts:
if opt == '-s':
start_date = arg
elif opt == '-e':
end_date = arg
elif opt == '-q':
qry_type = arg
else:
print("Invalid Option in command line")
if __name__ == '__main__':
try:
timestamp = datetime.strftime(datetime.now(),'%Y-%m-%d : %H:%M')
print("DATE : ",timestamp,"\n")
print("AdRoll data extraction process Started")
readfile(sys.argv[1:])
#Read the Credentials from the JSON file.
adroll_cred = "./source/adroll_cred.json"
adroll_cred= open(adroll_cred, 'r')
cred_json = json.load(adroll_cred)
client_id = cred_json["api_key"]
client_secret = cred_json["api_secret"]
access_token = cred_json["access_token"]
#refresh_token = cred_json["refresh_token"]
account_id = cred_json["account_id"]
if account_id and access_token:
#call get adroll insight.
adroll_data_df = adroll_graphQL(access_token,start_date,end_date,qry_type,account_id)
print("\n AdRoll Data : ",adroll_data_df)
except:
print("\n***ADROLL_MAIN : data extraction processing Failed !!!!: ", sys.exc_info())
NOTE: We are importing the module created in step3 in the above main by using the command from get_adroll_data import *. Save the above file as adroll_main.py and use the below command to run the project.
Python ./source/adroll_main.py -s 2021-04-26 -e 2021-04-26 -q query_type(week/day)
The output of the above project will be a DataFrame table with each row representing ads-level marketing insights.
Congratulations! you have successfully created python logic (module) to retrieve the AdRoll marketing insights using the AdRoll API. Hope I was able to make the entire process of building python logic for AdRoll API calls and data retrieval simpler.
Check out other API Set-Up Guide.
If you have any questions or comments feel free to reach me at.