Skip to content

Instantly share code, notes, and snippets.

@ideadude
Forked from strangerstudios/pmpro_queries.sql
Last active October 24, 2022 21:22
Show Gist options
  • Save ideadude/0f78cef59928cacd65de90e02d819a44 to your computer and use it in GitHub Desktop.
Save ideadude/0f78cef59928cacd65de90e02d819a44 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', '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
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment