*Updated 28th March 2018, this is a rewrite of how the output is provided so please re read the instructions.
I love the On-page Optimisation in MOZ. This feature allows you to enter your focus keyword against a page for the url you wish to score.
The GetYoastData output can also be used for adding keywords to the Position Tracking/Rankings Overview section of semrush plus may other applications.
Personally I add my focus keywords with a label just to keep it tidy when reporting back. However you can not extract the url from the database as it is created via the get_permalink() or get_page_link() functions.
I created GetYoastData which outputs to a csv (Excel) file that can be downloaded via FTP.
<?php
/**
* Version: 4.0
* Date: 28/03/2018
* Source: https://deanandrews.uk/get-yoast-seo-data/
*
* Note:
* This code is provided "as is" and any expressed or implied warranties, including, but not limited to, the implied
* warranties of merchantability and fitness for a particular purpose are disclaimed.
* In no event shall the regents or contributors be liable for any direct, indirect, incidental, special, exemplary, or
* consequential damages (including, but not limited to, procurement of substitute goods or services; loss of use, data, or
* profits; or business interruption) however caused and on any theory of liability, whether in contract, strict liability, or
* tort (including negligence or otherwise) arising in any way out of the use of this code, even if advised of
* the possibility of such damage.
*/
// Get the key data from the URL
function CheckAccess(){
return @$_GET['key']=='MySecretKEY';
}
// If the key is incorrect block access to data
if (!CheckAccess()){
header('HTTP/1.0 404 Not Found');
exit;
}
// The ouput filename
$out = fopen('Yoast Data Output.csv', 'w');
// Add column headers
fputcsv($out, array("Post ID","Post Type","URL","Focus Key Word","Page Title","Meta Description"));
require('../wp-load.php');
// Get the data
global $wpdb;
$posts = $wpdb->get_results("
SELECT (SELECT `meta_value` FROM `$wpdb->postmeta` WHERE `meta_key` = '_yoast_wpseo_focuskw' AND post_id = $wpdb->posts.ID) AS focuskw,
(SELECT `meta_value` FROM `$wpdb->postmeta` WHERE `meta_key` = '_yoast_wpseo_title' AND post_id = $wpdb->posts.ID) AS title,
(SELECT `meta_value` FROM `$wpdb->postmeta` WHERE `meta_key` = '_yoast_wpseo_metadesc' AND post_id = $wpdb->posts.ID) AS metadesc,
$wpdb->posts.ID, $wpdb->posts.post_type FROM `$wpdb->posts`
WHERE $wpdb->posts.post_status = 'publish'
AND (SELECT `meta_value` FROM `$wpdb->postmeta` WHERE `meta_key` = '_yoast_wpseo_meta-robots-noindex' AND post_id = $wpdb->posts.ID)is null
AND ($wpdb->posts.post_type = 'page' OR $wpdb->posts.post_type = 'post')
");
// Loop through the data and add to the csv output
foreach($posts as $post) {
$permalink = get_permalink($post->ID);
fputcsv($out, array(($post->ID),($post->post_type),($permalink),($post->focuskw),($post->title),($post->metadesc)));
}
// Output the csv file
fclose($out);
?>Instructions:
Grab the code and save it to a file called GetYoastData.php on your desktop
Edit the ‘MySecretKEY’ to any string that you want. This will form a basic security key as part of the URL.
Create a folder in the root directory via FTP (where the wp-config.php file is) for this example we will assume the folder name ‘yoast’
Upload GetYoastData.php to your new folder (yoast) via FTP
Visit page www.YOUR-DOMAIN.COM/yoast/GetYoastData.php?key=MySecretKEY
Refresh your FTP client and you will see a new file named ‘Yoast Data Output.csv’
Download the file Yoast Data Output.csv
I would recommend that you delete your newly created folder (‘yoast’)
Change Log:
Version: 4.0
Date: 28/03/2018
A rewrite of how the data is output, which is no longer output to the screen, you will need to download the csv file via FTP.
Version: 3.0
Date: 02/11/2016
Changed table_prefixes fixed as suggested by Alexander
Added basic security
Version: 2.0
Date: 13/05/2014
Applied changes re get_permalink() as suggested by Joost de Valk
Compatible with WordPress SEO – Version 1.5.3
Version: 1.0
Date: 29/10/2012
Source: https://deanandrews.uk/get-yoast-seo-data/
Massive help mate, you just saved me £79 on Yoast Premium, thanks a bunch!
Hi and thanks. I would love to be able to get the same info (in a .csv) related to my woocommerce products – 3000+ books… If anybody volunteers… 🙂
Hi Alain,
Thanks for the comment. To get the WooCommerce data can be accomplished by editing the SQL so find the line
AND ($wpdb->posts.post_type = ‘page’ OR $wpdb->posts.post_type = ‘post’)and relace with:
AND ($wpdb->posts.post_type = ‘page’ OR $wpdb->posts.post_type = ‘post’ OR $wpdb->posts.post_type = ‘product’)Thank you so much Dean! I’ll try tomorrow…
Hi there,
This seems like exactly what I need, but like others I’m only getting headers.
If I show php errors, I get this:
Warning: mysqli_query(): (21000/1242): Subquery returns more than 1 row in /var/www/html/wp-includes/wp-db.php on line 1942
Hi Jimmy,
Have a look again as this has been fixed. Please re read the instructions as they have also changed.
Hi Dean, In the code snippet I do not see a close for the PHP and I am not getting any data extracted from the file running. There are a 1,000 or so products in my system along with 25 site pages. Is something missing?
Hi Dave,
If your still looking to get this working, please try again and let me know if your still experiencing a problem.
header(‘Content-type:text/plain’); has to be output before you echo
echo “Post ID\tPost Type\tPermalink\tFocus Key Word\tPage Title\tMeta Description”;
Hi Ivan,
Thanks for your comment, I have done a rewrite of how the data is provided. The data is now presented as a csv that is downloaded via FTP.
The snippet appears to be broken:
Call to undefined function get_results()
Hi Jake,
Thanks for the heads up. My new theme decided to strip the code. It’s back now.
I added the following to the top of the snippet to ensure it would work:
error_reporting(E_ALL);
ini_set(‘display_errors’, 1);
set_time_limit(-1);
define(‘WP_USE_THEMES’, false);
require(‘./wp-blog-header.php’);
Hi Jake,
You shouldn’t need to add this code. As you mentioned the snippet was broken so if you try it again all should be good.
Hi Dean, also got only the headers, please advise
Hi AML,
This should be fixed now.
Hi
Please fix names of tables in your code in order to fit the different table_prefixes. I think it is the main issue of all guys.
$posts = $wpdb->get_results(”
SELECT (SELECT `meta_value` FROM `{$wpdb->postmeta}` WHERE `meta_key` = ‘_yoast_wpseo_focuskw’ AND post_id = p.ID) AS focuskw,
(SELECT `meta_value` FROM `{$wpdb->postmeta}` WHERE `meta_key` = ‘_yoast_wpseo_title’ AND post_id = p.ID) AS title,
(SELECT `meta_value` FROM `{$wpdb->postmeta}` WHERE `meta_key` = ‘_yoast_wpseo_metadesc’ AND post_id = p.ID) AS metadesc,
p.ID, p.post_type FROM `{$wpdb->posts}` p
WHERE p.post_status = ‘publish’
AND (SELECT `meta_value` FROM `{$wpdb->postmeta}` WHERE `meta_key` = ‘_yoast_wpseo_meta-robots-noindex’ AND post_id = p.ID)is null
AND (p.post_type = ‘page’ OR p.post_type = ‘post’)
“);
Hi Alexander,
Thank you for your suggestion. I have incorporated that now as above.
Works with WP 4.5.3 and Yoast SEO 3.0.3
Wowww. Thanks a lot. Saved hours of work 🙂 God bless u
Hi,
I just tried your php-file, but I just got the header as described above. Might it be, that there came some changes with the latest updates of WordPress (4) or the Yoast plugin?
Would be great if you could check it again. Tried it with wp3.9.2 and 4.0
Hi,
I have just quickly tested on WordPress 4.0 with Yoast SEO 1.6. Are you on the latest Yoast SEO plugin?
I tried it on 2 different pages and updated everything. So it’s WP4 and Yoast 1.6 now, but still don’t get the list. File is in the same folder as the wp-config.php
Hi Matthias,
I have emailed you back however I have tested the code against a fresh install of WordPress 4.0 and Yoast SEO 1.6, no other plugins or themes active on this install and everything worked fine. I would suggest that you enable the default theme first, test against that. If that fails to resolve the issue then disable the plugins one at a time (except Yoast SEO) and test. Let me know how you get on please.
Have you also created a way of importing modified data back into the Yoast plugin?
Hi Tammi
The code is to purely export the focus keyword for analysis or import into third party software. If you need to modify data in bulk then you could use the ‘Bulk Title Editor’ & ‘Bulk Description Editor’ built into Yoast WordPress SEO.
Hi Dean,
This is an excellent job – thank you for creating this tool – just perfect for what I wanted to do
Thanks Nick, glad you found it useful.
Hi Dean,
It works perfectly and now I have the exact information I wanted. I appreciate the time and effort you’ve taken to update the tool, thanks very much! Regards Joe.
Hi Dean, I have uploaded GetYoastData.php to the same location as wp-config.php (your instructions say config.php but I guessed you meant wp-config.php) but it doesn’t work. Maybe there’s something in my wp-config.php that stops it working?
Hi, You are correct it is the wp-config.php, I have corrected the instructions. I have also updated the code to be compatible with WordPress SEO version 1.5.3 so you should now see the data being displayed correctly.
Hi, this is exactly what I need but unfortunately when added to the root directory of my site the yoast data is not retrieved – just the headers and a blank page are displayed.
I don’t have a config.php anywhere within my WP setup – there’s the usul wp-config.php in the root. Any idea why it doesn’t work?
Hi Joe,
I suspect if you cannot see the wp-config.php then you may be in the wrong file path. The config file is required for WordPress to work.