performance.ts•3.03 kB
export type PerformanceLevel = 'account' | 'campaign' | 'ad_group' | 'ad';
export type PerformanceFilters = {
status?: string;
name_contains?: string;
campaign_name_contains?: string;
min_clicks?: number;
min_impressions?: number;
};
export function buildPerformanceQuery(
level: PerformanceLevel,
days = 30,
limit = 50,
filters: PerformanceFilters = {}
): string {
// Runtime bounds
const safeDays = Math.max(1, Math.min(365, Math.floor(days)));
const safeLimit = Math.max(1, Math.min(1000, Math.floor(limit)));
const baseMetrics = `
metrics.impressions,
metrics.clicks,
metrics.cost_micros,
metrics.conversions,
metrics.average_cpc,
customer.currency_code
`;
let fields = '';
let from = '';
let statusField = '';
let nameField = '';
const campaignNameField = 'campaign.name';
switch (level) {
case 'account':
fields = `
customer.id,
customer.descriptive_name,
customer.currency_code,
${baseMetrics}
`;
from = 'customer';
statusField = 'customer.status';
nameField = 'customer.descriptive_name';
break;
case 'campaign':
fields = `
campaign.id,
campaign.name,
campaign.status,
${baseMetrics}
`;
from = 'campaign';
statusField = 'campaign.status';
nameField = 'campaign.name';
break;
case 'ad_group':
fields = `
campaign.name,
ad_group.id,
ad_group.name,
ad_group.status,
${baseMetrics}
`;
from = 'ad_group';
statusField = 'ad_group.status';
nameField = 'ad_group.name';
break;
case 'ad':
fields = `
campaign.name,
ad_group.name,
ad_group_ad.ad.id,
ad_group_ad.status,
${baseMetrics}
`;
from = 'ad_group_ad';
statusField = 'ad_group_ad.status';
nameField = 'ad_group.name';
break;
default:
throw new Error('Invalid level. Use campaign | ad_group | ad');
}
const whereClauses: string[] = [`
SELECT
${fields}
FROM ${from}
WHERE segments.date DURING LAST_${safeDays}_DAYS`];
// Apply filters
const esc = (v: string) => v.replace(/'/g, "''");
if (filters.status) whereClauses.push(`AND ${statusField} = '${esc(filters.status)}'`);
if (filters.name_contains) whereClauses.push(`AND ${nameField} LIKE '%${esc(filters.name_contains)}%'`);
if (filters.campaign_name_contains && level !== 'account')
whereClauses.push(`AND ${campaignNameField} LIKE '%${esc(filters.campaign_name_contains)}%'`);
if (typeof filters.min_clicks === 'number') whereClauses.push(`AND metrics.clicks >= ${Math.max(0, Math.floor(filters.min_clicks))}`);
if (typeof filters.min_impressions === 'number') whereClauses.push(`AND metrics.impressions >= ${Math.max(0, Math.floor(filters.min_impressions))}`);
const query = `
${whereClauses.join('\n ')}
ORDER BY metrics.cost_micros DESC
LIMIT ${safeLimit}
`;
return query;
}