BigQuery

Connect SealMetrics to Google BigQuery to unlock advanced SQL analysis, custom reporting, and seamless integration with your data warehouse and BI tools.

---

Why BigQuery?

SealMetrics dashboards cover the most common analytics needs. But when you need to go deeper, BigQuery gives you:

• Custom SQL queries on aggregated traffic, page, and conversion data
• BI tool integration with Looker, Data Studio, Tableau, or Power BI
• Cross-platform joins combining analytics with CRM, ad spend, or backend data
• Machine learning using BigQuery ML on your traffic and conversion patterns
• Long-term storage with flexible retention beyond your SealMetrics plan limits

tip

BigQuery integration is included with all plans (Growth, Scale, and Enterprise). Check your current plan in Settings → Billing.

---

How It Works

SealMetrics automatically exports your analytics data to a BigQuery dataset on a schedule you configure. The sync process:

1. Extracts data from your SealMetrics account
2. Transforms it into structured BigQuery tables
3. Loads it into your GCP project on your chosen schedule (hourly, daily, or manual)

Your data stays in your Google Cloud project — SealMetrics never stores copies outside your account.

---

Prerequisites

Before starting, make sure you have:

1. Any SealMetrics plan (Growth, Scale, or Enterprise)
2. A Google Cloud Platform (GCP) account with billing enabled
3. The BigQuery API enabled in your GCP project
4. A GCP service account (you'll create this in the next steps)

---

Step-by-Step Connection Guide

1. Create a GCP Project (if needed)

If you don't have a GCP project yet:

1. Go to console.cloud.google.com
2. Click Select a project → New Project
3. Name it (e.g., sealmetrics-analytics) and click Create
4. Make sure billing is enabled for the project

2. Enable the BigQuery API

1. In Google Cloud Console, go to APIs & Services → Library
2. Search for BigQuery API
3. Click Enable (if not already enabled)

3. Create a Service Account

The service account allows SealMetrics to write data to your BigQuery dataset securely.

1. Go to IAM & Admin → Service Accounts
2. Click + Create Service Account
3. Fill in the details:

Service Account Details:
  Name: sealmetrics-export
  ID: sealmetrics-export
  Description: Service account for SealMetrics BigQuery export

4. Click Create and Continue

4. Grant BigQuery Permissions

Assign the following roles to the service account:

Role	ID	Purpose
BigQuery Data Editor	roles/bigquery.dataEditor	Create tables and insert data
BigQuery Job User	roles/bigquery.jobUser	Run data load jobs

Click Continue and then Done.

note

For tighter security, you can create a custom role with only these permissions: bigquery.datasets.create, bigquery.datasets.get, bigquery.tables.create, bigquery.tables.get, bigquery.tables.updateData, bigquery.jobs.create.

5. Generate the JSON Key

1. Click on the service account you just created
2. Go to the Keys tab
3. Click Add Key → Create new key
4. Select JSON format
5. Click Create

A .json file will download automatically. Keep this file secure — it grants access to your BigQuery dataset.

6. Configure in SealMetrics

1. Log in to your SealMetrics account
2. Go to Settings → Integrations → BigQuery
3. Upload your JSON key file (or paste its contents)
4. Configure your dataset:

Dataset Configuration
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

GCP Project:  your-project-id (detected from credentials)

Dataset Name: [sealmetrics                    ]
              (created automatically if it doesn't exist)

Location:     [EU (europe-west1)              ▼]

The dataset uses a fixed star-schema layout: fact tables (fact_traffic_daily, fact_conversions, etc.) and dimension tables (dim_accounts, dim_countries). Table names are not configurable.

5. Choose your sync schedule:

Sync Schedule
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Sync Frequency:
  ○ Hourly
  ● Daily
  ○ Manual (sync on demand only)

Data to Export:
  ☑ Traffic (daily)
  ☐ Traffic (hourly)
  ☑ Conversions
  ☑ Microconversions
  ☑ Pages
  ☑ Landing pages
  ☐ Accounts (metadata)

Historical Backfill:
  ☑ Export historical data
    Backfill: [30] days (max 365)

6. Click Activate Integration

7. Verify the Connection

After activation, the initial sync will start. You can monitor progress in the integration status panel:

BigQuery Integration Status
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Status: ✅ Active

Last Sync:     Today at 03:15 UTC
Records Synced: 1,234,567
Next Sync:     Tomorrow at 03:00 UTC

Tables:
  fact_traffic_daily      1,023,456 rows
  fact_pages                156,789 rows
  fact_landing_pages         89,012 rows
  fact_conversions           34,567 rows
  fact_microconversions      12,345 rows

[View in BigQuery]  [Sync Now]  [Pause]

caution

The initial sync may take several hours if you enabled historical backfill. Subsequent syncs are incremental and much faster.

---

Data Available in BigQuery

SealMetrics exports a star schema: pre-aggregated daily fact tables plus dimension tables for context and JOINs. There are no raw-hit or session tables — data is aggregated by day across UTM, geo, and device dimensions. Every fact table is partitioned by date and carries sync_id/synced_at columns for auditing.

Traffic (fact_traffic_daily)

Unified daily traffic broken down by source, geo, and device.

Column	Type	Description
account_id	STRING	SealMetrics account ID
date	DATE	Aggregation day (partition key)
utm_source	STRING	UTM source
utm_medium	STRING	UTM medium
utm_campaign	STRING	UTM campaign
utm_term	STRING	UTM term
utm_content	STRING	UTM content
channel_group	STRING	Channel grouping
country	STRING	ISO country code
device_type	STRING	mobile / desktop / tablet
browser	STRING	Browser name
os	STRING	Operating system
entrances	INT64	Entrances
engaged_entrances	INT64	Engaged entrances
page_views	INT64	Page views
microconversions	INT64	Microconversion count
conversions	INT64	Conversion count
revenue	NUMERIC	Revenue

Hourly Traffic (fact_traffic_hourly)

Optional intraday granularity (opt-in). Same dimensions as fact_traffic_daily plus an hour column (0–23). This table has a 90-day partition expiration.

Pages (fact_pages)

Page-level daily metrics.

Column	Type	Description
account_id	STRING	SealMetrics account ID
date	DATE	Aggregation day (partition key)
page_path	STRING	URL path
content_grouping	STRING	Content grouping
country	STRING	ISO country code
channel_group	STRING	Channel grouping
entrances	INT64	Entrances
engaged_entrances	INT64	Engaged entrances
page_views	INT64	Page views

Landing Pages (fact_landing_pages)

Landing-page performance by source and geo.

Column	Type	Description
account_id	STRING	SealMetrics account ID
date	DATE	Aggregation day (partition key)
landing_page	STRING	Landing page path
content_grouping	STRING	Content grouping
utm_source	STRING	UTM source
utm_medium	STRING	UTM medium
channel_group	STRING	Channel grouping
country	STRING	ISO country code
entrances	INT64	Entrances
engaged_entrances	INT64	Engaged entrances
microconversions	INT64	Microconversion count
conversions	INT64	Conversion count
revenue	NUMERIC	Revenue

Conversions (fact_conversions)

Conversion events with full attribution and revenue data.

Column	Type	Description
account_id	STRING	SealMetrics account ID
date	DATE	Aggregation day (partition key)
conversion_type	STRING	Conversion label
utm_source	STRING	Attributed source
utm_medium	STRING	Attributed medium
utm_campaign	STRING	Campaign
utm_term	STRING	UTM term
utm_content	STRING	UTM content
channel_group	STRING	Channel grouping
country	STRING	ISO country code
device_type	STRING	Device type
browser	STRING	Browser name
os	STRING	Operating system
landing_page	STRING	Landing page path
click_id	STRING	Ad-platform click ID (gclid, fbclid, …)
count	INT64	Conversion count
amount	NUMERIC	Per-conversion value
revenue	NUMERIC	Total revenue
properties	JSON	Custom properties

Microconversions (fact_microconversions)

Lightweight engagement events (form fills, clicks, etc.).

Column	Type	Description
account_id	STRING	SealMetrics account ID
date	DATE	Aggregation day (partition key)
conversion_type	STRING	Event type
utm_source	STRING	UTM source
utm_medium	STRING	UTM medium
utm_campaign	STRING	UTM campaign
channel_group	STRING	Channel grouping
country	STRING	ISO country code
device_type	STRING	Device type
count	INT64	Event count
properties	JSON	Event metadata

Dimension & metadata tables

Table	Description
dim_accounts	Account metadata (name, timezone, currency, plan tier) for context and JOINs. Synced only if the Accounts data type is enabled.
dim_countries	Static ISO 3166-1 country lookup (country_code, country_name, continent, region). Always created.
sync_metadata	Sync audit log (sync type, date range, tables synced, row counts, duration). Always created.

---

Example Queries

Once your data is flowing, try these queries in the BigQuery Console:

Daily Traffic Overview

SELECT
  date,
  SUM(page_views) AS pageviews,
  SUM(entrances) AS entrances,
  SUM(conversions) AS conversions
FROM `your-project.sealmetrics.fact_traffic_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY date
ORDER BY date DESC

Revenue by Traffic Source

SELECT
  utm_source,
  utm_medium,
  SUM(count) AS conversions,
  SUM(revenue) AS revenue,
  ROUND(SAFE_DIVIDE(SUM(revenue), SUM(count)), 2) AS avg_order_value
FROM `your-project.sealmetrics.fact_conversions`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY utm_source, utm_medium
ORDER BY revenue DESC

Top Landing Pages by Engagement

SELECT
  landing_page,
  SUM(entrances) AS entrances,
  SUM(engaged_entrances) AS engaged_entrances,
  ROUND(SAFE_DIVIDE(SUM(engaged_entrances), SUM(entrances)) * 100, 1) AS engagement_rate
FROM `your-project.sealmetrics.fact_landing_pages`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY landing_page
HAVING entrances >= 10
ORDER BY entrances DESC
LIMIT 20

---

Troubleshooting

"Permission Denied" Error

• Verify the service account has BigQuery Data Editor and BigQuery Job User roles
• Confirm the service account belongs to the correct GCP project
• Check that GCP billing is active

Sync Not Running

1. Confirm the integration status is Active in Settings → Integrations → BigQuery
2. Verify your service account credentials haven't been revoked
3. Check the sync logs for error details

Missing Data

• Data syncs with a 1-2 hour delay from collection
• Verify your date range covers the expected period
• Check that the relevant data types are enabled in your export settings

Reducing BigQuery Costs

• Tables are partitioned by date by default — always filter by date in your queries
• Use SELECT only the columns you need instead of SELECT *
• Avoid scanning full tables — use WHERE date >= ... clauses (the partition column)
• Set up BigQuery budget alerts in GCP

---

Costs

SealMetrics Side

BigQuery integration is included at no extra cost with all plans (Growth, Scale, and Enterprise).

Google Cloud Side

You pay Google directly for storage and queries:

Resource	Approximate Cost	Notes
Storage	~$0.02/GB/month	Typically $1-2/month for mid-size sites
Queries	~$5/TB scanned	Depends on query complexity and frequency

For a site with ~1M events/month, expect approximately $5-20/month in GCP costs depending on query usage.

tip

BigQuery offers a free tier: 10 GB storage and 1 TB of queries per month — enough for many small to mid-size sites.

---

Next Steps

• Advanced configuration: See BigQuery Settings for data retention, sync options, and detailed schema
• API access: Use the BigQuery API to manage the integration programmatically
• Build dashboards: Connect Looker Studio to your BigQuery dataset for custom visualizations