This tutorial explains how to Excel export WordPress Yoast SEO data of all articles. Here I will talk about a simple method to get Yoast SEO data including title of the articles, focus keywords, and the meta description to a CSV file. Here I will mention a free PHP script that lets you export all the SEO data from all the articles to an Excel file. The script does all the work itself. You just have to put it on the root directory of your website and execute it. In just a couple of seconds, it will create the CSV file that you can download through a FTP client.
If you have a lot of published articles and for some reasons you want the SEO data, then you can’t really do that. Also, Yoast plugin allows you to export the plugin settings only. That is where the following script comes in handy. If you have an FTP access to your website, then you just have to upload the PHP script and get the job done.
How to Excel Export WordPress Yoast SEO Data of All Articles (Title, Description, Keyword)?
I have already mentioned it above that you just need an FTP client and the PHP file. After that, you can follow the below steps to easily export WordPress SEO data of all articles.
Step 1: Copy this code and then save to a PHP file. For example, I am saving this code inside “yoast_data.php” file. Also, you have to edit the “MySecretKey” in the PHP file too. Simply replace it with any key, for example, “abcd1234”.
<?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);
?>
Step 2: Log in to the FTP account of your WordPress website. Next reach the root directory where the “wp-config.php” file is. Here you have to create a new folder say “yoast”. After that, upload the PHP file that you have created to this folder.
Step 3: Now, navigate to the address bar of your browser and then paste the URL to the PHP script. After that, get back to the FTP client and refresh it. You will see a CSV file in the same folder where you kept the PHP script. You can download that and open it in any CSV viewer.
http://<your domain>/yoast/<PHP file name>?key=”MySecretKey”
In this way, you can easily export the Yoast SEO data from a WordPress website. You just have to use a simple PHP script to do it. Getting the SEO data can be useful as you can use the focus keywords from the exported file for tracking rank of your keywords.
Final thoughts
Well, if you are looking for a way around to get SEO data of all the posts, then you may try out the script that I have mentioned. It hardly takes a few seconds to use it to get all the post titles, their description, and the focus keyword used in a CSV file.
