Help Center

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.

Integrating BlueConic customer data with Google BigQuery

This article explains several ways you can integrate BlueConic customer data with data housed in Google BigQuery.

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.

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.
   

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.

6. Click key type JSON.
7. Click Create to download the private key file to your computer.

8. 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.
   

9. 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.
   
3. Optional: Process your data before the import.
   Here you can add a data processor to filter or transform the data during the import. For example, you can use a processor to normalize email addresses, filter data fields, or transform data during the import, before it is matched and mapped to BlueConic properties. For processors that require configuration, click the configuration icon (cog wheel) to set up the data processor fields. To add data processors to your BlueConic environment, contact your Customer Success Manager. Learn more about using data processors to filter or transform data imports.
   
4. 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.
   

5. 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.
   

   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)

    

6. Optionally import events to the BlueConic Timeline. Map the Google BigQuery fields to matching BlueConic Timeline event properties.
   

7. 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 on scheduling connections. 
   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.

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. (Optional) Transform or filter your data before the export.
   If you have data processors installed in your tenant, you can add one ore more data processors here. Data processors can transform, filter, or modify your profile and event data before the export. Contact your Customer Success Manager for more information on data processors.
4. 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
     
   • (Optional) Processor value: If you have selected one or more data processors in step 3, you can map the processor value(s) here. For example, if you are using a data processor that outputs several values, you can add them here as fields to be mapped to BigQuery.
     Select Add mapping or Add multiple mappings to add one or more mappings.
5. 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.
   Optional: If you have installed data processors in your tenant, the processor(s) you select in step 3 can filter or transform your event data.
   Exporting profile properties with events: An additional mapping below the Timeline event mappings lets you export BlueConic profile property data with the event data.
   
   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: 
   Timeline period: To help manage the amount of event data exported, you can choose a time period for the events you want to export.
   
6. To run the connection or to schedule a run, Save your settings and go to the Set up and run page. 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.
   
   See Notes about running the Google BigQuery Connection for configuration details about running Google BigQuery connections, and Scheduling connections for general information on scheduling connections.

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

Running the Google BigQuery Connection

Notes about running the Google BigQuery Connection

• Partitioning: Selecting ‘Add partition to tables every run’ for the Run setting means the connection's run date will be used for partitioning. Because this is done automatically, you cannot select a different profile property for partitioning, and you also do not need to define partition settings on the BigQuery side. The connection only requires that the BigQuery table include the fields that you are mapping.

• Data exports: If the table does not already contain data, the connection may need to run once without using the partition if the table does not already contain data. Once the table contains valid data, the connection can be run using partitioning. This is necessary because the connection will only export profiles that have been updated since the last run when using the partition setting.
• Working with large segments or datasets: 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.

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.

Google BigQuery Connection FAQs

Why am I seeing a "Provided schema does not match table" error when running an export goal?

When export data to a table in Google BigQuery, we generally recommend to let BlueConic set up the table automatically in the first run. This can prevent errors that occur when table schemas do not match between BlueConic and BigQuery. For the first run, and in general, BlueConic maps profile properties to the following BigQuery data types:

• BlueConic Date Time properties will be mapped to BigQuery DATETIME types
• BlueConic Number properties will be mapped to BigQuery INTEGER types
• All other BlueConic properties will be mapped to BigQuery STRING types

On the second run, all properties are set to be repeated unless you are mapping BlueConic profile IDs or BlueConic Timeline event IDs. Therefore, on the second run, an issue will occur if your schema is currently set to "NULLABLE" for a value.