Pmax Device Distribution v1.2 (Single Account)

Understand device distribution for your Performance Max campaigns. This script automates the process of analyzing device-specific performance across your PMax campaigns.

Author

Francesco Cifardi
August 20, 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 advertising 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! 🚀 

Pmax Device Distribution v1.2 - Why Use This Script

Understand device distribution for your Performance Max campaigns. This script automates the process of analyzing device-specific performance across your PMax campaigns. This information can help you refine your targeting and budget allocation strategies. By using this script, you can:

  • Save time on manual device performance analysis

  • Make data-driven decisions about device targeting and bid adjustments

  • Quickly identify high-performing and underperforming devices within your PMax campaigns

  • Ensure PMax is not over-spending your budget on unwanted devices

  • Adapt your strategy based on recent performance data across different devices

What the Script Does

This script generates a report of all your active Performance Max campaigns, focusing on device-specific performance metrics over the last 30 days. Here's what it provides:

  • A breakdown of performance data for each PMax campaign, segmented by device (Mobile, Tablet, Desktop)

  • Daily performance metrics including impressions, clicks, conversions, all conversions, and conversion value

  • Data for the last 30 days, ensuring that analysis is based on recent performance

The script populates this data into a Google Sheets report, which includes an interactive dashboard. This dashboard allows you to compare performance across devices, helping identify trends and make informed decisions about your PMax campaign optimization.

How It Works

  1. The script connects to your Google Ads account and retrieves data for all active Performance Max campaigns from the past 30 days.

  2. It segments this data by campaign, date, and device (Mobile, Tablet, Desktop).

  3. All this data is populated into a Google Sheets report, with an interactive dashboard.

How to Use It

  1. Make a copy of this template spreadsheet

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

  3. In the script, locate the spreadsheetUrl variable near the beginning. Replace 'YOUR_SHEET_URL' with the URL of your copy of the template, making sure to keep it wrapped in either single ('') or double ("") quotes.

  4. Run the script. 

  5. Schedule the script to run on a daily basis. 

  6. Review the interactive dashboard, paying special attention to:
    - Devices that are driving a high number of conversions or conversion value
    - Devices with high impressions or clicks but low conversions
    - Any significant disparities in performance across devices for individual campaigns

Use these insights to inform your PMax optimization strategy. You might consider:

  • Tailoring ad assets to better suit the top-performing devices

  • Investigating campaigns with unusual device-specific metrics to identify opportunities or issues

  • Pause campaigns that overspend on unwanted devices for a long period of time

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

Customize this script

Want to an MCC version? Maybe customize the prompt, or specific filters? 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 reachout on Linkedin (https://www.linkedin.com/in/francescocifardi/)
 * Free to use and share, a mention on Linkedin would be appreciated :-)
 * Looking to get new script updates via email? Subscribe to my Free Google Ads Scripts Newsletter. No content, no regular emails, only scripts and other automation tools whenever they are ready.
 * https://medialauncher.beehiiv.com/subscribe
 *
 * Pmax Device Distribution 1.2 (Single Account) Script
 * 
 * Why Use This Script:
 * Understand device distribution for your Performance Max campaigns. This script automates the process of
 * analyzing device-specific performance across your PMax campaigns. This information can help you refine
 * your targeting and budget allocation strategies. By using this script, you can:
 * - Save time on manual device performance analysis
 * - Make data-driven decisions about device targeting and bid adjustments
 * - Quickly identify high-performing and underperforming devices within your PMax campaigns
 * - Ensure PMax is not over-spending your budget on unwanted devices
 * - Adapt your strategy based on recent performance data across different devices
 * 
 * What the Script Does:
 * This script generates a report of all your active Performance Max campaigns, focusing on device-specific
 * performance metrics over the last 30 days. Here's what it provides:
 * - A breakdown of performance data for each PMax campaign, segmented by device (Mobile, Tablet, Desktop)
 * - Daily performance metrics including impressions, clicks, conversions, all conversions, and conversion value
 * - Data for the last 30 days, ensuring that analysis is based on recent performance
 * The script populates this data into a Google Sheets report, which includes an interactive dashboard.
 * This dashboard allows you to compare performance across devices, helping identify trends and make
 * informed decisions about your PMax campaign optimization.
 * 
 * How It Works:
 * 1. The script connects to your Google Ads account and retrieves data for all active Performance Max
 *    campaigns from the past 30 days.
 * 2. It segments this data by campaign, date, and device (Mobile, Tablet, Desktop).
 * 3. All this data is populated into a Google Sheets report, with an interactive dashboard.
 * 
 * How to Use It:
 * 1. First, make a copy of this template spreadsheet:
 *    https://docs.google.com/spreadsheets/d/1oz45a-AeaGA04_Ryhv7K8lcu0KoE-4QK_SWbNJqb6vE/copy
 * 2. In your Google Ads account, create a new script and paste the provided code.
 * 3. In the script, locate the `spreadsheetUrl` variable near the beginning. Replace 'YOUR_SHEET_URL'
 *    with the URL of your copy of the template, making sure to keep it wrapped in either single ('') or
 *    double ("") quotes.
 * 4. Run the script.
 * 5. Schedule the script to run on a daily basis.
 * 6. Review the interactive dashboard, paying special attention to:
 *    - Devices that are driving a high number of conversions or conversion value
 *    - Devices with high impressions or clicks but low conversions
 *    - Any significant disparities in performance across devices for individual campaigns
 * 
 * Use these insights to inform your PMax optimization strategy. You might consider:
 * - Tailoring ad assets to better suit the top-performing devices
 * - Investigating campaigns with unusual device-specific metrics to identify opportunities or issues
 * - Pause campaigns that overspend on unwanted devices for a long period of time
 * 
 * Remember, while the script provides data-driven insights, always use your expertise and knowledge of
 * your business goals when making final optimization decisions.
 */
function main() {
  try {
    const spreadsheetUrl = 'YOUR_SHEET_URL'; // Make a copy: https://docs.google.com/spreadsheets/d/1oz45a-AeaGA04_Ryhv7K8lcu0KoE-4QK_SWbNJqb6vE/copy
    const spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    const sheet = spreadsheet.getSheetByName('data') || spreadsheet.insertSheet('data');
    
    const campaignData = getCampaignData();
    const allDaysData = fillMissingDays(campaignData);
    
    if (allDaysData.length > 0) {
      writeToSheet(sheet, allDaysData);
      Logger.log('Data has been successfully written to the spreadsheet.');
    } else {
      Logger.log('No data retrieved. Check your campaign settings or date range.');
    }
  } catch (error) {
    Logger.log('An error occurred: ' + error.message);
  }
}

function getCampaignData() {
  const yesterday = new Date(new Date().setDate(new Date().getDate() - 1));
  const thirtyDaysAgo = new Date(yesterday.getTime() - 29 * 24 * 60 * 60 * 1000);
  
  const startDate = Utilities.formatDate(thirtyDaysAgo, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
  const endDate = Utilities.formatDate(yesterday, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');

  const campaignQuery = "SELECT campaign.id, campaign.name, " +
    "metrics.impressions, metrics.clicks, metrics.conversions, metrics.all_conversions, metrics.conversions_value, " +
    "segments.date, segments.device " +
    "FROM campaign " +
    "WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' " +
    `AND segments.date BETWEEN '${startDate}' AND '${endDate}'`;

  const campaigns = {};
  let offset = 0;
  const pageSize = 10000;

  while (true) {
    try {
      const reportQuery = AdsApp.report(campaignQuery, {
        apiVersion: 'v17',
        limit: pageSize,
        offset: offset
      });

      const rows = reportQuery.rows();
      let rowCount = 0;

      while (rows.hasNext()) {
        const row = rows.next();
        const date = row['segments.date'];
        const campaignName = row['campaign.name'];
        const device = row['segments.device'];
        const key = `${date}_${campaignName}_${device}`;

        if (!campaigns[key]) {
          campaigns[key] = {
            date: date,
            campaignName: campaignName,
            device: device,
            impressions: parseInt(row['metrics.impressions']),
            clicks: parseInt(row['metrics.clicks']),
            conversions: parseFloat(row['metrics.conversions']),
            allConversions: parseFloat(row['metrics.all_conversions']),
            conversionValue: parseFloat(row['metrics.conversions_value'])
          };
        } else {
          campaigns[key].impressions += parseInt(row['metrics.impressions']);
          campaigns[key].clicks += parseInt(row['metrics.clicks']);
          campaigns[key].conversions += parseFloat(row['metrics.conversions']);
          campaigns[key].allConversions += parseFloat(row['metrics.all_conversions']);
          campaigns[key].conversionValue += parseFloat(row['metrics.conversions_value']);
        }
        rowCount++;
      }

      if (rowCount < pageSize) {
        break;
      }
      offset += pageSize;
    } catch (error) {
      Logger.log('Error fetching data: ' + error.message);
      break;
    }
  }
  
  return Object.values(campaigns);
}

function fillMissingDays(campaignData) {
  const allData = [];
  const yesterday = new Date(new Date().setDate(new Date().getDate() - 1));
  const thirtyDaysAgo = new Date(yesterday.getTime() - 29 * 24 * 60 * 60 * 1000);
  const devices = ['MOBILE', 'TABLET', 'DESKTOP'];
  const campaignNames = [...new Set(campaignData.map(item => item.campaignName))];

  for (let d = new Date(thirtyDaysAgo); d <= yesterday; d.setDate(d.getDate() + 1)) {
    const dateString = Utilities.formatDate(d, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
    
    for (let campaignName of campaignNames) {
      for (let device of devices) {
        const existingData = campaignData.find(item => 
          item.date === dateString && 
          item.campaignName === campaignName && 
          item.device === device
        );

        allData.push({
          date: dateString,
          campaignName: campaignName,
          device: device,
          impressions: existingData ? existingData.impressions : 0,
          clicks: existingData ? existingData.clicks : 0,
          conversions: existingData ? existingData.conversions : 0,
          allConversions: existingData ? existingData.allConversions : 0,
          conversionValue: existingData ? existingData.conversionValue : 0
        });
      }
    }
  }

  return allData;
}

function writeToSheet(sheet, campaigns) {
  // Clear existing content
  sheet.clear();
  
  // Set headers
  const headers = ['Date', 'Campaign Name', 'Device', 'Impressions', 'Clicks', 'Conversions', 'All Conversions', 'Conversion Value'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
  
  // Prepare data for sheet
  const data = campaigns.map(campaign => [
    campaign.date,
    campaign.campaignName,
    campaign.device,
    campaign.impressions,
    campaign.clicks,
    campaign.conversions,
    campaign.allConversions,
    campaign.conversionValue
  ]);
  
  // Write data to sheet
  if (data.length > 0) {
    sheet.getRange(2, 1, data.length, data[0].length).setValues(data);
    
    // Format the sheet
    sheet.getRange(2, 4, data.length, 2).setNumberFormat('#,##0');  // Impressions and Clicks
    sheet.getRange(2, 6, data.length, 2).setNumberFormat('#,##0.00');  // Conversions and All Conversions
    sheet.getRange(2, 8, data.length, 1).setNumberFormat('#,##0.00');  // Conversion Value
    
    // Auto-resize columns
    sheet.autoResizeColumns(1, headers.length);
    
    Logger.log(`Data has been written to the sheet. Total rows: ${data.length + 1}`);
  } else {
    Logger.log("No data to write to the sheet.");
  }
}

Wishing you a great day, with my Meme Of The Week 😬

Have questions or feedback? Hit reply.

Loved it? Share it! 🙂

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