如何在Google Sheets中分析免费试课转付费客户的转化率及转化时长

Hey there! Love that you're focusing on getting more kids into sports—such a great mission. Let's break down how to pull those two key metrics from your WooCommerce data in Google Sheets.

First, let's assume your sheet has these core columns (adjust references if yours are different):

• Column A: Unique User ID (to link trial and paid orders for the same person)
• Column B: Order Type (e.g., "Free Trial Class" or "Paid Enrollment")
• Column C: Order Timestamp (when the trial/paid order was completed)

1. Count of Free Trial Users Who Converted to Paid Customers #

This formula will count unique users who signed up for a free trial and later purchased a paid course:

=COUNTUNIQUE(
  FILTER(
    A:A,
    ISNUMBER(MATCH(A:A, FILTER(A:A, B:B="Paid Enrollment"), 0)),
    B:B="Free Trial Class"
  )
)

How it works: #

• FILTER(A:A, B:B="Paid Enrollment") grabs all User IDs that have a paid order
• MATCH(A:A, ..., 0) checks which trial users appear in that paid ID list
• FILTER(A:A, ...) narrows down to only trial users who converted
• COUNTUNIQUE ensures we count each converted user only once (even if they had multiple trials/paid orders)

2. Average Time Between Free Trial and First Paid Purchase #

To calculate the average number of days (adjust the unit if needed) from a user's first trial to their first paid order:

=AVERAGE(
  FILTER(
    ARRAYFORMULA(
      DATEDIF(
        VLOOKUP(A:A, SORT(FILTER(A:C, B:B="Free Trial Class"), 3, TRUE), 3, FALSE),
        VLOOKUP(A:A, SORT(FILTER(A:C, B:B="Paid Enrollment"), 3, TRUE), 3, FALSE),
        "D"
      )
    ),
    ISNUMBER(VLOOKUP(A:A, FILTER(A:C, B:B="Paid Enrollment"), 3, FALSE))
  )
)

How it works: #

• SORT(FILTER(A:C, B:B="Free Trial Class"), 3, TRUE) sorts trial orders by timestamp to get the first trial date for each user
• VLOOKUP(A:A, ..., 3, FALSE) pulls that earliest trial date for each user
• Same logic applies to fetch the first paid order date
• DATEDIF(..., ..., "D") calculates days between the two dates (use "H" for hours, "M" for months if needed)
• FILTER removes users who didn't convert (so we only include valid trial-to-paid pairs)
• AVERAGE gives the mean time to convert across all successful users

Quick Tips: #

• Double-check that your timestamp column (Column C) is formatted as Date/Time in Google Sheets (go to Format > Number > Date time)
• If your order type labels are different (e.g., "Free Class" instead of "Free Trial Class"), update the text in the formulas to match your exact values
• For larger datasets, add a helper column to flag converted users first—it makes troubleshooting way easier

内容的提问来源于stack exchange，提问作者Leendert