All
compare
CM360 Campaign Comparison
Group KPIs into cohorts and compare performance across time and geography. Quickly discover where national and local campaigns need to be targeted, funded, or optimized.
Impact Level
| Spend Optimization |
|
| Spend Growth |
|
| Time Savings |
|
| Account Health |
|
| Csat Improvement |
|
Value Proposition
| Compare campaign performance for A/B testing. |
| Evaluate national vs. local campaigns. |
| Evaluate tier 1 vs tier 2 campaigns. |
| Compare performance along time and geography. |
| Identify KPI drop of relative to local population. |
Instructions
| Add required parameters and run recipe. |
| After recipe completes make a copy of the Campaign Comparison Dashboard. |
| Keep the data source as is on the copy screen. It will change later. |
| After the copy is made, click Edit->Resource->Manage Added Data Sources->CC_Report->Edit->Edit Connection. |
| Connect to the newly created BigQuery->->_Campaign_Comparison->Comparison table. |
| Or give these intructions to the client. |
Details
| Open Source | YES |
| Age | Aug. 24, 2021 (1 year, 4 months) |
| Authors | kenjora@google.com |
| Shedule Days | Configured by user. |
| Shedule Hours | 3 |
[
{
"dataset": {
"description": "Create a dataset for bigquery tables.",
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "service",
"description": "Authorization used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
}
}
},
{
"dcm": {
"description": "Create KPI report.",
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"report": {
"account": {
"field": {
"name": "account",
"kind": "integer",
"order": 1,
"default": 12345,
"description": "Campaign Manager Account ID"
}
},
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"order": 3,
"suffix": " Campaign Comparison",
"default": "",
"description": "Name of report."
}
},
"body": {
"kind": "dfareporting#report",
"format": "CSV",
"type": "STANDARD",
"criteria": {
"dateRange": {
"kind": "dfareporting#dateRange",
"relativeDateRange": {
"field": {
"name": "relativeDateRange",
"kind": "choice",
"order": 4,
"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 report for."
}
}
},
"dimensions": [
{
"kind": "dfareporting#sortedDimension",
"name": "date"
},
{
"kind": "dfareporting#sortedDimension",
"name": "advertiserId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "campaignId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "adId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "placementId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "platformType"
},
{
"kind": "dfareporting#sortedDimension",
"name": "dmaRegion"
},
{
"kind": "dfareporting#sortedDimension",
"name": "zipCode"
}
],
"metricNames": [
"impressions",
"clicks",
"totalConversions",
"mediaCost"
]
},
"schedule": {
"active": true,
"repeats": "WEEKLY",
"repeatsOnWeekDays": "MONDAY",
"every": 1
},
"delivery": {
"emailOwner": false
}
},
"filters": {
"advertiser": {
"values": {
"field": {
"name": "advertiser",
"kind": "integer_list",
"order": 3,
"default": [],
"description": "Optional comma delimited list of ids."
}
}
}
}
}
}
},
{
"dcm": {
"description": "Download KPI report.",
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"report": {
"account": {
"field": {
"name": "account",
"kind": "integer",
"order": 1,
"default": 12345,
"description": "Campaign Manager Account ID"
}
},
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"order": 3,
"suffix": " Campaign Comparison",
"default": "",
"description": "Name of report."
}
}
},
"out": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "service",
"description": "Authorization used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
},
"table": "CM_Report",
"header": true
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"api": "dfareporting",
"version": "v3.5",
"function": "advertisers.list",
"iterate": true,
"kwargs": {
"fields": "advertisers.id,advertisers.name,nextPageToken",
"accountId": {
"field": {
"name": "account",
"kind": "integer",
"order": 1,
"default": 12345,
"description": "Campaign Manager Account ID"
}
}
},
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "service",
"description": "Authorization used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
},
"table": "CM_Advertisers"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"api": "dfareporting",
"version": "v3.5",
"function": "campaigns.list",
"iterate": true,
"kwargs": {
"fields": "campaigns.id,campaigns.name,nextPageToken",
"accountId": {
"field": {
"name": "account",
"kind": "integer",
"order": 1,
"default": 12345,
"description": "Campaign Manager Account ID"
}
}
},
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "service",
"description": "Authorization used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
},
"table": "CM_Campaigns"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"api": "dfareporting",
"version": "v3.5",
"function": "ads.list",
"iterate": true,
"kwargs": {
"fields": "ads.id,ads.name,ads.type,nextPageToken",
"accountId": {
"field": {
"name": "account",
"kind": "integer",
"order": 1,
"default": 12345,
"description": "Campaign Manager Account ID"
}
}
},
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "service",
"description": "Authorization used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
},
"table": "CM_Ads"
}
}
}
},
{
"google_api": {
"auth": {
"field": {
"name": "auth_cm",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"api": "dfareporting",
"version": "v3.5",
"function": "placements.list",
"iterate": true,
"kwargs": {
"fields": "placements.id,placements.name",
"accountId": {
"field": {
"name": "account",
"kind": "integer",
"order": 1,
"default": 12345,
"description": "Campaign Manager Account ID"
}
}
},
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "service",
"description": "Credentials used for reading data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
},
"table": "CM_Placements"
}
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "service",
"description": "Credentials used for reading data."
}
},
"from": {
"query": "WITH CCReport AS ( SELECT Report_Day, CONCAT(CA.name, ' - ', CA.id) AS Advertiser, CONCAT(CC.name, ' - ', CC.id) AS Campaign, CONCAT(CD.name, ' - ', CD.id) AS Ad, CONCAT(CP.name, ' - ', CP.id) AS Placement, CR.Platform_Type AS Platform_Type, CD.type AS Ad_Type, Zip_Postal_Code AS Zip_Code, Designated_Market_Area_Dma AS DMA, CR.Impressions AS Impressions, CR.Clicks AS Clicks, CAST(CR.Total_conversions AS INT64) AS Conversions, CR.Media_Cost AS Costs FROM `{dataset}.CM_Report` AS CR LEFT JOIN `{dataset}.CM_Advertisers` AS CA ON CR.Advertiser_Id=CA.id LEFT JOIN `{dataset}.CM_Campaigns` AS CC ON CR.Campaign_Id=CC.id LEFT JOIN `{dataset}.CM_Ads` AS CD ON CR.Ad_Id=CD.id LEFT JOIN `{dataset}.CM_Placements` AS CP ON CR.Placement_Id=CP.id ), CCReportZip AS ( SELECT R.* EXCEPT(Zip_Code, DMA), STRUCT ( R.Zip_Code, Z.city AS City, Z.county AS County, R.DMA, Z.state_code AS State_Code, Z.area_land_meters AS Area_Land_Meters ) AS Location FROM CCReport AS R LEFT JOIN `bigquery-public-data.geo_us_boundaries.zip_codes` AS Z ON Z.zip_code=R.Zip_Code ), CCReportPopulation AS ( SELECT R.*, C.pop_16_over AS Population FROM CCReportZip AS R LEFT JOIN `bigquery-public-data.census_bureau_acs.zip_codes_2018_5yr` AS C ON R.Location.Zip_Code=C.geo_id ), CCReportMax AS ( SELECT MAX(Population) AS Population, MAX(SAFE_DIVIDE(Population, Location.Area_Land_Meters)) AS Density, MAX(Impressions) AS Impression, MAX(SAFE_DIVIDE(Impressions, Population)) AS Impression_Rate, MAX(SAFE_DIVIDE(Impressions, Costs)) AS Impression_Cost, MAX(Clicks) AS Click, MAX(SAFE_DIVIDE(Clicks, Impressions)) AS Click_Rate, MAX(SAFE_DIVIDE(Clicks, Costs)) AS Click_Cost, MAX(Conversions) AS Conversion, MAX(SAFE_DIVIDE(Conversions, Clicks)) AS Conversion_Rate, MAX(SAFE_DIVIDE(Conversions, Costs)) AS Conversion_Cost, MAX(Costs) AS Costs FROM CCReportPopulation ), CCReportRanks AS ( SELECT R.*, STRUCT ( ROUND(SAFE_DIVIDE(R.Population, M.Population),3) AS Population, ROUND(SAFE_DIVIDE(SAFE_DIVIDE(R.Population, Location.Area_Land_Meters), M.Density),3) AS Density, ROUND(SAFE_DIVIDE(R.Impressions, M.Impression),3) AS Impression, ROUND(SAFE_DIVIDE(SAFE_DIVIDE(R.Impressions,R.Population), M.Impression_Rate),3) AS Impression_Rate, ROUND(SAFE_DIVIDE(SAFE_DIVIDE(R.Impressions,R.Costs), M.Impression_Cost),3) AS Impression_Cost, ROUND(SAFE_DIVIDE(R.Clicks, M.Click),3) AS Click, ROUND(SAFE_DIVIDE(SAFE_DIVIDE(R.Clicks,R.Impressions),M.Click_Rate),3) AS Click_Rate, ROUND(SAFE_DIVIDE(SAFE_DIVIDE(R.Clicks, R.Costs), M.Click_Cost),3) AS Click_Cost, ROUND(SAFE_DIVIDE(R.Conversions, M.Conversion),3) AS Conversion, ROUND(SAFE_DIVIDE(SAFE_DIVIDE(R.Conversions, R.Clicks), M.Conversion_Rate),3) AS Conversion_Rate, ROUND(SAFE_DIVIDE(SAFE_DIVIDE(R.Conversions, R.Costs), M.Conversion_Cost),3) AS Conversion_Cost, ROUND(SAFE_DIVIDE(R.Costs, M.Costs),3) AS Costs ) AS Location_Ranking FROM CCReportPopulation AS R CROSS JOIN CCReportMax AS M ) SELECT 'COHORT-A' AS Cohort, Report_Day, Advertiser, Campaign, Ad, Placement, Ad_Type, Platform_Type, Location, Location_Ranking, STRUCT( Advertiser, Campaign, Ad, Placement, Population, Impressions, Clicks, Conversions, Costs ) AS Cohort_A, STRUCT( '!COHORT-B' AS Advertiser, '!COHORT-B' AS Campaign, '!COHORT-B' AS Ad, '!COHORT-B' AS Placement, 0 AS Population, 0 AS Impressions, 0 AS Clicks, 0 AS Conversions, 0 AS Costs ) AS Cohort_B FROM CCReportRanks UNION ALL SELECT 'COHORT-B' AS Cohort, Report_Day, Advertiser, Campaign, Ad, Placement, Ad_Type, Platform_Type, Location, Location_Ranking, STRUCT( '!COHORT-A' AS Advertiser, '!COHORT-A' AS Campaign, '!COHORT-A' AS Ad, '!COHORT-A' AS Placement, 0 AS Population, 0 AS Impressions, 0 AS Clicks, 0 AS Conversions, 0 AS Costs ) AS Cohort_A, STRUCT( Advertiser, Campaign, Ad, Placement, Population, Impressions, Clicks, Conversions, Costs ) AS Cohort_B, FROM CCReportRanks",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
}
},
"legacy": false
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
},
"view": "Comparison_View"
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_bq",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"from": {
"query": "SELECT * FROM `{dataset}.Comparison_View`",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
}
},
"legacy": false
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 1,
"suffix": "_Campaign_Comparison",
"default": "",
"description": "Name of dataset."
}
},
"table": "Comparison"
}
}
}
]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.