如何在Google Data Studio中复现Google Sheets中的临时同期群分析（注册月份vs购买月份）

Got it, let's walk through how to replicate your ad-hoc cohort analysis in Google Data Studio step by step. I've dealt with similar setups before, so here's a practical breakdown:

Since you're working with three separate data sources, you need to tie them together logically. Start by creating a consolidated view in your Google Cloud PostgreSQL instance—this will make Data Studio's work way easier than trying to mix all sources directly in DS.

Here's a SQL script to create a cohort-ready view that combines users and orders:

WITH user_cohorts AS (
  SELECT
    u."User ID",
    -- Format registration date into YYYY-MM cohort group
    TO_CHAR(u."User reg date", 'YYYY-MM') AS cohort_month,
    DATE_TRUNC('month', u."User reg date") AS cohort_start_date,
    -- Count total users per cohort (for later marketing spend calculations)
    COUNT(*) OVER (PARTITION BY TO_CHAR(u."User reg date", 'YYYY-MM')) AS cohort_total_users
  FROM users u
),
order_cohorts AS (
  SELECT
    o."User ID",
    o."Money earned",
    -- Calculate months between registration and order date
    EXTRACT(YEAR FROM o."Payment date") * 12 + EXTRACT(MONTH FROM o."Payment date") 
    - (EXTRACT(YEAR FROM u."User reg date") * 12 + EXTRACT(MONTH FROM u."User reg date")) AS month_offset,
    TO_CHAR(o."Payment date", 'YYYY-MM') AS order_month
  FROM orders o
  JOIN users u ON o."User ID" = u."User ID"
)
SELECT
  uc.cohort_month,
  oc.month_offset,
  SUM(oc."Money earned") AS total_cohort_revenue,
  COUNT(DISTINCT oc."User ID") AS active_users_in_month,
  uc.cohort_total_users,
  uc.cohort_start_date
FROM user_cohorts uc
LEFT JOIN order_cohorts oc ON uc."User ID" = oc."User ID"
GROUP BY uc.cohort_month, oc.month_offset, uc.cohort_total_users, uc.cohort_start_date
ORDER BY uc.cohort_month, oc.month_offset;

This view gives you core cohort metrics: which month users registered, how many months later they placed orders, total revenue per cohort-month, and total users in each cohort.

Now, bring in your Google Sheets marketing spend table into Data Studio, then use Data Blending to link it with your PostgreSQL cohort view:

• Set your PostgreSQL cohort view as the Primary Data Source
• Add the Google Sheets marketing spend table as a Secondary Data Source
• Define the join condition: cohort_start_date (from primary) = Date (from secondary, since your spend table uses the first day of the month)
• Make sure both date fields are set to Date type in Data Studio (not text) to avoid mismatches.

Add these calculated fields to your blended data to get actionable metrics:

1. Cohort Conversion Rate:

   active_users_in_month / cohort_total_users
   

   (Format as percentage to see what % of the cohort was active each month)
2. Cohort ROI:

   IFNULL(total_cohort_revenue / "Spend for a month", 0)
   

   (Handles cases where there's no revenue for a cohort-month to avoid errors)
3. Average Revenue Per Active User (ARPA):

   total_cohort_revenue / active_users_in_month
   

The best way to display cohort analysis in Data Studio is with a Heatmap or a Pivot Table:

• For a Heatmap:
  • Rows: cohort_month (your registration cohorts)
  • Columns: month_offset (0 = registration month, 1 = first month post-reg, etc.)
  • Values: Pick your metric (e.g., total_cohort_revenue or Cohort Conversion Rate)
  • Use conditional formatting to highlight high/low values (makes trends easier to spot)
• For a Pivot Table:
  • Same row/column setup as the heatmap, but adds exact numeric values for deeper analysis.

Double-check these to ensure accuracy:

• Verify that month_offset is calculating correctly: A user who registered in January 2023 and ordered in January should have month_offset = 0, February orders = 1, etc.
• Confirm marketing spend is tied to the correct cohort: The spend for January 2023 should only link to the January 2023 cohort.
• Check for nulls: Use IFNULL() in calculated fields to replace empty values with 0 where needed.

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