Coupon Reporting

[AllanD]

With the new custom reporting in 3.5 I would like to report on where coupons were used.  I checked the docs and did not find much.  I looked at the sql table structure but need a little help understanding the relationships. Is there any published entity diagram?

 

1. When a coupon is used, is it captured as a line item on an order and/or invoice?  What table holds the coupon id or code used?

 

2. Is it possible to generate a report showing the coupon code and the paid invoices or are the relationships not there?

 

 

 

Here is a little background in why I need to track the coupon usage:

The coupon codes are being used to track resellers and sales people. Each code has an id embedded in it that refers to a client id.  Each reseller, or sales person has an client record that is assigned to their respective group.  When a new customer uses a coupon, it ties the order back to the reseller or sales person. I'd like to generate a report to pay commissions.  Ideally the report will have the customer, service, invoice amount, coupon code and sales person name.

 

Thanks!

[Tyson]

Although a coupon credit may be added as a line item to an invoice it applies to, the coupon itself is not tied to the invoice or any of its line items. However, coupons are tied to services, and services are tied to invoice line items, so it is possible to deduce that a coupon was used on an invoice because the service(s) that used it has the coupon. This has the potential to be inaccurate in your case, for example, if the coupon on the service was changed or removed after it had been used and before you have generated the report. Similarly if the coupon was not used, but was added after the service was created. Additionally, a coupon may apply when a service renews, so the same service with the same coupon might appear in multiple invoices, possibly skewing your commission results.

 

Join `services` with `invoice_lines` on service ID where the service has the coupon ID you're looking for.

[AllanD]

Hmmm....  I think having the coupon associated with the service may make it easier in my situation because there are very few products and coupons only apply to the initial service not renewals.

 

Please clarify: "if the coupon on the service was changed or removed after it had been used".  If a coupon is deleted from the system, is the coupon code removed from the service?

 

In my case a coupon would only be added after the fact the customer did not enter it themselves (or it did not work).  This should be ok since the coupon code is still tied to a sales person.

 

I am not anticipating 

 

So the one thing I might be missing here is 'date paid'.  I am using date_closed, but that does not confirm an item was paid.  Is there a field for the date paid?

 

 

 

Here is what I have so far:

SELECT s.coupon_id,
ls.description, ls.qty, ls.amount, ls.invoice_id,
i.paid


FROM `services` as s


Left join invoice_lines as ls on s.id = ls.service_id
Left join invoices i on i.id = ls.invoice_id 


WHERE s.status = 'active' and i.date_closed >= '1/1/2015' and  i.paid >0

[Tyson]

If a coupon is deleted from the system, is the coupon code removed from the service?

 

Yes, the coupon is removed from services if the coupon is deleted.

 

 

Please clarify: "if the coupon on the service was changed or removed after it had been used".

 

When you manage a service, you can change (or remove) the coupon from that service. For instance, if a customer orders a service with coupon XYZ, an admin can go in and remove that coupon from the service or change it to something else,  like "ABC". If you then generate your report based on coupon XYZ, this service would not appear in the list because the service is now assigned coupon ABC. To summarize, there is no knowledge of what the coupon was when the service was created, but only what it is now, at the time the report is generated.

 

 

So the one thing I might be missing here is 'date paid'.  I am using date_closed, but that does not confirm an item was paid.  Is there a field for the date paid?

 

There is no "paid" date. For all effective purposes, this is the date closed, as the invoice is only marked closed when it is paid. If you refund a payment from an invoice, then the invoice will be open again.

SELECT s.`coupon_id`, ls.`description`, ls.`qty`, ls.`amount`, ls.`invoice_id`, i.`date_closed`
FROM `services` AS s
INNER JOIN `invoice_lines` ls ON s.`id` = ls.`service_id`
INNER JOIN `invoices` i on i.`id` = ls.`invoice_id`
WHERE s.`status` = 'active' 
AND i.`date_closed` IS NOT NULL
GROUP BY i.`id`

[AllanD]

This was all very helpful in understanding the relationships.

 

Since the coupons can be deleted or changed, I am taking a slightly different approach.

 

My coupon codes contain the id number of the Client record used for the reseller or sales person (part of my work around config is to add a Client record in Blesta for each sales person or reseller and assign them to a Sales category).

 

I added a hidden field to the service options in the Universal module to hold the id for the sales person record.

 

I spliced in some code (via vqmod) into the page with javascript functions to parse the coupon code for the client id and add it to the hidden field.

 

This way the coupon code can change or be deleted but the sales person id will remain associated with the service via the options field.  And with the sales person (or reseller) information present in the system the commission report can contain the sales persons name. 

 

I should be able to create a query to pull it all together.

 

Thanks for your explanations and help.

 

I am still looking for any type of logical entity relationship diagram for the database (for the core at least), if available.

[Tyson]

Good to hear you were able to find a work-around.

 

I am still looking for any type of logical entity relationship diagram for the database (for the core at least), if available.

 

We don't release information, like an ER diagram, for commercial/IP reasons.

[AllanD]

Will there be some sort of guide for building custom reports?

 

Maybe just a general description of what resides in common tables?