PMPro SQL Queries Comparing Sales From 2 Different Periods

pmpro_queries_with_period_vars.sql

SET @p1start = '2016-04-01';
SET @p1end = '2016-07-01';
SET @p2start = '2017-04-01';
SET @p2end = '2017-07-01';

#period 1
select CONCAT('Period 1, ', @p1start, ' to ', @p1end) AS '';

# new paid 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 >  @p1start
   AND mo1.timestamp <  @p1end
   AND mo2.id IS NULL;

# recurring paid 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 >  @p1start
   AND mo1.timestamp <  @p1end
   AND mo2.id IS NOT NULL;
   
# average order total
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 >  @p1start
   AND mo1.timestamp <  @p1end
GROUP BY mo1.id ) as q1;

# average recurring total
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 >  @p1start
   AND mo1.timestamp <  @p1end   
   AND mo2.id IS NOT NULL
GROUP BY mo1.id ) as q1;

#total revenue
SELECT SUM(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 >  @p1start
   AND mo1.timestamp <  @p1end
GROUP BY mo1.id ) as q1;

#period 2
select CONCAT('Period 2, ', @p2start, ' to ', @p2end) AS '';

# new paid 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 >  @p2start
   AND mo1.timestamp <  @p2end
   AND mo2.id IS NULL;

# recurring paid 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 >  @p1start
   AND mo1.timestamp <  @p1end
   AND mo2.id IS NOT NULL;
   
# average order total
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 >  @p2start
   AND mo1.timestamp <  @p2end
GROUP BY mo1.id ) as q1;

# average recurring total
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 >  @p2start
   AND mo1.timestamp <  @p2end   
   AND mo2.id IS NOT NULL
GROUP BY mo1.id ) as q1;

#total revenue
SELECT SUM(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 >  @p2start
   AND mo1.timestamp <  @p2end
GROUP BY mo1.id ) as q1;