Return to site

在Google表格中构建可视化报告

· seo优化

如果您还不知道,Google表格有一个出色的Google Analytics插件,可用于在Google表格中创建自定义报告。有关此工具的报告功能的更多信息,请阅读 2016年的博客文章,该文章还将教您如何下载加载项并设置自定义报告配置。作为概述,此附加组件允许您:

将您有权访问的Google Analytics(GA)帐户中的任何数据直接快速提取到电子表格中
轻松比较自定义时间段内的历史数据
直接在Google表格中过滤和细分您的数据
自动定期报告
轻松调整现有报告(将保存到Google云端硬盘)以获取新数据
除了如何使用该工具 - 我们有免费的东西!
您可以使用此工具获取的所有大量数据都很有用,但如果您希望能够快速比较自定义报告配置中的数据,该怎么办?通过比较有机会话和订单的数量(并且接近 - ,通过报告视图直观地显示您的网站每周(WoW)或同比(YoY)的表现,这不是一件好事吗自动化)?

我也是这么想的。

最近,我使用Google表格中的Google Analytics插件制作了一份报告。我已创建此报告的模板版本以与您分享。随意复制它并随意使用它。

以下是此报告为您提供的一些见解:

整个网站的有机会话和订单WoW和YoY
针对不同页面类型的有机会话和订单WoW和YoY,包括类别,内容,产品和季节性页面
有机会议和主页的WoW和YoY订单以及静态前20页
您的移动网站的有机会话和订单WoW和YoY
使用公式,一些正则表达式和条件格式,他们的每周SEO报告流程现在几乎使用他们的Google Analytics数据自动化。

等等,我不能在GA界面中完成所有这些吗?
不完全的。以下是此附加组件在标准GA界面上的一些优点:

在加载项中,您可以过滤报表中尚未包含的维度或指标。在GA界面中,如果您查看的是将目标网页作为唯一维度的报告,则无法使用过滤器过滤为有机流量。使用附加组件,您可以
您可以使用段而不是过滤器在GA中执行此操作,但是段比过滤器更容易导致采样问题
使用加载项加载数据后,您可以在不必持续导出文件的情况下对其进行操作
您可以与附加组件进行多次比较(我在本报告中进行了这种比较),而在GA中,您只能做两次,即年复一年或一周比较,而不是同时进行两次比较
使用附加组件可以为您提供单一的事实来源,而不是拥有GA提供的所有其他数据,您可能不希望潜入
在Google表格中使用条件格式表示我能够按颜色标记不同程度的正面或负面更改
如何使此报告成为您自己的报告
这篇博文将向您介绍报告有用的原因,如何自定义报告,然后如果您感到好奇,我还会在下面的附录中详细介绍报告的工作原理。这对于您可能需要做的任何潜在的调试也很有用。

通过以下说明,即使您是初学者,例如正则表达式和Excel / Google表格公式,您仍然可以自定义报告并自行使用。

为了构建报告,我在报告配置中使用了正则表达式来过滤到我想要的特定页面类型,然后在“比较”表中,我使用公式从表中提取数据然后获取WoW和同比比较。所有这些都发生在唯一的Google表格中。

对于一次性报告,这可能不值得投入时间,但如果您或客户需要标准化的持续报告 - 并且您可以访问该帐户的GA数据 - 那么此模板可以是很好的方式让您快速,轻松地了解您的有机交通趋势。

它节省了我一周一小时的时间 - 或一年近6个工作日。

您需要自定义此报告的内容
要将此报告作为您自己的报告,您需要:

要下载Google表格的Google Analytics插件(说明在此处)
访问您要报告的GA帐户
您的GA查看ID(有关如何在此处查找此内容的说明)
您可能会发现有用的其他资源:

GA的查询资源管理器 - 可用于测试度量和维度过滤器的不同组合的输出
GA Reporting API - 列出并描述通过Core Reporting API提供的所有维度和指标
为什么这份报告很有用
此报告使用条件格式来突出显示任何重要的正面或负面更改。它还使用了网站趋势的宏观视图和更详细的视图。在顶部,它包含整个网站的总会话和订单,以及WoW和YoY比较,当您进一步向下报告时,它变得更加细化。

我构建了这份报告,以便更好地了解网站的具体部分是如何执行的。我报告的主要页面是类别页面,内容页面,产品页面和移动页面。接下来,我已经在数据中列出了前20页的列表,包括主页。最后,有一个季节性页面部分。

报告的前20页是静态的。这些是通过查看始终具有最高有机会话的页面预先确定的。我们选择使用静态前20名而不是实际前20名每周会话,因为使用实际需要在每次运行报表时更新SUMIF公式。

这是我每周更新的报告。日期公式是根据今天的日期计算的,然后在报告配置中使用。

这允许日期自己更新,而不必每次我想要运行报告时手动更改日期。我只需要安排报告每周运行和BAM! - 没有必要的手。

要为报告设置自动运行,请转到加载项> Google Analytics>计划报告,选中“启用报告以自动运行”,然后设置您希望报告运行的时间和频率。

这让我的生活变得更轻松,希望分享它也会让你的生活更轻松。

如何自定义此报告
在此报告中,您将不得不自定义:

您的报告配置
您希望它运行的日期
您要比较的主要页面类型(我们有类别页面,内容页面,产品页面和移动设备)
您要报告的前20页(您可能选择不使用此页面)
您的季节性页面(如果适用)
必要的更改 - 报告配置

一级定制
要了解如何设置和运行报告配置,我在开头引用的博客文章可以为您提供帮助。出于本文的目的,我将专注于您需要为您的网站或客户调整它的位置。

您需要输入要报告的视图ID(第3行),并且您必须编辑过滤器部分(第9行)中的正则表达式,以使它们对您的客户端是唯一的,我将在下面介绍。

我正在使用的日期(第4-5行)是我在比较选项卡中的引用。如果您想使用不同的日期,可以在此处手动更改它们,或者在下一节中,我将解释日期公式的工作原理。这是单元格B4中公式的屏幕截图,以便您可以看到我在说什么:

您可能还想要更改每列的报告名称(第2行)。如果您这样做,请确保清楚地标记每个部分。报告名称将成为运行报告后生成的工作表的名称,以及稍后在“比较”选项卡中的公式中使用的名称。

请注意,更改“报告名称”时,它不会替换旧名称,而只会创建一个新名称。您必须手动删除旧的不需要的工作表。

更新正则表达式
正则表达式的两个部分对于您必须更新的网站是唯一的。

您必须更新的第一部分是我必须过滤出由GA错误地报告为有机的PPC数据。对于此网站,可以使用包含“gclid”,“cm_mmc”,“newsl”或“google”的任何网址识别PPC数据 - 这可能会对您有所不同,因此请更改报价中的内容以反映这个。

由于用于PPC数据的标记最初用于不同的报告平台,因此报告为有机,因此对您来说可能不是问题。如果是这样,您可以删除此部分。

如果这对您来说不是问题,那么您可以继续删除过滤器部分的这一部分(报告配置选项卡的单元格B9-D9中的ga:medium == organic之后的所有内容)。

您必须对正则表达式进行的第二次更新是针对除“Everything”之外的所有列的Filter部分中使用的更新(单元格E9-P9)。这些用于标识要筛选的URL路径部分。

每个过滤器都用分号分隔,因此如果要向这些过滤器添加任何内容,请务必在其中添加。分号在Core Reporting API中表示“AND”。对于逗号,您使用“OR”。

这是“一切”部分:

ga:medium==organic;ga:landingPagePath!@gclid;ga:landingPagePath!@cm_mmc;ga:landingPagePath!@newsl;ga:landingPagePath!@google

除了ga:medium == organic(仅过滤到有机会话),这只是过滤掉PPC数据。

我已经在我的所有部分中复制了这个表达式,但是对于特定页面类型的部分,我还包含了另一个正则表达式来获取我要查找的特定URL,如下所示。对于这些部分,您会看到以下变体:

ga:medium==organic;ga:landingPagePath=~\/category\/;ga:landingPagePath!@gclid;ga:landingPagePath!@cm_mmc;ga:landingPagePath!@newsl;ga:landingPagePath!@google

对于此示例,它正在过滤包含“/ category /”的URL。此过滤器仅向客户的类别页面报告。同样,您可以将此正则表达式自定义到您的唯一网站或客户端。请务必使用反斜杠转义本节中使用的任何斜杠。

移动部分(单元格N9-P9)略有不同,因为这是GA中定义的维度。在有机过滤器之后,您会在“ga:deviceCategory == mobile”中添加的那些列中看到。

完成所有操作后,您可以运行报告并继续自定义“比较”选项卡。

必要的更改 - 比较选项卡
日期公式

单元格M13:S18中的日期公式进一步自动化报告。该报告定义了一周作为星期一到星期日,因为这是我们的客户定义他们的方式,所以如果这对你不同,你将不得不改变它。如果您对这些特定公式的工作原理感到好奇,我已在附录中详细介绍了它。

如果您确实更改了此部分,请确保将日期格式设置为YYYY-MM-DD。要执行此操作,请转到格式>数字>更多格式>更多日期和时间格式。

我还留出空间来输入自定义的开始和结束日期。这个特定的客户是为了能够比较奇数周与特定假期的同比比较。仅当单元格N16-S16不为空白时才使用这些日期。

Google表格公式 - 适用于主页,前20页和季节性页面
一旦您对日期感到满意,您需要更新的主要内容是公式,特别是所引用工作表的名称以及定义要报告的页面的条件。

如果您在自定义公式时遇到错误,尤其是#N / A!错误,首先尝试重新运行比较表中的单元格,只需突出显示并按Enter键。

对于单元格B6:K10顶部的主页面,如果您更改了上一部分的报告名称,则只需更新要引用的工作表名称。您还必须为以下部分执行此操作。

当你这样做时,一定不要混淆上周和前一年。

这可能是一个漫长而恼人的过程。我发现有助于提高速度的另一件事是另一个Google表格附加高级查找和替换。这使您可以在公式中使用查找和替换功能,这意味着您可以简单地找到“当前所有星期 - 英国”并将其替换为您拥有的任何替代方案。

这个插件有一个免费试用版,一旦启动你只能每天使用一次 - 所以在你拥有它时充分利用它!如果您知道任何其他免费替代品,我很乐意听到他们。

前20页中的公式,单元格B13:K24,具有略微不同的公式,具体取决于页面类型。

我在下面的公式中突出显示的是此公式的一部分,您必须更改以匹配您的特定页面类型。这是来自单元格B14:

= SUMIF('当前所有周 - 英国'!$ A:$ A,“*”&“ /首页2 / ”,'当前所有周 - 英国'!$ B:$ B)

您看到的数字是所有页面的总和/页面顶部2 /在我的当前周的结果细分 - 英国标签中的URL,如下所示。

对于B34:K35单元格中的季节性部分,您只需要替换“圣诞节”或“黑色星期五”,以包含您要报告的特定季节性术语。请记住,这必须是包含在URL中的引用。

您可以进行的其他更改 - 报告配置
对于指标,我使用了会话和交易,但如果您希望报告的指标不同,则可以对其进行调整。请务必更改比较标签中的标题,以便记住您的报告内容。

对于尺寸,我使用了着陆页。同样,如果您希望(例如)报告关键字,则可以对此进行调整。

我将订单设置为降序而不是升序。这可以组织数据,但也有助于确定前20页。

我已将这些限制设置为1,000。我这样做是因为我只关心顶页的具体数据。限制不会更改报告的总数,它只会限制行数。

不幸的是,这也是我不得不谈论采样的地方。在单元格A6和B6的报告选项卡中,它显示“包含采样数据,否”。如果您的数据是高度抽样的,那么您需要决定这是否会成为您的障碍。

这是一种资源,有一些方法可以绕过采样数据。

这是报告时间
如果您已进行上述更改,则在使用报告配置更新生成报告后,您应该有一份Google Sheet报告您的特定数据。

这是很多信息,所以如果您对此过程的特定部分有任何疑问或需要任何帮助,请在下面评论!

正如所承诺的那样,我在下面这篇文章中添加了一个附录,供那些想要更详细地了解它是如何工作的。

快乐的报道!

附录:如果您感到好奇,本报告的工作原理
主要报告公式
最高报告部分的总计,WoW和YoY
顶部的B列和G列只是简单地提取每个工作表的总会话和总订单的报告数量。这很有用,不仅因为它将所有绝对数字放在一个地方,而且因为我现在可以在公式中引用这些单元格。

对于WoW relative(C列),我再次引用了相同的单元格,但创建了一个(当前 - 上一个)/上一个公式的百分比。

D列使用C列中生成的百分比来提取绝对数字差异。

对于YoY亲属(E栏),我遵循了相同的方法,只是引用了前一年而不是前一周的数据。我再次使用这些数字来提取F列中的绝对数字。

灰色订单部分执行完全相同的操作,而是使用订单总计而不是会话引用每个相应配置中的单元格。

我还将这些公式包装在IFERROR中,以防止工作表出现任何错误消息。这主要是为了美学,虽然值得注意的是,有时这可以说它有0%的变化,也许有一个100%的增加,因为上一年不存在该页面类型。

日期公式
我们的客户希望每周报告比较周一至周日的周数,因为这是我们的客户定义他们的方式。由于GA周从周日到周六运行,因此必须进行定制。

这些日期是根据单元格M14中的“= TODAY()”日期以及在M16中计算的去年的第一天,在M18中的去年的第一个星期一以及单元格O12和Q12中的周数来计算的。

由于这些日期是在此自动计算的,因此在“报告配置”选项卡中,我可以简单地从“比较”表中引用特定单元格,而不是每次运行报表时都手动输入日期。这也使得我可以将此报告设置为在我进入办公室之前每周一早上自动运行。

您还会注意到,在我留下空间以输入自定义开始日期和结束日期之后,这又是因为建立的特定客户有时想比较他们的同比比较的奇数周和特定假期的帐户。

在报告配置表中,我在单元格中有一个IF公式,如果自定义单元格为空,则使用通常的日期,如果不是,则使用它们。在那些场合,它确实意味着我必须手动运行报告,但我想你不能拥有一切。

前20页的报道
前20页的部分是公式有点野兽的地方,但这是客户特别要求的。我们最初希望它报告每周的前20页,但是使用公式是不可能的,因为我们需要静态参考。

对于这些,我使用了SUMIF公式。例如,在单元格C13中,我使用此公式来报告主页的WoW相对编号:

=IFERROR((SUMIF('Everything current week - UK'!A:A,"*"&".co.uk/",'Everything current week - UK'!B:B)-SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B))/SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B),0)

同样,围绕我的公式的IFERROR语句只是为了清理,所以让我们放弃它并分解这个公式的其余部分。

=(SUMIF('Everything current week - UK'!A:A,"*"&".co.uk/",'Everything current week - UK'!B:B)-SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B))/SUMIF('Everything previous week - UK'!A:A,"*"&".co.uk/",'Everything previous week - UK'!B:B)

SUMIF公式在满足特定条件的情况下对单元格进行求和。它的工作原理是定义范围,在本例中为“Everything current week - UK ”!A:A (表格A当前周的每一行 - 英国),然后是您想要求和的标准。这里,所有包含任何内容的单元格都以“.co.uk /”结尾。

最后,定义总和范围,如果它与定义的原始范围不同,则该范围是要求和的范围。我们在这里使用过这个,因为我们想要所有会话的总和,而不是着陆页路径。 整个事情吐出了本周主页上所有会话的总和。然后,我从该数字中减去前一周所有会话的总和。

最后,我将它除以前一周所有会话的总和以获得百分比变化。我在这些单元格中设置格式规则以将数字格式化为百分比,但您也可以添加该公式以乘以因此,在这些单元格中,您需要自定义两件事(1)要引用的工作表的名称,以及(2)定义要报告的页面的条件。您会注意到,在前20页中,根据页面类型的不同,它们会有所不同(它们是故意更改的,以便自行决定)。

All Posts
×

Almost done…

We just sent you an email. Please click the link in the email to confirm your subscription!

OK