All
whatshot
DV360 Deal Finder
Compares open vs. deal CPM, CPC, and CPA so that clients can decide which sites, inventory, and deals work best.
Impact Level
| Spend Optimization |
|
| Spend Growth |
|
| Time Savings |
|
| Account Health |
|
| Csat Improvement |
|
Value Proposition
| Move client towards more deals. |
| Help client decide which inventory to invest in. |
| Optimize client spend to improve CPM, CPC, or CPA performance. |
| Justify deal spend. |
| Identify poorly performing deals. |
Instructions
| Wait for BigQuery->->->Deal_Finder_Dashboard to be created. |
| Join the StarThinker Assets Group to access the following assets |
| Copy Deal Finder Sample Data. |
| Click Edit Connection, and change to BigQuery->StarThinker Data->->Deal_Finder_Dashboard. |
| Copy Deal Finder Sample Report. |
| When prompted choose the new data source you just created. |
| Or give these intructions to the client. |
Details
| Open Source | YES |
| Age | Nov. 22, 2017 (5 years, 1 month) |
| Authors | kenjora@google.com |
| Shedule Days | Configured by user. |
| Shedule Hours | 3, 4 |
[
{
"dataset": {
"description": "Create a dataset for bigquery tables.",
"hour": [
4
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be created in BigQuery."
}
}
}
},
{
"dbm": {
"description": "Create a DV360 report.",
"hour": [
3
],
"auth": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for reading data."
}
},
"report": {
"filters": {
"FILTER_PARTNER": {
"values": {
"field": {
"name": "partners",
"kind": "integer_list",
"order": 5,
"default": [],
"description": "DV360 partner id."
}
}
},
"FILTER_ADVERTISER": {
"values": {
"field": {
"name": "advertisers",
"kind": "integer_list",
"order": 6,
"default": [],
"description": "Comma delimited list of DV360 advertiser ids."
}
}
}
},
"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": "Deal Finder For ",
"description": "Name of report in DV360, should be unique."
}
},
"dataRange": "LAST_30_DAYS",
"format": "CSV"
},
"params": {
"type": "TYPE_CROSS_PARTNER",
"groupBys": [
"FILTER_PARTNER_NAME",
"FILTER_PARTNER",
"FILTER_ADVERTISER_NAME",
"FILTER_ADVERTISER",
"FILTER_APP_URL",
"FILTER_SITE_ID",
"FILTER_INVENTORY_SOURCE_NAME",
"FILTER_INVENTORY_SOURCE",
"FILTER_INVENTORY_SOURCE_TYPE",
"FILTER_ADVERTISER_CURRENCY",
"FILTER_CREATIVE_WIDTH",
"FILTER_CREATIVE_HEIGHT",
"FILTER_CREATIVE_TYPE"
],
"metrics": [
"METRIC_IMPRESSIONS",
"METRIC_CLICKS",
"METRIC_TOTAL_CONVERSIONS",
"METRIC_TOTAL_MEDIA_COST_ADVERTISER",
"METRIC_REVENUE_ADVERTISER",
"METRIC_ACTIVE_VIEW_MEASURABLE_IMPRESSIONS",
"METRIC_ACTIVE_VIEW_VIEWABLE_IMPRESSIONS"
]
}
}
}
}
},
{
"dbm": {
"description": "Copy a DV360 report to BigQuery.",
"hour": [
4
],
"auth": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 1,
"default": "user",
"description": "Credentials used for reading data."
}
},
"report": {
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"prefix": "Deal Finder For ",
"description": "Name of report in DV360, should be unique."
}
},
"timeout": 10
},
"out": {
"bigquery": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"table": "Deal_Finder_DV360_Report",
"header": true,
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Partner_ID",
"type": "INTEGER"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Advertiser_ID",
"type": "INTEGER"
},
{
"name": "Site",
"type": "STRING"
},
{
"name": "Site_ID",
"type": "INTEGER"
},
{
"name": "Inventory",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Inventory_ID",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "Inventory_Type",
"type": "STRING"
},
{
"name": "Advertiser_Currency",
"type": "STRING"
},
{
"name": "Creative_Width",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Creative_Height",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Creative_Type",
"type": "STRING"
},
{
"name": "Impressions",
"type": "INTEGER"
},
{
"name": "Clicks",
"type": "INTEGER"
},
{
"name": "Conversions",
"type": "FLOAT"
},
{
"name": "Cost",
"type": "FLOAT"
},
{
"name": "Revenue",
"type": "FLOAT"
},
{
"name": "AV_Impressions_Measurable",
"type": "INTEGER"
},
{
"name": "AV_Impressions_Viewable",
"type": "INTEGER"
}
]
}
}
}
},
{
"bigquery": {
"description": "The logic query for Deal Finder, transforms report into view used by datastudio.",
"hour": [
4
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"query": "SELECT Partner, Partner_ID, Advertiser, Advertiser_ID, Site, Site_ID, Inventory, Inventory_Type, Creative_Type, Creative_Size, Always_On, Deal_Impressions, Open_Impressions, Rank_Impressions, Deal_Clicks, Open_Clicks, Rank_Clicks, Deal_Conversions, Open_Conversions, Rank_Conversions, Deal_Impressions_Viewable, Open_Impressions_Viewable, Rank_Impressions_Viewable, Deal_Impressions_Measurable, Open_Impressions_Measurable, Rank_Impressions_Measurable, Deal_Cost, Open_Cost, Rank_Cost, FROM ( SELECT FIRST(Partner) AS Partner, FIRST(Partner_ID) AS Partner_ID, FIRST(Advertiser) AS Advertiser, Advertiser_ID, First(Site) AS Site, Site_ID, Inventory, Inventory_Type, Creative_Type, Creative_Width + ' x ' + Creative_Height AS Creative_Size, IF (LEFT(Inventory, 5) == 'AO - ', True, False) AS Always_On, SUM(Deal_Impressions) AS Deal_Impressions, SUM(Open_Impressions) AS Open_Impressions, SUM(Open_Impressions) + SUM(Deal_Impressions) AS Total_Impressions, ROW_NUMBER() OVER (PARTITION BY Advertiser_ID ORDER BY Total_Impressions DESC) AS Rank_Impressions, SUM(Deal_Clicks) AS Deal_Clicks, SUM(Open_Clicks) AS Open_Clicks, SUM(Open_Clicks) + SUM(Deal_Clicks) AS Total_Clicks, ROW_NUMBER() OVER (PARTITION BY Advertiser_ID ORDER BY Total_Clicks DESC) AS Rank_Clicks, SUM(Deal_Conversions) AS Deal_Conversions, SUM(Open_Conversions) AS Open_Conversions, SUM(Open_Conversions) + SUM(Deal_Conversions) AS Total_Conversions, ROW_NUMBER() OVER (PARTITION BY Advertiser_ID ORDER BY Total_Conversions DESC) AS Rank_Conversions, SUM(Deal_Cost) AS Deal_Cost, SUM(Open_Cost) AS Open_Cost, SUM(Open_Cost) + SUM(Deal_Cost) AS Total_Cost, RANK() OVER (PARTITION BY Advertiser_ID ORDER BY Total_Cost DESC) AS Rank_Cost, SUM(Deal_Impressions_Viewable) AS Deal_Impressions_Viewable, SUM(Open_Impressions_Viewable) AS Open_Impressions_Viewable, SUM(Open_Impressions_Viewable) + SUM(Deal_Impressions_Viewable) AS Total_Impressions_Viewable, ROW_NUMBER() OVER (PARTITION BY Advertiser_ID ORDER BY Total_Impressions_Viewable DESC) AS Rank_Impressions_Viewable, SUM(Deal_Impressions_Measurable) AS Deal_Impressions_Measurable, SUM(Open_Impressions_Measurable) AS Open_Impressions_Measurable, SUM(Open_Impressions_Measurable) + SUM(Deal_Impressions_Measurable) AS Total_Impressions_Measurable, ROW_NUMBER() OVER (PARTITION BY Advertiser_ID ORDER BY Total_Impressions_Measurable DESC) AS Rank_Impressions_Measurable, FROM ( SELECT Partner, Partner_ID, Advertiser, Advertiser_ID, Site, Site_ID, Inventory, Inventory_Type, Creative_Type, Creative_Width, Creative_Height, IF(Inventory_ID IS NULL, Impressions, 0) AS Open_Impressions, IF(Inventory_ID IS NULL, 0, Impressions) AS Deal_Impressions, IF(Inventory_ID IS NULL, Clicks, 0) AS Open_Clicks, IF(Inventory_ID IS NULL, 0, Clicks) AS Deal_Clicks, IF(Inventory_ID IS NULL, Conversions, 0) AS Open_Conversions, IF(Inventory_ID IS NULL, 0, Conversions) AS Deal_Conversions, IF(Inventory_ID IS NULL, Cost, 0) AS Open_Cost, IF(Inventory_ID IS NULL, 0, Cost) AS Deal_Cost, IF(Inventory_ID IS NULL, AV_Impressions_Viewable, 0) AS Open_Impressions_Viewable, IF(Inventory_ID IS NULL, 0, AV_Impressions_Viewable) AS Deal_Impressions_Viewable, IF(Inventory_ID IS NULL, AV_Impressions_Measurable, 0) AS Open_Impressions_Measurable, IF(Inventory_ID IS NULL, 0, AV_Impressions_Measurable) AS Deal_Impressions_Measurable, FROM [[PARAMETER].Deal_Finder_DV360_Report] OMIT RECORD IF Site == 'Low volume inventory') GROUP By Advertiser_ID, Site_ID, Inventory, Inventory_Type, Creative_Type, Creative_Size, Always_On) WHERE Rank_Impressions < 100 OR Rank_Clicks < 100 OR Rank_Conversions < 100 OR Rank_Cost < 100;",
"parameters": [
{
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
]
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Deal_Finder_Dashboard"
}
}
}
]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.