Some queries around PMPro sales/etc

pmpro_queries.sql

# successful paid orders for a given month
SELECT COUNT( * ) 
FROM wp_pmpro_membership_orders
WHERE 
  total > 0 AND
  status NOT IN (
'error',  'token',  'refund',  'pending',  'review'
)
   AND TIMESTAMP >  '2017-10-01'
   AND TIMESTAMP <  '2017-11-01';

# exclude recurring orders (have a previous order for the same level)
SELECT COUNT(DISTINCT(mo1.id))
FROM wp_pmpro_membership_orders mo1 
  LEFT JOIN wp_pmpro_membership_orders mo2 ON mo1.user_id = mo2.user_id AND 
                                              mo1.membership_id = mo2.membership_id AND 
                                              mo1.id <> mo2.id AND
                                              mo2.status NOT IN('error', 'token', 'refund', 'pending', 'review')
WHERE 
  mo1.total > 0 AND
  mo1.status NOT IN ('error',  'token',  'refund',  'pending',  'review')
   AND mo1.timestamp >  '2017-10-01'
   AND mo1.timestamp <  '2017-11-01'
   AND mo2.id IS NULL
   
# same query, but changing the last AND mo2.id IS NULL to ... IS NOT NULL will give you only recurring orders
SELECT COUNT(DISTINCT(mo1.id))
FROM wp_pmpro_membership_orders mo1 
  LEFT JOIN wp_pmpro_membership_orders mo2 ON mo1.user_id = mo2.user_id AND 
                                              mo1.membership_id = mo2.membership_id AND 
                                              mo1.id <> mo2.id AND
                                              mo2.status NOT IN('error', 'token', 'refund', 'pending', 'review')
WHERE 
  mo1.total > 0 AND
  mo1.status NOT IN ('error',  'token',  'refund',  'pending',  'review')
   AND mo1.timestamp >  '2017-10-01'
   AND mo1.timestamp <  '2017-11-01'
   AND mo2.id IS NOT NULL
   
# example to get average order values across those queries:
SELECT AVG(total) FROM (
SELECT mo1.total
FROM wp_pmpro_membership_orders mo1 
  LEFT JOIN wp_pmpro_membership_orders mo2 ON mo1.user_id = mo2.user_id AND 
                                              mo1.membership_id = mo2.membership_id AND 
                                              mo1.id <> mo2.id AND
                                              mo2.status NOT IN('error', 'token', 'refund', 'pending', 'review')
WHERE 
  mo1.total > 0 AND
  mo1.status NOT IN ('error',  'token',  'refund',  'pending',  'review')
   AND mo1.timestamp >  '2017-09-13'
   AND mo1.timestamp <  '2017-10-13'  
GROUP BY mo1.id ) as q1

Forked to here: https://gist.github.com/ideadude/0f78cef59928cacd65de90e02d819a44

The status for refunded orders is "refunded" not refund.