%%writefile credentials.json
{
  "type": "service_account",
  "project_id": "[project_id]",
  "private_key_id": "[private_key_id]",
  "private_key": "[private_key]",
  "client_email": "[client_email]",
  "client_id": "[client_id]",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "[client_x509_cert_url]"
  }

%%writefile utils_mini.py

from google.oauth2 import service_account
from google.cloud import bigquery
from googleapiclient import discovery

def get_credentials():
  credentials = service_account.Credentials.from_service_account_file(
    'credentials.json', scopes=["https://www.googleapis.com/auth/cloud-platform"])
  print("credentials"+str(credentials))
  return credentials

def init_bq():
  credentials = get_credentials()
  bq_client = bigquery.Client(credentials=credentials, project=credentials.project_id)
  return bq_client

def init_cm():
  api_name = 'dfareporting'
  api_version = 'v3.4'
  oauth_scopes = ['https://www.googleapis.com/auth/dfatrafficking']

  credentials = get_credentials()
  credentials = credentials.with_scopes(oauth_scopes)
  service = discovery.build(api_name, api_version, credentials=credentials)

  return service

bq_client = init_bq()
cm_client = init_cm()

%%writefile bq_helper_mini.py

from utils_mini import bq_client
from google.cloud import bigquery
import ndjson

#bq_input_schema: row_num, creative_id, file_name (should include extension), full_url, creative_pixel_size (recommended) 

def write_batch_to_json(creative_data, batch_file_name):
  with open(batch_file_name, 'w') as f:
    ndjson.dump(creative_data, f)

def write_to_bq(dataset_name, table_name, table_file_name):
  try:
    global bq_client
    dataset_ref = bq_client.dataset(dataset_name)
    table_ref = dataset_ref.table(table_name)
    job_config = bigquery.LoadJobConfig()
    job_config.write_disposition = 'WRITE_TRUNCATE'
    job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
    job_config.autodetect = True
    with open(table_file_name, "rb") as source_file:
      job = bq_client.load_table_from_file(source_file, table_ref,
                                           job_config=job_config)
      job.result()
    print(f"Loaded {table_name} table")
  except Exception as e:
    print(f"ERROR - {table_name}: {e}")

%%writefile cm_helper.py

from __future__ import print_function

import random
import time

from utils_mini import cm_client
from googleapiclient.errors import HttpError
from ssl import SSLError
import pandas as pd
import requests

def retry(request, retries=4):
  """Retries a standard CM request for appropriate error codes.

  Args:
    request: CM request (before .execute())
    retries: cap on retries

  Returns:
    response

  Raises:
    HttpError: if exceeded
  """
  
  for this_retry in range(retries):
    try:
      return request.execute()
    except HttpError as e:
      if this_retry == retries - 1 or e.resp.status not in [403, 429, 500, 503]:
        raise
      wait = 10 * 2**this_retry
      time.sleep(wait)
    except SSLError as e:
      if this_retry == retries - 1 or 'timed out' not in e.message:
        raise
      wait = 10 * 2**this_retry
      time.sleep(wait)


def fetch_cm_creatives(cm_profile_id, job_type,
                       start_date=None, end_date=None, limit=False):
  """Fetches creatives using the CM API.

  Args:
    cm_profile_id: Campaign Manager User Profile with read access to creatives
    job_type: 'image' or 'video'
    start_date: filter on last changelog timestamp
    end_date: filter on last changelog timestamp
    limit: optional numerical limit for number of creatives

  Returns:
    list of dicts, each with keys Creative_ID, Advertiser_ID, Creative_Name,
      Full_URL
  """

  global cm_client

  # This extraction is done in three steps:
  # 1. Search changelogs for all creatives modified in date range
  # In batches of 500 (maximum filter size for creative IDs):
  # 2. Fetch creative objects matching those IDs
  # 3. Extract URLs from each batch of creatives
  # Step 1 is done because a date filter isn't available in the API's creative
  # endpoint, and step 2 is done to reduce the number of API calls vs. 1 per
  # creative.

  # STEP 1: search changelogs
  print('fetching creative updates from changelogs...')

  # make time/date strings for filtering changelogs
  zero_time = "T00:00:00-00:00" # UTC
  start_str = None if start_date is None else str(start_date) + zero_time
  end_str = None if end_date is None else str(end_date) + zero_time

  # assemble request
  request = cm_client.changeLogs().list(profileId=cm_profile_id,
                                 objectType='OBJECT_CREATIVE',
                                 minChangeTime=start_str,
                                 maxChangeTime=end_str)

  # paginate
  creative_ids = set()
  start = time.time()
  page = 0
  while True:
    page += 1
    print('fetching page {}, time: {}...'
          .format(page, time.time() - start))
    response = retry(request)

    # collect creative IDs from changelog response
    for changelog in response['changeLogs']:
      creative_ids.add(changelog['objectId'])

    if 'nextPageToken' in response:
      request = cm_client.changeLogs().list_next(request, response)
    else:
      break
    if limit and len(creative_ids) >= limit:
      break

  creative_ids = list(creative_ids)
  if limit and len(creative_ids) > limit:
    creative_ids = creative_ids[:limit]
  print('found {} creatives modified in date range'.format(len(creative_ids)))

  # BATCH: define batches for steps 2 & 3
  batch_size = 500
  out_creatives = []  # final output collection
  row_num=1
  for i in range(0, len(creative_ids), batch_size):
    print('processing creative batch {} to {}...'.format(i, i + batch_size))

    # STEP 2: assemble creative list
    print('fetching creative details...')
    batch_cids = creative_ids[i:i + batch_size]
    request = cm_client.creatives().list(profileId=cm_profile_id, ids=batch_cids)
    response = retry(request)
    creatives = response['creatives']

    # STEP 3: extract asset URLs
    print('extracting URLs...')
    for creative in creatives:
      if 'creativeAssets' not in creative:
        continue

      assets = creative['creativeAssets']
      assets.sort(reverse=True, key=(lambda asset: asset['fileSize']))

      accepted_formats = {
          'video': ['mp4', 'mov', 'wmv', 'm4v', 'webm'],
          'image': ['jpg', 'png', 'gif', 'jpeg','html','htm']
      }

      url = None
      for asset in assets:
        # check two special cases for video creatives first, in which case
        # the URL is easy to get
        if job_type == 'video' and 'progressiveServingUrl' in asset:
          url = asset['progressiveServingUrl']
          break
        elif job_type == 'video' and 'streamingServingUrl' in asset:
          url = asset['streamingServingUrl']
          break
        # otherwise, for image creatives or video creatives not captured by the
        # above, try a reconstructed URL and check the file extension
        else:
          # one parameter in the reconstructed_url is unknown, and we
          # must try multiple candidates
          params = [creative['advertiserId'], 'sadbundle', 'simgad']
          
          for param in params:
            reconstructed_url = 'https://s0.2mdn.net/{}/{}'.format(
                param,
                asset['assetIdentifier']['name'])
            extension = reconstructed_url.split('.')[-1].lower()

            if extension in accepted_formats[job_type]:
              # check if valid url
              request = requests.get(reconstructed_url)
              if request.status_code == 200:
                url = reconstructed_url
                break

      if url:
        if (creative['size']['width']>1) and (creative['size']['height']):
          out_creatives.append({
              'row_num' : row_num,
              'creative_id': creative['id'],
              'advertiser_id': creative['advertiserId'],
              'creative_name': creative['name'],
              'full_url': url,
              'file_name': str(creative['name']+"."+extension),
              'creative_pixel_size': str(str(creative['size']['width'])+'x'+str(creative['size']['height'])),
              'last_modified_date': ""
          })
          row_num+=1

  print('found {} creatives with suitable assets'.format(len(out_creatives)))
  return out_creatives

"""
Explanation: <a href="https://colab.research.google.com/github/google/oculi/blob/master/colabs/CM360/%5Bcolab_1%5D_Creative_Extraction.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>
The Project focuses on analysing Ad Creatives and its related performance metrics and tries to find correlations between the elements present in the past creatives to suggest what can be good add-ons and what can help to improve performance of future creatives.
GCP Setup

Create a new Service Account is created in the IAM. Assign the following permissions to the service account:
BigQuery Admin
Storage Admin
optional: AutoML Editor


Enable the DCM/DFA Reporting and Trafficking API, using the link: create or select a project in the Google API Console and enable the API. Using this link guides you through the process and activates the DCM/DFA Reporting and Trafficking API automatically.
Enable the Vision API, using this link
Download the json key for the new Service Account created in step1 and paste in cell below.

CM Setup

For CM link, follow this link: Manage user access to provide access to the service account email and get job profile id
Use the offline reporting API to download the creative level performance data (Creative ID will be the primary key)

Input1
Paste the json key downloaded from GCP over placeholder contents in cell below
End of explanation
"""


from cm_helper import *

profile_id =  6471785#@param {type:'integer'}
start_date =  "2019-01-03" #@param {type:'string'}
end_date = "2021-04-21" #@param {type:'string'}
creatives = fetch_cm_creatives(cm_profile_id=profile_id,
                               job_type='image',
                               start_date=start_date,
                               end_date=end_date)

print("#Creatives with suitable assets : " + str(len(creatives)))

"""
Explanation: Input2

CM_profile_id : Obtained from CM account while setting up user profile
job_type : image to process jpeg, gif & html5 creatives
start_date & end_date : To filter out creatives changed with in a specific time duration
End of explanation
"""


creatives[0]

"""
Explanation: Viewing the Data for a Creative
End of explanation
"""


!pip install ndjson

"""
Explanation: Install python dependencies
End of explanation
"""


from bq_helper_mini import *

write_batch_to_json(creatives, "creatives.json")

"""
Explanation: Exporting the urls in json file
End of explanation
"""


input_dataset_name="demoverse" #@param {type:'string'}
input_table_name="creative_urls" #@param {type:'string'}
write_to_bq(dataset_name =input_dataset_name  #BQ Dataset Name
            ,table_name = input_table_name #BQ Table Name
            ,table_file_name = "creatives.json" #json file name
            )

"""
Explanation: Exporting the Urls to BQ dataset
Inputs
- Enter valid BQ dataset Name
- Enter valid BQ table Name
End of explanation
"""


