I am trying to build a query that gets the page_views. I have constructed this query so far:
SELECT
p_log_link_visit_action.*, p_log_action.*
FROM p_log_link_visit_action
INNER JOIN p_log_visit ON p_log_visit.idvisit = p_log_link_visit_action.idvisit
INNER JOIN p_site ON p_site.idsite = p_log_link_visit_action.idsite
INNER JOIN p_log_action ON p_log_action.idaction = p_log_link_visit_action.idaction_url AND p_log_action.type = 1
WHERE date(CONVERT_TZ(p_log_link_visit_action.server_time, 'UTC', p_site.timezone)) = '2020-06-04' AND p_site.idsite=78;
The values match most of the time however on specific days it doesn’t match. I would appreciate some pointers as to why this could be, the values are always lower than what the UI reports which indicates like I am excluding something. To only filteration i have is the action type = 1 … is this wrong? Are other action types considered as page views too ?
I would really appreciate your help here. Thanks