Cross websites reporting: Let me see the number of users who browsed two websites (new plugin)

[mattab]

The goal of this ticket is to create a new plugin published on the Marketplace that will let a user pick two websites from the list of tracked websites in Piwik, and will then output the number of unique users that visited both websites in the selected time period.

Proposal

• in All Websites dashboard, add a new link Users viewing several websites
• when clicked, it displays (in a popover?) the following: Visitors who viewed [Website Selector] and also viewed [Website selector]
• when user selects the two websites, the report is displayed below, eg. 10% of users who browsed SiteA also viewed SiteB. That represents 560 users.
• on hover on SiteA and SiteB, display tooltip Total visitors: $visitors
• in order to recognise users across websites, we will use the fingerprint hash processed by Piwik config_id

Tasks

• Create plugin
• The cross reporting metric is offered via API
• Add test, and screenshot test
• Publish on Marketplace

Note: for more advanced cross websites reporting, check out the RollUp Reporting plugin. see also #6079

[tassoman]

This plugin would be nice for users with many Sites, we have dozens. How this could involve outgoing clicks? Would be good also appending to its stat a revised "outgoing links" minus the "between sites" amounts. Another part that would be affected by this would be the tansaction graphs: "from websites" would be splitted in "from others websites", "from our websites"

[kylekatarnls]

+1 It would be very great!

[mattab]

How this could involve outgoing clicks? Would be good also appending to its stat a revised "outgoing links" minus the "between sites" amounts.

That's a good idea.

[mattab]

For the SQL query to detect unique users across websites, maybe this is a good and fast option?

Example to get number of cross users across website 1 and 2:

SELECT count(distinct config_id)
FROM
(
   SELECT log_visit_t1.config_id
    FROM piwik_log_visit as log_visit_t1 
        INNER JOIN piwik_log_visit as log_visit_t2 
    WHERE log_visit_t1.config_id = log_visit_t2.config_id 
       AND log_visit_t1.idsite = 1 
       AND log_visit_t2.idsite = 2  
 ) as config_ids

[kylekatarnls]

Maybe a newbie question, but is it different from this:

SELECT COUNT(DISTINCT log_visit_t1.config_id) AS config_ids
    FROM piwik_log_visit as log_visit_t1 
        INNER JOIN piwik_log_visit as log_visit_t2 
    WHERE log_visit_t1.config_id = log_visit_t2.config_id 
       AND log_visit_t1.idsite = 1 
       AND log_visit_t2.idsite = 2

?

[tassoman]

I've ran both against my test db and tooks abt 297ms in the first case and abt 234ms in second case. Result was 749

[mattab]

@kylekatarnls good point we don't need the outer query, yours works better :+1:

[diosmosis]

Plugin is here: https://github.com/PiwikPRO/plugin-InterSites

Closing ticket.

@tassoman I put your suggestion here: https://github.com/PiwikPRO/plugin-InterSites/issues/1

[mattab]

InterSites not compatible with Piwik 3, re-opening

[mattab]

We've released a new premium plugin which aggregates data from multiple websites, mobile apps and shops into a Roll-Up site to gain new insights and save time. Also gives you a view of visitors across several websites/apps.

Get it here: https://plugins.piwik.org/RollUpReporting