• Replies 3 replies
  • Subscribers 961 subscribers
  • Views 97 views
  • Users 0 members are here
Related

Repeat Cycle

Sarah Mischner
$organization

Hey all!

I've been working on trying to get a clear picture of the repeat cycle at Shedd. I am quantifying this as time between visits, and calculating based off of order data using average time (months or days) between the latest and second most recent visit. Wondering if anyone else in the museum world especially has been working with this type of analysis and might have recommendations for how to manage this (particularly any recs on time parameters and  nuance on visit parameters!).

Thanks!

Parents
  • $organization

    Hi Sarah, 

    I found this (+) Average time between visits - Reporting & Analytics - Forums - Tessitura Network

    But it doesn't focus on only the last two visits for each constituent. There isn't something natively in Analytics that will flag up those last two visits in a way that will allow us to then compare the time between them and aggregate those results across all constituents. We can get a widget to tell us what those last two visit dates were, but then we're stuck...

    To get what you're after, I recommend configuring two constituent division elements, Custom Date 01 and Custom Date 02 let's say, to be populated with constituents' most recent two distinct attendance dates.

    Then in Analytics, a formula like this will return the overall average days between constituents' most recent two attendance dates.

    AVG ( [Constituent ID] , MAX ( DDIFF ( [Days in Custom Date 01] , [Days in Custom Date 02] ) ) )

    An alternative to Attended Dates is to use Order Dates if you're excluding sales prior to visit.

  • $organization in reply to Chris Wallingford

    I had a fiddle with this and was able to perform the whole calculation in Analytics....

    It is possible to create a formula that recognizes the rank of the Attended Date, and conditionally render that rank as 1 or 2, otherwise show nothing.

    IF(
      RANK(
        MAX([Calendar Year] * 10000 + [Calendar Month] * 100 + [Calendar Day Of Month]), "DESC", "1223", [Constituent ID]
        ) = 1, 1, NULL
     )

    And then enhance that formula to show a value form of the Attended Date formatted as the (Calendar Year * 365) + (Calendar Day of Year). With that formatting, it's reasonably accurate to subtract one date value from another and get the number of days in between. DDIFF() functions don't support applying filters in-line, so this numeric approach is necessary.

    IF(
      RANK(
        MAX([Calendar Year] * 365 + [Calendar Day Of Year]), "DESC", "1223", [Constituent ID]
        ) = 1
      ,MAX([Calendar Year] * 365 + [Calendar Day Of Year])
      ,NULL
    )

    Proving out that a formula can reference the distinct results across the ranking, here their combined to look at the top 2 ranks and compare them... the first formula to get the days between the dates ranked 1 and 2. For each constituent's attended dates exists one with rank of 2, then subtract the date value for the attended date with rank of 1 from the attended date with rank of 2 to get the number of days between those two ranked attended dates. Otherwise, return nothing (NULL).

    SUM([Constituent ID],
    IF(
        MAX([Days in Date],
            IF(
                RANK(
                    MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                NULL
            )
        ) > 0
        , MIN([Days in Date],
            IF(
                RANK(
                    MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
            )
        ) -
        MIN([Days in Date],
            IF(
                RANK(
                    MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
            )
        )
        , NULL
    )
)

    To take the average from across those days between dates, but with some special handling to avoid counting constituents who don't have the required 2 visits to be included in the metric. If the previous formula returns a not NULL value, then use that previous formula as the numerator and divide by the number of constituents having an attended date of rank 2 (excluding those with only single attendance). 

    IF(
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0,
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ) -
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ), NULL
        )
    ) > 0,
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0,
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ) -
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ), NULL
        )
    ) 
    /
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0, 1, NULL
        )
    ), NULL
)

    It's weedy, but it works.

    The dashboard is filtered by a list of constituents with tickets to the current and prior season fiscal years. The formulas should respect dashboard filters, so if there were to be filtered to only a Daily Admissions production season, the results should adjust as expected. The Constituent ID filter would need to be updated as well to reflect those changes, or switch to an actual Lists filter if that feels more effective.

    RepeatCycle.dash

Reply
  • $organization in reply to Chris Wallingford

    I had a fiddle with this and was able to perform the whole calculation in Analytics....

    It is possible to create a formula that recognizes the rank of the Attended Date, and conditionally render that rank as 1 or 2, otherwise show nothing.

    IF(
      RANK(
        MAX([Calendar Year] * 10000 + [Calendar Month] * 100 + [Calendar Day Of Month]), "DESC", "1223", [Constituent ID]
        ) = 1, 1, NULL
     )

    And then enhance that formula to show a value form of the Attended Date formatted as the (Calendar Year * 365) + (Calendar Day of Year). With that formatting, it's reasonably accurate to subtract one date value from another and get the number of days in between. DDIFF() functions don't support applying filters in-line, so this numeric approach is necessary.

    IF(
      RANK(
        MAX([Calendar Year] * 365 + [Calendar Day Of Year]), "DESC", "1223", [Constituent ID]
        ) = 1
      ,MAX([Calendar Year] * 365 + [Calendar Day Of Year])
      ,NULL
    )

    Proving out that a formula can reference the distinct results across the ranking, here their combined to look at the top 2 ranks and compare them... the first formula to get the days between the dates ranked 1 and 2. For each constituent's attended dates exists one with rank of 2, then subtract the date value for the attended date with rank of 1 from the attended date with rank of 2 to get the number of days between those two ranked attended dates. Otherwise, return nothing (NULL).

    SUM([Constituent ID],
    IF(
        MAX([Days in Date],
            IF(
                RANK(
                    MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                NULL
            )
        ) > 0
        , MIN([Days in Date],
            IF(
                RANK(
                    MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
            )
        ) -
        MIN([Days in Date],
            IF(
                RANK(
                    MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
            )
        )
        , NULL
    )
)

    To take the average from across those days between dates, but with some special handling to avoid counting constituents who don't have the required 2 visits to be included in the metric. If the previous formula returns a not NULL value, then use that previous formula as the numerator and divide by the number of constituents having an attended date of rank 2 (excluding those with only single attendance). 

    IF(
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0,
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ) -
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ), NULL
        )
    ) > 0,
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0,
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ) -
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ), NULL
        )
    ) 
    /
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0, 1, NULL
        )
    ), NULL
)

    It's weedy, but it works.

    The dashboard is filtered by a list of constituents with tickets to the current and prior season fiscal years. The formulas should respect dashboard filters, so if there were to be filtered to only a Daily Admissions production season, the results should adjust as expected. The Constituent ID filter would need to be updated as well to reflect those changes, or switch to an actual Lists filter if that feels more effective.

    RepeatCycle.dash

Children
No Data