How to export products for Google XML Product Feed quickly

October 13, 2023

Exporting from WordPress and WooCommerce can be slow because most plugins export one item at a time. If you know SQL, you’ll know that a single well-written query is many orders of magnitude faster at pulling this data out.

In this guide I’ll show a fast query that grabs data from wp_posts, wp_postmeta and the taxonomy tables with as few joins as possible, making this one of the fastest ways to export product data for a Google product feed. Exporting 1.8 million rows using the SQL below took 15 minutes in one client’s case.

The easier way: wp ssi export

Before you hand-write SQL, note that Super Speedy Imports now ships a native CLI export command that produces a CSV including all post fields, all postmeta, all taxonomy terms, featured/gallery image URLs and parent SKUs – no SQL required:

wp ssi export {output_file} [post_type] [--exclude-dates]

For example, to export all WooCommerce products:

wp ssi export ./exports/products.csv product

See Using the Export CLI Command in Super Speedy Imports for the full reference. This is the maintained, supported path and is what we recommend for most people.

The hand-written SQL method below is still worth knowing if you want total control over exactly which columns and taxonomies appear in the output, or you want to run the export directly from the database with no WordPress in the loop. The rest of this guide covers that approach.

Construct your SQL

Decide which fields you want. In the SQL below I grab a number of columns from wp_posts, some from wp_postmeta, and then a bunch of taxonomy information too. Alter the taxonomy and meta key entries to grab what you need for your product feed. You can safely delete rows you don’t need, and you should update the output file location to suit your server.

SELECT
    p.ID,
    p.post_title,
    p.post_content AS 'Product description',
    p.post_excerpt AS 'Short Description',
    CONCAT('https://example.com/?p=', p.ID) AS 'Permalink',
    MAX(CASE WHEN pm.meta_key = '_product_url' THEN pm.meta_value END) AS '_product_url',
    MAX(CASE WHEN pm.meta_key = '_regular_price' THEN pm.meta_value END) AS '_regular_price',
    MAX(CASE WHEN pm.meta_key = '_sale_price' THEN pm.meta_value END) AS '_sale_price',
    MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS '_sku',
    SUBSTRING_INDEX(MAX(CASE WHEN pm.meta_key = 'external_image_url' THEN pm.meta_value END), '|', 1) AS 'external_image_url',
    MAX(CASE WHEN pm.meta_key = '_wc_rating_count' THEN pm.meta_value END) AS '_wc_rating_count',
    MAX(CASE WHEN pm.meta_key = '_wc_average_rating' THEN pm.meta_value END) AS '_wc_average_rating',
    MAX(CASE WHEN pm.meta_key = '_wc_review_count' THEN pm.meta_value END) AS '_wc_review_count',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'product_cat' THEN t.name END) AS 'product_category',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'product_tag' THEN t.name END) AS 'product_tags',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Größe' THEN t.name END) AS 'Größe',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Lieferzeit' THEN t.name END) AS 'Lieferzeit',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Versandkosten' THEN t.name END) AS 'Versandkosten',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Marke' THEN t.name END) AS 'Marke',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Verkäufer-Shop' THEN t.name END) AS 'Verkäufer-Shop',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_EAN' THEN t.name END) AS 'EAN'
INTO OUTFILE '/var/log/mysql/export.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM
    wp_posts p
LEFT JOIN
    wp_postmeta pm ON p.ID = pm.post_id
LEFT JOIN
    wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN
    wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN
    wp_terms t ON tt.term_id = t.term_id
WHERE
    p.post_type = 'product'
    AND p.post_status = 'publish'
GROUP BY
    p.ID;

A few things to check before you run it:

  • Table prefix. This query assumes the default wp_ prefix. If your site uses a custom prefix (check $table_prefix in wp-config.php), update every wp_ table name to match.
  • Permalink. Replace https://example.com/ with your real site URL. The ?p=ID form always resolves, even if you use pretty permalinks.
  • post_status = 'publish'. This keeps trashed, draft and auto-draft products out of your feed. Remove or widen this if you have a reason to.
  • LEFT JOINs. These ensure products with no terms in a given taxonomy (for example an uncategorised product) still appear in the export – an INNER JOIN on wp_term_relationships would silently drop them.
  • LINES TERMINATED BY '\n'. Make sure the line terminator is a real newline (\n), not the literal letter n.

Adding additional postmeta values

To add another postmeta value, duplicate one of the MAX(CASE ...) lines and adjust the meta_key it pulls from and the column alias it outputs:

    MAX(CASE WHEN pm.meta_key = '_wc_average_rating' THEN pm.meta_value END) AS '_wc_average_rating',

Adding additional taxonomies

To add another taxonomy, comma-joined in its own column when a product has multiple terms, duplicate one of the GROUP_CONCAT lines:

    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'product_tag' THEN t.name END) AS 'product_tags',

Running your SQL

The easiest way to get output is to run this SQL from phpMyAdmin: paste it in, run it, then use the export button.

To automate it, you’ll want to run it from the command line. Knowing this lets us schedule the export later.

  1. Log onto your server using SSH or PuTTY.
  2. Save your SQL export script somewhere cron will have access to.
  3. Alter the command below to include your username, password and database name, and choose the output location and file name, then run it.
  4. Confirm the output file is created and looks correct.
mysql --user="youruserfromwpconfig" --password="passwordfromwpconfig" dbname < /path/to/your/export.sql

Troubleshooting permissions

Your export folder needs to be writable by your MySQL user. To find your MySQL user, run:

ls -l /var/log/mysql/

Here the user and group are both mysql. If your output folder is /root/exports/, set the owner and group of that folder to mysql:

chown mysql:mysql /root/exports/

Note that with INTO OUTFILE the MySQL server may also be restricted by the secure_file_priv setting – the destination must be inside the directory that setting points to. Run SHOW VARIABLES LIKE 'secure_file_priv'; to check where that is.

Automating your SQL export

Open your crontab:

crontab -e

Add a row to run the export once weekly, every Sunday. Adjust the schedule as you like:

0 2 * * 7 mysql --user="youruserfromwpconfig" --password="passwordfromwpconfig" dbname < /path/to/your/export.sql ; mv /var/log/mysql/export.csv /var/log/mysql/export_$(date +%Y%m%d).csv

There are two commands here – one to generate the CSV and one to rename it with the current date. MySQL will not overwrite an INTO OUTFILE target that already exists, so renaming the previous file (or removing it) is required for the next run to succeed.

Summary

Using the above, you can vastly reduce the resources used for these weekly exports for Google product feeds, save time generating the export, and guarantee the file is refreshed every week. Exporting 1.8 million rows with this SQL took 15 minutes in one client’s case.

If you’d rather not maintain hand-written SQL, the built-in wp ssi export command gives you a complete CSV of any post type with a single command.

×
▲
▼
1/1