Excel Analytics (Excel数据分析)

Overview

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.

When to Use

Use when:

Building custom tracking spreadsheets for Xiaohongshu data
Analyzing exported data from platform analytics
Creating personalized dashboards and reports
Conducting custom calculations and metrics
Visualizing performance trends and patterns
Learning data analysis fundamentals
Comparing multiple time periods or content types
Creating executive summaries for stakeholders

Do NOT use when:

Real-time monitoring needed (use platform analytics)
Complex statistical analysis required (use specialized tools)
Automated reporting at scale (use BI tools)
Simple quick checks (use platform native analytics)

Core Pattern

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:

Import - Bring data from Xiaohongshu into Excel
Clean - Structure and format data properly
Calculate - Create metrics and KPIs with formulas
Visualize - Build charts and dashboards
Analyze - Extract insights and identify patterns
Report - Summarize findings for decision-making

Quick Reference

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

Implementation

Step 1: Set Up Master Spreadsheet

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

Step 2: Import and Clean Data

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

Step 3: Create Calculated Metrics

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:

Total engagement by content type
Average reach by topic
Best posting times by day
Monthly performance comparisons
Top 10 posts by engagement

Pivot Table Steps:

Select data range (including headers)
Insert → Pivot Table
Choose rows (e.g., Content_Type)
Choose values (e.g., Average of Engagement_Rate)
Add filters (e.g., Date range)
Format and style pivot table
Refresh when data updates


### 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:

Follower growth trend
Engagement rate over time
Reach vs Impressions comparison

Bar Chart (Comparisons) Best for: Comparing categories Data: Categories on X-axis, Metric on Y-axis Examples:

Engagement by content type
Reach by topic
Performance by day of week

Column Chart (Rankings) Best for: Top/bottom performers Data: Items ranked by metric Examples:

Top 10 posts by engagement
Bottom 5 posts (identify issues)
Hashtag performance ranking

Pie Chart (Distributions) Best for: Showing proportions Data: Category and percentage Examples:

Content type distribution
Topic mix percentage
Traffic source breakdown

Scatter Plot (Correlations) Best for: Relationship between two metrics Data: Metric 1 on X-axis, Metric 2 on Y-axis Examples:

Reach vs Engagement Rate
Post Length vs Engagement
Time of Day vs Reach

Combo Chart (Multiple Metrics) Best for: Related metrics with different scales Data: Two chart types combined Examples:

Reach (bar) + Engagement Rate (line)
Impressions (bar) + CTR (line)

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

Dashboard Title: "Xiaohongshu Performance Dashboard"
Date Range Selector: Dropdown or cell reference
Last Updated: Timestamp

Row 2: Key Performance Indicators (Big Numbers)

Total Followers: [Value] [Growth %]
Total Engagement: [Sum] [Avg per post]
Avg Engagement Rate: [%] [vs last period]
Total Reach: [Sum] [Avg per post]
Top Post: [Title] [Engagement Rate]

Row 3-4: Trend Charts (Left - Right)

Line chart: Follower growth (30 days)
Line chart: Engagement rate trend (30 days)

Row 5-6: Comparison Charts

Bar chart: Performance by content type
Bar chart: Performance by day of week

Row 7-8: Rankings

Top 10 posts table (linked to data)
Bottom 5 posts table (troubleshooting)

Row 9: Goals and Progress

Goal vs Actual comparison
Traffic light status (Green/Yellow/Red)

Interactive Elements:

Drop-down for content type filter
Date range selectors (start/end cells)
Slicers for dynamic filtering
Buttons to refresh data
Hyperlinks to detailed sheets

Conditional Formatting:

KPIs > Goal: Green background
KPIs 80-100% of Goal: Yellow background
KPIs < 80% of Goal: Red background
Trends up: Green arrow
Trends down: Red arrow

Auto-Refresh Setup (if using external data):

Data → Get Data → From File
Select data source
Set refresh properties (e.g., every 24 hours)
Protect formula cells
Allow refresh on open


### Step 5: Analyze and Extract Insights

**Analysis Framework**:

Weekly Analysis Routine:

Update Data (15 min)

Import new data from Xiaohongshu
Paste into Raw Data sheet
Refresh calculations and pivot tables
Verify data integrity

Review Dashboard (10 min)

Check KPIs vs goals
Review trend charts for anomalies
Identify top/bottom performers
Note significant changes

Deep Dive Analysis (20 min)

Content Type Analysis
- Which types performed best?
- Any types underperforming?
- Seasonal patterns?
Topic Analysis
- Which topics resonated?
- Any surprises?
- Content gaps identified?
Timing Analysis
- Best/worst posting times
- Day-of-week patterns
- Optimal posting schedule
Competitive Analysis
- How do we compare?
- Any competitor wins to learn from?
- Market shifts?

Document Insights (15 min)

Key findings: What worked, what didn't
Recommendations: Action items for next week
Tests running: A/B tests in progress
Questions raised: Unknowns to investigate

Report Creation (20 min)

Executive summary (1 page)
Detailed analysis (2-3 pages)
Visualizations (charts, graphs)
Appendices (raw data if needed)

Total Time: ~80 minutes per week Saves: 3-4 hours vs manual analysis

Insight Extraction Techniques:

Performance Analysis:

Calculate growth rates (week-over-week, month-over-month)
Compare to benchmarks (past performance, goals, competitors)
Identify outliers (exceptionally good/bad)
Spot trends (improving, declining, stable)

Correlation Analysis:

Post time vs Engagement
Content length vs Reach
Hashtag count vs Saves
Topic vs Follower growth

Segmentation Analysis:

Performance by content type
Performance by topic
Performance by time of day
Performance by day of week
Performance by season

Gap Analysis:

Goal vs Actual (performance gaps)
Competitor comparison (market gaps)
Audience demand vs Content supply (content gaps)
Resource allocation vs ROI (efficiency gaps)


### Step 6: Report and Communicate Findings

**Report Structure**:

Weekly Performance Report (1-2 pages):

Page 1: Executive Summary

Key metrics overview
Progress toward goals
Top 3 wins this week
Top 3 challenges/learnings
3-5 recommendations for next week

Page 2: Detailed Analysis

Content performance breakdown
Engagement quality analysis
Follower growth analysis
Competitive comparison
Testing and learnings

Monthly Performance Report (5-8 pages):

Page 1: Executive Dashboard

All KPIs visualized
Month-over-month comparison
Goal progress
Traffic light status

Page 2: Content Deep Dive

Performance by content type
Top performing posts
Underperforming content analysis
Content recommendations

Page 3: Audience Insights

Follower growth analysis
Audience demographics (if available)
Engagement quality
Audience feedback themes

Page 4: Timing and Seasonality

Best posting times
Day-of-week performance
Seasonal patterns
Holiday/event impacts

Page 5: Competitive Analysis

Market position
Competitor benchmarking
Share of voice
Opportunities/threats

Page 6: Goals and Forecast

Goal progress
Forecasting (next 30/60/90 days)
Resource allocation recommendations
Strategic initiatives

Page 7: Appendices

Detailed data tables
Methodology
Definitions
Raw data (if needed)

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

excel-analytics