BrightonSEO 2023 presentation

				
					/**
* Copyright Pixel Nordic AB
*
* This script is designed to compare and summarize SEO-related data
* between the latest two timestamped files within a specific project folder.
* The comparison results are sent via email.
*
* More details about the usage can be found at
* https://www.pixel.se
*/


// Configuration parameters
const EMAIL_RECIPIENT = "your@email.com";
const ROOT_FOLDER_NAME = "Screaming Frog SEO Spider";
const CLIENT_NAME = "Client Name"; // Only Used for email
const MAIN_FOLDER_NAME = "Project Name";
const SCHEDULE_FOLDER_NAME = "Task Name";
const FILE_NAME = "internal_all"; // Should be consistent between crawls
const COMPARE_FIELDS = ["Unique Inlinks", "GA Transaction Revenue", "GA Goal Conversion Rate All", "GA Users", "GA Sessions", "GA4 Conversions", "Clicks", "Impressions", "Ahrefs RefDomains - Exact"];


/**
* Main function to initiate the comparison and email sending.
*/
function compareAndSendEmail() {
try {
Logger.log('Starting the comparison process...');


// Configure the folder navigation
Logger.log('Configuring the folder navigation...');
const rootFolder = DriveApp.getRootFolder();
const mainFolder = getFolderByName(rootFolder, ROOT_FOLDER_NAME);
const projectFolder = getFolderByName(mainFolder, MAIN_FOLDER_NAME);
const scheduleFolder = getFolderByName(projectFolder, SCHEDULE_FOLDER_NAME);


// Extract the timestamped folders
Logger.log('Extracting the timestamped folders...');
const timestampedFolders = getTimestampedFolders(scheduleFolder);


// Retrieve and compare data from the latest two timestamped files
Logger.log('Building the comparison summary...');
const summary = buildComparisonSummary(timestampedFolders, COMPARE_FIELDS);


// Find the draft email template
Logger.log('Fetching the draft email template...');
const draft = getDraftEmail("SEO Comparison Summary: {{client_name}}");


if (!draft) {
Logger.log('Draft email template not found.');
return;
}


// Send the email with the summary using the draft template
Logger.log('Sending the comparison summary via email...');
sendFormattedEmail(draft, summary, EMAIL_RECIPIENT);


Logger.log('Comparison and email sending completed successfully.');
} catch (err) {
Logger.log('Failed with error: ' + err.message);
}
}


/**
* Function to retrieve a draft email by subject line.
* @param {string} subjectTemplate - The subject template string including placeholders.
* @return {object} An object containing the draft, attachments, and inline images, or null if not found.
*/
function getDraftEmail(subjectTemplate) {
const drafts = GmailApp.getDrafts();
for (let draft of drafts) {
if (draft.getMessage().getSubject() === subjectTemplate) {
const allInlineImages = draft.getMessage().getAttachments({includeInlineImages: true, includeAttachments: false});
const attachments = draft.getMessage().getAttachments({includeInlineImages: false});
const htmlBody = draft.getMessage().getBody();
const imgObj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj), {});
const imgExp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');
const matches = [...htmlBody.matchAll(imgExp)];
const inlineImagesObj = {};
matches.forEach(match => inlineImagesObj[match[1]] = imgObj[match[2]]);


return {
draft: draft,
attachments: attachments,
inlineImages: inlineImagesObj
};
}
}
return null;
}


/**
* Function to send a formatted email, replacing placeholders with provided summary data and recipient information.
* @param {object} emailTemplate - The object containing draft, attachments, and inline images.
* @param {string} summaryData - The summary data to be included in the email body.
* @param {string} recipient - The recipient's email address.
*/
function sendFormattedEmail(emailTemplate, summaryData, recipient) {
const message = emailTemplate.draft.getMessage();
const subject = message.getSubject().replace("{{client_name}}", CLIENT_NAME);


// Get the HTML body for replacing the placeholders
let htmlBody = message.getBody();


htmlBody = htmlBody
.replace(/{{contact_name}}/g, recipient)
.replace(/{{client_name}}/g, CLIENT_NAME)
.replace(/{{summary}}/g, summaryData);


MailApp.sendEmail({
to: recipient,
subject: subject,
htmlBody: htmlBody, // Use the modified HTML body with placeholders replaced
inlineImages: emailTemplate.inlineImages // Include inline images
});


}


/**
* Function to retrieve a folder by name from a parent folder.
* @param {Folder} parentFolder - Parent Google Drive folder.
* @param {string} folderName - Name of the folder to retrieve.
* @return {Folder} The found folder.
*/
function getFolderByName(parentFolder, folderName) {
let folders = parentFolder.getFoldersByName(folderName);
if (folders.hasNext()) {
return folders.next();
} else {
throw new Error('Folder not found: ' + folderName);
}
}


/**
* Function to extract and sort timestamped folders.
* @param {Folder} scheduleFolder - Schedule folder containing timestamped folders.
* @return {Array} Array of sorted timestamped folders.
*/
function getTimestampedFolders(scheduleFolder) {
let folders = scheduleFolder.getFolders();
let timestampedFolders = [];


// Collect timestamped folders
while (folders.hasNext()) {
let folder = folders.next();
let dateParts = folder.getName().split('.').map(Number); // Split the folder name into parts
let date = new Date(dateParts[0], dateParts[1] - 1, dateParts[2], dateParts[3], dateParts[4], dateParts[5]); // Construct the date
timestampedFolders.push({ folder: folder, date: date });
}


// Sort the timestamped folders by date in descending order
timestampedFolders.sort(function(a, b) {
return b.date - a.date;
});


return timestampedFolders;
}


/**
* Function to build the comparison summary for specified fields.
* @param {Array} timestampedFolders - Array of sorted timestamped folders.
* @param {Array} compareFields - Fields to compare.
* @return {string} Comparison summary text.
*/
function buildComparisonSummary(timestampedFolders, compareFields) {
// Get the files (Google Sheets) from the latest two timestamped folders
let latestFile = timestampedFolders[0].folder.getFilesByName(FILE_NAME).next(); // Use constant
let secondLatestFile = timestampedFolders[1].folder.getFilesByName(FILE_NAME).next(); // Use constant


// Open the sheets
let latestSheet = SpreadsheetApp.open(latestFile).getActiveSheet();
let secondLatestSheet = SpreadsheetApp.open(secondLatestFile).getActiveSheet();


// Collect and compare data
let summary = "<b>Comparison Summary between " +
timestampedFolders[0].date.toISOString() +
" and " +
timestampedFolders[1].date.toISOString() + "</b><br><br>";


let latestFolderName = timestampedFolders[0].folder.getName();
let secondLatestFolderName = timestampedFolders[1].folder.getName();


for (let i = 0; i < compareFields.length; i++) {
let field = compareFields[i];
Logger.log('Comparing data for field: ' + field + '...');
let latestData = getDataForField(latestSheet, field);
let secondLatestData = getDataForField(secondLatestSheet, field);
let deltaData = calculateDelta(latestData, secondLatestData, field, latestFolderName, secondLatestFolderName);
// Check if all data is missing
let allDataMissing = deltaData.every(item => typeof item.delta === "string");


if (allDataMissing) {
summary += "<b><i>Top 10 changes for " + field + ":</i></b><br>No comparison data available<br><br>";
} else {
deltaData.sort(function(a, b) { return b.delta - a.delta; });
let top10 = deltaData.slice(0, 10);
summary += "<b><i>Top 10 changes for " + field + ":</i></b><ul>";
for (let j = 0; j < top10.length; j++) {
summary += "<li>" + top10[j].url + ": " + top10[j].delta + "</li>";
}
summary += "</ul><br>";
}
}


return summary;
}




/**
* Function to fetch data for a specific field.
* @param {Sheet} sheet - Google Sheet to read from.
* @param {string} fieldName - Field name to fetch.
* @return {Array} Data for the specified field.
*/
function getDataForField(sheet, fieldName) {
Logger.log('Fetching data for field: ' + fieldName); // Informative message
let folderName = sheet.getParent().getName(); // Get the folder name
let data = [];
let colIdx = findColumnByName(sheet, fieldName);
if (colIdx === -1) {
return data;
}
let rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, colIdx + 2).getValues();
for (let i = 0; i < rows.length; i++) {
let row = rows[i];
let rawValue = row[colIdx];
if (rawValue === undefined || rawValue === "" || isNaN(rawValue)) {
data.push({ url: row[0], value: "missing value", folderName: folderName }); // Include folder name
} else {
let value = parseFloat(rawValue.toString().replace(',', '.'));
if (isNaN(value)) value = 0;
data.push({ url: row[0], value: value, folderName: folderName }); // Include folder name
}
}


return data;
}


/**
* Function to find a column by its name.
* @param {Sheet} sheet - Google Sheet to search.
* @param {string} name - Column name to find.
* @return {number} Index of the found column or -1 if not found.
*/
function findColumnByName(sheet, name) {
Logger.log('Searching for column with name: ' + name); // Informative message
let headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
if (!headers || !headers.length) {
return -1;
}
for (let i = 0; i < headers.length; i++) {
if (headers[i] === name) {
return i;
}
}
return -1;
}


/**
* Function to calculate the delta between two sets of data.
* @param {Array} latestData - Latest data set.
* @param {Array} secondLatestData - Second latest data set.
* @param {string} fieldName - Field name to calculate delta for.
* @return {Array} Array of delta data.
*/
function calculateDelta(latestData, secondLatestData, fieldName, latestFolderName, secondLatestFolderName) {
Logger.log('Calculating delta for field: ' + fieldName);
let deltaData = [];


for (let i = 0; i < latestData.length; i++) {
let latest = latestData[i];
let secondLatest = findSecondLatestByURL(secondLatestData, latest.url);


if (secondLatest) {
let delta = latest.value - secondLatest.value;
let folderName = "timestamped folder"; // Default folder name


if (latest.value === "missing value") {
folderName = latestFolderName || folderName;
} else if (secondLatest.value === "missing value") {
folderName = secondLatestFolderName || folderName;
}


if (latest.value === "missing value" || secondLatest.value === "missing value") {
deltaData.push({
url: latest.url,
delta: "Comparison data missing (no value for " + fieldName + " in " + folderName + ")"
});
} else if (isNaN(delta)) {
deltaData.push({
url: latest.url,
delta: "Comparison data missing (no value for " + fieldName + ")"
});
} else {
deltaData.push({ url: latest.url, delta: delta });
}
}
}


return deltaData;
}


/**
* Helper function to find second latest data by URL
* @param {Array} secondLatestData - Second latest data set.
* @param {string} url - URL to match.
* @return {Object} Matching data or null if not found.
*/
function findSecondLatestByURL(secondLatestData, url) {
for (let j = 0; j < secondLatestData.length; j++) {
let secondLatest = secondLatestData[j];
if (url === secondLatest.url) {
return secondLatest;
}
}
return null;
}