Google BigQuery Connection

How to connect Google BigQuery to the BlueConic customer data platform

What:  The Google BigQuery connection allows you to enrich BlueConic profiles with data and event information from Google BigQuery and to export profile data and Timeline events to Google BigQuery tables. The connection supports batch import and export.

About BlueConic: The BlueConic Customer Data Platform harnesses the data required to power the recognition of an individual at each interaction, and then synchronizes their intent across the marketing ecosystem.

Why: By retrieving data from BigQuery to BlueConic and exporting data to BigQuery from BlueConic, you can enrich your customer data in both systems. This data can then be used to synchronize to other systems, for example for retargeting, content or product recommendations, or on-site or email personalization.

Two ways to connect to Google BigQuery

BlueConic offers two ways to synchronize your data with Google BigQuery:

  • Use the instructions below to exchange profile data via batch import or export with Google BigQuery.
  • To export a large amount of data in real time to Google Cloud, or to stream BlueConic events, use the Google Cloud Pub/Sub connection and follow the use case instructions for exporting your data to Google BigQuery using a firehose connection.

How to synchronize marketing data between Google BigQuery and the BlueConic customer data platform

Adding a Google BigQuery connection

  1. Click Connections in the Navigation bar.
  2. Click Add Connection.
  3. A pop-up window appears. Check the Show all box. Enter “BigQuery” in the Search bar. Click Google BigQuery connection.
  4. The Google BigQuery Connection page opens. To expand or collapse metadata fields (favorite, labels, and description), click the gray chevron at the top of the page.
  5. In the metadata section you can choose whether to get email notifications when the connection runs or fails to run.Google-Big-Query-connection-metadata-section.png
  6. Save your settings.

Configuring a Google BigQuery connection

Authentication

You can use an existing service account or create a new one specifically for BlueConic access via Create service account.

Make sure to assign a BigQuery Data Viewer or Admin role to the service account you use. For existing accounts, you can assign roles via 'Permissions'.

To authenticate the BlueConic connection with Google BigQuery, you will need to generate a service account key in Google BigQuery.

  1. Open the Google Cloud Platform Console.
  2. Select the BigQuery project you want to connect to in the top header.
  3. Click the Products & services hamburger menu in the upper left corner.
  4. Click IAM & admin.
  5. Click Service Accounts.
  6. Click behind the account you want to use for BlueConic.
  7. Click Create Key.

How to configure the Google BigQuery connectionn to the BlueConic customer data platform

  1. Click key type JSON.
  2. Click Create to download the private key file to your computer.

How to set up a connection between Google BigQuery and the BlueConic customer data platform

  1. In BlueConic, open the Set up and run page of the connection, and in the Authentication section, add your service account key. BlueConic will then detect a Project and display the name here. To use a different project, you need to upload a different Service account key.
    Screen_Shot_2022-03-15_at_10.18.23_AM.png
  1. Click the Save button to save your authentication information.

Creating Import goals for importing data from Google BigQuery to BlueConic

Click Add goal and select Import goal to import data from Google BigQuery.

  1. If you use domain groups, you can optionally select a BlueConic domain group for the import.
  2. Select the Google BigQuery dataset that holds the query. Select the name of the query Table to import data from. You can select multiple nested tables to import data from. Use the link icon to link tables together through IDs that exist in both tables.
    Screen_Shot_2022-03-10_at_3.08.05_PM.png
  3. Link identifiers between Google BigQuery and BlueConic to link data rows from the query to BlueConic profiles.
    If there is no match for a Google BigQuery identifier field, either no data will be imported or a new BlueConic profile will be created if the "Allow the creation of new BlueConic profiles" option is checked. Google BigQuery table rows that contain no value for the matching field will be excluded from the import.

    Optional: Allow BlueConic to create a new profile if no match is found.
    How to create new customer profiles from Google BigQuery to the BlueConic customer data platform

  4. Map the Google BigQuery data to BlueConic profile properties.

    Select a data field from the dropdown list and select the BlueConic profile property it populates by entering a search term.
    Screen_Shot_2022-03-10_at_3.10.36_PM.png

    Select how to import the data from the drop-down list:

    • Set
    • Set if empty
    • Set or clear
    • Add the data field to the list of existing values
    • Sum a number with the existing values (if the data field is a number)

     

  5. Optionally import events to the BlueConic Timeline. Map the Google BigQuery fields to matching BlueConic Timeline event properties.
    import-order-event.png
  6. To run the connection or to schedule a run, Save your settings and go to the Set up and run page. See Scheduling connections for details.

Note: If you are sending large segments or datasets via this connection, limit the connection to run no more than once per day to avoid slowing the performance of this and other connections.

Optional: In the left panel, click Add goal and then Import goal to create another Import goal.
google-big-query-setup.png

Creating Export goals to export BlueConic data to Google BigQuery

Click Add goal and select Export goal to export profile property and Timeline event data to Google BigQuery. Follow the steps to configure the export goal.

Note: When using partitioned tables in BigQuery, you should limit the connection to run no more than once per day, because BigQuery partitioned tables do not support multiple updates per day.

  1. Select a segment of BlueConic profiles that you would like to export.
  2. Select the Google BigQuery location for the export, which is the dataset where you want to store exported data. Choose whether you want to replace the table on every run, or if you want to add a partition to the table every run.
  3. Map the BlueConic profile property data that you want to export to the fields of the Google BigQuery table. 

    Click the BlueConic icon to open a drop-down menu where you can select the type of information you want to export. Pick one of:

    • Profile property: The value of a specific profile property
    • BlueConic profile identifier: The unique identifier for a profile
    • Associated segments: All segments, or a selection of one or more specific segments that the profile is associated with
    • Associated lifecycles: All lifecycles this profile is associated with. Learn more about Lifecycles.
    • All viewed interactions (all time): The interactions that the profile has seen
    • Permission level: The permission level set in the profile
    • Text value: Enter any static text
      BlueConic-data-export-mapping-options.png
      Select Add mapping or Add multiple mappings to add one or more mappings.
  4. Optionally choose to export Timeline event data from BlueConic to a BigQuery table. Here you can map BlueConic Timeline events to relevant fields in Google BigQuery.
    Exporting profile properties with events: An additional mapping below the Timeline event mappings lets you export BlueConic profile property data with the event data.
    Export-to-nested-BigQuery-tables.png
    Sending events to multiple nested tables:
    You can choose to export events to multiple nested tables in BigQuery. Use the link icon to link tables together through IDs that exist in both tables.
    In the example shown below, the Order table has a nested product table in BigQuery. BlueConic picks up these mappings and they appear in the right-hand column using dot notation to show nested entries. If BlueConic Timeline event fields do not yet appear in the BigQuery schema (for example, product > category), you can create them via mappings (product.category) in the right-hand column of the mapping table: 
    BigQuery-export-create-nested-field.pngTimeline period: To help manage the amount of event data exported, you can choose a time period for the events you want to export.
    Timeline_exports_BigQuery_Time_period.png
  5. To run the connection or to schedule a run, Save your settings and go to the Set up and run page.
    See Scheduling connections for details.

    Note: If you are sending large segments or datasets via this connection, limit the connection to run no more than once per day to avoid slowing the performance of this and other connections.

Optional: In the left panel, select Add goal and then Export goal to create another Export goal.

Privacy management

Connections can be added to Objectives, allowing for privacy management of the information that is being picked up. A connection will only process the profiles of visitors who have consented to at least one of the objectives that the connection is linked to.