Matomo és un producte per tal d’analitzar el comportament web de la gent que ens visita.
Per sota matomo té una base de dades MySQL.
Algunes consultes útils:
SELECT max(visit_last_action_time), min(visit_last_action_time)
FROM matomo_log_visit
LEFT JOIN matomo_log_link_visit_action ON matomo_log_visit.idvisit = matomo_log_link_visit_action.idvisit
LEFT JOIN matomo_log_action ON matomo_log_action.idaction = matomo_log_link_visit_action.idaction_url
LEFT JOIN matomo_log_conversion ON matomo_log_visit.idvisit = matomo_log_conversion.idvisit
LEFT JOIN matomo_log_conversion_item ON matomo_log_visit.idvisit = matomo_log_conversion_item.idvisit
-- WHERE visit_last_action_time >= '2021-11-15'
-- AND visit_last_action_time < '2022-05-15';
SELECT v.idvisit, v.idvisitor, v.referer_url
,c.idorder, c.server_time, c.items, c.revenue, c.revenue_shipping, c.revenue_subtotal, c.revenue_tax, c.revenue_discount, c.visitor_returning, c.visitor_count_visits
,ci.idorder, ci.price, ci.quantity, ci.deleted
, acs.name as ref, an.name as product_name, ac.name as product_cat_lvl1, ac2.name as product_cat_lvl2, ac3.name as product_cat_lvl3, ac4.name as product_cat_lvl4, ac5.name as product_cat_lvl5
FROM v_matomo_log_visit v
JOIN matomo_log_conversion c ON v.idvisit = c.idvisit
JOIN matomo_log_conversion_item ci ON v.idvisit = ci.idvisit
left join matomo_log_action acs on acs.idaction =ci.idaction_sku
left join matomo_log_action an on an.idaction =ci.idaction_name
left join matomo_log_action ac on ac.idaction =ci.idaction_category
left join matomo_log_action ac2 on ac2.idaction =ci.idaction_category2
left join matomo_log_action ac3 on ac3.idaction =ci.idaction_category3
left join matomo_log_action ac4 on ac4.idaction =ci.idaction_category4
left join matomo_log_action ac5 on ac5.idaction =ci.idaction_category5
where v.idsite=3 -- and v.location_ip='10.2.0.0' and (v.idvisitor= upper('d838607436e126ae') or v.idvisitor = upper('b5ad8261921208b8'));
SELECT v.idvisit, v.idvisitor, v.referer_url, a.name as current_url, a2.name as page_name, a3.name as previous_url
,va.pageview_position, va.server_time
FROM v_matomo_log_visit v
LEFT JOIN matomo_log_link_visit_action va ON v.idvisit = va.idvisit
LEFT JOIN matomo_log_action a ON a.idaction = va.idaction_url
LEFT JOIN matomo_log_action a2 ON a2.idaction = va.idaction_name_ref
LEFT JOIN matomo_log_action a3 ON a3.idaction = va.idaction_url_ref
where v.idsite=3 and (v.idvisitor= upper('4196c1181e011e43'));
select
`matomo_db`.`matomo_log_visit`.`idvisit` AS `idvisit`,
`matomo_db`.`matomo_log_visit`.`idsite` AS `idsite`,
conv(hex(`matomo_db`.`matomo_log_visit`.`idvisitor`), 16, 16) AS `idvisitor`,
`matomo_db`.`matomo_log_visit`.`visit_last_action_time` AS `visit_last_action_time`,
conv(hex(`matomo_db`.`matomo_log_visit`.`config_id`), 16, 16) AS `config_id`,
inet6_ntoa(`matomo_db`.`matomo_log_visit`.`location_ip`) AS `location_ip`,
`matomo_db`.`matomo_log_visit`.`profilable` AS `profilable`,
`matomo_db`.`matomo_log_visit`.`user_id` AS `user_id`,
`matomo_db`.`matomo_log_visit`.`visit_first_action_time` AS `visit_first_action_time`,
`matomo_db`.`matomo_log_visit`.`visit_goal_buyer` AS `visit_goal_buyer`,
`matomo_db`.`matomo_log_visit`.`visit_goal_converted` AS `visit_goal_converted`,
`matomo_db`.`matomo_log_visit`.`visitor_returning` AS `visitor_returning`,
`matomo_db`.`matomo_log_visit`.`visitor_seconds_since_first` AS `visitor_seconds_since_first`,
`matomo_db`.`matomo_log_visit`.`visitor_seconds_since_order` AS `visitor_seconds_since_order`,
`matomo_db`.`matomo_log_visit`.`visitor_count_visits` AS `visitor_count_visits`,
`matomo_db`.`matomo_log_visit`.`visit_entry_idaction_name` AS `visit_entry_idaction_name`,
`matomo_db`.`matomo_log_visit`.`visit_entry_idaction_url` AS `visit_entry_idaction_url`,
`matomo_db`.`matomo_log_visit`.`visit_exit_idaction_name` AS `visit_exit_idaction_name`,
`matomo_db`.`matomo_log_visit`.`visit_exit_idaction_url` AS `visit_exit_idaction_url`,
`matomo_db`.`matomo_log_visit`.`visit_total_actions` AS `visit_total_actions`,
`matomo_db`.`matomo_log_visit`.`visit_total_interactions` AS `visit_total_interactions`,
`matomo_db`.`matomo_log_visit`.`visit_total_searches` AS `visit_total_searches`,
`matomo_db`.`matomo_log_visit`.`referer_keyword` AS `referer_keyword`,
`matomo_db`.`matomo_log_visit`.`referer_name` AS `referer_name`,
`matomo_db`.`matomo_log_visit`.`referer_type` AS `referer_type`,
`matomo_db`.`matomo_log_visit`.`referer_url` AS `referer_url`,
`matomo_db`.`matomo_log_visit`.`location_browser_lang` AS `location_browser_lang`,
`matomo_db`.`matomo_log_visit`.`config_browser_engine` AS `config_browser_engine`,
`matomo_db`.`matomo_log_visit`.`config_browser_name` AS `config_browser_name`,
`matomo_db`.`matomo_log_visit`.`config_browser_version` AS `config_browser_version`,
`matomo_db`.`matomo_log_visit`.`config_client_type` AS `config_client_type`,
`matomo_db`.`matomo_log_visit`.`config_device_brand` AS `config_device_brand`,
`matomo_db`.`matomo_log_visit`.`config_device_model` AS `config_device_model`,
`matomo_db`.`matomo_log_visit`.`config_device_type` AS `config_device_type`,
`matomo_db`.`matomo_log_visit`.`config_os` AS `config_os`,
`matomo_db`.`matomo_log_visit`.`config_os_version` AS `config_os_version`,
`matomo_db`.`matomo_log_visit`.`visit_total_events` AS `visit_total_events`,
`matomo_db`.`matomo_log_visit`.`visitor_localtime` AS `visitor_localtime`,
`matomo_db`.`matomo_log_visit`.`visitor_seconds_since_last` AS `visitor_seconds_since_last`,
`matomo_db`.`matomo_log_visit`.`config_resolution` AS `config_resolution`,
`matomo_db`.`matomo_log_visit`.`config_cookie` AS `config_cookie`,
`matomo_db`.`matomo_log_visit`.`config_flash` AS `config_flash`,
`matomo_db`.`matomo_log_visit`.`config_java` AS `config_java`,
`matomo_db`.`matomo_log_visit`.`config_pdf` AS `config_pdf`,
`matomo_db`.`matomo_log_visit`.`config_quicktime` AS `config_quicktime`,
`matomo_db`.`matomo_log_visit`.`config_realplayer` AS `config_realplayer`,
`matomo_db`.`matomo_log_visit`.`config_silverlight` AS `config_silverlight`,
`matomo_db`.`matomo_log_visit`.`config_windowsmedia` AS `config_windowsmedia`,
`matomo_db`.`matomo_log_visit`.`visit_total_time` AS `visit_total_time`,
replace(`matomo_db`.`matomo_log_visit`.`location_city`, char(195), '') AS `location_city`,
`matomo_db`.`matomo_log_visit`.`location_country` AS `location_country`,
`matomo_db`.`matomo_log_visit`.`location_latitude` AS `location_latitude`,
`matomo_db`.`matomo_log_visit`.`location_longitude` AS `location_longitude`,
`matomo_db`.`matomo_log_visit`.`location_region` AS `location_region`,
`matomo_db`.`matomo_log_visit`.`last_idlink_va` AS `last_idlink_va`,
`matomo_db`.`matomo_log_visit`.`custom_dimension_1` AS `custom_dimension_1`,
`matomo_db`.`matomo_log_visit`.`custom_dimension_2` AS `custom_dimension_2`,
`matomo_db`.`matomo_log_visit`.`custom_dimension_3` AS `custom_dimension_3`,
`matomo_db`.`matomo_log_visit`.`custom_dimension_4` AS `custom_dimension_4`,
`matomo_db`.`matomo_log_visit`.`custom_dimension_5` AS `custom_dimension_5`
from
`matomo_db`.`matomo_log_visit`;
A través de javascript es poden configurar els permisos de cookies a l’hora de guardar events.
Exemple de codi javascript amb consentiment de visita i trackeig de cookies:
<script>
var _paq = window._paq = window._paq || [];
//_paq.push(['forgetCookieConsentGiven']);
//_paq.push(['forgetConsentGiven']);
_paq.push(['rememberConsentGiven']);
_paq.push(['rememberCookieConsentGiven']);
</script>
Per configurar un event que tingui en compte el trackeig de cookies s’ha de crear una variable a tag manager amb el flag “Reqiure tracking consent” activat.
Després s’ha d’assignar aquesta variable a la configuració del tag de Matomo.
Tot i que les campanyes de Matomo no guarden dades personals i son GDPR compilant, podem voler que l’usuari pugui desactivar-les. No hi ha una forma directa de fer-ho amb matomo, però es pot fer una simulació amb Tag Manager.
Primer hem de crear una variable de tipus Data-Layer. Li direm: ConsentCampaign
Després crearem un trigger de tipus Custom Event que li direm: consent-campaign
I finalment crearem un Event que saltarà quan s’executi el trigger anterior i guardarem la variable ConsentCampaign que hem creat.
A la pàgina web inserirem el següent script
<script>
function permetcampanya(permet) {
_mtm.push({'consentcampaign': permet});
_mtm.push({"event": "consent-campaign"});
return true;
}
</script>
Invocarem a la funció permetcampanya segons les preferències de l’usuari (ex. permetcampanya(0) si no les permet o permetcampanya(1) si sí que les permet.
La funció afegeix el valor de la funció a la variable consentcampaign i genera l’event que fa que es guardi a Matomo.
Un cop tenim aquest valor guardat s’haurà d’executar la següent sentència SQL per esborrar definitivament la campanya de Matomo:
update matomo_log_visit set campaign_keyword=null, campaign_medium=null, campaign_name=null,campaign_source=null
,campaign_content=null,campaign_id=null
where exists (
select *
from matomo_log_link_visit_action va
left join matomo_log_action a on va.idaction_event_action = a.idaction
where va.idvisit = matomo_log_visit.idvisit and va.idsite =matomo_log_visit.idsite
and a.name='givesconsentcampaign'
and va.custom_float=0)
and matomo_log_visit.campaign_id is not null
and matomo_log_visit.idsite =<idSite>;
On givesconsentcampaign és l’event action que hem configurat al Tag.