GMC Top Converting Products Issue Checker

/*
Copyright 2024 Krzysztof Bycina, www.LiveAds.pl
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
    http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// --------------------------------------- Configuration:
const EMAIL = ['YOUR EMAIL HERE'];
const MERCHANT_ID = 'MERCHANT CENTER ID HERE';

// Optional Configuration:
const PRODUCTS_LIMIT = 100; // Number of top converting items to fetch
const DAYS_LOOK_BACK = 30; // Number of days to look back on the conversion numbers
// --------------------------------------- End of the configuration



function main() {
  validateEmail(EMAIL);
  validateMerchantID(MERCHANT_ID);

  const topConvertingItems = getTopConvertingItemIDs();
  const allIssues = countIssues(MERCHANT_ID, topConvertingItems);

  allIssues.sort((a, b) => b.conversions - a.conversions);

  logIssues(allIssues); // Log issues

  const emailSent = sendEmails(allIssues);

  if (emailSent) {
    Logger.log("Email sent with issues.");
  } else {
    Logger.log("No issues found - email not sent.");
  }
}


function getTopConvertingItemIDs() {
  const startDate = Utilities.formatDate(new Date(Date.now() - DAYS_LOOK_BACK * 86400000), 
                                          AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
  const endDate = Utilities.formatDate(new Date(Date.now() - 86400000), 
                                       AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');

  const query = `SELECT segments.product_item_id, metrics.conversions, campaign.advertising_channel_type
                 FROM shopping_performance_view
                 WHERE segments.date BETWEEN '${startDate}' AND '${endDate}'
                 ORDER BY metrics.conversions DESC
                 LIMIT ${PRODUCTS_LIMIT}`;

  const report = AdsApp.report(query);
  return [...report.rows()].map(row => ({
    id: row['segments.product_item_id'],
    conversions: row['metrics.conversions']
  }));
}

function countIssues(merchantId, topConvertingItems) {
  let issuesData = [];
  let pageToken;
  const maxResults = 250;

  do {
    const response = ShoppingContent.Productstatuses.list(merchantId, {pageToken, maxResults});
    response.resources.forEach(product => {
      const productId = product.productId.split(':').pop();
      const item = topConvertingItems.find(({ id }) => id.toLowerCase() === productId.toLowerCase());

      if (item && product.itemLevelIssues) {
        issuesData.push({
          id: productId,
          issue: product.itemLevelIssues[0].description,
          conversions: item.conversions
        });
      }
    });

    pageToken = response.nextPageToken;
  } while (pageToken);

  return issuesData;
}

function validateEmail(email) {
  if (!/^\S+@\S+\.\S+$/.test(email)) {
    throw new Error('Invalid email address.');
  }
}

function validateMerchantID(id) {
  if (!/^\d+$/.test(id)) {
    throw new Error('Invalid Merchant Center ID.');
  }
}

function logIssues(issuesData) {
  if (issuesData.length === 0) {
    Logger.log("No issues found.");
    return;
  }

  Logger.log("List of issues:");
  issuesData.forEach(issue => {
    Logger.log(`ID: ${issue.id}, Issue: ${issue.issue}, Conversions: ${issue.conversions}`);
  });
}

function sendEmails(issuesData) {
  if (issuesData.length === 0) {
    return false;
  }

  const subject = `${AdsApp.currentAccount().getName()}: Converting IDs Issues - Look Back: ${DAYS_LOOK_BACK} Days`;
  let htmlContent = '<p>Hi,<br><br>Please see the list of issues:</p><table border="1"><tr><th>ID</th><th>Issue Type</th><th>Conversions</th></tr>';
  issuesData.forEach(issue => {
    htmlContent += `<tr><td>${issue.id}</td><td>${issue.issue}</td><td>${issue.conversions}</td></tr>`;
  });
  htmlContent += '</table>';

  MailApp.sendEmail({
    to: EMAIL.join(','),
    subject,
    htmlBody: htmlContent
  });

  return true;
}