{
"log":{ "bigquery":{ "auth":"service", "dataset":"DV360_Dataset", "table":"BQFlow", "disposition":"WRITE_TRUNCATE" }},
"tasks": [
{ "dataset": {
"description":"Create PUB dataset.",
"auth": "service",
"dataset": "DV360_Dataset"
}},
{ "google_api": {
"description":"Pull PUB partners.",
"api": "displayvideo",
"version": "v2",
"auth": "service",
"function": "partners.get",
"iterate": false,
"kwargs_remote": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"parameters": {
"partners": "[1487333]"
},
"query": "SELECT CAST(partnerId AS STRING) partnerId FROM (SELECT DISTINCT * FROM UNNEST({partners}) AS partnerId)"
}
},
"results": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"table": "DV360_Partners"
}
}
}},
{ "google_api": {
"description":"Pull PUB advertiser.",
"api": "displayvideo",
"version": "v2",
"auth": "service",
"function": "advertisers.list",
"iterate": true,
"kwargs_remote": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"query": "SELECT DISTINCT CAST(partnerId AS STRING) partnerId FROM `DV360_Partners`"
}
},
"results": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"table": "DV360_Advertisers"
}
}
}},
{ "google_api": {
"description":"Pull PUB insertion orders for dashboard.",
"api": "displayvideo",
"version": "v2",
"auth": "service",
"function": "advertisers.insertionOrders.list",
"iterate": true,
"kwargs_remote": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"query": "SELECT DISTINCT CAST(advertiserId AS STRING) advertiserId FROM `DV360_Advertisers`"
}
},
"results": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"table": "DV360_InsertionOrders"
}
}
}},
{ "google_api": {
"description":"Pull PUB campaigns for dashboard.",
"api": "displayvideo",
"version": "v2",
"auth": "service",
"function": "advertisers.campaigns.list",
"iterate": true,
"kwargs_remote": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"query": "SELECT DISTINCT CAST(advertiserId AS STRING) advertiserId FROM `DV360_Advertisers`"
}
},
"results": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"table": "DV360_Campaigns"
}
}
}},
{ "google_api": {
"description":"Pull PUB line items.",
"api": "displayvideo",
"version": "v2",
"auth": "service",
"function": "advertisers.lineItems.list",
"iterate": true,
"kwargs_remote": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"query": "SELECT DISTINCT CAST(advertiserId AS STRING) AS advertiserId FROM `DV360_Advertisers`"
}
},
"results": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"table": "DV360_LineItems"
}
}
}},
{ "google_api": {
"description":"Pull PUB creatives.",
"api": "displayvideo",
"version": "v2",
"auth": "service",
"function": "advertisers.creatives.list",
"iterate": true,
"kwargs_remote": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"query":"SELECT DISTINCT CAST(advertiserId AS STRING) AS advertiserId,
'creatives.updateTime,creatives.advertiserId,creatives.creativeId,creatives.displayName,creatives.creativeType,creatives.reviewStatus,creatives.entityStatus,nextPageToken' AS fields,
FORMAT('updateTime>=\"%s\"', REGEXP_REPLACE(COALESCE(C.updateTime, FORMAT_TIMESTAMP('%FT%H:%M:%SZ', TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY), 'UTC')), r'\\.\\d+Z', 'Z')) AS filter,
'creativeId desc' AS orderBy,
200 AS pageSize
FROM `DV360_Dataset.DV360_Advertisers` AS A
LEFT JOIN (SELECT advertiserId, MAX(updateTime) AS updateTime FROM `DV360_Dataset.DV360_Creatives` GROUP BY 1) AS C
USING(advertiserId)"
}
},
"results": {
"bigquery": {
"auth": "service",
"dataset": "DV360_Dataset",
"table": "DV360_Creatives",
"merge":["creativeId"]
}
}
}},
{ "bigquery":{
"description":"Create PUB patch view.",
"auth":"service",
"from":{
"query":"
WITH LINEITEM_PRISMA AS (
SELECT
advertiserId,
lineItemId,
displayName AS lineItemName,
REGEXP_EXTRACT(displayName, r'\\(.*?\\)') AS prismaId
FROM `DV360_Dataset.DV360_LineItems`
),
CREATIVE_PRISMA AS (
SELECT
creativeId,
displayName AS creativeName,
REGEXP_EXTRACT(displayName, r'\\(.*?\\)') AS prismaId
FROM `DV360_Dataset.DV360_Creatives`
),
NEW_APPENDS AS (
SELECT
advertiserId,
lineItemId,
ARRAY_AGG(creativeId IGNORE NULLS ORDER BY creativeId) AS creativeIds,
ARRAY_TO_STRING(ARRAY_AGG(CAST(creativeId AS STRING) IGNORE NULLS ORDER BY creativeId), '') AS new_fingerprint
FROM LINEITEM_PRISMA LP
LEFT JOIN CREATIVE_PRISMA CP
USING(prismaId)
GROUP BY 1,2
),
EXISTING_APPENDS AS (
SELECT
advertiserId,
lineItemId,
ARRAY_TO_STRING(ARRAY_AGG(CAST(creativeIds AS STRING) IGNORE NULLS ORDER BY creativeIds), '') AS existing_fingerprint
FROM `DV360_Dataset.DV360_LineItems`, UNNEST(creativeIds) AS creativeIds
GROUP BY 1,2
)
SELECT
CAST(A.advertiserId AS STRING) AS advertiserId,
CAST(A.lineItemId AS STRING) AS lineItemId,
STRUCT(
A.creativeIds AS creativeIds
) AS body,
'lineItem.creativeIds' AS updateMask
FROM NEW_APPENDS AS A
LEFT JOIN EXISTING_APPENDS AS E
USING(lineItemId)
WHERE new_fingerprint != existing_fingerprint
"
},
"to":{
"dataset":"DV360_Dataset",
"view":"API_Patch"
}
}},
{ "bigquery":{
"description":"Create PUB dashboard view.",
"auth":"service",
"from":{
"query":"
WITH LINEITEM_PRISMA AS (
SELECT
STRUCT (
advertiserId,
insertionorderId,
campaignId,
CAST(C AS STRING) AS creativeId
) AS Joinables,
STRUCT(
CONCAT (displayName, ' - ', lineItemId) AS Name,
updateTime AS Update_Time,
REGEXP_EXTRACT(displayName, r'\\(P.*?\\)') AS Map_Id,
REPLACE(IFNULL(lineItemType, 'UNSPECIFIED'), 'LINE_ITEM_TYPE_', '') AS Type,
REPLACE(IFNULL(entityStatus, 'UNSPECIFIED'), 'ENTITY_STATUS_', '') AS Status
) AS LineItem
FROM `DV360_Dataset.DV360_LineItems`, UNNEST(creativeIds) AS C
),
CREATIVE_PRISMA AS (
SELECT
STRUCT (
advertiserId,
CAST(creativeId AS STRING) AS creativeId
) AS Joinables,
STRUCT (
CONCAT (displayName, ' - ', creativeId) AS Name,
updateTime AS Update_Time,
REGEXP_EXTRACT(displayName, r'\\(P.*?\\)') AS Map_Id,
REPLACE(IFNULL(creativeType, 'UNSPECIFIED'), 'CREATIVE_TYPE_', '') AS Type,
STRUCT (
REPLACE(IFNULL(reviewStatus.approvalStatus, 'UNSPECIFIED'), 'APPROVAL_STATUS_', '') AS Approval,
REPLACE(IFNULL(reviewStatus.contentAndPolicyReviewStatus, 'UNSPECIFIED'), 'REVIEW_STATUS_', '') AS Content,
REPLACE(IFNULL(reviewStatus.creativeAndLandingPageReviewStatus, 'UNSPECIFIED'), 'REVIEW_STATUS_', '') AS Landing_Page,
ARRAY((SELECT AS STRUCT exchange, REPLACE(IFNULL(status, 'UNSPECIFIED'), 'REVIEW_STATUS_', '') AS status FROM UNNEST(reviewStatus.exchangeReviewStatuses))) AS Exchange,
ARRAY((SELECT AS STRUCT publisherName, REPLACE(IFNULL(status, 'UNSPECIFIED'), 'REVIEW_STATUS_', '') AS status FROM UNNEST(reviewStatus.publisherReviewStatuses))) AS Publisher
) AS Review,
REPLACE(IFNULL(entityStatus, 'UNSPECIFIED'), 'ENTITY_STATUS_', '') AS Status
) AS Creative
FROM `DV360_Dataset.DV360_Creatives`
)
SELECT
CONCAT(P.displayName, ' - ', P.partnerId) AS Partner,
CONCAT(A.displayName, ' - ', A.advertiserId) AS Advertiser,
CONCAT(I.displayName, ' - ', I.insertionOrderId) AS InsertionOrder,
CONCAT(Z.displayName, ' - ', Z.campaignId) AS Campaign,
L.LineItem,
C.Creative,
CASE
WHEN L.LineItem.Name IS NOT NULL AND C.Creative.Name IS NOT NULL THEN 'MAPPED'
WHEN L.LineItem.Name IS NULL AND C.Creative.Name IS NOT NULL THEN 'UNMAPPED CREATIVE'
WHEN L.LineItem.Name IS NOT NULL AND C.Creative.Name IS NULL THEN 'UNMAPPED LINEITEM'
ELSE 'IMPOSSIBLE'
END AS Mapping_Status,
CASE
WHEN L.LineItem.Map_Id IS NULL AND C.Creative.Map_Id IS NULL THEN NULL /* BOTH BLANK */
WHEN L.LineItem.Map_Id IS NULL AND C.Creative.Name IS NOT NULL THEN C.Creative.Map_Id /* L BLANK */
WHEN L.LineItem.Map_Id IS NULL AND C.Creative.Name IS NOT NULL THEN L.LineItem.Map_Id /* C BLANK */
WHEN L.LineItem.Map_Id = C.Creative.Map_Id THEN L.LineItem.Map_Id /* NOT BLANK AND EQUAL */
ELSE CONCAT(L.LineItem.Map_Id, ' - ', C.Creative.Map_Id) /* NOT BLANK AND NOT EQUAL */
END AS Mapping_ID
FROM LINEITEM_PRISMA AS L
FULL OUTER JOIN CREATIVE_PRISMA AS C
ON L.Joinables.creativeId=C.Joinables.creativeId
LEFT JOIN `DV360_Dataset.DV360_Advertisers` AS A
ON COALESCE(L.Joinables.advertiserId, C.Joinables.advertiserId)=A.advertiserId
LEFT JOIN `DV360_Dataset.DV360_Partners` AS P
ON A.partnerId=P.partnerId
LEFT JOIN `DV360_Dataset.DV360_InsertionOrders` AS I
ON L.Joinables.insertionOrderId=I.insertionOrderId
LEFT JOIN `DV360_Dataset.DV360_Campaigns` AS Z
ON L.Joinables.campaignId=Z.campaignId
"
},
"to":{
"dataset":"DV360_Dataset",
"view":"Dashboard"
}
}}
]
}
{ "google_api": {
"description":"Write creative append patches back into DV360.",
"api": "displayvideo",
"version": "v2",
"auth": "service",
"function": "advertisers.lineItems.patch",
"iterate": true,
"kwargs_remote": {
"bigquery": {
"auth": "service",
"dataset": "pub_dv360",
"query": "SELECT * FROM `DV360_Dataset.API_Patch`"
}
},
}}