We are building a way to track free membership conversions which is an essential tool to any membership site.
We want to be able to track total free trial members, free trial members, open free trial members, and converted free trial members. We see there is a way to search for this in Manage Members. Now we want to see how to pull this data into our own proprietary plugin.
We want to be able to search within a date range and see what % of free trial members converted.
Your assistance would be greatly appreciated.
This is huge! We'd love this functionality as well.
I've been trying to figure this out for many clients as well. The fact that free trials don't process a transaction makes it impossible to track. I wish "Transactions" had an option to search for "Trials". A couple ideas that I'm playing around with:
1. Create a hidden field that's only visible on free trial checkouts that adds text to a custom field "Trial" and when checkout is completed, that hidden field populates that custom field with "Yes". Then be sure to set up a push notification for "rebill" and have it trigger a script that first checks to be sure it's the first rebill for your specific trial product, if it is... continue the script. The script will literally just change the "Yes" text from the trial custom field to "Converted".
Now you can do a search in Manage Members for custom field "Yes" and Status "Active" to see all current "free trials" in system. Then to see converted, you'd do another search for custom field "Converted" and Status "Active" to see all converted active subscriptions. Unfortunately it's not a good method to backtrack - to see what your conversions were 6 months ago, because those custom fields would have changed or their status may have changed since then, thus they wouldn't show up in your search. You could instead do all this tracking in a spreadsheet and send all order data into Google Sheets through Zapier or your script.
2. Query the database and trigger your script to do whatever you want it to. When a trial is purchased, it still populates the mm_orders and mm_order_items tables, if it's a trial product, it displays the trial period and the trial cost. If it's not a trial product, those fields are populated with "null". So you can easily see which orders are trials based on those being filled and can keep track of your free trials. Then when they convert, you could use the same method in #1, or query the database again. I'm unsure where those 1st rebill orders come through in my quick search through the database tables but it's in there somewhere. I see "mm_scheduled_payments".
I was interested in this as well. We have a 7 day $1 trial and I wanted to know how many of those are still active after 10 days. I check this on the 10th day since there's always some people that forgot to cancel their trial and ask for a refund. This SQL query fetches all users that did a trial payment 10 days ago:
SELECT wp_users.user_email, mm_user_data.cancellation_date FROM mm_order_items INNER JOIN mm_orders ON mm_order_items.order_id=mm_orders.id JOIN mm_user_data ON mm_orders.user_id=mm_user_data.wp_user_id JOIN wp_users ON mm_orders.user_id=wp_users.ID JOIN mm_transaction_log ON mm_order_items.order_id=mm_transaction_log.order_id WHERE (mm_transaction_log.amount = '-1.0000') AND DATE(mm_transaction_log.transaction_date) = DATE_SUB(DATE(NOW()), INTERVAL 14400 MINUTE)
Maybe this gives you something to work on for free trials :)