Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PMPro SQL Queries Comparing Sales From 2 Different Periods
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.