Auditing WordPress Websites for SEO with Yoast and MySQL

Let’s say you run WordPress with the Yoast SEO plugin. As time goes on you add hundreds of posts and pages. Some of these will have strong SEO titles and metas, some less so. It’s impractical to use the WordPress interface to audit them all.

You could use a site audit tool such as Screaming Frog, but this may not identify all content on your site, especially if there are no links to it. But this content could potentially appear in a site search even if not linked.

Here’s a MySQL query which will pull this information straight from your MySQL database so you can export it into an Excel document or CSV file, and hand off to somebody else for auditing.

select p.id, p.post_type, p.post_status, p.post_title, u.display_name as 'author', p.post_modified as 'last_modified', concat('https://www.yoursite.com/', post_name, '/') as 'url',
(select meta_value from wp_postmeta pm where pm.post_id = p.id and pm.meta_key = '_yoast_wpseo_title') as 'seo_title',
(select meta_value from wp_postmeta pm where pm.post_id = p.id and pm.meta_key = '_yoast_wpseo_metadesc') as 'seo_metadesc',
(select meta_value from wp_postmeta pm where pm.post_id = p.id and pm.meta_key = '_yoast_wpseo_focuskw') as 'seo_focus_keyword',
(select meta_value from wp_postmeta pm where pm.post_id = p.id and pm.meta_key = '_yoast_wpseo_content_score') as 'seo_score'
from wp_posts p
inner join wp_users u on p.post_author = u.id
where (post_type = 'page' or post_type = 'post')
and post_status = 'publish'
order by post_type asc, post_modified asc

This only shows published posts (not those in the trash or draft).

This will give you a list of posts and pages, together with their titles, URLs, and Yoast SEO title, meta descriptions, focus keyword and content score.

Enjoy!

Find out about our Search Engine Optimisation and WordPress maintenance services.