Database queries for site search usage

I am running Piwik 1.11 for a fairly large site, and we recently added site search to our site, and are tracking it in Piwik. The site search report is showing up in Piwik as one would have expected, so all is working well.

However, in using Piwik, I have become quite proficient in running SQL queries directly against the Piwik database to get answers to questions not readily available within the Piwik web interface (e.g. what proportion of users visiting page A subsequently visited page B, etc). I would like to do the same for search, but I cannot figure out how search information is captured in the database.

Could someone help me along? I am assuming that this must be done using the piwik_log_action and piwik_log_link_visit_action tables somehow, but I cannot figure out how it is working or where the data is stored. Given that the reports are showing up in the web interface, I know the data is stored somewhere, I just cannot find where. I have searched all documentation, tried to find the information in the Piwik code, searched the forums, but I am stumped.

Very grateful for clues.

Hi Staffan

search keywords are stored as Actions in piwik_log_action
They have a type = 8

Searches are stored as Page views in log_link_visit_actions

Search categories are stored as custom variable on this “page view” row.

Hope it helps! we will update our “Piwik core” documentation in the next 4 months as we plan to document better the Piwik Platform.

Hi Matt!

Thanks for the response! This does help. I thought the log_action records with type 8 looked suspiciously like search keywords…

Could you clarify one thing? How do the log_link_visit_action records link to the log_action records for the search keywords? As far as I can tell, there are four fields in log_link_visit_action pointing to log_action: idaction_url, idaction_url_ref, idaction_name, inaction_name_ref.

Running a query like:

SELECT v.idvisit
, inet_ntoa(conv(hex(location_ip), 16, 10)) AS ip_address
, server_time
FROM piwik_log_link_visit_action l
JOIN piwik_log_action ua ON( ua.idaction = l.idaction_url )
JOIN piwik_log_visit v ON( v.idvisit = l.idvisit )
JOIN piwik_log_action ka ON( ka.idaction = l.idaction_name_ref AND ka.type = 8 )
WHERE v.idsite = 3
AND server_time BETWEEN ‘2013-04-01’ AND '2013-05-01’
ORDER BY v.idvisit, server_time;

I only get results for action type 8 when that join is done on the idaction_name_ref field in the log_link_visit_action table (linking on idaction_name yields no results). Is that right? The log_action record associated to these log_link_visit_action records using the idaction_url field seem to be the URL of the NEXT page the user visited (so I assume “ref” stands for referrer?). Incidentally, the log_action record associated with idaction_url_ref for all of these records is NULL, so there is no clear pointer to the search page.

Looking at the log_link_visit_action records for the search page itself, I do not see any search keywords logged.

So, I just want to confirm that this is how it works, so I’m not looking in the wrong places or have things misconfigured. I should be looking for the search keyword on the page view for the NEXT page viewed after the user searched.

I look forward to seeing the documentation update, and thanks for a great product!

You’re correct, have fun!