Match type report (last 360 days)

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;
  }

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

  // Calculate the start and end dates for the last 90 days
  const endDate = new Date();
  const startDate = new Date();
  startDate.setDate(endDate.getDate() - 360);

  const formatDate = (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 START_DATE = formatDate(startDate);
  const END_DATE = formatDate(endDate);

  const query = `SELECT QueryMatchTypeWithVariant, 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 matchType = row['QueryMatchTypeWithVariant'];
    const impressions = parseInt(row['Impressions'], 10);
    const clicks = parseInt(row['Clicks'], 10);
    const cost = parseFloat(row['Cost']);
    const conversions = parseFloat(row['Conversions']);
    const conversionValue = parseFloat(row['ConversionValue']);

    if (!data[matchType]) {
      data[matchType] = { impressions: 0, clicks: 0, cost: 0, conversions: 0, conversionValue: 0 };
    }

    data[matchType].impressions += impressions;
    data[matchType].clicks += clicks;
    data[matchType].cost += cost;
    data[matchType].conversions += conversions;
    data[matchType].conversionValue += conversionValue;
  }

  const aggregatedData = [];

  for (const matchType in data) {
    const impressions = data[matchType].impressions;
    const clicks = data[matchType].clicks;
    const ctr = impressions > 0 ? (clicks / impressions) * 100 : 0;
    const cost = data[matchType].cost;
    const conversions = data[matchType].conversions;
    const costPerConversion = conversions > 0 ? cost / conversions : 0;
    const conversionValue = data[matchType].conversionValue;

    aggregatedData.push([
      matchType, impressions, clicks, ctr.toFixed(2), cost, conversions, costPerConversion.toFixed(2), conversionValue
    ]);
  }

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

  if (aggregatedData.length > 0) {
    sheet.getRange(2, 1, aggregatedData.length, 8).setValues(aggregatedData);
  } 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.