Megalista

Sample integration code for onboarding offline/CRM data from BigQuery as custom audiences or offline conversions in Google Ads, Google Analytics 360, Google Display & Video 360 and Google Campaign Manager.

Disclaimer: This is not an officially supported Google product.

Supported integrations

• Google Ads

  • Contact Info Customer Match (email, phone, address) [details]
  • Id Based Customer Match (device Id, user id)
  • Offline Conversions through gclid [details]
  • Store Sales Direct (SSD) conversions [details]

• Google Analytics (Universal analytics)

  • Custom segments through Data Import [details]
  • Measurement Protocol [details]

• Campaign Manager

  • Offline Conversions API (user id, device id, match id, gclid, dclid, value, quantity, and customVariables) [details]

• Google Analytics 4

  • Measurement protocol (Web + App) [details]

• Appsflyer

  • S2S Offline events API (conversion upload), to be used for audience creation and in-app events with Google Ads and DV360 [details]

How does it work

Megalista was design to separate the configuration of conversion/audience upload rules from the engine, giving more freedom for non-technical teams (i.e. Media and Business Inteligence) to setup multiple upload rules on their own.

The solution consists of #1 a configuration environment (either Google Sheet or JSON file, or a Google Cloud Firestore collection) in which all rules are defined by mapping a data source (BigQuery Table) to a destination (data upload endpoint) and #2, an Apache Beam workflow running on Google Dataflow, scheduled to upload the data in batch mode.

Prerequisites

Google Cloud Services

• Google Cloud Platform account
  • Billing enabled
  • BigQuery enabled
  • Dataflow enabled
  • Cloud storage enabled
  • Cloud scheduler enabled
  • App Engine enabled
• At least one of:
  • Google Ads API Access
  • Campaign Manager API Access
  • Google Analytics API Access
• Python3
• Google Cloud SDK

Access Requirements

Those are the minimum roles necessary to deploy Megalista:

• OAuth Config Editor
• BigQuery User
• BigQuery Job User
• BigQuery Data Viewer
• Cloud Scheduler Admin
• Storage Admin
• Dataflow Admin
• Service Account Admin
• Logs Viewer
• Service Consumer

APIs

Required APIs will depend on upload endpoints in use.

• Google Sheets (required if using Sheets configuration) [link]
• Google Analytics [link]
• Google Analytics Reporting [link]
• Google Ads [link]
• Campaign Manager [link]
• Google Cloud Firestore [link]

Configure Megalista

Megalista can be configured via Google Sheets, a JSON file or a Google Cloud Firestore collection. Expected data schemas (Sources) and metadata (Destinations) for each use case defined in the Megalista Wiki.

Instructions for each configuration method method can be found in the Megalista wiki

• [Google Sheets] (https://github.com/google/megalista/wiki/Google-Sheets)
• [JSON] (https://github.com/google/megalista/wiki/JSON)
• [Firestore] (https://github.com/google/megalista/wiki/Firestore)

Deployment

These guide assumes it'll be followed inside Google Cloud Platform Console.

Creating required access tokens

To access campaigns and user lists on Google's platforms, this dataflow will need OAuth tokens for an account that can authenticate in those systems.

In order to create it, follow these steps:

• Access GCP console
• Go to the API & Services section on the top-left menu.
• On the OAuth Consent Screen and configure an Internal Consent Screen
• Then, go to the Credentials and create an OAuth client Id with Application type set as Desktop App
• This will generate a Client Id and a Client secret. Save these values as they are required during the deployment
• Run the generate_megalista_token.sh script in this folder providing these two values and follow the instructions
  • Sample: ./generate_megalista_token.sh client_id client_secret
• This will generate the Access Token and the Refresh token
  • The user who opened the generated link and clicked on Allow must have access to the platforms that Megalista will integrate, including the configuration Sheet, if this is the chosen method for configuration.

Deploying Pipeline

To deploy the full Megalista pipeline, use the following command from the root folder: ./terraform_deploy.sh The script will required some parameters, between them:

• Auxliary bigquery dataset for Megalista operations to create
  • This dataset will be used for storing operational data and will be created by Terraform
• Google Cloud Storage Bucket to create
  • This Cloud Storage Bucket will be used to store Megalista compiled binary, metadata and temp files and will be created by Terraform.
• Setup Firestore collection, URL for JSON configuration and Setup Sheet Id
  • Only one of these three should be filled and the other should be left black accordingly to the chosen configuration method.

Updating the Binary

to update the binary whitouth redoing the whole deployment process, run:

• ./terraform/scripts/deploy_cloud.sh gcp_project_id bucket_name region

Usage

Every upload method expects as source a BigQuery data with specific fields, in addition to specific configuration metadata. For details on how to setup your upload routines, refer to the Megalista Wiki.

Note about Google Ads API access

Calls to the Google Ads API will fail if the user that generated the OAuth2 credentials (Access Token and Refresh Token) doesn't have direct access to the Google Ads account which the calls are being directed to. It's not enough for the user to have access to a MCC above this account and being able to access the account through the interface, it's required that the user has permissions on the account itself.