Hi all!
I'm looking to add a widget to a dashboard that pulls outstanding/unused gift certificate totals. We'd like to get a better idea of how our COVID cancellations are tracking in terms of how many returns are being made to gift certificates for patrons' future use. However, I can't simply track return transaction types with gift certificate payment methods because our ticket office has been occasionally refunding to gift certificate for immediate use when a patron calls and wants their refund credited toward next season's order. Is it possible to limit this to just unused gift certificates?
Thanks!
Lauren
I had this exact issue! Right now, my Gift Certificate widget gives me the outstanding totals by just filtering on Payment Method = Gift Cert and the widget Value is -1*(sum([Total Transaction Amount])) to get a positive number.
Since mine doesn't filter on Transaction Type, its including any transaction that happens with Gift Certs, whether that's a Ticket Refund, a Ticket Purchase (for next season), or a Contribution/Zero Adjustment.
What I haven't figured out is getting the # of Gift Certificates that have balances on them. If you do the Count of Unique Gift Cert IDs, it still counts the $0 Gift Certificates because the initial purchase and refund are still pulled, they just zero each other out when you're looking at dollars.
Lily, have you considered adding a value greater than 0 in the Gift Certificate amount filter? Like this: screencast.com/.../JPOvvfCo
Joe, I can filter it to be >0 in a Pivot Table widget, but I'm looking for an indicator widget that reports Total GC Balance and # of GCs. The Transaction amount sums correctly here, but my Gift Certificate count is inflated by 13 because of Gift Certs that have been redeemed, and I'm not sure how to filter those out when the widget is an aggregate like this. Do you know if there's a way?
Ah, yes. I see what your saying. Unfortunately, I can't seem to figure it out. I was playing around with the IF function, but not successful. I was successful in creating the right formula in an excel document, but not sure how to update or convert it in Analytics. Here is my sample that may provide some insight? =countif(C3:C12,">0")
Long story short, use the Gift Certificate Listing report in the Finance folder. It will tell you the number of outstanding gift certificates as well as the amount.
If you want to go down the rabbit hole, from what I can tell:
1. All GCs with a Negative Transaction Amount are examples of a GC purchase.
2. All GC's with a Positive Transaction Amount are examples of a GC that has been redeemed (full or partial redemption, with a purchase date outside off your widget/dashboard timeframe).
3. All GC's with a Transaction Amount = Zero are examples of GSs that have been fully redeemed within your timeframe that you set on your widget/dashboard.
Below are some examples that have transaction dates all within the same month.
The problem is that you must use an aggregate function in the Values section of a widget (for example "SUM([Transaction Amount])." However, you cannot filter by an aggregate amount; you can only filter by specific values (for example, Transaction Amount < 0; notice that it is not a summation/aggregation).
The best I could do is to create two different widgets both counting [# of unique Gift Certificate Number]. One widget counts the number of GCs sold (filter set to Transaction Amount < 0). The other widget counts the number of GCs redeemed (filter set to Transaction Amount > 0).
Like I said at the begining, just use the report.