Google BigQuery

Documentation Index

Fetch the complete documentation index at: https://docs.conversion.ai/llms.txt

Use this file to discover all available pages before exploring further.

​

Before You Begin

• Owner or IAM Admin access in your GCP project: You’ll need to create a service account, assign roles, and generate a key. The Owner, IAM Admin, or an equivalent role is required.
• The dataset(s) you want to sync identified: Know which project, and optionally which datasets and tables, Conversion should be able to read from.
• Your GCP project ID: Found in the Google Cloud Console next to the project name (e.g. my-company-prod). You’ll need it when connecting in Step 2.

​

Step 1: Set Up BigQuery Access

​

Create a Service Account

1. In the Google Cloud Console, go to IAM & Admin → Service Accounts and create a new service account (e.g. conversion-sync).
2. Grant it the BigQuery Job User and BigQuery User roles.
3. Open the service account, go to the Keys tab, and create a new JSON key. Save the downloaded file; you’ll paste its contents into Conversion in Step 2.

​

Grant Read Access to Your Data

​

Granting Data Viewer at the project level

​

Allow Conversion’s IP Addresses

​

Step 2: Connect BigQuery to Conversion

1. In Conversion, go to Settings → CRM & Syncing → Connections.
2. Click Add BigQuery connection.
3. Fill in the connection details using the values from the earlier steps:

   Field	What to enter	Where it came from
   Name	A friendly label, e.g. Production BigQuery	Free-text, just for your reference in Conversion
   Project ID	Your GCP project ID, e.g. my-company-prod	Found in the Google Cloud Console next to the project name
   Service Account Credentials	The full contents of your JSON key file	Generated in Step 1

4. Click Connect

​

Step 3: Create a Sync

​

Writing Queries for BigQuery

• Name your columns to match Conversion fields. Conversion maps results to fields by column name, so each column in your SELECT clause must match the field key in Conversion exactly (e.g. email, first_name, userId, eventId). Use AS to alias when the source name doesn’t match.
• Always filter by last_sync_time so each run only processes changed rows. In BigQuery, wrap it with TIMESTAMP_SECONDS(); see Using last_sync_time for details.
• Select only the columns you’ll actually use, as every column scanned costs money in BigQuery.

​

Contact Sync

SELECT
  id AS userId,
  email,
  first_name,
  last_name,
  title AS job_title,
  phone,
  city
FROM `project.dataset.users`
WHERE updated_at >= TIMESTAMP_SECONDS({{last_sync_time}})

​

Custom Objects (Without Relationships)

SELECT 
  id,
  name,
  status,
  plan_tier,
  monthly_price
FROM `project.dataset.subscriptions`
WHERE updated_at >= TIMESTAMP_SECONDS({{last_sync_time}})

​

Custom Objects with Relationships

SELECT 
  -- Object fields
  p.id,
  p.name,
  p.sku,
  p.category,
  p.price,
  
  -- Contact to relate this object to
  u.email,
  
  -- Relationship-specific fields
  JSON_OBJECT(
    'role', up.role,
    'quantity', up.quantity,
    'purchased_at', UNIX_SECONDS(up.purchased_at)
  ) AS relationshipFields
  
FROM `project.dataset.user_products` up
JOIN `project.dataset.products` p ON up.product_id = p.id
JOIN `project.dataset.users` u ON up.user_id = u.id
WHERE up.updated_at >= TIMESTAMP_SECONDS({{last_sync_time}})

• Object fields (id, name, sku, category, price) are stored on the Product object
• email identifies which contact to create a relationship with
• relationshipFields contains fields stored on the relationship itself (this contact’s role, quantity, etc.)

​

Events

SELECT
  CAST(u.id AS STRING) AS userId,
  u.email,
  'purchase_completed' AS event_name,
  UNIX_SECONDS(o.created_at) AS timestamp,
  CAST(o.id AS STRING) AS eventId,
  o.total_amount,
  o.product_name,
  o.currency
FROM `project.dataset.orders` o
JOIN `project.dataset.users` u ON o.user_id = u.id
WHERE o.created_at >= TIMESTAMP_SECONDS({{last_sync_time}})

​

BigQuery SQL Reference

​

Converting Timestamps

SELECT 
  email,
  UNIX_SECONDS(created_at) AS created_at,
  UNIX_SECONDS(last_login) AS last_login
FROM `project.dataset.users`
WHERE updated_at >= TIMESTAMP_SECONDS({{last_sync_time}})

​

Using last_sync_time

WHERE updated_at >= TIMESTAMP_SECONDS({{last_sync_time}})

WHERE UNIX_SECONDS(updated_at) >= {{last_sync_time}}

WHERE updated_at >= TIMESTAMP_SECONDS({{last_sync_time}} - 300)  -- 5 minute buffer

​

Building Nested Objects with JSON_OBJECT

JSON_OBJECT(
  'role', role_column,
  'quantity', quantity_column,
  'started_at', UNIX_SECONDS(started_at_column)
) AS relationshipFields

​

Handling NULLs

SELECT 
  email,
  COALESCE(first_name, '') AS first_name,
  IFNULL(phone, '') AS phone
FROM `project.dataset.users`

​

Casting Types

SELECT 
  CAST(user_id AS STRING) AS id,
  CAST(score AS INT64) AS lead_score
FROM `project.dataset.users`

​

Querying Across Datasets

SELECT 
  u.email,
  o.order_id
FROM `project.users_dataset.users` u
JOIN `project.orders_dataset.orders` o ON u.id = o.user_id

​

Troubleshooting

​

”Permission denied” errors

• BigQuery Job User (project level)
• BigQuery User (project level)
• BigQuery Data Viewer (on each dataset you want to query)

​

“Could not connect” errors

• The service account JSON key was pasted in full, including the surrounding { and }
• Your project ID matches the project the service account belongs to
• Conversion’s IP addresses are allowed if you use VPC Service Controls

​

Sync taking too long

• Ensure you’re filtering by last_sync_time to reduce rows
• Select only the columns you need
• Use partitioned tables and filter on the partition column for time-based data
• Reduce sync frequency for large datasets

​

Duplicate data appearing

​

Frequently Asked Questions

SELECT u.email, o.order_id
FROM `my-project.users_dataset.users` u
JOIN `my-project.orders_dataset.orders` o ON u.id = o.user_id