I’m interested in collecting our piwik metrics to build out an array of actions/events for use in Neo4J that would allow us to visualize users trends in a graph model. Essentially I need to build out a linked list of events by visit as follows:
EventA, EventB
EventB, EventC
EventC, EventD
EventD, EvenA
I’ve decomposed the underlying MySQL databases and can build a series based of this SQL, but I’d prefer to issue this via API call.
SELECT s.idsite
, s.name
, l.server_time
, t.type, a.type, c.type
, COALESCE(c.type, a.type, t.type) AS type
– , SUBSTRING_INDEX(a.name, ‘?’, 1) AS url
, t.name AS pageTitle
, a.name actionTitle
, c.name AS eventCategory
– , count(1)
FROM piwik_site s
JOIN piwik_log_link_visit_action l on l.idsite = s.idsite
LEFT JOIN piwik_log_action t on t.idaction = l.idaction_name
LEFT JOIN piwik_log_action a on a.idaction = l.idaction_url
LEFT JOIN piwik_log_action c on c.idaction = l.idaction_event_category
Is there a method that would return a visting and the series of events? I was looking at something similar to this:
Appreciate any help you can provide.