Google Ads - user journey n grams script

function main() {
  var spreadsheetUrl = 'YOUR_SHEET_URL_HERE';
  var ss = SpreadsheetApp.openByUrl(spreadsheetUrl);

  // Log enabled campaigns first
  logEnabledCampaigns();

  // Configuration Settings
  var config = {
    campaignNames: ["Campaign 1", "Campaign 2"], // Add your campaign names here
    enableAdAssetProcessing: true,
    enableLandingPageProcessing: true
  };

  if (config.enableAdAssetProcessing) {
    processAdAssets(ss, config);
  }

  if (config.enableLandingPageProcessing) {
    processLandingPages(ss, config);
  }

  Logger.log('Report generation completed!');
}

function logEnabledCampaigns() {
  Logger.log('=== Enabled Campaigns ===');
  
  var query = "SELECT campaign.name " +
              "FROM campaign " +
              "WHERE campaign.status = 'ENABLED' " +
              "ORDER BY campaign.name";
              
  var report = AdsApp.report(query);
  var rows = report.rows();
  
  var campaignCount = 0;
  while (rows.hasNext()) {
    var row = rows.next();
    Logger.log(row['campaign.name']);
    campaignCount++;
  }
  
  Logger.log('Total enabled campaigns: ' + campaignCount);
  Logger.log('=====================');
}
function buildCampaignFilter(campaignNames) {
  if (!campaignNames || campaignNames.length === 0) return "";
  
  // Escape single quotes in campaign names and wrap each in single quotes
  var escapedNames = campaignNames.map(name => 
    "'" + name.replace(/'/g, "\\'") + "'"
  );
  
  return "AND campaign.name IN (" + escapedNames.join(", ") + ") ";
}

function processAdAssets(ss, config) {
  var assetSheet = ss.getSheetByName('ad copy analysis');
  var ngramSheet = ss.getSheetByName('ad copy n-grams');

  if (!assetSheet || !ngramSheet) {
    if (!assetSheet) assetSheet = ss.insertSheet('ad copy analysis');
    if (!ngramSheet) ngramSheet = ss.insertSheet('ad copy n-grams');
  }

  assetSheet.clear();
  ngramSheet.clear();

  var assetHeaders = [
    'Campaign Name',
    'Campaign Status', 
    'Ad Group Name',
    'Ad Group Status',
    'Asset Text',
    'Asset Type',
    'Performance Label',
    'Pinned Position',
    'Impressions'
  ];

  var ngramHeaders = [
    'Campaign Name',
    'Ad Group Name',
    'Asset Type',
    'Impressions',
    'N-gram Type',
    'Term',
    'Frequency',
    'Weighted Impressions'
  ];

  assetSheet.getRange(1, 1, 1, assetHeaders.length).setValues([assetHeaders]);
  ngramSheet.getRange(1, 1, 1, ngramHeaders.length).setValues([ngramHeaders]);

  var campaignFilter = buildCampaignFilter(config.campaignNames);
  
  var query = "SELECT " +
    "campaign.name, " +
    "campaign.status, " +
    "ad_group.name, " +
    "ad_group.status, " +
    "asset.text_asset.text, " +
    "ad_group_ad_asset_view.field_type, " +
    "ad_group_ad_asset_view.performance_label, " +
    "ad_group_ad_asset_view.pinned_field, " +
    "metrics.impressions " +
    "FROM ad_group_ad_asset_view " +
    "WHERE segments.date DURING LAST_30_DAYS " +
    "AND ad_group.status != 'REMOVED' " +
    campaignFilter +
    "AND metrics.impressions > 0 " +
    "ORDER BY metrics.impressions DESC";

  var report = AdsApp.report(query);
  var rows = report.rows();
  var assetData = [];
  var ngramData = [];
  
  while (rows.hasNext()) {
    var row = rows.next();
    try {
      var impressions = parseInt(row['metrics.impressions']) || 0;
      var text = row['asset.text_asset.text'];

      assetData.push([
        row['campaign.name'],
        row['campaign.status'],
        row['ad_group.name'],
        row['ad_group.status'],
        text,
        row['ad_group_ad_asset_view.field_type'],
        row['ad_group_ad_asset_view.performance_label'],
        row['ad_group_ad_asset_view.pinned_field'],
        impressions
      ]);

      var ngrams = generateAllNgrams(text, 3);
      Object.entries(ngrams).forEach(function([term, frequency]) {
        ngramData.push([
          row['campaign.name'],
          row['ad_group.name'],
          row['ad_group_ad_asset_view.field_type'],
          impressions,
          term.split(' ').length + '-gram',
          term,
          frequency,
          impressions * frequency
        ]);
      });
    } catch (e) {
      Logger.log('Error processing row: ' + e.toString());
    }
  }

  if (assetData.length > 0) {
    assetSheet.getRange(2, 1, assetData.length, assetHeaders.length).setValues(assetData);
    assetSheet.autoResizeColumns(1, assetHeaders.length);
  }
  if (ngramData.length > 0) {
    ngramSheet.getRange(2, 1, ngramData.length, ngramHeaders.length).setValues(ngramData);
    ngramSheet.autoResizeColumns(1, ngramHeaders.length);
  }
}

function processLandingPages(ss, config) {
  var sheet = ss.getSheetByName("landing page n-grams") || ss.insertSheet("landing page n-grams");
  clearSheet(sheet);

  var headers = [
    "Campaign", 
    "Ad Group", 
    "Landing Page", 
    "Impressions", 
    "Clicks", 
    "Conversions", 
    "N-gram Type", 
    "HTML Tag", 
    "Term", 
    "Frequency",
    "Weighted Impressions"
  ];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  var today = new Date();
  var thirtyDaysAgo = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);

  var campaignFilter = buildCampaignFilter(config.campaignNames);

  var query = "SELECT " +
              "campaign.name, " +
              "ad_group.name, " +
              "expanded_landing_page_view.expanded_final_url, " +
              "metrics.impressions, metrics.clicks, " +
              "metrics.conversions " +
              "FROM expanded_landing_page_view " +
              "WHERE segments.date BETWEEN '" + formatDate(thirtyDaysAgo) + 
              "' AND '" + formatDate(today) + "' " +
              campaignFilter;

  var report = AdsApp.report(query);
  var rows = report.rows();
  var landingPages = {};

  while (rows.hasNext()) {
    var row = rows.next();
    var url = removeQueryParams(row['expanded_landing_page_view.expanded_final_url']);
    var key = JSON.stringify({
      campaign: row['campaign.name'],
      adGroup: row['ad_group.name'],
      url: url
    });

    var impressions = parseInt(row['metrics.impressions']);
    var clicks = parseInt(row['metrics.clicks']);
    var conversions = parseInt(row['metrics.conversions']) || 0;

    if (key in landingPages) {
      landingPages[key].impressions += impressions;
      landingPages[key].clicks += clicks;
      landingPages[key].conversions += conversions;
    } else {
      landingPages[key] = {
        impressions: impressions,
        clicks: clicks,
        conversions: conversions
      };
    }
  }

  var uniqueLandingPages = Object.entries(landingPages)
    .sort((a, b) => b[1].impressions - a[1].impressions)
    .slice(0, 20)
    .map(([key, data]) => ({
      ...JSON.parse(key),
      impressions: data.impressions,
      clicks: data.clicks,
      conversions: data.conversions
    }));

  var urls = uniqueLandingPages.map(page => page.url);
  var contents = fetchMultipleLandingPageContents(urls);

  var outputRows = [];
  uniqueLandingPages.forEach((page, i) => {
    var content = contents[i] || '';
    var combinedContent = Object.values(splitContentByTag(content)).join(' ');
    processNgramsForOutput(combinedContent, page, 'all', outputRows);
  });

  if (outputRows.length > 0) {
    sheet.getRange(2, 1, outputRows.length, headers.length).setValues(outputRows);
    sheet.autoResizeColumns(1, headers.length);
  }
}

function generateAllNgrams(text, maxGrams) {
  var words = text.toLowerCase()
    .replace(/[^\w\s]/g, '')
    .split(/\s+/)
    .filter(word => word.length > 0);
    
  var ngrams = {};
  
  for (var n = 1; n <= maxGrams; n++) {
    for (var i = 0; i <= words.length - n; i++) {
      var ngram = words.slice(i, i + n).join(' ');
      ngrams[ngram] = (ngrams[ngram] || 0) + 1;
    }
  }
  
  return ngrams;
}

function clearSheet(sheet) {
  sheet.clear();
}

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

function removeQueryParams(url) {
  try {
    return url ? url.split('?')[0].split('#')[0] : '';
  } catch (e) {
    return '';
  }
}

function fetchMultipleLandingPageContents(urls) {
  return urls.map(url => {
    try {
      return UrlFetchApp.fetch(url).getContentText();
    } catch (e) {
      Logger.log('Error fetching URL ' + url + ': ' + e.toString());
      return '';
    }
  });
}

function splitContentByTag(content) {
  var tagContent = {
    h1: '',
    h2: '',
    h3: '',
    p: '',
    meta: ''
  };
  
  try {
    // Simple regex-based extraction for each tag type
    tagContent.h1 = extractContent(content, /<h1[^>]*>(.*?)<\/h1>/gi);
    tagContent.h2 = extractContent(content, /<h2[^>]*>(.*?)<\/h2>/gi);
    tagContent.h3 = extractContent(content, /<h3[^>]*>(.*?)<\/h3>/gi);
    tagContent.p = extractContent(content, /<p[^>]*>(.*?)<\/p>/gi);
    tagContent.meta = extractMetaContent(content);
  } catch (e) {
    Logger.log('Error splitting content by tag: ' + e.toString());
  }
  
  return tagContent;
}

function extractContent(content, regex) {
  var matches = content.match(regex) || [];
  return matches
    .map(match => match.replace(/<[^>]+>/g, ''))
    .join(' ')
    .trim();
}

function extractMetaContent(content) {
  var metaMatches = content.match(/<meta[^>]+content="([^"]*)"[^>]*>/gi) || [];
  return metaMatches
    .map(match => {
      var content = match.match(/content="([^"]*)"/i);
      return content ? content[1] : '';
    })
    .join(' ')
    .trim();
}

function processNgramsForOutput(content, pageData, tagType, outputRows) {
  var ngrams = generateAllNgrams(content, 3);
  
  Object.entries(ngrams).forEach(function([term, frequency]) {
    outputRows.push([
      pageData.campaign,
      pageData.adGroup,
      pageData.url,
      pageData.impressions,
      pageData.clicks,
      pageData.conversions,
      term.split(' ').length + '-gram',
      tagType,
      term,
      frequency,
      pageData.impressions * frequency
    ]);
  });
}