Clickhouse sessions_v2 column clarifications

[ampaze]

I am working on importing my Matomo data into Clickhouse, but could not find details on the db structure. I also tried to look through the elixir code, but had never even heard of the programming language before.

Would it be possible to give me a few pointers? I am planning to contribute the work for this to maybe help others with their own Matomo import.

I have mostly matched all the columns of the sessions_v2 table, but am unclear on a few of them:

1. sign: how is this set?
2. is_bounce: how is this determined?
3. pageviews and events: Is an pageview also counted as an event? So must events always be greater or equal to pageviews?
4. transfered_from: Can I just fill this with "matomo"?
5. user_id: When exactly is the daily salt changed? At midnight? As Matomos visitors are cookie based, they cannot be used directly. So I would have to generate a user_id from combining visits within a 24h period.

Thanks!

[ruslandoga]

👋 @ampaze

I won't be able to answer all your question but here's what I know:

1. Regarding what sign is see https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree, and here's how it is set:

   analytics/lib/plausible/session/cache_store.ex

   Line 11 in ae0019a

   buffer.insert([%{updated_session | sign: 1}, %{found_session | sign: -1}])

   -- which means if there is a session in the cache, it's collapsed into the new one on insert

2. analytics/lib/plausible/session/cache_store.ex

   Line 54 in ae0019a

   is_bounce: false,

   -- if the session is updated (Plausible cache receives at least one more event for it), it's not a bounce
3. I'd think pageview is counted as an event
4. I don't see any logic for transferred_from https://github.com/search?q=repo%3Aplausible%2Fanalytics+transferred_from&type=code so I'd guess it can be anything

5. analytics/config/runtime.exs

   Lines 433 to 434 in ae0019a

   	# Daily at midnight
   	{"0 0 * * *", Plausible.Workers.RotateSalts},

[ampaze]

Wow thank you!

1. If sign is an implementation detail for sessions that are still being updated, then for imported data this should always be 1. Right?
2. This means is_bounce is the same as events == 1?

[ruslandoga]

Yeah, I'd think so (for both points). But note that I'm not on the core team so I might be wrong.

[ampaze]

I stumpled upon more questions ... 😅

1. In referrer in which cases is the pathname included? Most often this is just the hostname without www.
2. What is entry_meta.key and entry_meta.value for? Sometimes they contain a url?

[ruslandoga]

Sorry, I don't know... 😅🙈

[ampaze]

Okay, so I think I can answer 1. myself. It is probably just a matter of the refererrer policy only allowing the browser to send the origin in most cases, especially these days.

[ampaze]

For anyone interested in what I ended up with. I made a Gist for the whole process:

https://gist.github.com/ampaze/3a22d3c2adbc1d0bbeff409966ccd132