Audience

BigQuery is Google’s fully managed, petabyte scale, low cost analytics data warehouse.

Enable the Connection

1. Enable BigQuery in your project in Google Cloud Platform.
2. From the IAM page for your project, add mparticle-gcp-clientfacing@gcp-prod-170221.iam.gserviceaccount.com as a member with the role of Project Viewer.
   

3. From the BigQuery page for your project:

   • Create a dataset for your app data to be loaded into.
   • Click on Share Dataset on the dataset page.
   • Add mparticle-gcp-clientfacing@gcp-prod-170221.iam.gserviceaccount.com with the “BigQuery Data Editor” role.
     
4. Connect your audience from the Audiences page in the mParticle dashboard. You’ll need to enter your BigQuery Project ID and Dataset ID.
   
5. This step is optional, if you want to sync data downstream using Storage API instead of Streaming API. The default Use Storage API Data Sync configuration setting is set to false and mParticle uses Streaming API endpoint for data sync. However, if you wish to use Storage API, you must first enable access to Storage API in your Google project and then set the Use Storage API Data Sync configuration setting in mParticle to true.

BigQuery Schema

All of your audience data is stored in a single BigQuery dataset. Multiple audiences can be sent to each dataset. The data sent to BigQuery is not the current state for the audience, but changes to the audience over time as users are added and removed.

For each audience, mParticle creates one table per week, showing all users added to, or removed from the audience each week. Table names are in the format {Audience Name}_{Audience ID}_{Beginning of the week in yyyyMMdd format}. Audience name is either audience or the external audience name depending on the Use External Audience Name in Table Name setting. If a table with the configured name does not exist, a new table will be created the next time memberships are uploaded.

For example, if your audience ID is 7185 and the external audience name is New Users Low Engagement,

1. If Use External Audience Name in Table Name is unchecked, data for the week beginning on March 1, 2020 will be written to a table named audience_7185_20200301.
2. If Use External Audience Name in Table Name is checked, data for the week beginning on March 1, 2020 will be written to a table named newuserslowengagement_7185_20200301.

To find the ID of an audience in the mParticle Dashboard, look for your audience name in the Audiences summary page.

Table fields

Customer ID Only Option

You can opt to only send Customer ID to BigQuery by checking Only Send Customer ID in the Configuration Settings

Sample Query for Audience Membership

You can run a query similar to the following to get audience membership at a given point in time.

-- selects only mPIDs added based on latest timestamp for each distinct mPID
WITH t1 AS
(
  SELECT DISTINCT mpid, MAX(timestamp) AS max_timestamp, isadd
  FROM `{BQ Project}.{BQ Dataset}.audience_{audience_id}_*`
  GROUP BY mpid, timestamp, isadd
)
SELECT count(mpid)
FROM t1
WHERE isadd = true

Sample Query for RECORD Data Types

You can run a query similar to the following to properly query on fields mParticle schema defines as RECORD, since they can be have multiple values such as device identifiers:

-- selects IDFA from the table, querying over all weekly tables
SELECT idfaid.id AS IDFA
FROM `{project_id}.{data_set}.audience_{audience_id}_*`, UNNEST(idfa) idfaid

Upload Frequency

The BigQuery Audience Integration uses Bulk Forwarding. Bulk Forwarding means that, instead of uploading updates to an audience in real time, mParticle compiles updates into a queue until either a given amount of time has passed since the last upload, or until a certain number of updates are waiting to be sent.

By default, mParticle uploads to BigQuery whenever at least one of the following conditions is met:

• At least 50000 messages are in the queue.
• 15 minutes have passed since the last update.

Upload frequency can sometimes be adjusted. Reach out to your mParticle Customer Success Manager if you need to discuss upload frequency.

Deleting an Audience

mParticle doesn’t delete the downstream audience when you delete an audience in mParticle.

Configuration Settings