Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 24 Apr 2014, 00:13
nogi nogi is offline
 
Join Date: Mar 2014
SQL query to pull out non-paying subscribers

I have various subscriptions on my site. The main one is called Premium, users that take this sub move from Regular to Premium user group and back when the sub expires. However, with the recent change of site ownership and paypal addresses, subs that are autorenewing/cancelling via PayPal aren't updating back on VB and the users are not reverting back to regular.

I've been able to proactively manage upcoming renewals by using the following query:
Membership Due Dates

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

But, how can I pull a list of users that are still in the Premium group but without any current subscription?
Reply With Quote
  #2  
Old 24 Apr 2014, 00:22
Zachery's Avatar
Zachery Zachery is offline
 
Join Date: Jul 2002
Real name: Zachery Woods
The old owner needs to cancel the subs in paypal on their end. Because they are renewing, or they'd get dropped.
__________________
Looking for ImpEx?
Reply With Quote
  #3  
Old 24 Apr 2014, 00:48
nogi nogi is offline
 
Join Date: Mar 2014
Originally Posted by Zachery View Post
The old owner needs to cancel the subs in paypal on their end. Because they are renewing, or they'd get dropped.
He has already done this. This has/is causing me another issue with subs, I've got a ton of these messages in the logs now:

vb_error_code authentication_failure
txn_type subscr_cancel
I've taken over the email/domain as well. All the previous subs were associated with his PayPal account, I couldn't see anyway to migrate them over to my PayPal account. Upon cancelling them all on his PayPal - VB tried to cancel the subs on my forum and fails, which is kind of right as peoples subs shouldn't be cut short just because of ownership change.

So far, I've disabled the old sub and created a new one to stop the renewal failures. Problem with this is people can't seem to be bothered to go through the process again resulting in loss of subscribers. For some subscribers that don't renew, the group membership isn't reverting to the old ones, hence the SQL query request.
Reply With Quote
  #4  
Old 24 Apr 2014, 10:48
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
I don't have subscriptions set up so I can't test this, but I think you should be able to add this to the end of the query you posted above:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Where you'd replace the P with the usergroupid of the Premium group. You might also want to add "AND subscriptionlog.subscriptionid = S" if it's possible for users to have more than one subscription (to make sure you're matching the subscriptionid S of the Premium subscription).

I have to admit that I don't understand a lot about paid subscriptions. Do the records get removed from the subscription log when the subscription is done? If so, you might want to change your query to "...FROM user LEFT JOIN subscriptionlog", that way you'll find users even if they have no record in the subscriptionlog (and I think it still would work since in that case expirydate should be NULL).
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 07:37.

Layout Options | Width: Wide Color: