Skip to content
Skills Library
Data & analytics · Analysis

analyze-usage

/analyze-usage

Use when analyzing BigQuery usage patterns, costs, and query performance for a GCP project

Repository
ai-helpers ↗
Licence
Apache-2.0
Last repository change
27.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What it does and when to use it

Use when analyzing BigQuery usage patterns, costs, and query performance for a GCP project

Original author description

Use when analyzing BigQuery usage patterns, costs, and query performance for a GCP project

How it works

  1. When to Use This Skill
  2. Prerequisites
  3. Parameters
  4. Analysis Workflow
  5. Error Handling
  6. Implementation Notes
  7. Example Output Insights
  8. Success Criteria
  9. Future Enhancements
Original SKILL.md headings · 348 lines, 1,415 words
SKILL.md preview
---
name: analyze-usage
description: Use when analyzing BigQuery usage patterns, costs, and query performance for a GCP project
---

# Analyze BigQuery Usage

This skill performs comprehensive analysis of BigQuery usage patterns, costs, and query performance for a given project. It identifies expensive queries, heavy users, and provides actionable optimization recommendations.

## When to Use This Skill

This skill is automatically invoked by the `/bigquery-cost-analysis:analyze-usage` command to perform usage analysis.

## Prerequisites

- Google Cloud SDK (`bq` command-line tool) must be installed
- User must have BigQuery read access to the project
- User must be authenticated (`gcloud auth login`)
- User needs `bigquery.jobs.list` permission at minimum

## Parameters

When invoked, this skill expects:
- **Project ID**: The GCP project ID to analyze (required)
- **Timeframe**: Time period for analysis in hours (e.g., 24, 168 for 7 days)

## Analysis Workflow

### 1. Validate Prerequisites

First, verify the environment is ready:
- Check if `bq` command is available
- Verify project access
- Parse timeframe into hours

### 2. Collect Usage Data

Execute the following BigQuery queries against INFORMATION_SCHEMA:

#### Total Usage Summary
```sql
SELECT
  COUNT(*) as total_queries,
  ROUND(SUM(total_bytes_processed) / POW(10, 12), 2) as total_tb_scanned,
  ROUND(SUM(total_bytes_processed) / POW(10, 12) * 6.25, 2) as estimated_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @hours HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type != 'SCRIPT'
```

#### Usage by User/Service Account
```sql
SELECT
  user_email,
  COUNT(*) as query_count,
  ROUND(SUM(total_bytes_processed) / POW(10, 12), 2) as total_tb_scanned,
  ROUND(SUM(total_bytes_processed) / POW(10, 12) * 6.25, 2) as estimated_cost_usd,
  ROUND(AVG(total_bytes_processed) / POW(10, 9), 2) as avg_gb_per_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @hours HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type != 'SCRIPT'
GROUP BY user_email
ORDER BY total_tb_scanned DESC
LIMIT 20
```

#### Top Individual Queries by Cost
```sql
SELECT
  creation_time,
  user_email,
  job_id,
  ROUND(total_bytes_processed / POW(10, 12), 3) as tb_scanned,
  ROUND(total_bytes_processed / POW(10, 12) * 6.25, 2) as cost_usd,
  SUBSTR(query, 1, 200) as query_preview
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @hours HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type != 'SCRIPT'
  AND total_bytes_processed > 0
ORDER BY total_bytes_processed DESC
LIMIT 20
```

#### Query Pattern Analysis
```sql
SELECT
  SUBSTR(query, 1, 200) as query_pattern,
  COUNT(*) as execution_count,
  ROUND(SUM(total_bytes_processed) / POW(10, 12), 3) as total_tb_scanned,
  ROUND(AVG(total_bytes_processed) / POW(10, 9), 2) as avg_gb_per_execution,
  ANY_VALUE(user_email) as sample_user
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @hours HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type != 'SCRIPT'
  AND total_bytes_processed > 0
GROUP BY query_pattern
HAVING execution_count > 10
ORDER BY total_tb_scanned DESC
LIMIT 15
```

### 3. Per-User Deep Dive Analysis

For the top 2-3 users by data scanned, perform detailed query pattern analysis:

```sql
SELECT
  SUBSTR(query, 1, 300) as query_pattern,
  COUNT(*) as execution_count,
  ROUND(SUM(total_bytes_processed) / POW(10, 12), 3) as total_tb_scanned,
  ROUND(AVG(total_bytes_processed) / POW(10, 9), 2) as avg_gb_per_execution,
  MIN(creation_time) as first_execution,
  MAX(creation_time) as last_execution
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @hours HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type != 'SCRIPT'
  AND user_email = @user_email
  AND total_bytes_processed > 0
GROUP BY query_pattern
ORDER BY total_tb_scanned DESC
LIMIT 20
```

This reveals:
- What each heavy user is querying
- Patterns in their query behavior
- Opportunities for user-specific optimizations
- Whether queries are automated (service accounts) or manual (humans)

### 4. Analyze Results and Identify Patterns

Look for common issues:

**Query Anti-Patterns:**
- `SELECT *` on large tables
- Full table scans without WHERE clauses
- High-frequency queries that could be cached
- Queries scanning data unnecessarily (e.g., counting rows by scanning 40GB)
- Missing partitioning filters
- Repeated identical queries

**User Behavior Patterns:**
- Service accounts with high query volume (automation candidates)
- Deduplication checks that scan large amounts of data
- Dashboard queries hitting raw tables instead of materialized views
- Scheduled queries running too frequently

**Cost Drivers:**
- Single expensive queries
- High-volume low-cost queries (death by a thousand cuts)
- Inefficient aggregations
- Missing indexes/clustering

### 5. Generate Optimization Recommendations

For each issue found, provide:

1. **What**: Describe the issue clearly
2. **Why**: Explain why it's expensive
3. **How**: Provide specific fix instructions
4. **Savings**: Estimate potential cost reduction
5. **Difficulty**: Rate implementation effort (easy/medium/hard)
6. **Priority**: Based on impact and ease

**Prioritization Framework:**
- Priority 1: High impact, easy wins (>$5/day saved, easy implementation)
- Priority 2: Medium impact (>$2/day saved)
- Priority 3: Architectural improvements (long-term benefits)

### 6. Format Comprehensive Report

Structure the output as:

```markdown
# BigQuery Usage Analysis Report
**Project:** <project-id>
**Analysis Period:** Last <timeframe>
**Generated:** <timestamp>

## Executive Summary
- Total Queries Executed: <count>
- Total Data Scanned: <TB>
- Estimated Cost: $<amount>

### Key Findings
1. <Top issue with data/cost>
2. <Second major issue>
3. <Third issue>

## Usage by User/Service Account
<Table with top 10-20 users>

## Top Query Patterns
<Detailed breakdown of top patterns with recommendations>

## Per-User Analysis

### Top User 1: <user_email> (<TB> scanned, $<cost>)
<Summary of what this user does>

**Primary Query Types:**
1. <Pattern description> (<data scanned>)
   - Execution count
   - Average per query
   - Specific optimization recommendation

### Top User 2: <user_email> (<TB> scanned, $<cost>)
<Summary of what this user does>

**Primary Query Types:**
1. <Pattern description> (<data scanned>)
   - Execution count
   - Average per query
   - Specific optimization recommendation

## Top Individual Queries
<Table of most expensive queries>

## Optimization Recommendations

### Priority 1: High Impact, Easy Wins
1. **<Recommendation title>**
   - Issue: <description>
   - Fix: <specific steps>
   - Estimated Savings: <$/day or %>
   - Difficulty: Easy/Medium/Hard

### Priority 2: Medium Impact
...

### Priority 3: Architectural Improvements
...

## Cost Breakdown Summary
- Service Accounts: $<amount> (<percentage>)
- Human Users: $<amount> (<percentage>)
```

### 7. Offer to Save Report

After presenting the analysis, ask the user if they want to save it to a markdown file:
- Suggest filename: `bigquery-usage-<project-id>-<YYYYMMDD>.md`
- Include all analysis details
- Format with proper markdown tables and sections

## Error Handling

Handle common issues by displaying a clear error message and suggesting a fix:

**"bq command not found"**
- Provide installation instructions for user's platform
- macOS: `brew install google-cloud-sdk`
- Linux: Point to cloud.google.com/sdk
- Verify PATH configuration

**"Access Denied" errors**
- Guide user through `gcloud auth login`
- Verify project access: `bq ls --project_id=<project-id>`
- Check IAM permissions (need BigQuery Job User role minimum)

**"Invalid project ID"**
- Verify project ID vs project name
- List available projects: `gcloud projects list`
- Check for typos

**No data returned**
- Verify queries have run in the specified timeframe
- Check region (try `region-us`, `US`, `EU`, etc.)
- Ensure querying correct project

**Query execution errors**
- Check INFORMATION_SCHEMA availability
- Verify region-specific schema locations
- Adjust queries for project's BigQuery setup

## Implementation Notes

### Cost Calculation
- Use on-demand pricing: $6.25/TB (as of 2024-2025)
- Note in report if project may have flat-rate pricing
- Savings estimates are based on on-demand pricing

### Region Handling
- Default to `region-us` INFORMATION_SCHEMA
- If queries fail, try `US` or `EU`
- Consider making region configurable in future

### Performance Considerations
- All analysis queries are read-only
- Use INFORMATION_SCHEMA (metadata only, very efficient)
- Queries should complete in seconds
- Longer timeframes (30 days) may take longer but still fast

### Query Optimization
- Use `--format=json` for easy parsing
- Use `--use_legacy_sql=false` for standard SQL
- Limit result sets to 20 rows unless the user requests more
- Filter to completed queries only (state = 'DONE')

## Example Output Insights

**Good User Analysis Example:**

```
### openshift-ci-data-writer (4.07 TB, $25.41)
This service account runs test run analysis and backend disruption monitoring.

**Primary Query Types:**
1. **TestRuns_Summary_Last200Runs** (3.02 TB - 74% of usage)
   - 96 executions using SELECT *
   - 31.41 GB per query
   - **Recommendation:** Replace SELECT * with specific columns.
     Estimated savings: $9-15/day

2. **BackendDisruption Lookups** (0.68 TB - 17% of usage)
   - 4,165 queries checking for job run names
   - **Recommendation:** Add clustering on JobName + JobRunStartTime.
     Implement result caching.
```

This level of detail helps users understand exactly what's driving their costs and how to fix it.

## Success Criteria

A successful analysis should:
1. Identify all queries scanning >100GB per execution
2. Find high-frequency query patterns (>100 executions)
3. Provide at least 3 actionable optimization recommendations
4. Include cost estimates for top recommendations
5. Offer clear next steps for the user
6. Be formatted clearly and professionally

## Future Enhancements

Add:
- Trend analysis (compare current period to previous periods)
- Query performance metrics (execution time, slot usage)
- Automatic detection of partitioning opportunities
- Cost anomaly detection (unusual spikes)
- Integration with BigQuery Reservations data
- Historical cost tracking over time

Versions

The database has no other skills named analyze-usage.

Database snapshot: 13.11.2026 · package version 723b7ad4f447 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100bigquery-scheduled-query
    Manage bigquery scheduled query operations. Auto-activating skill for GCP Skills. Triggers on: bigquery scheduled query, bigquery scheduled query Part of the GCP Skills skill category. Use when working with bigquery scheduled query functionality. Trigger with phrases like…
  • 100bigquery-table-creator
    Create bigquery table creator operations. Auto-activating skill for GCP Skills. Triggers on: bigquery table creator, bigquery table creator Part of the GCP Skills skill category. Use when working with bigquery table creator functionality. Trigger with phrases like "bigquery…
  • 100bigquery
    Query and manage data in Google BigQuery. Covers dataset and table management, IAM, cost-safe querying (dry-run first), partitioning, clustering, and bq CLI patterns. Always estimates cost before executing queries — BigQuery bills by bytes processed. Use when the user mentions:…
  • 92write-script-bigquery
    MUST use when writing BigQuery queries.
  • 95cloud-native-gcp-bigquery
    Use this skill when the user says 'bigquery', 'bigquery cost', 'bigquery partitioning', 'bigquery clustering', 'dremel', 'capacitor', 'bigquery slots', 'bigquery editions', 'bigquery slow query', 'bytes scanned'. Explains BigQuery's architecture (Dremel execution, Capacitor…
  • 80developing-with-bigquery
    A repository of BigQuery-specific logic, knowledge, and specialized standards. Use this skill whenever you are doing anything with BigQuery, including: 1. BigQuery query optimization 2. BigFrames Python code 3. BigQuery ML/AI functions.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.