How to quickly SEO optimize and update H1 and Title page headers in Wordpress, using Excel and SQL

I've recently bought a few Wordpress sites. I was doing SEO optimization, and noticed that on too many pages the H1 and Title headings are not well SEO optimized. I decided to update them en masse to increase traffic to the site. In the article I described the algorithm and saved useful formulas for the future.

Usually I make sites on CMS MODX Revolution, and there is a nice Collections extension, where you can quickly edit page fields in the form of a table.

Unfortunately, I haven't found anything similar for Wordpress. By default, the Title field is not displayed in the quick edit form in the post table. And in general, I don't like the post table in Wordpress. But I guess it's a matter of taste.

So I came up with a quick way to update the H1 and Title of posts in Excel. In doing so, I wrote an Excel auto-check Title function that controls the number of characters and stop characters that cannot be used in the Title.

I would like to note that these sites have the Yoast SEO plugin installed. In the instructions, the database queries are written for this plugin. Also, before making any changes in this way make a backup of the database. I know a lot about website development. Even not knowing Wordpress very well, I have a good understanding of SQL and can fix errors quickly. If you don't have a specialist around - take care of yourself and make a backup.

Step 1 - SQL query

First we need to upload all published records from the site database to Excel. It will be most convenient to do this in PHPMyAdmin. Look on your hosting site, how to enter PHPMyAdmin. The way to access it may differ depending on your hosting control panel.

In PHPMyAdmin you need to execute a SQL query to select posts from the database:

SELECT 
	`p`.`ID`, 
	`p`.`post_title`, 
	`seo`.`meta_value` AS `meta_title`, 
	`yoast`.`permalink` AS `URL`, 
	'wp' AS `prefix` 
FROM `wp_posts` `p`
LEFT JOIN `wp_postmeta` `seo`
	ON `p`.`ID` = `seo`.`post_id` 
		AND `seo`.`meta_key` = '_yoast_wpseo_title'
LEFT JOIN `wp_yoast_indexable` `yoast`
	ON `p`.`ID` = `yoast`.`object_id` 
		AND `yoast`.`object_sub_type` = 'post'
WHERE `p`.`post_type` = 'post' 
	AND `p`.`post_status` = 'publish'

Warning - I have specified the standard table prefix wp_, on your site it can be changed for security reasons. You can view it in PHPMyAdmin.

Here we select posts from the wp_posts table and attach the wp_postmeta table to it. The wp_postmeta table stores additional fields for posts, one of which is the Yoast Meta Title. Also join the table wp_yoast_indexable, because I want to have the actual URLs in the Excel spreadsheet.

In addition, select the table prefix as a string. This is necessary to simplify the creation of SQL queries in Excel formulas.

Step 2 - Export data

Now you need to export the resulting data to a CSV file (you can open it as an Excel spreadsheet). Click "Export" in the panel below the query results table.

Select "Custom" export option to see additional settings. Select "CSV for MS Excel" from the "Format" menu.

Compression can be left on, especially for large data. But for small volume (up to 5000 lines) you can turn it off to avoid unpacking the archive.

Click "Go" - the file download will start.

Step 3 - Preparing the table

Open the downloaded file in Excel. Now you need to prepare a table for easy editing and updating of data in the database.

Select any cell with data. Convert the range into a formatted table using the Ctrl+T hotkeys. The "Table with headers" checkbox must be active.

Copy the code below and paste into any cell in the empty column to the right of the table. It should automatically add to the formatted table and the formula should fill the entire column.

="("&LEN([@[meta_title]])&") "&IF(LEN([@[meta_title]])<70;"Too short Title;";"")&IF(LEN([@[meta_title]])>150;"Too long Title;";"")&IF(ISERROR(SEARCH("!";[@[meta_title]];1));"";"Contains '!';")&IF(ISERROR(SEARCH(".";[@[meta_title]];1));"";"Contains '.';")&IF(ISERROR(SEARCH("~?";[@[meta_title]];1));"";"Contains '?';")&IF(ISERROR(SEARCH(":";[@[meta_title]];1));"";"Contains ':';")&IF(ISERROR(SEARCH("_";[@[meta_title]];1));"";"Contains '_';")&IF(ISERROR(SEARCH("(";[@[meta_title]];1));"";"Contains '(';")&IF(ISERROR(SEARCH(")";[@[meta_title]];1));"";"Contains ')';")&IF(ISERROR(SEARCH("[";[@[meta_title]];1));"";"Contains '[';")&IF(ISERROR(SEARCH("]";[@[meta_title]];1));"";"Contains ']';")&IF(ISERROR(SEARCH("{";[@[meta_title]];1));"";"Contains '{';")&IF(ISERROR(SEARCH("}";[@[meta_title]];1));"";"Contains '}';")&IF(ISERROR(SEARCH("/";[@[meta_title]];1));"";"Contains '/';")

This formula is needed to check the SEO rules for writing the Title of the page. It contains the following rules:

• Length
  
  • at least 70 characters
  • max 150 characters
• Stop characters
  
  • dots
  • exclamation points
  • question marks
  • colons
  • underscore
  • parentheses
  • curly brackets
  • square brackets
  • slashes

You can adjust the length of the Title depending on your SEO strategy. For me, these are the numbers that seem to be optimal. Search engines display the first 50-60 characters in the SERP. That's why you should put your keyword query at the beginning of the Title. Everything else we can use to increase the relevancy of the page.

Stop words in the Title prevent the search engine from ranking it correctly. For example, the dot symbol breaks the title into two "passages". These are two independent parts that the search engine evaluates separately. 

The formula checks for these errors and displays a notification. It also outputs the number of characters in the Title at the beginning.

In the next free column we add a formula for generating SQL queries to update H1 headings.

="UPDATE `"&[@[prefix]]&"_posts` SET `post_title` = '"&[@[post_title]]&"' WHERE `ID` = "&[@ID]&";"

In the next column - the formula for generating SQL queries to change Meta Title in two tables.

=IF([@[meta_title]]<>"NULL";"UPDATE `"&[@prefix]&"_yoast_indexable` SET `title` = '"&[@[meta_title]]&"' WHERE `object_id` = "&[@ID]&" AND `object_type` = 'post'; UPDATE `"&[@prefix]&"_postmeta` SET `meta_value` = '"&[@[meta_title]]&"' WHERE `meta_key` = '_yoast_wpseo_title' AND `post_id` = "&[@ID]&";";"")

Now you can edit H1 and Title directly in Excel, highlight actual ones with color. Site pages can be opened from the URL column.

If you have a working table for SEO optimization of the site - you can adapt the formulas to it and also quickly update the data on the site, saving time on manual editing through the admin panel.

Step 4 - Update Data

Select and copy the cells from the rows and columns where you changed the values. Go back to the SQL query execution window in PHPMyAdmin, paste the copied queries and execute the script. Check the changes on the site. If you have page caching plugins installed, you may need to refresh the cache. If something is broken, restore the backup.