How to find the heavy-hitting competitors via your own search console export

When you’re working on an SEO project, one of the most important steps is competitor analysis. By analyzing your competitors, you can identify what they’re doing right (and wrong), and use that information to inform your own SEO strategy. However, identifying your competitors can be a challenge. There may be many players in your niche, and it can be hard to know who to focus on. One way to identify your competitors is by using your own search console export. By exporting your search console data and analyzing it in a spreadsheet, you can identify the sites that are appearing most frequently for your target queries. In this blog post, we’ll tell you how to find the heavy-hitting competitors via your own search console export

Step 1: Export your search console data

The first step is to export your search console data. To do this, go to your search console account and navigate to the “Performance” report. Set the date range to the last 16 months, and make sure that the “Queries” and “Pages” checkboxes are selected. Then, click the “Export” button and select “Google Sheets” as the export format.

This will create a new Google Sheet with your search console data. Make sure to rename the sheet to “Content Auditor” (or any other name you prefer).

Read Also: Best and Worst Use Cases of ChatGPT for SEO

Step 2: Run the script

function getGOOGLESERPURLS() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Content Auditor');
 var lastRow = sheet.getLastRow();
 var keywords = sheet.getRange('A2:A' + lastRow).getValues().flat().filter(Boolean);
 var cseId = 'YOUR-CSE-ID';
 var apiKey = 'YOUR-API-KEY';

 for (var i = 0; i < keywords.length; i++) {
 var keyword = keywords[i];
 var url = 'https://www.googleapis(.)com/customsearch/v1?key=' + apiKey + '&cx=' + cseId + '&q=' + encodeURIComponent(keyword) + '&num=10';
 var response = UrlFetchApp.fetch(url);
 var results = JSON.parse(response.getContentText()).items;
 var urls = results.map(function(result) {
 return [result.link];
 });
 var numRows = urls.length;
 var numCols = Math.ceil(numRows / 10); // Calculate number of columns needed
 var range = sheet.getRange(i + 2, 7, numRows, numCols);
 range.setValues(urls);

 // Transpose the range to move URLs from columns to rows
 var transposedValues = [];
 for (var j = 0; j < numCols; j++) {
 transposedValues[j] = [];
 for (var k = 0; k < numRows; k++) {
 transposedValues[j][k] = range.getCell(k + 1, j + 1).getValue();
 }
 }
 var newRange = sheet.getRange(i + 2, 7, numCols, numRows);
 newRange.setValues(transposedValues);

 // Check for duplicates and set background color to green
 var urlRange = sheet.getRange(i + 2, 7, numRows, numCols);
 var urlValues = urlRange.getValues();
 var uniqueUrls = new Set();
 for (var j = 0; j < numRows; j++) {
 for (var k = 0; k < numCols; k++) {
 var url = urlValues[j][k];
 if (url !== '') {
 if (uniqueUrls.has(url)) {
 urlRange.getCell(j + 1, k + 1).setBackground('green');
 } else {
 uniqueUrls.add(url);
 }
 }
 }
 }
 }
}

The next step is to run the Google Apps Script provided earlier in this blog post. This script will get the top 10 organic result URLs for each query in column A and spit it out to adjacent columns.

Here’s how to run the script:

• Open the Google Sheet containing your search console data.
• Go to the “Tools” menu and select “Script editor”.
• Paste the script provided earlier in this blog post into the script editor.
• Replace “YOUR-CSE-ID” and “YOUR-API-KEY” with your own CSE ID and API key. You can obtain a CSE ID by creating a Custom Search Engine in your Google Cloud console. You can obtain an API key by enabling the Custom Search API in your Google Cloud console.
• Save the script.
• Go back to your Google Sheets and refresh the page.
• Go to the “Tools” menu and select “Script editor” again.
• Click the “Run” button to run the script.

This will pull the top 10 URLs for each query in column A from your search console data and output them to adjacent columns.

Step 3: Use conditional formatting to highlight duplicates

Now that you have the top 10 URLs for each query in your sheet, it’s time to identify the sites that are appearing most frequently for your target queries. To do this, you can use conditional formatting to highlight duplicates.

• Here’s how to use conditional formatting:
• Select the entire range of URLs in your sheet (excluding the header row).
• Go to the “Format” menu and select “Conditional formatting”.
• In the conditional formatting dialogue box, select “Custom formula is” from the drop-down menu.
• Enter the following formula: =COUNTIF($G$2:$ZZ, G2)>1
• Choose a formatting style to highlight the duplicates (e.g. change the cell background colour to green).
• Click “Done” to apply the formatting.

This will highlight the URLs that appear more than once in your sheet, which indicates that they’re appearing frequently for multiple queries.

Step 4: Evaluate the identified competitors

Now that you have a list of your competitors, it’s time to evaluate them to see how they are performing and what strategies they are using. Here are a few ways to evaluate your competitors:

• Look at their website: Check out their website to see how it is designed, what kind of content they have, how they are presenting their products or services, and how easy it is to navigate. This will give you an idea of their branding strategy and how they are targeting their audience.
• Analyze their backlinks: Use a tool like Ahrefs or SEMrush to analyze your competitor’s backlink profile. Look at the number of backlinks, the quality of those backlinks, and the anchor text they are using. This will give you an idea of how they are building their backlinks and what kind of content they are creating to attract links.
• Check their social media: See how active they are on social media and what kind of content they are sharing. This will give you an idea of their social media strategy and how they are engaging with their audience.
• Look at their search engine rankings: Check their search engine rankings to see which keywords they are ranking for and how they are performing in organic search. This will give you an idea of how they are optimizing their website for search engines.
• Check their paid search campaigns: Look at their paid search campaigns to see what kind of keywords they are bidding on, what their ad copy looks like, and how they are targeting their audience. This will give you an idea of how they are using paid search to drive traffic to their website.

By evaluating your competitors, you can identify their strengths and weaknesses, and use this information to improve your own marketing strategies.

Read Also: Exploring the Possibilities of Voice Search Marketing with ChatGPT

Step 5. Develop a marketing strategy

Once you have a clear understanding of your competitors and their strategies, it’s time to develop your own marketing strategy. Here are a few tips to help you get started:

• Identify your unique selling proposition (USP): Your USP is what sets you apart from your competitors. Identify what makes your products or services unique and focus on that in your marketing.
• Determine your target audience: Who are your ideal customers? What are their needs and pain points? Understanding your target audience will help you create targeted marketing campaigns that resonate with them.
• Choose the right marketing channels: Based on your target audience, choose the marketing channels that will be most effective in reaching them. This may include social media, email marketing, content marketing, paid search, or other channels.
• Develop your messaging: Craft messaging that speaks to your target audience and highlights your USP. This messaging should be consistent across all your marketing channels.
• Monitor and adjust: Monitor the results of your marketing campaigns and adjust your strategy as needed. Use data to inform your decisions and optimize your campaigns for better results.

By following these steps, you can develop a marketing strategy that sets you apart from your competitors and helps you reach your target audience effectively.

Conclusion

Identifying your competitors and evaluating their strategies is an essential part of developing an effective marketing strategy. By using the Google Search Console export and the script we provided, you can easily identify your competitors and evaluate their performance. With this information, you can develop a marketing strategy that sets you apart from your competitors and helps you reach your target audience effectively. Remember to monitor your results and adjust your strategy as needed to achieve the best results.