BigQuery Insights - Complete Example

Build an AI agent that connects to Google BigQuery, runs complex SQL queries, analyzes large datasets, and generates actionable insights from your data warehouse.

What You'll Build

A data analysis agent that:

Connects to Google BigQuery datasets
Writes and executes SQL queries
Analyzes trends across millions of records
Generates visualizations and insights
Creates automated data reports
Identifies anomalies and opportunities

Time to complete: 60-75 minutes

Prerequisites

Before you begin, you need:

A TeamDay account with an organization
Google Cloud Project with BigQuery enabled
BigQuery datasets with data
An Anthropic API key
Basic SQL knowledge

Architecture Overview

graph LR
    A[User Request] --> B[Data Agent]
    B --> C[BigQuery MCP]
    C --> D[BigQuery API]
    D --> E[Your Datasets]
    B --> F[SQL Generation]
    B --> G[Data Analysis]
    G --> H[Insights Agent]
    H --> I[Recommendations]
    B --> J[Visualization Suggestions]

Use Cases

This example is perfect for:

Product Analytics: User behavior, feature adoption, retention
Business Intelligence: Revenue analysis, customer segmentation
Marketing Analytics: Campaign performance, attribution analysis
Operations: System metrics, performance monitoring
Research: Exploratory data analysis, hypothesis testing

Step 1: Google Cloud Setup

1.1 Create Google Cloud Project

Go to Google Cloud Console
Create project: "TeamDay BigQuery Analytics"
Note Project ID: teamday-bq-analytics-12345

1.2 Enable BigQuery API

# Using gcloud CLI
gcloud config set project teamday-bq-analytics-12345
gcloud services enable bigquery.googleapis.com
gcloud services enable bigquerystorage.googleapis.com

# Or via Console:
# APIs & Services → Enable APIs → "BigQuery API"

1.3 Create Service Account

Create a service account for API access:

# Create service account
gcloud iam service-accounts create teamday-bigquery \
  --display-name="TeamDay BigQuery Agent" \
  --description="Service account for TeamDay BigQuery access"

# Get service account email
SA_EMAIL="teamday-bigquery@teamday-bq-analytics-12345.iam.gserviceaccount.com"

# Grant BigQuery permissions
gcloud projects add-iam-policy-binding teamday-bq-analytics-12345 \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/bigquery.dataViewer"

gcloud projects add-iam-policy-binding teamday-bq-analytics-12345 \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/bigquery.jobUser"

# Create and download key
gcloud iam service-accounts keys create ~/teamday-bigquery-key.json \
  --iam-account=$SA_EMAIL

Service Account Roles:

bigquery.dataViewer - Read dataset contents
bigquery.jobUser - Run queries
bigquery.dataEditor - (Optional) Write data

1.4 Verify Service Account

Test the service account:

# Install BigQuery CLI
pip install google-cloud-bigquery

# Test query
cat > test-bq.py << 'EOF'
from google.cloud import bigquery
import os

os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'teamday-bigquery-key.json'
client = bigquery.Client()

query = "SELECT COUNT(*) as total FROM `bigquery-public-data.usa_names.usa_1910_current`"
results = client.query(query).result()

for row in results:
    print(f"Total records: {row.total}")
EOF

python test-bq.py
# Should output: Total records: 6122890

1.5 Create or Identify Datasets

For this example, we'll use:

Public dataset (for learning): bigquery-public-data.usa_names.usa_1910_current
Your own dataset: Create one for your data

Create a dataset:

bq mk --dataset \
  --location=US \
  --description="TeamDay Analytics Data" \
  teamday-bq-analytics-12345:analytics

# Create sample table
bq query --use_legacy_sql=false "
CREATE TABLE analytics.user_events AS
SELECT
  GENERATE_UUID() as event_id,
  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL CAST(RAND()*30 AS INT64) DAY) as timestamp,
  CONCAT('user_', CAST(CAST(RAND()*1000 AS INT64) AS STRING)) as user_id,
  ['page_view', 'click', 'signup', 'purchase'][CAST(RAND()*4 AS INT64)] as event_type,
  RAND()*100 as value
FROM UNNEST(GENERATE_ARRAY(1, 10000))
"

Step 2: TeamDay Setup

2.1 Store Service Account Credentials

Upload the service account JSON to TeamDay:

Via CLI:

# Base64 encode the service account file
cat ~/teamday-bigquery-key.json | base64 > ~/sa-encoded.txt

# Store in TeamDay
teamday secrets set GCP_SERVICE_ACCOUNT_JSON "$(cat ~/sa-encoded.txt)"
teamday secrets set GCP_PROJECT_ID "teamday-bq-analytics-12345"
teamday secrets set BQ_DATASET "analytics"

Or store the file path:

# Upload file to space (more common approach)
# We'll do this in the space setup

2.2 Add Anthropic API Key

teamday secrets set ANTHROPIC_API_KEY "sk-ant-api03-..."

2.3 Create Space

teamday spaces create "BigQuery Analytics"

# Get space ID
teamday spaces list
# Output: cs_bigquery123...

Step 3: Configure BigQuery MCP

3.1 Upload Service Account to Space

Upload the service account JSON to your space:

Via UI:

Open space
Files panel → New File
Name: gcp-service-account.json
Paste contents of teamday-bigquery-key.json
Save

Via API:

curl -X POST https://api.teamday.app/api/spaces/cs_bigquery123/files/write \
  -H "Authorization: Bearer $TEAMDAY_TOKEN" \
  -H "Content-Type: application/json" \
  -d "{
    \"path\": \"/gcp-service-account.json\",
    \"content\": $(cat ~/teamday-bigquery-key.json | jq -R -s '.')
  }"

3.2 MCP Server Configuration

Configure the BigQuery MCP server:

{
  "mcpServers": {
    "bigquery": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-bigquery"],
      "env": {
        "GOOGLE_APPLICATION_CREDENTIALS": "/workspace/gcp-service-account.json",
        "GCP_PROJECT_ID": "${GCP_PROJECT_ID}"
      }
    }
  }
}

3.3 Install MCP Plugin

Via UI:

Space Settings → MCP Plugins
Click "Install Plugin"
Search "BigQuery"
Configuration auto-populated
Click "Install & Test"

Via API:

curl -X POST https://api.teamday.app/api/plugins/install \
  -H "Authorization: Bearer $TEAMDAY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "spaceId": "cs_bigquery123",
    "plugin": "bigquery",
    "config": {
      "mcpServers": {
        "bigquery": {
          "command": "npx",
          "args": ["-y", "@modelcontextprotocol/server-bigquery"],
          "env": {
            "GOOGLE_APPLICATION_CREDENTIALS": "/workspace/gcp-service-account.json",
            "GCP_PROJECT_ID": "teamday-bq-analytics-12345"
          }
        }
      }
    }
  }'

3.4 Verify Installation

Test the BigQuery connection:

You: Test the BigQuery connection

Agent: Testing BigQuery MCP...

✅ Authentication successful
✅ Connected to project: teamday-bq-analytics-12345
✅ Available datasets: 3
   - analytics
   - logs
   - user_data

✅ Test query successful
✅ Sample data retrieved

Ready to query BigQuery!

Step 4: Create Data Analysis Agent

4.1 Create Primary Agent

curl -X POST https://api.teamday.app/api/v1/agents \
  -H "Authorization: Bearer $TEAMDAY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "BigQuery Data Analyst",
    "model": "claude-3-5-sonnet-20241022",
    "visibility": "organization",
    "organizationId": "org_xxx"
  }'

Save agent ID: ag_bq_analyst789

4.2 System Prompt

# BigQuery Data Analyst System Prompt

You are an expert data analyst with deep knowledge of SQL, BigQuery, and data analysis.

## Your Capabilities

1. **SQL Query Generation**
   - Write efficient BigQuery SQL queries
   - Use BigQuery-specific functions and optimizations
   - Handle large datasets (partitioning, clustering)
   - Optimize query performance and cost

2. **Data Analysis**
   - Analyze query results for trends and patterns
   - Calculate statistics and aggregations
   - Identify anomalies and outliers
   - Perform comparative analysis

3. **Insights Generation**
   - Explain what the data shows
   - Provide business context
   - Make data-driven recommendations
   - Highlight opportunities and risks

4. **Visualization Guidance**
   - Suggest appropriate chart types
   - Describe visualization layouts
   - Recommend data transformations for viz

## BigQuery Best Practices

**Query Optimization:**
- Use partitioned tables when filtering by date
- Select only needed columns (avoid SELECT *)
- Use approximate aggregation functions when appropriate
- Leverage table clustering for common filters
- Use LIMIT for exploratory queries

**Cost Control:**
- Check query cost before running large queries
- Use cached results when possible
- Partition and cluster large tables
- Use materialized views for repeated queries

**Performance:**
- Use ARRAY_AGG and STRUCT for denormalization
- Leverage window functions for complex calculations
- Use WITH clauses for query readability
- Avoid self-JOINs when possible

## Available Datasets

### analytics.user_events
- event_id (STRING)
- timestamp (TIMESTAMP)
- user_id (STRING)
- event_type (STRING): page_view, click, signup, purchase
- value (FLOAT64)

### analytics.users
- user_id (STRING)
- created_at (TIMESTAMP)
- country (STRING)
- plan (STRING): free, pro, enterprise

### logs.application_logs
- timestamp (TIMESTAMP)
- severity (STRING)
- message (STRING)
- user_id (STRING)
- request_id (STRING)

## Analysis Framework

For each request:
1. **Understand the question** - Clarify if needed
2. **Write SQL query** - Explain your approach
3. **Execute query** - Use BigQuery MCP
4. **Analyze results** - Look for patterns
5. **Generate insights** - Explain findings
6. **Make recommendations** - Actionable next steps

## Response Format

Structure your analysis as:

### 📊 [Analysis Title]

**Question:** [What we're analyzing]

**Query:**
```sql
[Your SQL query]

Results:Formatted table or key findings

Insights:

Key finding 1
Key finding 2
Key finding 3

Recommendations:

Actionable recommendation 1
Actionable recommendation 2

Suggested Visualization:Description of chart/graph to create

Be precise with numbers but explain insights in plain language.


### 4.3 Set Agent Permissions

```bash
curl -X PATCH https://api.teamday.app/api/v1/agents/ag_bq_analyst789 \
  -H "Authorization: Bearer $TEAMDAY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "permissions": {
      "readFiles": true,
      "writeFiles": true,
      "executeCode": true,
      "commitToGit": false,
      "accessSecrets": false
    }
  }'

Step 5: Install Additional MCP Plugins

5.1 Sequential Thinking Plugin

For complex analysis:

{
  "mcpServers": {
    "sequential-thinking": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-sequential-thinking"]
    }
  }
}

5.2 Filesystem Plugin (Optional)

For saving query results:

{
  "mcpServers": {
    "filesystem": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-filesystem"],
      "env": {
        "ALLOWED_PATHS": "/workspace/reports"
      }
    }
  }
}

Step 6: Usage Examples

6.1 Basic Query

You: How many total events do we have?

Agent: I'll query the user_events table to get the total count.

**Query:**
```sql
SELECT COUNT(*) as total_events
FROM `teamday-bq-analytics-12345.analytics.user_events`

Results:

Total Events: 10,000

Context: Events span from November 9 to December 9, 2024 (30 days).


### 6.2 User Behavior Analysis

You: Analyze user engagement by event type over the last 7 days

Agent: I'll analyze event distribution and trends.

Query:

SELECT
  DATE(timestamp) as date,
  event_type,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_id) as unique_users,
  ROUND(AVG(value), 2) as avg_value
FROM `teamday-bq-analytics-12345.analytics.user_events`
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY date, event_type
ORDER BY date DESC, event_count DESC

Results:

Date	Event Type	Events	Unique Users	Avg Value
2024-12-09	page_view	89	67	48.23
2024-12-09	click	76	58	52.11
2024-12-09	signup	12	12	45.67
2024-12-09	purchase	8	8	78.92
2024-12-08	page_view	92	71	51.34
...	...	...	...	...

Insights: ✨ Conversion funnel is healthy

Page views → Clicks: 85% engagement rate
Clicks → Signups: 15.8% conversion
Signups → Purchases: 66.7% conversion (excellent!)

📈 Purchase behavior

Average purchase value: $78.92 (highest of all events)
8 purchases from 12 signups shows strong product-market fit

⚠️ Traffic consistency

Daily events relatively stable (89-95 events/day)
Could indicate growth ceiling or steady state

Recommendations:

Focus on top-of-funnel growth - Traffic is stable, push acquisition
Investigate high purchase conversion - What makes 67% of signups purchase?
Optimize page views - With 85% click-through, content is engaging

Suggested Visualization: Stacked area chart showing event types over time, with separate line for conversion rate.


### 6.3 Cohort Analysis

You: Perform a cohort analysis of user retention

Agent: I'll analyze user retention by signup cohort.

Query:

WITH user_cohorts AS (
  -- Get each user's first event date as cohort
  SELECT
    user_id,
    DATE_TRUNC(MIN(timestamp), WEEK) as cohort_week
  FROM `teamday-bq-analytics-12345.analytics.user_events`
  GROUP BY user_id
),
user_activity AS (
  -- Get user activity by week
  SELECT
    user_id,
    DATE_TRUNC(timestamp, WEEK) as activity_week
  FROM `teamday-bq-analytics-12345.analytics.user_events`
  GROUP BY user_id, activity_week
)
SELECT
  cohort_week,
  COUNT(DISTINCT uc.user_id) as cohort_size,
  DATE_DIFF(ua.activity_week, uc.cohort_week, WEEK) as weeks_since_signup,
  COUNT(DISTINCT ua.user_id) as active_users,
  ROUND(COUNT(DISTINCT ua.user_id) * 100.0 / COUNT(DISTINCT uc.user_id), 1) as retention_pct
FROM user_cohorts uc
LEFT JOIN user_activity ua ON uc.user_id = ua.user_id
GROUP BY cohort_week, weeks_since_signup
HAVING cohort_week >= '2024-11-01'
ORDER BY cohort_week, weeks_since_signup

Results:

Cohort	Size	Week 0	Week 1	Week 2	Week 3	Week 4
Nov 4	178	100%	56.2%	38.8%	29.2%	24.7%
Nov 11	195	100%	58.5%	41.0%	31.3%	-
Nov 18	203	100%	59.1%	43.3%	-	-
Nov 25	189	100%	61.4%	-	-	-
Dec 2	201	100%	-	-	-	-

Insights: 📊 Week 1 retention: 56-61% (improving trend)

Latest cohort (Dec 2) shows 61.4% Week 1 retention
5-point improvement over 4 weeks

📉 Week 2 drop-off: 15-18%

Consistent 40-43% Week 2 retention
Major drop happens between Week 1-2

🎯 Week 4 stabilization: ~25%

Users who make it to Week 4 tend to stick
25% core user base

Recommendations:

Focus on Week 1-2 transition - Biggest drop-off point
- Implement Week 1 onboarding email sequence
- Add "quick win" features for new users
- Trigger re-engagement at Day 10
Investigate improving cohorts - What changed?
- Review product changes in late November
- Survey recent users about onboarding experience
Nurture Week 4+ users - These are your champions
- Consider referral program for this segment
- Gather feedback for product roadmap

Suggested Visualization: Cohort retention heatmap with color gradient (green = high retention, red = low).


### 6.4 Revenue Analysis

You: Analyze revenue trends and identify our best customers

Agent: I'll analyze purchase events and customer value.

Query:

WITH customer_metrics AS (
  SELECT
    user_id,
    COUNT(*) as total_purchases,
    SUM(value) as total_revenue,
    AVG(value) as avg_order_value,
    MIN(timestamp) as first_purchase,
    MAX(timestamp) as last_purchase,
    DATE_DIFF(CURRENT_TIMESTAMP(), MAX(timestamp), DAY) as days_since_last_purchase
  FROM `teamday-bq-analytics-12345.analytics.user_events`
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  -- Customer segmentation
  CASE
    WHEN total_revenue >= 500 THEN 'VIP'
    WHEN total_revenue >= 200 THEN 'High Value'
    WHEN total_revenue >= 100 THEN 'Medium Value'
    ELSE 'Low Value'
  END as customer_segment,
  COUNT(*) as customer_count,
  SUM(total_revenue) as segment_revenue,
  ROUND(AVG(total_revenue), 2) as avg_customer_value,
  ROUND(AVG(total_purchases), 1) as avg_purchases_per_customer,
  ROUND(AVG(avg_order_value), 2) as avg_order_value,
  ROUND(AVG(days_since_last_purchase), 1) as avg_days_since_purchase
FROM customer_metrics
GROUP BY customer_segment
ORDER BY segment_revenue DESC

Results:

Segment	Customers	Revenue	Avg Value	Purchases	AOV	Days Since
VIP	8	$4,234	$529.25	6.8	$77.87	3.2
High Value	23	$5,892	$256.17	3.2	$80.05	5.1
Medium Value	67	$8,123	$121.24	1.5	$80.83	8.7
Low Value	156	$7,234	$46.37	1.1	$42.15	12.3

Total Revenue: $25,483 Total Customers: 254

Insights: 💎 VIP customers are gold

3% of customers generate 17% of revenue
High purchase frequency (6.8x vs 1.1x for low value)
Recent activity (3.2 days) shows strong engagement

📈 Pareto principle in action

Top 12% (VIP + High Value) = 40% of revenue
Focus here has 3x impact

⚠️ Low value segment at risk

12.3 days since purchase (approaching churn)
Low order value suggests price sensitivity
Large segment (156 customers) - opportunity if activated

🎯 Average order value consistent (~$80)

Except low value segment ($42) - different behavior
Price point works for engaged customers

Recommendations:

VIP retention program
- Personal account manager
- Exclusive features/early access
- Loyalty rewards (already buying frequently)
Upgrade medium → high value
- Cross-sell opportunities
- Bundle offers to increase AOV
- Usage-based triggers for upgrades
Re-engage low value segment
- Win-back campaign for 10+ days inactive
- Entry-level product at $40-50 price point
- Time-limited discount to drive action
Optimize for $80 price point
- Sweet spot for most customers
- Package products around this value
- Upsell accessories to reach $80+ cart

Suggested Visualization:

Revenue pyramid by segment
Customer lifetime value distribution
Days since purchase histogram by segment


### 6.5 Anomaly Detection

You: Check for any anomalies in the last 24 hours of data

Agent: I'll analyze recent data for unusual patterns.

Query:

WITH hourly_metrics AS (
  SELECT
    TIMESTAMP_TRUNC(timestamp, HOUR) as hour,
    event_type,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
  FROM `teamday-bq-analytics-12345.analytics.user_events`
  WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  GROUP BY hour, event_type
),
historical_avg AS (
  SELECT
    EXTRACT(HOUR FROM timestamp) as hour_of_day,
    event_type,
    AVG(hourly_count) as avg_count,
    STDDEV(hourly_count) as stddev_count
  FROM (
    SELECT
      TIMESTAMP_TRUNC(timestamp, HOUR) as hour,
      event_type,
      COUNT(*) as hourly_count
    FROM `teamday-bq-analytics-12345.analytics.user_events`
    WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
      AND timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
    GROUP BY hour, event_type
  )
  GROUP BY hour_of_day, event_type
)
SELECT
  hm.hour,
  hm.event_type,
  hm.event_count,
  ha.avg_count as expected_count,
  ROUND((hm.event_count - ha.avg_count) / NULLIF(ha.stddev_count, 0), 2) as z_score,
  CASE
    WHEN ABS((hm.event_count - ha.avg_count) / NULLIF(ha.stddev_count, 0)) > 2 THEN '🚨 ANOMALY'
    WHEN ABS((hm.event_count - ha.avg_count) / NULLIF(ha.stddev_count, 0)) > 1.5 THEN '⚠️ WARNING'
    ELSE '✅ NORMAL'
  END as status
FROM hourly_metrics hm
JOIN historical_avg ha
  ON EXTRACT(HOUR FROM hm.hour) = ha.hour_of_day
  AND hm.event_type = ha.event_type
WHERE ABS((hm.event_count - ha.avg_count) / NULLIF(ha.stddev_count, 0)) > 1.5
ORDER BY ABS((hm.event_count - ha.avg_count) / NULLIF(ha.stddev_count, 0)) DESC

Results:

Hour	Event Type	Actual	Expected	Z-Score	Status
2024-12-09 14:00	purchase	28	12.3	3.2	🚨 ANOMALY
2024-12-09 14:00	signup	45	23.1	2.8	🚨 ANOMALY
2024-12-09 08:00	page_view	156	98.2	1.9	⚠️ WARNING
2024-12-09 03:00	click	12	34.5	-1.7	⚠️ WARNING

Anomalies Detected:

🚨 CRITICAL: Purchase spike at 2 PM (z-score: 3.2)

Actual: 28 purchases (227% of expected)
This is highly unusual (>3 standard deviations)
Possible causes:
- Marketing campaign launched?
- Product hunt/HN feature?
- Flash sale or promotion?
- Pricing error (check immediately!)

🚨 CRITICAL: Signup spike at 2 PM (z-score: 2.8)

Actual: 45 signups (195% of expected)
Correlates with purchase spike
Good news: Conversion funnel working

⚠️ WARNING: Morning traffic higher (z-score: 1.9)

156 page views vs expected 98
60% increase - significant but less concerning
Possible causes:
- Weekend traffic pattern?
- Newsletter sent?
- Viral content?

⚠️ WARNING: Late night drop (z-score: -1.7)

12 clicks vs expected 34
65% decrease
Less concerning: Normal variance in low-traffic hours

Recommendations:

IMMEDIATE: Investigate 2 PM spike
- Check for pricing errors
- Review payment processing
- Verify fraud detection
- Confirm inventory/capacity
Capture opportunity
- If legitimate, what drove it?
- Can we replicate this?
- Document the source
Monitor next 24h
- Watch for sustained increase
- Or regression to mean
- Set up alerts for z-score > 2

Suggested Actions:

-- Check traffic sources during spike
SELECT
  utm_source,
  utm_medium,
  utm_campaign,
  COUNT(*) as conversions
FROM analytics.user_events
WHERE event_type = 'purchase'
  AND timestamp BETWEEN '2024-12-09 14:00:00' AND '2024-12-09 15:00:00'
GROUP BY 1,2,3
ORDER BY conversions DESC


## Step 7: Advanced Analysis Patterns

### 7.1 Funnel Analysis

Create a reusable funnel analysis:

```sql
WITH funnel_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) as step_1_view,
    MAX(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) as step_2_click,
    MAX(CASE WHEN event_type = 'signup' THEN 1 ELSE 0 END) as step_3_signup,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) as step_4_purchase
  FROM `teamday-bq-analytics-12345.analytics.user_events`
  WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY user_id
)
SELECT
  SUM(step_1_view) as users_viewed,
  SUM(step_2_click) as users_clicked,
  SUM(step_3_signup) as users_signed_up,
  SUM(step_4_purchase) as users_purchased,
  ROUND(SUM(step_2_click) * 100.0 / NULLIF(SUM(step_1_view), 0), 1) as view_to_click_pct,
  ROUND(SUM(step_3_signup) * 100.0 / NULLIF(SUM(step_2_click), 0), 1) as click_to_signup_pct,
  ROUND(SUM(step_4_purchase) * 100.0 / NULLIF(SUM(step_3_signup), 0), 1) as signup_to_purchase_pct,
  ROUND(SUM(step_4_purchase) * 100.0 / NULLIF(SUM(step_1_view), 0), 1) as overall_conversion_pct
FROM funnel_steps

7.2 Predictive Analysis

Identify users likely to churn:

WITH user_features AS (
  SELECT
    user_id,
    COUNT(*) as total_events,
    COUNT(DISTINCT DATE(timestamp)) as active_days,
    DATE_DIFF(CURRENT_DATE(), MAX(DATE(timestamp)), DAY) as days_since_last_event,
    AVG(CASE WHEN event_type = 'purchase' THEN value ELSE 0 END) as avg_purchase_value,
    COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) as purchase_count
  FROM `teamday-bq-analytics-12345.analytics.user_events`
  GROUP BY user_id
)
SELECT
  user_id,
  CASE
    WHEN days_since_last_event > 14 AND purchase_count > 0 THEN 'HIGH_RISK'
    WHEN days_since_last_event > 7 AND active_days < 3 THEN 'MEDIUM_RISK'
    WHEN days_since_last_event > 3 AND total_events < 5 THEN 'LOW_RISK'
    ELSE 'ACTIVE'
  END as churn_risk,
  days_since_last_event,
  total_events,
  purchase_count,
  ROUND(avg_purchase_value, 2) as avg_purchase_value
FROM user_features
WHERE days_since_last_event > 3
ORDER BY
  CASE
    WHEN days_since_last_event > 14 AND purchase_count > 0 THEN 1
    WHEN days_since_last_event > 7 AND active_days < 3 THEN 2
    ELSE 3
  END,
  days_since_last_event DESC

Step 8: Automation & Scheduling

8.1 Daily Insights Mission

curl -X POST https://api.teamday.app/api/missions \
  -H "Authorization: Bearer $TEAMDAY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Daily BigQuery Insights",
    "schedule": "0 8 * * *",
    "agentId": "ag_bq_analyst789",
    "action": {
      "type": "execute",
      "prompt": "Analyze yesterday'\''s data: 1) Overall metrics 2) Top products 3) Any anomalies 4) Recommendations. Save report to /workspace/reports/daily-$(date +%Y-%m-%d).md"
    },
    "organizationId": "org_xxx",
    "spaceId": "cs_bigquery123"
  }'

8.2 Weekly Deep Dive

curl -X POST https://api.teamday.app/api/missions \
  -H "Authorization: Bearer $TEAMDAY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Weekly Data Analysis",
    "schedule": "0 9 * * 1",
    "agentId": "ag_bq_analyst789",
    "action": {
      "type": "execute",
      "prompt": "Comprehensive weekly analysis: 1) Cohort retention 2) Revenue trends 3) User segmentation 4) Funnel performance 5) Key insights & recommendations"
    },
    "organizationId": "org_xxx",
    "spaceId": "cs_bigquery123"
  }'

Troubleshooting

Authentication Failed

Error:

Error: BigQuery authentication failed (403 Forbidden)

Solution:

Verify service account JSON is valid
Check service account has required roles
Ensure BigQuery API is enabled
Verify project ID is correct
Check file path in MCP config

Query Timeout

Error:

Error: Query exceeded time limit

Solution:

Add LIMIT clause for testing
Use table partitioning
Optimize JOIN operations
Check query cost estimate
Break into smaller queries

No Data Returned

Error:

Error: Query returned no results

Solution:

Verify dataset and table names
Check date filters
Confirm data exists in table
Review WHERE conditions
Test with SELECT COUNT(*)

Cost Concerns

Issue: High BigQuery costs

Solution:

Use query cost estimation
Enable table partitioning
Use cached results
Set maximum bytes billed
Review query patterns
Use LIMIT for development

Cost Estimation

BigQuery Costs:

Query: $5 per TB scanned
Storage: $0.02 per GB/month
Typical query: 10-100 MB = $0.00005-$0.0005

TeamDay Agent:

Analysis with SQL: ~15,000 tokens input, ~4,000 output
Cost per analysis: ~$0.18
Monthly (daily reports): ~$5.40

Total: ~$6-10/month for daily analytics

Next Steps

Analytics Reporter Example - Combine with GA4 data
Code Review Bot - Analyze engineering metrics
Custom MCP Servers - Build custom data tools
Automation Guide - Advanced scheduling