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

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