結合 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 等）可以獲得非常有價值的數據驅動洞察力的潛力。一旦創建了這個基礎設施，它就可以動態維護並用於報告業務的許多不同方面。

如果我們在此博客中回答的問題與您可能對自己的業務感興趣的問題相似，您可以通過填寫我們的聯繫表與我們取得聯繫。