Budget Adjust Report v2.1 (Single Account)

This script generates a comprehensive report of all your active Google Ads campaigns, focusing on key performance metrics and budget allocation.

Author

Francesco Cifardi
August 07, 2024

Hey, before I leave you to your script I have an announcement to make: you can now get me (and soon my team) to create custom automations for you!

You can book us for a one-off project, or on a monthly basis. We offer:

  • Up to Unlimited requests

  • Unlimited revisions

  • Free technical project analysis 

  • Tailored recommendations

  • High priority support

  • Full access to the source code

  • Whitelabeling

  • 30 days money back guarantee

This is perfect for you if your business relies heavily on Google Ads for revenue, but lacks technical capabilities to automate tasks to increase productivity. Ideal for mid to large Ecommerce, startups that raised funding, agencies, or corporates with small marketing teams.

Platforms we automate: Google Ads, Meta Ads, Linkedin Ads, Native, & more.

The tools we use: Custom scripts, Zapier, Make, n8n, Supermetrics, Funnel, & more.

We’re still working on the website, so if you’re interested in learning more submit a request here. Describe your needs, attach your company website, and let’s see if we’re a fit.

Looking forward to taking your ads game to the next level! 🚀 

Budget Adjust Report v2.1 - What the Script Does

This script generates a comprehensive report of all your active Google Ads campaigns, organizing them into custom groups and focusing on key performance metrics and budget allocation. Here's what it provides:

  1. A complete list of all active campaigns across all types (Search, Display, Video, Shopping, Performance Max, etc.), organized by your custom groups

  2. Current daily budgets and actual spend

  3. Conversion data, including number of conversions and conversion value

  4. Performance ratios, such as percentage of total budget, cost, conversions, and conversion value for each campaign

  5. Proposed new budgets based on both conversion volume and conversion value

Why Use This Script

Optimizing your campaign budgets is crucial for maximizing account performance. This script automates the process of analyzing campaign budget, spend, and conversion data, and proposing budget adjustments based on real data from your Google Ads account. By using this script, you can:

  • Save time on manual budget analysis and calculations

  • Make data-driven decisions about budget allocation

  • Quickly identify high-performing and underperforming campaigns within specific groups

  • Ensure your ad spend is aligned with campaign performance

  • Adapt your budget strategy based on recent performance data

The script uses the last 30 days of data to ensure that budget recommendations are based on recent performance.

How It Works

  1. The script reads campaign group mappings from a "Campaigns" sheet in your spreadsheet

  2. It connects to your Google Ads account and retrieves data for all active campaigns from the past 30 days

  3. It calculates total budget, spend, conversions, and conversion value across all campaigns

  4. For each campaign, it computes what percentage of these totals the campaign represents

  5. Based on these percentages, it calculates two proposed budgets: a) One based on the campaign's share of total conversions b) Another based on the campaign's share of total conversion value

  6. The data is then organized into separate sheets based on your group mappings

Visual Analysis Features: Each group sheet includes conditional formatting in columns G to J (percentage columns) using a yellow color scale. This visual aid helps you quickly identify outlier campaigns:

  • Darker yellow indicates a higher percentage

  • Lighter yellow or white indicates a lower percentage

To keep the formatting, make sure to duplicate the “Template” tab for each campaign group you specified in the “Campaigns” sheet, and rename each duplicate accordingly

This allows you to spot at a glance which campaigns are consuming a large portion of your budget or driving a significant share of your conversions or conversion value.

How to Use It

  1. First, make a copy of this template spreadsheet.

  2. In the "Campaigns" sheet, fill the mapping table by listing your campaigns and their desired group names:

    • Column A header: "Campaign" (with campaign names)

    • Column B header: "Group" (with campaign groups, this can be custom as long as you then rename the tabs accordingly)

  3. Duplicate the “Template” tab for each campaign group, and rename each duplicate according to the group names you provided in the “Campaigns” sheet

  4. In your Google Ads account, create a new script and paste the code below

  5. Replace the 'YOUR_SHEET_URL' placeholder in the script with the URL of your copy of the template

  6. Run the script. It will create/update sheets based on your group names and populate each with the relevant campaign data

  7. Review the reports in each sheet, paying special attention to:

    • Campaigns with high conversion value but low budget allocation

    • Campaigns with high spend but low conversions or conversion value

    • The proposed budgets in columns K and L

  8. Use this data to inform your budget adjustment decisions. You might choose to:

    • Increase budgets for high-performing campaigns that are limited by budget

    • Decrease budgets for underperforming campaigns

    • Investigate campaigns with unusual metrics to identify opportunities or issues

Note: If you prefer a simpler approach, community member Rares Zlavog has created version 1.4 of the original script that adds label-based filtering functionality while maintaining the single-sheet format. You can find it here.

By running this script regularly and acting on its insights, you can continuously optimize your campaign budgets, improving the overall performance of your Google Ads account.

Remember, while the script provides data-driven recommendations, always use your expertise and knowledge of your business goals when making final budget decisions.

Customize this script

Want to an MCC version? Maybe specific filters, or even add AI features? We got you covered!

Submit a request here. Describe your needs, attach your company website, and let’s see if we’re a fit.

The code

/**
 * Created by Francesco Cifardi
 * For issues or questions reach out on Linkedin (https://www.linkedin.com/in/francescocifardi/)
 * Free to use and share, a mention on Linkedin would be appreciated :-)
 * Free Google Ads Scripts Newsletter - 
 * No boring content, no regular emails, only scripts and other automation tools whenever they are ready. 
 * https://medialauncher.beehiiv.com/subscribe
 *
 * Budget Adjustments Report v2.1 (Single Account) 
 *
 * What the Script Does:
 * This script generates a comprehensive report of all your active Google Ads campaigns, organizing them into custom 
 * groups and focusing on key performance metrics and budget allocation. Here's what it provides:
 * - A complete list of all active campaigns across all types (Search, Display, Video, Shopping, Performance Max, etc.), 
 *   organized by your custom groups
 * - Current daily budgets and actual spend
 * - Conversion data, including number of conversions and conversion value
 * - Performance ratios, such as percentage of total budget, cost, conversions, and conversion value for each campaign
 * - Proposed new budgets based on both conversion volume and conversion value
 *
 * Why Use This Script:
 * Optimizing your campaign budgets is crucial for maximizing account performance. This script automates the process 
 * of analyzing campaign budget, spend, and conversion data, and proposing budget adjustments based on real data from 
 * your Google Ads account. By using this script, you can:
 * - Save time on manual budget analysis and calculations
 * - Make data-driven decisions about budget allocation
 * - Quickly identify high-performing and underperforming campaigns within specific groups
 * - Ensure your ad spend is aligned with campaign performance
 * - Adapt your budget strategy based on recent performance data
 *
 * The script uses the last 30 days of data to ensure that budget recommendations are based on recent performance.
 *
 * How It Works:
 * 1. The script reads campaign group mappings from a "Campaigns" sheet in your spreadsheet
 * 2. It connects to your Google Ads account and retrieves data for all active campaigns from the past 30 days
 * 3. It calculates total budget, spend, conversions, and conversion value across all campaigns
 * 4. For each campaign, it computes what percentage of these totals the campaign represents
 * 5. Based on these percentages, it calculates two proposed budgets:
 *    a) One based on the campaign's share of total conversions
 *    b) Another based on the campaign's share of total conversion value
 * 6. The data is then organized into separate sheets based on your group mappings
 *
 * Visual Analysis Features:
 * Each group sheet includes conditional formatting in columns G to J (percentage columns) using a yellow color scale. 
 * This visual aid helps you quickly identify outlier campaigns:
 * - Darker yellow indicates a higher percentage
 * - Lighter yellow or white indicates a lower percentage
 *
 * This allows you to spot at a glance which campaigns are consuming a large portion of your budget or driving a 
 * significant share of your conversions or conversion value.
 *
 * How to Use It:
 * 1. First, make a copy of this template spreadsheet here.
 * 2. In the spreadsheet, rename the "Report" sheet to "Campaigns"
 * 3. In the "Campaigns" sheet, create a mapping table:
 *    - Column A header: "Campaign"
 *    - Column B header: "Group"
 *    - List your campaigns and their desired group names in the rows below
 * 4. In your Google Ads account, create a new script and paste the code below
 * 5. Replace the 'YOUR_SHEET_URL' placeholder in the script with the URL of your copy of the template
 * 6. Run the script. It will create/update sheets based on your group names and populate each with the relevant campaign data
 * 7. Review the reports in each sheet, paying special attention to:
 *    - Campaigns with high conversion value but low budget allocation
 *    - Campaigns with high spend but low conversions or conversion value
 *    - The proposed budgets in columns K and L
 *
 * Use this data to inform your budget adjustment decisions. You might choose to:
 * - Increase budgets for high-performing campaigns that are limited by budget
 * - Decrease budgets for underperforming campaigns
 * - Investigate campaigns with unusual metrics to identify opportunities or issues
 *
 * Note: If you prefer a simpler approach, community member Rares Zlavog [https://www.linkedin.com/in/rares-zlavog-ppc-b66b85113/] has created version 1.4 of the 
 * original script that adds label-based filtering functionality while maintaining the single-sheet format. 
 * You can find it here: [https://docs.google.com/document/d/1cl3fP8XWJnJ3ty34BzAMA64sEjAjmtW2Q-O9-OipJ-M/]
 *
 * By running this script regularly and acting on its insights, you can continuously optimize your campaign budgets, 
 * improving the overall performance of your Google Ads account.
 *
 * Remember, while the script provides data-driven recommendations, always use your expertise and knowledge of your 
 * business goals when making final budget decisions.
 */

function main() {
  var scriptStartTime = new Date();
  var today = new Date();
  var last30Days = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);
  var spreadsheetUrl = 'YOUR_SHEET_URL_HERE';
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  
  // Read campaign mappings
  var campaignMappings = getCampaignMappings(spreadsheet);
  
  // Get all sheets except "Campaigns" and clear their content
  var sheets = spreadsheet.getSheets();
  sheets.forEach(function(sheet) {
    if (sheet.getName() !== "Campaigns") {
      clearSheetContent(sheet);
    }
  });

  // Define headers
  var headers = [
      'Campaign Name', 'Campaign Type', 'Current Daily Budget', 'Cost', 'Conversions', 'Conversion Value',
      '% of Total Daily Budget', '% of Total Cost', '% of Total Conversions', '% of Total Conv Value',
      'Proposed New Budget (Conversions)', 'Proposed New Budget (Conv value)',
      'Start Date', 'End Date', 'Script Run Time'
      ];

  // Define query elements
  var metrics = 'metrics.cost_micros, metrics.conversions_value, metrics.conversions, metrics.impressions, metrics.clicks';
  var campaignFields = 'campaign.name, campaign.id, campaign_budget.amount_micros, campaign.advertising_channel_type';
  var dateCondition = 'segments.date DURING LAST_30_DAYS';
  var enabledCondition = 'campaign.status = "ENABLED"';

  // Construct the GAQL query for all enabled campaigns
  var query = `
    SELECT ${campaignFields}, ${metrics}
    FROM campaign
    WHERE ${dateCondition} AND ${enabledCondition}
    ORDER BY campaign.name
  `;

  // Run the report and get the data
  var report = AdsApp.report(query);
  var rows = report.rows();
  
  var totalDailyBudget = 0;
  var totalConvValue = 0;
  var totalCost = 0;
  var totalConversions = 0;
  var campaignData = [];

  // Process the report data
  while (rows.hasNext()) {
    var row = rows.next();
    var campaignName = row['campaign.name'];
    
    // Skip campaigns not in mapping
    if (!campaignMappings[campaignName]) continue;
    
    var campaignType = row['campaign.advertising_channel_type'];
    var dailyBudget = row['campaign_budget.amount_micros'] / 1000000;
    var cost = row['metrics.cost_micros'] / 1000000;
    var conversions = parseFloat(row['metrics.conversions']);
    var convValue = parseFloat(row['metrics.conversions_value']);

    totalDailyBudget += dailyBudget;
    totalConvValue += convValue;
    totalCost += cost;
    totalConversions += conversions;

    campaignData.push({
      name: campaignName,
      type: campaignType,
      dailyBudget: dailyBudget,
      cost: cost,
      conversions: conversions,
      convValue: convValue
    });
  }

  // Organize campaigns by group
  var groupedData = {};
  for (var i = 0; i < campaignData.length; i++) {
    var data = campaignData[i];
    var groupName = campaignMappings[data.name];
    
    if (!groupedData[groupName]) {
      groupedData[groupName] = [];
    }
    
    var percentOfTotalBudget = data.dailyBudget / totalDailyBudget;
    var percentOfTotalCost = data.cost / totalCost;
    var percentOfTotalConversions = totalConversions === 0 ? 0 : data.conversions / totalConversions;
    var percentOfTotalConvValue = totalConvValue === 0 ? 0 : data.convValue / totalConvValue;
    var proposedBudgetConversions = totalConversions === 0 ? 0 : totalDailyBudget * (data.conversions / totalConversions);
    var proposedBudgetConvValue = totalConvValue === 0 ? 0 : totalDailyBudget * (data.convValue / totalConvValue);

    groupedData[groupName].push([
      data.name,
      data.type,
      data.dailyBudget,
      data.cost,
      data.conversions,
      data.convValue,
      percentOfTotalBudget,
      percentOfTotalCost,
      percentOfTotalConversions,
      percentOfTotalConvValue,
      proposedBudgetConversions,
      proposedBudgetConvValue,
      formatDate(last30Days),
      formatDate(today),
      formatDateTime(scriptStartTime)
    ]);
    
    // Logging for verification
    Logger.log('Campaign: ' + data.name + ' (' + data.type + ') -> Group: ' + groupName);
    Logger.log('  Current daily budget: ' + data.dailyBudget.toFixed(2) + ' (' + formatPercentage(percentOfTotalBudget) + ')');
    Logger.log('  Cost: ' + data.cost.toFixed(2) + ' (' + formatPercentage(percentOfTotalCost) + ')');
    Logger.log('  Conversions: ' + data.conversions.toFixed(2) + ' (' + formatPercentage(percentOfTotalConversions) + ')');
    Logger.log('  Conversion value: ' + data.convValue.toFixed(2) + ' (' + formatPercentage(percentOfTotalConvValue) + ')');
    Logger.log('  Proposed budget (Conversions): ' + proposedBudgetConversions.toFixed(2));
    Logger.log('  Proposed budget (Conv value): ' + proposedBudgetConvValue.toFixed(2));
  }
  
  // Update each group sheet
  Object.keys(groupedData).forEach(function(groupName) {
    var sheet = getOrCreateSheet(spreadsheet, groupName);
    updateSheetWithHeaders(sheet, headers);
    
    // Write data and apply formatting
    if (groupedData[groupName].length > 0) {
      sheet.getRange(2, 1, groupedData[groupName].length, groupedData[groupName][0].length)
           .setValues(groupedData[groupName]);
      applyFormatting(sheet);
    }
  });
}

// New function to read campaign mappings
function getCampaignMappings(spreadsheet) {
var mappingsSheet = spreadsheet.getSheetByName('Campaigns');
if (!mappingsSheet) {
  throw new Error('Campaigns sheet not found');
}

var data = mappingsSheet.getDataRange().getValues();
var mappings = {};

// Skip header row
for (var i = 1; i < data.length; i++) {
  var campaignName = data[i][0];
  var groupName = data[i][1];
  if (campaignName && groupName) {
    mappings[campaignName] = groupName;
  }
}

return mappings;
}

// New function to clear sheet content
function clearSheetContent(sheet) {
var lastRow = Math.max(sheet.getLastRow(), 1);
var lastColumn = Math.max(sheet.getLastColumn(), 1);
if (lastRow > 1) {
  sheet.getRange(2, 1, lastRow - 1, lastColumn).clearContent();
}
}

// New function to get or create sheet
function getOrCreateSheet(spreadsheet, sheetName) {
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
  sheet = spreadsheet.insertSheet(sheetName);
}
return sheet;
}

// New function to update sheet with headers
function updateSheetWithHeaders(sheet, headers) {
if (sheet.getRange("A1").getValue() !== headers[0]) {
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}
}

// New function to apply formatting
function applyFormatting(sheet) {
var numRows = sheet.getLastRow();
if (numRows > 1) {
  // Currency columns (C, D, F, K, L)
  sheet.getRange(2, 3, numRows - 1, 1).setNumberFormat("$#,##0.00");
  sheet.getRange(2, 4, numRows - 1, 1).setNumberFormat("$#,##0.00");
  sheet.getRange(2, 6, numRows - 1, 1).setNumberFormat("$#,##0.00");
  sheet.getRange(2, 11, numRows - 1, 2).setNumberFormat("$#,##0.00");
  
  // Percentage columns (G, H, I, J)
  sheet.getRange(2, 7, numRows - 1, 4).setNumberFormat("0.00%");
  
  // Conversion column (E)
  sheet.getRange(2, 5, numRows - 1, 1).setNumberFormat("#,##0.00");
}
}

function formatPercentage(value) {
  return value;  // Return raw value, formatting applied in sheet
}

function formatDate(date) {
  return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
}

function formatDateTime(date) {
  return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd HH:mm:ss');
}

Have questions or feedback? Hit reply.

Loved it? Share it! 🙂

And if this email was forwarded to you, and you’re not subscribed…