google-bigquery - 使用 BigQuery 和 Data Studio 计算和显示客户生命周期价值直方图

Question

考虑 Google BigQuery 中的一个表，其中包含客户的购买记录。为了简单起见，让我们关注以下属性： customer_id, product_id, amount

我想根据上述数据集创建一个 Google Data Studio 报告，显示客户生命周期价值直方图。客户生命周期价值是amount任何给定客户的总和。直方图将显示有多少客户按其总数落入某个存储桶中——我将定义诸如 0-10、10-20、20-30 等值范围之类的存储桶。

像这样：

最后，我还想按product_id 过滤直方图。当过滤器处于活动状态时，直方图将显示至少一次购买给定产品的客户的总数。

截至目前，我认为这在 Datastudio 中是不可能实现的，但我希望我错了。

到目前为止我尝试过的事情：

1. 通过 Datastudio 中的计算字段显示整个数据集的平均客户生命周期价值很容易SUM(amount) / COUNT(customer_id)

2. 对于创建直方图，我在 Data Studio 中看不到任何方法（基于上述数据集）。我想我需要创建一个原始表的视图，其中包含每个客户的单行和总金额。可以在 Big Query 或 Data Studio 中使用CASE ... WHEN.

然而，对于最后一步，即创建一个产品过滤器，为购买给定产品的客户过滤直方图，我不知道如何处理这个问题。

有什么想法吗？

标签： google-bigquerygoogle-data-studio

Answer 1

我能够进行与您描述的类似的复制，但这并不简单，因此我将尝试详细说明所有内容。主要思想是从同一个表中获得两个数据源：一个包含customer_id并且product_id我们可以过滤它，而另一个包含customer_id和已经计算的amount_bucket字段。这样我们就可以加入它（混合数据）customer_id并根据product_id它进行过滤，不会改变amount_bucket计算。

我使用以下脚本在 BigQuery 中创建了一些数据：

CREATE OR REPLACE TABLE data_studio.histogram
(
  customer_id STRING,
  product_id STRING,
  amount INT64
);

INSERT INTO data_studio.histogram (customer_id, product_id, amount)
VALUES ('John', 'Game', 60),
       ('John', 'TV', 800),
       ('John', 'Console', 300),
       ('Paul', 'Sofa', 1200),
       ('George', 'TV', 750),
       ('Ringo', 'Movie', 20),
       ('Ringo', 'Console', 250)
;

然后我直接连接到 BigQuery 表并获取以下字段。数据源称为histogram：

我们BigQuery使用自定义查询添加第二个数据源 ( )：

SELECT
  customer_id,
  CASE
    WHEN SUM(amount) < 500 THEN '0-500'
    WHEN SUM(amount) < 1000 THEN '500-1000'
    WHEN SUM(amount) < 1500 THEN '1000-1500'
    ELSE '1500+'
END
  AS amount_bucket
FROM
  data_studio.histogram
GROUP BY
  customer_id

只有后者，我们已经可以使用以下配置制作基本直方图：

维度是amount_bucket，度量是Record count。我创建了一个bucket_order自定义字段来对其进行排序，因为字典上的“1000-1500”在“500-1000”之前：

CASE 
  WHEN amount_bucket = '0-500' THEN 0
  WHEN amount_bucket = '500-1000' THEN 1
  WHEN amount_bucket = '1000-1500' THEN 2
  ELSE 3
END

现在我们product_id在顶部添加过滤器和一个具有以下配置的新图表：

请注意，metric 是 CTD（Count Distinct），customer_id数据Blended data源实现为：

TV我仅按 so过滤George并John出现但其他产品仍计入总金额计算的示例：

我希望这个对你有用。