Analyze Shopify AOV: Segment High-Value Customers

Step 1: Connect Your Shopify Store to MCP Analytics

Let's start with the basics and build from there. First, we need to establish a connection between your Shopify store and MCP Analytics.

1.1 Set Up the Integration

1. Log into your MCP Analytics account at mcpanalytics.ai/analysis
2. Navigate to Integrations → Add New Integration
3. Select Shopify from the available platforms
4. Click Connect to Shopify
5. You'll be redirected to Shopify to authorize the connection
6. Grant permissions for read_orders, read_customers, and read_products

1.2 Verify the Connection

After authorization, you should see a confirmation screen. Verify that:

• Your store name appears correctly
• The connection status shows "Active"
• The initial data sync begins (this may take 5-30 minutes depending on your order volume)

Expected outcome: You'll receive an email when the initial sync completes, typically containing a summary like "Synced 1,247 orders from the past 2 years."

Step 2: Pull Order Data with the Shopify Orders Module

Now that we're connected, let's extract the specific data we need for AOV analysis. This is where understanding the Shopify export format becomes important.

Understanding Shopify Order Export CSV Columns

When you export orders from Shopify (or pull them via MCP Analytics), you'll encounter several pricing columns. Let me explain the key ones:

• Lineitem Price: The actual price the customer paid per item
• Lineitem Compare-at Price: The original or "regular" price before discounts
• Total: The complete order value including taxes and shipping
• Subtotal: Order value before taxes and shipping

Many people search for variations like "shopify order export csv columns lineitem compare at price" or "shopify orders export csv columns lineitem compare-at price" because they're trying to analyze discount effectiveness alongside AOV. We'll use both in our analysis.

2.1 Extract Your Order Data

In MCP Analytics:

1. Go to Data Sources → Shopify Orders
2. Set your date range (recommend starting with the last 12 months)
3. Select these fields for export:
   • Order ID
   • Order Date
   • Customer ID
   • Customer Email
   • Subtotal
   • Total
   • Lineitem Name
   • Lineitem Price
   • Lineitem Compare-at Price
   • Lineitem Quantity
   • Discount Code
   • Referring Site
4. Click Generate Dataset

2.2 Review Your Data Structure

Your exported data will have one row per line item. Here's what a sample might look like:

Order ID    | Order Date | Customer Email      | Total   | Lineitem Name  | Lineitem Price | Lineitem Quantity
#1001       | 2024-01-15 | [email protected]   | $145.50 | Blue Widget    | $45.00         | 2
#1001       | 2024-01-15 | [email protected]   | $145.50 | Premium Setup  | $55.00         | 1
#1002       | 2024-01-15 | [email protected]    | $32.00  | Starter Pack   | $32.00         | 1

Expected outcome: You should have a dataset with multiple rows per order if customers bought multiple items. This is normal and exactly what we need for product-level analysis.

Step 3: Calculate AOV by Customer Segment and Time Period

What does this visualization tell us? Let's look together at how order values are distributed across your customer base.

3.1 Calculate Overall AOV

First, let's establish your baseline AOV. In MCP Analytics:

1. Navigate to Analysis → Create New Analysis
2. Select your Shopify Orders dataset
3. Create a calculated metric:

   AOV = SUM(Total) / COUNT(DISTINCT Order ID)

This gives you the average across all orders. For example, if you have $125,000 in revenue from 1,000 orders, your AOV is $125.

3.2 Segment by Time Period

AOV changes over time due to seasonality, promotions, and customer behavior shifts. Create a monthly trend:

SELECT
    DATE_TRUNC('month', Order_Date) as Month,
    SUM(Total) / COUNT(DISTINCT Order_ID) as AOV,
    COUNT(DISTINCT Order_ID) as Order_Count
FROM shopify_orders
GROUP BY DATE_TRUNC('month', Order_Date)
ORDER BY Month

Expected outcome: You'll see a line chart showing AOV trends. Most stores see higher AOV during November-December (holiday shopping) and dips in January-February.

3.3 Segment by Customer Type

Now let's segment by new vs. returning customers:

WITH customer_orders AS (
    SELECT
        Customer_ID,
        Order_ID,
        Order_Date,
        Total,
        ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date) as order_number
    FROM shopify_orders
)
SELECT
    CASE
        WHEN order_number = 1 THEN 'New Customer'
        ELSE 'Returning Customer'
    END as customer_type,
    SUM(Total) / COUNT(DISTINCT Order_ID) as AOV,
    COUNT(DISTINCT Order_ID) as orders
FROM customer_orders
GROUP BY customer_type

What to look for: Typically, returning customers have 20-40% higher AOV than new customers because they trust your brand and know what they want.

3.4 Segment by Discount Usage

Understanding how discounts affect AOV is crucial:

SELECT
    CASE
        WHEN Discount_Code IS NOT NULL THEN 'Used Discount'
        ELSE 'No Discount'
    END as discount_status,
    SUM(Total) / COUNT(DISTINCT Order_ID) as AOV,
    COUNT(DISTINCT Order_ID) as orders,
    COUNT(DISTINCT Customer_ID) as unique_customers
FROM shopify_orders
GROUP BY discount_status

Expected outcome: You might find that orders with discounts have higher AOV if the discount encouraged customers to add more items to reach a threshold (e.g., "spend $100, get 15% off").

Step 4: Visualize AOV Distribution to Find Customer Tiers

Before we build a model, let's just look at the data. The distribution of order values tells us a lot about customer behavior patterns.

4.1 Create an AOV Histogram

In MCP Analytics:

1. Select Visualization → Histogram
2. Set X-axis to "Total" (order value)
3. Set bin width to $10 or $20 depending on your typical order size
4. Click Generate

What to look for: Most e-commerce stores show a right-skewed distribution—lots of smaller orders and fewer large ones. Look for "humps" in the distribution that suggest natural customer tiers.

4.2 Identify Natural Breakpoints

Based on your histogram, identify tiers. Here's a common pattern:

• Budget Buyers: $0-50 (typically 40-50% of orders)
• Core Customers: $50-150 (typically 35-45% of orders)
• Premium Buyers: $150-300 (typically 10-15% of orders)
• VIP Customers: $300+ (typically 2-5% of orders)

Your breakpoints will differ based on your products. A jewelry store might have tiers at $100, $500, and $2,000, while a consumables store might use $25, $75, and $200.

4.3 Calculate Revenue Contribution by Tier

SELECT
    CASE
        WHEN Total < 50 THEN 'Budget ($0-50)'
        WHEN Total < 150 THEN 'Core ($50-150)'
        WHEN Total < 300 THEN 'Premium ($150-300)'
        ELSE 'VIP ($300+)'
    END as customer_tier,
    COUNT(DISTINCT Order_ID) as orders,
    ROUND(100.0 * COUNT(DISTINCT Order_ID) / SUM(COUNT(DISTINCT Order_ID)) OVER (), 1) as pct_of_orders,
    SUM(Total) as revenue,
    ROUND(100.0 * SUM(Total) / SUM(SUM(Total)) OVER (), 1) as pct_of_revenue
FROM shopify_orders
GROUP BY customer_tier
ORDER BY MIN(Total)

Expected outcome: You'll likely discover that your VIP tier (smallest by order count) contributes 25-35% of total revenue. This is the "80/20 rule" in action.

This finding connects to broader statistical principles explored in our guide to A/B testing and statistical significance, where understanding distributions helps you design better experiments.

Step 5: Identify High-AOV Products and Bundles

Let's start with the basics and build from there. Certain products naturally lead to higher order values, either because they're expensive or because they're frequently bought together.

5.1 Analyze Individual Product Performance

SELECT
    Lineitem_Name,
    COUNT(DISTINCT Order_ID) as times_purchased,
    AVG(order_total.Total) as avg_order_value_when_purchased,
    SUM(Lineitem_Price * Lineitem_Quantity) as product_revenue
FROM shopify_orders line
JOIN (
    SELECT Order_ID, Total
    FROM shopify_orders
    GROUP BY Order_ID, Total
) order_total ON line.Order_ID = order_total.Order_ID
GROUP BY Lineitem_Name
HAVING COUNT(DISTINCT Order_ID) >= 10
ORDER BY avg_order_value_when_purchased DESC
LIMIT 20

What this reveals: Products that appear in high-AOV orders aren't necessarily expensive themselves. Sometimes a $20 accessory is the key item that gets added to $200+ orders.

5.2 Find Effective Product Bundles

Identify which products are frequently bought together in high-value orders:

WITH high_value_orders AS (
    SELECT Order_ID, Total
    FROM shopify_orders
    WHERE Total > (SELECT AVG(Total) * 1.5 FROM shopify_orders)
    GROUP BY Order_ID, Total
)
SELECT
    a.Lineitem_Name as product_a,
    b.Lineitem_Name as product_b,
    COUNT(DISTINCT a.Order_ID) as times_bought_together,
    AVG(hvo.Total) as avg_order_value
FROM shopify_orders a
JOIN shopify_orders b ON a.Order_ID = b.Order_ID AND a.Lineitem_Name < b.Lineitem_Name
JOIN high_value_orders hvo ON a.Order_ID = hvo.Order_ID
GROUP BY product_a, product_b
HAVING COUNT(DISTINCT a.Order_ID) >= 5
ORDER BY times_bought_together DESC
LIMIT 15

Expected outcome: You'll discover natural product pairings that drive higher order values. These are prime candidates for creating formal bundles or "frequently bought together" recommendations.

5.3 Understand the Lineitem Compare-at Price Impact

Many Shopify users search for "shopify orders csv export columns lineitem compare at price" because they want to understand discount effectiveness. Let's analyze it:

SELECT
    CASE
        WHEN Lineitem_Compare_at_Price IS NULL THEN 'No Compare Price'
        WHEN Lineitem_Price < Lineitem_Compare_at_Price THEN 'Discounted'
        ELSE 'Full Price'
    END as pricing_status,
    COUNT(DISTINCT Order_ID) as orders,
    SUM(Total) / COUNT(DISTINCT Order_ID) as AOV,
    AVG(Lineitem_Compare_at_Price - Lineitem_Price) as avg_discount_amount
FROM shopify_orders
GROUP BY pricing_status

Key insight: Products shown with a compare-at price (showing the "savings") often drive higher AOV because they create urgency and perceived value.

Step 6: Set Up Automated Alerts for AOV Drops

Let me walk you through setting up monitoring so you catch problems early, before they significantly impact revenue.

6.1 Establish Your Baseline and Thresholds

First, calculate your normal AOV range:

SELECT
    AVG(daily_aov) as mean_aov,
    STDDEV(daily_aov) as std_dev,
    AVG(daily_aov) - (2 * STDDEV(daily_aov)) as lower_threshold
FROM (
    SELECT
        DATE(Order_Date) as date,
        SUM(Total) / COUNT(DISTINCT Order_ID) as daily_aov
    FROM shopify_orders
    WHERE Order_Date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY DATE(Order_Date)
) daily_stats

What this does: Calculates your normal AOV variation over the past 90 days. The lower threshold is set at 2 standard deviations below the mean—a point where only ~2.5% of normal days fall below.

6.2 Create the Alert in MCP Analytics

1. Navigate to Monitoring → Create Alert
2. Set Alert Name: "AOV Drop Detection"
3. Set Metric: Average Order Value (from your Shopify dataset)
4. Set Timeframe: Daily
5. Set Condition: "Falls below" → Enter your calculated lower threshold
6. Set Notification: Email to your address
7. Set Check Frequency: Every 6 hours

6.3 Add Context to Alerts

In the alert configuration, add additional metrics to include in the notification:

• Number of orders (to confirm it's not just low volume)
• Average discount percentage (to see if over-discounting is the cause)
• Top products sold that day (to identify if product mix changed)

Expected outcome: You'll receive an email alert within 6 hours if AOV drops significantly, with enough context to diagnose why.

This proactive approach mirrors the monitoring strategies used in AI-first data analysis pipelines, where automated detection prevents small issues from becoming major problems.

Step 7: Advanced - Cohort Analysis of AOV by Acquisition Channel

Now let's take it a step further. Not all customers are created equal, and understanding how AOV varies by where customers came from helps you invest your marketing budget wisely.

7.1 Identify Customer Acquisition Cohorts

First, tag each customer with their acquisition source based on their first order:

WITH first_orders AS (
    SELECT
        Customer_ID,
        MIN(Order_Date) as first_order_date,
        FIRST_VALUE(Referring_Site) OVER (
            PARTITION BY Customer_ID
            ORDER BY Order_Date
        ) as acquisition_source
    FROM shopify_orders
    GROUP BY Customer_ID, Referring_Site, Order_Date
),
acquisition_cohort AS (
    SELECT
        Customer_ID,
        first_order_date,
        CASE
            WHEN acquisition_source LIKE '%google%' THEN 'Google'
            WHEN acquisition_source LIKE '%facebook%' THEN 'Facebook'
            WHEN acquisition_source LIKE '%instagram%' THEN 'Instagram'
            WHEN acquisition_source IS NULL THEN 'Direct'
            ELSE 'Other'
        END as channel
    FROM first_orders
)
SELECT * FROM acquisition_cohort

7.2 Calculate AOV by Cohort and Lifecycle Stage

WITH order_numbers AS (
    SELECT
        o.Order_ID,
        o.Customer_ID,
        o.Order_Date,
        o.Total,
        ac.channel,
        ROW_NUMBER() OVER (PARTITION BY o.Customer_ID ORDER BY o.Order_Date) as order_number
    FROM shopify_orders o
    JOIN acquisition_cohort ac ON o.Customer_ID = ac.Customer_ID
)
SELECT
    channel,
    CASE
        WHEN order_number = 1 THEN 'First Order'
        WHEN order_number = 2 THEN 'Second Order'
        WHEN order_number <= 5 THEN 'Orders 3-5'
        ELSE 'Orders 6+'
    END as lifecycle_stage,
    COUNT(DISTINCT Order_ID) as orders,
    SUM(Total) / COUNT(DISTINCT Order_ID) as AOV
FROM order_numbers
GROUP BY channel, lifecycle_stage
ORDER BY channel, MIN(order_number)

What to look for: Some channels (often Google/Direct) bring customers with higher first-order AOV. Others (often social media) start lower but grow AOV over time as trust builds.

7.3 Calculate Cohort Retention and Lifetime AOV

SELECT
    channel,
    COUNT(DISTINCT Customer_ID) as customers,
    SUM(Total) / COUNT(DISTINCT Customer_ID) as revenue_per_customer,
    SUM(Total) / COUNT(DISTINCT Order_ID) as aov,
    COUNT(DISTINCT Order_ID) * 1.0 / COUNT(DISTINCT Customer_ID) as orders_per_customer
FROM order_numbers
GROUP BY channel
ORDER BY revenue_per_customer DESC

Expected outcome: This reveals which channels bring the most valuable customers over time, not just at first purchase. You might find that Instagram customers have lower AOV but order more frequently, resulting in higher lifetime value.

This type of cohort analysis builds on principles from survival analysis and time-to-event modeling, where understanding customer behavior over time drives better decisions.