DV360 API Example

Overview

We had a project where the client needed to build a DV360 data warehouse fast. This example shows how to download various DV360 endpoints from partner down to creative. The workflow includes a few tricks for doing recursive API calls based on earlier loaded data, paginating API endpoints that can only take a finite number of identifiers, and applying filters to reduce the size of the calls.

DV360 API Endpoints

Each one of these are replicated to a table in BigQuery:

• partners.list
• advertisers.list
• advertisers.lineItems.list
• advertisers.campaigns.list
• advertisers.campaigns.listAssignedTargetingOptions - Note the append option, adds call parameters to the result.
• advertisers.lineItems.bulkListAssignedTargetingOptions - Note the filters applied to the API call.
• inventorySources.list
• advertisers.creatives.list - Note the MOD operation, limits the call to 50 line items at a time per API requirement.

Execution

python3 bqflow/run.py workflow.json -s $SERVICE_JSON -p $PROJECT_ID

Workflow

{
  "tasks": [
    { "dataset": {
      "description":"Create dataset.",
      "auth": "service",
      "dataset": "my_dv360"
    }},
    { "google_api": {
      "description":"Pull my partners.",
      "api": "displayvideo",
      "version": "v3",
      "auth": "service",
      "function": "partners.list",
      "iterate": true,
      "kwargs": {},
      "results": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "table": "DV360_Partners"
        }
      }
    }},
    { "google_api": {
      "description":"Pull my advertiser.",
      "api": "displayvideo",
      "version": "v3",
      "auth": "service",
      "function": "advertisers.list",
      "iterate": true,
      "kwargs_remote": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "query": "SELECT DISTINCT CAST(partnerId  AS STRING) partnerId FROM `DV360_Partners`"
        }
      },
      "results": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "table": "DV360_Advertisers"
        }
      }
    }},
    { "google_api": {
      "description":"Pull my line items.",
      "api": "displayvideo",
      "version": "v3",
      "auth": "service",
      "function": "advertisers.lineItems.list",
      "iterate": true,
      "kwargs_remote": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "query": "SELECT DISTINCT CAST(advertiserId AS STRING) AS advertiserId FROM `DV360_Advertisers`"
        }
      },
      "results": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "table": "DV360_LineItems"
        }
      }
    }},
    { "google_api": {
      "description":"Pull my campaigns.",
      "api": "displayvideo",
      "version": "v3",
      "auth": "service",
      "function": "advertisers.campaigns.list",
      "iterate": true,
      "kwargs_remote": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "query": "SELECT DISTINCT CAST(advertiserId AS STRING) AS advertiserId FROM `DV360_Advertisers`"
        }
      },
      "results": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "table": "DV360_Campaigns"
        }
      }
    }},
    { "google_api": {
      "description":"Pull my campaign targeting",
      "api": "displayvideo",
      "version": "v3",
      "auth": "service",
      "function": "advertisers.campaigns.listAssignedTargetingOptions",
      "iterate": true,
      "kwargs_remote": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "query": "SELECT 
            CAST(advertiserId AS STRING) AS advertiserId,
            CAST(campaignId AS STRING) AS campaignId,
            'targetingType=TARGETING_TYPE_VIEWABILITY OR targetingType=TARGETING_TYPE_SENSITIVE_CATEGORY_EXCLUSION OR targetingType=TARGETING_TYPE_DIGITAL_CONTENT_LABEL_EXCLUSION OR targetingType=TARGETING_TYPE_THIRD_PARTY_VERIFIER' AS filter,
            5000 AS pageSize
            FROM `DV360_Campaigns`"
        }
      },
      "append":[
        { "name": "advertiserId", "type": "INTEGER", "mode": "REQUIRED" },
        { "name": "campaignId", "type": "INTEGER", "mode": "REQUIRED" }
      ],
      "results": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "table": "DV360_Campaigns_Targeting"
        }
      }
    }},
    { "google_api": {
      "description":"Pull my lineitem targeting",
      "api": "displayvideo",
      "version": "v3",
      "auth": "service",
      "function": "advertisers.lineItems.bulkListAssignedTargetingOptions",
      "iterate": true,
      "kwargs_remote": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "query": "SELECT 
            CAST(advertiserId AS STRING) AS advertiserId,
            ARRAY_AGG(lineItemId) OVER(PARTITION BY advertiserId ORDER BY num ASC RANGE BETWEEN CURRENT ROW AND 49 FOLLOWING) AS lineItemIds,
            'targetingType=TARGETING_TYPE_CHANNEL OR targetingType=TARGETING_TYPE_SENSITIVE_CATEGORY_EXCLUSION OR targetingType=TARGETING_TYPE_AUTHORIZED_SELLER_STATUS OR targetingType=TARGETING_TYPE_CONTENT_INSTREAM_POSITION OR targetingType=TARGETING_TYPE_CONTENT_OUTSTREAM_POSITION OR targetingType=TARGETING_TYPE_DIGITAL_CONTENT_LABEL_EXCLUSION OR targetingType=TARGETING_TYPE_INVENTORY_SOURCE' AS filter,
            5000 AS pageSize
          FROM (
            SELECT 
              advertiserId,
              lineItemId,
              ROW_NUMBER() OVER(PARTITION BY advertiserId ORDER BY lineItemId ASC) AS num,
            FROM `DV360_LineItems`
          )
          QUALIFY MOD(num, 50) = 1"
        }
      },
      "results": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "table": "DV360_LineItems_Targeting"
        }
      }
    }},
    { "google_api": {
      "description":"Pull my inventory sources.",
      "api": "displayvideo",
      "version": "v3",
      "auth": "service",
      "function": "inventorySources.list",
      "iterate": true,
      "kwargs_remote": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "query": "SELECT DISTINCT CAST(advertiserId AS STRING) AS advertiserId FROM `DV360_Advertisers`"
        }
      },
      "results": {
        "bigquery": {
          "auth": "service",
          "dataset": "my_dv360",
          "table": "DV360_Inventory_Sources"
        }
      }
    }},
    { "google_api": {
      "description":"Pull my creatives.",
      "api": "displayvideo",
      "version": "v3",
      "auth": "user",
      "function": "advertisers.creatives.list",
      "iterate": true,
      "kwargs_remote": {
        "bigquery": {
          "auth": "user",
          "dataset": "my_dv360",
          "comment":"500 LIMIT / (12 text + 11 id + 4 or) = 500 / 27 = 18 ids per call",
          "query": "SELECT
                      CAST(advertiserId AS STRING) AS advertiserId,
                      ARRAY_TO_STRING(ARRAY_AGG(FORMAT('lineItemIds:%d', lineItemId)) OVER(PARTITION BY advertiserId ORDER BY num ASC RANGE BETWEEN CURRENT ROW AND 17 FOLLOWING), ' OR ') AS filter,
                      'creatives.advertiserId,creatives.creativeId,creatives.reviewStatus.approvalStatus,nextPageToken' AS fields,
                      200 AS pageSize
                    FROM (
                      SELECT
                        advertiserId,
                        lineItemId,
                        ROW_NUMBER() OVER(PARTITION BY advertiserId ORDER BY lineItemId ASC) AS num
                      FROM `my_dv360.DV360_LineItems` AS L
                    )
                    QUALIFY MOD(num, 18) = 1"
        }
      },
      "results": {
        "bigquery": {
          "auth": "user",
          "dataset": "my_dv360",
          "table": "DV360_Creatives"
        }
      }
    }}
  ]
}