How to find products or services based on coupon codes or configurable options?

[Mechanic]

Hi,

Is there any way to search for products/services/clients based on coupon codes they used or the configurable options?

For example, let's say you offered two discount coupons (promo codes) and a few clients ordered your services using those coupon codes. Now, you want to see how many clients/services used coupon1 and how many used coupon2. Is there a way to search this?

Similarly, say you offer a service that has configurable options - a dropdown with 4 values to choose from. Is there any way to search for clients/services based on the value/name of the configurable option?

Any suggestion/help would be appreciated.

[brian!]

On 15/05/2019 at 01:08, Mechanic said:

For example, let's say you offered two discount coupons (promo codes) and a few clients ordered your services using those coupon codes. Now, you want to see how many clients/services used coupon1 and how many used coupon2. Is there a way to search this?

wouldn't those values already show in the "Uses" column on the Promotions/Coupon page in the admin area ?

On 15/05/2019 at 01:08, Mechanic said:

Similarly, say you offer a service that has configurable options - a dropdown with 4 values to choose from. Is there any way to search for clients/services based on the value/name of the configurable option?

any way? not from the settings that I can think of.... certainly you could use a SQL query to get the information from the database - though the data you need is spread over multiple tables.

 

[Mechanic]

 

 

1

21 minutes ago, brian! said:

wouldn't those values already show in the "Uses" column on the Promotions/Coupon page in the admin area ?

 

Hi brian!

I am glad that you replied.

It only shows how many times a coupon is used. I want to see a list of products/clients/order that used the coupons. Is it possible in any way?

 

Quote

any way? not from the settings that I can think of.... certainly you could use a SQL query to get the information from the database - though the data you need is spread over multiple tables.

 

So no option from admin area. Could you suggest any solution?

 

How come WHMCS became this big and not have these functions??

[brian!]

Hi,

13 minutes ago, Mechanic said:

I am glad that you replied.

still working through a backlog of threads while I was away... seems to be never-ending and i'm losing the will to live! 😲

14 minutes ago, Mechanic said:

It only shows how many times a coupon is used.

technically that's what you asked for in your question - you didn't want to know who, just how many used each. 🙂

16 minutes ago, Mechanic said:

I want to see a list of products/clients/order that used the coupons. Is it possible in any way?

I can think of 4 ways...

1. SQL query - the promotion code used will be stored in the orders table... so querying tblorders will give you most of the details...
   but I know you'd prefer an admin solution, so...
2. go to Reports -> Promotion Usage and you will get the same list of promotions as from the above page, but with a "Drill Down" option that should give you a list of orders that used that promo code... by default, it will only give you order ID with no client, but the report could be tweaked to give userid with little effort, or the client names with some more effort... btw - there's a bug in that report which must have been there for years, as these reports don't get updated regularly.
3. go to the invoices page, open the search filter and type your promotion code into the Line Item Description field...
   
4. I have previously written a report that allowed the searching and exporting from the tblinvoiceitems table and w...hopefully, your promo codes aren't the same as product names otherwise you could get a lot of wrong results - I could quickly tweak the invoiceitems report to only search for promo codes, but I shouldn't need to unless you're getting a lot of non-applicable results.

31 minutes ago, Mechanic said:

So no option from admin area. Could you suggest any solution? 

you mean give an exact SQL query? not off the top of my head, i'd need to think about it... steven's post in the thread below could be a starting point, and possibly could be converted to a report too.

39 minutes ago, Mechanic said:

How come WHMCS became this big and not have these functions??

because I think their focus over the last few years has been more on $$$ and not usability.... there are lots of trivial (e.g easy for them to do) little features that should be there, but aren't - but what can we do about it? no point submitting a feature request as that will take 5+ years until completion.... sure you can get third-party developers in to fix them if required, but i'm not a big fan of encouraging users to pay for features that should be included by default - though it's sadly often necessary.

I don't think that it's unreasonable to want to search which users are using a specific configurable option... or even to expect the feature to be there in the software.

I doubt much will change with v7.8.... so v8 at the end of the year will be their last chance... if that's not a radical improvement on what we have in front of us now, then we might as well all pack our bags and move onto something else... I suspect that I will do so for my own businesses.