Hi, I’m planing on running a website to do a/b testing, and I’m having the following issue
I need to show information of several sites at the same time in a dashboard that just show visits, unique visits, conversion, unique conversions. This values are total values, from the moment the site_id was created on.
Up to a while I had the following query that worked pretty well
$visits_sql = "SELECT cv.count_visits, cuv.count_unique_visits, cc.count_conversion, cuc.count_unique_conversion FROM
(SELECT count(*) as count_visits FROM piwik_log_link_visit_action
WHERE idsite = ".$item->piwik_site_id.") as cv,
(SELECT count(*) as count_unique_visits FROM
(SELECT * FROM piwik_log_link_visit_action GROUP BY idvisitor) as temp
WHERE idsite = ".$item->piwik_site_id.") as cuv,
(SELECT count(*) as count_conversion FROM piwik_log_conversion
WHERE idsite = ".$item->piwik_site_id.") as cc,
(SELECT count(*) as count_unique_conversion FROM
(SELECT * FROM piwik_log_conversion GROUP BY idvisitor) as temp
WHERE idsite = ".$item->piwik_site_id.") as cuc";
I suspect that I could make it better, but it runs pretty smooth.
So I started looking at the api for another reason and I figured out that if the logs are archived it should be faster than doing my own sql query, but right now the api is quite a lot slower! I’ve tried with api.get with periods of last 3 years and for +20 websites it can take more than 30s in complete the query.
Is it appropriated to use the api for this type of query? maybe is looking for a lot more information that the one I need and thats slowing me down.
Also, does my current query going to get slow when I pick up visitors?
My regards
Francis