BigQuery

BigQuery is the supported way to import product usage from Google Cloud. Sonora reads a single table or view per product, joining it to your existing customers via an external ID column.

Authentication uses a Sonora-owned Google Cloud service account. You grant that service account read access to the dataset; no credentials are exchanged.

What Sonora reads

A BigQuery connection imports product usage only. Customer records have to come from a CRM connection (Salesforce or HubSpot) — BigQuery usage rows then match to those customers via an external ID column.

Sonora treats three columns as fixed metadata: account ID, date, and (optionally) product. Every other numeric column is imported as a usage metric.

Shape your usage table

Sonora imports aggregated usage, not raw events. Each row is one account on one date, and each numeric column is a value you have already computed for that account. Sonora never adds rows together, so an event, click or session log does not work: each event would be read as the account’s whole value for that day. Roll the events up in a view and point Sonora at the view.

Column	Holds
Account ID	The Salesforce Account or HubSpot Company ID of the account the row belongs to
Date	The day the row covers, or the first day of the week or month it covers
Product (optional)	The product name, when one table carries several products
Every other numeric column	One metric each, named after the column

How to aggregate depends on what the column counts:

• Counts (events, API calls, reports run). Write one value per day. Sonora adds days up into weeks and months.

• Snapshots (seats, storage, licenses). Write one value per day. Set the metric’s aggregation to Last so a month shows its closing value.

• Distinct users (active users, active admins). Count each period in your warehouse. Weekly active users are not the sum of daily active users, because someone active on Monday and Tuesday is one weekly user, not two. Sonora cannot recover distinct users from daily totals, so write a separate column per period:

  • the daily count on every row,
  • the weekly count only on the row for the week’s Monday (ISO weeks),
  • the monthly count only on the row for the month’s 1st.

  Leave the other rows empty; Sonora skips empty cells. This is the same day, week and month split Sonora computes for Mixpanel.

For example:

account_id	date	events	active_users_day	active_users_week	active_users_month
0015g00000AbCdE	2026-09-28	1284	41	88
0015g00000AbCdE	2026-09-29	1102	37
0015g00000AbCdE	2026-09-30	967	35
0015g00000AbCdE	2026-10-01	1350	44		131

September 28 is a Monday, so its row carries that week’s count. October 1 starts a month, so its row carries October’s. Every sync re-reads the whole table, so the current week and month can hold their count so far and update as the period fills in.

A view that builds this table from an event log:

CREATE OR REPLACE VIEW `product_usage.account_usage` AS
WITH activity AS (
  SELECT account_id, user_id, DATE(event_timestamp) AS day
  FROM `analytics.events`
)
SELECT
  account_id,
  date,
  SUM(events) AS events,
  SUM(active_users_day) AS active_users_day,
  SUM(active_users_week) AS active_users_week,
  SUM(active_users_month) AS active_users_month
FROM (
  SELECT account_id, day AS date, COUNT(*) AS events,
    COUNT(DISTINCT user_id) AS active_users_day,
    NULL AS active_users_week, NULL AS active_users_month
  FROM activity GROUP BY 1, 2
  UNION ALL
  SELECT account_id, DATE_TRUNC(day, ISOWEEK), NULL, NULL, COUNT(DISTINCT user_id), NULL
  FROM activity GROUP BY 1, 2
  UNION ALL
  SELECT account_id, DATE_TRUNC(day, MONTH), NULL, NULL, NULL, COUNT(DISTINCT user_id)
  FROM activity GROUP BY 1, 2
)
GROUP BY account_id, date

A view that reads another dataset (here analytics) needs that dataset shared with Sonora’s service account too, or the view authorized on it.

Sonora creates every new column with Sum aggregation. After the first sync, open Settings → Products and, for each active-user metric, set Aggregation to Last and Chart tabs to the one period the column counts (active_users_week → Week). The chart then never rolls one period’s count into another.

If you can’t connect BigQuery directly, see Product usage via a CSV for an alternative.

Prerequisites

• roles/bigquery.admin or roles/owner on the Google Cloud project containing your dataset
• A configured customer source in Sonora (Salesforce or HubSpot) to match account IDs against

Grant Sonora’s service account access

Sonora connects as turbine@sonora-production.iam.gserviceaccount.com with the BigQuery Data Viewer role. Pick whichever method fits your workflow.

Option A: Cloud Console

1. Open the BigQuery console and select your dataset.
2. Click Share → Manage permissions → Add principal.
3. Paste turbine@sonora-production.iam.gserviceaccount.com.
4. Assign BigQuery Data Viewer (roles/bigquery.dataViewer).
5. Save.

Option B: gcloud (project-level)

This grants access to every dataset in the project. Replace YOUR_PROJECT:

gcloud projects add-iam-policy-binding YOUR_PROJECT \
  --member="serviceAccount:turbine@sonora-production.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer"

Option C: Terraform (dataset-level)

resource "google_bigquery_dataset_iam_member" "sonora_viewer" {
  project    = "your-project"
  dataset_id = "your_dataset"
  role       = "roles/bigquery.dataViewer"
  member     = "serviceAccount:turbine@sonora-production.iam.gserviceaccount.com"
}

Configure customer matching in Sonora

In Settings → Integrations → BigQuery → Configure, set the Customer External ID Source to whichever CRM owns the IDs in your BigQuery table:

• salesforce or salesforce_oauth for Salesforce Account IDs
• hubspot for HubSpot Company IDs

If this isn’t set, product usage imports will fail.

Share connection details

Send your Sonora contact:

Field	Example
Project	acme-analytics-prod
Dataset	product_usage
Table	account_usage

Find the project ID in the Cloud Console URL, or run gcloud config get-value project.

Troubleshooting

“Access denied” when Sonora queries — Verify the grant: bq show --format=prettyjson YOUR_PROJECT:YOUR_DATASET | grep turbine. The service account should appear in the access list with role READER. If it isn’t there, re-grant access.

“Not found” — Project, dataset, and table names are case-sensitive in BigQuery. Confirm each one matches exactly.

Empty results after sync — Check that the IDs in your usage table actually come from the source you configured. Sonora reports zero rows when the external ID source is mismatched, even when the BigQuery query itself returns data.