Export products & orders WooCommerce

To export order data from your online store WooCommerce version 6.X.X, ask your IT department or your online store developer to run the following SQL query on the database:

EXPORT Orders with ITEM_ID:

SELECT posts.post_date AS 'Timestamp',
       wcorder_itemmeta.meta_value AS 'Item_ID',
       wcorder_items.order_id AS 'Session_ID',
       REPLACE(SUM(wcorder_itemmeta_total.meta_value), ',', '.') AS 'Purchase_Price',
       NULL AS 'Parent_Click_ID',
       NULL AS 'Online',
       postmeta.meta_value AS 'User_ID',
       NULL AS 'Purchase_Group_ID',
       'fdcbb3c07c17ce1e832b852eebdf4fa0' AS 'Cust_ID',
       REGEXP_REPLACE(wcorder_items.order_item_name, '<.+?>', '') AS 'Name'
FROM wp_woocommerce_order_items AS wcorder_items
JOIN wp_woocommerce_order_itemmeta AS wcorder_itemmeta ON wcorder_items.order_item_id = wcorder_itemmeta.order_item_id
JOIN wp_woocommerce_order_itemmeta AS wcorder_itemmeta_total ON wcorder_items.order_item_id = wcorder_itemmeta_total.order_item_id
JOIN wp_posts AS posts ON wcorder_items.order_id = posts.ID
LEFT JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id
AND postmeta.meta_key = '_customer_user'
WHERE wcorder_items.order_item_type = 'line_item'
  AND wcorder_itemmeta.meta_key = '_product_id'
  AND (wcorder_itemmeta_total.meta_key = '_line_total'
       OR wcorder_itemmeta_total.meta_key = '_line_total_tax')
GROUP BY wcorder_items.order_id,
         wcorder_items.order_item_id

EXPORT Orders with SKU_ID:

SELECT posts.post_date AS 'Timestamp',
       sku_meta.meta_value AS 'Item_ID',
       wcorder_items.order_id AS 'Session_ID',
       REPLACE(SUM(wcorder_itemmeta_total.meta_value), ',', '.') AS 'Purchase_Price',
       NULL AS 'Parent_Click_ID',
       NULL AS 'Online',
       postmeta.meta_value AS 'User_ID',
       NULL AS 'Purchase_Group_ID',
       'fdcbb3c07c17ce1e832b852eebdf4fa0' AS 'Cust_ID',
       REGEXP_REPLACE(wcorder_items.order_item_name, '<.+?>', '') AS 'Name'
FROM wp_woocommerce_order_items AS wcorder_items
JOIN wp_woocommerce_order_itemmeta AS wcorder_itemmeta ON wcorder_items.order_item_id = wcorder_itemmeta.order_item_id
JOIN wp_woocommerce_order_itemmeta AS wcorder_itemmeta_total ON wcorder_items.order_item_id = wcorder_itemmeta_total.order_item_id
JOIN wp_posts AS posts ON wcorder_items.order_id = posts.ID
LEFT JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id
AND postmeta.meta_key = '_customer_user'
JOIN wp_postmeta AS sku_meta ON wcorder_itemmeta.meta_value = sku_meta.post_id
AND sku_meta.meta_key = '_sku'
WHERE wcorder_items.order_item_type = 'line_item'
  AND wcorder_itemmeta.meta_key = '_product_id'
  AND (wcorder_itemmeta_total.meta_key = '_line_total'
       OR wcorder_itemmeta_total.meta_key = '_line_total_tax')
GROUP BY wcorder_items.order_id,
         wcorder_items.order_item_id

After running the SQL query please save the rows returned from the database in CSV format, one CSV for ITEM_ID and one for SKU_ID.

To export product data from your online store WooCommerce version 6.X.X, ask your IT department or your online store developer to run the following SQL query on the database:

SELECT t.term_id AS Product_ID,
       pm_sku.meta_value AS SKU,
       'fdcbb3c07c17ce1e832b852eebdf4fa0' AS 'Customer_ID',
       'fdcbb3c07c17ce1e832b852eebdf4fa0' AS 'Category_ID',
       p.post_title AS Name,
       p.post_content AS Description
FROM wp_terms AS t
JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
JOIN wp_posts AS p ON tr.object_id = p.ID
JOIN wp_postmeta AS pm_sku ON p.ID = pm_sku.post_id
WHERE tt.taxonomy = 'product_cat'
  AND p.post_status = 'publish'
  AND pm_sku.meta_key = '_sku'

After running the SQL query please save the rows returned from the database in CSV format.

Finally, read the following guide for the steps you need to take to upload your product & order data.