Skip to content

Instantly share code, notes, and snippets.

@strangerstudios
Last active November 2, 2021 13:05
Show Gist options
  • Save strangerstudios/d1c9aa5e6e668518748f543eaf3104df to your computer and use it in GitHub Desktop.
Save strangerstudios/d1c9aa5e6e668518748f543eaf3104df to your computer and use it in GitHub Desktop.
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
@ideadude
Copy link

ideadude commented Nov 2, 2021

@ideadude
Copy link

ideadude commented Nov 2, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment