Account keyword optimizer (date range)

This script allows you to understand which search terms are triggering your ads and how these keywords are performing in terms of generating user clicks. This data is valuable for evaluating the performance of your keywords and making informed decisions about how to optimize your campaigns.

To set up this script, follow these steps:

1. Open your Google Ads account.

2. Go to the "Tools & Settings" menu and select "Scripts".

3. Click the "Create" or "+" button to create a new script.

4. Copy and paste the following script.

function main() {
  const SPREADSHEET_URL = 'INSERT-SPREADSHEET-URL';
  const SHEET_NAME = 'INSERT-SHEET-NAME'; // Replace with your sheet name
  const START_DATE = 'YYYYMMDD'; // Specify the start date in 'YYYYMMDD' format
  const END_DATE = 'YYYYMMDD'; // Specify the end date in 'YYYYMMDD' format

  const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);

  if (!sheet) {
    Logger.log('Sheet not found.');
    return;
  }

  sheet.clearContents();
  sheet.appendRow([
    'Search Term', 'Keyword', 'Match Type', 'AdGroup', 'Campaign',
    'Impressions', 'Clicks', 'CTR', 'Cost', 'Conversions', 'Cost per Conversion', 'Conversion Value'
  ]);

  const query = `SELECT Query, KeywordTextMatchingQuery, QueryMatchTypeWithVariant, CampaignName, AdGroupName, 
                 Impressions, Clicks, Cost, Conversions, ConversionValue
                 FROM SEARCH_QUERY_PERFORMANCE_REPORT
                 DURING ${START_DATE},${END_DATE}`;
                 
  const report = AdsApp.report(query);
  const rows = report.rows();
  const data = [];

  while (rows.hasNext()) {
    const row = rows.next();
    const searchQuery = row['Query'];
    const keyword = row['KeywordTextMatchingQuery'];
    const matchType = row['QueryMatchTypeWithVariant'];
    const campaign = row['CampaignName'];
    const adGroup = row['AdGroupName'];
    const impressions = parseInt(row['Impressions'], 10);
    const clicks = parseInt(row['Clicks'], 10);
    const ctr = impressions > 0 ? clicks / impressions : 0;
    const cost = parseFloat(row['Cost']);
    const conversions = parseFloat(row['Conversions']);
    const costPerConversion = conversions > 0 ? cost / conversions : 0;
    const conversionValue = parseFloat(row['ConversionValue']);

    data.push([
      searchQuery, keyword, matchType, adGroup, campaign,
      impressions, clicks, ctr, cost, conversions, costPerConversion, conversionValue
    ]);
  }

  // Sort data by CTR in descending order, then by Impressions in descending order if CTR is the same
  data.sort((a, b) => (b[7] - a[7]) || (b[5] - a[5]));

  if (data.length > 0) {
    sheet.getRange(2, 1, data.length, 12).setValues(data);
  } else {
    Logger.log('No data to write to the sheet.');
  }
}

1. Replace the spreadsheet URL, sheet name, dates, and campaign name.

2. Save the script.

3. Review and authorize the script to access your Google Ads and Google Sheets accounts.

4. Run the script manually for the first time to populate the initial values in the spreadsheet.

5. (Optional) Schedule the script to run on a specific cadence (ie: daily, weekly, monthly, etc.)

Note: Ensure that you have the necessary permissions to access and modify the target Google Spreadsheet.