General 

The integration with ChurnZero creates a read-only connection between ChurnZero and your BigQuery instance. With the BigQuery integration, you can import data from BigQuery into the following objects in ChurnZero:

 

Object	Action	Matches On
Accounts	Create New and Update Existing Records	Account External ID
Contacts	Create New and Update Existing Records	Contact External ID
Events	Create New Only	N/A
Activities	Create New Only	N/A
Custom Tables	Create New and Update Existing Records	Identifier Field created on the Custom Table. If no identifier field exists it will operate as Create New only.

 

ChurnZero utilizes the BigQuery API to extract data from BigQuery. 

Authentication

The BigQuery/ChurnZero integration supports authentication using a BigQuery Service Account. We do not support OAuth authentication at this time.

Authentication in ChurnZero

To authenticate in BigQuery in ChurnZero, you'll need some information from Google: 

• Project ID: The ID of a project created in Google BigQuery.
• Dataset ID: The ID of a dataset created within Google BigQuery > Project. 
• Service Account Key File: A file generated within BigQuery that acts as the authorization mechanism.

Your Project ID can be found within BigQuery by clicking the project name at the top of the page. This will bring up a list of projects and associated IDs.

Your Dataset ID can be found by going into your BigQuery Studio and looking within the specific project. Click on the dataset you would like to integrate to see the dataset ID.

Lastly, you'll download a service account key file: 

1. In your Cloud Console Big Query Connector, click the ellipses and then select IAM and Admin>Service Accounts.
   
2. Click Create Service Account
   • When creating the service account, give BigQuery Data Viewer and Viewer access (see below). 
      
3. When you've created your service account, you should see it listed in service accounts for your project:
   
4. Click into the service account and then click Keys to add a new key. Select JSON as the key type. 
   

5. This will automatically start a file download. Add this file to the authentication area within ChurnZero.

 

Creating an Import Job

From your navigation panel, go to Admin>BigQuery. At the top of the page, you will see your authorized connection information and status information about the connection (such as the number of API calls made recently). Click Add Import Job. 

Job Info

A modal will pop up to create your new import job. 

• Name: Name of the import job.
• Description: You can add a short description to add more detail on what data the job will import. 
• BigQuery Table: The table from which you would like to import BigQuery data. 
• ChurnZero Table: The table in ChurnZero to which the BigQuery data will be imported. 

The ChurnZero table must be created before you map data using the BigQuery Integration. If you do not see your desired object in the ChurnZero Table dropdown, you will have to add a new custom table. See our Custom Fields Documentation to learn how to add new tables and fields to ChurnZero. 

Table Lookups

Database tables are often linked together via references or field lookups. For example, on your contacts table, you may not have the Account External ID of the account it is related to. Instead, you may have a lookup to your account table. Table lookups give you access to any fields referenced on that lookup table. 

Using the contacts example, you may have an Account Reference on your contact table that tells you which account that contact is associated with.

Configure a lookup table by clicking Add Lookup Configuration.

From here we will determine how to link our two tables together.

You will need to provide 3 pieces of information

• Lookup Table: The table that has the information you need. 
  • In our example, we are looking up to the Accounts table, because that has our Account External ID.
• BigQuery Field: The field on the current table (contacts) that has the field referencing your lookup table (Accounts). 
  • In our example, a field called contacts.account_ID references the associated account.
• Lookup Identifier: The field on the lookup table (accounts) matching the BigQuery field.
  • In our example, our account table has an "accounts.id" field. The value of this field on the account table matches the same value of contacts.account_id on the contact table. 

Once your lookup is configured successfully, any fields on the lookup table will be available for mapping. You can see the access below that allows us to use the "accounts.external_id" field.

 

Sync Settings

• Active: Turns the import job on or off. When the job is off, the import will not process at the scheduled time
• Allow duplicates:  ChurnZero treats events sent events sent with the same time, account, contact, and description as duplicate events.  So if I had a list of 7 "Login" events all tied to the same contact all occurring at 5 pm, having the setting ON would process 7 events, while turning it OFF would only process 1 event (with the rest being ignored as duplicates).
  • This setting is only available for Event type imports.
• Schedule: Determine the cadence at which the import jobs will run. This can happen Hourly, Daily, Weekly, Monthly or Yearly. 
• Primary Key: This is the field on the BigQuery table that will be considered the unique identifier of the record (this is usually the same as the identifier field on the record of your table). This is needed to enforce a processing order for when we make a query to Bigquery. 
• Last Modified Date: A field on the BigQuery table that tells us when the record was last modified. 
  • When the import job runs on its scheduled cadence, the integration will look at the field mapped here. Any record with a last modified date after the last import was run will be updated. This allows us to only look for new and updated records, saving the amount of data being queried on each import. 

Filter

If no filter is applied, ChurnZero will attempt to import all records on the BigQuery table. You can add a filter to place parameters around the query, limiting the records that will be returned.

Our Logic Builder is an easy way to construct a query. Click "Add Condition" to begin to construct the query. Click the "Add Condition" button to add a filter. The below example shows a filter for only pulling back records where Opportunity Closed=True AND Name CONTAINS "CS Opps".

Once you have constructed your query, you can use the Test Logic button to preview which records will be returned. This is also great for testing new filter logic, as you can evaluate the changes before setting it as your active filter logic.

If you are editing an existing filter, you can always click Revert to Saved Logic to undo any changes you've made since you last saved.

 

Field Mappings

Once you have your settings determined, it's time to map your fields. Your ChurnZero Table fields will appear on the left, and then you can select the appropriate BigQuery fields from the dropdown.

Only fields marked "Required" on the ChurnZero custom table will appear by default. Click "Manage Fields" to add or remove any fields to the import job. 

Reference Fields

If you want a record to be visible on an Account or Contact record, you must have an Account and/or Contact reference field available on the ChurnZero table to map. The value should be the Account External ID or Contact External ID, respectively. If those fields do not live on your BigQuery table but lookup to another table, you can use the Lookup Tables configuration to get those values. If you do not see an Account or Contact External ID field on your table, you may have to add a new Reference field type in Admin>Custom Fields.

Identifier Fields

The BigQuery Integration can update existing records on Account, Contact, and Custom Tables. For us to know which records are new vs which are existing, we need to know an identifier field. For Accounts and Contacts, that is handled by the Account and Contact External IDs. However, for Custom Tables, there needs to be an established identifier field. This is a unique identifier of each record, usually a database identifier. ChurnZero will then use that value to determine if we should update an existing record, or create a new one. If you do not see an Identifier type field on your table, you will have to add it in Admin>Custom Fields. 

Date Fields

We support the following date fields from BigQuery:

• Timestamp: Includes a timezone which we preserve
• Datetime: This field type does not include a timezone. We preserve the time but interpret the timezone as the ChurnZero tenant timezone.
• Date: This field type does not include a time or timezone. We interpret interpret the time as midnight on the date sent, using the ChurnZero tenant timezone.

Managing Import Jobs

Edit Import Jobs

To edit an existing job, hover over the record and click Edit.

 

You can then edit your import job settings. If you edit an import job and add additional field mappings, you will receive a prompt asking if you would like to initiate a full sync for the next run. This will ensure that all records get updated with the new field mappings.

Initiating a full sync will reset the "Next Import" time for the job to the current time, prompting a sync of all data that meets the filter criteria within 15 minutes. If you choose to keep the partial sync, the job will run at the next scheduled cadence. 

Delete Import Jobs

If you no longer need an import job, you can delete it by clicking Delete.

This will only delete the import job. The data imported will stay in ChurnZero and would have to be deleted separately. 

Initiating a Full Sync

When you activate an Import Job, the import will then run on it's scheduled cadence. Once the next scheduled sync time comes, ChurnZero will query BigQuery for any records that meet the filter criteria and have been modified since the last sync run. This will ensure that we are only querying for newly modified records. If you want to sync all records, regardless of last modified date, you can initiate a full sync by clicking "Run" job list page.

Initiating a full sync will reset the "Next Import" time for the job to the current time, prompting a sync of all data that meets the filter criteria within 15 minutes.

Reorder Import Jobs

Some of your input jobs may have dependencies on each other. For example, you may have a job that imports contacts, but those contacts lookup to an Account. You would ideally want the Account information first so that the account exists before the contact import job runs. This is where Job Scheduling comes in. Jobs will run in the order that is set, depending on the cadence. So if you have all of your Jobs set to import hourly, once the top of the hour comes, the jobs will kick off in order. Use the "Reorder Import Jobs" button to change the order of your jobs.