Account search query optimizer (yesterday)

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 spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);

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

  // Clear the previous data
  sheet.clearContents();

  // Add header row with the new Date column (Column A)
  sheet.appendRow([
    'Date', 'Search Term', 'Keyword', 'Match Type', 'AdGroup', 'Campaign',
    'Impressions', 'Clicks', 'CTR', 'Cost', 'Conversions', 'Cost per Conversion', 'Conversion Value'
  ]);

  // Get yesterday's date
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);

  const formatDateForGAQL = (date) => {
    const year = date.getFullYear();
    const month = ('0' + (date.getMonth() + 1)).slice(-2);
    const day = ('0' + date.getDate()).slice(-2);
    return `${year}${month}${day}`;
  };

  const formatDateForSheet = (date) => {
    const year = date.getFullYear();
    const month = ('0' + (date.getMonth() + 1)).slice(-2);
    const day = ('0' + date.getDate()).slice(-2);
    return `${year}-${month}-${day}`; // Format: YYYY-MM-DD
  };

  const YESTERDAY_DATE_GAQL = formatDateForGAQL(yesterday);
  const YESTERDAY_DATE_SHEET = formatDateForSheet(yesterday);

  // Google Ads Query (GAQL) to pull only yesterday's data
  const query = `SELECT Query, KeywordTextMatchingQuery, QueryMatchTypeWithVariant, AdGroupName, CampaignName, 
                 Impressions, Clicks, Cost, Conversions, ConversionValue
                 FROM SEARCH_QUERY_PERFORMANCE_REPORT
                 DURING ${YESTERDAY_DATE_GAQL},${YESTERDAY_DATE_GAQL}`;
                 
  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 adGroup = row['AdGroupName'];
    const campaign = row['CampaignName'];
    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([
      YESTERDAY_DATE_SHEET, // Column A: Date
      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[8] - a[8]) || (b[6] - a[6]));

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

1. Replace the spreadsheet URL, and sheet 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.