query

name	query
description	Runs a read-only SQL query against the Customer Experience tables (moz-fx-data-shared-prod.customer_experience_derived) and returns results as a formatted table. Use when the question requires counts, aggregations, rankings, or distributions — not when it requires reading document text.

Query Skill

Runs a read-only SQL query against BigQuery and prints results as a formatted text table. No embedding required.

This skill is locked to moz-fx-data-shared-prod.customer_experience_derived — it can read any table or view in that dataset, and anything outside it is rejected. Only a single read-only SELECT (optionally WITH … SELECT) is accepted. A static allowlist check rejects any out-of-dataset reference before the query runs; the authoritative boundary is the service account's IAM, which grants read access to that dataset only, so BigQuery rejects any out-of-bounds read regardless of the SQL.

Authentication

This skill connects to BigQuery using a service account — read-only access is enforced on the impersonated token. Just log in:

gcloud auth application-default login

Your authenticated BigQuery credentials are only used to create a new and temporary read-only access token on behalf of the service account defined in SERVICE_ACCOUNT, which requires the roles/iam.serviceAccountTokenCreator role on that service account. Your login no longer needs --scopes.

Usage

python ${CLAUDE_PLUGIN_ROOT}/skills/query/scripts/query.py \
  --sql "<read-only SELECT over moz-fx-data-shared-prod.customer_experience_derived.<table>>"

Reference tables by their fully-qualified name (moz-fx-data-shared-prod.customer_experience_derived.<table>).

When to use

Use this skill instead of embed + vector-search when the question requires:

• Counts ("how many tickets about X?")
• Rankings ("top N topics by volume")
• Distributions ("ticket count by product")
• Aggregations ("average sentiment by category")
• Any answer that is a number, ranked list, or summary statistic

Use embed + vector-search when the question requires reading and synthesizing document text.

Common SQL patterns

Dataset: moz-fx-data-shared-prod.customer_experience_derived Tables: kitsune_retrieval_index, zendesk_retrieval_index, knowledge_base_retrieval_index

Date filter columns:

• Kitsune: creation_date DATE — use creation_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
• Zendesk: creation_date DATE — use creation_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
• Knowledge Base: query the whole table — no date filter. It is a small, curated reference set, so never bound it by last_approved_revision_date or any other date column.

Every query against the date-partitioned Kitsune/Zendesk tables must carry a creation_date window — the period the caller asked for, or the last 30 days by default. "Most recent / latest N" questions bound the window first and ORDER BY creation_date DESC LIMIT N within it; never run an unbounded ORDER BY creation_date DESC across all history. (The Knowledge Base is exempt — query it in full.)

-- Most recent N problems reported (Zendesk) — window first, then order within it
SELECT creation_date, ticket_summary_llm, product
FROM `moz-fx-data-shared-prod.customer_experience_derived.zendesk_retrieval_index_v1`
WHERE creation_date BETWEEN '2026-07-09' AND '2026-07-16'
ORDER BY creation_date DESC LIMIT 3

-- Top topics by volume (Kitsune)
SELECT topic, COUNT(*) AS count
FROM `moz-fx-data-shared-prod.customer_experience_derived.kitsune_retrieval_index_v1`
WHERE creation_date BETWEEN '2026-03-24' AND '2026-04-22'
GROUP BY topic ORDER BY count DESC LIMIT 10

-- Top categories by volume (Zendesk)
SELECT ticket_category_llm, COUNT(*) AS count
FROM `moz-fx-data-shared-prod.customer_experience_derived.zendesk_retrieval_index_v1`
WHERE creation_date BETWEEN '2026-03-24'  
  ticket_category_llm   count  LIMIT 


 topic, ()  count, ROUND((question_sentiment_score), )  avg_sentiment
 `mozfxdatasharedprod.customer_experience_derived.kitsune_retrieval_index_v1`
 creation_date    
  topic   avg_sentiment  LIMIT 


 product, ()  count
 `mozfxdatasharedprod.customer_experience_derived.zendesk_retrieval_index_v1`
 creation_date    
  product   count 

Output

A plain-text formatted table:

topic          count
-------------  -----
site-breakage  142
sync           98
passwords      74

Troubleshooting

Symptom	Fix
Authentication rejected (401) / GCP authentication required	Re-run gcloud auth application-default login; confirm you have roles/iam.serviceAccountTokenCreator on the service account in SERVICE_ACCOUNT
Missing dependency	pip install google-auth requests
Refusing to run … outside the allowed dataset / Only read-only SELECT	Query only tables or views in moz-fx-data-shared-prod.customer_experience_derived with a single read-only SELECT
No results	Check date range, table name, and filter values