Hi Folks
I am trying to replicate some Google Analytics “Real-time” functionality in Piwik and would appreciate some help and/or advice.
In both instances my Piwik extracts differ from those reported by GA.
Since several forum posts have made it clear that it is unlikely that the two will match I wonder if somebody would mind having a look at my SQL and let me know if I am on the right track (or missing the point completely).
- Pageviews per minute (typically for the last 30 minutes)
I am additionally trying to show the split between new and returning (GA simply shows the total per minute).
The differences for this metric do not appear to have a pattern (as best as I can tell).
Sometimes GA is greater than Piwik, sometimes vice versa but generally Piwik seems to report more.
The SQL which I run against the Piwik database:
SELECT
CAST(TRUNCATE(UNIX_TIMESTAMP(server_time)/(1 * 60), 0) * (1 * 60) AS UNSIGNED) as start_time,
count(1) as count,
visitor_returning
FROM
piwik_log_link_visit_action
INNER JOIN
piwik_log_visit
ON piwik_log_link_visit_action.idvisit = piwik_log_visit.idvisit
WHERE
piwik_log_visit.idsite = 2
AND
(server_time >= '2012-06-24 22:25:00' and server_time < '2012-06-25 00:55:00')
GROUP BY
start_time,
visitor_returning
[The purpose of the “start_time” column is to truncate the server_time to the nearest minute]
- Active visitors on site right now (split between new and returning)
For this metric my Piwik result is far higher than GA (for example Piwik=110, GA=35) so I suspect that my Piwik approach is incorrect.
The SQL which I run against the Piwik database:
select
visitor_returning,
count(1)
from
piwik_log_visit
where
idsite = 2
and
visit_last_action_time > subtime(now(), '00:30:00')
group by
visitor_returning
order by
visitor_returning;
Thanks for reading this far - I appreciate any help or pointers that people may have to offer.
Regards, Tony