How many posts or pages should we publish per month?” Do you have a data-driven answer when your client asks this question? Chances are, you don’t.
However, you can analyze your competitors to determine how many pages they are publishing and updating each month. This will give you an understanding of their content velocity and the types of pages they are creating, which may guide you in establishing a content publishing frequency for your SEO efforts.
The challenge is that there is no tool or simple method to find out how many posts are published or updated by your competitors.
I also didn’t have a straightforward solution until I asked our SEO team to find one. Thaheer Basha, our senior SEO executive spent one day creating this super easy method that anyone can use for free.
Here’s the process for finding the content publishing and updating frequency of your SEO competitors using a sitemap and Google Sheets.
1. Select the Target Site
Identify and choose the competitor website you wish to analyze for content publishing and updating frequency.
2. Locate the XML Sitemap URL
Find the XML sitemap URL of the selected site. This URL typically ends with /sitemap.xml.
Here’s an example of XML sitemap: https://10pie.com/sitemap_index.xml
3. Extract URLs from the Sitemap
Use the XML Sitemap URL Extractor tool available at SEOWL Sitemap Extractor (you may use any other alternative) to extract all URLs from the sitemap.
4. Choose a specific sitemap (Optional)
If you want to check the content updating and modifying frequency of only specific page types such as:
- Posts
- Pages
- Products
- Categories
- Services
Then select the specific sitemap URL and extract the URLs. This filtering will provide greater clarity on the competitor’s content strategy.
Here’s an example of post sitemap: https://10pie.com/post-sitemap.xml
5. Input URLs into Google Sheets
Paste the extracted URLs into a new Google Sheet.
6. Create Additional Columns
In the Google Sheet, create two additional columns:
- Column B: Published Date
- Column C: Modified Date
7. Access Google Apps Script
Click on ‘Extensions’ in the Google Sheet menu and select ‘Apps Script’ to open the script editor.
8. Insert and Run the Script
Copy and paste the following script code into the Apps Script editor:
function getPublishedDatesAndModifiedDates() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var urls = sheet.getRange('A2:A' + sheet.getLastRow()).getValues(); // Get all URLs in column A
var dates = [];
var modifiedDates = [];
for (var i = 0; i < urls.length; i++) {
var url = urls[i][0];
if (url) {
try {
var response = UrlFetchApp.fetch(url);
var html = response.getContentText();
var publishedDate = extractPublishedDate(html);
var modifiedDate = extractModifiedDate(html);
dates.push([publishedDate]);
modifiedDates.push([modifiedDate]);
} catch (e) {
dates.push(['Error']);
modifiedDates.push(['']);
}
} else {
dates.push(['']);
modifiedDates.push(['']);
}
}
sheet.getRange(2, 2, dates.length, 1).setValues(dates); // Place published dates in column B starting from row 2
sheet.getRange(2, 3, modifiedDates.length, 1).setValues(modifiedDates); // Place modified dates in column C starting from row 2
}
function extractPublishedDate(html) {
var date = '';
// Search for the datePublished schema
var datePublishedMatch = html.match(/"datePublished"\s*:\s*"([^"]+)"/);
if (datePublishedMatch && datePublishedMatch[1]) {
date = datePublishedMatch[1];
} else {
// Fallback to searching meta tags if datePublished schema is not found
var metaTags = html.match(/<meta[^>]*>/g);
if (metaTags) {
for (var i = 0; i < metaTags.length; i++) {
var tag = metaTags[i];
if (tag.indexOf('property="article:published_time"') > -1 || tag.indexOf('name="date"') > -1 || tag.indexOf('itemprop="datePublished"') > -1) {
var match = tag.match(/content="([^"]*)"/);
if (match) {
date = match[1];
break;
}
}
}
}
}
return date || 'Date not found';
}
function extractModifiedDate(html) {
var modifiedDate = '';
// Search for the modified date in meta tags
var metaTags = html.match(/<meta[^>]*>/g);
if (metaTags) {
for (var i = 0; i < metaTags.length; i++) {
var tag = metaTags[i];
if (tag.indexOf('property="article:modified_time"') > -1) {
var match = tag.match(/content="([^"]*)"/);
if (match) {
modifiedDate = match[1];
break;
}
}
}
}
return modifiedDate || '';
}
Run the script that you just pasted.
9. Wait for Data Processing
Allow a few minutes for the script to run. The Google Sheet will be updated automatically with the published and modified dates for all the page URLs.
Here’s the sample result:
9. Upload the CSV File to Claude AI (or ChatGPT)
Upload the prepared CSV document to Claude or ChatGPT and include a similar prompt:
“Create a chart showing the number of pages published per month vs. pages modified per month for the last 6 months. Also, show the average number per month for content publishing and content modifying. Also, mention the site name in the chart.”
This is what the final chart will look like:
Final words
By following these steps, you will be able to effectively analyze the content publishing and updating frequency of competitor websites, providing valuable insights that can enhance your own content strategy and SEO efforts.