回到主页

如何使用BigQuery进行大规模的SEO(或者每当Excel失败时)

这篇文章对你有什么帮助

  1. 处理大量数据。
    • 例如内部链接审核,反向链接审核,日志文件分析
  2. 快速执行复杂的数据查询。
  3. 通过强制定义您正在寻找的内容来改进您处理问题的方式,而不是漫无目的地通过数据漫游。
当主力失败时我们该怎么办?

当Excel失败时你会怎么做?也许你有超过一百万行的数据。也许您已尝试使用VLOOKUP过滤大型数据集,Excel已决定放下并死亡。Excel是一个很棒的工具,但这并不意味着它应该用于所有事情。

你应该用什么?

传统的答案是SQL,但是如果你还没有设置SQL服务器并且你不是技术人员则是一个挑战。用于处理它的免费界面通常非常繁琐,对于那些希望快速入门的人来说并不直观。

输入BigQuery。

什么是BigQuery?

谷歌的模糊描述为:

  1. “用于大规模数据分析的快速,经济且完全托管的数据仓库。”

如果我们更具体一点,我们得到:

  1. 它是一个SQL(esque)数据库,您可以将数据放入并运行查询。无论数据大小如何,它都能正常工作。
为什么使用BigQuery进行数据分析?

这有两个部分。第一个是:为什么要使用SQL来分析大型数据集?

  1. 它专为复杂的混乱查询而设计,Excel很糟糕/很慢。
  2. 许多组织将其业务数据存储在SQL数据库中。能够在SQL中工作是一项宝贵的职业技能。
  3. 虽然你需要学习一些语法,但这是非常合乎逻辑的。实际上编写查询本身就具有直观的逻辑意义。
  4. 如果您标准化用于标记数据的名称(例如,始终调用页面访问page_visits),那么您可以在多个数据集中重复使用相同的复杂查询,从而允许您创建一种方法来回答复杂的问题并重新轻松使用它。
  5. 就个人而言,我发现工作非常令人满意; 你不能只是闲着或采取措施朝着一个未定义的目标迈进。你必须知道你想问什么问题才能得到答案。
其次,为什么使用BQ来分析大型数据集?
  1. 它位于云端,因此可以扩展到您需要的任何级别。
  2. 除非你正在做一些真正巨大的事情,否则它的现收现付成本很便宜。
  3. 它所需的唯一设置可以在没有编程技能的情况下完成。
  4. 界面非常简单易用。
什么时候应该使用BigQuery进行数据分析?

正如我们上面提到的,这个的主要用例要么是当Excel失败/变得太慢,要么是你需要在不同的集合中重复回答一个复杂的问题而写一个查询比在Excel中操作多个数据集要快得多。

这些问题有哪些好例子?

  1. 使用超过1,048,576行的任何数据集,例如:
    • 大型网站的反向链接分析
    • 日志分析
    • 内部链接审核。
  2. 使用多个数据集,其中VLOOKUPS和过滤可以使Excel停顿,例如:
    • 将内部电子商务产品数据与大型网站的抓取数据合并,以回答诸如“我们有多少历史上最成功的产品具有正确设置页面?”之类的问题。
    • 将非抽样GA数据与爬网数据混合,您需要在完整路径而不是URL文件夹上进行汇总。

就个人而言,我发现我的计算机一旦超过600k行就开始挣扎,所以通常在那时我会转向BQ。

希望你现在正在考虑这个选项,当Excel崩溃时,让我们跳进去吧。

1.提出聪明的问题

您不能像在Excel中一样操纵和使用SQL表中的数据。偶尔任务确实需要那种手动拨动和调查(枢轴表很好); 但是,我发现我每天遇到的许多问题都不需要那种分析。他们需要我确切地定义我想回答的问题是什么。

因此,让我们定义一些我们可以通过本文回答的问题:

  1. 在我的抓取数据中哪些标题标签比512px更宽,按有机访问次数划分优先级?
  2. 在非规范登录页面上开始了多少个会话?
  3. 我们的目标完成来自哪些网站部分?

我用Screaming Frog(SF)抓取了Distilled.net网站,然后创建了一些虚假的Google Analytics(GA)数据,以便回答这些问题。在这种情况下,数据集并不庞大,可以在Excel中完成,但它可以让我们感到舒服。

2.获取BigQuery帐户

首先,您需要一个Google帐户。

然后您注册了BQ免费试用版,但您实际获得的是Google Cloud Platform帐户。尽管我们稍后将在本教程中使用Google云端存储(GCS),但您不必使用Google云端平台中的任何其他软件。

(GCS就像是一个企业级Dropbox / Google Drive,对用户不太友好。)

一旦你全部注册,你应该看一个仪表板。

从这里我们需要访问BigQuery本身。这可以从左上角菜单完成。在这里您还可以找到我们之前提到的“存储”下的云存储。

3. BQ仪表板

这是主要的BQ界面。快速浏览屏幕:

  1. 这些是主要菜单选项。
    • 撰写查询打开框3。
    • 查询历史记录是您最近查询的记录。
    • 作业历史记录是您对数据采取的所有最近操作的记录,几乎总是加载并提取它。
  2. 这是存储所有数据集的位置。每个数据集基本上都是一个项目。数据集包含包含数据的各个表。
  3. 这是您编写查询的地方。
  4. 这是显示作业历史记录和查询历史记录的位置。

足够无聊的博览会。让我们通过实践来学习并上传这些示例数据集。

4.如何将数据上传到BQ?

首先,我们创建一个新的数据集。我们称之为distil_test。

我们点击2上方的小箭头(如上图所示)并输入我们的详细信息。

  • ID: distilled_test
  • 数据位置:欧盟
  • 数据过期:从不(如果您认为忘记删除它,可以设置此项。)

然后我们将鼠标悬停在数据集上,然后单击加号图标。我们将获得创建表格并上传数据的选项。

将数据加载到SQL样式数据库不同于将数据加载到Excel中 - 除非另有说明,否则规则更严格。主要4是:

1.每行必须具有相同数量的列。采用以下示例CSV:

  • 苹果,土豆,巧克力
  • Apple,甜甜圈
  • 苹果,土豆,巧克力
  • Apple,甜甜圈

第一个是有效的; 第二个不行。值得庆幸的是,我们的大多数常见导出(如GA和SF)默认执行此操作,您可以启用“允许锯齿状行”选项来绕过此选项。

2.默认情况下,您无法使用字段内的新行加载数据。同样,这在大多数情况下都不应成为问题,但您可以通过爬网遇到问题。

事实上,我们将使用我们的Distilled抓取数据集来处理它,因为我们有一些非常可怕的页面,我们在H2中有多个段落。谢天谢地,它很容易解决; 我们通过勾选“允许引用的换行符”框来执行此操作。

3.您必须为每列定义数据类型。这将限制你可以做的事情; 例如,您无法对字符串执行添加。您可以在表单中或在文本框中逐行执行此操作。我已经为Google Landing Page Report和Screaming Frog基本导出提供了以下两种模式,以帮助您入门,您可以将其复制并粘贴到文本框中。(如果将SF用于其他SF数据集,则SF可能需要进行小的更改,因为SF导出会根据找到的内容返回动态的列数)。

4.您必须告诉BigQuery您希望在数据中允许多少错误 - 否则它会在一次错误时失败。如果我一次上传一个文件,我倾向于假设没有错误,除非另有证明。如果我自动化大的东西,我倾向于允许一些错误并监视它们,以防止它在我不看的时候破坏。

从加载屏幕中提到的另一个不可自由解释的是位置。您只需手动上传数据集,使用Google云端硬盘或Google云端存储(GCS)即可。因为我通常使用具有非常大的数据集的BQ,所以我使用GCS(GCS非常简单,所以我不打算在这里介绍它),但是你可以使用适合你的任何东西。

我提供的两个示例数据集都应该直接加载下面的Schema:

SF标准出口模式:

地址:STRING,内容:STRING,STATUS_CODE:INTEGER,状态:STRING,TITLE1:STRING,title_1_length:INTEGER,title_1_pixel:INTEGER,meta_description:STRING,meta_description_length:INTEGER,meta_description_pixel:INTEGER,meta_keyword:STRING,meta_keyword_length:INTEGER,h1_1: STRING,h1_1_length:INTEGER,h1_2:STRING,h1_2_length:INTEGER,h2_1:STRING,h2_1_length:INTEGER,h2_2:STRING,h2_2_length:INTEGER,meta_robots:STRING,meta_refresh:STRING,规范:STRING,尺寸:INTEGER,WORD_COUNT:INTEGER,级:INTEGER,反向链接:INTEGER,对外连结:INTEGER,external_outlinks:INTEGER,哈希:STRING,响应:FLOAT,last_mod:STRING,REDIRECT_URL:STRING

GA登陆页面出口架构:

LANDING_PAGE:STRING,会议:INTEGER,percent_new_sessions:FLOAT,NEW_USERS:INTEGER,BOUNCE_RATE:FLOAT,pages_per_sessions:FLOAT,avg_session_length:FLOAT,交易:INTEGER,收入:FLOAT,ecomm_conversion_rate:FLOAT

除此之外:Screaming Frog在CSV的顶部添加了一个名为“Internal All”的额外行。我事先删除了。

5.添加几个额外的字段

在我们提出任何问题之前,对SF数据进行一些额外处理是很有用的。具体来说,我们想要分离出我们的URL参数并提取每个文件夹,以便我们可以轻松地使用它。

我们可以通过运行以下查询并将结果保存为新表来实现。(由于这个SQL有点复杂,而且你也不会经常使用这些函数[如果有的话],我们会在你查看所有其他查询之后在最后解释这个。)

选择 * SUBSTR(path_and_host,INSTR(地址,'。net')+ 4)作为路径, NTH(3,SPLIT(path_and_host,'/'))page_path_1, NTH(4,SPLIT(path_and_host,'/'))page_path_2, NTH(5,SPLIT(path_and_host,'/'))page_path_3, NTH(6,SPLIT(path_and_host,'/'))page_path_4 来自( 选择 * NTH(2,SPLIT(地址,'?'))作为查询, NTH(1,SPLIT(地址,'?'))作为path_and_host, 从 [distilled_test.crawl_data] )

然后我们删除旧表,我们很高兴。看着我们的桌子,我们现在应该:

6.使用BQ运行查询

谈到有趣的部分:回答这些问题。

我将解释这些问题,但我们会尽快保持这篇文章的合理篇幅。还有一些关于学习SQL的免费课程可以填补这些空白。Code Academy有一个你可以在这里找到的,我强烈建议你这样做。

问题1:我的抓取数据中哪些标题标签比512px更宽,优先考虑有机访问次数?

让我们逐一处理这部分内容。首先,我们要从SF数据集返回标题标签及其所属的页面:

选择 地址,标题1 从 [distilled_test.crawl_data_final]

然后我们想要将其过滤到宽度大于512px的所有标题。

选择 地址,标题1 从 [distilled_test.crawl_data_final] 哪里 title_1_pixel> 512

到现在为止还挺好!

现在它变得有点复杂了。要按有机访问次数划分优先级,我们需要引入我们的ga_data。这意味着我们需要加入这两个表。

选择 地址,标题1,会话 来自( 选择 地址, 路径, TITLE1 从 [distilled_test.crawl_data_final] 哪里 title_1_pixel> 512)AS crawl_query LEFT JOIN( 选择 登陆页面, 会议 从 [distilled_test.ga_data])AS ga_data 上 crawl_query.path = ga_data.landing_page 订购 会议设计

要围绕此问题,请查找括号和命名查询。我们运行了两个单独的查询:

首先,我们有查询过滤所有超过512px的标题:

( 选择 地址, 路径, TITLE1 从 [distilled_test.crawl_data_final] 哪里 title_1_pixel> 512)AS crawl_query

然后我们有第二个查询返回每个登录页面的会话:

选择 登陆页面, 会议 从 [distilled_test.ga_data])AS ga_data

然后我们一起加入这两个。

我们已经对BQ说过:第一个查询的页面与第二个查询的登录页面匹配,将这些行连接在一起。GA默认只为您提供路径(即它不存储www.distilled.net/page,仅存储/页面),因此我们将其与我们在上一步中创建的路径相连。

SQL中有许多不同类型的JOIN。如果你有兴趣的话,我发现他们的最佳解释就是这个

Q2:在非规范登录页面上开始的会话数量是多少?

首先,我们需要获得每页的会话总数:

选择 登陆页面, 会议 从 [distilled_test.ga_data])AS ga_data

然后我们需要避免陷阱。如果您在Excel中执行此操作,这正是一种很容易错过的问题,但SQL会迫使您去思考。

寻找规范网址实际上有点痛苦。如果我们从我们的scrape中选择所有非规范URL,删除它们并加入其中,那么我们假设GA中的其他所有内容都是规范的,并且肯定不会成立。所以我们需要做相反的事情:找到所有规范的URL并假设其他一切都是非规范的(一个不太糟糕的假设,尽管这会错过孤立的规范页面)。

(这也会假设没有规范的页面是非规范的,但我们可以做的很少。如果这是审计,我们可以找到没有规范的所有页面并让客户端设置它们向上。)

因此,让我们选择所有规范网址。

选择 地址 从 [distilled_test.crawl_data_final] 哪里 地址=规范

然后加入两个表并仅选择条目WHERE,我们与规范URL和ORDER BY会话不匹配,以确定它们的优先级。

选择 登陆页面, 会议 来自( 选择 登陆页面, 会议 从 [distilled_test.ga_data])AS ga_data LEFT JOIN( 选择 路径, 从 [distilled_test.crawl_data_final] 哪里 address = canonical)AS crawl_data 上 ga_data.landing_page = crawl_data.path 哪里 crawl_data.path是NULL 订购 会议DESC

全做完了!以下是前5个网址。

  • /资源/视频/ SL-BOS-16-marcustober
  • /company/people/tom-critchlow.html/
  • /资源/视频/ SL-圣迭戈-15-scottedwards
  • /资源/视频/社区
  • /说话的语气//
问题3:我们的目标完成来自哪些网站部分?

我们可以通过连接两个表来完成此操作,就像我们在前两个查询中所做的那样,或者我们可以像处理SF数据一样处理数据并直接查询。这实际上会更容易,并避免一些可能的其他陷阱。

偶尔退一步是避免“当你有一把锤子,你看到的只是指甲”问题的好方法。

选择 * NTH(1,SPLIT(路径,'/'))page_path_1, NTH(2,SPLIT(路径,'/'))page_path_2, NTH(3,SPLIT(路径,'/'))page_path_3, NTH(4,SPLIT(路径,'/'))page_path_4 来自( 选择 * NTH(1,SPLIT(landing_page,'?'))作为路径, NTH(2,SPLIT(landing_page,'?'))作为查询 从 [distilled_test.ga_data] )

此时,现在值得解释这个查询。我们有一个查询包含在另一个查询中。第一个说“通过使用'?'拆分目标网页来添加额外的两列 作为分界符。“

选择 * NTH(1,SPLIT(landing_page,'?'))作为路径, NTH(2,SPLIT(landing_page,'?'))作为查询 从 [distilled_test.ga_data]

第二个说“然后用正斜杠作为分隔符SPLIT路径,并将前4个page_paths保存为单独的列。”

选择 * NTH(1,SPLIT(路径,'/'))page_path_1, NTH(2,SPLIT(路径,'/'))page_path_2, NTH(3,SPLIT(路径,'/'))page_path_3, NTH(4,SPLIT(路径,'/'))page_path_4 来自( QUERY_1 )

一旦我们有了新表,我们就可以问一个非常基本的查询来得到我们的答案。

选择 page_path_1,SUM(transactions)为total_transactions 从 [distilled_test.ga_data_final] 通过...分组 page_path_1 订购 total_transactions DESC

这里唯一的新事物是GROUP BY。我们想要所有事务的总和,所以我们将一个计算字段添加到SUM事务中,但我们还必须告诉它要汇总什么。我们用GROUP BY做到这一点。我们说GROUP的所有字段都具有相同的page_path_1,并且我们将计算字段的内容提供给SUM。

恭喜你,你已经到了尽头!我们不仅得到了一些有用的答案,而且如果你现在要在不同的项目或更新的集合上提出这些相同的问题,你可以复制并粘贴相同的查询并获得新的结果。我们曾经做过一次艰苦的工作,而且不会再这样做了。

关于导出结果的最后一件事

想要导出这些结果?可以将小查询的结果直接下载为CSV,但是对于较大的结果,您需要将它们另存为另一个表,然后将表导出到GCS并从那里下载。

在这里,我将结果下载到GCS中的test_distilled存储桶中。

所有文章
×

还剩一步!

确认邮件已发至你的邮箱。 请点击邮件中的确认链接,完成订阅。

好的