Hey friends,
I am wondering if any of you amazing people have built a dashboard (or custom report) that can find accounts that a total ticket count for a show that is higher (or equal to) your published ticket limits. We are trying to find an easy way to find accounts who have either: A) purchased to the ticket limit (or over somehow) on web orders and/or B) accounts that have placed multiple orders that bring them to a total ticket amount that is equal to or more than your ticket limit.
We have been getting slammed by chargebacks and we need to find every way possible to find these accounts as soon as possible to return those tickets before a chargeback would happen.
Any and all advice is appreciated.
Chris
Hi Chris - I haven't fully cracked this myself, but here are some musings in case anything here is worth acting upon. I've approached this problem by trying to create a custom SQL query within List Manager, just to keep it agile and easy to edit over time. Ideally this list query is finely targeted to pull in suspect accounts whenever it's refreshed (with a minimum of false positives), so you could run it against something like Single Sale Order Listing to flag the specific bad orders that need to be addressed.
If you can get a solid list built to reliably flag a likely reseller, you could make it dynamic and combine it with a report schedule to deliver on a regular basis to try to catch these with a shorter turnaround time. That could be a ton of report server email traffic, though - one idea we've had is to try to set up an Analytics pulse where notifications are sent whenever this reseller list has more than 0 constituents in it, but skip any notifications if it's empty. The downside there is that it would only ping on a 24 hour cycle based on the Analytics load.
Right now I'm not fully satisfied with my 'suspected reseller' list, but here are the segments it's looking for. Rather than look for accounts that are passing a ticket threshold by performance, it just looks at all orders from the past 24 hours - that may be less helpful, but a bit easier to code for. These are the three queries joined together in our list right now, let me know if getting the actual SQL would be helpful for you:
1) someone booked 7+ tickets and resides outside of our main geographic area in the PNW, or
2) has 3 or more orders created all coming from the same IP address (right now this pulls in some false positives from our employees reserving their staff comps on our office network, so I need to figure out how to exclude our onsite IP address), or,
3) has booked 11+ tickets across any number of orders.
There are probably other segments still to be brainstormed, and these could still use some more work - but hope this might help toward finding a good solution!
I have noticed a lot of the same indicators with broker accounts. in addition to all that you wrote, I've noticed a lot of broker emails end in "@manboto.com", so orders with that email are always red flags.
May I offer a suggestion for your #2...Place all of your staff into a "Staff" constituency, and then just exclude the constituency from your report.