In [ ]:

    

# Copyright 2020 Google LLC

# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at

#     https://www.apache.org/licenses/LICENSE-2.0

# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.


    

In [ ]:

    

!pip install google-api-python-client
!pip install google-cloud-vision

import io
import datetime
import json
import csv
import pprint
import requests
import pandas as pd

from google.colab import files
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient import discovery
from googleapiclient import http
from google.api_core import retry
from google.cloud import vision
from google.cloud import storage

print("Successfully imported Python libraries!")


    

In [ ]:

    

API_SCOPES = ['https://www.googleapis.com/auth/doubleclickbidmanager',
            'https://www.googleapis.com/auth/display-video',
            'https://www.googleapis.com/auth/devstorage.read_only',
            'https://www.googleapis.com/auth/cloud-vision']

# Authenticate using user credentials stored in client_secrets.json
client_secrets_file = files.upload()
client_secrets_json = json.loads(next(iter(client_secrets_file.values())))

flow = InstalledAppFlow.from_client_config(client_secrets_json, API_SCOPES)
credentials = flow.run_console()
print("Success!")

# Build DBM Read API service object
dbm_service = discovery.build('doubleclickbidmanager', 'v1.1', credentials=credentials)
print("DBM API service object created")

# Build Google Cloud Storage Read API service object
gcs_service = discovery.build('storage', 'v1', credentials=credentials)
print("GCS service object created")

# Create Display Video API service object
display_video_service = discovery.build('displayvideo', 'v1', credentials=credentials)
print("Display Video API service object created")


    

In [ ]:

    

PARTNER_ID = "234340" #@param {type:"string"}
ADVERTISER_ID = "2436036" #@param {type:"string"}
CAMPAIGN_ID = "4258803" #@param {type:"string"}

# For use with legacy DBM API
SDF_VERSION = "5.1" #@param {type:"string"}

# For use with DV360 API
SDF_VERSION_DV360 = "SDF_VERSION_5_1" #@param {type:"string"}

print("DV360 settings saved!")


    

In [ ]:

    

YOUR_NAME = "Matt" #@param {type:"string"}

# Set dates for new campaign
month = datetime.datetime.today().strftime('%m')
day = datetime.datetime.today().strftime('%d')
year = datetime.datetime.today().strftime('%Y')
month_plus30 = (datetime.datetime.today() + datetime.timedelta(days=30)).strftime('%m')
day_plus30 = (datetime.datetime.today() + datetime.timedelta(days=30)).strftime('%d')
year_plus30 = (datetime.datetime.today() + datetime.timedelta(days=30)).strftime('%Y')

def create_campaign(YOUR_NAME):
  """ Creates a new DV360 Campaign object."""
  
  campaign_name = f"{year}-{month}-{day} | {YOUR_NAME}"
  campaign_obj = {
    'displayName': campaign_name,
    'entityStatus': 'ENTITY_STATUS_ACTIVE',
    'campaignGoal': {
        'campaignGoalType' : 'CAMPAIGN_GOAL_TYPE_ONLINE_ACTION',
        'performanceGoal': {
            'performanceGoalType': 'PERFORMANCE_GOAL_TYPE_CPC',
            'performanceGoalAmountMicros': 1000000
        }
    },
    'campaignFlight': {
        'plannedSpendAmountMicros': 1000000,
        'plannedDates': {
            'startDate': {
                'year': year,
                'month': month,
                'day': day
            },
            'endDate': {
                'year': year_plus30,
                'month': month_plus30,
                'day': day_plus30
            }
        }
    },
    'frequencyCap': {
        'maxImpressions': 10,
        'timeUnit': 'TIME_UNIT_DAYS',
        'timeUnitCount': 1
    }
  }

  # Create the campaign.
  campaign = display_video_service.advertisers().campaigns().create(
    advertiserId=ADVERTISER_ID,
    body=campaign_obj
  ).execute()
  
  return campaign

campaign = create_campaign(YOUR_NAME)

# Display the new campaign.
CAMPAIGN_ID = campaign['campaignId']
print(f"\nCampaign '{campaign['name']}' was created."
      f"\nCampaign id: '{campaign['campaignId']}'"
      f"\nCampaign name: '{campaign['displayName']}'"
      f"\nCampaign status: '{campaign['entityStatus']}'")


    

In [ ]:

    

# Configure the sdf.download request
request_body={
    "fileTypes": ["LINE_ITEM"],
    "filterType": "CAMPAIGN_ID",
    "filterIds" : [CAMPAIGN_ID],
    "version": SDF_VERSION
}

# Make the request to download all SDF LineItems for your new campaign
request = dbm_service.sdf().download(body=request_body)
response = request.execute()

# Load SDF response to Pandas DataFrame
sdf_df = pd.read_csv(io.StringIO(response['lineItems']))

# Show sample (5 rows) of DataFrame
sdf_df.head()


    

In [ ]:

    

targeting_template = {
  'Channel Targeting - Include': '2580510;',
  'Channel Targeting - Exclude': '2580509;',
  'Inventory Source Targeting - Include': '1;',
  'Inventory Source Targeting - Exclude': '6; 8; 9; 10; 2; 11; 12; 13; 16; 20; '
    +'23; 27; 29; 30; 31; 34; 35; 36; 38; 43; 46; 50; 51; 56; 60; 63; 67; 74;',
  'Digital Content Labels - Exclude': 'G; PG; T;',
  'Brand Safety Sensitivity Setting': 'Use custom',
  'Brand Safety Custom Settings': "Adult; Alcohol; Derogatory; "
      + "Downloads & Sharing; Drugs; Gambling; Politics; Profanity; Religion; "
      + "Sensitive social issues; Suggestive; Tobacco; Tragedy; "
      + "Transportation Accidents; Violence; Weapons;"
  }


    

In [ ]:

    

# Overwrite targeting columns using 'targeting_template'
sdf_df['Channel Targeting - Include'] = targeting_template['Channel Targeting - Include']
sdf_df['Channel Targeting - Exclude'] = targeting_template['Channel Targeting - Exclude']
sdf_df['Inventory Source Targeting - Include'] = targeting_template['Inventory Source Targeting - Include']
sdf_df['Inventory Source Targeting - Exclude'] = targeting_template['Inventory Source Targeting - Exclude']
sdf_df['Digital Content Labels - Exclude'] = targeting_template['Digital Content Labels - Exclude']
sdf_df['Brand Safety Sensitivity Setting'] = targeting_template['Brand Safety Sensitivity Setting']
sdf_df['Brand Safety Custom Settings'] = targeting_template['Brand Safety Custom Settings']

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update1_controls.csv', index=False)

# Show sample (5 rows) of DataFrame
sdf_df.head()


    

In [ ]:

    

# Download modified csv to local storage
files.download('sdf_update1_controls.csv')

print("Success, check your downloads folder for a file called 'sdf_update1_controls.csv'")


    

In [ ]:

    

yesterday = datetime.date.today() - datetime.timedelta(2) # Actually today-2 to avoid issues with collection

# Download public ERF for geolocation info
request = gcs_service.objects().get_media(
    bucket="gdbm-public",
    object='entity/' + yesterday.strftime('%Y%m%d') + '.0.GeoLocation.json')

response = request.execute()
geolocations = json.loads(response)

print("GeoLocation.json successfully downloaded \n")
print("Here's a random sample of 5 entries:\n")
pprint.pprint(geolocations[0:5])


    

In [ ]:

    

# Provide a list of store locations
store_locations = ['United Kingdom', 'France', 'Spain', 'Germany', 'Portugal']

# Create a new dictionary to save the country code and ID later on
geo_targeting_ids = {}

# Note: GeoLocation.json is over 800,000 lines
for location in geolocations:
  if location['canonical_name'] in store_locations:
    geo_targeting_ids[location['country_code']] = location['id']
    print(location)
    
print(geo_targeting_ids)


    

In [ ]:

    

# Configure the sdf.download request
request_body={
    "fileTypes": ["LINE_ITEM"],
    "filterType": "CAMPAIGN_ID",
    "filterIds" : [CAMPAIGN_ID],
    "version": SDF_VERSION
}

# Make the request to download all SDF LineItems for your new campaign
request = dbm_service.sdf().download(body=request_body)
response = request.execute()

# Load SDF response to Pandas DataFrame
sdf_df = pd.read_csv(io.StringIO(response['lineItems']))

# Show sample (5 rows) of DataFrame
sdf_df.head()


    

In [ ]:

    

for country in geo_targeting_ids:
  target_country = geo_targeting_ids[country]
  sdf_df.loc[sdf_df.Name.str.contains(country), 'Geography Targeting - Include'] = f'{target_country};'

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update2_geo.csv', index=False)

# Display updated DataFrame
sdf_df.head()


    

In [ ]:

    

# Download modified csv to local storage
files.download('sdf_update2_geo.csv')

print("Success, look for the file called 'sdf_update2_geo.csv' in your downloads folder")


    

In [ ]:

    

# Upload product feed using Colab's upload utility
product_feed_csv = files.upload()

contents = next(iter(product_feed_csv.values())).decode('utf-8')
products = csv.DictReader(io.StringIO(contents))

image_url_list = {}

# Iterate through each row and update dict() with sku:link
for row in products:
  image_url_list[row['sku']] = row['image_link']

pprint.pprint(image_url_list)


    

In [ ]:

    

def vision_analysis(image_url):
  """Process images using the Cloud Vision API"""
  
  # Assign image URL
  image = vision.types.Image()
  image.source.image_uri = image_url

  # Instantiates a Vision client
  client = vision.ImageAnnotatorClient(credentials=credentials)

  # Performs label detection on the image file
  response = client.label_detection(image=image)
  labels = response.label_annotations
  
  targeting_keywords = []

  for label in labels:
    targeting_keywords.append(label.description)
  
  return(targeting_keywords)


    

In [ ]:

    

imageslookup = {}

for sku, url in image_url_list.items():
    print(f"Vision analysis completed for: {url}")
    imageslookup[sku] = vision_analysis(url)

print("\nCreating lookup table:")
pprint.pprint(imageslookup, indent=4)


    

In [ ]:

    

# Configure the sdf.download request
request_body={
    "fileTypes": ["LINE_ITEM"],
    "filterType": "CAMPAIGN_ID",
    "filterIds" : [CAMPAIGN_ID],
    "version": SDF_VERSION
}

request = dbm_service.sdf().download(body=request_body)
response = request.execute()

# Load SDF response to Pandas DataFrame
sdf_df = pd.read_csv(io.StringIO(response['lineItems']))

for product in imageslookup:
  sdf_df.loc[sdf_df.Name.str.contains(product), \
      'Keyword Targeting - Include'] = ";".join(imageslookup[product]).lower()

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update3_keywords.csv', index=False)

# Show sample (5 rows) of DataFrame
sdf_df.head()


    

In [ ]:

    

# Download modified csv to local storage
files.download('sdf_update3_keywords.csv')

print("Success, look for the file called 'sdf_update3_keywords.csv' in your downloads folder")


    

In [ ]:

    

# Define DV360 report definition (i.e. metrics and filters)
report_definition = {
  'params': {
    'type': 'TYPE_GENERAL',
    'metrics': [
      'METRIC_IMPRESSIONS',
      'METRIC_CLICKS',
      'METRIC_CTR',
      'METRIC_REVENUE_ADVERTISER'
    ],
    'groupBys': [
      'FILTER_ADVERTISER',
      'FILTER_INSERTION_ORDER',
      'FILTER_LINE_ITEM',
      'FILTER_ADVERTISER_CURRENCY'
    ],
    'filters': [
      {
        'type': 'FILTER_ADVERTISER', 
        'value': ADVERTISER_ID
      }
    ],
  },
  "metadata": {
    "title": "DV360 Automation API-generated report",
    "dataRange": 'LAST_90_DAYS',
    "format": "csv"
  },
  "schedule": {
    'frequency': "ONE_TIME"
  }
}

# Create new query using report definition
operation = dbm_service.queries().createquery(body=report_definition).execute()
pprint.pprint(operation)

# Runs the given Queries.getquery request, retrying with an exponential
# backoff. Returns completed operation. Will raise an exception if the
# operation takes more than five hours to complete.
@retry.Retry(predicate=retry.if_exception_type(Exception), initial=5, maximum=60, deadline=18000)
def check_get_query_completion(getquery_request):
  response = getquery_request.execute()
  pprint.pprint(response)
  if response['metadata']['running']:
    raise Exception("The operation has not completed.")
  return response

getquery_request = dbm_service.queries().getquery(queryId=operation['queryId'])
response = check_get_query_completion(getquery_request)


    

In [ ]:

    

report_url = response['metadata']['googleCloudStoragePathForLatestReport']

# Use skipfooter to remove report footer from data
report_df = pd.read_csv(report_url, skipfooter=16, engine='python')
report_df.head(10)


    

In [ ]:

    

# Define our 'KPIs'
ctr_target = 0.15
imp_threshold = 10000

# Convert IDs to remove decimal point, then string
report_df['Line Item ID'] = report_df['Line Item ID'].apply(int)
poor_performers = report_df.query('Impressions > @imp_threshold & (Clicks / Impressions)*100 < @ctr_target')

# Convert results to Python list
poor_performers = list(poor_performers['Line Item ID'])

print(f"There are {len(poor_performers)} LineItems with a CTR"
      f" < {ctr_target}% and over {imp_threshold} impressions:"
      f"\n{poor_performers}")


    

In [ ]:

    

# Configure the sdf.download request
request_body={
    "fileTypes": ["LINE_ITEM"],
    "filterType": "CAMPAIGN_ID",
    "filterIds" : ['1914007'],
    "version": SDF_VERSION
}

request = dbm_service.sdf().download(body=request_body)
response = request.execute()

# Load SDF response to Pandas DataFrame
sdf_df = pd.read_csv(io.StringIO(response['lineItems']))

for li in poor_performers:
  geo = sdf_df.loc[sdf_df['Line Item Id'] == li, "Geography Targeting - Include"].iloc[0]
  sdf_df.loc[sdf_df['Line Item Id'] == li, "Bid Multipliers"] = f"(geo; {geo} 0.5;);"

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update4_bidmultipliers.csv', index=False)

# Display updated DataFrame
sdf_df.head()


    

In [ ]:

    

files.download('sdf_update4_bidmultipliers.csv')

print('Success, your new SDF file has been downloaded')


    

In [ ]:

    

#TODO


    

In [ ]:

    

# Format today-2 in required date format
yesterday = (datetime.date.today() - datetime.timedelta(2)).strftime('%Y%m%d')

# Download ERF for Language.json from public GCS bucket
request = gcs_service.objects().get_media(
    bucket="gdbm-public",
    object='entity/' + yesterday + '.0.Language.json')

response = request.execute()
languages = json.loads(response)

language_targets = ['en', 'fr']
lang_targeting_ids = {}

# Search language.json for language targets 'en' and 'fr'
for lang in languages:
  if lang['code'] in language_targets:
    lang_targeting_ids[lang['code']] = lang['id']
    print(lang)
    
print(lang_targeting_ids)

# Define targeting template
targeting_template = {
 'Affinity & In Market Targeting - Include': '4569529;4586809;4497529;',
 }

# Configure the sdf.download request
request_body={
    "fileTypes": ["LINE_ITEM"],
    "filterType": "CAMPAIGN_ID",
    "filterIds" : [CAMPAIGN_ID],
    "version": SDF_VERSION
}

request = dbm_service.sdf().download(body=request_body)
response = request.execute()

# Load SDF response to Pandas DataFrame
sdf_df = pd.read_csv(io.StringIO(response['lineItems']))

# Update DataFrame with Language and Audience targeting
sdf_df.loc[sdf_df.Name.str.contains("GB"), "Language Targeting - Include"] = f"{lang_targeting_ids['en']};"
sdf_df.loc[sdf_df.Name.str.contains("FR"), "Language Targeting - Include"] = f"{lang_targeting_ids['fr']};"
sdf_df['Affinity & In Market Targeting - Include'] = targeting_template['Affinity & In Market Targeting - Include']

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update5_challenge.csv', index=False)

# Display updated DataFrame
sdf_df.head()


    

In [ ]:

    

# Download file to disk using Colab syntax
files.download('sdf_update5_challenge.csv')

print("Success, check your downloads folder for a file called 'sdf_update5_challenge.csv'")


    

In [ ]:

    

def download_sdf(request_body):
  """Download sdf file, extract .csv files from .zip, load 
  'SDF-LineItems.csv' to Pandas DataFrame object. """

  # Create the sdfdownloadtask
  operation = display_video_service.sdfdownloadtasks().create(
      body=sdf_body).execute();
      
  print("Operation %s was created." % operation["name"])

  # Configure the operations.get request
  getRequest = display_video_service.sdfdownloadtasks().operations().get(
      name=operation["name"]
      )

  # Runs the given operations.get request, retrying with an exponential
  # backoff. Returns completed operation. Will raise an exception if the
  # operation takes more than five hours to complete.
  @retry.Retry(predicate=retry.if_exception_type(Exception), 
               initial=5, maximum=60, deadline=18000)
  def checkSdfdownloadtaskCompletion(getRequest):
    operation = getRequest.execute()
    if "done" not in operation:
      raise Exception("The operation has not completed.")
    return operation

  # Get current status of operation with exponential backoff retry logic
  operation = checkSdfdownloadtaskCompletion(getRequest)

  # Check if the operation finished with an error and return
  if "error" in operation:
    raise Exception("The operation finished in error with code %s: %s" % (
                    operation["error"]["code"],
                    operation["error"]["message"]))

  print("The operation completed successfully. Resource %s was created."
        % operation["response"]["resourceName"])

  # Extract download file resource name to use in download request
  resource_name = operation["response"]["resourceName"]

  # Configure the Media.download request
  downloadRequest = display_video_service.media().download_media(
    resourceName=resource_name)

  output_file = f"{resource_name.replace('/','-')}.zip"

  # Create output stream for downloaded file
  outStream = io.FileIO(output_file, mode='wb')

  # Make downloader object
  downloader = http.MediaIoBaseDownload(outStream, downloadRequest)

  # Download media file in chunks until finished
  download_finished = False
  while download_finished is False:
    _, download_finished = downloader.next_chunk()

  print(f"File downloaded to {output_file}")

  # Load output into a Pandas dataframe
  df = pd.read_csv(output_file, compression='zip')
  return df

print("Download SDF function created")


    

In [ ]:

    

targeting_template = {
  'Channel Targeting - Include': '2580510;',
  'Channel Targeting - Exclude': '2580509;',
  'Inventory Source Targeting - Include': '1;',
  'Inventory Source Targeting - Exclude': '6; 8; 9; 10; 2; 11; 12; 13; 16; 20; '
    +'23; 27; 29; 30; 31; 34; 35; 36; 38; 43; 46; 50; 51; 56; 60; 63; 67; 74;',
  'Digital Content Labels - Exclude': 'G; PG; T;',
  'Brand Safety Sensitivity Setting': 'Use custom',
  'Brand Safety Custom Settings': "Adult; Alcohol; Derogatory; "
      + "Downloads & Sharing; Drugs; Gambling; Politics; Profanity; Religion; "
      + "Sensitive social issues; Suggestive; Tobacco; Tragedy; "
      + "Transportation Accidents; Violence; Weapons;"
  }


    

In [ ]:

    

# Configure the sdfdownloadtasks.create request
sdf_body = {
    "version": SDF_VERSION_DV360,
    "advertiserId": ADVERTISER_ID,
    "parentEntityFilter": {
        "fileType": ["FILE_TYPE_LINE_ITEM"],
        "filterType": "FILTER_TYPE_CAMPAIGN_ID",
        "filterIds": [CAMPAIGN_ID]
    }
}

# Fetch updated SDF lineitem
sdf_df = download_sdf(sdf_body)

# Overwrite targeting columns using 'targeting_template'
sdf_df['Channel Targeting - Include'] = targeting_template['Channel Targeting - Include']
sdf_df['Channel Targeting - Exclude'] = targeting_template['Channel Targeting - Exclude']
sdf_df['Inventory Source Targeting - Include'] = targeting_template['Inventory Source Targeting - Include']
sdf_df['Inventory Source Targeting - Exclude'] = targeting_template['Inventory Source Targeting - Exclude']
sdf_df['Digital Content Labels - Exclude'] = targeting_template['Digital Content Labels - Exclude']
sdf_df['Brand Safety Sensitivity Setting'] = targeting_template['Brand Safety Sensitivity Setting']
sdf_df['Brand Safety Custom Settings'] = targeting_template['Brand Safety Custom Settings']

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update1_controls.csv', index=False)

# Show sample (5 rows) of DataFrame
sdf_df.head()


    

In [ ]:

    

# Download modified csv to local storage
files.download('sdf_update1_controls.csv')

print("Success, check your downloads folder for a file called 'sdf_update1_controls.csv'")


    

In [ ]:

    

yesterday = datetime.date.today() - datetime.timedelta(2) # Actually today-2 to avoid issues with collection

# Download public ERF for geolocation info
request = gcs_service.objects().get_media(
    bucket="gdbm-public",
    object='entity/' + yesterday.strftime('%Y%m%d') + '.0.GeoLocation.json')

response = request.execute()
geolocations = json.loads(response)

print("GeoLocation.json successfully downloaded \n")
print("Here's a random sample of 5 entries:\n")
pprint.pprint(geolocations[0:5])


    

In [ ]:

    

# Provide a list of store locations
store_locations = ['United Kingdom', 'France', 'Spain', 'Germany', 'Portugal']

# Create a new dictionary to save the country code and ID later on
geo_targeting_ids = {}

# Note: GeoLocation.json is over 800,000 lines
for location in geolocations:
  if location['canonical_name'] in store_locations:
    geo_targeting_ids[location['country_code']] = location['id']
    print(location)
    
print(geo_targeting_ids)


    

In [ ]:

    

# Configure the sdfdownloadtasks.create request
sdf_body = {
    "version": SDF_VERSION_DV360,
    "advertiserId": ADVERTISER_ID,
    "parentEntityFilter": {
        "fileType": ["FILE_TYPE_LINE_ITEM"],
        "filterType": "FILTER_TYPE_CAMPAIGN_ID",
        "filterIds": [CAMPAIGN_ID]
    }
}

sdf_df = download_sdf(sdf_body)
sdf_df.head()


    

In [ ]:

    

for country in geo_targeting_ids:
  target_country = geo_targeting_ids[country]
  sdf_df.loc[sdf_df.Name.str.contains(country), 'Geography Targeting - Include'] = f'{target_country};'

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update2_geo.csv', index=False)

# Display updated DataFrame
sdf_df.head()


    

In [ ]:

    

# Download modified csv to local storage
files.download('sdf_update2_geo.csv')

print("Success, look for the file called 'sdf_update2_geo.csv' in your downloads folder")


    

In [ ]:

    

# Upload product feed using Colab's upload utility
product_feed_csv = files.upload()

contents = next(iter(product_feed_csv.values())).decode('utf-8')
products = csv.DictReader(io.StringIO(contents))

image_url_list = {}

# Iterate through each row and update dict() with sku:link
for row in products:
  image_url_list[row['sku']] = row['image_link']

pprint.pprint(image_url_list)


    

In [ ]:

    

def vision_analysis(image_url):
  """Process images using the Cloud Vision API"""
  
  # Assign image URL
  image = vision.types.Image()
  image.source.image_uri = image_url

  # Instantiates a Vision client
  client = vision.ImageAnnotatorClient(credentials=credentials)

  # Performs label detection on the image file
  response = client.label_detection(image=image)
  labels = response.label_annotations
  
  targeting_keywords = []

  for label in labels:
    targeting_keywords.append(label.description)
  
  return targeting_keywords


    

In [ ]:

    

imageslookup = {}

for sku, url in image_url_list.items():
    print(f"Vision analysis completed for: {url}")
    imageslookup[sku] = vision_analysis(url)

print("\nCreating lookup table:")
pprint.pprint(imageslookup, indent=4)


    

In [ ]:

    

for product in imageslookup:
  sdf_df.loc[sdf_df.Name.str.contains(product), \
      'Keyword Targeting - Include'] = ";".join(imageslookup[product]).lower()

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update3_keywords.csv', index=False)

sdf_df.head()


    

In [ ]:

    

# Download modified csv to local storage
files.download('sdf_update3_keywords.csv')

print("Success, look for the file called 'sdf_update3_keywords.csv' in your downloads folder")


    

In [ ]:

    

# Define DV360 report definition (i.e. metrics and filters)
report_definition = {
  'params': {
    'type': 'TYPE_GENERAL',
    'metrics': [
      'METRIC_IMPRESSIONS',
      'METRIC_CLICKS',
      'METRIC_CTR',
      'METRIC_REVENUE_ADVERTISER'
    ],
    'groupBys': [
      'FILTER_ADVERTISER',
      'FILTER_INSERTION_ORDER',
      'FILTER_LINE_ITEM',
      'FILTER_ADVERTISER_CURRENCY'
    ],
    'filters': [
      {
        'type': 'FILTER_ADVERTISER', 
        'value': ADVERTISER_ID
      }
    ],
  },
  "metadata": {
    "title": "DV360 Automation API-generated report",
    "dataRange": 'LAST_90_DAYS',
    "format": "csv"
  },
  "schedule": {
    'frequency': "ONE_TIME"
  }
}

# Create new query using report definition
operation = dbm_service.queries().createquery(body=report_definition).execute()
pprint.pprint(operation)

# Runs the given Queries.getquery request, retrying with an exponential
# backoff. Returns completed operation. Will raise an exception if the
# operation takes more than five hours to complete.
@retry.Retry(predicate=retry.if_exception_type(Exception), 
             initial=5, maximum=60, deadline=18000)
def check_get_query_completion(getquery_request):
  response = getquery_request.execute()
  pprint.pprint(response)
  if response['metadata']['running']:
    raise Exception("The operation has not completed.")
  return response

getquery_request = dbm_service.queries().getquery(queryId=operation['queryId'])
response = check_get_query_completion(getquery_request)


    

In [ ]:

    

report_url = response['metadata']['googleCloudStoragePathForLatestReport']

# Use skipfooter to remove report footer from data
report_df = pd.read_csv(report_url, skipfooter=16, engine='python')
report_df.head(10)


    

In [ ]:

    

# Define our 'KPIs'
ctr_target = 0.15
imp_threshold = 10000

# Convert IDs to remove decimal point, then string
report_df['Line Item ID'] = report_df['Line Item ID'].apply(int)
poor_performers = report_df.query('Impressions > @imp_threshold & (Clicks / Impressions)*100 < @ctr_target')

# Convert results to Python list
poor_performers = list(poor_performers['Line Item ID'])

print(f"There are {len(poor_performers)} LineItems with a CTR"
      f" < {ctr_target}% and over {imp_threshold} impressions:"
      f"\n{poor_performers}")


    

In [ ]:

    

# Configure the sdfdownloadtasks.create request
sdf_body = {
    "version": SDF_VERSION_DV360,
    "advertiserId": ADVERTISER_ID,
    "parentEntityFilter": {
        "fileType": ["FILE_TYPE_LINE_ITEM"],
        "filterType": "FILTER_TYPE_CAMPAIGN_ID",
        "filterIds": ["1914007"]
    }
}

sdf_df = download_sdf(sdf_body)
sdf_df.head()

for li in poor_performers:
  geo = sdf_df.loc[sdf_df['Line Item Id'] == li, "Geography Targeting - Include"].iloc[0]
  sdf_df.loc[sdf_df['Line Item Id'] == li, "Bid Multipliers"] = f"(geo; {geo} 0.5;);"

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update4_bidmultipliers.csv', index=False)

# Display updated DataFrame
sdf_df.head()


    

In [ ]:

    

files.download('sdf_update4_bidmultipliers.csv')

print('Success, your new SDF file has been downloaded')


    

In [ ]:

    

#TODO


    

In [ ]:

    

# Format today-2 in required date format
yesterday = (datetime.date.today() - datetime.timedelta(2)).strftime('%Y%m%d')

# Download ERF for Language.json from public GCS bucket
request = gcs_service.objects().get_media(
    bucket="gdbm-public",
    object='entity/' + yesterday + '.0.Language.json')

response = request.execute()
languages = json.loads(response)

language_targets = ['en', 'fr']
lang_targeting_ids = {}

# Search language.json for language targets 'en' and 'fr'
for lang in languages:
  if lang['code'] in language_targets:
    lang_targeting_ids[lang['code']] = lang['id']
    print(lang)
    
print(lang_targeting_ids)

# Define targeting template
targeting_template = {
 'Affinity & In Market Targeting - Include': '4569529;4586809;4497529;',
 }

# Configure the sdfdownloadtasks.create request
sdf_body = {
    "version": SDF_VERSION_DV360,
    "advertiserId": ADVERTISER_ID,
    "parentEntityFilter": {
        "fileType": ["FILE_TYPE_LINE_ITEM"],
        "filterType": "FILTER_TYPE_CAMPAIGN_ID",
        "filterIds": [CAMPAIGN_ID]
    }
}

sdf_df = download_sdf(sdf_body)

# Update DataFrame with Language and Audience targeting
sdf_df.loc[sdf_df.Name.str.contains("GB"), "Language Targeting - Include"] = f"{lang_targeting_ids['en']};"
sdf_df.loc[sdf_df.Name.str.contains("FR"), "Language Targeting - Include"] = f"{lang_targeting_ids['fr']};"
sdf_df['Affinity & In Market Targeting - Include'] = targeting_template['Affinity & In Market Targeting - Include']

# Save modified dataframe to remote storage in Colab
sdf_df.to_csv('sdf_update5_challenge.csv', index=False)

# Display updated DataFrame
sdf_df.head()


    

In [ ]:

    

# Download file to disk using Colab syntax
files.download('sdf_update5_challenge.csv')

print("Success, check your downloads folder for a file called 'sdf_update5_challenge.csv'")


    

In [ ]:

    

request = display_video_service.advertisers().lineItems().list(
    advertiserId=ADVERTISER_ID,
    filter='entityStatus="ENTITY_STATUS_ACTIVE"',
    pageSize=1
    )

response = request.execute()

# Check if response is empty.
if not response:
  print("Advertiser has no active Line Items")
else:
  pprint.pprint(response['lineItems'])


    

In [ ]:

    

def get_active_lineitems(advertiser_id, campaign_id):
  """ Returns list of Lineitems with active status """
  list_lineitems = display_video_service.advertisers().lineItems().list(
        advertiserId=ADVERTISER_ID,
        filter=f'entityStatus="ENTITY_STATUS_ACTIVE" AND campaignId="{CAMPAIGN_ID}"',
        fields='lineItems(lineItemId,displayName)' # Return only two fields
        ).execute()

  active_lineitems = [li['lineItemId'] for li in list_lineitems['lineItems']]
  return active_lineitems


    

In [ ]:

    

# Upload product feed using Colab's upload utility
product_feed_csv = files.upload()

contents = next(iter(product_feed_csv.values())).decode('utf-8')
products = list(csv.DictReader(io.StringIO(contents)))

# Create unique list of country-codes -- set() automatically de dupes
unique_country_codes = set([row['country code'] for row in products])

print(unique_country_codes)


    

In [ ]:

    

def create_insertion_order(parent_campaign_id, new_io_name):
  """Creates a new DV360 insertion order object."""

  # Define our new Insertion Order boilerplate
  newInsertionOrder = {
    "campaignId": parent_campaign_id,
    "displayName": new_io_name, # Define naming convention
    "entityStatus": 'ENTITY_STATUS_DRAFT',
    "pacing": {
        "pacingPeriod": "PACING_PERIOD_DAILY", 
        "pacingType": "PACING_TYPE_EVEN",
        "dailyMaxMicros": '1000000' # Equiv to $1 or local currency
    },
    "frequencyCap": {
        "unlimited": False,
        "timeUnit": 'TIME_UNIT_MONTHS',
        "timeUnitCount": 1,
        "maxImpressions": 5
    },
    "performanceGoal": {
      "performanceGoalType": 'PERFORMANCE_GOAL_TYPE_CPC',
      "performanceGoalAmountMicros": '1000000', # $1 CPM/CPC target
    },
    "bidStrategy": {
        "fixedBid": {
           "bidAmountMicros": "0" 
        },
    },
    "budget": {
      "automationType": "INSERTION_ORDER_AUTOMATION_TYPE_NONE",
      "budgetUnit": "BUDGET_UNIT_CURRENCY",
      "budgetSegments": [
        {
            "budgetAmountMicros": '30000000', # Equiv to $30 or local currency
            "description": 'My first segment',
            "dateRange": {
                "startDate": {
                    "year": year,
                    "month": month,
                    "day": day
                },
                "endDate":  {
                    "year": year_plus30,
                    "month": month_plus30,
                    "day": day_plus30
                }
            }
        }
    ]
    }
  }

  # API create() request to generate new Insertion Order
  newinsertionorder_request = display_video_service.advertisers().insertionOrders().create(
    advertiserId=ADVERTISER_ID,
    body=newInsertionOrder
    ).execute()
  
  # Define patch to activate new Insertion Order afer creation
  patch = {
    "entityStatus": "ENTITY_STATUS_ACTIVE",
    }

  # API patch() request
  patchinsertionorder = display_video_service.advertisers().insertionOrders().patch(
    advertiserId=ADVERTISER_ID,
    insertionOrderId=newinsertionorder_request['insertionOrderId'],
    updateMask="entityStatus",
    body=patch
    ).execute()

  print(newinsertionorder_request)
  return (newinsertionorder_request)


print("Insertion Order function created")


    

In [ ]:

    

def create_lineitem(parent_io_id, new_li_name):
  """Creates a new DV360 lineitem object."""

  # Define our new LineItem boilerplate
  newLineItem = {
  "advertiserId": ADVERTISER_ID,
  "insertionOrderId": parent_io_id,
  "displayName": new_li_name, # Define naming convention
  "lineItemType": 'LINE_ITEM_TYPE_DISPLAY_DEFAULT',
  "entityStatus": 'ENTITY_STATUS_DRAFT',
  "flight": {
    "flightDateType": "LINE_ITEM_FLIGHT_DATE_TYPE_INHERITED",
  },
  "budget": {
    "budgetAllocationType": "LINE_ITEM_BUDGET_ALLOCATION_TYPE_FIXED",
    "maxAmount": "5000000" # $5 budget
  },
  "pacing": {
      "pacingPeriod": "PACING_PERIOD_DAILY", 
      "pacingType": "PACING_TYPE_EVEN",
      "dailyMaxMicros": '1000000' # $1 per day
  },
  "frequencyCap": {
    "unlimited": True
  },
  "creativeIds": [
    # TO DO
  ],
  "partnerRevenueModel": {
    "markupType": 'PARTNER_REVENUE_MODEL_MARKUP_TYPE_MEDIA_COST_MARKUP',
    "markupAmount": '0'
  },
  "bidStrategy": {
      "fixedBid": {
          "bidAmountMicros": '1000000'
      },
    }
  }

  # API create() request to generate new Lineitem
  newlineitem_request = display_video_service.advertisers().lineItems().create(
    advertiserId=ADVERTISER_ID, 
    body=newLineItem
    ).execute()

  # Define patch to activate new Line Item afer creation
  patch = {
    "entityStatus": "ENTITY_STATUS_ACTIVE",
    }

  # API patch() request
  patchlineitem = display_video_service.advertisers().lineItems().patch(
    advertiserId=ADVERTISER_ID,
    lineItemId=newlineitem_request['lineItemId'],
    updateMask="entityStatus",
    body=patch
    ).execute()
  
  print(newlineitem_request)
  return(newlineitem_request)

print("LineItem function created")


    

In [ ]:

    

%%time
for country_code in unique_country_codes:
  # Create() and patch() new Insertion Order
  io_name = f"Google Store | {country_code} | Display | Prospecting"
  insertionorder = create_insertion_order(CAMPAIGN_ID, io_name)
  for row in products:
    if country_code in row['country code']:
      # Create() and patch() new LineItem
      li_name = f"{row['country code']} | {row['title']} | {row['sku']}"
      lineitem = create_lineitem(insertionorder['insertionOrderId'], li_name)

print("Process completed")


    

In [ ]:

    

# Create the page token variable.
next_page_token = ""

while True:
# Request the targeting options list.
  response = display_video_service.targetingTypes().targetingOptions().list(
    advertiserId=ADVERTISER_ID,
    targetingType="TARGETING_TYPE_BROWSER",
    pageToken=next_page_token
    ).execute()

  # Check if response is empty.
  if not response:
    print("List request returned no Targeting Options")
    break

  # Iterate over retrieved targeting options.
  options_dict = {}
  for option in response['targetingOptions']:
    options_dict[option['targetingOptionId']] = option['browserDetails']['displayName']

  # Break out of loop if there is no next page.
  if 'nextPageToken' not in response:
    break

  # Update the next page token.
  next_page_token = response['nextPageToken']

pprint.pprint(options_dict)


    

In [ ]:

    

# Return list of Lineitems with active status
active_lineitems = get_active_lineitems(ADVERTISER_ID, CAMPAIGN_ID)

# Fetch first Lineitem ID
lineitem_id = active_lineitems[0]

# Create a assigned targeting option object.
assigned_targeting_option_obj = {
  'browserDetails': {
      'targetingOptionId': '500072'
  }
}

# Create the assigned targeting option.
assigned_targeting_option = display_video_service.advertisers().lineItems()\
.targetingTypes().assignedTargetingOptions().create(
  advertiserId=ADVERTISER_ID,
  lineItemId=f"{lineitem_id}",
  targetingType="TARGETING_TYPE_BROWSER",
  body=assigned_targeting_option_obj
).execute()

# Display the new assigned targeting option.
print(f"Assigned Targeting Option {assigned_targeting_option['name']} was created.")


    

In [ ]:

    

# Create the page token variable.
next_page_token = ""

while True:
# Request the targeting options list.
  response = display_video_service.googleAudiences().list(
    advertiserId=ADVERTISER_ID,
    filter='displayName : "Technology"',
    pageToken=next_page_token
    ).execute()

  # Check if response is empty.
  if not response:
    print("List request returned no Targeting Options")
    break

  # Iterate over retrieved targeting options.
  options_dict = {}
  for option in response['googleAudiences']:
    options_dict[option['googleAudienceId']] = [option['displayName'], option['googleAudienceType']]

  # Break out of loop if there is no next page.
  if 'nextPageToken' not in response:
    break

  # Update the next page token.
  next_page_token = response['nextPageToken']

pprint.pprint(response)


    

In [ ]:

    

google_audience_id = "92948"

# Return list of Lineitems with active status
active_lineitems = get_active_lineitems(ADVERTISER_ID, CAMPAIGN_ID)

# Create a assigned targeting option object.
assigned_targeting_option_obj = {
    "audienceGroupDetails": {
      "includedGoogleAudienceGroup": {
        "settings": [{"googleAudienceId": f"{google_audience_id}"}]
      }
    }
}

pprint.pprint(assigned_targeting_option_obj)

# Update bulk targeting
for li in active_lineitems:
  # Create the assigned targeting option.
  assigned_targeting_option = display_video_service.advertisers().lineItems().\
  targetingTypes().assignedTargetingOptions().create(
    advertiserId=ADVERTISER_ID,
    lineItemId=f'{li}',
    targetingType='TARGETING_TYPE_AUDIENCE_GROUP',
    body=assigned_targeting_option_obj
  ).execute()
  # Display the new assigned targeting option.
  print(f"Assigned Targeting Option {assigned_targeting_option['name']} was created.")


    

In [ ]:

    

def set_default_li_targeting(lineitem_id):
  """Sets default LineItem targeting according to standard template."""

  # Define 'Channels'
  createChannelAssignedTargetingOptions = []
  for targeting_id in ['1777746835', '1778039430']:
    createChannelAssignedTargetingOptions.append(
        {'channelDetails': {'channelId': targeting_id, "negative": False }}
    )

  # Define 'Inventory'
  createInventoryAssignedTargetingOptions = []
  for targeting_id in ['1']:
    createInventoryAssignedTargetingOptions.append(
        {'inventorySourceDetails': {'inventorySourceId': targeting_id}}
    )

  # Define 'Sensitive categories'
  createSensitiveCatAssignedTargetingOptions = []
  SensitiveCategory = [
          '1163177997', 
          '1163178297', 
          '118521027123', 
          '118521027843',
          '118521028083',
          '118521028563',
          '118521028803',
          '1596254697'
    ]
  for targeting_id in SensitiveCategory:
    createSensitiveCatAssignedTargetingOptions.append(
        {'sensitiveCategoryExclusionDetails': {'excludedTargetingOptionId': targeting_id}}
    )

  # Define 'Digital content labels'
  createDigitalContentAssignedTargetingOptions = []
  ContentRatingTier = [
          '19875634320',
          '19875634200',
          '19875634080'
    ]
  for targeting_id in ContentRatingTier:
    createDigitalContentAssignedTargetingOptions.append(
        {'digitalContentLabelExclusionDetails': {'excludedTargetingOptionId': targeting_id}}
    )

  # Contruct request
  bulk_edit_line_item_request = {
    'createRequests': [
        {
            'targetingType': 'TARGETING_TYPE_CHANNEL',
            'assignedTargetingOptions': [createChannelAssignedTargetingOptions]
        },
        {
            'targetingType': 'TARGETING_TYPE_INVENTORY_SOURCE',
            'assignedTargetingOptions': [createInventoryAssignedTargetingOptions]
        },
        {
            'targetingType': 'TARGETING_TYPE_SENSITIVE_CATEGORY_EXCLUSION',
            'assignedTargetingOptions': [createSensitiveCatAssignedTargetingOptions]
        },
        {
            'targetingType': 'TARGETING_TYPE_DIGITAL_CONTENT_LABEL_EXCLUSION',
            'assignedTargetingOptions': [createDigitalContentAssignedTargetingOptions]
        },
    ]
  }

  # Edit the line item targeting.
  request = display_video_service.advertisers().lineItems().bulkEditLineItemAssignedTargetingOptions(
      advertiserId=ADVERTISER_ID,
      lineItemId=lineitem_id,
      body=bulk_edit_line_item_request
      )

  response = request.execute()

  # Check if response is empty.
  # If not, iterate over and display new assigned targeting options.
  if not response:
    print("Bulk edit request created no new AssignedTargetingOptions")
  else:
    for assigned_targeting_option in response["createdAssignedTargetingOptions"]:
      print("Assigned Targeting Option %s was created."
            % assigned_targeting_option["name"])

print("Lineitem targeting function created")


    

In [ ]:

    

# Return list of Lineitems with active status
active_lineitems = get_active_lineitems(ADVERTISER_ID, CAMPAIGN_ID)

# Update bulk targeting
for li in active_lineitems:
  set_targeting = set_default_li_targeting(li)


    

In [ ]:

    

def optimise_lineitem(lineitem_id, action):
  """ Optimises lineitem according to given parameter."""

  lineitem_object = display_video_service.advertisers().lineItems().get(
      advertiserId=ADVERTISER_ID,
      lineItemId=lineitem_id).execute()

  if lineitem_object['entityStatus'] == 'ENTITY_STATUS_ACTIVE':
    if action == "pause":
      patch = {
      "entityStatus": "ENTITY_STATUS_PAUSED",
      }
      
      lineitem_patched = display_video_service.advertisers().lineItems().patch(
        advertiserId=ADVERTISER_ID,
        lineItemId=lineitem_id,
        updateMask="entityStatus",
        body=patch).execute()
      print(f"LineItemID {lineitem_patched['name']} was paused")
    
    elif action == "optimise":
      patch = {
      'bidStrategy': {
          'fixedBid': { "bidAmountMicros": '500000' },
          }
      }
      
      lineitem_patched = display_video_service.advertisers().lineItems().patch(
        advertiserId=ADVERTISER_ID,
        lineItemId=lineitem_id,
        updateMask="bidStrategy",
        body=patch).execute()

      print(f"{lineitem_patched['name']} was optimised")
    else:
      print("Not a valid action, must be either 'pause' or 'optimise'")
  else:
    print(f"{lineitem_object['name']} already paused/archived - no action taken")

print("Optimisation function created")


    

In [ ]:

    

out_of_stock_list = []
products = csv.DictReader(io.StringIO(contents))

# Iterate through each row, checking for products where availability = 0
for row in products:
  if row['availability'] == '0':
    out_of_stock_list.append(row['sku'])

# This should generate a list of 9 SKUs that are no-longer in stock    
print(f"We've found {len(out_of_stock_list)} out-of-stock products: {out_of_stock_list}")


    

In [ ]:

    

# Return list of Lineitems with active status
active_lineitems = get_active_lineitems(ADVERTISER_ID, CAMPAIGN_ID)

# Iterate through out-of-stock list. If sku is found in lineitem's name, perform optimisation.
for product in out_of_stock_list:
  for k, v in active_lineitems.items():
    if product in k:
      optimise_operation = optimise_lineitem(v, 'pause')


    

In [ ]:

    

# Define DV360 report definition (i.e. metrics and filters)
report_definition = {
  'params': {
    'type': 'TYPE_GENERAL',
    'metrics': [
      'METRIC_IMPRESSIONS',
      'METRIC_CLICKS',
      'METRIC_CTR',
      'METRIC_REVENUE_ADVERTISER'
    ],
    'groupBys': [
      'FILTER_ADVERTISER',
      'FILTER_INSERTION_ORDER',
      'FILTER_LINE_ITEM',
      'FILTER_ADVERTISER_CURRENCY'
    ],
    'filters': [
      {
        'type': 'FILTER_ADVERTISER', 
        'value': ADVERTISER_ID
      }
    ],
  },
  "metadata": {
    "title": "DV360 Automation API-generated report",
    "dataRange": 'LAST_90_DAYS',
    "format": "csv"
  },
  "schedule": {
    'frequency': "ONE_TIME"
  }
}

# Create new query using report definition
operation = dbm_service.queries().createquery(body=report_definition).execute()
pprint.pprint(operation)

# Runs the given Queries.getquery request, retrying with an exponential
# backoff. Returns completed operation. Will raise an exception if the
# operation takes more than five hours to complete.
@retry.Retry(predicate=retry.if_exception_type(Exception), initial=5, maximum=60, deadline=18000)
def check_get_query_completion(getquery_request):
  response = getquery_request.execute()
  pprint.pprint(response)
  if response['metadata']['running']:
    raise Exception("The operation has not completed.")
  return response

getquery_request = dbm_service.queries().getquery(queryId=operation['queryId'])
response = check_get_query_completion(getquery_request)


    

In [ ]:

    

# Capture report URL from response
report_url = response['metadata']['googleCloudStoragePathForLatestReport']

# Use skipfooter to remove report footer from data
report_df = pd.read_csv(report_url, skipfooter=16, engine='python')
report_df.head(10)


    

In [ ]:

    

# Define our 'KPIs'
ctr_to_pause = 0.1
ctr_to_optimise = 0.3
imp_threshold = 5000

# Convert IDs to remove decimal point, then string
report_df['Line Item ID'] = report_df['Line Item ID'].apply(int)
lineitems_to_pause = report_df.query('Impressions > @imp_threshold and (Clicks / Impressions)*100 < @ctr_to_pause')
lineitems_to_reducebid = report_df.query('Impressions > @imp_threshold and (Clicks / Impressions)*100 > @ctr_to_pause < @ctr_to_optimise')

# Convert results to Python list
lineitems_to_pause = list(lineitems_to_pause['Line Item ID'])
lineitems_to_reducebid = list(lineitems_to_reducebid['Line Item ID'])

print(f"Found {len(lineitems_to_pause)} LineItems with a CTR"
      f" < {ctr_to_pause}% and > {imp_threshold} impressions:"
      f"\n{lineitems_to_pause}\n")

print(f"Found {len(lineitems_to_reducebid)} LineItems with a CTR"
      f" between {ctr_to_pause}%-{ctr_to_optimise}%, and > {imp_threshold} impressions:"
      f"\n{lineitems_to_reducebid}")


    

In [ ]:

    

%%time
if lineitems_to_pause:
  for lineitem in lineitems_to_pause:
    optimise_lineitem(str(lineitem), 'pause')

if lineitems_to_reducebid:
  for lineitem in lineitems_to_reducebid:
    optimise_lineitem(str(lineitem), 'optimise')
  
print("Optimisation completed")


    

In [ ]:

    

def upload_creative_image_asset(asset_url, click_url):
  """Creates a new DV360 creative object."""

  # Fetch asset from cloud storage using requests library
  asset = requests.get(asset_url)

  # Create upload object from http image url
  fh = io.BytesIO(asset.content)
  media_body = http.MediaIoBaseUpload(fh, mimetype='image/png',
                                      chunksize=1024*1024, resumable=True)

  # Extract filename from url path
  filename = str(asset_url.rsplit(sep="/", maxsplit=1)[1])

  # Create the request body
  body = {
    'filename': filename
  }

  # Upload the asset
  asset_request = display_video_service.advertisers().assets().upload(
    advertiserId=ADVERTISER_ID,
    body=body,
    media_body=media_body
  ).execute()

  # Display the new asset media ID
  print(f"Asset was created with media ID {asset_request['asset']['mediaId']}")

  display_name = f"{filename}".split(sep=".")[0].lower() + " 300x250"

  # Create a creative object.
  creative_obj = {
    "displayName": f"{display_name}",
    "entityStatus": "ENTITY_STATUS_ACTIVE",
    "creativeType": "CREATIVE_TYPE_STANDARD",
    "hostingSource": "HOSTING_SOURCE_HOSTED",
    "dimensions": {
        "widthPixels": 300,
        "heightPixels": 250
    },
    "assets": [
      {
        "asset": {"mediaId": asset_request['asset']['mediaId']},
        "role": "ASSET_ROLE_MAIN"
      }
    ],
    "exitEvents": [
      {
        "type": "EXIT_EVENT_TYPE_DEFAULT",
        "url": f"{click_url}",
      }
    ]
  }

  creative_request = display_video_service.advertisers().creatives().create(
      advertiserId=ADVERTISER_ID,
      body=creative_obj
      ).execute()

  # Display the new creative ID
  print(f"Creative was created with ID {creative_request['creativeId']}"
        f" and DisplayName '{creative_request['displayName']}'")

  pprint.pprint(creative_request)

print("Creative upload function defined")


    

In [ ]:

    

image_assets = {
  'https://storage.googleapis.com/dv3workshop/googlestore/pixelbook.png': 
  'https://store.google.com/product/google_pixelbook',
  'https://storage.googleapis.com/dv3workshop/googlestore/googlehome.png': 
  'https://store.google.com/product/google_home_hub',
  'https://storage.googleapis.com/dv3workshop/googlestore/googlehomemini.png': 
  'https://store.google.com/product/google_home_mini',
  'https://storage.googleapis.com/dv3workshop/googlestore/pixel2.png': 
  'https://store.google.com/product/pixel_2',
  'https://storage.googleapis.com/dv3workshop/googlestore/chromecastultra.png': 
  'https://store.google.com/product/chromecast_ultra'
}

for asset, click_url in image_assets.items():
  upload_creative_image_asset(asset, click_url)


    

In [ ]:

    

#TODO


    

In [ ]:

    

%%time
# Load flight information from CSV file
googleairways_routes = files.upload()
contents = next(iter(googleairways_routes.values())).decode('utf-8')
routes = list(csv.DictReader(io.StringIO(contents)))

# Create a unique set (de-duped) of cities from the routes provided
unique_cities = set()
for row in routes:
 unique_cities.add(row['airport-city'])
print(unique_cities)

# Create Campaign and Patch()
new_campaign = create_campaign("Google Airways")
print(new_campaign)

# Step through each city within our unique set of cities
for city in unique_cities:
 # Create Insertion Order and Patch()
 io_name = f"Flights | {city}"
 create_io = create_insertion_order(new_campaign['campaignId'], io_name)
# Step through each route(row) of the CSV upload
 for row in routes:
   if city == row['airport-city']:
     # Create LineItems and Patch()
     li_name = f"Flight {row['flightno']} | {row['depairport-city']} to {row['arrairport-city']}"
     create_lis = create_lineitem(create_io['insertionOrderId'], li_name)

print("Process completed")


    

In [ ]:

    

# @title { display-mode: "form" }
#@markdown Reset DV360 account

# Exclude following campaigns in the reset process
protected_campaigns = ['1914007','985747']

def reset_demo_account():
  """Reset DV360 account to earlier state."""

  print("Resetting DV360 account...")
  
  # Reactivate Campaigns
  list_campaigns = display_video_service.advertisers().campaigns().list(
    advertiserId=ADVERTISER_ID,
    filter='entityStatus="ENTITY_STATUS_ACTIVE"'
    ).execute()

  results = list_campaigns['campaigns']
  print(f"Found {len(results)} active campaigns")

  for index, campaign in enumerate(results, start=1):
    print(f"Campaign {index} of {len(results)}")
    pause_campaign(campaign['campaignId'])

  # Reactivate LineItems
  list_lineitems = display_video_service.advertisers().lineItems().list(
  advertiserId=ADVERTISER_ID,
  filter='entityStatus="ENTITY_STATUS_PAUSED" AND campaignId="1914007"'
  ).execute()

  if len(list_lineitems) == 0:
    print("No paused lineitems found")
  else:
    for index, li in enumerate(list_lineitems['lineItems'], start=1):
      print(f"Lineitem {index} of {len(list_lineitems['lineItems'])}")
      lineitem_id = li['lineItemId']
      activate_lineItem(lineitem_id)
  
  print("Account reset completed")

def delete_campaign(campaign_id):
  """Updates DV360 campaign object status to deleted"""

  patch = {
    'entityStatus':'ENTITY_STATUS_DELETED'
  }

  if campaign_id in protected_campaigns:
    print(f"Campaign ID {campaign_id} not deleted (protected campaign)")
  else:
    try:
      deleted_campaign = display_video_service.advertisers().campaigns().delete(
        advertiserId=ADVERTISER_ID,
        campaignId=campaign_id
        ).execute()
      print(f"{campaign_id} successfully deleted")
    except:
      print(f"Could not delete campaign")

def archive_campaign(campaign_id):
  """Updates DV360 campaign object status to archived"""

  patch = {
    'entityStatus':'ENTITY_STATUS_ARCHIVED'
  }

  if campaign_id in protected_campaigns:
    print(f"Campaign ID {campaign_id} not archived (protected campaign)")
  else:
    archive_campaign = display_video_service.advertisers().campaigns().patch(
        advertiserId=ADVERTISER_ID,
        campaignId=campaign_id,
        updateMask="entityStatus",
        body=patch
        ).execute()
    print(f"Campaign ID {campaign_id} successfully archived")

def pause_campaign(campaign_id):
  """Updates DV360 campaign object status to paused"""
  
  patch = {
    'entityStatus':'ENTITY_STATUS_PAUSED'
  }

  if campaign_id in protected_campaigns:
    print(f"Campaign ID {campaign_id} not paused (protected campaign)")
  else:
    paused_campaign = display_video_service.advertisers().campaigns().patch(
        advertiserId=ADVERTISER_ID,
        campaignId=campaign_id,
        updateMask="entityStatus",
        body=patch
        ).execute()
    print(f"Campaign ID {campaign_id} successfully paused")

def activate_lineItem(lineitem_id):
  """Updates DV360 lineitem object status to active"""
  
  patch = {
    'entityStatus':'ENTITY_STATUS_ACTIVE'
  }

  activate_lineitem = display_video_service.advertisers().lineItems().patch(
    lineItemId=lineitem_id,
    advertiserId=ADVERTISER_ID,
    updateMask="entityStatus",
    body=patch
    ).execute()
  print(f"Lineitem ID {lineitem_id} reactivated")

# Call main function to intialise reset procedure
reset_demo_account()