WooCommerce Subscriptions: MySQL Query To Export All Subscriptions

Here are two queries that will allow you to generate a quick report showing you all Subscriptions. Why would you want to do this? Well, in some very large sites, the WooCommerce Subscriptions Reports will not load. This provides you with a report which can be exported from your MySQL client. I’ve tested these queries multiple servers and Websites containing over 25,000 subscriptions.

These reports will provide you with the following fields:

  • Subscription ID
  • Subscription status
  • Billing First Name
  • Billing Last Name
  • Billing Email
  • Product Title
  • Order Total
  • Order Tax

Export Only Active Subscriptions

You may only be interested in exporting active subscriptions (I know I would). Run the query below to get the fields mention above, but only for Active subscriptions.

[code language=”sql”]
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;
[/code]

Exporting All Subscriptions

This one is a little different because it will look at all subscriptions on your Website. It will display a user-friendly “Subscription Status” depending on the status of the subscription.

[code language=”sql”]
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’
GROUP BY p.ID;
[/code]