The StarThinker project will no longer receive new solution contributions from the Google team.
Please read the full StarThinker Open Source Support Ends At Google article for more details.



All

donut_large

GoogleAds Segmentology

GoogleAds funnel analysis using Census data.

GoogleAds Segmentology Sample Screenshot


lock_openGet Access directions_bikeSample listDocumentation listGit Hub sourcePython menu_bookColab airAirflow thumb_upTest



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.

Deployment Stepslaunch Developer Guidebuild UI How Tolaptop