> For the complete documentation index, see [llms.txt](https://docs.coupler.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coupler.io/sources/category/files-and-tables/google-big-query.md).

# BigQuery

Query BigQuery datasets with custom SQL and auto-sync results to Google Sheets, Excel, or AI tools via Coupler.io.

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

{% hint style="success" %}
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.
{% endhint %}

## How to connect

{% stepper %}
{% step %}
**Create a new data flow.** In Coupler.io, click "Create" and select BigQuery as your source.
{% endstep %}

{% step %}
**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](https://docs.coupler.io/sources/category/files-and-tables/google-big-query/how-to-get-google-cloud-key-file.md) to create one.
{% endstep %}

{% step %}
**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()`
{% endstep %}

{% step %}
**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.
{% endstep %}

{% step %}
**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.
{% endstep %}
{% endstepper %}

## 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:**

```sql
SELECT * FROM project_id.dataset_name.table_name
```

**Filtered and aggregated data:**

```sql
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:**

```sql
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

{% tabs %}
{% tab title="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.
{% endtab %}

{% tab title="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.
{% endtab %}

{% tab title="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.
{% endtab %}
{% endtabs %}

## Platform-specific notes

* **SQL dialect** — BigQuery uses standard SQL with some Google-specific functions. [View the full reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax).
* **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

<details>

<summary>"Access Denied: BigQuery Permission denied while getting Drive credentials"</summary>

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., `coupler-io@project-name.iam.gserviceaccount.com`)
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

</details>

<details>

<summary>"User does not have bigquery.jobs.create permission in project"</summary>

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

</details>

<details>

<summary>"Sorry, we cannot connect to Cloud Services without a project ID"</summary>

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

</details>

### Missing data

<details>

<summary>Query returns no results or fewer rows than expected</summary>

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

</details>

<details>

<summary>Data duplicates when using "Append" mode</summary>

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)`

</details>

### Permission errors

<details>

<summary>"Access Denied: Project X: User does not have [permission] in project"</summary>

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

</details>

<details>

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

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

</details>

### Data discrepancies

<details>

<summary>Data in Coupler.io import doesn't match BigQuery</summary>

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

</details>

<details>

<summary>Only part of the data shows up in Google Sheets</summary>

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

</details>

### Rate limits

<details>

<summary>Queries are slow or timing out</summary>

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

</details>

<details>

<summary>"Quota exceeded" or billing errors</summary>

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

</details>

## BigQuery FAQ

<details>

<summary>Can I use BigQuery as both a source and destination?</summary>

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.

</details>

<details>

<summary>How do I reference multiple tables in my query?</summary>

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

```sql
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.

</details>

<details>

<summary>What SQL dialect does BigQuery use?</summary>

BigQuery uses standard ANSI SQL with some Google-specific extensions. Learn more in the [BigQuery SQL reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax). Most common SQL patterns (SELECT, WHERE, GROUP BY, JOIN, UNION) work as expected.

</details>

<details>

<summary>Can I parameterize my query with dynamic values?</summary>

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.

</details>

<details>

<summary>What happens if my query returns 0 rows?</summary>

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.

</details>

<details>

<summary>How do I avoid duplicates when using Append mode?</summary>

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

```sql
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.

</details>

<details>

<summary>How do I reduce BigQuery costs?</summary>

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

{% hint style="info" %}
See the **Performance optimization** section in [Best Practices](https://docs.coupler.io/sources/category/files-and-tables/google-big-query/best-practices.md) for more details.
{% endhint %}

</details>

<details>

<summary>What if my Service Account loses permission to a dataset?</summary>

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.

</details>

<details>

<summary>Can I use nested or repeated fields in BigQuery?</summary>

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

```sql
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.

</details>

<details>

<summary>How do I troubleshoot a slow or timed-out query?</summary>

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

{% hint style="info" %}
Refer to [Common Issues](#bigquery-common-issues) for more troubleshooting steps.
{% endhint %}

</details>

<details>

<summary>Can I schedule the same query multiple times per day?</summary>

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.

</details>

<details>

<summary>What's the difference between Replace and Append modes?</summary>

**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.

{% hint style="info" %}
See the **Data refresh and scheduling** section in [Best Practices](https://docs.coupler.io/sources/category/files-and-tables/google-big-query/best-practices.md) for recommendations on when to use each mode.
{% endhint %}

</details>

<details>

<summary>Can I send BigQuery results to AI platforms like ChatGPT or Claude?</summary>

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.

</details>


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.coupler.io/sources/category/files-and-tables/google-big-query.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
