Average time between visits

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!

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). 

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). 

Returns this:

I updated my comment above. I had MIN and MAX reversed, subtracting MAX from MIN rather then the other way around.

With the MIN/MAX reversed, I get this:

The only filters I have on the widget are for 'Performance Year '= 2019, 2021, and 2022, and 'Title' = GA, but those don't affect the numbers much.

Would you add a filter on the widget for Attended Date as well, or for Attend Flag = Y please? I think we're seeing some Attended Date = 1900-01-01 slipping into the widget. This is the date on the attendance when they did not attend. So if they attended last year, and a ticket this year that has not been marked as attended, then it'll take the attended date from last year and subtract the 1900-01-01 date as the MIN attended date. 

A good point!

That does drop the numbers considerably, which is good. I'll keep poking at this one, but as it stands, the numbers seem viable- 2021 is an outlier here, but the formulas look good.

Thank you, sir!