All
donut_small
DV360 / CM360 Privacy Audit
Dashboard that shows performance metrics across browser to see the impact of privacy changes.
Impact Level
| Spend Optimization |
|
| Spend Growth |
|
| Time Savings |
|
| Account Health |
|
| Csat Improvement |
|
Value Proposition
| See drilled down metrics of your accounts performance as ITP changes |
Instructions
| Follow the instructions from this document. |
Details
| Open Source | YES |
| Age | Nov. 19, 2019 (3 years, 1 month) |
| Authors | terwilleger@google.com aallaman@google.com cmorgeson@google.com |
| Shedule Days | Configured by user. |
| Shedule Hours | 3 |
[
{
"drive": {
"auth": {
"field": {
"name": "auth_sheets",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for Sheets."
}
},
"hour": [],
"copy": {
"source": "https://docs.google.com/spreadsheets/d/1rH_PGXOYW2mVdmAYnKbv6kcaB6lQihAyMsGtFfinnqg/",
"destination": {
"field": {
"name": "recipe_name",
"prefix": "Privacy Audit ",
"kind": "string",
"order": 1,
"description": "Name of document to deploy to.",
"default": ""
}
}
}
}
},
{
"dataset": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
}
}
},
{
"dbm": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for DV360."
}
},
"report": {
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"prefix": "ITP_Audit_Browser_",
"default": "ITP_Audit_Browser_",
"order": 1,
"description": "Name of report in DV360, should be unique."
}
},
"timeout": 120,
"filters": {
"FILTER_ADVERTISER": {
"values": {
"field": {
"name": "dv360_advertiser_ids",
"kind": "integer_list",
"order": 6,
"default": [],
"description": "Optional: Comma delimited list of DV360 Advertiser ids."
}
}
},
"FILTER_PARTNER": {
"values": {
"field": {
"name": "dv360_partner_id",
"kind": "integer",
"order": 5,
"default": "",
"description": "DV360 Partner id"
}
}
}
},
"body": {
"timezoneCode": {
"field": {
"name": "recipe_timezone",
"kind": "timezone",
"description": "Timezone for report dates.",
"default": "America/Los_Angeles"
}
},
"metadata": {
"title": {
"field": {
"name": "recipe_name",
"default": "ITP_Audit_Browser_",
"kind": "string",
"prefix": "ITP_Audit_Browser_",
"order": 1,
"description": "Name of report in DV360, should be unique."
}
},
"dataRange": {
"field": {
"name": "date_range",
"kind": "choice",
"order": 3,
"default": "LAST_365_DAYS",
"choices": [
"LAST_7_DAYS",
"LAST_14_DAYS",
"LAST_30_DAYS",
"LAST_365_DAYS",
"LAST_60_DAYS",
"LAST_7_DAYS",
"LAST_90_DAYS",
"MONTH_TO_DATE",
"PREVIOUS_MONTH",
"PREVIOUS_QUARTER",
"PREVIOUS_WEEK",
"PREVIOUS_YEAR",
"QUARTER_TO_DATE",
"WEEK_TO_DATE",
"YEAR_TO_DATE"
],
"description": "Timeframe to run the ITP report for."
}
},
"format": "CSV"
},
"params": {
"type": "TYPE_GENERAL",
"groupBys": [
"FILTER_ADVERTISER",
"FILTER_ADVERTISER_NAME",
"FILTER_ADVERTISER_CURRENCY",
"FILTER_MEDIA_PLAN",
"FILTER_MEDIA_PLAN_NAME",
"FILTER_CAMPAIGN_DAILY_FREQUENCY",
"FILTER_INSERTION_ORDER",
"FILTER_INSERTION_ORDER_NAME",
"FILTER_LINE_ITEM",
"FILTER_LINE_ITEM_NAME",
"FILTER_PAGE_LAYOUT",
"FILTER_WEEK",
"FILTER_MONTH",
"FILTER_YEAR",
"FILTER_PARTNER",
"FILTER_PARTNER_NAME",
"FILTER_LINE_ITEM_TYPE",
"FILTER_DEVICE_TYPE",
"FILTER_BROWSER",
"FILTER_ANONYMOUS_INVENTORY_MODELING",
"FILTER_OS"
],
"metrics": [
"METRIC_MEDIA_COST_ADVERTISER",
"METRIC_IMPRESSIONS",
"METRIC_CLICKS",
"METRIC_TOTAL_CONVERSIONS",
"METRIC_LAST_CLICKS",
"METRIC_LAST_IMPRESSIONS",
"METRIC_CM_POST_CLICK_REVENUE",
"METRIC_CM_POST_VIEW_REVENUE",
"METRIC_REVENUE_ADVERTISER"
]
},
"schedule": {
"frequency": "ONE_TIME"
}
},
"schedule": {
"frequency": "ONE_TIME"
}
},
"delete": true
}
},
{
"dbm": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for DV360."
}
},
"report": {
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"prefix": "ITP_Audit_Audience_",
"default": "ITP_Audit_Audience_",
"order": 1,
"description": "Name of report in DV360, should be unique."
}
},
"timeout": 90,
"filters": {
"FILTER_ADVERTISER": {
"values": {
"field": {
"name": "dv360_advertiser_ids",
"kind": "integer_list",
"order": 6,
"default": [],
"description": "Optional: Comma delimited list of DV360 Advertiser ids."
}
}
},
"FILTER_PARTNER": {
"values": {
"field": {
"name": "dv360_partner_id",
"kind": "integer",
"order": 5,
"default": "",
"description": "DV360 Partner id"
}
}
}
},
"body": {
"timezoneCode": {
"field": {
"name": "recipe_timezone",
"kind": "timezone",
"description": "Timezone for report dates.",
"default": "America/Los_Angeles"
}
},
"metadata": {
"title": {
"field": {
"name": "recipe_name",
"kind": "string",
"prefix": "ITP_Audit_Audience_",
"default": "ITP_Audit_Audience_",
"order": 1,
"description": "Name of report in DV360, should be unique."
}
},
"dataRange": "LAST_90_DAYS",
"format": "CSV"
},
"params": {
"type": "TYPE_GENERAL",
"groupBys": [
"FILTER_DATE",
"FILTER_ADVERTISER_CURRENCY",
"FILTER_LINE_ITEM_NAME",
"FILTER_LINE_ITEM",
"FILTER_USER_LIST",
"FILTER_USER_LIST_TYPE"
],
"metrics": [
"METRIC_IMPRESSIONS",
"METRIC_BILLABLE_IMPRESSIONS",
"METRIC_CLICKS",
"METRIC_CTR",
"METRIC_TOTAL_CONVERSIONS",
"METRIC_LAST_CLICKS",
"METRIC_LAST_IMPRESSIONS",
"METRIC_REVENUE_ADVERTISER",
"METRIC_CM360_POST_VIEW_REVENUE",
"METRIC_CM360_POST_CLICK_REVENUE"
],
"options": {
"includeOnlyTargetedUserLists": false
}
},
"schedule": {
"frequency": "DAILY"
}
}
},
"delete": false
}
},
{
"dcm": {
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for CM."
}
},
"timeout": 90,
"report": {
"timeout": 90,
"account": {
"field": {
"name": "cm_account_id",
"kind": "string",
"order": 2,
"default": "",
"description": "Campaign Manager Account Id."
}
},
"filters": {
"advertiser": {
"values": {
"field": {
"name": "cm_advertiser_ids",
"kind": "integer_list",
"order": 3,
"default": [],
"description": "Optional: Comma delimited list of CM advertiser ids."
}
}
}
},
"body": {
"kind": "dfareporting#report",
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"order": 1,
"prefix": "ITP_Audit_Browser_",
"default": "ITP_Audit_Dashboard_Browser",
"description": "Name of the Campaign Manager browser report."
}
},
"format": "CSV",
"type": "STANDARD",
"criteria": {
"dateRange": {
"kind": "dfareporting#dateRange",
"relativeDateRange": {
"field": {
"name": "date_range",
"kind": "choice",
"order": 3,
"default": "LAST_365_DAYS",
"choices": [
"LAST_7_DAYS",
"LAST_14_DAYS",
"LAST_30_DAYS",
"LAST_365_DAYS",
"LAST_60_DAYS",
"LAST_7_DAYS",
"LAST_90_DAYS",
"MONTH_TO_DATE",
"PREVIOUS_MONTH",
"PREVIOUS_QUARTER",
"PREVIOUS_WEEK",
"PREVIOUS_YEAR",
"QUARTER_TO_DATE",
"WEEK_TO_DATE",
"YEAR_TO_DATE"
],
"description": "Timeframe to run the ITP report for."
}
}
},
"dimensions": [
{
"kind": "dfareporting#sortedDimension",
"name": "campaign"
},
{
"kind": "dfareporting#sortedDimension",
"name": "campaignId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "site"
},
{
"kind": "dfareporting#sortedDimension",
"name": "advertiser"
},
{
"kind": "dfareporting#sortedDimension",
"name": "advertiserId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "browserPlatform"
},
{
"kind": "dfareporting#sortedDimension",
"name": "platformType"
},
{
"kind": "dfareporting#sortedDimension",
"name": "month"
},
{
"kind": "dfareporting#sortedDimension",
"name": "week"
}
],
"metricNames": [
"impressions",
"clicks",
"totalConversions",
"activityViewThroughConversions",
"activityClickThroughConversions"
],
"dimensionFilters": []
},
"schedule": {
"active": false,
"repeats": "WEEKLY",
"repeatsOnWeekDays": [
"MONDAY"
],
"every": 1
},
"delivery": {
"emailOwner": false
}
},
"delete": true
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"from": {
"values": [
[
"App",
"App"
],
[
"Web optimized for device",
"Web"
],
[
"Web not optimized for device",
"Web"
]
]
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_Environment"
},
"schema": [
{
"name": "Environment",
"type": "STRING"
},
{
"name": "Environment_clean",
"type": "STRING"
}
]
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"from": {
"values": [
[
"Other",
"TrueView",
""
],
[
"Opera",
"Other",
""
],
[
"Google Chrome",
"Chrome/Android",
""
],
[
"Android Webkit",
"Chrome/Android",
""
],
[
"Safari",
"Safari/iOS",
""
],
[
"Safari 10",
"Safari/iOS",
""
],
[
"Safari 11",
"Safari/iOS",
""
],
[
"Safari 6",
"Safari/iOS",
""
],
[
"Safari 8",
"Safari/iOS",
""
],
[
"Safari 9",
"Safari/iOS",
""
],
[
"Safari 12",
"Safari/iOS",
"Includes Safari mobile web and webkit, both re v12"
],
[
"Safari 13",
"Safari/iOS",
""
],
[
"Safari 12+13",
"Safari/iOS",
""
],
[
"Safari 14",
"Safari/iOS",
""
],
[
"Safari 7",
"Safari/iOS",
""
],
[
"Safari 5",
"Safari/iOS",
""
],
[
"Safari 4",
"Safari/iOS",
""
],
[
"Safari 3",
"Safari/iOS",
""
],
[
"Firefox",
"Firefox",
""
],
[
"Microsoft Edge",
"IE/Edge",
""
],
[
"Internet Explorer 11",
"IE/Edge",
""
],
[
"Internet Explorer 10",
"IE/Edge",
""
],
[
"Internet Explorer 9",
"IE/Edge",
"",
""
],
[
"Internet Explorer 8",
"IE/Edge",
""
]
]
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_Browser"
},
"schema": [
{
"name": "Browser_Platform",
"type": "STRING"
},
{
"name": "Browser_Platform_clean",
"type": "STRING"
},
{
"name": "Browser_Platform_detail",
"type": "STRING"
}
]
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"from": {
"values": [
[
"Firefox",
"Firefox",
"Firefox"
],
[
"Mozilla",
"Firefox",
"Firefox"
],
[
"Microsoft Edge",
"IE/Edge",
"IE/Edge"
],
[
"Microsoft Internet Explorer",
"IE/Edge",
"IE/Edge"
],
[
"Netscape Navigator",
"Other",
"Other"
],
[
"Opera",
"Other",
"Other"
],
[
"Opera Next",
"Other",
"Other"
],
[
"Roku",
"Other",
"Other"
],
[
"Yandex",
"Other",
"Other"
],
[
"Android",
"Chrome/Android",
"Android"
],
[
"Chrome",
"Chrome/Android",
"Chrome"
],
[
"iPad",
"Safari/iOS",
"iDevice"
],
[
"iPhone / iPod touch",
"Safari/iOS",
"iDevice"
],
[
"Safari",
"Safari/iOS",
"Safari"
],
[
"Unknown",
"Unknown",
"Unknown"
]
]
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_CM_Browser_lookup"
},
"schema": [
{
"name": "Browser_Platform",
"type": "STRING"
},
{
"name": "Browser_Platform_clean",
"type": "STRING"
},
{
"name": "Browser_Platform_detail",
"type": "STRING"
}
]
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"from": {
"values": [
[
"Desktop",
"Desktop"
],
[
"Smart Phone",
"Mobile"
],
[
"Tablet",
"Mobile"
],
[
"Connected TV",
"Connected TV"
]
]
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_Device_Type"
},
"schema": [
{
"name": "Device_Type",
"type": "STRING"
},
{
"name": "Device",
"type": "STRING"
}
]
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"from": {
"values": [
[
"View-through",
"Attributed"
],
[
"Attributed",
"Attributed"
],
[
"Unattributed",
"Unattributed"
],
[
"Click-through",
"Unattributed"
]
]
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_Floodlight_Attribution"
},
"schema": [
{
"name": "Floodlight_Attribution_Type",
"type": "STRING"
},
{
"name": "Attribution_Type",
"type": "STRING"
}
]
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for DV360."
}
},
"api": "displayvideo",
"version": "v1",
"function": "firstAndThirdPartyAudiences.list",
"kwargs": {
"partnerId": {
"field": {
"name": "dv360_partner_id",
"kind": "integer",
"order": 5,
"default": "",
"description": "DV360 Partner id"
}
}
},
"results": {
"bigquery": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_dv_audiences"
}
}
}
},
{
"dbm": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for DV360."
}
},
"report": {
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"prefix": "ITP_Audit_Browser_",
"default": "ITP_Audit_Browser_",
"order": 1,
"description": "Name of report in DV360, should be unique."
}
}
},
"out": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_Dv360_Browser_Report_Dirty",
"header": true,
"schema": [
{
"name": "Advertiser_Id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Advertiser",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Advertiser_Currency",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Campaign_Id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Campaign",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Insertion_Order_Daily_Frequency",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Insertion_Order_Id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Insertion_Order",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Line_Item_Id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Line_Item",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Environment",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Week",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Month",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Year",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Partner_Id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Partner",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Line_Item_Type",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Device_Type",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Browser",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Anonymous_Inventory_Modeling",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Operating_System",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Media_Cost_Advertiser_Currency",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Impressions",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Clicks",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Total_Conversions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Post_Click_Conversions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Post_View_Conversions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Cm_Post_Click_Revenue",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Cm_Post_View_Revenue",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Revenue_Adv_Currency",
"type": "FLOAT",
"mode": "NULLABLE"
}
]
}
}
}
},
{
"dcm": {
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for CM."
}
},
"report": {
"account": {
"field": {
"name": "cm_account_id",
"kind": "string",
"order": 2,
"default": "",
"description": "Campaign Manager Account Id."
}
},
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"order": 1,
"prefix": "ITP_Audit_Browser_",
"default": "ITP_Audit_Dashboard_Browser",
"description": "Name of the Campaign Manager browser report."
}
}
},
"out": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_CM_Browser_Report_Dirty",
"header": true,
"is_incremental_load": false
}
}
}
},
{
"dbm": {
"auth": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for DV360."
}
},
"report": {
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"prefix": "ITP_Audit_Audience_",
"default": "ITP_Audit_Audience_",
"order": 1,
"description": "Name of report in DV360, should be unique."
}
}
},
"out": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"table": "z_Dv_Audience_Report",
"header": true,
"schema": [
{
"name": "Advertiser_Currency",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Line_Item",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Line_Item_Id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Audience_List_Id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Audience_List_Type",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "ReportDate",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Impressions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Billable_Impressions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Clicks",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "CTR",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Total_Conversions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Post_Click_Conversions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Post_View_Conversions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Revenue_Adv_Currency",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Cm_Post_View_Revenue",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Cm_Post_Click_Revenue",
"type": "FLOAT",
"mode": "NULLABLE"
}
]
}
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"run": {
"legacy": false,
"query": "CREATE OR REPLACE TABLE `{dataset}.DV_Audiences` AS with dv_entities as ( SELECT distinct b.Advertiser, b.Advertiser_Id, b.Campaign, b.Campaign_Id, b.Insertion_Order, b.Insertion_Order_Id, b.Line_Item_Id FROM `{dataset}.z_Dv360_Browser_Report_Dirty` AS b ) SELECT case when a.firstAndThirdPartyAudienceType = 'FIRST_AND_THIRD_PARTY_AUDIENCE_TYPE_UNSPECIFIED' then 'N/A' when a.firstAndThirdPartyAudienceType is null then 'N/A' when a.firstAndThirdPartyAudienceType = 'FIRST_AND_THIRD_PARTY_AUDIENCE_TYPE_FIRST_PARTY' then 'First Party' when a.firstAndThirdPartyAudienceType = 'FIRST_AND_THIRD_PARTY_AUDIENCE_TYPE_THIRD_PARTY' then 'Third Party' end as firstAndThirdPartyAudienceType, CASE WHEN a.audienceType = 'AUDIENCE_TYPE_UNSPECIFIED' THEN 'N/A' WHEN a.audienceType is null THEN 'N/A' WHEN a.audienceType = 'CUSTOMER_MATCH_CONTACT_INFO' THEN 'CustomerMatch ContactInfo' WHEN a.audienceType = 'CUSTOMER_MATCH_DEVICE_ID' THEN 'CustomerMatch DeviceId' WHEN a.audienceType = 'CUSTOMER_MATCH_USER_ID' THEN 'CustomerMatch UserId' WHEN a.audienceType = 'ACTIVITY_BASED' THEN 'Activity Based' WHEN a.audienceType = 'FREQUENCY_CAP' THEN 'Frequency Cap' WHEN a.audienceType = 'TAG_BASED' THEN 'Tag Based' WHEN a.audienceType = 'YOUTUBE_USERS' THEN 'YouTube Users' WHEN a.audienceType = 'LICENSED' THEN 'Licensed' ELSE a.audienceType END AS audienceType, b.Advertiser, b.Advertiser_Id, b.Campaign, b.Campaign_Id, b.Insertion_Order, b.Insertion_Order_Id, r.Line_Item, r.Line_Item_Id, r.Audience_List_Id, r.Date, r.Impressions, r.Clicks, r.CTR, r.Total_Conversions, r.Post_Click_Conversions, r.Post_View_Conversions, r.Revenue_Adv_Currency, r.Cm_Post_View_Revenue, r.Cm_Post_Click_Revenue FROM `{dataset}.z_Dv_Audience_Report` AS r LEFT JOIN `{dataset}.z_dv_audiences` AS a ON r.Audience_List_Id = a.firstAndThirdPartyAudienceId LEFT JOIN dv_entities AS b ON b.Line_Item_Id = r.Line_Item_Id;",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
}
}
}
}
},
{
"itp_audit": {
"auth_dv": {
"field": {
"name": "auth_dv",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for DV360."
}
},
"auth_cm": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for CM."
}
},
"auth_sheets": {
"field": {
"name": "auth_sheets",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for Sheets."
}
},
"auth_bq": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for BigQuery."
}
},
"account": {
"field": {
"name": "cm_account_id",
"kind": "string",
"order": 2,
"default": "",
"description": "Campaign Manager Account Id."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"sheet": {
"field": {
"name": "recipe_name",
"prefix": "Privacy Audit ",
"kind": "string",
"order": 7,
"description": "Name of document to deploy to.",
"default": ""
}
},
"timeout": 120,
"read": {
"advertiser_ids": {
"single_cell": true,
"bigquery": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
},
"query": "select distinct Advertiser_Id from `{dataset}.z_CM_Browser_Report_Dirty`",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 7,
"default": "ITP_Audit_Dashboard",
"description": "BigQuery dataset for store dashboard tables."
}
}
},
"legacy": false
}
}
},
"floodlightConfigIds": {
"field": {
"name": "floodlight_configuration_ids",
"kind": "integer_list",
"order": 2,
"default": [],
"description": "Comma delimited list of floodlight configuration ids for the Campaign Manager floodlight report."
}
},
"reportPrefix": {
"field": {
"name": "recipe_name",
"kind": "string",
"prefix": "ITP_Audit_Floodlight_",
"order": 7,
"description": "Name of report in DBM, should be unique."
}
}
}
}
]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.