All
donut_large
DV360 Segmentology
DV360 funnel analysis using Census data.
Impact Level
| Spend Optimization |
|
| Spend Growth |
|
| Time Savings |
|
| Account Health |
|
| Csat Improvement |
|
Value Proposition
| Optimize creatives and landing pages for engaged demographics. |
| Examine DV360 funnel in terms of demographics. |
| Discover missed marketing opportunities. |
| Run continously to measure impact of funnel changes. |
Instructions
| Wait for BigQuery->->->Census_Join to be created. |
| Join the StarThinker Assets Group to access the following assets |
| Copy DV360 Segmentology Sample. Leave the Data Source as is, you will change it in the next step. |
| Click Edit Connection, and change to BigQuery->->->Census_Join. |
| Or give these intructions to the client. |
Details
| Open Source | YES |
| Age | Aug. 13, 2020 (2 years, 4 months) |
| Authors | kenjora@google.com |
| Shedule Days | Configured by user. |
| Shedule Hours | Configured by user. |
[
{
"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",
"suffix": "_Segmentology",
"description": "Place where tables will be created in BigQuery."
}
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing function."
}
},
"function": "Pearson Significance Test",
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
}
}
},
{
"dbm": {
"auth": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 0,
"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",
"order": 3,
"suffix": " Segmentology",
"default": "",
"description": "Name of report, not needed if ID used."
}
},
"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 report for."
}
},
"format": "CSV"
},
"params": {
"type": "TYPE_CROSS_PARTNER",
"groupBys": [
"FILTER_PARTNER",
"FILTER_PARTNER_NAME",
"FILTER_ADVERTISER",
"FILTER_ADVERTISER_NAME",
"FILTER_MEDIA_PLAN",
"FILTER_MEDIA_PLAN_NAME",
"FILTER_ZIP_POSTAL_CODE"
],
"metrics": [
"METRIC_BILLABLE_IMPRESSIONS",
"METRIC_CLICKS",
"METRIC_TOTAL_CONVERSIONS"
]
},
"schedule": {
"frequency": "WEEKLY"
}
}
}
}
},
{
"dbm": {
"auth": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"report": {
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"order": 3,
"suffix": " Segmentology",
"default": "",
"description": "Name of report, not needed if ID used."
}
}
},
"out": {
"bigquery": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Authorization used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
},
"table": "DV360_KPI",
"header": true,
"schema": [
{
"name": "Partner_Id",
"type": "INTEGER",
"mode": "REQUIRED"
},
{
"name": "Partner",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "Advertiser_Id",
"type": "INTEGER",
"mode": "REQUIRED"
},
{
"name": "Advertiser",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "Campaign_Id",
"type": "INTEGER",
"mode": "REQUIRED"
},
{
"name": "Campaign",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "Zip",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Impressions",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Clicks",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "Conversions",
"type": "FLOAT",
"mode": "NULLABLE"
}
]
}
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Authorization used for writing data."
}
},
"from": {
"query": "SELECT Partner_Id, Partner, Advertiser_Id, Advertiser, Campaign_Id, Campaign, Zip, SAFE_DIVIDE(Impressions, SUM(Impressions) OVER(PARTITION BY Advertiser_Id)) AS Impression, SAFE_DIVIDE(Clicks, Impressions) AS Click, SAFE_DIVIDE(Conversions, Impressions) AS Conversion, Impressions AS Impressions FROM `{dataset}.DV360_KPI`; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"description": "Place where tables will be created in BigQuery."
}
}
},
"legacy": false
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "DV360_KPI_Normalized"
}
}
},
{
"census": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Authorization used for writing data."
}
},
"normalize": {
"census_geography": "zip_codes",
"census_year": "2018",
"census_span": "5yr"
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
},
"type": "view"
}
}
},
{
"census": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Authorization used for writing data."
}
},
"correlate": {
"join": "Zip",
"pass": [
"Partner_Id",
"Partner",
"Advertiser_Id",
"Advertiser",
"Campaign_Id",
"Campaign"
],
"sum": [
"Impressions"
],
"correlate": [
"Impression",
"Click",
"Conversion"
],
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
},
"table": "DV360_KPI_Normalized",
"significance": 80
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
},
"type": "view"
}
}
}
]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.