Average time between visits

Hi Chris,

Great, thank you so much! I now have calculated this, but when doing so I realized that I also need to know the distribution of our customers in terms of how often they visit us, i. e. how large share visit us every year, every second year, every third year etc. Again I need help. Do you, or anyone else, know how I should go about getting this result?

Best,

Sofia

Hi Sofia,

Is that switching from calculating days between to years between visits to get an average?

SUM ( [Constituent ID] ,
  MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] )
) 
/ SUM ( [Constituent ID] , [# of unique Years in Performance Date] - 1 )

Or do you need to bucket / segment by counts of visitors that come with each frequency? If so, create a value called Every Year that takes the total distinct count of years in which constituents visited, and divides that by the count of years between their first and last visit. Only counting for constituents that attended in more than one year. This could be adjusted down if you want someone who attended 9 years in the last 10 to count as every year.

SUM ( [Constituent ID] ,
  IF ( ( [# of unique Performance Date Calendar Years] - 1 )
       / ( MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] ) )
       >= 1.0
  , 1
  , NULL
  )
)

While Every Second Year might be constituents who attended in a count of years that is between 50%-99.9% of all years between their first and last year. If you adjust the Every Year value down to less than 1.0, then adjust this percentages in this one to align with your preferences as well.

SUM ( [Constituent ID] ,
  IF ( ( [# of unique Performance Date Calendar Years] - 1 )
       / ( MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] ) )
       < 1.0
       AND 
       ( [# of unique Performance Date Calendar Years] - 1 )
       / ( MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] ) )
       >= 0.5 
 , 1
 , NULL
 )
)

For the Every Third Year value, you might do < 0.5 AND >= 0.333, etc.

Best,
Chris

Hi Sofia,

Is that switching from calculating days between to years between visits to get an average?

SUM ( [Constituent ID] ,
  MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] )
) 
/ SUM ( [Constituent ID] , [# of unique Years in Performance Date] - 1 )

Or do you need to bucket / segment by counts of visitors that come with each frequency? If so, create a value called Every Year that takes the total distinct count of years in which constituents visited, and divides that by the count of years between their first and last visit. Only counting for constituents that attended in more than one year. This could be adjusted down if you want someone who attended 9 years in the last 10 to count as every year.

SUM ( [Constituent ID] ,
  IF ( ( [# of unique Performance Date Calendar Years] - 1 )
       / ( MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] ) )
       >= 1.0
  , 1
  , NULL
  )
)

While Every Second Year might be constituents who attended in a count of years that is between 50%-99.9% of all years between their first and last year. If you adjust the Every Year value down to less than 1.0, then adjust this percentages in this one to align with your preferences as well.

SUM ( [Constituent ID] ,
  IF ( ( [# of unique Performance Date Calendar Years] - 1 )
       / ( MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] ) )
       < 1.0
       AND 
       ( [# of unique Performance Date Calendar Years] - 1 )
       / ( MAX( [Performance Date Calendar Year] ) - MIN( [Performance Date Calendar Year] ) )
       >= 0.5 
 , 1
 , NULL
 )
)

For the Every Third Year value, you might do < 0.5 AND >= 0.333, etc.

Best,
Chris