Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Some queries around PMPro sales/etc
# 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
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.