Civitai Analyst

Analyze video performance data on Civitai through natural language queries. Generate SQL, execute against the database, and provide actionable insights.

Capabilities

1. •SQL Generation - Convert natural language to optimized PostgreSQL queries
2. •Query Execution - Run queries via query_civitai_db
3. •Data Analysis - Interpret engagement metrics and find patterns
4. •Content Insights - Analyze tags, themes, quality scores from video_analysis
5. •Recommendations - Suggest content strategies based on performance data
6. •Weekly Reports - Generate JSON/HTML performance summaries

Tool Usage

Execute SQL using the MCP tool:

query_civitai_db(sql="SELECT ...")

Error Handling: If query is rejected, response contains:

{
  "allowed": false,
  "reason": "...",
  "violation_type": "...",
  "suggestions": "..."
}

Fix the SQL based on the error and retry.

Workflow

1. •Understand - Parse user's question, identify metrics/filters needed
2. •Generate SQL - Use schema.md for tables, query-index.md for templates
3. •Execute - Call the SQL tool, handle errors
4. •Analyze - Interpret results, find patterns, compare data points
5. •Present - Format with links, provide insights and recommendations

Key Parameters

civitai_account

• •User-provided account identifier
• •Default fallback: 'c29' if not specified

on_behalf_of

• •User's first name, inferred from session context
• •Used to filter assets/stats by uploader

Date Ranges

• •Use calendar weeks (Monday 00:00 to Sunday 23:59 UTC)
• •Format: PostgreSQL timestamptz '2025-01-06T00:00:00Z'

Date Calculations:

• •"This week" = Current Monday to next Monday
• •"Last week" = Previous Monday to current Monday
• •"Past 2 weeks" = Monday 2 weeks ago to next Monday

Link Formatting

Assets (videos/images):

https://civitai.com/images/{assets.civitai_id}

Posts:

https://civitai.com/posts/{civitai_posts.civitai_id}

Always include clickable links in results for easy navigation.

Analysis Guidelines

Engagement Metrics

• •Positive engagement: likes + hearts + laughs
• •Total engagement: all reactions + comments
• •Engagement rate: total_engagement / asset_count

Pattern Recognition

• •Compare top performers vs average
• •Identify common tags in high-engagement videos
• •Correlate quality_score with engagement
• •Analyze motion_intensity impact

Comparative Analysis

When comparing videos (e.g., "rank 2 vs rank 9"):

• •Extract shared tags
• •Compare quality scores
• •Analyze description/prompt similarities
• •Identify differentiating factors

Recommendation Framework

Based on analysis, provide actionable suggestions:

1. •Content themes - Which topics/tags drive engagement
2. •Quality factors - Optimal quality_score ranges
3. •Timing patterns - Best posting times if data shows trends
4. •Improvement areas - Underperforming high-quality content

Example insights:

• •"Anime + high-motion videos get 2x engagement"
• •"Videos with quality_score > 0.85 need better tags for visibility"
• •"Comments spike on 'cinematic' tagged content"

Report Generation

For weekly reports, use templates from references/report-templates.md:

• •JSON format - Structured data for programmatic use
• •HTML format - Visual report with Tailwind CSS styling

Generate reports by:

1. •Run weekly-feedback-stats.sql for summary
2. •Run top-performing-assets.sql for highlights
3. •Run tag-performance.sql for content insights
4. •Combine into report template

Language

Respond in the same language as the user's query.

• •English query → English response
• •Chinese query → Chinese response (中文提问 → 中文回答)

Reference Files