thunderstorm

A new EIP Policy expires user ceredentials within 20 hours, preventing Googler driven workflows. A decision has been made to shut down the StarThinker UI, please seek alternatives. The UI will remain on GitHub for partners.



All

donut_large

CM360 Segmentology

CM360 funnel analysis using Census data.

CM360 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 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 (1 year, 6 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.

Deployment Stepslaunch Developer Guidebuild UI How Tolaptop