PRODUCT

Amazon Ad Server will be sunset in Q4 2024, please visit this page (AAS offboarding information) for offboarding support resources and sunset FAQs. Details shared on that page represent the most up to date information in the Help Center, if you find disparate information in other resources please default to the information in the AAS offboarding information page accordingly.

Follow

SQL Samples

Impressions aggregated on the hour
Simple example of creating a column based on the date to add an additional bucket to group on for impressions.

 

SELECT
advertiser,
EXTRACT(HOUR FROM event_dt) AS event_hour,
SUM(impressions) AS impressions
FROM
adserver_traffic
GROUP BY
advertiser,event_hour

 

API Usage example code

 

#Importing the required libraries
import requests
import json
import time

#Defining the URL resources
sasLoginUrl='https://adapi.sizmek.com/sas/login/login'
avcUrlRoot='https://adapi.sizmek.com/avc'

#Setting the API key, username and passwords
apiKey=''
username=''
password=''

# ................
# Authentication
# ................

#Setting the header information
headers = {
"api-key": apiKey,
"Content-Type": "application/json"
}

#Setting the body information
body = {"username":username, "password": password}

#Making the Auth post request
response = requests.post(sasLoginUrl, headers=headers, data=json.dumps(body))
result = response.json()

#getting the Session ID
sessionId = result["result"]["sessionId"]

# //....................................
# //Vertification API workflow execution
# //.....................................

#Preparing the timeWindow parameter (data availability is last 30 days) -
#The full list of allowed time zones can be found at
#https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
timeWindow = {
"start": "2023-12-10T00:00:00",
"timeZone": "CET",
"end": "2023-12-20T23:59:59",
"type": "EXPLICIT"
}

#Preparing the SQL Query
sqlQuery= '''SELECT
event_date,
event_hour,
advertiser_id,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM
adserver_traffic
GROUP BY
event_date,event_hour,advertiser_id'''

#advertiser Id of interest
advertiserId=''

#Preparing the body - WorkflowId is a user defined unique identifier for your references
body={
"timeWindow": timeWindow,
"workflow": {
"sqlQuery":sqlQuery,
"workflowId":"12345"
}
}

#Preparing the workflow header
headers = {
"api-key": apiKey,
"Content-Type": "application/json",
"Authorization": sessionId,
"amazon-advertising-advertiser-id": advertiserId
}

#Making the request
response = requests.post(f"{avcUrlRoot}/workflowexecutions", headers=headers, data=json.dumps(body))
print(response)
result = response.json()

#getting the workflow execution ID, so that status can be queried before getting the download URLs.
workflowExecutionId = result['workflowExecution']['workflowExecutionId']

# //.................................................
# //Checking the status
# //..................................................

#preparing the status header
headers = {
"api-key": apiKey,
"Content-Type": "application/json",
"Authorization": sessionId,
"amazon-advertising-advertiser-id": advertiserId
}
workflowExecutionStatus=''
sleepSeconds=10

#Looping with 10 sec timer, until succeeded
while (not workflowExecutionStatus=='SUCCEEDED'):
time.sleep(sleepSeconds)
response = requests.get(f"{avcUrlRoot}/workflowexecutions/{workflowExecutionId}", headers=headers)
print(response)
result = response.json()
# print(result)
workflowExecutionStatus = result['workflowExecution']['status']
print(f"Status: {workflowExecutionStatus}")

# //.................................................
# //Getting the download URL
# //..................................................

#Preparing the Header for call
headers = {
"api-key": apiKey,
"Content-Type": "application/json",
"Authorization": sessionId,
"amazon-advertising-advertiser-id": advertiserId
}

#Getting the URL
response = requests.get(f"{avcUrlRoot}/workflowexecutions/{workflowExecutionId}/downloadUrls", headers=headers)
result = response.json()
url = result['downloadUrls'][0]
print(f"Signed URL: {url}")
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments