SEO involves constantly creating and optimizing content for your website. Achieving more traffic and better organic rankings means you’ll need a content strategy.
Google Sheets functions make it easy to turn your keyword research and site performance data into a list of content to be created or updated.
Turn keywords into URLs with LOWER and SUBSTITUTE functions
Let’s start by adding some new content to your content plan.
If you have a list of target keywords for new pages, you can turn them into URLs with some quick functions.
Use the LOWER function if your keywords are capitalized and you want them all to be lowercase, as in:
=LOWER(A1)
Use SUBSTITUTE to change any spaces to dashes like:
=SUBSTITUTE(A1,“ ”,“-”)
The basic logic for SUBSTITUTE is:
- Change the content in this cell.
- That matches this string.
- Into this string.
Or do both at once:
=LOWER(SUBSTITUTE(A1,“ ”,“-”))
Use JOIN to include categories or folders in your URL and separate them with a slash. JOIN is like CONCATENATE but lets you add a character or string between each cell you’re combining.
Include the character or string to separate them at the start, then list the cells to combine, like this:
=JOIN(“/”,A1,A2)
You might not want to use the keyword by itself as your final URL, but it’s an easier starting point than manually removing spaces.
Use the Control + Shift + V keyboard shortcut on Windows (or Command + Shift + V on Mac) to copy this to plain text in another column without the functions. Then, use that column to start editing your URLs.
Now you have a list of your target keyword and proposed URL to add to your content brief or content schedule.
Change URLs with CONCATENATE and JOIN functions
Let’s say you want to see which pages on your site aren’t performing well and need to be updated.
You might start by exporting current page performance from Google Analytics and Search Console.
Your Analytics report might export page paths, while your Search Console report exports full URLs. You want to use VLOOKUP to combine your data into one report.
Use CONCATENATE to combine your domain with your page path or slug to get your full URL.
JOIN is helpful if you have multiple categories or folders in your URL. Split each folder into a column, and JOIN will combine them with a slash between them.
Or, if you want to reverse it, use the SUBSTITUTE function to remove your domain from a URL. This is also handy for searching by page path in Google Analytics.
Find your oldest content with MIN and MAX functions
In addition to checking pages that have dropped in rankings, you can also plan to update old pages that haven’t been edited recently.
If you have a list of your content and the published or updated dates, MIN and MAX functions will tell you which pages are the oldest and newest.
Use MIN for the oldest and MAX for the oldest, then select the range for your dates, for instance:
=MIN(A1:A10)
You can also check for your oldest content that meets other criteria from your data, like old articles that aren’t ranking well. Add conditions to check with a MIN IF or MIN IFS function.
Get the daily newsletter search marketers rely on.
Filter your data with AVERAGE IF and SUM IF functions
You can’t tackle your whole content library at once. It can be helpful to break it into groups to see which series or categories must be updated first.
AVERAGE IF and SUM IF are helpful for filtering data for a specific group of pages or keywords.
The basic logic for these functions is:
- If the content in this range.
- Matches this copy/cell.
- Show me this data.
Use AVERAGE IF for data like rank and conversion rate, for example. Use SUM IF to total your sessions and conversions.
The cell to check can be an absolute value or accept wildcards like *keyword* to show content that’s a partial match.
You can also have the function reference content in a specific cell. If you do, you can use $ to reference an absolute cell, row, or column (like $B$1) if you drag your function to other cells.
Use AVERAGE IFS or SUM IFS functions if you have multiple conditions or IF statements you want to check for. AVERAGE IFS can include up to 127 conditions.
The logic for these functions is almost the opposite:
- Show me this data if it matches my criteria.
- Check this first range of data.
- For my first condition.
- Check this second range.
- For my second condition.
- And so on.
Clean up your spreadsheets with IFERROR and IF ISBLANK functions
You might want to clean up your spreadsheet before you add your pages to your content calendar.
#N/A and #ERROR can make your plan look busy or mess up your calculations.
Add IFERROR before any formula to change what will appear in the cell if there is a formula error, like this:
=IFERROR(“Custom error message”,VLOOKUP(…))
You can change it to show 0 or make it blank with “ ”, for example.
IF ISBLANK lets you customize what should appear if a cell is blank instead of throwing an error.
The basic logic for both functions is:
- If this cell has a problem.
- Show or do this.
- Otherwise, do this.
Check your numbers and finalize your content plan
Spreadsheets take much of the manual work out of the equation, but it’s still up to you to discover what’s best for your users.
You’ll still need the expertise to know when a keyword isn’t relevant for your article or to decide to 301 an underperforming page instead of improving it for the 30th time.
SEO is an art and a science. And getting better at the science part with new functions can help you spend more time mastering your art.
Contributing authors are invited to create content for Search Engine Land and are chosen for their expertise and contribution to the search community. Our contributors work under the oversight of the editorial staff and contributions are checked for quality and relevance to our readers. The opinions they express are their own.
