[Bug] For same idvisit, idvisitor is different in different tables.

[AltamashShaikh]

What happened?

We received a complaint from a customer that AdvertisingConversionExport plugin exports less data when they configure an export with and without clickIds.

On debugging the queries on Cloud, we came to know that issue is due to our join logic, when report is configured to export visits with clickIds, we join on idvsit log_clickid.idvisit = log_conversion.idvisit and when its configured to export all visits we join on idvisitor log_conversion.idvisitor = log_clickid.idvisitor and hence we see the difference.

We debugged further with this query to see the impact and found a mismatch of 2057 rows.

Select count(*) from log_clickid a, log_conversion b where a.idvisit=b.idvisit and a.idvisitor!=b.idvisitor and a.server_time between '2024-08-13 00:00:00' and '2024-09-13 00:00:00';

We also ran similar query between log_visit and log_conversion table and saw a similar mismatch of 905 rows

Select count(*) from log_visit a, log_conversion b where a.idvisit=b.idvisit and a.idvisitor!=b.idvisitor and b.server_time between '2024-08-13 00:00:00' and '2024-09-13 00:00:00';

What should happen?

Idvistor should be same across all tables.

How can this be reproduced?

Check JIRA.

Matomo version

5.1.1

PHP version

No response

Server operating system

No response

What browsers are you seeing the problem on?

No response

Computer operating system

No response

Relevant log output

No response

Validations

• Read our Contributing Guidelines.
• Follow our Security Policy.
• Check that there isn't already an issue that reports the same bug to avoid creating duplicates.
• The provided steps to reproduce is a minimal reproducible of the Bug.

[sgiehl]

This problem also occurs when using cookie consent. When a website has the tracker configured to require cookie consent all tracking requests sent before cookie consent was given will be sent without a visitorid (generated by the tracker). This causes the actions to be grouped to a visit on backend side.
Once the cookie consent was given, Matomo tracker will generate a visitorid and store it in the cookie - to be able to identify the visitor later again. This generated visitorid always differs from the one, that was previously generated by the backend. So the first tracking request sent with the new visitorid will update such in the database.
This then also results in the problems described in the issue.

[utrautmann]

Three customers are currently affected in my projects. All work cookieless and with cookies after consent has been given. The change to the VisitorId described here, particularly in the AdvertisingConversionExport plugin mentioned above, means that the click IDs of new visitors who are initially tracked cookieless and later with cookies are not found during export.

This means that my customers lose a lot of conversions during export.

It is important for me and especially for my customers that this topic is given high priority.

[peterbo]

Hey everyone, as discussed before, I and customers of the (AdvertisingConversionsExport) Plugin are affected as well. In many cases, this is critical, because the campaign optimization (ROAS) depends on a more or less precise number of conversions being exported. If not, the optimization can not work effectively and therefore, campaigns can become expensive and ineffective.