• Replies 2 replies
  • Subscribers 701 subscribers
  • Views 102 views
  • Users 0 members are here
Options
Related

5 Year Return Analysis

Emily Davies
$organization

Hi There! Thanks to these forums I have managed to put together a cracking new/return/lapse/churn dashboard - thank you!

I'm now looking to do a 5 year analysis with the following categories:

% attended every year for last 5 years

% attended only once in last 5 years

% attended more than once but less 5 of the last 5 years

I tried to do this in the same way I did the new/return/lapse widget. Because our Festival runs November - March I filter by Performance Detail Title. I thought I could do something like ([# of unique Constituent ID], [title 1]  AND [title 2] AND [title 3]) etc where each title is the Festival year. This doesn't seem to work. Any suggestions on how to get % attended for last 5 years and % attended only once in last 5 years? 

Thanks in advance!

Emily

  • $organization

    Hi Emily,

    For a Column or Bar widget filtered to include the last 5 years-worth of Festival runs, without anything on Categories, add 3 value formulas.

    % attended every year for last 5 years

    SUM ( [Constituent ID] , IF ( [# unique Season Fiscal Year] = 5 , 1 , 0 ) ) / [# unique Constituent ID]

    % attended only once in last 5 years

    SUM ( [Constituent ID] , IF ( [# unique Season Fiscal Year] = 1 , 1 , 0 ) ) / [# unique Constituent ID]

    % attended more than once but less 5 of the last 5 years

    SUM ( [Constituent ID] , IF ( [# unique Season Fiscal Year] > 1 AND [# unique Season Fiscal Year] < 5, 1 , 0 ) ) / [# unique Constituent ID]

    Each of these is returning the unique count of Season Fiscal Years in which each constituent held tickets, and then if they meet the criteria of having all 5 that are in the widget, or only 1, or something in between, then count them in the associated bucket.