MainWP Community

How to export all active subscriptions from WooCommerce Subscriptions using a MySQL query

Knowing the correct MySQL queries to run in phpMyAdmin can make your life so much easier and running the MySQL then being able to export to CSV will be much faster than running any plugin or export plugin.

The MySQL query to run would be

SELECT
  p.ID as 'Subscription ID',
  p.post_status as 'Subscription Status',
  pm1.meta_value as 'Billing First Name',
  pm2.meta_value as 'Billing Last Name',
  pm3.meta_value as 'Billing Email',
  oitems.order_item_name as 'Product',
  pm4.meta_value as 'Order Total',
  pm5.meta_value as 'Order Tax'
FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
INNER JOIN wp_postmeta pm2 ON pm2.post_id = p.ID
INNER JOIN wp_postmeta pm3 ON pm3.post_id = p.ID
INNER JOIN wp_postmeta pm4 ON pm4.post_id = p.ID
INNER JOIN wp_postmeta pm5 ON pm5.post_id = p.ID
INNER JOIN wp_woocommerce_order_items oitems ON oitems.order_id = p.ID
WHERE
  post_type = 'shop_subscription'
  AND post_status = 'wc-active'
  AND pm1.meta_key = '_billing_first_name'
  AND pm2.meta_key = '_billing_last_name'
  AND pm3.meta_key = '_billing_email'
  AND pm4.meta_key = '_order_total'
  AND pm5.meta_key = '_order_tax'
  AND oitems.order_item_type = 'line_item'
GROUP BY p.ID;
2 Likes

To export all subscriptions you can run this MySQL query.

SELECT
  p.ID as 'Subscription ID',
  CASE
    WHEN p.post_status = 'wc-active' THEN 'Active'
    WHEN p.post_status = 'wc-cancelled' THEN 'Cancelled'
    WHEN p.post_status = 'wc-expired' THEN 'Expired'
    WHEN p.post_status = 'wc-failed' THEN 'Failed'
    WHEN p.post_status = 'wc-on-hold' THEN 'On-Hold'
    WHEN p.post_status = 'wc-pending' THEN 'Pending'
    WHEN p.post_status = 'wc-pending-cancel' THEN 'Pending Cancellation'
    WHEN p.post_status = 'wc-switched' THEN 'Switched'
    ELSE 'Unknown'
  END AS 'Subscription Status',
  pm1.meta_value as 'Billing First Name',
  pm2.meta_value as 'Billing Last Name',
  pm3.meta_value as 'Billing Email',
  oitems.order_item_name as 'Product',
  pm4.meta_value as 'Order Total',
  pm5.meta_value as 'Order Tax'
FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
INNER JOIN wp_postmeta pm2 ON pm2.post_id = p.ID
INNER JOIN wp_postmeta pm3 ON pm3.post_id = p.ID
INNER JOIN wp_postmeta pm4 ON pm4.post_id = p.ID
INNER JOIN wp_postmeta pm5 ON pm5.post_id = p.ID
INNER JOIN wp_woocommerce_order_items oitems ON oitems.order_id = p.ID
WHERE
  post_type = 'shop_subscription'
  AND pm1.meta_key = '_billing_first_name'
  AND pm2.meta_key = '_billing_last_name'
  AND pm3.meta_key = '_billing_email'
  AND pm4.meta_key = '_order_total'
  AND pm5.meta_key = '_order_tax'
  AND oitems.order_item_type = 'line_item'