Looking for help trying to replicate GA real-time functionality using Piwik


#1

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).

  1. 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]

  1. 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


(Matthieu Aubry) #2

For these metrics I recommend to use the Live API getCounters call: http://piwik.org/docs/analytics-api/reference/#Live