Average time between visits

Hi Sofia,

This is calculable in Analytics if you'd like to take that approach. When looking at this metric by constituent, we can calculate the number of days between their earliest event date and the last event date, and then divide that by their count of visits minus one. The count minus one is to count spans between visits rather than the visits themselves.

To get the earliest performance date into a number format with which we can do some math, we might try this:

MIN( [Performance Date Calendar Year]*365 + [Performance Date Calendar Day Of Year] )

To get the latest date, use the same formula with MAX instead of MIN. And to get the count of days between the two, subtract the MIN formula result from the MAX formula result.

To determine the count of visits, we could count unique event dates and then subtract 1 from that:

[# of unique Days in Performance Date] - 1

The whole formula then might look like:

( 
MAX( [Performance Date Calendar Year]*365 + [Performance Date Calendar Day Of Year] )
- MIN( [Performance Date Calendar Year]*365 + [Performance Date Calendar Day Of Year] )
) 
/ ( [# of unique Days in Performance Date] - 1 )

To move this up a level and report on it's change over time, or just as an overarching KPI, we need to alter the formula slightly to calculate the result for each constituent, and then aggregate those results across all constituents. This is referred to as Multi-Pass Aggregation. In this case, we'll group each of the formulas by [Constituent ID], and then take the sum of the results from each constituent.

SUM ( [Constituent ID] ,
MAX( [Performance Date Calendar Year]*365 + [Performance Date Calendar Day Of Year] )
- MIN( [Performance Date Calendar Year]*365 + [Performance Date Calendar Day Of Year] )
) 
/ SUM ( [Constituent ID] , [# of unique Days in Performance Date] - 1 )

Chris

Hi Chris Wallingford if I wanted to break down fiscal year by artform would each row show me the days between visits for that artform specifically ie  for Visual Arts would the results give me the days between a constituent's visits for that artform only?

Yes Ogo,

The formula would still take the MIN/MAX date for each constituent, but within each artform within each fiscal year.

Cheers

Yes Ogo,

The formula would still take the MIN/MAX date for each constituent, but within each artform within each fiscal year.

Cheers

Thanks!