如何使用Google Sheets自动化GSC报表

我们出海SEO的日常工作中了解URL与检索词的排名情况是我们从更低视角理解SEO表现的方法。

举个例子，某个URL本周对比上周的表现下降了。那或许是这个URL出现在一些新出现的检索词上而这些检索词恰巧我们这个URL排在较低的位置。此时我们的排名被平均了而不是下降。

同样的逻辑，某个词的排名下降了。那也有可能是我们有新的URL被排到了较低的位置从而拉低了我们平均排名。

所以SEO上讲究URL-Query Pair的排名。而要获得这样的数据，我们必须在Google Search Console的Performance报表中一个一个Drill down。这实际上是一个非常不效率的做法。不管是GSC中下载导出，还是使用Looker Studio来进行自定义报表都无法实现。

为了一举获得报表，马老师建议读者使用Google Sheets和其Extension来增加效率。具体做法如下：

第一步，新建一个Google Sheet，获取Add-on，如下图：

检索API Connector并安装，其过程中需要授权。安装之后你的Extensions菜单中就有了API Connector了。

第二步，打开API Connector，Create request。在Application菜单中选择Google Search Console并点击Connect to Google Search Console。授权后继续。

第三步，这里有两个Endpoint可选。第一个GET /sites是看你连在了哪些网站上，第二个才是我们要查询用的。不过我们这次先选第一个。然后在OUTPUT SETTINGS的Destination sheet里面选Set current选择当前表作为目标输出表。点Run。如果这步出错，请Disconnect OAuth重新再连。

第四步，选一个你要查的Site。然后将URL编码。使用ENCODEURL公式。如下图：

编码后的URL我们下一步要用。

第五步，Endpoint选择POST的那个。并在siteUrl中填入我们刚才编码的URL（例子中是https%3A%2F%2Fmaxket.com%2F）。填入startDate与endDate。然后在dimensions中填入page和query。先不要点Run，选左边的按钮Edit fields。这会出来Preview and Edit Fields界面。在这个界面中你可以修改表头名称并删除不需要的CTR栏和responseAggregationType栏。点击Save fields。现在已经初见雏形了。如下图所示：

第六步，测试跑一下，点击Run。稍加整理你应该能看到下图这样的结果。

如果跑出来没问题的话你可以把rowLimit放宽到25,000。一般来说这可以满足我们研究的需求了。如果不够的话也可以在Output options里面选择Pagination-offset-limit body，填入开始行和返回最大限制。然后我们点Save保存。

以上六个步骤就是我们利用Google Sheets导出GSC数据的方法。那么如何进行自动化呢？你可以先建一个Settings的Sheet。放入日期，这里用了公式自动算上个月的第一天和最后一天。

然后我们回到我们的Request在startDate和endDate这里点小图标Toggle input type，然后分别填入+++Sheet1!B1+++和+++Sheet1!B2+++。这样每次跑的时间段都会自动跑上个月的数据。

接着我们回到API Connector插件在最上面选择Schedule，然后Create trigger。填入下面的参数。

至此我们的工作就完成了。如果你需要自定义GSC报表也可以用Custom的Query然后使用JSON来查询。

最后，很重要的一点再强调一下，GSC里面的Query数据并不全。由于隐私合规问题谷歌隐去了许多它认为的敏感数据。因此加起来总和不对也不要意外。

以上，API Connector是一个很好用的插件，而且是免费的。希望本文对你的工作有所帮助。