All
gps_fixed
DV360 Targeting Audit
Continously audit all targeting settings for a DV360 Partner in one dashboard. Use filters to locate errant targeting and quickly correct issues.
Impact Level
| Spend Optimization |
|
| Spend Growth |
|
| Time Savings |
|
| Account Health |
|
| Csat Improvement |
|
Value Proposition
| See all settings across Partner, Advertiser, and Line Item in one view. |
| Spot errant targeting in seconds. |
| Correct targeting before and during campaigns. |
| Deploys at access level of user, managed via DV360. |
Instructions
| Wait for BigQuery->->->Targeting Audit to be created. |
| Join the StarThinker Assets Group to access the following assets |
| Copy Sample DV360 Targeting Audit. |
| Edit the data source to point at BigQuery->->->Targeting_Audit. |
| Or give these intructions to the client. |
Details
| Open Source | YES |
| Age | Dec. 30, 2020 (2 years) |
| Authors | kenjora@google.com |
| Shedule Days | Configured by user. |
| Shedule Hours | 4, 10, 8 |
[
{
"dataset": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "partners.get",
"kwargs": {
"partnerId": {
"field": {
"name": "partner",
"kind": "string",
"order": 4,
"default": "",
"description": "DV360 Partner to load, user access determines returned data."
}
}
},
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_Partners"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "advertisers.list",
"kwargs": {
"partnerId": {
"field": {
"name": "partner",
"kind": "string",
"order": 4,
"default": "",
"description": "DV360 Partner to load, user access determines returned data."
}
},
"fields": "advertisers.displayName,advertisers.advertiserId,advertisers.partnerId,nextPageToken"
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_Advertisers"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "advertisers.lineItems.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(advertiserId AS STRING) AS advertiserId FROM `{dataset}.DV_Advertisers`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_LineItems"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "partners.targetingTypes.assignedTargetingOptions.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(partnerId AS STRING) AS partnerId, 'TARGETING_TYPE_CHANNEL' AS targetingType FROM `{dataset}.DV_Partners`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_Targeting"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "advertisers.bulkListAdvertiserAssignedTargetingOptions",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(advertiserId AS STRING) AS advertiserId FROM `{dataset}.DV_Advertisers`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_Targeting",
"disposition": "WRITE_APPEND"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "advertisers.lineItems.bulkListLineItemAssignedTargetingOptions",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(advertiserId AS STRING) AS advertiserId, CAST(lineItemId AS STRING) AS lineItemId FROM `{dataset}.DV_LineItems`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_Targeting",
"disposition": "WRITE_APPEND"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "targetingTypes.targetingOptions.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT SAFE_CAST(REGEXP_EXTRACT(name, r'advertisers/(\\d+)/') AS INT64) AS advertiserId, targetingType FROM `{dataset}.DV_Targeting` WHERE targetingType IN ('TARGETING_TYPE_EXCHANGE', 'TARGETING_TYPE_SUB_EXCHANGE') GROUP BY 1,2 HAVING advertiserId IS NOT NULL",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_TargetingOptions"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "partners.channels.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(partnerId AS STRING) AS partnerId FROM `{dataset}.DV_Partners`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_Channels"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "advertisers.channels.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(advertiserId AS STRING) AS advertiserId FROM `{dataset}.DV_Advertisers`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_Channels",
"disposition": "WRITE_APPEND"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "advertisers.locationLists.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(advertiserId AS STRING) AS advertiserId FROM `{dataset}.DV_Advertisers`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_LocationLists"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "advertisers.negativeKeywordLists.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(advertiserId AS STRING) AS advertiserId FROM `{dataset}.DV_Advertisers`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_NegativeKeywordLists"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "inventorySources.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(partnerId AS STRING) AS partnerId FROM `{dataset}.DV_Partners`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_InventorySources"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "inventorySourceGroups.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(partnerId AS STRING) AS partnerId FROM `{dataset}.DV_Partners`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_InventorySourceGroups"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "firstAndThirdPartyAudiences.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(partnerId AS STRING) AS partnerId, 'firstAndThirdPartyAudienceId desc' AS orderBy FROM `{dataset}.DV_Partners`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"limit": 10000,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_FirstAndThirdPartyAudiences"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "googleAudiences.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(partnerId AS STRING) AS partnerId FROM `{dataset}.DV_Partners`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_GoogleAudiences"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "combinedAudiences.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(partnerId AS STRING) AS partnerId FROM `{dataset}.DV_Partners`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_CombinedAudiences"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials to use for DV360 reads."
}
},
"api": "displayvideo",
"version": "v1",
"function": "customLists.list",
"kwargs_remote": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"query": "SELECT CAST(advertiserId AS STRING) AS advertiserId FROM `{dataset}.DV_Advertisers`;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
}
},
"iterate": true,
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"table": "DV_CustomLists"
}
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bigquery",
"kind": "authentication",
"order": 3,
"default": "service",
"description": "Credentials to use for BigQuery reads and writes."
}
},
"from": {
"query": "WITH DV_Targeting_With_Ids AS ( SELECT SAFE_CAST(REGEXP_EXTRACT(name, r'partners/(\\d+)/targetingTypes') AS INT64) AS partnerId, SAFE_CAST(REGEXP_EXTRACT(name, r'advertisers/(\\d+)/targetingTypes') AS INT64) AS advertiserId, SAFE_CAST(REGEXP_EXTRACT(name, r'lineItems/(\\d+)/targetingTypes') AS INT64) AS lineItemId, * FROM `{dataset}.DV_Targeting` ), DV_Targeting_With_Names AS ( SELECT CONCAT(P.displayName, ' - ', P.partnerId) AS Partner, NULL AS Advertiser, NULL AS LineItem, NULL AS lineItemType, NULL AS pacingType, NULL AS performanceGoalType, NULL AS targetingExpansionLevel, NULL AS excludeFirstPartyAudience, T.* FROM DV_Targeting_With_Ids AS T LEFT JOIN `{dataset}.DV_Partners` AS P ON T.partnerId=P.partnerId WHERE T.lineItemId IS NULL AND T.advertiserId IS NULL AND T.partnerId IS NOT NULL UNION ALL SELECT CONCAT(P.displayName, ' - ', P.partnerId) AS Partner, CONCAT(A.displayName, ' - ', A.advertiserId) AS Advertiser, NULL AS LineItem, NULL AS lineItemType, NULL AS pacingType, NULL AS performanceGoalType, NULL AS targetingExpansionLevel, NULL AS excludeFirstPartyAudience, T.* FROM DV_Targeting_With_Ids AS T LEFT JOIN `{dataset}.DV_Advertisers` AS A ON T.advertiserId=A.advertiserId LEFT JOIN `{dataset}.DV_Partners` AS P ON A.partnerId=P.partnerId WHERE T.lineItemId IS NULL AND T.advertiserId IS NOT NULL AND T.partnerId IS NULL UNION ALL SELECT CONCAT(P.displayName, ' - ', P.partnerId) AS Partner, CONCAT(A.displayName, ' - ', A.advertiserId) AS Advertiser, CONCAT(L.displayName, ' - ', L.lineItemId) AS LineItem, L.lineItemType AS lineItemType, L.pacing.pacingType AS pacingType, L.bidStrategy.performanceGoalAutoBid.performanceGoalType AS performanceGoalType, L.targetingExpansion.targetingExpansionLevel AS targetingExpansionLevel, L.targetingExpansion.excludeFirstPartyAudience AS excludeFirstPartyAudience, T.*, FROM DV_Targeting_With_Ids AS T LEFT JOIN `{dataset}.DV_LineItems` AS L ON T.lineItemId=L.lineItemId LEFT JOIN `{dataset}.DV_Advertisers` AS A ON L.advertiserId=A.advertiserId LEFT JOIN `{dataset}.DV_Partners` AS P ON A.partnerId=P.partnerId WHERE T.lineItemId IS NOT NULL AND T.advertiserId IS NULL AND T.partnerId IS NULL ), DV_Included_Google_Audience_Group AS ( SELECT assignedTargetingOptionId, ARRAY_AGG(CONCAT(DV_AG.displayName, ' - ', DV_AG.googleAudienceId)) AS Included_Google_Audience, FROM DV_Targeting_With_Names, UNNEST(audienceGroupDetails.includedGoogleAudienceGroup.settings) AS T_AG LEFT JOIN `{dataset}.DV_GoogleAudiences` AS DV_AG ON T_AG.googleAudienceId=DV_AG.googleAudienceId GROUP BY 1 ), DV_Excluded_Google_Audience_Group AS ( SELECT assignedTargetingOptionId, ARRAY_AGG(CONCAT(DV_AG.displayName, ' - ', DV_AG.googleAudienceId)) AS Excluded_Google_Audience, FROM DV_Targeting_With_Names, UNNEST(audienceGroupDetails.excludedGoogleAudienceGroup.settings) AS T_AG LEFT JOIN `{dataset}.DV_GoogleAudiences` AS DV_AG ON T_AG.googleAudienceId=DV_AG.googleAudienceId GROUP BY 1 ), DV_Included_Custom_List AS ( SELECT assignedTargetingOptionId, ARRAY_AGG(CONCAT(DV_AG.displayName, ' - ', DV_AG.customListId)) AS Included_Custom_List, FROM DV_Targeting_With_Names, UNNEST(audienceGroupDetails.includedCustomListGroup.settings) AS T_AG LEFT JOIN `{dataset}.DV_CustomLists` AS DV_AG ON T_AG.customListId=DV_AG.customListId GROUP BY 1 ), DV_Included_Combined_Audience AS ( SELECT assignedTargetingOptionId, ARRAY_AGG(CONCAT(DV_AG.displayName, ' - ', DV_AG.combinedAudienceId)) AS Included_Combined_Audience, FROM DV_Targeting_With_Names, UNNEST(audienceGroupDetails.includedCombinedAudienceGroup.settings) AS T_AG LEFT JOIN `{dataset}.DV_CombinedAudiences` AS DV_AG ON T_AG.combinedAudienceId=DV_AG.combinedAudienceId GROUP BY 1 ), DV_Excluded_First_And_Third_Party_Audience AS ( SELECT assignedTargetingOptionId, ARRAY_AGG(CONCAT(DV_AG.displayName, ' - ', DV_AG.firstAndThirdPartyAudienceId)) AS Excluded_First_And_Third_Party_Audience, FROM DV_Targeting_With_Ids, UNNEST(audienceGroupDetails.excludedFirstAndThirdPartyAudienceGroup.settings) AS T_AG LEFT JOIN `{dataset}.DV_FirstAndThirdPartyAudiences` AS DV_AG ON T_AG.firstAndThirdPartyAudienceId=DV_AG.firstAndThirdPartyAudienceId GROUP BY 1 ), DV_Included_First_And_Third_Party_Audience AS ( SELECT T_AO.assignedTargetingOptionId, ARRAY_AGG(CONCAT(DV_AG.displayName, ' - ', DV_AG.firstAndThirdPartyAudienceId)) AS Included_First_And_Third_Party_Audience, FROM ( SELECT assignedTargetingOptionId, ARRAY_CONCAT(T_AG.settings) AS First_And_Third_Party_Audience FROM DV_Targeting_With_Ids, UNNEST(audienceGroupDetails.includedFirstAndThirdPartyAudienceGroups) AS T_AG ) AS T_AO, UNNEST(First_And_Third_Party_Audience) AS T_AG LEFT JOIN `{dataset}.DV_FirstAndThirdPartyAudiences` AS DV_AG ON T_AG.firstAndThirdPartyAudienceId=DV_AG.firstAndThirdPartyAudienceId GROUP BY 1 ) SELECT CONCAT(C.displayName, ' - ', C.channelId) AS Channel, CONCAT(RL.displayName, ' - ', RL.LocationListId) AS Regional_Location_List, CONCAT(PL.displayName, ' - ', PL.LocationListId) AS Proximity_Location_List, CONCAT(I.displayName, ' - ', I.inventorySourceId) AS Inventory_Source, CONCAT(IG.displayName, ' - ', IG.inventorySourceGroupId) AS Inventory_Source_Group, CONCAT(NK.displayName, ' - ', NK.negativeKeywordListId) AS Negative_Keyword_List, EO.exchangeDetails.exchange AS Exchange, SEO.subExchangeDetails.displayName AS SubExchange, DV_IGAG.Included_Google_Audience, DV_EGAG.Excluded_Google_Audience, DV_ICL.Included_Custom_List, DV_ICA.Included_Combined_Audience, DV_IFTPA.Included_First_And_Third_Party_Audience, DV_EFTPA.Excluded_First_And_Third_Party_Audience, T.* FROM DV_Targeting_With_Names AS T LEFT JOIN `{dataset}.DV_Channels` AS C ON T.channelDetails.channelId=C.channelId LEFT JOIN `{dataset}.DV_LocationLists` AS RL ON T.regionalLocationListDetails.regionalLocationListId=RL.LocationListId LEFT JOIN `{dataset}.DV_LocationLists` AS PL ON T.proximityLocationListDetails.proximityLocationListId=PL.LocationListId LEFT JOIN `{dataset}.DV_InventorySources` AS I ON T.inventorySourceDetails.inventorySourceId=I.inventorySourceId LEFT JOIN `{dataset}.DV_InventorySourceGroups` AS IG ON T.inventorySourceGroupDetails.inventorySourceGroupId=IG.inventorySourceGroupId LEFT JOIN `{dataset}.DV_NegativeKeywordLists` AS NK ON T.negativeKeywordListDetails.negativeKeywordListId=NK.negativeKeywordListId LEFT JOIN `{dataset}.DV_TargetingOptions` AS EO ON T.exchangeDetails.targetingOptionId=EO.targetingOptionId LEFT JOIN `{dataset}.DV_TargetingOptions` AS SEO ON T.subExchangeDetails.targetingOptionId=SEO.targetingOptionId LEFT JOIN DV_Included_Google_Audience_Group AS DV_IGAG ON T.assignedTargetingOptionId=DV_IGAG.assignedTargetingOptionId LEFT JOIN DV_Excluded_Google_Audience_Group AS DV_EGAG ON T.assignedTargetingOptionId=DV_EGAG.assignedTargetingOptionId LEFT JOIN DV_Included_Custom_List AS DV_ICL ON T.assignedTargetingOptionId=DV_ICL.assignedTargetingOptionId LEFT JOIN DV_Included_Combined_Audience AS DV_ICA ON T.assignedTargetingOptionId=DV_ICA.assignedTargetingOptionId LEFT JOIN DV_Included_First_And_Third_Party_Audience AS DV_IFTPA ON T.assignedTargetingOptionId=DV_IFTPA.assignedTargetingOptionId LEFT JOIN DV_Excluded_First_And_Third_Party_Audience AS DV_EFTPA ON T.assignedTargetingOptionId=DV_EFTPA.assignedTargetingOptionId ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
},
"legacy": false
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 0,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
},
"view": "Targeting_Audit"
}
}
}
]Run This Workflow In Minutes On Google Cloud
Everything from a quick Google Cloud UI to reference developer code for your team in one GitHub repository.