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.

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;

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.

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s