Creating a Segment with AND clause returns no data about matomo HOT 9 OPEN

Hi @oraclerob, I see, I can reproduce this, it's definitely a bug. It's also a problem with the core product and not Matomo for WordPress, so I'm going to transfer the issue to their repository. Thanks for taking the time to report this.

Details for the core team:

Bug: segments like actionUrl=@grouper;actionUrl=@reef incorrectly result in no data, which can be seen on demo.matomo.cloud:

has no data (actionUrl=@grouper;actionUrl=@reef): https://demo.matomo.cloud/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#?idSite=1&period=day&date=yesterday&category=General_Visitors&subcategory=Live_VisitorLog&segment=actionUrl%3D%40grouper%3BactionUrl%3D%40reef

has data (actionUrl=@grouper): https://demo.matomo.cloud/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#?idSite=1&period=day&date=yesterday&category=General_Visitors&subcategory=Live_VisitorLog&segment=actionUrl%3D%40grouper

has data (actionUrl=@reef): https://demo.matomo.cloud/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#?idSite=1&period=day&date=yesterday&category=General_Visitors&subcategory=Live_VisitorLog&segment=actionUrl%3D%40reef

The segment SQL that is generated for such a segment is:

 SELECT log_visit.* FROM matomo_log_visit AS log_visit LEFT JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit LEFT JOIN matomo_log_action AS log_action_segment_log_link_visit_actionidaction_url ON log_link_visit_action.idaction_url = log_action_segment_log_link_visit_actionidaction_url.idaction WHERE ( log_visit.idsite in (?) AND log_visit.visit_last_action_time >= ? ) AND (( (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '1') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '3') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '2') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '10')) AND ( (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '1') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '3') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '2') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '10'))) GROUP BY log_visit.idvisit ORDER BY log_visit.idsite DESC, log_visit.visit_last_action_time DESC LIMIT 0, 12

The problem here appears to be that the segment selects from log_link_visit_action instead of log_visit.

from matomo.

Thanks @diosmosis - I thought I had already tested a combination with different events, but obviously not. By using an AND condition with actionURL and Goal it is now working as expected. Brilliant I have a workaround 👍

from matomo.

Hi @oraclerob, can you check whether this segment matches anything in the Visits Log (not in a normal report)?

from matomo.

Hi @oraclerob, can you check whether this segment matches anything in the Visits Log (not in a normal report)?

I did. I created the same segment with an "OR" condition and it returns both visits in the report correctly. As soon as I make it "AND" it returns no data. Any other event or visitor metric also does the same.

from matomo.

Hi @oraclerob, I see, I can reproduce this, it's definitely a bug. It's also a problem with the core product and not Matomo for WordPress, so I'm going to transfer the issue to their repository. Thanks for taking the time to report this.

Details for the core team:

Bug: segments like actionUrl=@grouper;actionUrl=@reef incorrectly result in no data, which can be seen on demo.matomo.cloud:

has no data (actionUrl=@grouper;actionUrl=@reef): https://demo.matomo.cloud/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#?idSite=1&period=day&date=yesterday&category=General_Visitors&subcategory=Live_VisitorLog&segment=actionUrl%3D%40grouper%3BactionUrl%3D%40reef

has data (actionUrl=@grouper): https://demo.matomo.cloud/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#?idSite=1&period=day&date=yesterday&category=General_Visitors&subcategory=Live_VisitorLog&segment=actionUrl%3D%40grouper

has data (actionUrl=@reef): https://demo.matomo.cloud/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#?idSite=1&period=day&date=yesterday&category=General_Visitors&subcategory=Live_VisitorLog&segment=actionUrl%3D%40reef

The segment SQL that is generated for such a segment is:

SELECT
  *
FROM
  log_link_visit_action AS log_link_visit_action LEFT JOIN log_action AS log_action_segment_log_link_visit_actionidaction_url ON log_link_visit_action.idaction_url = log_action_segment_log_link_visit_actionidaction_url.idaction
WHERE
  ( log_link_visit_action.idvisit = ? )
  AND
  (( (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '1') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '3') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '2') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '10')) AND ( (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '1') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '3') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '2') OR (log_action_segment_log_link_visit_actionidaction_url.name LIKE ? AND log_action_segment_log_link_visit_actionidaction_url.type = '10')))

The problem here appears to be that the segment selects from log_link_visit_action instead of log_visit.

Hi @diosmosis thanks for your prompt answer - are you able to show me which file/line to make this fix? I'm surprised this is in core as well as I would think many users would want AND conditions on segments. I need this functionality urgently for a campaign and would rather not wait and make a temporary fix until the patch is out. Many thanks.

from matomo.

@oraclerob Apologies, but this looks like a complicated bug to fix. Regarding the bug itself, it's not that AND conditions don't work, it's that they don't work when two or more action dimensions are used together in a segment. (I understand though that that clarification doesn't help you.)

If I can think of a solution before the core team picks it up I'll notify you, but in either case I don't think a solution will come quickly.

from matomo.

@oraclerob just to confirm, in the example listed above, are you looking for visits with an action URL that has both contains segments (eg, http://mysite.com/booking-confirmation/winter-down-south/) or visits that have one action where the URL contains booking-confirmation and one action where the URL contains booking-confirmation?

from matomo.

@oraclerob just to confirm, in the example listed above, are you looking for visits with an action URL that has both contains segments (eg, http://mysite.com/booking-confirmation/winter-down-south/) or visits that have one action where the URL contains booking-confirmation and one action where the URL contains booking-confirmation?

@diosmosis - we are looking at visits that include 2 or more actionsURLs or Goals. Imagine we have a campaign landing page with a URI of winter-down-south and if that user clicks on a couple more pages then makes a booking with the URI - booking-confirmation - that means that the campaign got us the booking. I hope that makes sense? That's what we want to track. According to the "Goal" setup - I can group URIs to do this, but you cannot? There is only 1 URI you can enter in the Goal setup, therefore I have gone down this path to track the campaign.

from matomo.

@oraclerob thanks for the clarification (to core team: this means pageUrl=@abc;pageUrl=@def being visit that has page URL containing 'abc' and also a page URL containing 'def').

@oraclerob based on your specific needs, there might be alternative segments you can use to achieve the same result. Would you be able to use Referrer Name/Campaign Name to match the winter-down-south campaign, for example? Then you could combine that with Action URL contains booking-confirmation. If you have a goal for booking confirmation, you could also use Visit converted a specific goal ID/Name instead of Action URL contains booking confirmation. Would either of these options work for you?

from matomo.