• Replies 5 replies
  • Subscribers 334 subscribers
  • Views 239 views
  • Users 0 members are here
Related

Tracking donations made with ticket purchases

Angela Herstek-Zongilla
$organization

We are currently trying to run some reports that connect donations made at the time of ticket purchases.  It seems that they are not connected after the gift is made.  Has anyone else had this issue, and how did you amend this issue?  We are trying to determine which shows generate the most donations and are unable to run a report with this information.  In order to do this, we have to do a ton of manual manipulation of the data and guessing.

Thank you for your assistance!  Angela

Parents
  • $organization

    Ah, the joy of online donations!

    I'm unsure of what you mean by "not connected after the gift is made".  The joy of these sort of contributions is that they actually appear in two places - in the Contributions tab of the constituent (from the record in T_CONTRIBUTION) and in the order itself (from the record in T_ORDER_CONTRIBUTION).

    For the kind of analysis you're looking for, I suspect this is some work you will need to do in-house - do you have in-house SQL/SSRS/InfoMaker skills?  I'd be happy to help if you need some assistance.

    Oh - bear in mind that if the order in which the contribution was created has more than one product in it, determining which show was the driver for the donation can be difficult.  The source number might offer a nice alternative?

    Martin 

  • $organization in reply to Angela Herstek-Zongilla

    Angela,

    Yep, we upgraded to v15.0.5 last Friday - which version are you on?

    To be honest, we don't track this so I can't offer any advice on best practice!

    I understand that the BO cancels off the contribution so it's not immediately obvious as the order no longer holds a contribution BUT there's an audit trail in the Transactions tab of the order as the original web order has a transaction of Gift and the BO reversal has the transaction of Adjustment to Gift.

    Here's a very quick and dirty bit of SQL which would show you the value of the original contribution made at the time the order was placed, returning the production season name along with the value of the donation:

    select distinct
    o.order_no
    ,o.order_dt
    ,i.description prod_season_desc
    ,t.trn_amt
    from T_ORDER_CONTRIBUTION oc
    join T_ORDER o
    on o.order_no = oc.order_no
    join T_SUB_LINEITEM sl
    on sl.order_no = o.order_no
    join T_PERF p
    on p.perf_no = sl.perf_no
    join t_inventory i
    on i.inv_no = p.prod_season_no
    join T_TRANSACTION t
    on t.order_no = oc.order_no
    and t.trn_type = 1 -- this transaction is the original donation - type 1 = Gift, type 4 = Adjustment to gift
    join T_CONTRIBUTION c
    on c.ref_no = t.ref_no
    where oc.order_no = <insert order_no here>

    Try this yourself using an example order no - or pass it to your coding team/DBA.  However - beware, it's not elegant code and would return one row for each production season involved in the order.

    Happy to help more of this is useful!

    Martin

  • $organization in reply to Angela Herstek-Zongilla

    Thank you VERY much Martin!  I am going to try this out in test -

    We are currently in the process of updating to v15, so I am going to wait until we are through with the test environment for that purpose before testing this code.

    I really appreciate the input.

    Angela

Reply Children
No Data