【備忘録】DV360の配信結果をPythonで取得する

import csv
import datetime
import io
import json
import pprint
import os

from google.api_core import retry
from google.colab import files
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient import discovery
from googleapiclient import http
import pandas as pd
import requests

print('Successfully imported Python libraries!')

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_json = {"installed":
                       {"client_id":"********",
                        "project_id":"********",
                        "auth_uri":"********",
                        "token_uri":"********",
                        "auth_provider_x509_cert_url":"********",
                        "client_secret":"********",
                        "redirect_uris":["********","********"]
                        }
                       }

flow = InstalledAppFlow.from_client_config(client_secrets_json, API_SCOPES)
credentials_apis = flow.run_console()

creds = None
if os.path.exists("token.pickle"):
    with open("token.pickle", "rb") as token:
        creds = pickle.load(token)
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file("client_id.json", SCOPES)
        # creds = flow.run_local_server()
        creds = flow.run_console()
    with open("token.pickle", "wb") as token:
        pickle.dump(creds, token)

print('Success!')

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

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

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

print('Display Video API service object created')

# このIDの部分で欲しい情報の大枠が決まる。
PARTNER_ID = '********'  
ADVERTISER_ID = '********'  
CAMPAIGN_ID = '********'  

# For use with legacy DBM API
SDF_VERSION = '5.3'  

# For use with DV360 API
SDF_VERSION_DV360 = 'SDF_VERSION_5_3'
print('DV360 settings saved!')

# Define DV360 report definition (i.e. metrics and filters)
num = 0
pd.set_option('display.max_columns', 20)

report_definition = {
    'params': {
        'type': 'TYPE_GENERAL',
        'metrics': [
                    'METRIC_IMPRESSIONS',
                    'METRIC_CLICKS',
                    'METRIC_CTR',
                    'METRIC_REVENUE_ADVERTISER',
                    'METRIC_ACTIVE_VIEW_ELIGIBLE_IMPRESSIONS',
                    'METRIC_ACTIVE_VIEW_MEASURABLE_IMPRESSIONS',
                    'METRIC_ACTIVE_VIEW_PCT_MEASURABLE_IMPRESSIONS',
                    'METRIC_ACTIVE_VIEW_VIEWABLE_IMPRESSIONS',
                    'METRIC_ACTIVE_VIEW_PCT_VIEWABLE_IMPRESSIONS',
                    'METRIC_TRUEVIEW_VIEWS',
                    'METRIC_TRUEVIEW_VIEW_RATE',
        ],
        'groupBys': [
            'FILTER_DATE',
            'FILTER_ADVERTISER',
            'FILTER_ADVERTISER_NAME',
            'FILTER_INSERTION_ORDER',
            'FILTER_INSERTION_ORDER_NAME',
            'FILTER_LINE_ITEM',
            'FILTER_LINE_ITEM_NAME',
            'FILTER_CREATIVE_ID',
            'FILTER_CREATIVE',
            'FILTER_CREATIVE_TYPE',
            'FILTER_DEVICE_TYPE',
            'FILTER_ADVERTISER_CURRENCY',
            'FILTER_INSERTION_ORDER_GOAL_TYPE',
            'FILTER_LINE_ITEM_BUDGET',
        ],
        'filters': [{
            'type': 'FILTER_PARTNER',
            'value': ********
        }],
    },
    'metadata': {
        'title': 'DV360 Automation API-generated report',
        'dataRange': 'PREVIOUS_DAY',
        'format': 'csv'
    },
    'schedule': {
        'frequency': 'ONE_TIME'
    }
}

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

# 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()
  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)

# 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)
pprint.pprint(report_df.head(10)) # ちゃんと取得できているかの確認。

!pip install google-api-python-client