• Replies 4 replies
  • Subscribers 961 subscribers
  • Views 337 views
  • Users 0 members are here
Related

Analytics -- First Gift Date and/or fiscal year

Former Member
Former Member $organization

Our finance department would like to be able to tell the board that there were (say) 100 new donors in FY19.

In other words we would like to be able to track how many new donors we get a year regardless of what campaign and then be able to drill down on them.  This is separate from the new, renew, reinstate option in Membership.  They then want to use this criteria be able to say what channel the gifts came in on, what the dollar amount was etc, and even who they were.  By being able to do this for each year we can then see, also, the ebbs and flows of new donors.

Any ideas?

Thanks

Margaret

Parents
  • $organization

    Hi Margaret,

    I very much like Norma's recommendation. On the Analytics side, you could set a constituent element to pull this information in or it's possible to produce some basic output without updating an element.

    For the element, in TX_ANALYTICS_DIVISION_ELEMENT, one would update one of the Custom Date rows by setting:

    • data_select: MIN(cont_dt)
    • data_from: dbo.T_CONTRIBUTION q
    • data_where: cont_amt>0

    Then after the next full load, you'd be able to filter a dashboard by this Custom Date and see all the characteristics of those constituents' contributions within the selected time period. Similar to this post.

    Alternatively, though a little less elegant, one could produce a pivot table with a row for each constituent that shows their first gift amount and date, which could then be filtered to having a year >= 2019. It's not as flexible as having the Custom Date in my opinion, and the value filters are a little a couple levels deep. Here we're seeing a value for Contribution Amount that's filtered (for the constituent of each row) to the MIN Contribution ID (first). Then the same for the Date and Year, being represented as values pulled from the contribution with the MIN Contribution ID for each constituent. Then not visible in the screenshot, the Year value is filtered to 2019 and there's an Amount > 0 filter as well.

    Cheers,
    Chris

Reply
  • $organization

    Hi Margaret,

    I very much like Norma's recommendation. On the Analytics side, you could set a constituent element to pull this information in or it's possible to produce some basic output without updating an element.

    For the element, in TX_ANALYTICS_DIVISION_ELEMENT, one would update one of the Custom Date rows by setting:

    • data_select: MIN(cont_dt)
    • data_from: dbo.T_CONTRIBUTION q
    • data_where: cont_amt>0

    Then after the next full load, you'd be able to filter a dashboard by this Custom Date and see all the characteristics of those constituents' contributions within the selected time period. Similar to this post.

    Alternatively, though a little less elegant, one could produce a pivot table with a row for each constituent that shows their first gift amount and date, which could then be filtered to having a year >= 2019. It's not as flexible as having the Custom Date in my opinion, and the value filters are a little a couple levels deep. Here we're seeing a value for Contribution Amount that's filtered (for the constituent of each row) to the MIN Contribution ID (first). Then the same for the Date and Year, being represented as values pulled from the contribution with the MIN Contribution ID for each constituent. Then not visible in the screenshot, the Year value is filtered to 2019 and there's an Amount > 0 filter as well.

    Cheers,
    Chris

Children
No Data