sidebar.joinCommunitysidebar.communicateWithUsers
sidebar.joinAskTableCommunity
sidebar.wechat
sidebar.feishu
sidebar.chooseYourWayToJoin
sidebar.contactUssidebar.getProfessionalSupport
sidebar.contactUs
sidebar.scanToAddConsultant
sidebar.wechat
sidebar.feishu
sidebar.chooseYourWayToJoin
sidebar.scanToAddConsultant
E-commerce is the most typical data-driven industry. From GMV and conversion rates to user lifetime value (LTV), every decision requires data support. However, the complexity of e-commerce data also troubles many teams: data scattered across multiple systems, inconsistent indicator definitions, business personnel rely on technical teams for data queries...
This article systematically sorts out the complete system of e-commerce data analysis and explores how to improve analysis efficiency with AI-driven natural language query tools.
Core Objective: Monitor transaction health and discover growth opportunities
Definition: Total value of merchandise ordered by users (not deducting refunds)
Calculation Formula:
GMV = SUM(Order Amount)
Condition: Order Status = 'Paid' or 'Completed'
Analysis Dimensions:
Key Insights:
Natural Language Query Examples (using AskTable):
"This month's GMV compared to last month"
"GMV proportion by category"
"Which channel has the fastest GMV growth?"
Definition: Actual transaction amount after deducting refunds
Calculation Formula:
Net Sales = GMV - Refund Amount
Why It Matters:
Analysis Scenarios:
Definition: Total number of successfully paid orders
Key Metrics:
Correlation Analysis:
GMV = Order Volume × Average Order Value
If GMV grows but order volume remains unchanged, it indicates average order value increased (possibly product upgrade or price increase)
Natural Language Query Examples:
"Order volume trend for the past 7 days"
"Compare this week's order volume with last week"
"Find the day when order volume suddenly dropped"
Definition: Average amount per order
Calculation Formula:
AOV = GMV / Order Volume
Strategies to Increase AOV: -满减 activities (spend 99 get 20 off)
Analysis Dimensions:
Natural Language Query Examples:
"What is this month's AOV?"
"Which user group has the highest AOV?"
"Proportion of orders with AOV below 50 yuan"
Core Objective: Understand user behavior and enhance user value
Key Metrics:
Analysis Scenarios:
Scenario 1: Channel Effectiveness Analysis
Question: "Which channel has the lowest acquisition cost?"
Analysis: Compare CAC and user quality (retention rate, LTV) by channel
Decision: Increase investment in low-CAC and high-LTV channels
Scenario 2: Registration Conversion Funnel
Flow: Visit homepage → Registration page → Fill info → Verify phone → Registration success
Analysis: Find the step with the most serious attrition
Optimization: Simplify registration process, reduce attrition
Natural Language Query Examples:
"New users by channel this week"
"What is the registration conversion rate?"
"Which channel has the best user quality?" (need to define "quality")
Key Metrics:
Activity Definition: Different businesses define "active" differently:
E-commerce Recommended Definition: At least one of the following:
Activity Tiering:
Natural Language Query Examples:
"What is this month's MAU?"
"What is the activity rate trend?"
"How many users are in dormant state?"
Definition: Proportion of new users who continue using the product after registration
Key Metrics:
Retention Curve Analysis:
Excellent retention curve:
Day 1: 70%
Day 7: 50%
Day 30: 40%
Gradually flattens, indicating long-term value
Poor retention curve:
Day 1: 30%
Day 7: 10%
Day 30: 3%
Rapid decay, indicating insufficient product value
Natural Language Query Examples:
"Day 1 retention rate for users registered last week"
"Compare Day 7 retention by channel"
"Retention rate change trend over time"
Conversion Funnel:
Visit homepage (100%)
↓ 70%
Browse products (70%)
↓ 40%
Add to cart (28%)
↓ 50%
Submit order (14%)
↓ 80%
Payment success (11.2%)
Key Conversion Rates:
Strategies to Improve Conversion:
Natural Language Query Examples:
"What is this month's first purchase conversion rate?"
"At which step is cart-to-pay conversion losing the most?"
"New user coupon usage rate"
Key Metrics:
ARPU (Average Revenue Per User):
ARPU = Total Revenue / Total Users
ARPPU (Average Revenue Per Paying User):
ARPPU = Total Revenue / Paying Users
LTV (Lifetime Value): Total value created by users throughout their lifecycle
Simplified formula: LTV = AOV × Purchase frequency × Average lifecycle (months)
Example:
LTV vs CAC Ratio:
Natural Language Query Examples:
"What is the ARPPU for paying users?"
"How many high-value users (LTV > 5000)?"
"Compare LTV by channel"
Core Objective: Optimize product structure, improve sales efficiency
Key Metrics:
Analysis Scenarios:
Scenario 1: Hit Product Analysis
Identify hits: Products with sudden sales surge
Analyze reasons: Promotional activities, KOL recommendations, seasonal demand
Operations strategy: Increase inventory, boost exposure, similar product recommendations
Scenario 2: Slow-Moving Product Handling
Identify slow-movers: Products with no sales for 30 days
Analyze reasons: Price too high, unclear description, wrong category
Handling strategy: Price reduction, optimize detail page, adjust category
Natural Language Query Examples:
"Top 10 products by sales this month"
"Find products with no sales for 30 days"
"Category with the lowest inventory turnover"
Conversion Funnel:
Product exposure
↓
Click to enter detail page
↓
Add to cart
↓
Purchase
Key Metrics:
Optimization Directions:
Market Basket Analysis:
Discovery: 60% of users who purchase Product A also purchase Product B
Applications:
- Recommend Product B on Product A detail page
- Set bundle discounts (A + B at 10% off)
- Place A and B together in warehouse to improve picking efficiency
Association Rules:
Support = P(A ∩ B): Proportion of orders containing both A and B
Confidence = P(B|A): Proportion purchasing B among those who purchased A
Lift = P(B|A) / P(B): Greater than 1 indicates positive correlation
Natural Language Query Examples:
"Which products are frequently purchased together?"
"What do users who bought phones also buy?"
"Recommend products with the highest association with Product A"
Core Objective: Evaluate operations activity effectiveness, optimize operations strategies
Before activity:
During activity:
After activity:
Natural Language Query Examples:
"What is Double 11 activity GMV?"
"New users during activity compared to normal days"
"Coupon usage and redemption rates"
Traffic Sources:
Key Metrics:
Traffic Quality Evaluation:
High-quality traffic characteristics:
- Low bounce rate (< 30%)
- High average visit duration (> 3 minutes)
- High conversion rate (> 2%)
Low-quality traffic characteristics:
- High bounce rate (> 70%)
- Low average visit duration (< 30 seconds)
- Low conversion rate (< 0.5%)
Natural Language Query Examples:
"What are today's PV and UV?"
"Which channel has the highest bounce rate?"
"Which page has the highest traffic?"
Demographic Characteristics:
Behavioral Characteristics:
User Tiering (RFM Model):
R (Recency): Days since last purchase
F (Frequency): Purchase frequency
M (Monetary): Spending amount
Important Value Customers: Low R, High F, High M
Important Keep Customers: Low R, High F, Medium M
Important Development Customers: Low R, Medium F, Low M
Important Retention Customers: High R, High F, High M
Precision Marketing Application:
Goal: Improve activity for important retention customers
Strategy:
1. Issue exclusive coupons
2. Push personalized products
3. VIP customer care
Natural Language Query Examples:
"User age distribution"
"How many important value customers?"
"Characteristics of churn risk users (no orders for 30 days)"
Problem: GMV decreased 15% month-over-month, need to find the cause.
Analysis Path:
Step 1: Decompose GMV
GMV = Traffic × Conversion Rate × AOV
Using natural language query (AskTable):
"Compare this month's and last month's traffic, conversion rate, AOV"
Step 2: Locate Problem
Found: Traffic normal, conversion rate decreased 20%, AOV normal
Conclusion: Problem is with conversion rate
Step 3: Analyze Conversion Funnel
"Changes in add-to-cart and payment conversion rates"
Found: Add-to-cart conversion normal, payment conversion decreased
Conclusion: Problem is in the payment step
Step 4: Deep Investigation
Possible causes:
- Payment system malfunction
- Reduced promotional activities
- Competitor promotions stealing traffic
Query: "Number of payment failed orders"
Query: "Compare coupon usage rates"
Step 5: Verify Hypotheses
Found: Coupon usage rate dropped from 40% to 10%
Cause: Last month's promotion ended, reduced promotional intensity
Suggestion: Restore appropriate promotional activities
Problem: New user Day 1 retention only 20%, far below industry average of 40%.
Analysis Path:
Step 1: Compare by Channel
"Day 1 retention by channel for new users"
Found: Information flow ad channel at 10%, organic search at 50%
Conclusion: Information flow ads have poor quality, dragging down overall retention
Step 2: Analyze Churn Reasons
"Post-registration behavior of churned users"
Found: 80% of churned users only browsed the homepage, didn't enter product detail pages
Cause: Homepage content not attractive, or target users not precise
Step 3: Analyze Retained User Characteristics
"Differences between retained and churned users"
Found retained user characteristics:
- Browsed 5+ products within 10 minutes after registration
- 70% used new user coupons
- 50% added to cart
Step 4: Optimization Strategies
Strategy 1: Optimize new user onboarding process
- Push personalized product recommendations after registration
- Strengthen new user coupon reminders
Strategy 2: Optimize advertising investment
- Pause low-retention channel advertising
- Optimize ad copy to attract precise users
Step 5: A/B Test Verification
Control group: Original process
Experimental group: New onboarding process
After 7 days, query: "Compare A/B test retention rates"
Verify effect, decide whether for full rollout
Problem: Warehouse has大量 slow-moving products accumulating, occupying capital and storage space.
Analysis Path:
Step 1: Identify Slow-Moving Products
"List of products with 0 sales in 30 days"
"Bottom 100 products by inventory turnover"
Step 2: Analyze Slow-Movement Reasons
Reason 1: Seasonal products out of season (e.g., winter down jackets)
Reason 2: Price too high (30% higher than similar products)
Reason 3: Insufficient exposure (low search ranking, few recommendations)
Reason 4: Product quality issues (many negative reviews)
Step 3: Classified Handling
Strategy 1 (Out-of-season products):
- Price reduction to clear
- Bundle sales
- Pre-sell next year's inventory
Strategy 2 (Too high price):
- Adjust pricing
- Bundle with hit products for promotion
Strategy 3 (Insufficient exposure):
- Increase advertising investment
- Optimize SEO
- Participate in event promotions
Strategy 4 (Quality issues):
- Stop restocking
- Clearance sale
- Change suppliers
Step 4: Effect Tracking
"Sales situation of clearance activities"
"Trend of slow-moving product inventory reduction"
Pain Points of Traditional Approach:
Scenario: Operations wants to know "first purchase conversion rate for new users this week"
Total time: 3-5 days
AskTable Approach:
Total time: 30 seconds
Configure once, everyone uses
Configure business semantic layer in AskTable:
Indicator Definition:
- Name: First Purchase Conversion Rate
Definition: Proportion of new users completing first purchase within 30 days after registration
Calculation: COUNT(DISTINCT First Purchase Users) / COUNT(DISTINCT New Users)
Synonyms: [New User Conversion Rate, First Buy Conversion Rate]
- Name: GMV
Definition: Sum of amounts for paid orders
Calculation: SUM(amount) WHERE status IN ('paid', 'completed')
Synonyms: [Sales, Transaction Volume, Total Sales]
Dimension Definition:
- Name: Registration Channel
Field: users.register_channel
Possible Values: [Search Engine, Social Media, Advertising, Organic Traffic]
- Name: Time
Field: created_at
Supported Granularity: [Hour, Day, Week, Month, Quarter, Year]
Effects:
Case: A Cross-Border E-commerce Platform
Background:
After Introducing AskTable:
Week 1:
Week 2:
Month 1:
After 3 Months:
North Star Metric: Choose 1 most core metric as the business "north star"
Common e-commerce north star metrics:
Key Indicator Tree: Decompose around the north star metric into executable sub-indicators
North Star Metric: GMV
├─ Traffic
│ ├─ New Users
│ │ ├─ New users by channel
│ │ └─ Acquisition cost
│ └─ Active Users
│ ├─ DAU/MAU
│ └─ Activity rate
├─ Conversion Rate
│ ├─ First Purchase Conversion Rate
│ ├─ Repurchase Conversion Rate
│ └─ Conversion Rates by Step
└─ AOV
├─ ARPU
├─ ARPPU
└─ Attachment Rate (products per order)
Daily Report (Operations):
Weekly Report (Management):
Monthly Report (Whole company):
Discover Problem → Propose Hypothesis → Verify with Data → Develop Strategy → A/B Test → Full Rollout
Sensitive Data Protection:
Permission Control:
Data Auditing:
E-commerce data analysis is a systematic project requiring:
Complete Indicator System:
Efficient Analysis Tools:
Data-Driven Culture:
In the AI era, data analysis is no longer the exclusive skill of data teams but should become a basic capability for every business person. Through AI tools like AskTable, "everyone is a data analyst" is no longer a slogan but reality.
Learn More:
sidebar.noProgrammingNeeded
sidebar.startFreeTrial