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

Chris Wallingford HI Chris - this is super helpful as I was looking for an approach on this as well.  I just wanted to clarify - is this based on their attendance or orders?  
I'm trying to figure out the time in between individual orders that a constituent might place in a season.  So just wanted to see if I should make any modifications for what you had outlined in your approach.  Thanks!

Hi Michael,

If you swap out the [Performance Date] fields with the equivalent [Order Date] or [Attended Date] fields, you will get average time between orders or attendance.

Best,
Chris

Thanks Chris.  This will be super helpful!

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 again Chris,

(For some reason I can only reply to my message, not to yours.)

This was very helpful, thank you. I need to bucket the visitors so the  Every year, Every second year formulas is exactly what I'm after. I've tried to set it up in analytics. However, I have difficulty with the Performance date calendar year - could you tell me exactly which one to use?

The only one that I have gotten to work is Calender Year To Date Flag (but I actually don't know what that is so it might be completely wrong...). 

Thanks for all your help!

Sofia

Hi Sofia,

I'm trying, poorly , to reference the [Calendar Year] field under that [PERFORMANCE DATE] dimension in your screenshot.

And the [# of unique Years in Performance Date] is meant (also easier with a screenshot) to direct you to this value:

I have that same problem with "reply" sometimes, which I'll raise with our website team, but as a work around, clicking on the date of the post in its header will refresh the page and often fix it to present the "reply" link.

Thanks Chris for clarifying that (and for the tip of clicking on the date to get the "reply" link to appear - worked great). I'm doing something wrong though because I'm still not getting a value. This is how my formula looks:

When choosing Calendar year I select # All Items - perhaps that's where I go wrong? 

Sorry for taking up so much of your time. I get the logic of the formula, but I'm not fluent in Analytics yet 

Thanks,

Sofia

Not a problem at all Sofia, and my apologies, as I had a typo in my formula above. I'm going to correct it up there too, but we need more parentheses around the numerator and denominator content...

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

As it is now, I led you to take the number of unique years, minus (1/max calendar year), minus min calendar year. I'm attaching a sample as well.

EveryNthYear.dash

That did the job! Thank you so much Chris - this will give us important insights. 

Best,

Sofia

I'm looking to adjust the formula to look at 'time between Attended Dates'. When I go to adjust the formula to reference attendance, not performance dates, I can't seem to replicate the formula:

When I delete 'Perf Year' 

And substitute in Attended Date / Calendar Year

It gives me this error message:

I feel there's something I'm missing with my clicks.

HI Nathanael,

From that ATTENDED DATE dimension(table), instead of [Date] field, please try the [Calendar Year] field.

It yields some very large numbers. What needs tweaking?

Hi Nathanael, 

There's a few things going on... 

• For both of those ( [Max Calendar Year], they need to the "all items" version of that field inside a MAX aggregation, like MAX ( [Calendar Year]
• For both of the [Total Calendar Day of Year] )  they need to the "all items" version of that field, so just [Calendar Day of Year] ). 

This portion of the formula for each [Constituent ID] is producing a unique value for every distinct attendance date so that we can compare their MIN attendance date with their MAX attendance date. We're doing this conversion of attendance date to a number because Analytics formulas don't support taking the MIN or MAX of a Date field in a formula, so we can't use that as a basis of comparison for constituents' attended dates.

Instead, if my first attended date was 2021-05-20 (the 140th day of 2021), and my last attended date was 2022-04-15 (the 105th day of 2022), we can use the [Calendar Year] and [Calendar Day of Year] to produce these values:

• (2021 * 365) + 140 = (737,665) + 140 = 739,705
• (2022 * 365) + 105 = (738,030) + 105 = 738,135

Altered into these numerical representations we can compare the difference between these MIN and MAX values as a count of days between them.

MAX ( [Calendar Year All Items] * 365 + [Calendar Day of Year All Items] )

We do not want the MIN / MAX of each field separately, as the the Max Calendar Day of Year could have occurred in a year other than the Max Calendar Year. So not this:

( [Max Calendar Year] * 365 + [Max Calendar Day of Year] )

The numerator then looks something like this:

SUM ( [Constituent ID] , 
   MAX ( [Attended Date > Calendar Year > All Items] * 365 + [Attended Date > Calendar Day of Year > All Items] )
   - MIN ( [Attended Date > Calendar Year > All Items] * 365 + [Attended Date > Calendar Day of Year > All Items] )
)

And then I think your denominator looks correct as (the sum of all constituent's counts of unique attended dates) minus (the unique count of constituents).