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")