BrightonSEO 2023 presentation

Building an Automated Content Strategy: Practical Tips and Best Practices

Voxel’s CEO Daniel Axelsson spoke at BrightonSEO 2023. In his talk, Daniel gave actionable advice for organizations looking to implement automation in their content strategies and workflows.

He shared practical tips, tools, and resources for building an effective automated content strategy so you can overcome challenges of manual curation, improve efficiency, and achieve scalability.

See the presentation below or download the PDF.

The presentation

When working with automation of any kind I think it’s important to define your objectives and break them down into essential parts of what you are trying to accomplish. When we decided to build an automated content strategy we found that some parts of the strategy didn’t make sense to automate, at least not in the sense we first thought. 

When we broke down our strategy into pieces, we found that different parts of the process had different needs so we started to divide our automation into three parts. 

  • Reactive
  • Preemptive
  • Proaction

This makes sense because our content strategies is almost always a combination of the above since we plan for actions to be taken in advance as well as acts on data and insights continuously to improve high performing or under performing content. 

Just like any SEO professional, we are equipped with an extensive array of tools at our disposal and finding the right ones is the tricky part, afterall most often the right tool is out there.. we just don’t know it yet. 

For this how to guide we’ve decided to go with an SEO tech/tool stack consisting of: 

  • Ahrefs
  • Google Search Console
  • ConteentKing
  • Voxel
  • ChatGPT
  • Screamingfrog
  • Google Analytics
  • Google Apps script

Now let’s dive into the Reactive part of our strategy

Reactive: acting after something has occurred

Scheduled crawl and email alerts

In this part of the presentation we will look into how we can leverage scheduled crawls in Screaming frog to aggregate data from multiple sources that we can use as foundation.

The first thing we do is to open Screaming Frog and make sure that we have installed the latest version. Then you navigate to “File” on Mac and find scheduling in the drop down. Here you select add and a new dialouge will open and now you can select names, description, time and frequency for your recurring project. 

Visit the next tab called Start options were you select your crawl mode as either spider or list. And if you select spider please provide the website to be crawled and if you go with list add your list of urls. 

Now we head to the API section and connect relevant API’s. For this presentation I went with Google Analytics, Google Search Console and Ahrefs. It’s literally a couple of clicks to connect the API’s, just sign in to the valid account and allow permission. 

Next we head to Export and select headless which is required for exports and then connect your Google Drive so the data can be exported to the cloud where we can leverage it. Also remember to change Format to gsheet. 

In the slide above you can the output from our crawl where the colors illustrate the different sources: 

Green – Screaming Frog data

Yellow – Google Analytics data

Grey – Google Search Console data

Orange – Ahrefs data

Next we need to define our filter criteria which essentially means, we need to figure out which data we want and how we want it. I almost always include comparisons in my filter criteria since that makes sense for me and examples here could be:

  • Pages that have gained referring domains vs. previous period (Ahrefs)
  • Pages that have gained internal links vs. previous period (ScreamingFrog)
  • Pages that have gained clicks vs. previous period (GSC) 
  • Pages that have gained revenue vs. previous period (GA) 

You have to define your own filter criteria to suit your needs but if I were to give some pointers here I would say that looking at losses and gains within your defined period (daily, weekly or monthly etc) and remember that when you’ve 1 years worth of data which happens quicker then you think you can incorporate that to gain insights on how pages performed at that time last year which will provide even more insights. 

When our filter criteria is done and we know what we want we need to make it actionable and in this case we want to use Google Apps Script to read our recurring data and send us and email (daily, weekly or monthly) containing our winning or losing pages depending on your criteria (or both!). 

Here you can find the Google Apps Script to achieve this: 

1. We start by navigating to gmail.com and by creating a draft email. 

Note: It is important to use the correct subject as this will later be used to identify the correct email in apps script. Also make sure to include the necessary placeholders (marked by {{}} in the email draft).

Gmail Draft
Gmail Draft

2. Now that we have the email draft set up it is time to set up our apps script project:

First navigate to script.google.com 

Click on “Create new project”

Copy the code below and paste it into your new project

Note: Make sure to edit the “configuration patterns” so that they match those used in Screaming Frog. 

Now we need to create a time-based trigger so that it runs daily, weekly or monthly based on our needs.

Note: We need to make sure that the script runs after the Screaming Frog crawl has finished, so if you run the crawl every Monday it could be better to run the script on Tuesday just to give screaming frog enough time to finish the crawl

A weekly trigger that runs the script once every week
A weekly trigger that runs the script once every week

Code to copy:

				
					/**
* 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;
}

				
			

This is what the end product looks like and as I mentioned on stage during BrightonSEO, for me this a really nice automation workflow serving me with data that I’ve defined by email on a weekly basis allowing me to make the decision whether or not we should act on it.

More examples of the end product above and as you can see we’ve internal links as well as referring domains, however we had to censor/remove sensitive client data from GSC & GA for this presentation.

Real time monitoring and alerts:

And one of the first things we do is to setup real-time monitoring for our domain. It’s worth to put some time into setting this up so you can define your alerts by important pages, markets, page types and the whole website as well. 

This will, if you allow it to, notify you when titles, descriptions, headings and body content is changed, removed or added which is crucial for you to act in advance when shit happens, and shit always happen. As I mentioned during my talk, the way you set this up will really make it or break it for you. Be mindful of which alerts that are crucial that you want to be alerted about via email (super important stuff) and which alerts that are nice to know and should just be sent a slack channel or similar called “client-name-alerts”.

Preemptive: taken as a measure against something possible

SEOpionage

Here our objective is crystal clear: Monitor your competitors and setup alerts to stay informed about changes and decisions they are making

SEOpionage

With that being said I think it’s important to remember that this is sort of a gray area and wether you should implement something like this should be a very conscious decision. In the example above I’ve setup a mindful* contentking crawl for some competitors allowing me to be notified when they add or make changes to body content or if they shift their internal linking efforts, allowing us to act on their moves in advance. The alerts from all the different go in to one slack channel named “client-competitor-alerts” having it all in one place for when it’s needed.

Proactive: taking steps in advance to seize opportunities

Proactive

The next step is the proactive part of our strategy which is essentially taking steps in advance to seize opportunities.

Keywords are the Backbone in our content strategy

Keywords are the backbone of our strategy. It may sound simple, but keyword research is at the core of what we do.

So, the first step is to gather keyword data from various sources. These sources can include your own website data, Google Ads data, your search console data, and more. Be creative in your approach; for instance, if you have an internal search engine with user queries, leverage that data. You can even brainstorm ways to obtain as much relevant data as possible.

When we have all our data ready, we typically follow our usual process of merging files and starting the VLOOKUP…

However, in this particular case, we take a different approach.

Once we have collected all our data, the next step isn’t the traditional merging of files and using VLOOKUP. Instead, we’ll streamline our research using Voxel. This approach allows us to consolidate all our projects and data in one place. Voxel was designed with efficiency in mind, facilitating easy project sharing, translations, and project copying. This streamlining approach enhances our workflow.

After consolidating data from various sources and importing it into Voxel, the next step is to perform in-depth analysis. By “in-depth,” we mean focusing on the most critical keywords – the priority ones. How you define importance can vary, whether it’s based on return on ad spend, revenue, profit margin, or volume. Identify keywords associated with your definition of importance and explore long-tail keywords, “people also ask” questions, and related terms to gain a comprehensive understanding.

In some cases, it’s valuable to conduct programmatic SEO research. In the image provided, a client’s product feed was used to generate a vast number of potential keywords through a combination search. This process yielded an additional 15,325 keywords with volume, complementing an already extensive dataset of over 50,000 keywords. Programmatic SEO research can be a powerful tool for expanding your keyword set.

Now that we have gathered a substantial number of keywords, the challenge is to make sense of this data. Keyword clustering is the key to organizing this dataset. The goal is to group keywords into topics, as this aligns with our objective of owning topical relevance. In Voxel, you can choose to use a semi-automatic tagging approach, where you define your data model, create groups, and add tags to streamline the clustering process. I opted for fully automated tagging, enabling Voxel to tag all keywords based on an algorithm that compares search engine result pages between keywords. Depending on your criteria, keywords are either clustered together or separated.

Automatic content plan

Okay, so now we have had all our keywords clustered and tagged so they make sense. The next step for us is to generate a content plan in Voxel. You are allowed to do this automatically, essentially taking away all the groundwork for you, which I did in this case. As you can see on the slide above, this rendered in 552 to optimize, 1,250 URLs to create, and approximately 1,000 URLs were okay.

For me, this is really powerful. And, just to put this into context, we have conducted a keyword research project with well over 50,000 keywords. We have performed a ranking check to see how we perform on these keywords, and we are close to the keywords so they make sense to us. We have even managed to generate a full content plan with recommendations that we can work on for the coming year without a problem. And we did all this in Voxel in less than 4 hours. Less than 4 hours of work to conduct all this research and get all these insights. That’s half a day; it’s ridiculous.

Do you see the power and scalability in Voxel? If you ask me, this is completely brilliant.

But it doesn’t end there. We also have our content plan, which contains our carefully chosen keywords. Of course, Voxel goes even further by offering a ChatGPT integration. With this integration, Voxel allows you to incorporate your system prompt, providing essential context. This is where you can upload all your brand book, brand guidelines and any content related to your brand that you want to be associated with.

Additionally, we have the content prompt feature. Essentially, it operates on the principle of “what you type is what you get.” If you request a specific type of text, that’s precisely what you’ll receive.

What makes our system truly stand out is our use of brackets for primary and secondary keywords. This innovative approach makes it easy for you to generate content at scale while ensuring that it contains your primary and secondary keywords.

Please see the slide above for some examples regarding flapper dress, which Voxel has created in collaboration with Chat GPT. As you can see, we have an H1 header, a paragraph, an H2 header, another paragraph, and another H2 header, followed by a paragraph. If you scroll down to the end you’ll have title and meta description as well that we could use on a potential landing page.

There are numerous brilliant tools available, and it’s entirely possible to accomplish these tasks without Voxel. As depicted in the slide above, you can conduct your keyword research using your favorite tool. Additionally, you can organize your keywords within the tool of your preference. You have the flexibility to devise your content plan wherever you find most convenient. Subsequently, you can publish your content. However, it’s important to note that accomplishing these tasks outside of Voxel may require a longer timeframe compared to executing everything within the Voxel platform.

Key Takeaways

Define Your Needs: Think about your process and needs and which steps you should automate

Common Sense Prevails: Don't let automation replace thinking. Trust your skills.

Common sense prevails

Tool Assessment: Establish an assessment process for new tools and consistently stick to it.

Thank You!

Thank You!
Scaling keyword research and making sense of the data

BrightonSEO 2024 presentation

Scaling keyword research and making sense of the data Voxels CEO Daniel Axelsson had the opportunity to speak about keyword research and keyword analysis at scale at BrightonSEO. In this blog post we’ll cover his talk. Read about the talk

brightonSEO

Speaking at brightonSEO again

Our CEO, Daniel Axelsson, will be speaking at BrightonSEO! Join us for his session “Building an automated content strategy: practical tips and best practices”. … Read More