Piwik tables and fields in database


#1

When I create a Piwik report, one of the options to select is the page name in addition to the page URL. This is great and very useful. Now, I am creating a report using SQL queries to the database, and there is no name field, only a URL field. I looked at every table in the database, and found only the URL, which is the NAME" field in the piwik_log_action table. Do you know how to find the actual page name - not the URL? I appreciate any thoughts on this. Thank you! Dianna


(Matthieu Aubry) #2

Maybe this page can help you? Database schema: API Reference - Matomo Analytics (formerly Piwik Analytics) - Developer Docs - v3


#3

Yes, thank you! Piwik is great and so is the help in the Forum. Many thanks.


#4

What I have learned is that the page title is NOT stored in the database. Using HTTP:GET the title is grabbed on the fly. Is there some way I could store the page title in a custom variable?


(Matthieu Aubry) #5

The page title is stored in the database.


#6

Matt, I talked with a senior technician at Arvixe (our host) because I could not find the page title in any of the tables in the database. He confirmed that it is not stored in the database, but is picked up on the fly using HTTP GET.
Please share your different information and help me to locate this in the database. Many thanks!


(Matthieu Aubry) #7

it’s documented in: Database schema: API Reference - Matomo Analytics (formerly Piwik Analytics) - Developer Docs - v3

if this is hard to understand maybe you have specific question?


#8

Hi Matt. Thank you for staying with me on this. I did read the documentation.
Specifically, I refer to the name field in the log_action table. When I use SQL to create report, the name field has a URL instead of the page name from the meta tag .


#9

Matt, I am using the NAME field in the log_action table. When I use SQL to create report, the NAME field contains a URL instead of the actual meta tag .
I don’t understand why. I appreciate your help. Thank you, Dianna


#10

hi dianna,

i googled a similar question and landed in this thread.
after a bit of thinking here is the answer (if its not too late)

title AND url are both stored in table:log_action, column:name.
the column:type defines if the name is a
url = 1 (Piwik\Tracker\Action::TYPE_PAGE_URL)
or a
page title = 4 (Piwik\Tracker\Action::TYPE_PAGE_TITLE)

probably more important than this is:
actual measurements are stored in the table:log_link_visit_action, there you’ll find
column:idaction_url for links to log_action type "url"
and
column:idaction_name for links to log_action type “page title”

p.s.
if you are using piwik tracker instead of log file import, you can alter either url or page title before they are logged to the database using

_paq.push([‘setCustomUrl’, ‘#url#’]);
_paq.push([‘setDocumentTitle’, ‘#title#’]);
_paq.push([‘trackPageView’]);


#11

Yes! This is very helpful. Many thanks.