Interactions rows for different people have the same InteractionSessionId

[stanalyst]

Description

It is our understanding that an InteractionSession row should only be related to Interaction rows for one distinct person - not multiple people. We have discovered in our database that this is not always the case. In fact out of 16,566,269 total InteractionSession rows - 4,912 of these have more than 1 person's interactions related to them. I realize this is less than 1% - but shouldn't this be 0%?

Here is the SQL I used to find the rows:

SELECT  s.[Id] AS 'InteractionSessionId'
        , ic.[Name] AS 'InteractionChannel'
        , ic.[ChannelTypeMediumValueId] AS 'ChannelMedium'
        , s.[CreatedDateTime] AS 'SessionCreatedDate'
        , s.[DurationSeconds] AS 'SessionDurationSeconds'
        , COUNT(DISTINCT pa.[PersonId]) AS 'DistinctPersonCount'
FROM    [InteractionSession] s
INNER JOIN  [Interaction] i
    ON  s.[Id] = i.[InteractionSessionId]
INNER JOIN  [PersonAlias] pa
    ON  i.[PersonAliasId] = pa.[Id]
INNER JOIN  [Person] p
    ON  p.[Id] = pa.[PersonId]
    AND p.[RecordTypeValueId] = 1
LEFT OUTER JOIN [InteractionChannel] ic
    ON  ic.[Id] = s.[InteractionChannelId]
GROUP BY s.Id
        , ic.[Name]
        , ic.[ChannelTypeMediumValueId]
        , s.CreatedDateTime
        , s.[DurationSeconds]
HAVING  COUNT(DISTINCT pa.[PersonId]) > 1
ORDER BY 'DistinctPersonCount' desc
        , s.[Id];

I also posted about this in the Troubleshooting and Rock-Stars channels. So far 2 other rock-stars have found similar results when they run this SQL on their databases. I also ran this on the prealpha site - and found 3 rows. I did not find any rows on the demo site - but I'm guessing that's because it is reset too frequently.

Actual Behavior

We have over 4000 InteractionSession rows that related to Interactions for more than 1 person.

Expected Behavior

I would expect that all of the Interaction rows related to a single InteractionSession are for the same individual person - NOT for multiple people. Unless we just really don't understand how these sessions are supposed to work.

Steps to Reproduce

I don't know - but RUN the above SQL on prealpha and you should see those rows. And based on our results and the results from the other churches - there does seem to be a correlation between total Sessions and how many have multiple people.

Issue Confirmation

• Perform a search on the Github Issues to see if your bug is already reported.
• Reproduced the problem on a fresh install or on the demo site.

Rock Version

17.6.1 and PreAlpha

Client Culture Setting

en-US

[nairdo]

...out of 16,566,269 total InteractionSession rows - 4,912 of these have more than 1 person's interactions related to them. I realize this is less than 1% - but shouldn't this be 0%?

@stanalyst This won't always be 0%. Below are a few of reasons that I'm aware of. Let me know if you've found a case outside of these.

Here is a variation of your query that shows who the people are (they are sorted by interaction session id, so you'll want to keep that in mind... I also limited mine down to interactions since 1/1/2026, InteractionDateKey > 20260101)

SELECT p.FirstName, p.LastName, d.Name, s0.IpAddress, i.InteractionDateTime, i.InteractionSessionId, i.InteractionSummary, i.InteractionData 
FROM Interaction i 
INNER JOIN PersonAlias pa ON pa.Id = i.PersonAliasId
INNER JOIN Person p on p.Id = pa.PersonId
INNER JOIN InteractionSession s0 ON s0.Id = i.InteractionSessionId
INNER JOIN InteractionDeviceType d ON d.Id = s0.DeviceTypeId
WHERE InteractionSessionId IN (
            -- variant of "duplicate sessions" query
            SELECT  s.[Id] AS 'InteractionSessionId'
            FROM    [InteractionSession] s
            INNER JOIN  [Interaction] i
                ON  s.[Id] = i.[InteractionSessionId]
            INNER JOIN  [PersonAlias] pa
                ON  i.[PersonAliasId] = pa.[Id]
            INNER JOIN  [Person] p
                ON  p.[Id] = pa.[PersonId]
                AND p.[RecordTypeValueId] = 1
            LEFT OUTER JOIN [InteractionChannel] ic
                ON  ic.[Id] = s.[InteractionChannelId]
            GROUP BY s.Id
                    , ic.[Name]
                    , ic.[ChannelTypeMediumValueId]
                    , s.CreatedDateTime
                    , s.[DurationSeconds]
            HAVING  COUNT(DISTINCT pa.[PersonId]) > 1
    ) 
AND InteractionDateKey > 20260101
ORDER BY i.InteractionSessionId, i.InteractionDateTime

Anonymous After Logout or Combos

Depending on what the person is doing in their browser, you'll see records like these:

Rock Impersonation

An admin using impersonation will share the session too:

Browser Sharing and Login

If you're testing (or similar) and let the other person login using your browser/kiosk, it can show up as the same session:

[stanalyst]

@nairdo First let me say THANK YOU - this helps me understand much better what we are seeing and provided much needed context. Most of the duplicates we were seeing (but not all) fell into the 3 types you listed. But there are a couple scenarios in our data that still confuse/concern us.

The worst one is the InteractionSession row that has a Guid of all 0's. It has over 750 Interaction rows associated with it. The earliest row is from June 23, 2024 - and the latest is from today. Each time new Interactions are added that use this InteractionSession's Id - it causes our Populate Interaction Session Data job to throw an error when it runs. QUESTION: How do we 'fix' it so this Session is no longer used for new interactions? Do we just need to delete the InteractionSession row and all related rows on associated tables?
BTW - here is a spreadsheet for all the interactions with this session... SessionWithBadGuidOpenYears.xlsx

The other behavior we're seeing that confuses us - is when an Interaction Session is 'reused' over a long period of time. Is this normal? Here are 3 excel files showing what we're talking about...

SessionOpenOver300Days.xlsx

SessionReusedAfter36Days.xlsx

SessionReusedAfter19Days.xlsx

I'll admit I've probably made some assumptions about what an Interactive Session is supposed to represent. Based on this documentation...

"An Interaction Session is like a snapshot of the person's entire visit to your site. It groups all the interactions that took place during a single browsing session, capturing important details like which device the visitor used, when the session began, and how long they stayed. Interaction. Sessions give you a full picture of a person’s overall experience during their visit, even if they hop between multiple pages."

...could you clarify what "a single browsing session" means? Should these be spanning weeks, months and in some cases years?

[nairdo]

The worst one is the InteractionSession row that has a Guid of all 0's.
...
How do we 'fix' it so this Session is no longer used for new interactions?

@stanalyst The most likely spot where the Guid.Empty (00000000-...) comes from is here in RockPage.cs, although I've not spotted how the Session["RockSessionId"] would be missing. At this point, I'd guess possibly some nefarious bot behavior... but regardless, I will look into changing that from Guid.Empty to Guid.NewGuid.

I also know we have at least one place in the code that looks to ignore it:

Rock/Rock/Jobs/PopulateInteractionSessionData.cs

Line 264 in ce8990e

if ( interactionSession.Guid == Guid.Empty )

But I would not try to delete the data at this point.

"a single browsing session" means? Should these be spanning weeks, months and in some cases years?

I'm not sure there is a cap on how long a session can last.

[nairdo]

@stanalyst Stan, would you be able to RocketChat/DM me a private link to an SSMS or Data Studio export of those 750 interaction rows tied to the 00000 session? We're trying to do some correlation analysis that that would help.