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',  'refunded',  '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',  'refunded',  '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', 'refunded', 'pending', 'review')
WHERE 
  mo1.total > 0 AND
  mo1.status NOT IN ('error',  'token',  'refunded',  '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', 'refunded', 'pending', 'review')
WHERE 
  mo1.total > 0 AND
  mo1.status NOT IN ('error',  'token',  'refunded',  'pending',  'review')
   AND mo1.timestamp >  '2017-09-13'
   AND mo1.timestamp <  '2017-10-13'  
GROUP BY mo1.id ) as q1

# How many orders were there for a specific discount code?
SELECT COUNT( * ) 
#SELECT mo1.id, mo1.user_id, mo1.status, mo1.total
FROM wp_pmpro_membership_orders mo1
    LEFT JOIN wp_pmpro_discount_codes_uses dcu
        ON mo1.id = dcu.order_id    
WHERE 
  mo1.total > 0
  AND mo1.status NOT IN ('error',  'token',  'refunded',  'pending',  'review')
  AND dcu.code_id = 89 #birthday  
 ;
 
 # How many of those users had a more recent order (renewed)
 SELECT user_id, id, membership_id, total, status
 FROM wp_pmpro_membership_orders
 WHERE timestamp > '2021-09-01'
 AND status NOT IN ('error',  'token',  'refunded',  'pending',  'review')
 AND total > 0
 AND user_id IN(

     SELECT mo1.user_id
     FROM wp_pmpro_membership_orders mo1
         LEFT JOIN wp_pmpro_discount_codes_uses dcu
             ON mo1.id = dcu.order_id    
     WHERE 
       mo1.total > 0
       AND mo1.status NOT IN ('error',  'token',  'refunded',  'pending',  'review')
       AND dcu.code_id = 89 #birthday  
);