Campaign view count seems to display invalid values

[marcinkunert]

Version:

• listmonk: v2.1.0
• OS: Docker

I've noticed a weird behaviour in displaying campaign view count.

select count(*) from campaign_views where campaign_id = 9; -- result: 4955
select count(distinct subscriber_id) from campaign_views where campaign_id = 9; -- result: 2830

With individual subscriber tracking enabled.

I've made sure the option is enabled. I've even disabled and enabled it to be sure.

Expected 2830, got 4955 (looks like is not counting the distinct value)

Expected 2830, got 3564. This seems to be the sum of the day values:

select EXTRACT(day FROM created_at) as day, count(distinct subscriber_id) from campaign_views
where campaign_id = 9
group by EXTRACT(day FROM created_at);

returns

day;count
2;2066
3;792
4;242
5;89
6;71
7;78
8;45
9;51
10;28
11;32
12;16
13;22
14;15
15;16
16;1

after summing up the count: 3564

Without individual subscriber tracking enabled.

Looks in both list view and detail view.

[NicoHood]

@knadh Any comment from your side? This issue is currently keeping me from upgrading to the lastest version.

[marcinkunert]

@NicoHood these values are being calculated in runtime and are not persisted, so there is not much risk involved in upgrading

[knadh]

I am yet to debug this, but like @marcinkunert said, it's just a read query. Has no effect on how data is stored internally.

[knadh]

Ah!

listmonk/queries.sql

Line 577 in 3b0c8b3

SELECT campaign_id, COUNT(%s) AS "count", DATE_TRUNC((SELECT * FROM intval), created_at) AS "timestamp"

The distinct (subscriber_id) applies to each day (GROUP BY timestamp), not the entire group of all days. Glaring miss. Have to change the query so that the it only considers the first occurrence of a subscriber_id across all days.

[NicoHood]

Will we see a bugfix release for those fixes, or do we have to wait for a new minor release?

[knadh]

These will be fixed and merged to master. You'll be able to run an RC version without waiting for a versioned release.

[NicoHood]

I personally would always go for a tagged release. Especially if it is gpg signed ;-) #711

[knadh]

RCs can be RC-versioned and GPG signed :)