如何通过BigQuery导出Google Analytics近12个月全量搜索查询数据

你已经激活GA API的前提下，无需复杂的BigQuery配置，用以下两种方案都可满足需求，完全规避界面单页5000条的导出限制：

前置准备 #

• 确认你当前的GCP账号拥有目标GA数据视图的读取权限
• 提前记录目标数据视图ID，可在GA后台「管理 - 视图设置」中直接获取
• 若选择BigQuery方案，确认GA账户有对应GCP项目的关联权限

方案1：Google Apps Script实现（零本地环境，最简便） #

无需安装任何本地工具，直接在浏览器内完成全量数据拉取，自动处理分页：

1. 新建空白Google Sheets，点击顶部「扩展程序 - Apps Script」进入脚本编辑器
2. 删除默认代码，替换为以下模板，修改对应配置项即可：

// 仅需修改这三个基础配置
const VIEW_ID = '替换为你的GA视图ID';
const START_DATE = '12monthsAgo'; // 固定取近12个月，无需修改
const END_DATE = 'today';

function pullFullSearchQueries() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 写入表头，可自行增减需要的维度、指标
  sheet.clear();
  sheet.appendRow(['搜索查询', '展示次数', '点击次数', '点击率', '平均排名']);

  let nextPageToken = null;
  do {
    const request = {
      viewId: VIEW_ID,
      dateRanges: [{startDate: START_DATE, endDate: END_DATE}],
      dimensions: [{name: 'ga:searchKeyword'}],
      metrics: [
        {expression: 'ga:impressions'},
        {expression: 'ga:adClicks'},
        {expression: 'ga:CTR'},
        {expression: 'ga:averagePosition'}
      ],
      pageSize: 10000,
      pageToken: nextPageToken,
      samplingLevel: 'LARGE'
    };
    const res = AnalyticsReporting.Reports.batchGet({reportRequests: [request]});
    const report = res.reports[0];
    const rows = report.data.rows || [];
    // 批量写入当前页数据
    rows.forEach(row => sheet.appendRow(row.dimensions.concat(row.metrics[0].values)));
    nextPageToken = report.nextPageToken;
  } while (nextPageToken);
  SpreadsheetApp.getUi().alert('全量数据拉取完成');
}

3. 点击编辑器左侧「服务」，添加「Analytics Reporting API v4」服务后保存
4. 运行pullFullSearchQueries函数，首次运行按照提示完成账号授权即可
5. 数据全部同步到Sheets后，直接点击「文件 - 下载 - Microsoft Excel (.xlsx)」即可导出Excel格式

注：100万条数据Google Sheets可正常承载，拉取过程无需值守，脚本会自动处理分页，无5000条上限限制，遇到采样问题可把时间范围拆为单月分别拉取后合并即可。

方案2：BigQuery同步方案（适合后续有大量分析需求的场景） #

无需自行对接API导数据，直接配置GA到BigQuery的官方同步即可：

1. 打开GA后台，进入「管理 - 产品关联 - BigQuery 关联」，选择已激活的GCP项目完成关联配置
2. 同步完成后在BigQuery中运行以下SQL即可提取符合要求的搜索查询数据：

SELECT
  searchKeyword AS 搜索查询,
  SUM(impressions) AS 展示次数,
  SUM(clicks) AS 点击次数
FROM `你的BigQuery项目ID.你的GA数据集ID.ga_sessions_*`,
UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)) 
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND searchKeyword IS NOT NULL
GROUP BY searchKeyword

3. 查询结果可直接从BigQuery界面导出为CSV格式，手动转存为Excel即可。

内容的提问来源于stack exchange，提问作者Nes Elm