Google BigQuery

​

Before You Begin

• Create a dedicated service account — Set up a service account specifically for Conversion with read-only access to the tables you need. This limits exposure and makes it easy to revoke access if needed.
• Use a read replica if possible — Running queries against a replica prevents sync operations from impacting your production database.
• Sync only the data you need — Select only the columns you’ll use, and always filter with last_sync_time to limit results to changed rows.

​

Step 1: Create a Service Account

1. Go to IAM & Admin → Service Accounts in the Google Cloud Console
2. Click Create Service Account
3. Enter a name (e.g., “Conversion Sync”) and description
4. Click Create and Continue
5. Add the following roles:
   • BigQuery User — Allows running queries
   • BigQuery Job User — Allows creating query jobs
   • BigQuery Data Viewer — Allows reading table data (grant this on specific datasets if you want to limit access)
6. Click Continue, then Done
7. Find your new service account in the list, click the three dots under Actions, and select Manage keys
8. Click Add Key → Create new key
9. Select JSON and click Create
10. Save the downloaded JSON file — you’ll need it when connecting to Conversion

​

Step 2: Allow Conversion’s IP Addresses

​

Step 3: Connect BigQuery to Conversion

1. In Conversion, go to Settings → Integrations → Data Warehouse
2. Click Add Integration and select Google BigQuery
3. Enter a name for this connection (e.g., “Production BigQuery”)
4. Paste the contents of your service account JSON file into the Service Account Credentials field
5. Click Connect

​

Step 4: Create a Sync

1. Click Add Sync
2. Select the destination type:
   • Contacts — Create or update contact records
   • Custom Objects — Create or update custom object records (with optional relationships)
   • Events — Record events on contacts
3. For Custom Objects, select the specific object type
4. Enter your SQL query (see examples below)
5. Click Run Query to preview results
6. Set the sync schedule (how often to run)
7. Click Enable

​

Writing Queries for BigQuery

​

Contact Sync

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

​

Custom Objects (Without Relationships)

SELECT 
  id,
  name,
  status,
  plan_tier,
  monthly_price
FROM `project.dataset.subscriptions`
WHERE updated_at > TIMESTAMP('{{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('{{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 
  u.email,
  'purchase_completed' AS event_name,
  o.created_at AS timestamp,
  CAST(o.id AS STRING) AS message_id,
  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('{{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('{{last_sync_time}}')

​

Using last_sync_time

WHERE updated_at > TIMESTAMP('{{last_sync_time}}')

WHERE UNIX_SECONDS(updated_at) > {{last_sync_time}}

​

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 User
• BigQuery Job User
• BigQuery Data Viewer (on the relevant datasets)

​

“Could not connect” errors

​

Sync taking too long

• Ensure you’re filtering by last_sync_time to reduce rows
• Select only the columns you need
• Consider reducing sync frequency for large datasets
• Use partitioned tables if your data is time-based

​

Duplicate data appearing

​

Frequently Asked Questions