如何在 Google 表格中構建多合一的數字儀表板
已發表: 2020-12-14很難想像沒有電子表格的互聯網營銷人員的工作。 以前，主要工具是 MS Office 的 Excel，但現在越來越多的專家轉向 Google Sheets。 考慮到以下優點，這並沒有什麼奇怪的：
- 基本的 Google 功能是免費的。
- 方便的合作——無需一直互相發送各種文件。
- 所有更改都會自動保存。
- 歷史訪問 - 您可以回滾到一切都出錯的那一刻。
- 自動第三方數據導入——分析和廣告服務、跟踪呼叫的工具等。
Coupler.io 的 Google 表格集成是一種多功能且實用的工具，具有許多獨特的功能和腳本可供使用。 僅使用公式，您就可以寫一本書——而不會將您的內容變成可怕的長篇閱讀。 更不用說使用腳本、深入了解細節以及為每個功能做大量說明等高級功能了。
對於在線營銷人員來說，製作報告是一項例行任務。 但是，如果沒有它們，將很難組織工作、了解其結果並將其展示給客戶和管理層。 Google 表格可以幫助您以最方便的方式創建和存儲報告。 在這篇文章中，我們將了解服務參數並學習如何創建一個終極的多合一儀表板。
從事電子商務意味著始終了解正在發生的事情。 如果您密切關注您的銷售和轉化率，您總能發現您業務的弱點並了解投資方向。 這就是為什麼您需要結構化數據來幫助您處理上述業務方面以及更多問題的原因。 畢竟，決策的質量和速度直接取決於信息的提供方式以及我們大腦處理信息的速度。 這就是數字儀表板在當今在線業務中如此受歡迎的原因——它們以完美的方式可視化關鍵數據，讓您以最小的風險控制您的業務。
今天，我們將向您展示如何在 Google 表格中為您的企業構建一個多合一的數字儀表板。
電子商務指標
首先，您需要決定將哪些指標發送到未來的數字儀表板。
請記住，每個指標都很重要，但是，您不應使儀表板過載。 確定最重要的指標來跟踪電子商務業務模型的績效。 也許你有一家服務店或一家標準的網上商店——這並不重要。 重要的是我們為您提供的以下 7 個電子商務指標，供您選擇以跟踪您的業務。 當然，您也可以添加您的選項。
在本文中，我們選擇了舊金山灣區一家在線銷售三明治的小型電子商務零售商的數據。 這就是我們的儀表板將基於的內容。
這些是我們選擇的營銷指標：
- 地區銷售分佈（順豐灣區縣）
- 總訂單價值和轉化率
- 預期收入和總收入
- 平均訂單價值
- 平均訂單生命週期
- 最佳銷售和收入表現
數字儀表板初始數據
儀表板上的每個指標都需要特定數據，如下所示：
1. 銷售區域分佈。 要查看SF灣區各個縣的銷售額和收入，需要以下數據：
- 每個銷售信息；
- 每個客戶的信息。
2. 總訂單價值和轉化率。 這是所有已下訂單的總和，包括丟失的訂單。 銷售轉化率是銷售/數量合格的潛在客戶比率。 要計算此指標，您將需要有關每個訂單和每次銷售的信息。
3. 預期收入和總收入。 總收入=所有銷售額的總和。 並且預期收入是所有銷售+未結訂單。 要計算此指標，您將需要有關每個單獨訂單的信息。
4. 平均訂單價值。 總收入與數量或訂單的比率。
5. 平均訂單生命週期。 您將需要有關每次銷售的信息，以確定進行銷售需要多長時間。
6. 最佳銷售和收入表現。 該指標要求按銷售額和收入對銷量最高的 5 種產品（在我們的例子中是三明治）進行分類。 同樣，在這裡您將需要有關每個銷售和產品的信息。
初始數據源
如果您的在線商店或服務中心在 3dcart 等電子商務平台上運行，則您的大部分數據都將在其上。 否則，例如，如果您在 Instagram 上銷售或擁有一個包含投資組合的網站，您可以選擇將您的業務數據存儲在您想要的任何位置。 您可以將 Airtable 用作有關產品、客戶、銷售、銀行帳戶等信息的數據庫。您的基本營銷工具集還可能包括其他功能和服務，如下所示：
- CRM 應用程序（例如 HubSpot 或 Pipedrive），方便客戶/銷售管理；
- 用於網站流量和客戶行為分析等的谷歌分析；
- 電子郵件營銷服務（例如 Mailchimp 或 Sender）以與您的客戶保持聯繫。
我們選擇 Pipedrive CRM 進行銷售管道管理，選擇 Airtable 進行產品/客戶信息存儲。 因此，這些是我們的數據來源。
將數據導入 Google 表格
您可以手動（以支持的文件格式從您的數據源導出數據集，然後將其導入 Google 表格）或自動（使用特殊工具（例如 Coupler.ia 或 Automate.io）將您的數據源連接到 Google Sheets 自動同步您的數據。我們為我們的案例選擇了自動版本和 Coupler.io，因為我們正在構建一個實時數字儀表板。我們選擇的其他原因如下：
Coupler.io 從各種來源（如 Airtable、Pipedrive、HubSpot 等）提取數據，並按計劃（每小時、3 小時、每天等）自動導入數據。
您需要選擇數據類別（在我們的案例中為 Deals）並將電子表格連接到 Pipedrive。 這是將 Pipedrive 數據導入 Google Sheets。
現在，要從 Airtable 導入數據，需要 Airtable 數據源的共享視圖鏈接。 您將需要此作為最佳表現產品指標。 一旦電子表格有了原始數據，我們就可以開始構建我們的儀表板。
如何在 Google 表格中構建終極電子商務儀表板
我們將向您展示我們用於計算下面每個指標的公式。 為方便起見，我們還將每個部分錶示在單獨的表格中。
各地區銷售額
順豐縣專欄
我們將以下公式應用於 A2 單元格：
=unique('Airtable Data'！$B$2:$B)
這意味著每個銷售區域名稱的範圍。 “唯一”函數將返回此範圍內的所有唯一值。
銷售欄
必須將以下公式應用於 B2 單元格。 將其向下拖動到範圍末端：
=countif('Airtable Data'!$B$2:$B,A2)
“countif”函數將計算每個縣的銷售額。
收入欄
將以下公式應用於 C2 單元格並將其向下拖動到範圍末端：
=sumif('Airtable Data'!$B$2:$B,A2,'Airtable Data'!$I$2:$I)
這是每次銷售金額的範圍。 “sumif”函數將對每個縣的收入進行匯總。
插入氣泡圖
選擇範圍 A1:C10 並轉到 Insert=> Chart。 選擇氣泡圖類型。
銷售轉化率
應用以下公式：
=COUNTIF('Pipedrive Deals'！$AP$2:$AP,"won")/
COUNTA('Pipedrive Deals'！$AP$2:$AP)
第一個公式是訂單狀態的範圍：打開、贏得和丟失。 “countif”函數將統計所有狀態為“won”的訂單。 “counta”函數將計算所有訂單。 銷售轉化率是第一個公式除以第二個公式的結果。 選擇具有值的單元格並插入儀表圖類型。
總訂單和收入
總訂單
您將需要應用下一個公式：
=COUNTA('Pipedrive Deals'！$AP$2:$AP)
公式是訂單狀態的範圍（打開、贏得、丟失）。 “counta”函數將計算所有訂單。
總收入
應用以下公式：
=總和（
Filter('Pipedrive Deals'！$AI$2:$AI,'Pipedrive Deals'！$AP$2:$AP=”won”))
這是每個訂單值的範圍。 “過濾器”功能將按“贏得”狀態過濾訂單。 “sum”功能將匯總贏得的訂單以計算總收入。
預期收入
您將需要應用以下公式：
=總和（
Filter('Pipedrive Deals'!$AI$2:$AI,'Pipedrive Deals'!$AP$2:$AP=”won”),
Filter('Pipedrive Deals'！$AI$2:$AI,'Pipedrive Deals'！$AP$2:$AP=”open”))
在這裡，“過濾器”功能將通過兩種狀態過濾掉訂單：“贏得”和“打開”。 “sum”函數將贏得訂單和未結訂單相加以計算預期收入。
記分卡圖表
分別為每個指標插入一個記分卡圖表。
平均訂單生命週期和平均訂單價值
平均訂單價值
您將需要應用以下公式：
=總和（
Filter('Pipedrive Deals'！$AI$2:$AI,'Pipedrive Deals'！$AP$2:$AP=”won”))/
COUNTA('Pipedrive Deals'！$AP$2:$AP
上面解釋了總收入和訂單。
平均訂單生命週期
這是您需要了解每次銷售花費的天數的地方。 為此，請轉到 Pipedrive 交易表，在該表的開頭創建 1 列，並將以下公式應用於 A1 單元格：
={“每個訂單的天數”；ARRAYFORMULA（IF（ISBLANK（AY2：AY），“”，
減號(AY2:AY,AK2:AK)))}
“減號”函數將顯示訂單創建日期 (AK2:AK) 和訂單關閉日期 (AY2:AY) 之間的差異。
然後您需要返回儀表板並應用下一個公式來計算平均訂單生命週期：
=IFERROR(AVERAGE('Pipedrive Deals'！$A$2:$A))
這是新創建的範圍，每個訂單的天數。 “平均值”函數將取回指定範圍的平均值。
記分卡圖表
分別為每個指標插入記分卡圖表。
細分或訂單
您將需要以下公式按狀態細分訂單：
未結訂單：
=COUNTIF('Pipedrive Deals'！$AP$2:$AP,"open")
丟失的訂單：
=COUNTIF('Pipedrive Deals'！$AP$2:$AP，“丟失”）
贏得訂單（銷售）：
=COUNTIF('Pipedrive Deals'！$AP$2:$AP,"won")
Pipedrive Deals'!$AP$2:$AP 是訂單狀態的範圍（打開、贏得、丟失）。 “countif”函數將返回按所選狀態（“open”、“lost”或“won”）排序的訂單數量。
按狀態選擇所有訂單的值並創建 3D 餅圖。
最佳產品
您需要過濾掉所有產品，計算每個產品的銷售額 + 收入。 以下“唯一”公式將從 Airtable 導出的產品列 ('Airtable Data'!$E$2:$E) 中“提取”所有產品：
=unique('Airtable Data'！$E$2:$E)
現在，我們應該使用下一個“sumif”公式計算每種產品的銷售額：
=sumif('Airtable Data'!$E$2:$E,A2,'Airtable Data'!$H$2:$H)
將其向下拖動到範圍的末尾。 然後，用“sumif”公式計算收入：
=sumif('Airtable Data'!$E$2:$E,A2,'Airtable Data'!$I$2:$I)
現在您應該有一個包含 3 列的表：產品 (A1:A11)、銷售額 (B1:B11) 和收入 (C1:C11)。 要獲得性能最佳的產品，請使用“SORTN”功能。 查看銷售額排名前 5 的產品的公式。
=SORTN(A2:B11,5,1,B2:B11,false)
以下是收入排名前 5 的產品的公式：
=SORTN({A2:A11,C2:C11},5,1,C2:C11,false)
選擇結果表格並為每個表格單獨插入表格圖表。
結論
這篇文章的主要目的是展示谷歌表格的力量，以最好的方式展示它的功能和特性。 我們的儀表板是基於 Pipedrive 和 Airtable 的數據構建的，但您可以自由地將這些知識應用到您的電子商務項目或案例研究中。 許多各種工具和插件允許您將電子表格與幾乎任何數據源同步。 這允許您向儀表板添加通用指標，並將更多所需數據保存在一個位置。 所以不要浪費您的時間，放心地使用 Google 表格吧！