我如何写SQL查询选择访客,页面浏览量,搜索,事件在Matomo数据库
当你想轻松地从你的网站访问者和应用程序用户提取原始数据时,你需要的所有信息都可以从Matomo数据库访问,有时你可能想使用SQL查询它。在这个FAQ中,你会发现一个SQL查询列表,你可以很容易地重复使用。
Matomo数据库模式文档
当使用SQL查询分析数据时,你可能想了解更多关于数据库模式和各种列的含义:阅读Matomo数据库模式参考文档了解更多信息.
下面的SQL查询将一次性读取大量数据,并使用数据库上的大量内存,特别是当Matomo收集大量数据时。
选择所有访问和操作的SQL查询(在特定时间段内)
运行这个标准SQL查询,从Matomo中提取所有原始数据和(所有行和所有列)所有访问及其在所有网站上的所有交互。在这个例子中,我们使用列从5月的前14天提取所有值visit_last_action_time
:
SELECT * 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 >= '2022-05-01' AND visit_last_action_time < '2022-05-15';
SQL查询,以选择特定网站ID的所有访问和操作
Matomo表中的所有数据都通过使用列对每个网站进行分离idsite
(网站ID):
SELECT * 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。idsite = X;
SQL查询以选择所有站点搜索的列表
选择操作类型为的所有访问操作8
,表明网站搜索:
选择matomo_log_visit。Idvisit, server_time, matomo_log_action.name作为搜索,matomo_log_link_visit_action。custom_var_v5 as searchresults FROM matomo_log_visit LEFT JOIN matomo_log_link_visit_action ON(matomo_log_visit)Idvisit = matomo_log_link_visit_action。idvisit)左JOIN matomo_log_action ON(matomo_log_action)Idaction = matomo_log_link_visit_action。idaction_name) WHERE type = 8 ORDER BY idvisit, server_time;
SQL查询,以选择Event操作包含特定字符串的所有操作
运行以下SQL查询以全部选中事件,其中“事件动作”匹配某个字符串(在本例中my-event-action-to-delete
):
SELECT * FROM matomo_log_link_visit_action llva JOIN matomo_log_action as la WHERE llva。Idaction_event_action = la。idaction和la.name LIKE '%search%';
选择所有事件名称的SQL查询,其中事件操作包含“搜索”
选择llva。*, la_names。*FROM matomo_log_link_visit_action llva JOIN matomo_log_action as la JOIN matomo_log_action as la_names WHERE llva.idaction_event_action = la.idaction AND llva.idaction_name = la_names.idaction AND la.name LIKE '%search%' AND idsite = X; /*You can additionally add two more AND statements to only query a specific timeframe:*/ /*This will only work in MySQL versions lower than MySQL 8*/ AND server_time >= '2021-03-01' AND server_time < '2021-03-29'
SQL查询选择所有页面标题页面视图(每行是一个页面标题的唯一页面视图)
SELECT * 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_name WHERE matomo_log_visit. WHEREidsite= X AND type = 4 AND matomo_log_link_visit_action.server_time >= '2021-06-01' AND matomo_log_link_visit_action.server_time < '2021-06-30';
SQL查询计算每个页面标题的总浏览量:
SELECT name作为page_title, COUNT(*)作为hits 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_name WHERE matomo_log_visit. WHEREidsite= X AND type = 4 AND matomo_log_link_visit_action.server_time >= '2021-06-01' AND matomo_log_link_visit_action.server_time < '2021-06-30' GROUP BY page_title ORDER BY hits DESC;
SQL查询选择所有页面URL页面视图(每一行都是一个页面URL的唯一页面视图)
SELECT * 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 WHERE matomo_log_visit。idsite= X AND type = 1 AND matomo_log_link_visit_action.server_time >= '2021-06-01' AND matomo_log_link_visit_action.server_time < '2021-06-30';
SQL查询来计算每个页面URL的总浏览量:
选择名称作为页面url, COUNT(*)作为点击从matomo_log_visit左JOIN matomo_log_link_visit_action对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 WHERE matomo_log_visit。idsite= X AND type = 1 AND matomo_log_link_visit_action.server_time >= '2021-06-01' AND matomo_log_link_visit_action.server_time < '2021-06-30' GROUP BY page_url ORDER BY hits DESC;
SQL查询计数和选择从特定页面url的所有outlink:
SELECT lan.name, COUNT(*) FROM (SELECT lva. name)idpageview AS idpageview, lva。idvisit AS idvisit FROM matomo_log_link_visit_action lva LEFT JOIN matomo_log_action ON matomo_log_action。Idaction = lva。idaction_url WHERE idsite = 1 AND server_time >= '2022-01-01 00:00:00' AND server_time < '2022-01-02 00:00:00' ANDtype = 1 AND matomo_log_action.name LIKE '%example.org/homepage') AS pages LEFT JOIN matomo_log_link_visit_action lvn ON lvn。Idpageview =页面。idpageview AND lvn。Idvisit =页面。idvisit LEFT JOIN matomo_log_action lan ON lan。Idaction = lvn。idaction_url WHERE idsite = 1 AND server_time >= '2022-01-01 00:00:00' AND server_time < '2022-01-02 00:00:00' AND lan。type = 2 GROUP BY lan.name BY COUNT(*) DESC;
为了让查询正确工作,你需要对查询进行一些调整:
—查询中有两个位置的“ID Site”需要设置为正确的“ID Site”
-您需要设置日期/时间之间的查询将在2个位置查找数据
-在上面的例子中,您需要设置您想要检查输出链接的URLexample.org/homepage
使用
您可以将此替换为您的网站的URL,不带URL前缀。例如,https://example.org/homepage
应设置为“% example.org/homepage”
你也可以通过在URL的末尾添加一个额外的%来获得网站特定部分的所有外链接。例如:“% example.org/homepage%”
将向您显示以下示例页面的所有外链接:https://example.org/homepage
https://example.org/homepage/section
https://example.org/homepage/abc
如果使用的前缀不是matomo_
对于您的数据库,您需要在查询中更新它。
SQL查询计数和选择所有outlink从特定网站搜索
SELECT lan.name, COUNT(*) FROM (SELECT lva. name)idpageview AS idpageview, lva。idvisit AS idvisit FROM matomo_log_link_visit_action lva LEFT JOIN matomo_log_action ON matomo_log_action。Idaction = lva。idaction_name WHERE idsite = 1 AND server_time >= '2022-01-01 00:00:00' AND server_time < '2022-01-02 00:00:00' AND matomo_log_action。type = 8 AND matomo_log_action.name LIKE 'search') AS pages LEFT JOIN matomo_log_link_visit_action lvn ON lvn。Idpageview =页面。idpageview AND lvn。Idvisit =页面。idvisit LEFT JOIN matomo_log_action lan ON lan。Idaction = lvn。idaction_url WHERE idsite = 1 AND server_time >= '2022-01-01 00:00:00' AND server_time < '2022-01-02 00:00:00' AND lan。type = 2 GROUP BY lan.name BY COUNT(*) DESC;
你将需要对这个查询进行调整,以适用于你的数据库:
—查询中有两个位置的“ID Site”需要设置为正确的“ID Site”
-您需要设置日期/时间之间的查询将在2个位置查找数据
-您需要设置您希望搜索的搜索字符串,“搜索”
在上面的示例中使用,需要用您想获取数据的搜索词替换
更多资源和SQL技巧
另见以下连结:
- SQL查询从Matomo DB获得媒体(视频和音频)的印象和播放总数
- 在SQL中选择IP地址,或在SQL中选择访问者id.
- 选择IP地址范围内的用户.
- 删除使用SQL查询的访问.
- Matomo数据库模式参考指南.
通过HTTPS API(可选)导出RAW访问者和操作数据
您也可以选择使用我们的HTTPs API导出所有这些原始数据,因此您不必使用SQL查询。用于导出所有原始数据的API称为Live。getLastVisitsDetails,让你导出所有的用户和点击流数据为给定的网站和给定的日期:学习更多的知识。