• Replies 9 replies
  • Subscribers 962 subscribers
  • Views 177 views
  • Users 0 members are here
Related

Analysis of the number of purchases per constituent over the last 10 years

Michael Moore
$organization

Hello all,

We are looking to do some further analysis of our existing constituents and their active buying over fiscal years. The question(s) we are trying to answer is along these lines:

How many patrons purchased 1-10 times in the last 10 fiscal years
How many patrons purchased 11-20 times in the last 10 fiscal years
How many patrons purchased 21-30 times in the last 10 fiscal years

If anyone has a formula for the above that they can pass along, I'd be most appreciative!

Thanks

Mike

Parents
  • $organization

    Hi Michael,

    A pattern like this should do you...

    SUM ( [Constituent ID] , 
      IF (
        ( [# unique Order ID] , [Season Current Fiscal Year Offset between -10 and 0] ) >= 11
        ( [# unique Order ID] , [Season Current Fiscal Year Offset between -10 and 0] ) <= 20
      , 1
      , NULL
      )
    )

    Change the 0 to -1 in the FY Offset if you don't want to count the current FY, or change -10 to -9 if you want the 10 years to include this FY.

  • $organization in reply to Chris Wallingford

    Hi Chris and Nate,

    In addition to counting the number of patrons with orders in certain bucket ranges, I also wanted to see the amount of seats constituents had over the past 10 years in buckets.  So I just swapped order id with total seat count.  That seems to also give me an accurate count in buckets as well.

    SUM ( [Constituent ID] ,
    IF (

    ( [Total Seat Count] , [Season Fiscal Current Year Offset] ) >=1
    AND
    ( [Total Seat Count] , [Season Fiscal Current Year Offset] ) <=10
    , 1
    , NULL
    )
    )

    This gives me something like this:

    Thanks, both of you for your help.

  • $organization in reply to Michael Moore

    Please consider Michael, that when grouping by Fiscal Years on rows, the constituents and seats will be constrained by that. So in 2012 and 1-10 Seats, you may be seeing the count of constituents who held between 1-10 seats ... in 2012. You may need to override the grouping using ALL()...

    ( [Total Seat Count] , [Season Fiscal Current Year Offset] , ALL( [Fiscal Year] ) )

    This will still give you ticket holders in Fiscal Year 2012, but the 1-10 seats will be of those with tickets in 2012, how many have had at least 10 seats over the past 10 years.

Reply
  • $organization in reply to Michael Moore

    Please consider Michael, that when grouping by Fiscal Years on rows, the constituents and seats will be constrained by that. So in 2012 and 1-10 Seats, you may be seeing the count of constituents who held between 1-10 seats ... in 2012. You may need to override the grouping using ALL()...

    ( [Total Seat Count] , [Season Fiscal Current Year Offset] , ALL( [Fiscal Year] ) )

    This will still give you ticket holders in Fiscal Year 2012, but the 1-10 seats will be of those with tickets in 2012, how many have had at least 10 seats over the past 10 years.

Children
  • $organization in reply to Chris Wallingford

    Thanks, Chris

    I adjusted my formula for all the columns,  so it reads now:

    SUM ( [Constituent ID] ,
    IF (

    ( [Total Seat Count] , [Season Fiscal Current Year Offset] , All ([Season Fiscal Year] ) ) >=1
    AND
    ( [Total Seat Count] , [Season Fiscal Current Year Offset] ,All ([Season Fiscal Year] ) ) <=10
    , 1
    , NULL
    )
    )

    Where >=1 and <10 vary for the columns.