filter customers with no payments

[snake]

On one of my sites I offer a FREE service with paid addons.

I want to be able to sent a mass mail to all my users who currently are not paying me anything, i.e. they are only using the free product but no paid addons.

Can anyone (i.e Brian) tell me how to do this? 
I do not see any way to filter my customers like this, so I am guessing I am going to have to manually extract a list of customers from the database and do it that way.

 

 

[brian!]

1 hour ago, snake said:

I do not see any way to filter my customers like this, so I am guessing I am going to have to manually extract a list of customers from the database and do it that way.

if you were just wanting to send to users of the free product, then that would be simple enough using MM/EC, but of course you want the ones who aren't paying anything - so the above condition might contact users of free products and paid products.

you wouldn't necessarily have to extract, but you would have to identify these clients in some way and mark them first... now I suppose the default way would be to either assign them to a client group (if none being used), failing that set a value in an admin-only CCF and then filter on that.

I would imagine the identification would be via a SQL query, e.g checking for specific packageid value(s) in tblhosting, and then seeing if that client doesn't have any entries in tblaccounts (or paid positive invoices in tblinvoices).

[snake]

Other than manually going into every single client I cannot see any way to do this.

Also the primary reason for doing this is to marketing upgrades and addons to those clients. So flagging them in that way with a group or CCF would be rather permanent. So if they do upgrade, they would still be marked as free,

Seems the only solution would be to extract the list of customers from the DB and import them into my MLM instead.

[brian!]

15 hours ago, snake said:

Other than manually going into every single client I cannot see any way to do this.

you don't think you can do it via SQL ?

15 hours ago, snake said:

Also the primary reason for doing this is to marketing upgrades and addons to those clients. So flagging them in that way with a group or CCF would be rather permanent. So if they do upgrade, they would still be marked as free,

then you null the value of the CCF before running the SQL query that marks applicable clients.

[snake]

Quote

you don't think you can do it via SQL ?

yes, when I said "Seems the only solution would be to extract the list of customers from the DB and import them into my MLM instead." this would need to be be done via SQL.

[steven99]

Using an addon, you could loop through the clients, check if they have free products, paid products, paid invoices, etc.  If criteria matches needs, then add to an array that is then returned in a format you want.   A hook or two might also be able to do it -- one hook to inject a button and another hook to watch for that button being hit.