const CONFIG = {
SPREADSHEET_URL:
'https://docs.google.com/spreadsheets/d/1huwFNmDxymzX3wi9bMMWBXL5nsBvWg8WXCb9pYas3rE/edit',
MAX_RUNTIME_MINUTES: 24,
RESUME_KEY: 'ADS_EXPORT_NEXT_JOB_INDEX',
LOG_SHEET: '00_RUN_LOG',
STATUS_SHEET: '00_STATUS',
FRESHNESS_SHEET: '00_DATA_FRESHNESS',
KEYWORD_QUALITY_SHEET: '07_keyword_quality_7d',
KEYWORD_QUALITY_SHARD_STATS_SHEET: '07_keyword_quality_shard_stats',
ZERO_IMPR_SAMPLE_SHEET: '08_keyword_zero_impr_7d_sample',
NEG_ADGROUP_SAMPLE_SHEET: '11_neg_keywords_adgroup_sample',
KEYWORD_INVENTORY_SHEET: '07_keyword_inventory_all',
POLICY_TOPIC_SHEET: '20_policy_topic_details',
POLICY_BATCH_ACTION_SHEET: '21_policy_batch_actions',
QUICK_VIEW_SHEET: '00_QUICK_VIEW',
DIAG_SHEET: '90_DIAG_SUMMARY',
TOP_CAMPAIGNS_SHEET: '91_top_campaigns',
AUTO_DIAG_SHEET: '92_auto_diag_actions',
CPC_DAILY_DASHBOARD_SHEET: '93_cpc_daily_dashboard',
CPC_MAPPING_AUDIT_SHEET: '94_cpc_mapping_audit',
BASE_ROWS: 60,
BASE_COLS: 8,
ZERO_IMPR_MAX_ROWS: 15000,
NEG_ADGROUP_MAX_ROWS: 20000,
KEYWORD_INVENTORY_MAX_ROWS: 100000,
KEYWORD_QUALITY_SHARD_MAX_ROWS: 180000,
KEYWORD_QUALITY_SHARD_CAMPAIGN_BATCH: 60,
QUICK_VIEW_MAX_ROWS: 100000,
AUTO_DIAG_MAX_ROWS: 6000,
WORKBOOK_CELL_SOFT_LIMIT: 9000000,
JOB_START_HEADROOM_MINUTES: 4,
POST_STEP_HEADROOM_MINUTES: 2,
LOOP_HEADROOM_MINUTES: 1,
FORCE_FULL_REFRESH: false,
DROP_UNMANAGED_SHEETS: true,
CLEAR_RUN_LOG_EACH_RUN: true,
NEGATIVE_CANDIDATE_TOKENS: [
'free',
'cheap',
'torrent',
'apk',
'mod',
'crack',
'pirate',
'reddit',
'wiki',
'meaning',
'definition',
'jobs',
'salary',
'course',
'template',
'pdf',
'download',
],
};
function main() {
const ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
const account = AdsApp.currentAccount();
const tz = account.getTimeZone();
const dateWindow = getDateWindow(tz);
const start = new Date();
const runId = Utilities.formatDate(start, tz, 'yyyyMMdd-HHmmss');
const jobs = buildJobs(dateWindow);
const props = PropertiesService.getScriptProperties();
if (CONFIG.FORCE_FULL_REFRESH) {
props.deleteProperty(CONFIG.RESUME_KEY);
}
let nextIdx = parseInt(props.getProperty(CONFIG.RESUME_KEY) || '0', 10);
if (isNaN(nextIdx) || nextIdx < 0 || nextIdx > jobs.length) nextIdx = 0;
ensureLogHeader(ss);
if (nextIdx === 0) {
hardResetWorkbook(ss, jobs);
}
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
nextIdx,
jobs.length,
'RUNNING',
''
);
for (let i = nextIdx; i < jobs.length; i++) {
if (isNearTimeout(start, CONFIG.JOB_START_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
i,
jobs.length,
jobs[i].tab,
'near timeout before starting next heavy job',
jobs[i].query
);
return;
}
const cells = getWorkbookCellCount(ss);
if (cells > CONFIG.WORKBOOK_CELL_SOFT_LIMIT) {
props.setProperty(CONFIG.RESUME_KEY, String(i));
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'PAUSED_CELL_GUARD',
jobs[i].tab,
0,
0,
`workbook cells near limit: ${cells}`,
truncate(jobs[i].query, 500),
]);
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
i,
jobs.length,
'PAUSED_CELL_GUARD',
`cells=${cells}`
);
return;
}
runExportJob(ss, account, tz, runId, jobs[i]);
props.setProperty(CONFIG.RESUME_KEY, String(i + 1));
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
i + 1,
jobs.length,
'RUNNING',
''
);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
'POST_STEPS',
'near timeout before post-step exports',
''
);
return;
}
try {
const rows = exportZeroImprKeywordSample(ss, dateWindow, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
rows,
0,
'',
'AdsApp.search zero impression keyword sample',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search zero impression keyword sample',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
'near timeout before negative keyword sample',
''
);
return;
}
try {
const rows = exportNegAdgroupKeywordSample(ss, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
rows,
0,
'',
'AdsApp.search negative adgroup keyword sample',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search negative adgroup keyword sample',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.KEYWORD_INVENTORY_SHEET,
'near timeout before keyword inventory snapshot',
''
);
return;
}
try {
const rows = exportKeywordInventorySnapshot(ss, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.KEYWORD_INVENTORY_SHEET,
rows,
0,
'',
'AdsApp.search keyword inventory snapshot',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.KEYWORD_INVENTORY_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search keyword inventory snapshot',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.KEYWORD_QUALITY_SHEET,
'near timeout before keyword quality sharded export',
''
);
return;
}
try {
const qualityRes = exportKeywordQualitySharded(ss, dateWindow, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.KEYWORD_QUALITY_SHEET,
qualityRes.rows,
0,
`countries=${qualityRes.countries}; shards=${qualityRes.shards}`,
'AdsApp.search keyword quality sharded',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.KEYWORD_QUALITY_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search keyword quality sharded',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.POLICY_TOPIC_SHEET,
'near timeout before policy topic export',
''
);
return;
}
try {
const topicRows = exportPolicyTopicDetails(ss, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.POLICY_TOPIC_SHEET,
topicRows,
0,
'',
'AdsApp.search policy details',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.POLICY_TOPIC_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search policy details',
]);
}
try {
buildDiagnosis(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.DIAG_SHEET,
0,
0,
truncate(String(e), 1000),
'buildDiagnosis',
]);
}
try {
buildTopCampaigns(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.TOP_CAMPAIGNS_SHEET,
0,
0,
truncate(String(e), 1000),
'buildTopCampaigns',
]);
}
try {
buildQuickView(ss, runId, dateWindow);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.QUICK_VIEW_SHEET,
0,
0,
truncate(String(e), 1000),
'buildQuickView',
]);
}
try {
buildAutoDiagnosisActions(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.AUTO_DIAG_SHEET,
0,
0,
truncate(String(e), 1000),
'buildAutoDiagnosisActions',
]);
}
try {
buildPolicyBatchActions(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.POLICY_BATCH_ACTION_SHEET,
0,
0,
truncate(String(e), 1000),
'buildPolicyBatchActions',
]);
}
try {
buildCpcDailyDashboard(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.CPC_DAILY_DASHBOARD_SHEET,
0,
0,
truncate(String(e), 1000),
'buildCpcDailyDashboard',
]);
}
try {
buildCpcMappingAudit(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.CPC_MAPPING_AUDIT_SHEET,
0,
0,
truncate(String(e), 1000),
'buildCpcMappingAudit',
]);
}
try {
writeDataFreshness(ss, account, tz, runId, dateWindow);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.FRESHNESS_SHEET,
0,
0,
truncate(String(e), 1000),
'writeDataFreshness',
]);
}
try {
enforceSheetOrder(ss, [CONFIG.QUICK_VIEW_SHEET]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.QUICK_VIEW_SHEET,
0,
0,
truncate(String(e), 1000),
'enforceSheetOrder',
]);
}
props.deleteProperty(CONFIG.RESUME_KEY);
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
jobs.length,
jobs.length,
'FINISHED',
'all jobs finished'
);
}
function buildJobs(dateWindow) {
return [
{
tab: '01_customer',
minCols: 5,
query: `
SELECT
customer.id,
customer.descriptive_name,
customer.currency_code,
customer.time_zone,
customer.status
FROM customer
`,
},
{
tab: '02_campaign_metrics_30d',
minCols: 17,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
campaign.serving_status,
campaign.advertising_channel_type,
campaign.bidding_strategy_type,
campaign_budget.amount_micros,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value,
metrics.search_impression_share,
metrics.search_rank_lost_impression_share,
metrics.search_budget_lost_impression_share
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
`,
},
{
tab: '03_campaign_daily_30d',
minCols: 11,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
segments.date,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
`,
},
{
tab: '04_ad_group_metrics_30d',
minCols: 14,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group.status,
ad_group.type,
ad_group.cpc_bid_micros,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value
FROM ad_group
WHERE ad_group.status != REMOVED
AND segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
`,
},
{
tab: '05_ad_policy_status',
minCols: 10,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_ad.ad.id,
ad_group_ad.status,
ad_group_ad.policy_summary.approval_status,
ad_group_ad.policy_summary.review_status,
ad_group_ad.ad.type,
ad_group_ad.ad.final_urls
FROM ad_group_ad
WHERE ad_group_ad.status != REMOVED
`,
},
{
tab: '06_asset_text_performance',
minCols: 9,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_ad.ad.id,
ad_group_ad_asset_view.field_type,
ad_group_ad_asset_view.performance_label,
asset.id,
asset.text_asset.text
FROM ad_group_ad_asset_view
`,
},
{
tab: '09_search_terms_7d',
minCols: 13,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
search_term_view.search_term,
segments.date,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value
FROM search_term_view
WHERE segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
AND metrics.impressions > 0
LIMIT 100000
`,
},
{
tab: '10_neg_keywords_campaign',
minCols: 6,
query: `
SELECT
campaign.id,
campaign.name,
campaign_criterion.criterion_id,
campaign_criterion.status,
campaign_criterion.keyword.text,
campaign_criterion.keyword.match_type
FROM campaign_criterion
WHERE campaign_criterion.negative = TRUE
AND campaign_criterion.type = KEYWORD
`,
},
{
tab: '12_campaign_today',
minCols: 9,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date DURING TODAY
`,
},
{
tab: '13_campaign_yesterday',
minCols: 9,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date DURING YESTERDAY
`,
},
];
}
function runExportJob(ss, account, tz, runId, job) {
const t0 = new Date();
try {
const sh = prepareTargetSheet(ss, job.tab, job.minCols || CONFIG.BASE_COLS);
AdsApp.report(job.query).exportToSheet(sh);
const rows = Math.max(sh.getLastRow() - 1, 0);
trimSheetToContent(sh, job.minCols || CONFIG.BASE_COLS);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
job.tab,
rows,
secondsBetween(t0, new Date()),
'',
truncate(job.query, 500),
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
job.tab,
0,
secondsBetween(t0, new Date()),
truncate(String(e), 1000),
truncate(job.query, 500),
]);
}
}
function exportZeroImprKeywordSample(ss, dateWindow, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.ZERO_IMPR_SAMPLE_SHEET, 9);
sh.getRange(1, 1, 1, 9).setValues([[
'campaign.id',
'campaign.name',
'ad_group.id',
'ad_group.name',
'ad_group_criterion.criterion_id',
'ad_group_criterion.keyword.text',
'ad_group_criterion.keyword.match_type',
'ad_group_criterion.cpc_bid_micros',
'metrics.impressions',
]]);
const q = `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_criterion.criterion_id,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type,
ad_group_criterion.cpc_bid_micros,
metrics.impressions
FROM keyword_view
WHERE ad_group_criterion.status = ENABLED
AND segments.date BETWEEN '${dateWindow.last7Start}' AND '${dateWindow.today}'
AND metrics.impressions = 0
LIMIT ${CONFIG.ZERO_IMPR_MAX_ROWS}
`;
const it = AdsApp.search(q);
const outRows = [];
while (it.hasNext() && outRows.length < CONFIG.ZERO_IMPR_MAX_ROWS) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const x = it.next();
outRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
toNumber(x.adGroupCriterion && x.adGroupCriterion.cpcBidMicros),
toNumber(x.metrics && x.metrics.impressions),
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 9).setValues(outRows);
}
trimSheetToContent(sh, 9);
return outRows.length;
}
function exportNegAdgroupKeywordSample(ss, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.NEG_ADGROUP_SAMPLE_SHEET, 8);
sh.getRange(1, 1, 1, 8).setValues([[
'campaign.id',
'campaign.name',
'ad_group.id',
'ad_group.name',
'ad_group_criterion.criterion_id',
'ad_group_criterion.status',
'ad_group_criterion.keyword.text',
'ad_group_criterion.keyword.match_type',
]]);
const q = `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_criterion.criterion_id,
ad_group_criterion.status,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type
FROM ad_group_criterion
WHERE ad_group_criterion.negative = TRUE
AND ad_group_criterion.type = KEYWORD
LIMIT ${CONFIG.NEG_ADGROUP_MAX_ROWS}
`;
const it = AdsApp.search(q);
const outRows = [];
while (it.hasNext() && outRows.length < CONFIG.NEG_ADGROUP_MAX_ROWS) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const x = it.next();
outRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(x.adGroupCriterion && x.adGroupCriterion.status),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 8).setValues(outRows);
}
trimSheetToContent(sh, 8);
return outRows.length;
}
function exportKeywordInventorySnapshot(ss, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.KEYWORD_INVENTORY_SHEET, 14);
sh.getRange(1, 1, 1, 14).setValues([[
'campaign.id',
'campaign.name',
'ad_group.id',
'ad_group.name',
'ad_group_criterion.criterion_id',
'ad_group_criterion.status',
'ad_group_criterion.keyword.text',
'ad_group_criterion.keyword.match_type',
'ad_group_criterion.cpc_bid_micros',
'ad_group_criterion.quality_info.quality_score',
'ad_group_criterion.quality_info.creative_quality_score',
'ad_group_criterion.quality_info.post_click_quality_score',
'ad_group_criterion.quality_info.search_predicted_ctr',
'ad_group_criterion.final_urls',
]]);
const q = `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_criterion.criterion_id,
ad_group_criterion.status,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type,
ad_group_criterion.cpc_bid_micros,
ad_group_criterion.quality_info.quality_score,
ad_group_criterion.quality_info.creative_quality_score,
ad_group_criterion.quality_info.post_click_quality_score,
ad_group_criterion.quality_info.search_predicted_ctr,
ad_group_criterion.final_urls
FROM ad_group_criterion
WHERE ad_group_criterion.type = KEYWORD
AND ad_group_criterion.negative = FALSE
AND ad_group_criterion.status != REMOVED
LIMIT ${CONFIG.KEYWORD_INVENTORY_MAX_ROWS}
`;
const it = AdsApp.search(q);
const outRows = [];
while (it.hasNext() && outRows.length < CONFIG.KEYWORD_INVENTORY_MAX_ROWS) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const x = it.next();
outRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(x.adGroupCriterion && x.adGroupCriterion.status),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
toNumber(x.adGroupCriterion && x.adGroupCriterion.cpcBidMicros),
toNumber(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.qualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.creativeQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.postClickQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.searchPredictedCtr
),
safeJson(x.adGroupCriterion && x.adGroupCriterion.finalUrls),
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 14).setValues(outRows);
}
trimSheetToContent(sh, 14);
return outRows.length;
}
function exportKeywordQualitySharded(ss, dateWindow, startDate) {
const out = prepareTargetSheet(ss, CONFIG.KEYWORD_QUALITY_SHEET, 20);
out.getRange(1, 1, 1, 20).setValues([[
'campaign.id',
'campaign.name',
'campaign.country_code',
'ad_group.id',
'ad_group.name',
'ad_group_criterion.criterion_id',
'ad_group_criterion.status',
'ad_group_criterion.keyword.text',
'ad_group_criterion.keyword.match_type',
'ad_group_criterion.cpc_bid_micros',
'ad_group_criterion.quality_info.quality_score',
'ad_group_criterion.quality_info.creative_quality_score',
'ad_group_criterion.quality_info.post_click_quality_score',
'ad_group_criterion.quality_info.search_predicted_ctr',
'metrics.impressions',
'metrics.clicks',
'metrics.ctr',
'metrics.average_cpc',
'metrics.cost_micros',
'metrics.conversions',
]]);
const shardStats = prepareTargetSheet(ss, CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET, 9);
shardStats.getRange(1, 1, 1, 9).setValues([[
'country_code',
'shard_index',
'campaign_count',
'campaign_ids',
'rows_exported',
'status',
'error_or_note',
'window_from',
'window_to',
]]);
const campaignMeta = loadCampaignShardMeta(ss);
const byCountry = {};
for (let i = 0; i < campaignMeta.length; i++) {
const x = campaignMeta[i];
const country = extractCountryFromCampaignName(x.name);
if (!byCountry[country]) byCountry[country] = [];
byCountry[country].push(x);
}
const countries = Object.keys(byCountry).sort(function(a, b) {
return byCountry[b].length - byCountry[a].length;
});
let writeAt = 2;
let shardWriteAt = 2;
let totalRows = 0;
let shardCount = 0;
let hitCap = false;
const maxRows = CONFIG.KEYWORD_QUALITY_SHARD_MAX_ROWS;
const batchSize = Math.max(1, CONFIG.KEYWORD_QUALITY_SHARD_CAMPAIGN_BATCH);
for (let i = 0; i < countries.length; i++) {
const country = countries[i];
const group = byCountry[country];
const chunks = chunkList(group, batchSize);
for (let c = 0; c < chunks.length; c++) {
shardCount++;
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) {
shardStats.getRange(shardWriteAt, 1, 1, 9).setValues([[
country,
shardCount,
chunks[c].length,
chunks[c].map(function(v) { return v.id; }).join('|'),
0,
'TIMEOUT_GUARD',
'stopped before shard execution due to runtime headroom',
dateWindow.last7Start,
dateWindow.today,
]]);
shardWriteAt++;
trimSheetToContent(out, 20);
trimSheetToContent(shardStats, 9);
return {
rows: totalRows,
shards: shardCount,
countries: countries.length,
};
}
const ids = [];
for (let m = 0; m < chunks[c].length; m++) {
const id = normalizeGaqlId(chunks[c][m].id);
if (id) ids.push(id);
}
if (ids.length === 0) continue;
const q = `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_criterion.criterion_id,
ad_group_criterion.status,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type,
ad_group_criterion.cpc_bid_micros,
ad_group_criterion.quality_info.quality_score,
ad_group_criterion.quality_info.creative_quality_score,
ad_group_criterion.quality_info.post_click_quality_score,
ad_group_criterion.quality_info.search_predicted_ctr,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions
FROM keyword_view
WHERE ad_group_criterion.status != REMOVED
AND segments.date BETWEEN '${dateWindow.last7Start}' AND '${dateWindow.today}'
AND campaign.id IN (${ids.join(',')})
`;
const localRows = [];
let status = 'OK';
let note = '';
try {
const it = AdsApp.search(q);
while (it.hasNext()) {
if (totalRows >= maxRows) {
hitCap = true;
break;
}
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) {
status = 'TIMEOUT_GUARD';
note = 'stopped while reading shard due to runtime headroom';
break;
}
const x = it.next();
localRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
extractCountryFromCampaignName(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(x.adGroupCriterion && x.adGroupCriterion.status),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
toNumber(x.adGroupCriterion && x.adGroupCriterion.cpcBidMicros),
toNumber(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.qualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.creativeQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.postClickQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.searchPredictedCtr
),
toNumber(x.metrics && x.metrics.impressions),
toNumber(x.metrics && x.metrics.clicks),
toNumber(x.metrics && x.metrics.ctr),
toNumber(x.metrics && x.metrics.averageCpc),
toNumber(x.metrics && x.metrics.costMicros),
toNumber(x.metrics && x.metrics.conversions),
]);
totalRows++;
}
} catch (e) {
status = 'ERROR';
note = truncate(String(e), 500);
}
if (localRows.length > 0) {
out.getRange(writeAt, 1, localRows.length, 20).setValues(localRows);
writeAt += localRows.length;
}
if (hitCap && status === 'OK') {
status = 'CAP_REACHED';
note = `stop at max rows=${maxRows}`;
}
shardStats.getRange(shardWriteAt, 1, 1, 9).setValues([[
country,
shardCount,
ids.length,
ids.join('|'),
localRows.length,
status,
note,
dateWindow.last7Start,
dateWindow.today,
]]);
shardWriteAt++;
if (hitCap) break;
if (status === 'TIMEOUT_GUARD') {
trimSheetToContent(out, 20);
trimSheetToContent(shardStats, 9);
return {
rows: totalRows,
shards: shardCount,
countries: countries.length,
};
}
}
if (hitCap) break;
}
trimSheetToContent(out, 20);
trimSheetToContent(shardStats, 9);
return {
rows: totalRows,
shards: shardCount,
countries: countries.length,
};
}
function loadCampaignShardMeta(ss) {
const sh = ss.getSheetByName('02_campaign_metrics_30d');
if (!sh || sh.getLastRow() < 2) return [];
const values = sh.getDataRange().getValues();
const h = values[0];
const iId = findCol(h, 'campaign.id');
const iName = findCol(h, 'campaign.name');
const seen = {};
const out = [];
for (let i = 1; i < values.length; i++) {
const id = normalizeGaqlId(values[i][iId]);
if (!id || seen[id]) continue;
seen[id] = true;
out.push({
id: id,
name: safeString(values[i][iName]),
});
}
return out;
}
function exportPolicyTopicDetails(ss, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.POLICY_TOPIC_SHEET, 12);
sh.getRange(1, 1, 1, 12).setValues([[
'source',
'campaign_name',
'ad_group_name',
'ad_id',
'approval_status',
'review_status',
'topic',
'type',
'evidence_json',
'notes',
'captured_at',
'resource',
]]);
const outRows = [];
const ts = Utilities.formatDate(
new Date(),
AdsApp.currentAccount().getTimeZone(),
'yyyy-MM-dd HH:mm:ss'
);
const adQuery = `
SELECT
campaign.name,
ad_group.name,
ad_group_ad.ad.id,
ad_group_ad.policy_summary.approval_status,
ad_group_ad.policy_summary.review_status
FROM ad_group_ad
WHERE ad_group_ad.status != REMOVED
AND ad_group_ad.policy_summary.approval_status != APPROVED
`;
const adIt = AdsApp.search(adQuery);
while (adIt.hasNext()) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const row = adIt.next();
const ps = row.adGroupAd && row.adGroupAd.policySummary;
const entries = (ps && ps.policyTopicEntries) ? ps.policyTopicEntries : [];
if (!entries || entries.length === 0) {
outRows.push([
'AD',
row.campaign ? row.campaign.name : '',
row.adGroup ? row.adGroup.name : '',
row.adGroupAd && row.adGroupAd.ad ? row.adGroupAd.ad.id : '',
ps ? safeString(ps.approvalStatus) : '',
ps ? safeString(ps.reviewStatus) : '',
'',
'',
'',
'no policyTopicEntries on ad object',
ts,
'ad_group_ad',
]);
continue;
}
for (let i = 0; i < entries.length; i++) {
const e = entries[i];
outRows.push([
'AD',
row.campaign ? row.campaign.name : '',
row.adGroup ? row.adGroup.name : '',
row.adGroupAd && row.adGroupAd.ad ? row.adGroupAd.ad.id : '',
ps ? safeString(ps.approvalStatus) : '',
ps ? safeString(ps.reviewStatus) : '',
safeString(e.topic),
safeString(e.type),
safeJson(e.evidences || []),
'',
ts,
'ad_group_ad',
]);
}
}
try {
const assetQuery = `
SELECT
asset.id,
asset.name,
asset.type
FROM asset
WHERE asset.status != REMOVED
LIMIT 1
`;
AdsApp.search(assetQuery);
outRows.push([
'ASSET',
'',
'',
'',
'',
'',
'',
'',
'',
'asset-level policy topic entries not reliably exposed in Ads Scripts report/search for this account version',
ts,
'asset',
]);
} catch (e) {
outRows.push([
'ASSET',
'',
'',
'',
'',
'',
'',
'',
'',
'asset policy details query not supported: ' + truncate(String(e), 300),
ts,
'asset',
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 12).setValues(outRows);
}
trimSheetToContent(sh, 12);
return outRows.length;
}
function buildDiagnosis(ss) {
const out = prepareTargetSheet(ss, CONFIG.DIAG_SHEET, 3);
out.getRange(1, 1, 1, 3).setValues([['metric', 'value', 'note']]);
const c = ss.getSheetByName('02_campaign_metrics_30d');
if (!c || c.getLastRow() < 2) {
out.appendRow(['error', 'missing 02_campaign_metrics_30d', 'rerun script']);
trimSheetToContent(out, 3);
return;
}
const vals = c.getDataRange().getValues();
const h = vals[0];
const iStatus = findCol(h, 'campaign.status');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
const iConv = findCol(h, 'metrics.conversions');
const iConvVal = findCol(h, 'metrics.conversions_value');
const iRankLost = findCol(h, 'metrics.search_rank_lost_impression_share');
const iBudgetLost = findCol(h, 'metrics.search_budget_lost_impression_share');
let total = 0;
let enabled = 0;
let enabledZeroImpr = 0;
let impr = 0;
let clicks = 0;
let cost = 0;
let conv = 0;
let convValue = 0;
let highRankLost = 0;
let highBudgetLost = 0;
for (let r = 1; r < vals.length; r++) {
const row = vals[r];
const status = safeString(row[iStatus]);
const vImpr = toNumber(row[iImpr]);
const vClicks = toNumber(row[iClicks]);
const vCost = toNumber(row[iCost]);
const vConv = toNumber(row[iConv]);
const vConvVal = toNumber(row[iConvVal]);
const vRankLost = toNumber(row[iRankLost]);
const vBudgetLost = toNumber(row[iBudgetLost]);
total++;
impr += vImpr;
clicks += vClicks;
cost += vCost;
conv += vConv;
convValue += vConvVal;
if (status === 'ENABLED') {
enabled++;
if (vImpr === 0) enabledZeroImpr++;
}
if (vImpr >= 100 && vRankLost >= 0.5) highRankLost++;
if (vImpr >= 100 && vBudgetLost >= 0.3) highBudgetLost++;
}
let disapprovedAds = 0;
let approvedLimitedAds = 0;
let adCount = 0;
let campaignWithAds = 0;
let adGroupWithAds = 0;
const adPol = ss.getSheetByName('05_ad_policy_status');
if (adPol && adPol.getLastRow() >= 2) {
const p = adPol.getDataRange().getValues();
const ph = p[0];
const iA = findCol(ph, 'ad_group_ad.policy_summary.approval_status');
const iCampaignId = findCol(ph, 'campaign.id');
const iAdGroupId = findCol(ph, 'ad_group.id');
const campaignSet = new Set();
const adGroupSet = new Set();
for (let i = 1; i < p.length; i++) {
const approval = safeString(p[i][iA]);
adCount++;
if (approval === 'DISAPPROVED') disapprovedAds++;
if (approval === 'APPROVED_LIMITED') approvedLimitedAds++;
campaignSet.add(safeString(p[i][iCampaignId]));
adGroupSet.add(safeString(p[i][iAdGroupId]));
}
campaignWithAds = campaignSet.has('') ? campaignSet.size - 1 : campaignSet.size;
adGroupWithAds = adGroupSet.has('') ? adGroupSet.size - 1 : adGroupSet.size;
}
let adGroupTotal = 0;
const ag = ss.getSheetByName('04_ad_group_metrics_30d');
if (ag && ag.getLastRow() >= 2) {
const a = ag.getDataRange().getValues();
const ah = a[0];
const iAgId = findCol(ah, 'ad_group.id');
const adGroupSet = new Set();
for (let i = 1; i < a.length; i++) {
adGroupSet.add(safeString(a[i][iAgId]));
}
adGroupTotal = adGroupSet.has('') ? adGroupSet.size - 1 : adGroupSet.size;
}
let runErrors = 0;
const log = ss.getSheetByName(CONFIG.LOG_SHEET);
if (log && log.getLastRow() >= 2) {
const l = log.getDataRange().getValues();
const lh = l[0];
const iS = findCol(lh, 'status');
for (let i = 1; i < l.length; i++) {
if (safeString(l[i][iS]) === 'ERROR') runErrors++;
}
}
const ctr = impr > 0 ? clicks / impr : 0;
const avgCpc = clicks > 0 ? cost / 1000000 / clicks : 0;
const cpa = conv > 0 ? cost / 1000000 / conv : 0;
const roas = cost > 0 ? convValue / (cost / 1000000) : 0;
const campaignWithAdsRate = total > 0 ? campaignWithAds / total : 0;
const adGroupWithAdsRate = adGroupTotal > 0 ? adGroupWithAds / adGroupTotal : 0;
const approvedLimitedRate = adCount > 0 ? approvedLimitedAds / adCount : 0;
const disapprovedRate = adCount > 0 ? disapprovedAds / adCount : 0;
const range = getCampaignDailyDateRange(ss);
const keywordQualityRows = getSheetDataRowCount(ss, '07_keyword_quality_7d');
const searchTermRows = getSheetDataRowCount(ss, '09_search_terms_7d');
const keywordInventoryRows = getSheetDataRowCount(ss, CONFIG.KEYWORD_INVENTORY_SHEET);
out.appendRow(['campaign_total', total, 'campaign count in 30d table']);
out.appendRow(['campaign_enabled', enabled, 'enabled campaign count']);
out.appendRow(['campaign_with_ads', campaignWithAds, 'campaigns having at least one ad in 05_ad_policy_status']);
out.appendRow(['campaign_with_ads_rate', campaignWithAdsRate.toFixed(4), 'campaign_with_ads / campaign_total']);
out.appendRow(['adgroup_total', adGroupTotal, 'ad group count in 04_ad_group_metrics_30d']);
out.appendRow(['adgroup_with_ads', adGroupWithAds, 'ad groups having at least one ad in 05_ad_policy_status']);
out.appendRow(['adgroup_with_ads_rate', adGroupWithAdsRate.toFixed(4), 'adgroup_with_ads / adgroup_total']);
out.appendRow(['ads_total', adCount, 'ad count in 05_ad_policy_status']);
out.appendRow(['enabled_zero_impr_30d', enabledZeroImpr, 'enabled campaigns with zero impressions']);
out.appendRow(['enabled_zero_impr_rate', enabled > 0 ? (enabledZeroImpr / enabled).toFixed(4) : '0', 'zero impression rate']);
out.appendRow(['impressions_30d', Math.round(impr), 'sum metrics.impressions']);
out.appendRow(['clicks_30d', Math.round(clicks), 'sum metrics.clicks']);
out.appendRow(['ctr_30d', ctr.toFixed(4), 'clicks / impressions']);
out.appendRow(['cost_30d', (cost / 1000000).toFixed(2), 'sum cost (account currency)']);
out.appendRow(['avg_cpc_30d', avgCpc.toFixed(4), 'cost / clicks']);
out.appendRow(['conversions_30d', conv.toFixed(2), 'sum conversions']);
out.appendRow(['conv_value_30d', convValue.toFixed(2), 'sum conversion value']);
out.appendRow(['cpa_30d', cpa.toFixed(4), 'cost / conversions']);
out.appendRow(['roas_30d', roas.toFixed(4), 'conversion value / cost']);
out.appendRow(['disapproved_ads', disapprovedAds, 'from 05_ad_policy_status']);
out.appendRow(['disapproved_ads_rate', disapprovedRate.toFixed(4), 'disapproved_ads / ads_total']);
out.appendRow(['approved_limited_ads', approvedLimitedAds, 'from 05_ad_policy_status']);
out.appendRow(['approved_limited_ads_rate', approvedLimitedRate.toFixed(4), 'approved_limited_ads / ads_total']);
out.appendRow(['jobs_error_count', runErrors, 'ERROR count in 00_RUN_LOG']);
out.appendRow(['keyword_quality_rows', keywordQualityRows, 'row count of 07_keyword_quality_7d']);
out.appendRow(['search_term_rows', searchTermRows, 'row count of 09_search_terms_7d']);
out.appendRow(['keyword_inventory_rows', keywordInventoryRows, `row count of ${CONFIG.KEYWORD_INVENTORY_SHEET}`]);
out.appendRow(['daily_data_earliest', range.earliest, 'from 03_campaign_daily_30d']);
out.appendRow(['daily_data_latest', range.latest, 'from 03_campaign_daily_30d']);
out.appendRow(['high_rank_lost_campaigns', highRankLost, 'impr>=100 and rank lost >=0.5']);
out.appendRow(['high_budget_lost_campaigns', highBudgetLost, 'impr>=100 and budget lost >=0.3']);
if (campaignWithAdsRate < 0.1) {
out.appendRow(['action_0', 'increase ad coverage first', 'too many campaigns/ad groups have no ads']);
}
if (disapprovedAds > 0) {
out.appendRow(['action_1', 'fix policy first', 'disapproved ads block serving']);
}
if (approvedLimitedRate > 0.15) {
out.appendRow(['action_1b', 'reduce policy-limited assets', 'approved_limited ratio is high']);
}
if (enabled > 0 && enabledZeroImpr / enabled > 0.5) {
out.appendRow(['action_2', 'consolidate campaigns', 'too many enabled campaigns with zero impression']);
}
if (runErrors > 0) {
out.appendRow(['action_2b', 'rerun and check ERROR tabs', 'missing tables will make diagnosis incomplete']);
}
if (keywordInventoryRows > 0 && keywordQualityRows === 0) {
out.appendRow(['action_2c', 'not a script bug: no keyword traffic', 'keywords exist but no rows in keyword metrics window']);
}
if (keywordInventoryRows === 0) {
out.appendRow(['action_2d', 'no positive keyword inventory found', 'check whether keyword upload has succeeded']);
}
if (searchTermRows === 0) {
out.appendRow(['action_2e', 'search term report empty', 'usually means near-zero serving in current window']);
}
if (highBudgetLost > highRankLost) {
out.appendRow(['action_3', 'raise budget first', 'budget lost is dominant']);
} else if (highRankLost > 0) {
out.appendRow(['action_3', 'raise bids and quality', 'rank lost is dominant']);
}
trimSheetToContent(out, 3);
}
function buildTopCampaigns(ss) {
const source = ss.getSheetByName('02_campaign_metrics_30d');
const out = prepareTargetSheet(ss, CONFIG.TOP_CAMPAIGNS_SHEET, 13);
out.getRange(1, 1, 1, 13).setValues([[
'rank',
'campaign_id',
'campaign_name',
'status',
'impressions_30d',
'clicks_30d',
'ctr_30d',
'cost_30d',
'avg_cpc_30d',
'conversions_30d',
'conv_value_30d',
'cpa_30d',
'roas_30d',
]]);
if (!source || source.getLastRow() < 2) {
out.appendRow(['1', '', 'no source data', '', 0, 0, 0, 0, 0, 0, 0, 0, 0]);
trimSheetToContent(out, 13);
return;
}
const vals = source.getDataRange().getValues();
const h = vals[0];
const iId = findCol(h, 'campaign.id');
const iName = findCol(h, 'campaign.name');
const iStatus = findCol(h, 'campaign.status');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
const iConv = findCol(h, 'metrics.conversions');
const iConvVal = findCol(h, 'metrics.conversions_value');
const rows = [];
for (let r = 1; r < vals.length; r++) {
const row = vals[r];
const impr = toNumber(row[iImpr]);
const clicks = toNumber(row[iClicks]);
const costMicros = toNumber(row[iCost]);
const conv = toNumber(row[iConv]);
const convVal = toNumber(row[iConvVal]);
const cost = costMicros / 1000000;
const ctr = impr > 0 ? clicks / impr : 0;
const cpa = conv > 0 ? cost / conv : 0;
const avgCpc = clicks > 0 ? cost / clicks : 0;
const roas = cost > 0 ? convVal / cost : 0;
rows.push({
id: safeString(row[iId]),
name: safeString(row[iName]),
status: safeString(row[iStatus]),
impr: impr,
clicks: clicks,
ctr: ctr,
cost: cost,
avgCpc: avgCpc,
conv: conv,
convVal: convVal,
cpa: cpa,
roas: roas,
});
}
rows.sort(function(a, b) {
if (b.conv !== a.conv) return b.conv - a.conv;
if (b.convVal !== a.convVal) return b.convVal - a.convVal;
if (b.clicks !== a.clicks) return b.clicks - a.clicks;
return b.impr - a.impr;
});
const topN = Math.min(rows.length, 100);
const outRows = [];
for (let i = 0; i < topN; i++) {
const x = rows[i];
outRows.push([
i + 1,
x.id,
x.name,
x.status,
Math.round(x.impr),
Math.round(x.clicks),
x.ctr,
x.cost,
x.avgCpc,
x.conv,
x.convVal,
x.cpa,
x.roas,
]);
}
if (outRows.length > 0) {
out.getRange(2, 1, outRows.length, 13).setValues(outRows);
}
trimSheetToContent(out, 13);
}
function buildQuickView(ss, runId, dateWindow) {
const out = prepareTargetSheet(ss, CONFIG.QUICK_VIEW_SHEET, 25);
out.getRange(1, 1, 1, 25).setValues([[
'run_id',
'date_window_from',
'date_window_to',
'segments.date',
'campaign.id',
'campaign.name',
'ad_group.id',
'ad_group.name',
'ads_in_ad_group',
'ad_ids',
'search_term_view.search_term',
'matched_keyword.text',
'matched_keyword.match_type',
'matched_keyword.max_cpc_usd',
'ad_group.default_cpc_usd',
'metrics.impressions',
'metrics.clicks',
'metrics.ctr',
'metrics.average_cpc_usd',
'metrics.cost_total_usd',
'metrics.cost_per_click_usd',
'metrics.conversions',
'metrics.conversions_value',
'matched_final_url',
'snapshot_exported_at',
]]);
const stSh = ss.getSheetByName('09_search_terms_7d');
const kwSh = ss.getSheetByName(CONFIG.KEYWORD_INVENTORY_SHEET);
const agSh = ss.getSheetByName('04_ad_group_metrics_30d');
const adSh = ss.getSheetByName('05_ad_policy_status');
const freshSh = ss.getSheetByName(CONFIG.FRESHNESS_SHEET);
if (!stSh || stSh.getLastRow() < 2) {
out.appendRow([
runId,
dateWindow.last30Start,
dateWindow.today,
'',
'',
'no data in 09_search_terms_7d',
'',
'',
'',
'',
'',
'',
'',
'',
'',
0,
0,
0,
0,
0,
0,
0,
0,
'',
'',
]);
trimSheetToContent(out, 25);
return;
}
const snapshotAt = readFreshnessValue(freshSh, 'exported_at_account_tz');
const stVals = stSh.getDataRange().getValues();
const stH = stVals[0];
const stIdxCampaignId = findCol(stH, 'campaign.id');
const stIdxCampaignName = findCol(stH, 'campaign.name');
const stIdxAdGroupId = findCol(stH, 'ad_group.id');
const stIdxAdGroupName = findCol(stH, 'ad_group.name');
const stIdxTerm = findCol(stH, 'search_term_view.search_term');
const stIdxDate = stH.indexOf('segments.date');
const stIdxImpr = findCol(stH, 'metrics.impressions');
const stIdxClicks = findCol(stH, 'metrics.clicks');
const stIdxCtr = findCol(stH, 'metrics.ctr');
const stIdxAvgCpc = findCol(stH, 'metrics.average_cpc');
const stIdxCost = findCol(stH, 'metrics.cost_micros');
const stIdxConv = findCol(stH, 'metrics.conversions');
const stIdxConvVal = findCol(stH, 'metrics.conversions_value');
const kwMap = {};
if (kwSh && kwSh.getLastRow() >= 2) {
const kwVals = kwSh.getDataRange().getValues();
const kwH = kwVals[0];
const kwIdxCampaignId = findCol(kwH, 'campaign.id');
const kwIdxAdGroupId = findCol(kwH, 'ad_group.id');
const kwIdxText = findCol(kwH, 'ad_group_criterion.keyword.text');
const kwIdxMatch = findCol(kwH, 'ad_group_criterion.keyword.match_type');
const kwIdxCpc = findCol(kwH, 'ad_group_criterion.cpc_bid_micros');
const kwIdxFinalUrls = findCol(kwH, 'ad_group_criterion.final_urls');
for (let i = 1; i < kwVals.length; i++) {
const row = kwVals[i];
const key = safeString(row[kwIdxCampaignId]) + '|' + safeString(row[kwIdxAdGroupId]);
if (!kwMap[key]) kwMap[key] = [];
kwMap[key].push({
text: safeString(row[kwIdxText]),
matchType: safeString(row[kwIdxMatch]),
cpcMicros: toNumber(row[kwIdxCpc]),
finalUrl: parseFirstFinalUrl(row[kwIdxFinalUrls]),
});
}
}
const agCpcMap = {};
if (agSh && agSh.getLastRow() >= 2) {
const agVals = agSh.getDataRange().getValues();
const agH = agVals[0];
const agIdxCampaignId = findCol(agH, 'campaign.id');
const agIdxAdGroupId = findCol(agH, 'ad_group.id');
const agIdxCpc = findCol(agH, 'ad_group.cpc_bid_micros');
for (let i = 1; i < agVals.length; i++) {
const row = agVals[i];
const key = safeString(row[agIdxCampaignId]) + '|' + safeString(row[agIdxAdGroupId]);
agCpcMap[key] = toNumber(row[agIdxCpc]);
}
}
const adCountMap = {};
const adIdsMap = {};
if (adSh && adSh.getLastRow() >= 2) {
const adVals = adSh.getDataRange().getValues();
const adH = adVals[0];
const adIdxCampaignId = findCol(adH, 'campaign.id');
const adIdxAdGroupId = findCol(adH, 'ad_group.id');
const adIdxAdId = findCol(adH, 'ad_group_ad.ad.id');
const temp = {};
for (let i = 1; i < adVals.length; i++) {
const row = adVals[i];
const key = safeString(row[adIdxCampaignId]) + '|' + safeString(row[adIdxAdGroupId]);
if (!temp[key]) temp[key] = new Set();
temp[key].add(safeString(row[adIdxAdId]));
}
const keys = Object.keys(temp);
for (let i = 0; i < keys.length; i++) {
const arr = Array.from(temp[keys[i]]).filter(function(x) { return x !== ''; });
adCountMap[keys[i]] = arr.length;
adIdsMap[keys[i]] = truncate(arr.slice(0, 20).join('|'), 300);
}
}
const outRows = [];
for (let i = 1; i < stVals.length; i++) {
if (outRows.length >= CONFIG.QUICK_VIEW_MAX_ROWS) break;
const row = stVals[i];
const campaignId = safeString(row[stIdxCampaignId]);
const campaignName = safeString(row[stIdxCampaignName]);
const adGroupId = safeString(row[stIdxAdGroupId]);
const adGroupName = safeString(row[stIdxAdGroupName]);
const searchTerm = safeString(row[stIdxTerm]);
const key = campaignId + '|' + adGroupId;
const kwCandidates = kwMap[key] || [];
const matched = chooseBestKeywordForSearchTerm(searchTerm, kwCandidates);
const clicks = toNumber(row[stIdxClicks]);
const costMicros = toNumber(row[stIdxCost]);
const avgCpcMicros = toNumber(row[stIdxAvgCpc]);
const ctr = toNumber(row[stIdxCtr]);
const costPerClickUsd = clicks > 0 ? (costMicros / 1000000) / clicks : 0;
outRows.push([
runId,
dateWindow.last30Start,
dateWindow.today,
stIdxDate >= 0 ? safeString(row[stIdxDate]) : '',
campaignId,
campaignName,
adGroupId,
adGroupName,
toNumber(adCountMap[key]),
safeString(adIdsMap[key]),
searchTerm,
matched ? matched.text : '',
matched ? matched.matchType : '',
matched ? microsToUsd(matched.cpcMicros) : 0,
microsToUsd(agCpcMap[key] || 0),
toNumber(row[stIdxImpr]),
clicks,
ctr,
microsToUsd(avgCpcMicros),
microsToUsd(costMicros),
roundTo(costPerClickUsd, 4),
toNumber(row[stIdxConv]),
toNumber(row[stIdxConvVal]),
matched ? matched.finalUrl : '',
safeString(snapshotAt),
]);
}
outRows.sort(function(a, b) {
if (b[16] !== a[16]) return b[16] - a[16];
if (b[15] !== a[15]) return b[15] - a[15];
return b[19] - a[19];
});
if (outRows.length > 0) {
out.getRange(2, 1, outRows.length, 25).setValues(outRows);
}
trimSheetToContent(out, 25);
}
function buildAutoDiagnosisActions(ss) {
const out = prepareTargetSheet(ss, CONFIG.AUTO_DIAG_SHEET, 15);
out.getRange(1, 1, 1, 15).setValues([[
'priority',
'issue_type',
'scope',
'campaign.id',
'campaign.name',
'ad_group.id',
'ad_group.name',
'search_term',
'matched_keyword',
'impressions',
'clicks',
'avg_cpc_usd',
'cost_usd',
'diagnosis',
'suggested_action',
]]);
const rows = [];
const dedup = {};
const addAction = function(
priority,
issueType,
scope,
campaignId,
campaignName,
adGroupId,
adGroupName,
searchTerm,
matchedKeyword,
impressions,
clicks,
avgCpcUsd,
costUsd,
diagnosis,
action
) {
if (rows.length >= CONFIG.AUTO_DIAG_MAX_ROWS) return;
const key = [
issueType,
safeString(campaignId),
safeString(adGroupId),
normalizeMatchText(searchTerm || ''),
normalizeMatchText(matchedKeyword || ''),
].join('|');
if (dedup[key]) return;
dedup[key] = true;
rows.push([
priority,
issueType,
scope,
safeString(campaignId),
safeString(campaignName),
safeString(adGroupId),
safeString(adGroupName),
safeString(searchTerm),
safeString(matchedKeyword),
toNumber(impressions),
toNumber(clicks),
roundTo(toNumber(avgCpcUsd), 4),
roundTo(toNumber(costUsd), 4),
safeString(diagnosis),
safeString(action),
]);
};
const adSh = ss.getSheetByName('05_ad_policy_status');
const campaignHasAds = new Set();
const adGroupHasAds = new Set();
if (adSh && adSh.getLastRow() >= 2) {
const vals = adSh.getDataRange().getValues();
const h = vals[0];
const iCampaignId = findCol(h, 'campaign.id');
const iAdGroupId = findCol(h, 'ad_group.id');
for (let i = 1; i < vals.length; i++) {
const cid = safeString(vals[i][iCampaignId]);
const agid = safeString(vals[i][iAdGroupId]);
if (cid) campaignHasAds.add(cid);
if (agid) adGroupHasAds.add(agid);
}
}
const campaignSh = ss.getSheetByName('02_campaign_metrics_30d');
if (campaignSh && campaignSh.getLastRow() >= 2) {
const vals = campaignSh.getDataRange().getValues();
const h = vals[0];
const iCampaignId = findCol(h, 'campaign.id');
const iCampaignName = findCol(h, 'campaign.name');
const iStatus = findCol(h, 'campaign.status');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
for (let i = 1; i < vals.length; i++) {
if (rows.length >= CONFIG.AUTO_DIAG_MAX_ROWS) break;
const row = vals[i];
const campaignId = safeString(row[iCampaignId]);
const campaignName = safeString(row[iCampaignName]);
const status = safeString(row[iStatus]);
const impressions = toNumber(row[iImpr]);
const clicks = toNumber(row[iClicks]);
const costUsd = toNumber(row[iCost]) / 1000000;
const avgCpcUsd = clicks > 0 ? costUsd / clicks : 0;
if (!campaignHasAds.has(campaignId)) {
addAction(
'P0',
'CAMPAIGN_WITHOUT_ADS',
'campaign',
campaignId,
campaignName,
'',
'',
'',
'',
impressions,
clicks,
avgCpcUsd,
costUsd,
'Campaign has no active ads in ad inventory table, so serving is structurally blocked.',
'Create at least 1 responsive search ad in this campaign, then relaunch.'
);
}
if (status === 'ENABLED' && impressions === 0) {
addAction(
'P0',
'ENABLED_CAMPAIGN_ZERO_IMPR',
'campaign',
campaignId,
campaignName,
'',
'',
'',
'',
impressions,
clicks,
avgCpcUsd,
costUsd,
'Campaign is enabled but has zero impressions in the selected window.',
'Consolidate low-volume campaigns, ensure ad presence, then tighten keyword/theme focus.'
);
}
}
}
const adGroupSh = ss.getSheetByName('04_ad_group_metrics_30d');
if (adGroupSh && adGroupSh.getLastRow() >= 2) {
const vals = adGroupSh.getDataRange().getValues();
const h = vals[0];
const iCampaignId = findCol(h, 'campaign.id');
const iCampaignName = findCol(h, 'campaign.name');
const iAdGroupId = findCol(h, 'ad_group.id');
const iAdGroupName = findCol(h, 'ad_group.name');
const iAdGroupStatus = findCol(h, 'ad_group.status');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
for (let i = 1; i < vals.length; i++) {
if (rows.length >= CONFIG.AUTO_DIAG_MAX_ROWS) break;
const row = vals[i];
const adGroupId = safeString(row[iAdGroupId]);
if (!adGroupId) continue;
const campaignId = safeString(row[iCampaignId]);
const campaignName = safeString(row[iCampaignName]);
const adGroupName = safeString(row[iAdGroupName]);
const adGroupStatus = safeString(row[iAdGroupStatus]);
const impressions = toNumber(row[iImpr]);
const clicks = toNumber(row[iClicks]);
const costUsd = toNumber(row[iCost]) / 1000000;
const avgCpcUsd = clicks > 0 ? costUsd / clicks : 0;
if (adGroupStatus === 'ENABLED' && !adGroupHasAds.has(adGroupId)) {
addAction(
'P0',
'ADGROUP_WITHOUT_ADS',
'ad_group',
campaignId,
campaignName,
adGroupId,
adGroupName,
'',
'',
impressions,
clicks,
avgCpcUsd,
costUsd,
'Enabled ad group has no ad records, so keyword traffic cannot enter auction.',
'Add ads to this ad group or merge it into a serving ad group.'
);
}
}
}
const quickViewSh = ss.getSheetByName(CONFIG.QUICK_VIEW_SHEET);
if (quickViewSh && quickViewSh.getLastRow() >= 2) {
const vals = quickViewSh.getDataRange().getValues();
const h = vals[0];
const iCampaignId = findCol(h, 'campaign.id');
const iCampaignName = findCol(h, 'campaign.name');
const iAdGroupId = findCol(h, 'ad_group.id');
const iAdGroupName = findCol(h, 'ad_group.name');
const iSearchTerm = findCol(h, 'search_term_view.search_term');
const iMatchedKeyword = findCol(h, 'matched_keyword.text');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iAvgCpc = findCol(h, 'metrics.average_cpc_usd');
const iCost = findCol(h, 'metrics.cost_total_usd');
for (let i = 1; i < vals.length; i++) {
if (rows.length >= CONFIG.AUTO_DIAG_MAX_ROWS) break;
const row = vals[i];
const campaignId = safeString(row[iCampaignId]);
const campaignName = safeString(row[iCampaignName]);
const adGroupId = safeString(row[iAdGroupId]);
const adGroupName = safeString(row[iAdGroupName]);
const searchTerm = safeString(row[iSearchTerm]);
const matchedKeyword = safeString(row[iMatchedKeyword]);
const impressions = toNumber(row[iImpr]);
const clicks = toNumber(row[iClicks]);
const avgCpcUsd = toNumber(row[iAvgCpc]);
const costUsd = toNumber(row[iCost]);
if (impressions <= 0) continue;
if (impressions >= 20 && clicks === 0) {
addAction(
'P1',
'ZERO_CLICK_HIGH_IMPR_TERM',
'search_term',
campaignId,
campaignName,
adGroupId,
adGroupName,
searchTerm,
matchedKeyword,
impressions,
clicks,
avgCpcUsd,
costUsd,
'High impressions but zero clicks indicates weak ad-message fit or weak keyword intent quality.',
'Rewrite RSA headlines/descriptions for this intent and add tighter match controls.'
);
}
if (impressions > 0 && impressions <= 2 && clicks === 0) {
addAction(
'P3',
'LOW_VOLUME_TERM',
'search_term',
campaignId,
campaignName,
adGroupId,
adGroupName,
searchTerm,
matchedKeyword,
impressions,
clicks,
avgCpcUsd,
costUsd,
'Term volume is too low to learn; keeping too many of these fragments traffic.',
'Merge into broader but relevant themes, then keep only winners with actual clicks.'
);
}
if (matchedKeyword && impressions >= 5 && clicks === 0) {
const overlap = computeTokenOverlapRate(searchTerm, matchedKeyword);
if (overlap < 0.34) {
addAction(
'P2',
'LOW_RELEVANCE_TERM',
'search_term',
campaignId,
campaignName,
adGroupId,
adGroupName,
searchTerm,
matchedKeyword,
impressions,
clicks,
avgCpcUsd,
costUsd,
'Search term has weak token overlap with matched keyword (overlap=' + overlap.toFixed(2) + ').',
'Split this theme into a dedicated ad group and tighten keyword matching.'
);
}
}
const neg = detectNegativeCandidate(searchTerm, matchedKeyword);
if (neg.hit && impressions >= 3 && clicks === 0) {
addAction(
'P2',
'NEGATIVE_KEYWORD_CANDIDATE',
'search_term',
campaignId,
campaignName,
adGroupId,
adGroupName,
searchTerm,
matchedKeyword,
impressions,
clicks,
avgCpcUsd,
costUsd,
'Term matches low-intent pattern token: ' + neg.token,
'Add as negative keyword (prefer phrase match first), then monitor for false negatives.'
);
}
}
}
rows.sort(function(a, b) {
const pa = priorityRank(a[0]);
const pb = priorityRank(b[0]);
if (pa !== pb) return pa - pb;
if (b[9] !== a[9]) return b[9] - a[9];
if (b[12] !== a[12]) return b[12] - a[12];
return String(a[1]).localeCompare(String(b[1]));
});
if (rows.length === 0) {
out.appendRow([
'P3',
'NO_ISSUE_DETECTED',
'global',
'',
'',
'',
'',
'',
'',
0,
0,
0,
0,
'No diagnosis rows were generated from current snapshots.',
'Keep running weekly snapshots to accumulate enough learning data.',
]);
} else {
out.getRange(2, 1, rows.length, 15).setValues(rows);
}
trimSheetToContent(out, 15);
}
function buildPolicyBatchActions(ss) {
const out = prepareTargetSheet(ss, CONFIG.POLICY_BATCH_ACTION_SHEET, 13);
out.getRange(1, 1, 1, 13).setValues([[
'priority',
'approval_status',
'is_lu_lb_campaign',
'campaign_name',
'campaign_id',
'ad_group_name',
'ad_group_id',
'ad_id',
'ad_type',
'ad_status',
'review_status',
'final_urls',
'suggested_action',
]]);
const source = ss.getSheetByName('05_ad_policy_status');
if (!source || source.getLastRow() < 2) {
out.appendRow([
'P3',
'',
'N',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'No policy source rows found in 05_ad_policy_status.',
]);
trimSheetToContent(out, 13);
return;
}
const vals = source.getDataRange().getValues();
const h = vals[0];
const iCampaignName = findCol(h, 'campaign.name');
const iCampaignId = findCol(h, 'campaign.id');
const iAdGroupName = findCol(h, 'ad_group.name');
const iAdGroupId = findCol(h, 'ad_group.id');
const iAdId = findCol(h, 'ad_group_ad.ad.id');
const iApproval = findCol(h, 'ad_group_ad.policy_summary.approval_status');
const iReview = findCol(h, 'ad_group_ad.policy_summary.review_status');
const iAdType = findCol(h, 'ad_group_ad.ad.type');
const iAdStatus = findCol(h, 'ad_group_ad.status');
const iFinalUrls = findCol(h, 'ad_group_ad.ad.final_urls');
const rows = [];
for (let i = 1; i < vals.length; i++) {
const row = vals[i];
const campaignName = safeString(row[iCampaignName]);
const approval = safeString(row[iApproval]).toUpperCase();
const isLuLb = campaignName.indexOf('-LU-lb-') >= 0;
if (approval !== 'APPROVED_LIMITED' && !(approval === 'DISAPPROVED' && isLuLb)) {
continue;
}
let priority = 'P1';
let action = 'Policy-limited cleanup: replace risky claims, remove unverifiable superlatives, and resubmit.';
if (approval === 'DISAPPROVED' && isLuLb) {
priority = 'P0';
action = 'Immediate fix: duplicate RSA with compliant claims/assets; pause disapproved ad only after replacement is approved.';
}
rows.push([
priority,
approval,
isLuLb ? 'Y' : 'N',
campaignName,
safeString(row[iCampaignId]),
safeString(row[iAdGroupName]),
safeString(row[iAdGroupId]),
safeString(row[iAdId]),
safeString(row[iAdType]),
safeString(row[iAdStatus]),
safeString(row[iReview]),
safeString(row[iFinalUrls]),
action,
]);
}
rows.sort(function(a, b) {
const pa = priorityRank(a[0]);
const pb = priorityRank(b[0]);
if (pa !== pb) return pa - pb;
return String(a[3]).localeCompare(String(b[3]));
});
if (rows.length > 0) {
out.getRange(2, 1, rows.length, 13).setValues(rows);
} else {
out.appendRow([
'P3',
'',
'N',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'No APPROVED_LIMITED rows or LU-lb DISAPPROVED rows found.',
]);
}
trimSheetToContent(out, 13);
}
function buildCpcDailyDashboard(ss) {
const out = prepareTargetSheet(ss, CONFIG.CPC_DAILY_DASHBOARD_SHEET, 11);
out.getRange(1, 1, 1, 11).setValues([[
'segments.date',
'cpc_level',
'nominal_cpc_usd',
'campaign_count',
'impressions',
'clicks',
'cost_usd',
'avg_cpc_usd',
'conversions',
'cvr',
'note',
]]);
const source = ss.getSheetByName('03_campaign_daily_30d');
if (!source || source.getLastRow() < 2) {
out.appendRow(['', '', 0, 0, 0, 0, 0, 0, 0, 0, 'No source data in 03_campaign_daily_30d']);
trimSheetToContent(out, 11);
return;
}
const vals = source.getDataRange().getValues();
const h = vals[0];
const iName = findCol(h, 'campaign.name');
const iDate = findCol(h, 'segments.date');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
const iConv = findCol(h, 'metrics.conversions');
const iCid = findCol(h, 'campaign.id');
const agg = {};
for (let i = 1; i < vals.length; i++) {
const row = vals[i];
const name = safeString(row[iName]);
const parsed = parseCpcFromCampaignName(name);
if (!parsed.level) continue;
const dateKey = normalizeDateKey(row[iDate]);
if (!dateKey) continue;
const key = dateKey + '|' + parsed.level;
if (!agg[key]) {
agg[key] = {
date: dateKey,
cpc: parsed.level,
nominal: parsed.nominal,
campaigns: new Set(),
impr: 0,
clicks: 0,
costMicros: 0,
conv: 0,
};
}
agg[key].campaigns.add(safeString(row[iCid]));
agg[key].impr += toNumber(row[iImpr]);
agg[key].clicks += toNumber(row[iClicks]);
agg[key].costMicros += toNumber(row[iCost]);
agg[key].conv += toNumber(row[iConv]);
}
const rows = [];
const keys = Object.keys(agg).sort();
for (let i = 0; i < keys.length; i++) {
const x = agg[keys[i]];
const costUsd = x.costMicros / 1000000;
const avgCpc = x.clicks > 0 ? costUsd / x.clicks : 0;
const cvr = x.clicks > 0 ? x.conv / x.clicks : 0;
rows.push([
x.date,
x.cpc,
x.nominal,
x.campaigns.size,
Math.round(x.impr),
Math.round(x.clicks),
roundTo(costUsd, 4),
roundTo(avgCpc, 4),
roundTo(x.conv, 4),
roundTo(cvr, 4),
x.clicks > 0 && x.conv === 0 ? 'has spend but no conversion' : '',
]);
}
if (rows.length > 0) {
out.getRange(2, 1, rows.length, 11).setValues(rows);
} else {
out.appendRow(['', '', 0, 0, 0, 0, 0, 0, 0, 0, 'No CPC-tagged campaigns found']);
}
trimSheetToContent(out, 11);
}
function buildCpcMappingAudit(ss) {
const out = prepareTargetSheet(ss, CONFIG.CPC_MAPPING_AUDIT_SHEET, 7);
out.getRange(1, 1, 1, 7).setValues([[
'cpc_level_in_name',
'nominal_cpc_from_name',
'actual_avg_keyword_bid_usd',
'keyword_rows',
'mismatch_flag',
'impact_note',
'recommended_fix',
]]);
const kw = ss.getSheetByName(CONFIG.KEYWORD_INVENTORY_SHEET);
if (!kw || kw.getLastRow() < 2) {
out.appendRow(['', 0, 0, 0, 'N', 'keyword inventory missing', 'Run keyword inventory export first']);
trimSheetToContent(out, 7);
return;
}
const vals = kw.getDataRange().getValues();
const h = vals[0];
const iName = findCol(h, 'campaign.name');
const iBid = findCol(h, 'ad_group_criterion.cpc_bid_micros');
const agg = {};
for (let i = 1; i < vals.length; i++) {
const row = vals[i];
const parsed = parseCpcFromCampaignName(row[iName]);
if (!parsed.level) continue;
if (!agg[parsed.level]) {
agg[parsed.level] = {
nominal: parsed.nominal,
bidSum: 0,
bidCnt: 0,
};
}
const bidMicros = toNumber(row[iBid]);
if (bidMicros <= 0) continue;
agg[parsed.level].bidSum += bidMicros / 1000000;
agg[parsed.level].bidCnt++;
}
const levels = Object.keys(agg).sort(function(a, b) {
return Number(a.replace('CPC', '')) - Number(b.replace('CPC', ''));
});
const rows = [];
for (let i = 0; i < levels.length; i++) {
const k = levels[i];
const x = agg[k];
const avgBid = x.bidCnt > 0 ? x.bidSum / x.bidCnt : 0;
const mismatch = Math.abs(avgBid - x.nominal) > 0.0001;
rows.push([
k,
roundTo(x.nominal, 4),
roundTo(avgBid, 4),
x.bidCnt,
mismatch ? 'Y' : 'N',
mismatch ? 'name-level CPC differs from actual keyword bid' : '',
mismatch
? 'Align bid template to CPC label OR rename campaign to actual bid tier'
: 'No action needed',
]);
}
if (rows.length > 0) {
out.getRange(2, 1, rows.length, 7).setValues(rows);
} else {
out.appendRow(['', 0, 0, 0, 'N', 'No CPC-tagged campaigns in keyword inventory', '']);
}
trimSheetToContent(out, 7);
}
function readFreshnessValue(sh, key) {
if (!sh || sh.getLastRow() < 2) return '';
const vals = sh.getDataRange().getValues();
for (let i = 1; i < vals.length; i++) {
if (safeString(vals[i][0]) === key) return vals[i][1];
}
return '';
}
function chooseBestKeywordForSearchTerm(searchTerm, candidates) {
if (!candidates || candidates.length === 0) return null;
const termNorm = normalizeMatchText(searchTerm);
const termTokens = tokenizeMatchText(termNorm);
let best = null;
let bestScore = -9999;
for (let i = 0; i < candidates.length; i++) {
const c = candidates[i];
const kwNorm = normalizeMatchText(c.text);
const kwTokens = tokenizeMatchText(kwNorm);
let score = 0;
if (kwNorm && kwNorm === termNorm) score += 100;
else if (kwNorm && termNorm.indexOf(kwNorm) >= 0) score += 80;
else if (termNorm && kwNorm.indexOf(termNorm) >= 0) score += 70;
let overlap = 0;
for (let t = 0; t < termTokens.length; t++) {
if (kwTokens.indexOf(termTokens[t]) >= 0) overlap++;
}
score += overlap * 5;
if (c.matchType === 'EXACT') score += 3;
if (c.matchType === 'PHRASE') score += 2;
if (c.matchType === 'BROAD') score += 1;
score += Math.min(toNumber(c.cpcMicros) / 1000000, 10) * 0.01;
if (score > bestScore) {
bestScore = score;
best = c;
}
}
return best;
}
function normalizeMatchText(s) {
return safeString(s)
.toLowerCase()
.replace(/[^a-z0-9\u00C0-\u024F\u4E00-\u9FFF\u3040-\u30FF\uAC00-\uD7AF]+/g, ' ')
.trim();
}
function tokenizeMatchText(s) {
if (!s) return [];
return s.split(/\s+/).filter(function(x) { return x.length > 0; });
}
function computeTokenOverlapRate(a, b) {
const ta = tokenizeMatchText(normalizeMatchText(a));
const tb = tokenizeMatchText(normalizeMatchText(b));
if (ta.length === 0 || tb.length === 0) return 0;
const bSet = {};
for (let i = 0; i < tb.length; i++) bSet[tb[i]] = true;
let hit = 0;
for (let i = 0; i < ta.length; i++) {
if (bSet[ta[i]]) hit++;
}
return hit / Math.max(ta.length, tb.length);
}
function detectNegativeCandidate(searchTerm, matchedKeyword) {
const term = normalizeMatchText(searchTerm);
const keyword = normalizeMatchText(matchedKeyword);
const tokens = tokenizeMatchText(term);
for (let i = 0; i < tokens.length; i++) {
const token = tokens[i];
if (CONFIG.NEGATIVE_CANDIDATE_TOKENS.indexOf(token) < 0) continue;
if (keyword && tokenizeMatchText(keyword).indexOf(token) >= 0) continue;
return { hit: true, token: token };
}
return { hit: false, token: '' };
}
function priorityRank(p) {
if (p === 'P0') return 0;
if (p === 'P1') return 1;
if (p === 'P2') return 2;
if (p === 'P3') return 3;
return 9;
}
function parseFirstFinalUrl(v) {
if (v === null || v === undefined) return '';
const s = safeString(v).trim();
if (!s) return '';
try {
const parsed = JSON.parse(s);
if (Array.isArray(parsed) && parsed.length > 0) return safeString(parsed[0]);
return safeString(parsed);
} catch (e) {
return s;
}
}
function microsToUsd(v) {
return roundTo(toNumber(v) / 1000000, 4);
}
function roundTo(n, d) {
const p = Math.pow(10, d || 0);
return Math.round(n * p) / p;
}
function writeDataFreshness(ss, account, tz, runId, dateWindow) {
const sh = prepareTargetSheet(ss, CONFIG.FRESHNESS_SHEET, 2);
const now = new Date();
const range = getCampaignDailyDateRange(ss);
sh.getRange(1, 1, 1, 2).setValues([['key', 'value']]);
sh.getRange(2, 1, 10, 2).setValues([
['run_id', runId],
['exported_at_account_tz', formatDate(now, tz)],
['account_id', String(account.getCustomerId())],
['account_name', safeString(account.getName())],
['account_timezone', tz],
['daily_data_earliest', range.earliest || dateWindow.last30Start],
['daily_data_latest', range.latest || dateWindow.today],
['is_realtime_stream', 'NO'],
['data_type', 'snapshot at script run time'],
['note', `window=${dateWindow.last30Start}..${dateWindow.today}; today metrics can still update later`],
]);
trimSheetToContent(sh, 2);
}
function getCampaignDailyDateRange(ss) {
const out = { earliest: '', latest: '' };
const sh = ss.getSheetByName('03_campaign_daily_30d');
if (!sh || sh.getLastRow() < 2) return out;
const values = sh.getDataRange().getValues();
const h = values[0];
const idxDate = h.indexOf('segments.date');
if (idxDate < 0) return out;
let min = null;
let max = null;
for (let i = 1; i < values.length; i++) {
const d = values[i][idxDate];
if (!d) continue;
const key = normalizeDateKey(d);
if (!key) continue;
if (min === null || key < min) min = key;
if (max === null || key > max) max = key;
}
out.earliest = min || '';
out.latest = max || '';
return out;
}
function getDateWindow(tz) {
const now = new Date();
return {
today: Utilities.formatDate(now, tz, 'yyyy-MM-dd'),
yesterday: Utilities.formatDate(
new Date(now.getTime() - 24 * 60 * 60 * 1000),
tz,
'yyyy-MM-dd'
),
last7Start: Utilities.formatDate(
new Date(now.getTime() - 6 * 24 * 60 * 60 * 1000),
tz,
'yyyy-MM-dd'
),
last30Start: Utilities.formatDate(
new Date(now.getTime() - 29 * 24 * 60 * 60 * 1000),
tz,
'yyyy-MM-dd'
),
};
}
function normalizeDateKey(v) {
if (v instanceof Date) {
return Utilities.formatDate(v, 'UTC', 'yyyy-MM-dd');
}
const s = String(v);
if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
return '';
}
function parseCpcFromCampaignName(name) {
const s = safeString(name);
const m = s.match(/CPC(\d{3})/i);
if (!m) return { level: '', nominal: 0 };
const level = 'CPC' + m[1];
return {
level: level,
nominal: Number(m[1]) / 100,
};
}
function extractCountryFromCampaignName(name) {
const s = safeString(name);
const parts = s.split('-');
if (parts.length >= 2 && /^[A-Za-z]{2}$/.test(parts[1])) {
return parts[1].toUpperCase();
}
return 'ZZ';
}
function normalizeGaqlId(v) {
const s = safeString(v).replace(/\.0$/, '').trim();
return /^\d+$/.test(s) ? s : '';
}
function chunkList(arr, size) {
const out = [];
const n = Math.max(1, Number(size) || 1);
for (let i = 0; i < arr.length; i += n) {
out.push(arr.slice(i, i + n));
}
return out;
}
function hardResetWorkbook(ss, jobs) {
const managedTabs = getManagedTabs(jobs);
const managedSet = new Set(managedTabs);
if (CONFIG.DROP_UNMANAGED_SHEETS) {
const all = ss.getSheets();
for (let i = all.length - 1; i >= 0; i--) {
const sh = all[i];
const name = sh.getName();
if (managedSet.has(name)) continue;
if (ss.getSheets().length <= 1) {
sh.clear();
resizeSheetGrid(sh, CONFIG.BASE_ROWS, CONFIG.BASE_COLS);
} else {
ss.deleteSheet(sh);
}
}
}
if (CONFIG.CLEAR_RUN_LOG_EACH_RUN) {
const logSh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
logSh.clear();
resizeSheetGrid(logSh, CONFIG.BASE_ROWS, 10);
}
const statusSh = getOrCreateSheet(ss, CONFIG.STATUS_SHEET);
statusSh.clear();
resizeSheetGrid(statusSh, CONFIG.BASE_ROWS, 2);
const extra = [
CONFIG.QUICK_VIEW_SHEET,
CONFIG.FRESHNESS_SHEET,
CONFIG.KEYWORD_QUALITY_SHEET,
CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET,
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
CONFIG.KEYWORD_INVENTORY_SHEET,
CONFIG.POLICY_TOPIC_SHEET,
CONFIG.POLICY_BATCH_ACTION_SHEET,
CONFIG.DIAG_SHEET,
CONFIG.TOP_CAMPAIGNS_SHEET,
CONFIG.AUTO_DIAG_SHEET,
CONFIG.CPC_DAILY_DASHBOARD_SHEET,
CONFIG.CPC_MAPPING_AUDIT_SHEET,
];
const colMap = {};
for (let i = 0; i < jobs.length; i++) {
colMap[jobs[i].tab] = jobs[i].minCols || CONFIG.BASE_COLS;
}
colMap[CONFIG.FRESHNESS_SHEET] = 2;
colMap[CONFIG.KEYWORD_QUALITY_SHEET] = 20;
colMap[CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET] = 9;
colMap[CONFIG.ZERO_IMPR_SAMPLE_SHEET] = 9;
colMap[CONFIG.NEG_ADGROUP_SAMPLE_SHEET] = 8;
colMap[CONFIG.KEYWORD_INVENTORY_SHEET] = 14;
colMap[CONFIG.POLICY_TOPIC_SHEET] = 12;
colMap[CONFIG.POLICY_BATCH_ACTION_SHEET] = 13;
colMap[CONFIG.DIAG_SHEET] = 3;
colMap[CONFIG.TOP_CAMPAIGNS_SHEET] = 13;
colMap[CONFIG.QUICK_VIEW_SHEET] = 25;
colMap[CONFIG.AUTO_DIAG_SHEET] = 15;
colMap[CONFIG.CPC_DAILY_DASHBOARD_SHEET] = 11;
colMap[CONFIG.CPC_MAPPING_AUDIT_SHEET] = 7;
const tabs = [CONFIG.QUICK_VIEW_SHEET]
.concat(jobs.map((j) => j.tab))
.concat(extra.filter((x) => x !== CONFIG.QUICK_VIEW_SHEET));
for (let i = 0; i < tabs.length; i++) {
prepareTargetSheet(ss, tabs[i], colMap[tabs[i]] || CONFIG.BASE_COLS);
}
enforceSheetOrder(ss, tabs.concat([CONFIG.LOG_SHEET, CONFIG.STATUS_SHEET]));
ensureLogHeader(ss);
}
function getManagedTabs(jobs) {
return [CONFIG.QUICK_VIEW_SHEET].concat(jobs.map((j) => j.tab)).concat([
CONFIG.LOG_SHEET,
CONFIG.STATUS_SHEET,
CONFIG.FRESHNESS_SHEET,
CONFIG.KEYWORD_QUALITY_SHEET,
CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET,
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
CONFIG.KEYWORD_INVENTORY_SHEET,
CONFIG.POLICY_TOPIC_SHEET,
CONFIG.POLICY_BATCH_ACTION_SHEET,
CONFIG.DIAG_SHEET,
CONFIG.TOP_CAMPAIGNS_SHEET,
CONFIG.AUTO_DIAG_SHEET,
CONFIG.CPC_DAILY_DASHBOARD_SHEET,
CONFIG.CPC_MAPPING_AUDIT_SHEET,
]);
}
function enforceSheetOrder(ss, desiredOrder) {
if (!desiredOrder || !desiredOrder.length) return;
// 去重，避免重复 tab 名导致无意义移动
const unique = [];
const seen = {};
for (let i = 0; i < desiredOrder.length; i++) {
const name = desiredOrder[i];
if (!name || seen[name]) continue;
seen[name] = true;
unique.push(name);
}
// 逆序移动到第 1 位，可保持 unique 的最终顺序
for (let i = unique.length - 1; i >= 0; i--) {
const name = unique[i];
const sh = ss.getSheetByName(name);
if (!sh) continue;
try {
if (typeof sh.getIndex === 'function' && sh.getIndex() === 1) continue;
// Google Ads Script / Apps Script 兼容方式：激活后移动活动 sheet
if (typeof ss.setActiveSheet === 'function') {
ss.setActiveSheet(sh, true);
} else if (typeof sh.activate === 'function') {
sh.activate();
}
if (typeof ss.moveActiveSheet === 'function') {
ss.moveActiveSheet(1);
} else if (typeof sh.setIndex === 'function') {
// 兼容部分运行环境
sh.setIndex(1);
} else {
Logger.log(
'[WARN] enforceSheetOrder: no supported move API for sheet "' +
name +
'"'
);
}
} catch (e) {
Logger.log(
'[WARN] enforceSheetOrder failed for sheet "' +
name +
'": ' +
(e && e.message ? e.message : e)
);
}
}
}
function prepareTargetSheet(ss, name, minCols) {
const sh = getOrCreateSheet(ss, name);
sh.clear();
resizeSheetGrid(sh, CONFIG.BASE_ROWS, Math.max(CONFIG.BASE_COLS, minCols || CONFIG.BASE_COLS));
return sh;
}
function trimSheetToContent(sh, minCols) {
const lastRow = Math.max(sh.getLastRow(), 1);
const lastCol = Math.max(sh.getLastColumn(), minCols || 1);
const targetRows = Math.max(lastRow + 5, 20);
const targetCols = Math.max(lastCol + 1, minCols || 1);
resizeSheetGrid(sh, targetRows, targetCols);
}
function resizeSheetGrid(sh, targetRows, targetCols) {
const tr = Math.max(1, targetRows);
const tc = Math.max(1, targetCols);
const maxRows = sh.getMaxRows();
const maxCols = sh.getMaxColumns();
if (maxRows > tr) {
sh.deleteRows(tr + 1, maxRows - tr);
} else if (maxRows < tr) {
sh.insertRowsAfter(maxRows, tr - maxRows);
}
if (maxCols > tc) {
sh.deleteColumns(tc + 1, maxCols - tc);
} else if (maxCols < tc) {
sh.insertColumnsAfter(maxCols, tc - maxCols);
}
}
function getWorkbookCellCount(ss) {
const sheets = ss.getSheets();
let total = 0;
for (let i = 0; i < sheets.length; i++) {
total += sheets[i].getMaxRows() * sheets[i].getMaxColumns();
}
return total;
}
function getOrCreateSheet(ss, name) {
let sh = ss.getSheetByName(name);
if (!sh) sh = ss.insertSheet(name);
return sh;
}
function ensureLogHeader(ss) {
const sh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
if (sh.getLastRow() === 0) {
sh.getRange(1, 1, 1, 10).setValues([[
'run_id',
'timestamp',
'account_id',
'account_name',
'status',
'tab',
'row_count',
'duration_sec',
'message',
'query',
]]);
}
}
function appendLog(ss, row) {
const sh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
sh.appendRow(row);
}
function writeStatus(ss, runId, account, startedAt, nextIndex, totalJobs, status, note) {
const sh = getOrCreateSheet(ss, CONFIG.STATUS_SHEET);
sh.clear();
sh.getRange(1, 1, 10, 2).setValues([
['run_id', runId],
['account_id', String(account.getCustomerId())],
['account_name', safeString(account.getName())],
['started_at', startedAt],
['next_job_index', nextIndex],
['total_jobs', totalJobs],
['status', status],
['progress', `'${nextIndex}/${totalJobs}`],
['note', note],
['updated_at', formatDate(new Date(), account.getTimeZone())],
]);
trimSheetToContent(sh, 2);
}
function getSheetDataRowCount(ss, name) {
const sh = ss.getSheetByName(name);
if (!sh) return 0;
return Math.max(sh.getLastRow() - 1, 0);
}
function resetResumeCheckpoint() {
PropertiesService.getScriptProperties().deleteProperty(CONFIG.RESUME_KEY);
}
function isNearTimeout(startDate, headroomMinutes) {
return elapsedMinutes(startDate) >= (CONFIG.MAX_RUNTIME_MINUTES - (headroomMinutes || 0));
}
function pauseForTimeout(
ss,
props,
runId,
account,
tz,
startedAt,
nextIndex,
totalJobs,
tab,
note,
query
) {
props.setProperty(CONFIG.RESUME_KEY, String(nextIndex));
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'TIMEOUT_GUARD',
tab,
0,
0,
note,
truncate(query || '', 500),
]);
writeStatus(
ss,
runId,
account,
formatDate(startedAt, tz),
nextIndex,
totalJobs,
'PAUSED_TIMEOUT',
note
);
}
function elapsedMinutes(startDate) {
return (new Date().getTime() - startDate.getTime()) / 60000;
}
function secondsBetween(a, b) {
return Math.round((b.getTime() - a.getTime()) / 1000);
}
function formatDate(d, tz) {
return Utilities.formatDate(d, tz, 'yyyy-MM-dd HH:mm:ss');
}
function truncate(text, maxLen) {
const s = String(text || '');
return s.length <= maxLen ? s : s.substring(0, maxLen - 3) + '...';
}
function safeJson(v) {
if (v === null || v === undefined) return '';
try {
const s = JSON.stringify(v);
return s === undefined ? '' : s;
} catch (e) {
return safeString(v);
}
}
function safeString(v) {
if (v === null || v === undefined) return '';
return String(v);
}
function toNumber(v) {
if (v === null || v === undefined || v === '') return 0;
const n = Number(v);
return isNaN(n) ? 0 : n;
}
function findCol(headers, name) {
const idx = headers.indexOf(name);
if (idx < 0) throw new Error('missing column: ' + name);
return idx;
}
本文档为站内渲染。原始文件本地路径：saas/source/ads/广告-ads-scripts-全量重脚本-691d24.txt（仅本地保留，不入库不部署）