All
donut_large
CM360 Segmentology
CM360 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 CM360 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 CM360 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."
}
}
}
}
},
{
"google_api": {
"auth": "user",
"api": "dfareporting",
"version": "v3.4",
"function": "accounts.get",
"kwargs": {
"id": {
"field": {
"name": "account",
"kind": "integer",
"order": 5,
"default": "",
"description": "Campaign Manager Account ID"
}
},
"fields": "id,name"
},
"results": {
"bigquery": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing function."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
},
"table": "CM360_Account"
}
}
}
},
{
"dcm": {
"auth": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"report": {
"filters": {
"advertiser": {
"values": {
"field": {
"name": "advertisers",
"kind": "integer_list",
"order": 6,
"default": [],
"description": "Comma delimited list of CM360 advertiser ids."
}
}
}
},
"account": {
"field": {
"name": "account",
"kind": "string",
"order": 5,
"default": "",
"description": "Campaign Manager Account ID"
}
},
"body": {
"name": {
"field": {
"name": "recipe_name",
"kind": "string",
"suffix": " Segmentology",
"description": "The report name.",
"default": ""
}
},
"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",
"LAST_24_MONTHS",
"MONTH_TO_DATE",
"PREVIOUS_MONTH",
"PREVIOUS_QUARTER",
"PREVIOUS_WEEK",
"PREVIOUS_YEAR",
"QUARTER_TO_DATE",
"WEEK_TO_DATE",
"YEAR_TO_DATE"
],
"description": "Timeframe to run report for."
}
}
},
"dimensions": [
{
"kind": "dfareporting#sortedDimension",
"name": "advertiserId"
},
{
"kind": "dfareporting#sortedDimension",
"name": "advertiser"
},
{
"kind": "dfareporting#sortedDimension",
"name": "zipCode"
}
],
"metricNames": [
"impressions",
"clicks",
"totalConversions"
]
},
"type": "STANDARD",
"delivery": {
"emailOwner": false
},
"format": "CSV"
}
}
}
},
{
"dcm": {
"auth": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"report": {
"account": {
"field": {
"name": "account",
"kind": "string",
"default": ""
}
},
"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": "CM360_KPI",
"header": true
}
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Authorization used for writing data."
}
},
"from": {
"query": "SELECT Id AS Partner_Id, Name AS Partner, Advertiser_Id, Advertiser, Zip_Postal_Code AS Zip, SAFE_DIVIDE(Impressions, SUM(Impressions) OVER(PARTITION BY Advertiser_Id)) AS Impression, SAFE_DIVIDE(Clicks, Impressions) AS Click, SAFE_DIVIDE(Total_Conversions, Impressions) AS Conversion, Impressions AS Impressions FROM `{dataset}.CM360_KPI` CROSS JOIN `{dataset}.CM360_Account` ",
"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": "CM360_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"
],
"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": "CM360_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.