BigQuery

BigQuery is Google's fully managed, serverless data warehouse that lets you analyze massive datasets using standard SQL. With Coupler.io, you can run custom SQL queries against your BigQuery datasets and automatically push results to Google Sheets, Excel, BigQuery itself, or AI destinations like Claude and ChatGPT.

Why connect BigQuery to Coupler.io?

• Automate reporting — Run scheduled queries and sync results directly to Google Sheets without manual downloads

• Custom SQL queries — Write any SQL query to extract, transform, and filter exactly the data you need

• Real-time insights — Keep dashboards and reports fresh with automated data flows

• Multiple destinations — Send results to Google Sheets, Excel, BigQuery, Looker Studio, or AI platforms like ChatGPT and Gemini

Prerequisites

Before connecting BigQuery, you'll need:

• A Google Cloud Platform (GCP) project with BigQuery enabled

• A Service Account with the following roles assigned:

  • BigQuery Data Viewer — to read data from datasets

  • BigQuery Job User — to run queries

• A JSON key file for your Service Account (downloaded from GCP)

• Access to at least one BigQuery dataset

Quick start

Most users should start by creating a simple SELECT query from a single table. Once your data flow runs successfully, you can schedule it or add transformations (joins, filters, aggregations) to build more complex workflows.

How to connect

1

Create a new data flow. In Coupler.io, click "Create" and select BigQuery as your source.

2

Upload your Service Account JSON key. Click "Connect" and select your .json key file from GCP. This grants Coupler.io permission to run queries on your behalf. If you don't have a key yet, follow the GCP setup guide to create one.

3

Enter your SQL query. In the "SQL query" field, write a standard SQL query to fetch the data you need. For example: SELECT order_id, customer_name, order_date FROM your_dataset.orders WHERE order_date >= CURRENT_DATE()

4

Choose your destination. Select where you want the data to go — Google Sheets, Excel, another BigQuery table, or an AI destination like Claude or ChatGPT.

5

Run the data flow. Click "Run" to execute your query and send the results to your destination. A successful manual run is required before you can schedule the data flow.

Common use cases

Use case	Example
Weekly reporting	Query sales data from the last 7 days and push to a Google Sheet for team review
Data transformation	Use SQL to aggregate metrics, join multiple tables, and clean data before exporting
Real-time dashboards	Feed filtered BigQuery results into Looker Studio or Google Sheets for live dashboards
Data backup	Export BigQuery tables to Google Sheets for archival or sharing with non-technical users
AI-powered analysis	Send query results to ChatGPT or Claude for automated insights and summaries

BigQuery data overview

With BigQuery as a source in Coupler.io, you have complete flexibility to query any data in your BigQuery datasets using standard SQL. Unlike pre-built data sources with fixed entities, BigQuery lets you write custom queries to extract exactly what you need.

How BigQuery works in Coupler.io

You provide a SQL query, and Coupler.io runs it against your BigQuery datasets and returns the results. The data structure depends entirely on your SQL — you control which tables, columns, and rows are included.

Example queries

Basic table export:

SELECT * FROM project_id.dataset_name.table_name

Filtered and aggregated data:

SELECT
  DATE(order_date) as date,
  product_category,
  COUNT(*) as order_count,
  SUM(revenue) as total_revenue
FROM project_id.dataset_name.orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY date, product_category
ORDER BY date DESC

Joining multiple tables:

SELECT
  o.order_id,
  c.customer_name,
  p.product_name,
  o.quantity,
  o.order_date
FROM project_id.dataset_name.orders o
JOIN project_id.dataset_name.customers c ON o.customer_id = c.customer_id
JOIN project_id.dataset_name.products p ON o.product_id = p.product_id
WHERE o.order_date >= CURRENT_DATE()

Common metric combinations

Here are patterns you might combine in a single query:

• Sales metrics — Order count, revenue, average order value, customer acquisition cost

• Inventory metrics — Stock levels by warehouse, reorder points, turnover rates

• Performance metrics — Page views, conversion rates, user retention, churn rate

• Financial metrics — Gross profit, COGS, margin percentage, cash flow

Use cases by role

Marketers

Campaign performance reporting — Query your marketing database to pull conversion rates, cost per acquisition, and ROI by campaign. Export daily or weekly summaries to Google Sheets for stakeholder reviews.

Audience segmentation — Use SQL to segment customers by behavior, demographics, or purchase history, then sync the results to a sheet for email list management or ad audience uploads.

Attribution tracking — Join customer interactions across multiple tables (web events, email opens, social clicks) to build attribution reports that feed into Looker Studio dashboards.

Finance teams

Budget vs. actual reporting — Query budget data and actual spend from separate BigQuery tables, join them, and automatically export variance reports to Google Sheets each month.

Consolidated P&L statements — Aggregate revenue, COGS, and operating expenses across business units using GROUP BY, then push to a master financial sheet.

Cash flow forecasting — Pull historical payment and receivables data, calculate aging buckets with CASE statements, and export to a rolling forecast sheet.

Sales operations

Pipeline tracking — Query deal data by stage and rep, calculate win rates and average deal size, and sync weekly to a sheet for sales leaders.

Territory performance — Join sales rep, account, and revenue data to build territory P&L reports; export to Looker Studio for real-time visibility.

Quota vs. actual — Pull YTD sales by rep against quota targets, calculate attainment %, and automatically email summaries via ChatGPT-generated insights.

Platform-specific notes

• SQL dialect — BigQuery uses standard SQL with some Google-specific functions. View the full reference.

• Project and dataset references — Always include your project ID and dataset name in table references: project_id.dataset_name.table_name

• Query limits — Large queries may take time to run. Consider using LIMIT clauses during testing and filtering by date ranges in production queries.

• Permissions — Your Service Account must have read access to all datasets referenced in your query. If a query fails with "Access Denied", verify the Service Account has the BigQuery Data Viewer role on that dataset.

• Billing — BigQuery charges for bytes scanned. Optimize queries by selecting only needed columns and filtering early to reduce costs.

• Nested data — BigQuery supports nested/repeated fields. You may need to use UNNEST() to flatten arrays in your queries.

BigQuery common issues

Connection issues

"Access Denied: BigQuery Permission denied while getting Drive credentials"

This error occurs when Coupler.io's service account doesn't have permission to access your BigQuery datasets or the destination Google Sheet.

Fix:

1. Copy the service account email from the error message (e.g., [e-mail])

2. In Google Cloud Console, go to your BigQuery dataset → Sharing → Add Principal

3. Paste the service account email and grant it the BigQuery Data Viewer role

4. If writing to Google Sheets, share the sheet directly with the same service account email

5. Run the data flow again

"User does not have bigquery.jobs.create permission in project"

Your Service Account is missing the BigQuery Job User role, which is required to execute queries.

Fix:

1. Go to Google Cloud Console → IAM & Admin → Roles

2. Find your Service Account

3. Click Edit and add the BigQuery Job User role

4. Save and retry the data flow

"Sorry, we cannot connect to Cloud Services without a project ID"

The JSON key file you uploaded is missing required fields or is corrupted.

Fix:

1. Download a new Service Account JSON key from Google Cloud Console

2. Delete the old connection in Coupler.io

3. Re-upload the new JSON key

4. Retry the data flow

Missing data

Query returns no results or fewer rows than expected

Your SQL query may be filtering out data incorrectly or referencing the wrong table.

Fix:

1. Run the same SQL query directly in BigQuery's web UI

2. Check your WHERE clause — verify dates and filter conditions are correct

3. Verify the table name includes the full path: project_id.dataset_name.table_name

4. Check for typos in column names (BigQuery is case-sensitive for column references)

5. If using date filters, confirm the date range is not excluding today's data

Data duplicates when using "Append" mode

When appending data, Coupler.io will add new rows even if they already exist in the sheet.

Fix:

1. If you need to replace all data each run, use Replace mode instead of Append

2. If using Append, filter your query with a date range to only fetch new/updated records since the last run

3. Example: WHERE updated_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)

Permission errors

"Access Denied: Project X: User does not have [permission] in project"

The service account has the wrong permissions or is trying to access a dataset it doesn't have access to.

Fix:

1. Verify the Service Account has BigQuery Data Viewer role on the specific dataset (not just the project)

2. If querying multiple datasets, the service account needs viewer access to all of them

3. Go to each dataset → Sharing → add the service account with BigQuery Data Viewer role

4. Retry the data flow

"Files /gdrive/id/... not found" when writing to Google Sheets

Coupler.io cannot find your destination Google Sheet.

Fix:

1. Verify the sheet URL is correct and the sheet is in your Google Drive

2. Share the sheet explicitly with the service account email from the error

3. Make sure the sheet is not in a shared drive owned by someone else without your access

4. Try creating a new, simple test sheet in your Drive and retry

Data discrepancies

Data in Coupler.io import doesn't match BigQuery

The query may be returning incorrect data, or the destination range is too small.

Fix:

1. Run your SQL query directly in BigQuery to confirm the expected result

2. If using a cell range (e.g., A1:B10) in Replace mode, verify the range is large enough to hold all results

3. Check for data type mismatches (e.g., numbers stored as strings)

4. If the query is correct in BigQuery but wrong in Coupler.io, contact support with your query and a screenshot of both

Only part of the data shows up in Google Sheets

You may have used a cell range that's too small, or the append range is incorrect.

Fix:

1. In Replace mode, use a large range or just specify the starting cell (e.g., A1) to auto-fit

2. In Append mode, set the range to an empty row where you want data to start (e.g., A2 for row 2)

3. Avoid specifying exact ranges like A1:B10 if your data might exceed that size

Rate limits

Queries are slow or timing out

Large queries or scans can take time in BigQuery, especially if scanning many bytes.

Fix:

1. Optimize your SQL: select only needed columns (avoid SELECT *)

2. Add WHERE clauses to filter by date or other dimensions early

3. Break very large queries into smaller, separate data flows

4. Check BigQuery's query performance in the web UI to identify slow operations

5. Consider materializing frequently-used aggregations as BigQuery views or tables

"Quota exceeded" or billing errors

Your GCP project may have hit a quota limit or billing is not enabled.

Fix:

1. Verify billing is enabled for your GCP project (go to Billing in Google Cloud Console)

2. Check your GCP quotas: Quotas → filter for "BigQuery"

3. If you've hit a quota, request a quota increase

4. Review your BigQuery usage in BigQuery Reports to identify expensive queries

5. Reduce query volume or optimize query efficiency to lower costs

BigQuery FAQ

Can I use BigQuery as both a source and destination?

Yes. You can query one BigQuery dataset (source) and write results to another BigQuery table (destination). This is useful for building data pipelines, creating aggregated tables, or copying data between projects.

How do I reference multiple tables in my query?

Use JOIN to combine data from multiple tables. Always include the full path (project.dataset.table):

SELECT o.order_id, c.customer_name, p.product_name
FROM project.dataset.orders o
JOIN project.dataset.customers c ON o.customer_id = c.customer_id
JOIN project.dataset.products p ON o.product_id = p.product_id

If the tables are in different datasets, use the full reference for each.

What SQL dialect does BigQuery use?

BigQuery uses standard ANSI SQL with some Google-specific extensions. Learn more in the BigQuery SQL reference. Most common SQL patterns (SELECT, WHERE, GROUP BY, JOIN, UNION) work as expected.

Can I parameterize my query with dynamic values?

Coupler.io doesn't currently support dynamic parameters in the SQL query field itself. However, you can use Coupler.io's Join transformation to combine BigQuery results with other data sources, or use Append mode to build running logs over time. For more advanced use cases, consider using BigQuery views to encapsulate parameterized logic.

What happens if my query returns 0 rows?

Coupler.io will still run successfully and send an empty result to your destination. If using Replace mode, it will clear the destination range (minus headers). If using Append mode, no new rows will be added. This is normal behavior — check your query filters and date ranges if you expected data.

How do I avoid duplicates when using Append mode?

Use date filtering in your SQL query to only fetch new or recently updated records:

SELECT * FROM project.dataset.orders
WHERE updated_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

This way, each scheduled run only appends truly new data. Alternatively, use Replace mode if you want a fresh snapshot each time.

How do I reduce BigQuery costs?

BigQuery charges for bytes scanned (not returned). To reduce costs:

• Select only needed columns (avoid SELECT *)

• Add WHERE clauses to filter early

• Partition and cluster tables by frequently-filtered columns

• Use date ranges to limit historical data scans

• Consider scheduling less frequently if the data doesn't change daily

See the Performance optimization section in Best Practices for more details.

What if my Service Account loses permission to a dataset?

Your data flow will fail with "Access Denied." To fix it, go to the dataset's Sharing settings in Google Cloud Console and grant the service account the BigQuery Data Viewer role again. Then run the data flow manually.

Can I use nested or repeated fields in BigQuery?

Yes, but you may need to flatten them using UNNEST(). For example:

SELECT order_id, customer_name, item
FROM project.dataset.orders,
UNNEST(items) as item

This converts array/nested fields into flat rows that Google Sheets can display.

How do I troubleshoot a slow or timed-out query?

1. Run the same query in BigQuery's web UI — check the execution time and bytes scanned

2. Optimize by selecting fewer columns and filtering more aggressively

3. Break the query into smaller parts and test each separately

4. Use LIMIT during development to test quickly

5. Check for expensive operations like unindexed JOINs or full table scans

Refer to Common Issues for more troubleshooting steps.

Can I schedule the same query multiple times per day?

Yes. In Coupler.io, you can set hourly, 6-hourly, or any frequency you prefer. Keep in mind that more frequent queries increase BigQuery costs. Use date-filtered queries to minimize bytes scanned on each run.

What's the difference between Replace and Append modes?

Replace mode — Clears the destination range and writes all results fresh each time. Use this for daily snapshots.

Append mode — Adds new rows below existing data without clearing. Use this for running logs or time-series data. Always combine with date filtering to avoid duplicates.

See the Data refresh and scheduling section in Best Practices for recommendations on when to use each mode.

Can I send BigQuery results to AI platforms like ChatGPT or Claude?

Yes! Coupler.io supports AI destinations including ChatGPT, Claude, Cursor, Gemini, Perplexity, and OpenClaw. You can send query results to these platforms for automated analysis, summarization, or insight generation. Select the AI destination when configuring your data flow.