to create a SEO content plan using Google Sheets functions

Content creation and optimization is a constant part of SEO. Content Strategy is essential to achieving more traffic and higher organic rankings.

Google Sheets features make it simple to convert your data on site performance and keyword research into a list for content that needs to be updated or created.

Lower and substitute functions to turn keywords into URLs

Start by adding new content to your plan.

You can convert a list keywords into URLs using some quick functions.

Use the LOWER feature if you have capitalized keywords and want to lowercase them, such as:

• 
=LOWER(A1)


Substitute any spaces with dashes, like:

• 
=SUBSTITUTE(A1," ","-")


The logic behind SUBSTITUTE is as follows:

• Change the contents of this cell.
• This string matches that string
• This string is a good place to start.

You can do both simultaneously:

• 
=LOWER(SUBSTITUTE(A1," ","-"))


Use JOIN in your URL to include folders or categories and separate them using a slash. The JOIN function is similar to CONCATENATE, but allows you to add a character between each cell that you are combining.

List the cells you want to combine and then include the character or string that separates them.

• 
=JOIN("/",A1,A2)


It’s easier to start with the keyword than to manually remove spaces.

Copy this text to plain-text in another column. Use that column to edit your URLs.

You now have your list of target keywords and URLs to add to the content brief or schedule.

Use CONCATENATE or JOIN to change URLs

You might want to know which pages of your website aren’t performing as well and should be updated.

Start by exporting the current performance of your pages from Google Analytics or Search Console.

You might find that your Analytics report exports page paths while the Search Console report exports complete URLs. You need to use VLOOKUP in order to combine all your data into a single report.

You can get your complete URL by using CONCATENATE. This is a way to combine your domain name with the page path.

If you have more than one category or folder in your URL, JOIN can be very useful. JOIN allows you to combine folders by splitting them into columns.

If you want to do the opposite, use SUBSTITUTE to remove your domain. It’s also useful for searching Google Analytics by page path.

Use the MIN and MAX function to find your oldest content

You can plan to edit old pages if you haven’t updated them recently.

The MIN and MAX function will let you know which pages are oldest and newest if you have a list with your content, and dates of publication or updates.

Select the dates you want to use, and then choose the range using MIN or MAX.

• 
=MIN(A1:A10)


You can check your data for older content that matches other criteria, such as articles that don’t rank well. You can add conditions with the MIN IF and MIN IFS functions.

Filter data using AVERAGE IF or SUM IF functions

It’s impossible to update your entire library all at once. You can break the content into categories to determine which series or categories need to be updated first.

The AVERAGE and SUMIF filters are useful for filtering out data from a group of pages or keywords.

These functions are based on the following logic:

• The content of this range.
• This copy/cell matches this.
• Show me the data.

You can use AVERAGEIF to calculate data such as rank and conversion rate. SUM IF is a great way to add up your conversions and sessions.

The cell that is checked can either be a value in absolute terms or it can accept wildcards such as *keyword* for partial matches.

The function can be referenced in a cell. You can also use $ to refer to an absolute row, column, or cell (like $B$1) when you drag the function to another cell.

If you want to compare multiple conditions or IF statements, use the AVERAGE or SUM functions. AVERAGE can contain up to 127 conditions.

These functions are almost the exact opposite of each other.

• Please show me the data that matches my criteria.
• Please check the first set of data.
• My first condition.
• Please check this second list.
• My second condition.
• The list goes on.

Use IFERROR to clean up your spreadsheets and the IF ISBLANK function.

Before you add pages to your calendar, it is a good idea to clean up the spreadsheet.

The #N/A or #ERROR codes can cause your plan to look cluttered and mess up your calculations.

Add IFERROR to any formula in order to change the contents of the cell when there is an error.

• 
=IFERROR("Custom error message",VLOOKUP(...))


It can be changed to “” or “0”, for example.

IF ISBLANK allows you to customize what appears if a blank cell instead of an error.

Both functions are based on the same logic:

• This cell is having a problem.
• Do or show this.
• Do this instead

Finalize your content plan and check your numbers

Spreadsheets can take a lot of manual work out of your hands, but you still have to decide what is best for the users.

It’s still important to have the knowledge to determine when a certain keyword is not relevant to your article, or to decide whether to 301 a page that’s underperforming instead of trying to improve it for the thirtyth time.

SEO is both an art and science. You can spend more time on your art by improving the science with new features.

The first post Create a SEO Content Plan with Google Sheets was published on Search Engine land.