Question on getting statistics from PayPal gateway

Joomla 3.9.2
MySQL 5.6.40
PHP 5.6.40
CB 2.4.0
CBSubs 4.3.0 latest

---

We are looking at where in the website flow we are losing prospective members, and we can do a lot of that with Google analytics.

What we can't get is losses through PayPal. That is, how many prospects fill out the CB registration form, go to PayPal, but never actually complete the pay transaction. Can we pull this info out of the CB database tables?

I'm not asking for a MySQL query to do that (I can do that myself), just whether the data is there for this/

You could use CB Auto Actions with the below usage to add custom tracking.

Subscription Pending (Registered Unpaid)
Global
Triggers: onCPayUserStateChange
User: Automatic
Access: Everybody
Conditions
1: [var3] Equal To PLAN_ID_HERE
2: [var2] Equal To R

Subscription Active
Global
Triggers: onCPayUserStateChange
User: Automatic
Access: Everybody
Conditions
1: [var3] Equal To PLAN_ID_HERE
2: [var2] Equal To A

If you need this behavior for baskets instead of subscriptions the below should work fine.

Basket Pending
Global
Triggers: onCPayAfterPaymentStatusUpdateEvent
User: Automatic
Access: Everybody
Conditions
1: [var4] Equal To Pending

Basket Paid
Global
Triggers: onCPayAfterPaymentStatusUpdateEvent
User: Automatic
Access: Everybody
Conditions
1: [var4] Is REGEXP /Completed|Processed/

You can check if those users still have a pending basket within CBSubs > Baskets, but CBSubs doesn't keep unpaid baskets forever and will clear them. Baskets are stored in the _cbsubs_payment_baskets table if you need to query for them.

Thanks. This is very useful. How long do uncompleted baskets hang around?

If it's marked Pending they should remain until you delete them. If they're marked Registered Unpaid then it depends on "Payment basket timeouts" parameters in CBSubs > Settings > Display > Baskets.

Thanks. One more question. What's the difference between NotInitiated and Pending?

Pending is waiting for a payment to arrive. This means a gateway selection has been confirmed and it's typically just waiting for a notification from the gateway. NotInitiated means no payment gateway was confirmed and the basket will timeout.