How to decode the ip address and visitorid from piwik in sql:
I wanted to program a little KI which tracks website users through their click process and groups them by their behaviour. Therefore i needed to select the id of the visit, the decoded ip address and the visitation id. After that i could match the visitation id with the other database with all of the clicked links. Then you can do some statistics in your code which website the specific user opens most and all through the click process. With piwik the visitor has a unique id, so you can follow him also after a few days. The time between those clicks can also be calculated with a bit of scripting.
SELECT idvisit, inet_ntoa(conv(hex(location_ip), 16, 10)) as ip, hex(idvisitor) as visitorId FROM piwik_log_visit ORDER BY idvisit DESC LIMIT 3000;
SELECT * FROM piwik_log_link_visit_action WHERE idvisitor = unhex('$visitorid');