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}")