↓ Skip to main content
Daniela Petruzalek
Daniela Petruzalek

Senior Developer Relations Engineer at Google

Writing about AI agents, Go, and Cloud.

Skill Analytics SQL Query Cookbook #

All SQL recipes below run directly against skill_analytics.db using uv run scripts/analyze.py query "<SQL>". Pair these queries with skill-optimizer to prune shelfware skills, tune under-performing frontmatter descriptions, and discover catalog gaps.

1. Shelfware & Context-Tax Audit (Always-In-Prompt Skills with 0 Activations) #

Identifies built-in, plugin, and globally installed skills that consume system-prompt tokens on every turn (is_always_in_prompt = 1) but have never been activated or executed in production conversations.

sql 
SELECT
    skill_name,
    source_tier,
    source_origin,
    tier1_tokens_est,
    cumulative_prompt_tax_tokens,
    roi_recommendation
FROM v_skill_roi
WHERE is_always_in_prompt = 1
  AND total_activations = 0
  AND script_runs = 0
ORDER BY tier1_tokens_est DESC, skill_name ASC;

2. High-Demand JIT Skills to Promote to Global Scope #

Finds catalog skills frequently loaded on-demand via kungfu load (PROMOTE_TO_GLOBAL) that are not yet installed globally.

sql 
SELECT
    skill_name,
    category,
    total_activations,
    resource_reads,
    script_runs,
    conversations_used,
    conversation_reach_pct || '%' AS reach_pct,
    last_used_at
FROM v_skill_roi
WHERE is_always_in_prompt = 0
  AND total_activations >= 5
ORDER BY total_activations DESC, conversations_used DESC;

3. Trigger Attribution Audit for skill-optimizer (Manual Override vs. Autonomous Match) #

Pinpoints skills that users must manually invoke with ::skill, //skill, or /skill because agents fail to match their frontmatter description autonomously (NEEDS_DESCRIPTION_TUNING or WEAK_AUTONOMOUS_TRIGGER).

sql 
SELECT
    skill_name,
    source_tier,
    total_activations,
    autonomous_activations,
    explicit_activations,
    autonomous_share_pct || '%' AS auto_pct,
    trigger_health
FROM v_trigger_attribution
WHERE total_activations > 0
ORDER BY explicit_activations DESC, total_activations DESC;

4. Progressive Disclosure Depth (Root Loads vs. Reference Reads & Script Runs) #

Measures how deeply agents engage with each skill’s Tier-3 companion references/ and scripts/ after loading the main SKILL.md.

sql 
SELECT
    skill_name,
    source_tier,
    total_activations,
    resource_reads,
    script_runs,
    ROUND(1.0 * (resource_reads + script_runs) / MAX(1, total_activations), 2) AS tier3_depth_ratio
FROM v_skill_roi
WHERE total_activations > 0 OR resource_reads > 0 OR script_runs > 0
ORDER BY (resource_reads + script_runs) DESC, total_activations DESC
LIMIT 20;

5. Catalog Gap Discovery (Unmet kungfu find and :? Searches) #

Lists search queries issued via kungfu find or :? that did not result in a subsequent skill load within 12 steps, revealing missing topics in the catalog.

sql 
SELECT
    search_query,
    invocation_kind,
    search_count,
    led_to_load_count,
    last_searched_at
FROM v_search_gaps
WHERE led_to_load_count = 0
ORDER BY search_count DESC, last_searched_at DESC;

6. User Prompt Notation Adoption Over Time (::skill vs. //skill vs. :?) #

Tracks the evolution of user prompt shortcut notations across conversations.

sql 
SELECT
    substr(step_created_at, 1, 10) AS day,
    notation_type,
    COUNT(*) AS notation_count,
    COUNT(DISTINCT conversation_id) AS conversations
FROM skill_events
WHERE invocation_kind = 'user_notation'
  AND is_self_test = 0
GROUP BY day, notation_type
ORDER BY day DESC, notation_count DESC;

7. KungFu state.json Telemetry Drift Audit #

Compares recorded load_count values in ~/.config/kungfu/state.json against ground-truth transcript activations.

sql 
SELECT
    skill_name,
    state_json_count,
    transcript_cli_load_primary,
    transcript_learn_load,
    transcript_direct_views,
    transcript_total_activations,
    drift_total_vs_state,
    drift_diagnosis
FROM v_skill_telemetry_drift
ORDER BY ABS(drift_total_vs_state) DESC, transcript_total_activations DESC;