Google BigQuery integration

Developer assistance needed

You’ll need help from your development team or Google Cloud administrator to set up the integration. Please share this guide with them.

The Google BigQuery integration streams selected Pushwoosh message events to your BigQuery dataset. Use it to analyze push, email, and SMS lifecycle events in BigQuery, build custom reports, or connect the data to your downstream analytics workflows.

Integration overview

Prerequisites

Prepare the following before you open the integration setup.

1. Use a Google Cloud project with billing enabled. Free Trial credits are supported. BigQuery Sandbox is not enough because the Storage Write API requires billing.

2. Make sure you have a paid Pushwoosh account.

Pricing

You pay Google directly for BigQuery usage. Pushwoosh does not charge for the integration itself.

For current rates, free tiers, and regional details, see BigQuery pricing.

Costs can include:

• Data ingestion: Pushwoosh streams events with the BigQuery Storage Write API.
• Storage: BigQuery stores the rows written to your destination table.
• Queries: BigQuery charges for queries based on your selected pricing model.

Integration type

Source: Data is sent from Pushwoosh to your BigQuery dataset.

Supported platforms

Pushwoosh streams events from iOS, Android, Huawei, Chrome, Safari, Firefox, and Web platforms.

Synchronized entities

Selected push, email, and SMS lifecycle events are streamed to BigQuery. Pushwoosh writes one row per selected event to the destination table.

Use cases

• Near real-time message analytics: analyze push, email, and SMS lifecycle events in BigQuery shortly after they are processed in Pushwoosh.
• Custom reporting: build BigQuery reports for selected event kinds, applications, campaigns, and message identifiers.
• Downstream data workflows: connect Pushwoosh event data to your analytics, reporting, or data processing workflows.

How the integration works

After you save the configuration, Pushwoosh begins streaming selected message events to your BigQuery table in near real time. For every message event that flows through Pushwoosh, the system checks whether the event kind is selected in your configuration.

If it is, Pushwoosh appends a new row to your destination table. If the table does not exist yet, Pushwoosh creates it automatically using the schema described below. Events typically appear in BigQuery within 30 seconds of being processed in Pushwoosh.

Set up the integration in Google Cloud

Choose a Google Cloud project

Sign in to Google Cloud Console, then pick or create the project that will own the BigQuery dataset.

提示

Note the Project ID. Use the short identifier, for example my-company-12345, not the human-readable project name.

Enable the required APIs

In Google Cloud Console, go to APIs & Services → Library and enable these APIs:

• BigQuery API
• BigQuery Storage API

Pushwoosh uses these APIs to create the destination table and stream events into BigQuery.

Create a Service Account

Pushwoosh uses the Service Account to write events to your BigQuery dataset.

1. Go to IAM & Admin → Service Accounts.

2. Click Create service account.

3. In Service account name, enter a name, for example, pushwoosh-bigquery.

   Google Cloud automatically generates the Service account ID from the name.

   

4. Click Create and continue.

Grant IAM roles

1. Grant the Service Account these IAM roles:

   • BigQuery Data Editor: lets Pushwoosh create the table and append rows.
   • BigQuery User: lets Pushwoosh use the Storage Write API.
   

注意

You can attach the roles at the project level or at the dataset level. Project-level access is the easiest to set up. Dataset-level access is more restrictive and recommended for production.

2. Click Continue.

3. Click Done.

Create a JSON key

Pushwoosh uses the JSON key to authenticate as the service account.

1. Open the service account you created.

2. Go to Keys → Add key → Create new key.

3. Select JSON.

Google Cloud downloads the JSON key file to your computer.

警告

Keep the JSON key file safe. Anyone with this file can write to your BigQuery project. Do not commit it to git or share it over chat. Pushwoosh stores the key encrypted at rest and does not expose it through the UI after upload.

Create a dataset

The dataset is where Pushwoosh stores the streamed event table.

1. In Google Cloud Console, open BigQuery.

2. In Explorer, select the project you prepared for the integration.

3. Click Create dataset.

4. In Dataset ID, enter a dataset ID, for example pushwoosh_data.

5. In Data location, select the dataset region.

6. Click Create dataset.

Configure the integration in Pushwoosh

1. In your Pushwoosh account, go to Settings → 3rd Party Integrations for the application you want to connect.

2. Find Google BigQuery in the list of available services and click Configure.

3. Fill in the configuration fields.

• GCP Project ID: enter the project ID from Google Cloud, for example my-company-12345.
• Service Account JSON: paste the full contents of the JSON key file you downloaded from Google Cloud.
• Dataset ID: once GCP Project ID and Service Account JSON are filled, Pushwoosh fetches the datasets your Service Account can access. Select the destination dataset. If the dropdown is empty, check that the Service Account has access and that the dataset exists in the project you specified.
• Dataset region: select the region of your BigQuery dataset.
• Table name: leave blank to use the default pushwoosh_events table. Pushwoosh creates the table with the schema described below.
• Events: select the events you want to stream. You can change this list later.
• Stream events to BigQuery: enable this toggle. Turn it off to pause streaming without deleting the configuration.

4. Click Test connection.

Pushwoosh validates the credentials against BigQuery without writing data.

You may see one of these connection statuses:

• Connection successful: the credentials work and the Service Account can access the dataset.
• auth_failed: the JSON key is invalid or revoked.
• dataset_not_found: the dataset ID is incorrect or the Service Account cannot access it.
• missing_permission: the Service Account is missing one of the required roles.

5. Click Apply.

Pushwoosh saves the configuration and starts using it within about 30 seconds. After that, selected events start streaming to BigQuery.

Verify the integration

1. Send a test push, or trigger another message that produces one of the event kinds you selected.

2. Wait about 30 seconds.

3. Open BigQuery Studio.

4. Go to your project, then open the dataset and the destination table you configured. If you left Table name blank, open pushwoosh_events.

5. Click Preview.

You should see the event row in the table.

Table schema

Pushwoosh writes each selected event as a separate row in the destination table. To make queries faster and easier to filter, the table is partitioned by day using timestamp and clustered by app_id and event_kind.

Field name	Type	Description
event_kind	STRING	Pushwoosh event kind, for example Push Sent or Email Opened.
message_id	STRING	Pushwoosh message code, such as the campaign or message identifier.
device_id	STRING	Pushwoosh hardware ID of the device that produced the event.
user_id	STRING	Your external user ID if known. Empty for anonymous devices.
timestamp	TIMESTAMP	Event time in UTC.
app_id	STRING	Pushwoosh application code.
platform	STRING	Source platform, for example ios, android, or web.
properties	JSON	Additional event fields. Use JSON_VALUE to query fields, as shown below.

Query properties

The properties column stores additional event fields as JSON. Use JSON_VALUE to extract individual fields in your queries.

For example, to see which campaigns drove the most opens over the last 7 days, click + to create a new query, paste the SQL below, and click Run.

SELECT
  event_kind,
  JSON_VALUE(properties, '$.campaign_id') AS campaign_id,
  COUNT(*) AS events
FROM `your-project.your_dataset.pushwoosh_events`
WHERE event_kind = 'Push Opened'
  AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY events DESC

To review event counts for the last hour, run this query:

SELECT
  event_kind,
  COUNT(*) AS events
FROM `your-project.your_dataset.pushwoosh_events`
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY event_kind
ORDER BY events DESC

Update the integration

Rotate the Service Account key

1. In Google Cloud Console, go to IAM & Admin → Service Accounts.

2. Open your Service Account.

3. Go to Keys and create a new JSON key.

4. Keep the old key active until you confirm the new key works.

5. In Pushwoosh, open the Google BigQuery configuration modal.

6. Paste the new JSON into Service Account JSON.

7. Click Apply.

Pushwoosh validates the new key, replaces the stored credential, and starts using it after the next configuration reload, which takes about 30 seconds.

After you confirm that events are still flowing, delete the old key in Google Cloud Console.

Change the destination dataset or table

1. In Pushwoosh, go to Settings → 3rd Party Integrations.

2. Open Google BigQuery settings.

3. Select a different dataset or enter a new table name.

4. Click Apply.

Pushwoosh reopens the stream with the new destination within about 30 seconds. Rows already written stay in the old table. Pushwoosh does not backfill historical data.

To keep the stored Service Account key unchanged when you update other settings, leave Service Account JSON empty before you click Apply.

Troubleshooting

Issue	What to check
Test connection fails with auth_failed	The Service Account JSON is malformed or the key has been revoked in Google Cloud. Create a new key and paste the full JSON file again. The file starts with {, ends with }, and contains a private_key block.
Test connection fails with dataset_not_found	The Dataset ID is misspelled or does not exist in the project you specified. Dataset IDs are case-sensitive. Select the dataset from the dropdown to avoid typos.
Test connection fails with missing_permission	The Service Account is missing BigQuery Data Editor or BigQuery User. Grant both roles at the project level, or grant them at the dataset level for more restrictive access.
Test connection passes, but no rows appear in BigQuery	Wait at least 30 seconds. Check that the event kind you are sending is selected in Events. For example, if only Push Opened is selected and nobody opens the push, no rows appear.
Configuration looks correct, but the modal shows empty fields	Reload the page. The configuration is fetched on each modal open and cached for 30 seconds by the underlying service. If you just saved the settings, wait a moment and open the modal again.

注意

Pushwoosh records Push Sent, Email Sent, and SMS Sent regardless of delivery status so BigQuery aggregates match canonical Pushwoosh statistics. For Delivered, Opened, Bounced, and Unsubscribed, Pushwoosh records only successful events.

FAQ

Can I use a free Google Cloud account?

Yes, as long as billing is enabled on the project. The Free Trial credits are enough to run this integration at typical volumes for the full trial period. BigQuery Sandbox without billing will not work because the Storage Write API requires billing.

Does Pushwoosh see my BigQuery data?

No. The Service Account credential you upload authorizes Pushwoosh to write to the dataset you select. Pushwoosh does not read from your dataset and has no access to the rest of your project.

Can I export to multiple BigQuery datasets?

One destination is supported per application. If you need the same events in two datasets, set up a BigQuery scheduled query in your project to copy data from pushwoosh_events into another table.

Can I change the table schema?

The schema is fixed across all customers. If you need additional columns, extract them from the properties JSON in your own views or scheduled queries.

What happens if I disable the integration temporarily?

Turn off Stream events to BigQuery and click Apply. Pushwoosh stops appending events for this application within about 30 seconds.

Events produced while the integration is off are not buffered or backfilled when you turn it back on. Pushwoosh keeps the configuration, including credentials, dataset, and event selection.

How do I delete the integration completely?

Contact support@pushwoosh.com to delete the integration configuration. The dataset and rows already written to BigQuery remain in your Google Cloud account.

Are there delivery guarantees?

The integration uses at-least-once delivery. Under normal operation, duplicates are rare. A process restart between an append and a commit can produce a small number of duplicate rows. Deduplicate in SQL if your downstream pipeline requires exactly-once results.

Why is there no Push Clicked event?

Pushwoosh currently exposes Push Sent, Push Delivered, and Push Opened for push notifications in this integration. A dedicated push click step is not available. Email and SMS have their lifecycle events.