Deploying a serverless integration solution based on Cloud Functions and Pub/Sub

This tutorial shows how to set up and test a data integration solution based on the concepts described in A serverless integration solution for Google Marketing Platform.

A demonstration of this serverless solution is available in an open source demonstration on GitHub. The solution comes with APIs and a shell script that helps you set up the solution in Google Cloud.

This tutorial is intended for data engineers or architects who want to automate integrating data with target systems by using APIs or other programmatic integration options such as SFTP Upload.

Objectives

  • Deploy a serverless integration solution.
  • Set up an integration in target systems for target APIs.
  • Test this solution by loading a CSV file to Google Sheets.

Costs

This tutorial uses the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

When you finish this tutorial, you can avoid continued billing by deleting the resources you created. For more information, see Cleaning up.

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  4. Open Cloud Shell.
  5. Open Cloud Shell

Installing the solution

  1. In Cloud Shell, clone the repository that contains the code you use for this tutorial:

    git clone https://github.com/GoogleCloudPlatform/cloud-for-marketing.git
    
  2. Run the installation script:

    cd cloud-for-marketing/marketing-analytics/activation/gmp-googleads-connector; chmod a+x deploy.sh; ./deploy.sh
    

    The script runs a series of processes, such as checking the environment and compiling dependencies.

  3. When you're prompted, enter the Cloud project ID you want to use for this tutorial.

  4. Select the region where you want to deploy your Cloud Function and store your data in Cloud Storage, for example,us-east1.

  5. Select the built-in APIs that you want to use, or press Enter to select them all. This solution comes with the following target APIs:

    • Google Analytics Measurement Protocol
    • Google Analytics Management API (Data Import)
    • Campaign Manager Conversions Upload
    • SFTP Upload for business data uploads to Search Ads 360
    • Google Sheets API for Google Ads conversions scheduled uploads based on Google Sheets
    • Search Ads 360 Conversions Upload

    Include the Google Sheets API because you will run a test that uses it after installation. A check is performed to determine if the necessary permissions are present to carry out the installation. If this check fails, you must request the missing permissions from your administrator and then run the script again.

  6. Enter a name for the Cloud Storage bucket that stores your data. If the bucket doesn't exist, select a region, and the script creates the bucket. (We suggest that you use the default region, which matches where Cloud Functions are to be deployed.)

  7. Enter a Cloud Storage folder that this solution monitors, or press Enter to select the default folder.

  8. Enter a prefix for Pub/Sub topics and subscriptions, or press Enter to use the default prefix.

  9. When you're prompted, type Y to confirm and save your settings to a config.json file.

    Pub/Sub topics and subscriptions are created.

  10. If a service account is required for the APIs you enabled, enter the name of a service account, and then confirm that you want to download the key file.

  11. After Cloud Functions are automatically deployed, the script indicates whether the Firestore or Datastore instance is ready. If it's not ready, print the instructions on how to initiate Firestore.

Preparing the data

Now that you've installed the solution, you can prepare your data for the target system.

File formats

The APIs built into this solution require specific file formats in some cases:

  • SFTP Upload has no format requests. This integration uploads the file to the server through SFTP regardless of file format.
  • The Google Analytics Management API (Data Import) and Google Sheets APIs expect CSV files.
  • For all the other APIs, the system expects newline-delimited JSON (NDJSON). Every line is a valid JSON string. For example, see the BigQuery export formats.

API configurations

Different APIs have different configurations, and one API could have more than one configuration for different usages. Hence, configurations are grouped by APIs and combine a single JSON object in the file config_api.json. The following listing from the JSON template file in the GitHub repository for this tutorial shows how the configurations are organized.

{
  "CM": {
    "foo": {
      "cmAccountId": "[YOUR-DCM-ACCOUNT-ID]",
      "cmConfig": {
        "idType": "encryptedUserId",
        "conversion": {
          "floodlightConfigurationId": "[YOUR-FL-CONFIG-ID]",
          "floodlightActivityId": "[YOUR-FL-ACTIVITY-ID]",
          "quantity": 1
        },
        "customVariables": [
          "[YOUR-U-VARIABLES-NAME-1]", "[YOUR-U-VARIABLES-NAME-2]"
        ],
        "encryptionInfo": {
          "encryptionEntityId": "[YOUR-ENCRYPTION-ID]",
          "encryptionEntityType": "DCM_ADVERTISER",
          "encryptionSource": "AD_SERVING"
        }
      }
    }
  },
  "GA": {
    "bar": {
      "dataImportHeader": "[YOUR-DATA-IMPORT-HEADER]",
      "gaConfig": {
        "accountId": "[YOUR-GA-ACCOUNT-ID]",
        "webPropertyId": "[YOUR-WEB-PROPERTY-ID]",
        "customDataSourceId": "[YOUR-CUSTOM-DATASOURCE-ID]"
      }
    }
  },
  "MP": {
    "baz": {
      "mpConfig":{
        "v": "1",
        "t": "transaction",
        "ni": "1",
        "dl": "[YOUR-SOMETHING-URL]",
        "tid": "[YOUR-WEB-PROPERTY-ID]"
      }
    }
  },
  "SFTP": {
    "qux": {
      "sftp":{
        "host": "[YOUR-SFTP-HOST]",
        "port": "[YOUR-SFTP-PORT]",
        "username": "[YOUR-SFTP-USERNAME]",
        "password": "[YOUR-SFTP-PASSWORD]"
      }
    }
  },
  "GS": {
    "foo": {
      "spreadsheetId": "[YOUR-SPREADSHEET-ID]",
      "sheetName": "[YOUR-SHEET-NAME]",
      "sheetHeader": "[ANYTHING-PUT-AHEAD-OF-CSV]",
      "pasteData": {
        "coordinate": {
          "rowIndex": 0,
          "columnIndex": 0
        },
        "delimiter": ","
      }
    }
  },
  "SA": {
    "bar": {
      "saConfig": {
        "currencyCode": "[YOUR-CURRENCY-CODE]",
        "type": "TRANSACTION",
        "segmentationType": "FLOODLIGHT",
        "segmentationId": "[YOUR-SEGMENTATION-ID]",
        "state": "ACTIVE"
      },
      "availabilities": [
        {
          "agencyId": "[YOUR-AGENCY-ID]",
          "advertiserId": "[YOUR-ADVERTISER-ID]",
          "segmentationType": "FLOODLIGHT",
          "segmentationId": "[YOUR-SEGMENTATION-ID]"
        }
      ]
    }
  },
  "ACLC": {
    "foo" : {
      "customerId": "[YOUR-GOOGLE-ADS-ACCOUNT-ID]",
      "loginCustomerId": "[YOUR-LOGIN-GOOGLE-ADS-ACCOUNT-ID]",
      "developerToken": "[YOUR-GOOGLE-ADS-DEV-TOKEN]",
      "adsConfig": {
        "conversion_action": "[YOUR-CONVERSION-ACTION-NAME]",
        "conversion_value": "[YOUR-CONVERSION-VALUE]",
        "currency_code": "[YOUR-CURRENCY-CODE]"
      }
    }
  },
  "ACM": {
    "foo" : {
      "developerToken": "[YOUR-GOOGLE-ADS-DEV-TOKEN]",
      "customerMatchConfig": {
        "customer_id": "[YOUR-GOOGLE-ADS-ACCOUNT-ID]",
        "login_customer_id": "[YOUR-LOGIN-GOOGLE-ADS-ACCOUNT-ID]",
        "list_id": "[YOUR-CUSTOMER-MATCH-LIST-ID]",
        "list_type": "[YOUR-CUSTOMER-MATCH-LIST-TYPE],
        "operation": "create|remove"
      }
    }
  }
}

You can rename the template filename to config_api.json, leave only targeted API sections, and edit the details of the configuration. If you have more than one configuration, copy the configurations and give them different names. After that, update the configurations in Firestore:

./deploy.sh update_api_config

Filename convention

To achieve flexibility for different kinds of APIs and configurations, we adopt a naming convention in this solution for incoming files. Filenames should contain the pattern API[X] and config[Y].

  • X stands for the codes of target APIs. In this solution, X could be:
    • MP: Google Analytics Measurement Protocol
    • GA: Google Analytics Management API (Data Import)
    • CM: DCM/DFA Reporting and Trafficking API
    • SFTP: SFTP Upload
    • GS: Google Sheets API
    • SA: Search Ads 360 API
  • Y is the configuration name, for example, foo or bar in the template code.

For example, a file named API[GA]_config[bar]_20191111.csv indicates the following:

  • [GA] means Google Analytics is the target system and Data Import is the target API.
  • [bar] means that the file is sent to the target system using the bar key of a JSON object stored in the template configuration file.

Optional patterns for filenames include the following:

  • If the filename contains dryrun, then the process follows all the steps but doesn't send the data to the API server.
  • If the filename contains _size[Z] or _size[Zmb], the incoming file is divided into multiple files, and file sizes under Z MB in Cloud Storage obey the file size limitation for some APIs. For example, Data Import has a limitation of 1 GB.

For example, a file named API[GA]_config[bar]_dryrun_20191111.csv is not sent to Google Analytics but does go through the whole integration system. A file named API[GA]_config[bar]_size[100]_20191111.csv is split into a group of files with file sizes no larger than 100 MB, and each file is sent individually to Google Analytics through Data Import.

Testing the solution

After you install the solution, you test an integration by creating a CSV file and automatically loading data from that file into a destination Google Sheets spreadsheet.

Grant permission to the service account

  1. Create a new Google Sheets spreadsheet, and then rename it, for example, Integration Test.
  2. Click Share.
  3. In Cloud Shell, get the email address of the service account you used during the installation steps:

    ./deploy.sh print_service_account
    
  4. In the People field, copy the email address of the service account, and then select Can edit to assign it Editor permissions.

Get the ID of your destination spreadsheet

  • Copy the spreadsheet ID for your destination Google Sheets spreadsheet.

    The spreadsheet ID is the value between /d/ and /edit in the spreadsheet's URL, for example, spreadsheetId in the following:

    https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0
    

Update the API configuration

  1. In Cloud Shell, go to the folder where you installed the solution, for example, ~/cloud-for-marketing/marketing-analytics/activation/gmp-googleads-connector.
  2. Open the JSON file named config_api.json and replace the content with following code:

    {
      "GS": {
        "foo": {
          "spreadsheetId": "your-spreadsheet-id",
          "sheetName": "your-sheet-name",
          "sheetHeader": "This is a test integration",
          "pasteData": {
            "coordinate": {
              "rowIndex": 0,
              "columnIndex": 0
            },
            "delimiter": ","
          }
        }
      }
    }
    

    Replace the following:

    • your-spreadsheet-id: Your spreadsheet ID.
    • your-sheet-name: The name of the destination sheet in your Google Sheets spreadsheet.
  3. Upload the configuration into Firestore or Datastore:

    ./deploy.sh update_api_config
    

    The output is the following:

    Init ApiConfig based on Datastore.
      Import Config for API[GS]_config[foo]
    

Prepare the files to be sent

In this test, you load the data in your CSV file into the target sheet.

  • In Cloud Shell, create a CSV file with the content of some built-in APIs:

    cat > API[GS]_config[foo]_test.csv <<EOF
    #What are Tentacles built in APIs
    Target System, Target API, Code
    Google Analytics, Measurement Protocol, MP
    Google Analytics, Google Analytics Management API, GA
    Campaign Manager, DCM/DFA Reporting and Trafficking API, CM
    Search Ads 360, SFTP, SFTP
    EOF
    

Upload the file to Cloud Storage

  • Upload the file into the Cloud Storage bucket:

    ./deploy.sh copy_file_to_gcs API[GS]_config[foo]_test.csv
    

    The output is similar to the following:

    Copy integration data file to target folder…
    Copying file:...
      Operation completed over...
    

Verify the result

  • Open the Google Sheets spreadsheet you created earlier.

    Sheet1 was resized and loaded with data from the CSV file.

    Screenshot that shows uploaded data to a Google Sheets
spreadsheet.

Clean up

The easiest way to eliminate billing is to delete the project you created for the tutorial.

  1. In the Cloud Console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next