如何在 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 表格吧！