clicks timeline 2

// IMPORTANT: Paste this file as-is into ONE Google Ads script project.
// Do NOT wrap it with `function main() { ... }` and do NOT mix 02/03/06 in the same project.
//
// This script is designed for append-only hourly snapshots:
// - query search-term level SEARCH traffic
// - keep segments.hour
// - append rows in capture order
// - support shard-based multi-script rotation
// - optional Feishu notifications

const CONFIG = {
  SPREADSHEET_URL:
    'https://docs.google.com/spreadsheets/d/1IRVoni0t8VQc2u9G35ajuC28vRyCBiJ3wYULpnipDwI/edit',
  API_VERSION: 'v22',
  MAX_RUNTIME_MINUTES: 25,

  // Snapshot mode
  TIME_GRAIN: 'HOURLY',
  LOOKBACK_HOURS: 2,
  MIN_IMPRESSIONS: 1,
  ONLY_ROWS_WITH_CLICKS: true,
  MAX_ROWS_PER_RUN: 80000,
  WRITE_BATCH_SIZE: 1000,

  // Multi-script sharding
  SCRIPT_LABEL: 'clicks-timeline-shard-01',
  SHARD_COUNT: 6,
  SHARD_INDEX: 1,

  // Optional campaign filter
  CAMPAIGN_NAME_CONTAINS: '',

  // Sheets
  TIMELINE_SHEET: '21_clicks_timeline_shard_01',
  LOG_SHEET: '00_RUN_LOG_CLICKS_TIMELINE_shard_01',
  STATUS_SHEET_PREFIX: '00_STATUS_CLICKS_TIMELINE_',

  BASE_ROWS: 200,
  BASE_COLS: 40,

  ENABLE_FEISHU: true,
  SEND_FEISHU_ON_EMPTY: false,
  FEISHU_WEBHOOK_URL:
    'https://open.feishu.cn/open-apis/bot/v2/hook/2c4db3a0-7bdc-42a6-bc0e-44da7344fe87',
  FEISHU_TOKEN: 'oRbJgofj3viHwkzjflTozh',
};

function main() {
  validateShardConfig();

  const ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  const account = AdsApp.currentAccount();
  const tz = account.getTimeZone();
  const startedAt = new Date();
  const runId = Utilities.formatDate(startedAt, tz, 'yyyyMMdd-HHmmss');
  const windowInfo = buildHourlyWindow(tz, CONFIG.LOOKBACK_HOURS);
  const statusSheet = getStatusSheetName();
  const timelineHeader = buildTimelineHeader();

  ensureLogHeader(ss);
  ensureTimelineHeader(ss, timelineHeader);
  writeStatus(ss, statusSheet, runId, account, tz, 'RUNNING', windowInfo, 0, '');

  const query = buildTimelineQuery(windowInfo.queryStartDate, windowInfo.queryEndDate);
  const report = AdsApp.report(query, { apiVersion: CONFIG.API_VERSION });
  const it = report.rows();

  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  let totalExamined = 0;
  let totalWritten = 0;
  let batch = [];

  try {
    while (it.hasNext()) {
      if (elapsedMinutes(startedAt) >= CONFIG.MAX_RUNTIME_MINUTES) {
        flushTimelineBatch(ss, batch);
        totalWritten += batch.length;
        batch = [];

        appendLog(ss, [
          runId,
          formatDateTime(new Date(), tz),
          CONFIG.SCRIPT_LABEL,
          String(CONFIG.SHARD_INDEX),
          String(account.getCustomerId()),
          safeString(account.getName()),
          'PAUSED_TIMEOUT',
          CONFIG.TIMELINE_SHEET,
          totalExamined,
          totalWritten,
          `window=${windowInfo.bucketStart} ~ ${windowInfo.bucketEnd}`,
        ]);

        writeStatus(
          ss,
          statusSheet,
          runId,
          account,
          tz,
          'PAUSED_TIMEOUT',
          windowInfo,
          totalWritten,
          `examined=${totalExamined}`
        );

        maybeSendFeishu(
          buildFeishuText({
            runId: runId,
            account: account,
            tz: tz,
            status: 'PAUSED_TIMEOUT',
            windowInfo: windowInfo,
            written: totalWritten,
            examined: totalExamined,
          }),
          true
        );
        return;
      }

      const row = it.next();
      totalExamined += 1;

      const mapped = mapTimelineRow(row, runId, tz, windowInfo);
      if (!mapped) continue;

      batch.push(mapped);
      if (batch.length >= CONFIG.WRITE_BATCH_SIZE) {
        flushTimelineBatch(ss, batch);
        totalWritten += batch.length;
        batch = [];
      }

      if (totalWritten + batch.length >= CONFIG.MAX_ROWS_PER_RUN) {
        flushTimelineBatch(ss, batch);
        totalWritten += batch.length;
        batch = [];
        appendLog(ss, [
          runId,
          formatDateTime(new Date(), tz),
          CONFIG.SCRIPT_LABEL,
          String(CONFIG.SHARD_INDEX),
          String(account.getCustomerId()),
          safeString(account.getName()),
          'PAUSED_MAX_ROWS',
          CONFIG.TIMELINE_SHEET,
          totalExamined,
          totalWritten,
          `limit=${CONFIG.MAX_ROWS_PER_RUN}`,
        ]);
        writeStatus(
          ss,
          statusSheet,
          runId,
          account,
          tz,
          'PAUSED_MAX_ROWS',
          windowInfo,
          totalWritten,
          `examined=${totalExamined}`
        );
        maybeSendFeishu(
          buildFeishuText({
            runId: runId,
            account: account,
            tz: tz,
            status: 'PAUSED_MAX_ROWS',
            windowInfo: windowInfo,
            written: totalWritten,
            examined: totalExamined,
          }),
          true
        );
        return;
      }
    }

    flushTimelineBatch(ss, batch);
    totalWritten += batch.length;
    batch = [];
    applyTimelineFormats(ss, timelineHeader);

    appendLog(ss, [
      runId,
      formatDateTime(new Date(), tz),
      CONFIG.SCRIPT_LABEL,
      String(CONFIG.SHARD_INDEX),
      String(account.getCustomerId()),
      safeString(account.getName()),
      'FINISHED',
      CONFIG.TIMELINE_SHEET,
      totalExamined,
      totalWritten,
      `window=${windowInfo.bucketStart} ~ ${windowInfo.bucketEnd}`,
    ]);

    writeStatus(
      ss,
      statusSheet,
      runId,
      account,
      tz,
      'FINISHED',
      windowInfo,
      totalWritten,
      `examined=${totalExamined}`
    );

    maybeSendFeishu(
      buildFeishuText({
        runId: runId,
        account: account,
        tz: tz,
        status: 'FINISHED',
        windowInfo: windowInfo,
        written: totalWritten,
        examined: totalExamined,
      }),
      totalWritten > 0 || CONFIG.SEND_FEISHU_ON_EMPTY
    );
  } finally {
    try {
      lock.releaseLock();
    } catch (e) {}
  }
}

function validateShardConfig() {
  if (CONFIG.TIME_GRAIN !== 'HOURLY') {
    throw new Error('CONFIG.TIME_GRAIN must stay HOURLY for clicks timeline');
  }
  if (CONFIG.SHARD_COUNT < 1) {
    throw new Error('CONFIG.SHARD_COUNT must be >= 1');
  }
  if (CONFIG.SHARD_INDEX < 0 || CONFIG.SHARD_INDEX >= CONFIG.SHARD_COUNT) {
    throw new Error('CONFIG.SHARD_INDEX must be between 0 and SHARD_COUNT-1');
  }
}

function buildTimelineHeader() {
  return [
    'captured_at',
    'run_id',
    'script_label',
    'shard_index',
    'shard_count',
    'hour_bucket',
    'segments.date',
    'segments.hour',
    'customer.id',
    'customer.descriptive_name',
    'campaign.id',
    'campaign.name',
    'ad_group.id',
    'ad_group.name',
    'ad_group_ad.ad.id',
    'segments.keyword.ad_group_criterion',
    'keyword.criterion_id',
    'segments.keyword.info.text',
    'segments.keyword.info.match_type',
    'search_term_view.search_term',
    'search_term_view.status',
    'segments.search_term_match_type',
    'segments.search_term_targeting_status',
    'metrics.impressions',
    'metrics.clicks',
    'metrics.ctr',
    'metrics.average_cpc_micros',
    'metrics.average_cpc',
    'metrics.cost_micros',
    'metrics.cost',
    'metrics.conversions',
    'metrics.conversions_value',
  ];
}

function buildTimelineQuery(startDate, endDate) {
  const whereClauses = [
    "campaign.advertising_channel_type = SEARCH",
    `segments.date BETWEEN '${startDate}' AND '${endDate}'`,
    `metrics.impressions >= ${Number(CONFIG.MIN_IMPRESSIONS)}`,
  ];

  if (CONFIG.ONLY_ROWS_WITH_CLICKS) {
    whereClauses.push('metrics.clicks > 0');
  }

  if (safeString(CONFIG.CAMPAIGN_NAME_CONTAINS)) {
    whereClauses.push(
      `campaign.name LIKE '%${escapeGaqlString(CONFIG.CAMPAIGN_NAME_CONTAINS)}%'`
    );
  }

  return `
    SELECT
      customer.id,
      customer.descriptive_name,
      campaign.id,
      campaign.name,
      ad_group.id,
      ad_group.name,
      ad_group_ad.ad.id,
      segments.keyword.ad_group_criterion,
      segments.keyword.info.text,
      segments.keyword.info.match_type,
      search_term_view.search_term,
      search_term_view.status,
      segments.search_term_match_type,
      segments.search_term_targeting_status,
      segments.date,
      segments.hour,
      metrics.impressions,
      metrics.clicks,
      metrics.ctr,
      metrics.average_cpc,
      metrics.cost_micros,
      metrics.conversions,
      metrics.conversions_value
    FROM search_term_view
    WHERE ${whereClauses.join('\n      AND ')}
    ORDER BY
      segments.date ASC,
      segments.hour ASC,
      campaign.id ASC,
      ad_group.id ASC,
      ad_group_ad.ad.id ASC,
      segments.keyword.info.text ASC,
      search_term_view.search_term ASC
    LIMIT ${Number(CONFIG.MAX_ROWS_PER_RUN)}
  `;
}

function buildHourlyWindow(tz, lookbackHours) {
  const now = new Date();
  const currentHourStart = new Date(now.getTime());
  currentHourStart.setMinutes(0, 0, 0);
  const firstHourStart = new Date(
    currentHourStart.getTime() - Math.max(0, lookbackHours - 1) * 60 * 60 * 1000
  );
  const bucketKeys = {};
  const bucketLabels = [];

  for (let t = firstHourStart.getTime(); t <= currentHourStart.getTime(); t += 60 * 60 * 1000) {
    const d = new Date(t);
    const bucket = formatHourBucket(d, tz);
    bucketKeys[bucket] = true;
    bucketLabels.push(bucket);
  }

  return {
    queryStartDate: Utilities.formatDate(firstHourStart, tz, 'yyyy-MM-dd'),
    queryEndDate: Utilities.formatDate(currentHourStart, tz, 'yyyy-MM-dd'),
    bucketKeys: bucketKeys,
    bucketStart: bucketLabels[0],
    bucketEnd: bucketLabels[bucketLabels.length - 1],
  };
}

function mapTimelineRow(row, runId, tz, windowInfo) {
  const dateText = safeString(row['segments.date']);
  const hourNum = toNumber(row['segments.hour']);
  const bucketKey = `${dateText} ${pad2(hourNum)}`;

  if (!windowInfo.bucketKeys[bucketKey]) {
    return null;
  }

  const shardKey = [
    safeString(row['campaign.id']),
    safeString(row['ad_group.id']),
    safeString(row['ad_group_ad.ad.id']),
    safeString(row['segments.keyword.ad_group_criterion']),
    safeString(row['search_term_view.search_term']),
  ].join('|');

  if (getShard(shardKey, CONFIG.SHARD_COUNT) !== CONFIG.SHARD_INDEX) {
    return null;
  }

  const avgCpc = toNumber(row['metrics.average_cpc']);
  const costMicros = toNumber(row['metrics.cost_micros']);
  const keywordResource = safeString(row['segments.keyword.ad_group_criterion']);

  return [
    formatDateTime(new Date(), tz),
    runId,
    CONFIG.SCRIPT_LABEL,
    CONFIG.SHARD_INDEX,
    CONFIG.SHARD_COUNT,
    bucketKey,
    dateText,
    hourNum,
    safeString(row['customer.id']),
    safeString(row['customer.descriptive_name']),
    safeString(row['campaign.id']),
    safeString(row['campaign.name']),
    safeString(row['ad_group.id']),
    safeString(row['ad_group.name']),
    safeString(row['ad_group_ad.ad.id']),
    keywordResource,
    parseCriterionId(keywordResource),
    safeString(row['segments.keyword.info.text']),
    safeString(row['segments.keyword.info.match_type']),
    safeString(row['search_term_view.search_term']),
    safeString(row['search_term_view.status']),
    safeString(row['segments.search_term_match_type']),
    safeString(row['segments.search_term_targeting_status']),
    toNumber(row['metrics.impressions']),
    toNumber(row['metrics.clicks']),
    toNumber(row['metrics.ctr']),
    Math.round(avgCpc * 1000000),
    avgCpc,
    costMicros,
    microsToCurrency(costMicros),
    toNumber(row['metrics.conversions']),
    toNumber(row['metrics.conversions_value']),
  ];
}

function getShard(text, shardCount) {
  let hash = 0;
  const s = safeString(text);
  for (let i = 0; i < s.length; i++) {
    hash = (hash * 31 + s.charCodeAt(i)) % 2147483647;
  }
  return Math.abs(hash) % shardCount;
}

function ensureTimelineHeader(ss, header) {
  const sh = getOrCreateSheet(ss, CONFIG.TIMELINE_SHEET);
  ensureSheetSize(sh, CONFIG.BASE_ROWS, Math.max(CONFIG.BASE_COLS, header.length));

  const hasHeader = sh.getLastRow() > 0 && sh.getLastColumn() >= header.length;
  if (!hasHeader) {
    sh.getRange(1, 1, 1, header.length).setValues([header]);
    sh.setFrozenRows(1);
    return;
  }

  const existing = sh.getRange(1, 1, 1, header.length).getValues()[0];
  const mismatch = existing.join('||') !== header.join('||');
  if (mismatch) {
    sh.clearContents();
    sh.getRange(1, 1, 1, header.length).setValues([header]);
    sh.setFrozenRows(1);
  }
}

function flushTimelineBatch(ss, rows) {
  if (!rows || rows.length === 0) return;
  const sh = getOrCreateSheet(ss, CONFIG.TIMELINE_SHEET);
  const nextRow = sh.getLastRow() + 1;
  ensureSheetSize(
    sh,
    nextRow + rows.length + 20,
    Math.max(CONFIG.BASE_COLS, rows[0].length)
  );
  sh.getRange(nextRow, 1, rows.length, rows[0].length).setValues(rows);
}

function applyTimelineFormats(ss, header) {
  const sh = getOrCreateSheet(ss, CONFIG.TIMELINE_SHEET);
  const lastRow = sh.getLastRow();
  if (lastRow < 2) return;
  const rowCount = lastRow - 1;
  const ctrCol = header.indexOf('metrics.ctr') + 1;
  const avgCpcCol = header.indexOf('metrics.average_cpc') + 1;
  const costCol = header.indexOf('metrics.cost') + 1;
  const convCol = header.indexOf('metrics.conversions') + 1;
  const convValueCol = header.indexOf('metrics.conversions_value') + 1;

  if (ctrCol > 0) sh.getRange(2, ctrCol, rowCount, 1).setNumberFormat('0.00');
  if (avgCpcCol > 0) sh.getRange(2, avgCpcCol, rowCount, 1).setNumberFormat('0.00');
  if (costCol > 0) sh.getRange(2, costCol, rowCount, 1).setNumberFormat('0.00');
  if (convCol > 0) sh.getRange(2, convCol, rowCount, 1).setNumberFormat('0.00');
  if (convValueCol > 0) sh.getRange(2, convValueCol, rowCount, 1).setNumberFormat('0.00');
}

function getStatusSheetName() {
  const safeLabel = CONFIG.SCRIPT_LABEL.replace(/[^A-Za-z0-9_-]+/g, '_');
  return `${CONFIG.STATUS_SHEET_PREFIX}${safeLabel}`.slice(0, 99);
}

function ensureLogHeader(ss) {
  const sh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
  ensureSheetSize(sh, CONFIG.BASE_ROWS, 11);
  const header = [
    'run_id',
    'ts',
    'script_label',
    'shard_index',
    'customer.id',
    'customer.name',
    'status',
    'sheet',
    'examined_rows',
    'written_rows',
    'note',
  ];
  sh.getRange(1, 1, 1, header.length).setValues([header]);
  sh.setFrozenRows(1);
}

function appendLog(ss, row) {
  const sh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
  ensureSheetSize(sh, sh.getLastRow() + 20, 11);
  sh.appendRow(row);
}

function writeStatus(ss, sheetName, runId, account, tz, status, windowInfo, rowsWritten, note) {
  const sh = getOrCreateSheet(ss, sheetName);
  ensureSheetSize(sh, 20, 2);
  const values = [
    ['key', 'value'],
    ['run_id', runId],
    ['ts', formatDateTime(new Date(), tz)],
    ['script_label', CONFIG.SCRIPT_LABEL],
    ['shard_index', String(CONFIG.SHARD_INDEX)],
    ['shard_count', String(CONFIG.SHARD_COUNT)],
    ['customer.id', String(account.getCustomerId())],
    ['customer.name', safeString(account.getName())],
    ['status', status],
    ['lookback_hours', String(CONFIG.LOOKBACK_HOURS)],
    ['window_start_bucket', windowInfo.bucketStart],
    ['window_end_bucket', windowInfo.bucketEnd],
    ['rows_written', String(rowsWritten)],
    ['timeline_sheet', CONFIG.TIMELINE_SHEET],
    ['note', safeString(note)],
  ];
  sh.clearContents();
  sh.getRange(1, 1, values.length, 2).setValues(values);
  sh.setFrozenRows(1);
}

function buildFeishuText(ctx) {
  return [
    '[Google Ads Clicks Timeline]',
    `status: ${ctx.status}`,
    `account: ${safeString(ctx.account.getName())} (${String(ctx.account.getCustomerId())})`,
    `script: ${CONFIG.SCRIPT_LABEL}`,
    `shard: ${CONFIG.SHARD_INDEX}/${CONFIG.SHARD_COUNT}`,
    `window: ${ctx.windowInfo.bucketStart} ~ ${ctx.windowInfo.bucketEnd}`,
    `examined: ${ctx.examined}`,
    `written: ${ctx.written}`,
    `run_id: ${ctx.runId}`,
    `time: ${formatDateTime(new Date(), ctx.tz)}`,
    `sheet: ${CONFIG.TIMELINE_SHEET}`,
  ].join('\n');
}

function maybeSendFeishu(text, shouldSend) {
  if (!CONFIG.ENABLE_FEISHU || !shouldSend) return;

  const prop = PropertiesService.getScriptProperties();
  const webhook =
    prop.getProperty('RAC_FEISHU_WEBHOOK_URL') ||
    prop.getProperty('FEISHU_WEBHOOK_URL') ||
    CONFIG.FEISHU_WEBHOOK_URL;
  const token =
    prop.getProperty('RAC_FEISHU_TOKEN') ||
    prop.getProperty('FEISHU_TOKEN') ||
    CONFIG.FEISHU_TOKEN;

  if (!webhook) return;

  const payload = {
    msg_type: 'text',
    content: { text: text },
  };

  const headers = { 'Content-Type': 'application/json' };
  if (token) headers.Authorization = 'Bearer ' + token;

  UrlFetchApp.fetch(webhook, {
    method: 'post',
    headers: headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  });
}

function getOrCreateSheet(ss, name) {
  let sh = ss.getSheetByName(name);
  if (!sh) sh = ss.insertSheet(name);
  return sh;
}

function ensureSheetSize(sh, minRows, minCols) {
  if (sh.getMaxRows() < minRows) {
    sh.insertRowsAfter(sh.getMaxRows(), minRows - sh.getMaxRows());
  }
  if (sh.getMaxColumns() < minCols) {
    sh.insertColumnsAfter(sh.getMaxColumns(), minCols - sh.getMaxColumns());
  }
}

function elapsedMinutes(startedAt) {
  return (new Date().getTime() - startedAt.getTime()) / 60000;
}

function formatDateTime(date, tz) {
  return Utilities.formatDate(date, tz, 'yyyy-MM-dd HH:mm:ss');
}

function formatHourBucket(date, tz) {
  return Utilities.formatDate(date, tz, 'yyyy-MM-dd HH');
}

function pad2(value) {
  const n = Number(value);
  return n < 10 ? `0${String(n)}` : String(n);
}

function microsToCurrency(value) {
  return toNumber(value) / 1000000;
}

function parseCriterionId(resourceName) {
  const match = safeString(resourceName).match(/~(\d+)$/);
  return match ? match[1] : '';
}

function safeString(value) {
  return value === null || value === undefined ? '' : String(value);
}

function toNumber(value) {
  if (value === null || value === undefined || value === '') return 0;
  const n = Number(value);
  return isNaN(n) ? 0 : n;
}

function escapeGaqlString(value) {
  return safeString(value).replace(/\\/g, '\\\\').replace(/'/g, "\\'");
}

本文档为站内渲染。原始文件本地路径：saas/source/knowledge-world/Knowledge-World-项目-Practice-Google-Ads-ads-data-script-click-7aae78.txt（仅本地保留，不入库不部署）