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