All
donut_large
GoogleAds Segmentology
GoogleAds funnel analysis using Census data.
Impact Level
| Spend Optimization |
|
| Spend Growth |
|
| Time Savings |
|
| Account Health |
|
| Csat Improvement |
|
Value Proposition
| Optimize keywords and landing pages for engaged demographics. |
| Examine Google Ads funnel in terms of demographics. |
| Discover missed marketing opportunities. |
| Run continuously 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 GoogleAds 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 instructions 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": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"api": "googleads",
"version": "v8",
"function": "customers.googleAds.search",
"kwargs": {
"customerId": {
"field": {
"name": "customer_id",
"kind": "string",
"description": "Google Ads customer.",
"default": ""
}
},
"body": {
"query": "SELECT campaign.name, ad_group.name, segments.geo_target_postal_code, metrics.impressions, metrics.clicks, metrics.conversions, metrics.interactions FROM user_location_view "
}
},
"headers": {
"developer-token": {
"field": {
"name": "developer_token",
"kind": "string",
"description": "Google Ads developer token.",
"default": ""
}
},
"login-customer-id": {
"field": {
"name": "login_id",
"kind": "string",
"description": "Google Ads login.",
"default": ""
}
}
},
"iterate": true,
"results": {
"bigquery": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
},
"table": "GoogleAds_KPI",
"schema": [
{
"name": "userLocationView",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
{
"name": "resourceName",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "segments",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
{
"name": "geoTargetPostalCode",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "metrics",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
{
"name": "interactions",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "impressions",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "conversions",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "clicks",
"type": "INTEGER",
"mode": "NULLABLE"
}
]
},
{
"name": "adGroup",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
{
"name": "name",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "resourceName",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "campaign",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
{
"name": "name",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "resourceName",
"type": "STRING",
"mode": "NULLABLE"
}
]
}
]
}
}
}
},
{
"bigquery": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Authorization used for writing data."
}
},
"from": {
"query": "SELECT campaign.name AS Campaign, adGRoup.name AS Ad_Group, segments.geoTargetPostalCode AS Postal_Code, SAFE_DIVIDE(metrics.impressions, SUM(metrics.impressions) OVER()) AS Impression, SAFE_DIVIDE(metrics.clicks, metrics.impressions) AS Click, SAFE_DIVIDE(metrics.conversions, metrics.impressions) AS Conversion, SAFE_DIVIDE(metrics.interactions, metrics.impressions) AS Interaction, metrics.impressions AS Impressions FROM `{dataset}.GoogleAds_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": "GoogleAds_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": "Postal_Code",
"pass": [
"Campaign",
"Ad_Group"
],
"sum": [
"Impressions"
],
"correlate": [
"Impression",
"Click",
"Conversion",
"Interaction"
],
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"suffix": "_Segmentology",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
},
"table": "GoogleAds_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.