This article is part of a series on the BigQuery integration:

Now BigQuery and Planhat are connected, we're going to cover how you can configure it for your specific selection of data, and start syncing data between the two systems.

You can choose whether to sync CRM data and/or usage data in the BigQuery integration. This article is divided into these two main parts:

In summary, you will be creating and configuring sync sections, and the main principles for each sync section are:

The BigQuery integration has broad and flexible CRM syncing capabilities, and you can choose which Planhat data models to sync with BigQuery. Choose from the wide range of standard models, from the main ones such as Company, End User and License, to more specialised models such as Campaign, Objective and Workspace.

You choose which Planhat models to map, and you can map as many or as few as you want.

Each sync section corresponds to 1 Planhat model and 1 BigQuery table (or view), but you can create more sync sections if you would like to map the same model or table/view again - example shown below.

The integration supports both Planhat standard and custom fields. Again, you choose which to map to your choice of BigQuery columns.

Firstly, ensure you have followed all the steps in our article on preparing BigQuery and connecting the integration.

Before you begin data mapping, make sure that you have selected your database. Also ensure the "CRM" tab is selected - this is the default tab when you first open the integration.

Click the blue "Add New Section" button to add a new sync section. Each sync section is for mapping one Planhat model to one BigQuery table or view; simply add another one when you want to map another model / table (or view) combination.

It's easy to set this up. The basic steps are:

... simple! 👍

The BigQuery integration is bidirectional for CRM data, meaning you can choose to push data to, and pull data from, each system.

You set the sync direction independently for each sync section (pair of Planhat data model plus BigQuery table/view), offering a great deal of flexibility.

For each sync section, you can choose from "Send to Provider", "Receive from Provider", or "Both Directions" - or "Not Syncing" means no data will be synced.

It's possible to change the sync direction after your initial configuration - e.g. you could initially select "Receive from Provider" when you're setting up your Planhat tenant, and then change it to "Both Directions" later on.

For data security reasons, regardless of direction, the integration does not delete records (e.g. if you manually delete an End User in Planhat, the integration won't delete the corresponding data in BigQuery), but records can be created and updated.

For each sync section, you will see boxes for IDs under the Planhat model and BigQuery table/view.

The ID (key) on the Planhat side could be Source ID or External ID, or alternatively email address for End User (your customers) and User (your colleagues). These are all fields on the Planhat models, containing a unique ID to specify that particular record (e.g. the Company record for Apple). Note that "Id" in the dropdown box corresponds to the Planhat ID; see the "Important to note" box at the bottom of this section.

For the ID (key) on the BigQuery side, select a suitable column from the dropdown menu. The ID/key must be unique and text. The name of the ID in BigQuery can be different, depending on your own system.

Here are a couple of examples of what this could look like in the integration:

For each of the sync sections, you can set up field mapping: link up specific Planhat fields (standard or custom fields) with specific BigQuery columns. (Note that the integration does not create fields - it only syncs data between them - so you would need to create any required custom fields first before selecting them in the integration.)

The field mapping for a sync section will look something like this:

This is very simple to set up.

If you want to delete a field mapping row, simply click the red "x" on the right-hand side.

The BigQuery integration also supports syncing of usage (time-series) data. This is always one-way: from BigQuery into Planhat.

Before you start mapping usage data, ensure you have followed all the steps in our article on preparing BigQuery and connecting the integration, and have selected your database.

To set up the usage data configuration, make sure you have selected the "USAGE" tab in the integration:

Simply click the blue "Add New Section" button to add a new sync section, and then follow the steps to complete the form.

Here are a couple of examples showing what a completed (and saved) sync section can look like:

Click the images to view them enlarged

Here we'll give you some tips on which properties on the Planhat side (left-hand column) you should map when setting up a sync section for User Activities. You can also refer to our API documentation for further details of User Activity properties.

As a minimum, you should map:

If each row of your table is a count of events, rather than a single event, you will also need to map "count".

Additionally, if you do not want the data to be synced to today's date, you will need to map "date".

Here's a typical example of field mapping for User Activities:

Additionally, if you wish to map User Activities to the Project or Asset models, you will need to map "assetExtId" or "projectExtId" as appropriate - this is the External ID for the relevant model in Planhat.

Here we'll give you some tips on which properties on the Planhat side (left-hand column) you should map when setting up a sync section for Custom Metrics. You can also refer to our API documentation for further details of Custom Metric properties.

As a minimum, you should map:

Additionally, if you do not want the data to be synced to today's date, you will need to map "date".

Here's a typical example of field mapping for Custom Metrics:

In Planhat, it's possible for new End Users to be automatically created from User Activities, if those End Users can be matched with an existing Company in Planhat. This is the case for syncing User Activities via the API or any suitable integration, including BigQuery.

New End Users can be matched with existing Companies via the domain of their email address, or via the Company External ID if stated.

If you would prefer that End Users are not created automatically in this way, speak to your CSM or TAM, and they can disable this feature in your tenant. Note that this applies generically to creating new End Users from User Activities, rather than specifically BigQuery.

For more information on field mapping of "Company Id" to use different ID types in BigQuery, see here - the article was written for Snowflake, but the same principles apply to BigQuery.

[Tag for search purposes: Big Query]