Keyword match optimizer

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 CAMPAIGN_NAME = 'INSERT-CAMPAIGN-NAME';

  const today = new Date();
  const endDate = Utilities.formatDate(today, 'GMT', 'yyyyMMdd');
  const startDate = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);
  const formattedStartDate = Utilities.formatDate(startDate, 'GMT', 'yyyyMMdd');

  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', 'Conversion Value'
  ]);

  const query = `SELECT Query, KeywordTextMatchingQuery, KeywordMatchType, CampaignName, AdGroupName, 
                 Impressions, Clicks, Cost, Conversions, ConversionValue
                 FROM SEARCH_QUERY_PERFORMANCE_REPORT
                 WHERE CampaignName = "${CAMPAIGN_NAME}"
                 DURING ${formattedStartDate},${endDate}`;
                 
  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['KeywordMatchType']; // Added match type
    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 = row['Cost'];
    const conversions = parseFloat(row['Conversions']);
    const conversionValue = parseFloat(row['ConversionValue']);

    data.push([
      searchQuery, keyword, matchType, adGroup, campaign, // Added match type here
      impressions, clicks, ctr, cost, conversions, conversionValue
    ]);
  }

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

  if (data.length > 0) {
    sheet.getRange(2, 1, data.length, 11).setValues(data); // Adjusted to 11 columns
  } 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.