From xiaohongshu-complete-skills
Builds Excel spreadsheets to analyze Xiaohongshu performance data, create custom dashboards with pivot tables, charts, formulas, and conduct manual social media analytics.
npx claudepluginhub vivy-yi/xiaohongshu-skills --plugin xiaohongshu-complete-skillsThis skill uses the workspace's default tool permissions.
Excel analytics is the systematic use of spreadsheet software to organize, analyze, visualize, and report on Xiaohongshu performance data, enabling custom analysis, flexible reporting, and data-driven insights without requiring specialized analytics tools.
Guides Next.js Cache Components and Partial Prerendering (PPR) with cacheComponents enabled. Implements 'use cache', cacheLife(), cacheTag(), revalidateTag(), static/dynamic optimization, and cache debugging.
Guides building MCP servers enabling LLMs to interact with external services via tools. Covers best practices, TypeScript/Node (MCP SDK), Python (FastMCP).
Generates original PNG/PDF visual art via design philosophy manifestos for posters, graphics, and static designs on user request.
Excel analytics is the systematic use of spreadsheet software to organize, analyze, visualize, and report on Xiaohongshu performance data, enabling custom analysis, flexible reporting, and data-driven insights without requiring specialized analytics tools.
Use when:
Do NOT use when:
Before (manual, scattered, inefficient):
❌ "Data scattered across screenshots and exports"
❌ "Manual calculator for engagement rates"
❌ "No visual trends, just numbers in cells"
❌ "Recreating same reports from scratch every week"
After (organized, automated, insightful):
✅ "All data in one structured master spreadsheet"
✅ "Automatic calculations update when data changes"
✅ "Interactive charts show trends at glance"
✅ "Reusable dashboard template saves hours weekly"
Excel Analytics Workflow:
| Excel Feature | Use Case | Complexity | Time Saved |
|---|---|---|---|
| Pivot Tables | Summarize large datasets | Medium | 2-3 hours/week |
| VLOOKUP/XLOOKUP | Match data across sheets | Medium | 1-2 hours/week |
| Charts | Visualize trends | Simple | 1 hour/week |
| Conditional Formatting | Highlight insights | Simple | 30 min/week |
| Formulas | Calculate metrics | Medium | 2-3 hours/week |
| Macros | Automate repetitive tasks | Advanced | 5+ hours/week |
Spreadsheet Architecture:
Workbook Structure (Separate Sheets/Tabs):
Sheet 1: README
- Purpose of spreadsheet
- How to use guide
- Definitions of metrics
- Contact for questions
Sheet 2: Raw Data Import
- Direct export from Xiaohongshu
- Date stamps for each import
- Raw, unmanipulated data
- Historical archive
Sheet 3: Content Performance Log
- One row per post
- Columns: Date, Content Type, Topic, Reach, Impressions, Likes, Comments, Shares, Saves, Engagement Rate
- Ongoing manual or automated entry
Sheet 4: Follower Growth Tracker
- Daily/weekly follower counts
- Growth rate calculations
- Follower acquisition sources
- Churn tracking
Sheet 5: Key Metrics Dashboard
- Executive summary
- Top-level KPIs
- Traffic light indicators (Green/Yellow/Red)
- Trend visualizations
Sheet 6: Content Analysis
- Performance by content type
- Performance by topic
- Top/bottom performing posts
- A/B test results
Sheet 7: Time Analysis
- Performance by day of week
- Performance by time of day
- Seasonal patterns
- Best posting times
Sheet 8: Competitor Tracking
- Competitor metrics
- Comparative analysis
- Market share
- Benchmarking
Sheet 9: Goals and Targets
- Target metrics by period
- Progress vs goals
- Gap analysis
- Forecasting
Sheet 10: Charts and Visualizations
- Trend charts
- Comparison charts
- Distribution charts
- Custom dashboards
Data Dictionary (Column Definitions):
Essential Columns for Content Log:
A: Date (YYYY-MM-DD format)
B: Content_ID (Unique identifier)
C: Content_Type (Post, Video, Story, Live)
D: Topic (Primary theme/category)
E: Title (Post title or description)
F: Publish_Time (HH:MM format)
G: Day_of_Week (Monday-Sunday)
H: Reach (Unique users who saw content)
I: Impressions (Total displays)
J: Likes (❤️ reactions)
K: Comments (💬 responses)
L: Shares (📤 forwards)
M: Saves (📑 bookmarks)
N: Profile_Visits (👁️ profile clicks)
O: Follows_Gained (📈 new followers from post)
P: Engagement_Rate (Calculated)
Q: CTR (Click-through rate if applicable)
R: Conversion_Signups (Email, purchase, etc.)
S: Cost (If paid promotion)
T: Notes (Campaign context, etc.)
Optional Advanced Columns:
U: Hashtag_1, Hashtag_2, Hashtag_3
V: Product_Mentioned
W: Promo_Code_Used
X: A/B_Test_Variant
Y: Season (Spring/Summer/Fall/Winter)
Z: Campaign_Name
Data Import Methods:
Method 1: Manual Entry
Best for: Small accounts, <50 posts/week
Process:
- Create template with required columns
- Manual entry from Xiaohongshu analytics
- Time required: 30-60 minutes/week
Pros: Complete control, no technical skill needed
Cons: Time-consuming, human error risk
Method 2: Export and Import
Best for: Medium accounts, 50-200 posts/week
Process:
- Export from Xiaohongshu Pro analytics
- Download CSV file
- Import into Excel
- Map columns to your structure
Steps:
1. Xiaohongshu Pro → Analytics → Content Performance
2. Select date range
3. Export as CSV
4. Excel → Data → From Text/CSV
5. Select file and import
6. Transform data to match your structure
Pros: Faster, less error-prone
Cons: Requires export capability, some cleanup needed
Method 3: API Integration (Advanced)
Best for: Large accounts, 200+ posts/week
Process:
- Use Xiaohongshu API (if available)
- Connect Excel to API (Power Query)
- Automated data refresh
- Real-time or scheduled updates
Pros: Automated, real-time, scalable
Cons: Technical setup, may require developer
Data Cleaning Checklist:
✅ Remove duplicate entries
✅ Fix date formats (consistent YYYY-MM-DD)
✅ Handle missing values (0 or NA)
✅ Remove special characters from text
✅ Standardize text casing (proper or upper)
✅ Verify data accuracy (spot check)
✅ Create backup before manipulation
Essential Excel Formulas:
Engagement Rate Formula:
=IF(H2>0, (J2+K2+L2+M2)/H2, 0)
Where H=Reach, J=Likes, K=Comments, L=Shares, M=Saves
Follower Growth Rate Formula:
=(O2/SUM($O$1:O2))*100
Where O=Follows Gained, calculates cumulative growth rate
Week-over-Week Growth Formula:
=(SUM(H2:H8)-SUM(H9:H15))/SUM(H9:H15)
Compares this week to last week
Moving Average Formula (7-day):
=AVERAGE(J2:J8)
Smooths daily fluctuations
Percentage Change Formula:
=(J2-J1)/J1
Shows change from previous period
Conditional Formatting Rules:
Highlight top performers:
- Engagement rate > 10%: Green
- Engagement rate 5-10%: Yellow
- Engagement rate < 5%: Red
Highlight outliers:
- Values > 2x standard deviation: Bold
- Values = 0: Gray text
Identify best performing content type:
=MAXIFS(P:P, C:C, "Video")
Returns max engagement rate for Video content
Calculate content type averages:
=AVERAGEIF(C:C, "Video", P:P)
Average engagement rate for Video posts
Advanced Formulas (Pivot Tables): Create dynamic summaries:
Pivot Table Steps:
### Step 4: Build Visualizations
**Chart Creation Guide**:
Chart Type Selection:
Line Chart (Trend Analysis) Best for: Metrics over time Data: Date on X-axis, Metric on Y-axis Examples:
Bar Chart (Comparisons) Best for: Comparing categories Data: Categories on X-axis, Metric on Y-axis Examples:
Column Chart (Rankings) Best for: Top/bottom performers Data: Items ranked by metric Examples:
Pie Chart (Distributions) Best for: Showing proportions Data: Category and percentage Examples:
Scatter Plot (Correlations) Best for: Relationship between two metrics Data: Metric 1 on X-axis, Metric 2 on Y-axis Examples:
Combo Chart (Multiple Metrics) Best for: Related metrics with different scales Data: Two chart types combined Examples:
Chart Best Practices: ✅ Clear title describing what chart shows ✅ Axis labels with units (%, K, M) ✅ Legend for multi-series charts ✅ Data labels for key points ✅ Consistent color scheme (on-brand) ✅ Not too much data (cluttered) ✅ Appropriate chart type for data ❌ Avoid 3D charts (harder to read) ❌ Avoid excessive colors (3-4 max) ❌ Don't clutter with gridlines ❌ No decorative elements (distracting)
**Dashboard Design**:
Executive Dashboard Layout:
Row 1: Header and Date Range
Row 2: Key Performance Indicators (Big Numbers)
Row 3-4: Trend Charts (Left - Right)
Row 5-6: Comparison Charts
Row 7-8: Rankings
Row 9: Goals and Progress
Interactive Elements:
Conditional Formatting:
Auto-Refresh Setup (if using external data):
### Step 5: Analyze and Extract Insights
**Analysis Framework**:
Weekly Analysis Routine:
Content Type Analysis
Topic Analysis
Timing Analysis
Competitive Analysis
Total Time: ~80 minutes per week Saves: 3-4 hours vs manual analysis
Insight Extraction Techniques:
Performance Analysis:
Correlation Analysis:
Segmentation Analysis:
Gap Analysis:
### Step 6: Report and Communicate Findings
**Report Structure**:
Weekly Performance Report (1-2 pages):
Page 1: Executive Summary
Page 2: Detailed Analysis
Monthly Performance Report (5-8 pages):
Page 1: Executive Dashboard
Page 2: Content Deep Dive
Page 3: Audience Insights
Page 4: Timing and Seasonality
Page 5: Competitive Analysis
Page 6: Goals and Forecast
Page 7: Appendices
Presentation Tips: ✅ Start with key insights (executive summary first) ✅ Use visuals (charts, graphs, dashboards) ✅ Keep text minimal (let data speak) ✅ Highlight key numbers (color, size, bold) ✅ Tell story with data (context, insights, actions) ✅ Include recommendations (actionable next steps) ✅ Know your audience (execs need summaries, analysts need detail) ❌ Don't overwhelm with data (curate for relevance) ❌ Don't present without interpretation (add insights) ❌ Don't omit context (explain why numbers matter)
## Common Mistakes
| Mistake | Why Happens | Fix |
|---------|-------------|-----|
| Not updating data regularly | Time pressure, forget | Schedule weekly update, set reminders |
| Poor data structure | Started without plan | Design structure before collecting data |
| Wrong formulas | Excel knowledge gap | Validate formulas, test with known data |
| Over-complicating dashboards | Want everything visible | Focus on key metrics, keep simple |
| Not backing up data | Complacency | Save backup copies regularly |
| Manual data entry errors | Human error | Use data validation, double-check |
| Ignoring data quality | Trust exports blindly | Clean and validate imported data |
| Too many charts | Want comprehensive analysis | Fewer, more focused visualizations |
| Not documenting metrics | Assumes everyone knows | Create data dictionary |
| Forgetting to refresh | Static dashboard | Set refresh schedule, note last update |
## Real-World Impact
**Case Study: Excel Analytics Transformation**
A brand manager created Excel analytics system replacing manual reporting.
**Before Excel Analytics**:
- Manual data collection from screenshots
- Calculator for engagement rates
- No visual trends, just numbers
- 4 hours weekly for basic reporting
- Inconsistent metrics (different definitions)
- No historical analysis
- Poor insights, missed opportunities
**After Excel Implementation** (3 months):
System Setup (Month 1):
- Created master spreadsheet with 10 sheets
- Automated imports from Xiaohongshu
- Built formulas for 15 calculated metrics
- Created executive dashboard
- Set up conditional formatting
- Documented data dictionary
Analysis and Refinement (Months 2-3):
- Added competitor tracking
- Created content type analysis
- Built time-of-day analysis
- Developed A/B testing templates
- Automated weekly report generation
- Created monthly deep-dive templates
**Results** (12 months):
Time Savings:
- Weekly reporting: 4 hours → 45 minutes (82% reduction)
- Monthly analysis: 8 hours → 2 hours (75% reduction)
- Annual savings: 250+ hours
Improved Insights:
- Identified optimal posting time: 7-9 PM (+40% engagement)
- Discovered top content type: Video tutorials (3x engagement)
- Found underserved topic: Ingredient education (filled gap, +25% growth)
- Spent less time on reporting, more time on strategy
Business Impact:
- Engagement rate: +35% from optimization
- Follower growth: +50% from data-driven content
- Content efficiency: 40% improvement (fewer posts, better results)
- Team productivity: +15% (time saved reinvested)
- Decision quality: Evidence-based, not gut feeling
Spreadsheet Capabilities Built:
- Automated data import (saves 1 hour/week)
- 15 calculated metrics (instant vs manual)
- Interactive dashboard (real-time insights)
- 12 visualization templates (consistent reporting)
- Competitor benchmarking (market intelligence)
- A/B test tracker (systematic testing)
**Data-Backed Insights**:
- Excel automation saves 75-85% of reporting time
- Visual dashboards improve insight speed by 3-5x
- Calculated metrics prevent errors and ensure consistency
- Historical data enables trend analysis and forecasting
- Competitor tracking identifies 30-40% more opportunities
- A/B testing in Excel increases optimization rate by 2x
- Conditional formatting highlights insights instantly
- Pivot tables enable multi-dimensional analysis in minutes
- Well-structured spreadsheets are scalable for years of data
- Excel skills transfer to other tools and platforms
## Related Skills
**REQUIRED**: Use data-analytics (understand metrics to track)
**REQUIRED**: Use data-metrics-understanding (know what metrics mean)
**REQUIRED**: Use data-report (communicate findings)
**Recommended for Excel mastery**:
- **pivot-tables** - Advanced data summarization and analysis
- **data-visualization** - Chart and dashboard design principles
- **advanced-formulas** - Complex calculations and lookups
- **macros-automation** - VBA and automation for Excel
- **power-query** - Data import and transformation
- **spreadsheet-design** - Structure and architecture best practices