结合 Google Analytics 和 Shopify：在 BigQuery 中创建电子商务数据仓库

已发表: 2023-02-15

Google Analytics 是一个很棒的工具，可以让您了解访问您网站的用户的行为。然而，Shopify 非常擅长从电子商务的角度提供详细的性能覆盖。两者都擅长自己的工作，但有一点是来自 Shopify 和 Google Analytics (GA) 的孤立数据无法提供您可能想要的更深入的见解。虽然每个人都有能力提供强大的信息，但通过组合数据可以获得更高层次的洞察力。

在这个特定的例子中，我们将介绍我们如何为 Pooch & Mutt 的朋友实现这一目标，Pooch & Mutt 是一家英国公司，生产天然健康的狗粮。我们希望通过获取 Google Analytics 中收集的数据并将其与来自 Shopify 的电子商务数据以及来自 Recharge 的订阅数据结合使用并将其与来自 Bold 的旧订阅数据结合使用来增强洞察力。使用各种数据源，我们可以构建更丰富、更完整的图景。

本博客将寻求概述我们如何聚合这些数据，然后概述将其组合以提取和可视化这些数据驱动的见解背后的思维过程。具体来说，我们将解释我们是如何着手回答以下三个问题的；

哪些渠道推动订阅注册？
GA 之外的篮子组合；最受订阅者和非订阅者欢迎的篮子是什么？哪些是最有利可图的？
用户成为订阅者之前平均需要多长时间和多少次购买？

方法

在我们开始回答这些问题之前，让我们首先介绍一下我们如何将所有数据从他们各自的平台收集到一个集中的位置，并构建一个我们可以用来整理这些数据的表格。

数据结构

我们选择使用 BigQuery (BQ) 来集中数据，因为它的多功能性和处理数据集组合的能力。下面的流程图强调了数据之间的一般联系以及实现这一点的方式：

缝

Stitch 是一种经济实惠的工具，可让您提取数据并将其加载到预建的数据仓库中。

我们通过 Stitch 轻松连接到 Shopify 和 Recharge，我们只需在其中选择我们想要的表格。在这种情况下，最重要的表是来自 Recharge 的“客户”和“订阅”，以及来自 Shopify 的“订单”。

这些信息随后通过 Stitch 的自动调度流程每 6 小时与 BQ 同步一次。

大胆的

对于历史订阅者数据，我们进行了最终导出并通过 Google 表格将其上传到 BQ。

谷歌分析

虽然 Stitch 也可用于此，但我们选择使用 RStudio 将此数据导入 BQ。如果您有兴趣了解有关如何执行此操作的更多信息，可以阅读我们自己的 R Studio 专家 Danny Smith 撰写的关于如何将 GA API 与 R 结合使用的博客。

构建主表

一旦我们在 BQ 中获得了所有必需的数据，我们就可以开始组合它们了。我们采取的步骤如下：

我们将来自 Bold 的旧订户数据与来自 Recharge 的当前订户数据相结合并对齐。
然后我们从 GA 中提取电子商务数据，其中包括订单 ID（例如 SPFYXXXXX），以及来源、媒介和渠道。
在加入 Shopify 订单数据之前，我们根据 GA 表和联合订户数据创建了新列。在这种情况下，其中一些额外的列包括在内；
1. “活动”订户标识符
2. 原始订阅日期
3. 订单的来源、媒介和渠道
4. 第一次或重复的客户订单
接下来，我们从 Shopify 订单数据中选择了我们想要加入此新数据的相关字段。一些关键的例子是；
1. 客户ID
2. 订单编号
3. 订购日期
4. 存货单位
5. 物品数量
6. 商品价格
7. 订单总价
8. 优惠码
9. 优惠金额
10. 标签（用作对订阅者与非订阅者进行分类的附加方法）
这里的最后阶段是创建更多的附加列，以更轻松地处理同一订单中不同项目的重复行；
1. 平均温度
2. 平均折扣
3. 订户状态（标签 + 如果它们出现在 Bold & Recharge 表中）

然后，我们安排此查询每天运行一次，并将结果用作其他查询的主表。这样做意味着我们不必多次运行完整查询，因为它是一个静态表，只在计划时更新，而不是每次通过 Looker Studio 仪表板发出请求时更新。

见解

现在我们已经介绍了方法并概述了为获取我们需要的所有数据所采取的步骤，我们可以继续讨论我们如何使用这些数据来回答我们的问题。

哪些渠道推动订阅注册？

为了计算这一点，我们查看了客户下的第一个订单，使用我们在主表中创建的字段，该字段根据第一个订单日期返回“First”或“Repeat”。
使用主表中的渠道分组和订阅者状态附加列，我们能够查看在首次购买或重复购买时成为订阅者的第一批订单，以及他们在 GA 中归因于哪个渠道.
删除仅查看订阅者的步骤，我们还可以看到哪些渠道主要推动首次购买。

GA 之外的篮子组合；最受订阅者和非订阅者欢迎的篮子是什么，哪些是最有利可图的？

使用 STRING_AGG 函数，我们能够将来自单个订单的不同产品组合在一行中，以 ' | 分隔'. 这实质上为每个订单创建了一个篮子。
连同这些信息，我们将这些项目中每一项的收入汇总为“购物篮总计”，目的是让每个订单都有一行包含所有相关信息。
当然，产品之间存在很多差异（口味、大小等），因此我们添加了一个列来剔除这种差异，以查看基本产品篮子组合。
然后，我们使用“ROW_NUMBER() OVER (PARTITION BY Transaction_ID) AS Identifier”为每个订单分配顺序值。然后将其用作仪表板中的过滤器，以隔离仍包含所有信息的单行。

用户成为订阅者之前平均需要多长时间和多少次购买？

使用我们的主表，我们提取了所有我们知道的订阅者的客户 ID 并获取了他们的第一个订阅日期。
然后我们获取最小订单日期并使用 DATE_DIFF 函数计算这些日期之间的“天数”差异。
最后一步引入了频道和订阅前的平均天数，这导致了一个表格显示客户通常成为订阅者之前的平均天数。
为了计算客户在成为订阅者之前所下的平均订单数，我们只需将唯一订单 ID 的数量除以唯一客户 ID 的数量，其中他们是“订阅者”并且他们的订单日期小于最短订阅日期。

这些只是我们使用这些报告生成的视图的一些表面示例。有了这些数据，就有了无限的可能性——这个项目的其他例子包括从不同的角度审视客户的生命周期价值，评估折扣代码的使用及其对客户生命周期行为的影响。有了这些见解，它们就可以用来补充业务决策。例如，知道平均需要 3 次订单才能成为订阅者，您可以寻求分享优惠和激励措施，鼓励客户在 2 次购买后成为订阅者。然后可以在以后测量其结果。

概括

本案例研究展示了通过组合多个数据源（从 GA 到 Shopify 等）可以获得非常有价值的数据驱动洞察力的潜力。一旦创建了这个基础设施，它就可以动态维护并用于报告业务的许多不同方面。

如果我们在此博客中回答的问题与您可能对自己的业务感兴趣的问题相似，您可以通过填写我们的联系表与我们取得联系。