Matomoデータベースの訪問者、ページビューのリスト、検索、イベントを選択するSQLクエリの書き方

2025年01月21日 API Rawデータエクスポート

ウェブサイトの訪問者やアプリのユーザーから RAW データを簡単に抽出したい場合、必要な情報はすべて Matomo データベースからアクセス可能です。この FAQ には、簡単に再利用できる SQL クエリのリストがあります。

Matomo データベーススキーマドキュメント

SQLを使用して分析データを照会する場合、データベーススキーマやさまざまなカラムの意味について詳しく学びたいと思うかもしれません：詳しくはMatomoデータベーススキーマのリファレンスドキュメントをお読みください。.

特にMatomoが多くのデータを収集している場合、以下のSQLクエリは一度に多くのデータを読み込み、データベースのメモリを大量に消費します。

このページに関する重要な注意事項Matomo はすべての日付/時刻を UTC としてデータベースに保存します。あなたのウェブサイトが UTC 以外のタイムゾーンを使用している場合は、クエリ内のすべての日付をあなたのウェブサイトの UTC オフセットで調整する必要があります。

すべての訪問と行動（特定の期間中）を選択するSQLクエリ

この標準SQLクエリを実行すると、Matomoからすべてのウェブサイトのすべての訪問とすべてのインタラクションの生データと（すべての行とすべての列）が抽出されます。この例では、visit_last_action_timeカラムを使用して、5月の最初の14日間のすべての値を抽出します：
この例では、visit_last_action_timeカラムを利用して5月の最初の14日間のすべての値を抽出します：

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';

特定のウェブサイトIDのすべての訪問とアクションを選択するSQLクエリ

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.idvisit
    WHERE idsite = X;

特定の期間とウェブサイトのユニークビジター数（ビジターID）をカウントするSQLクエリ

    SELECT COUNT(DISTINCT(idvisitor))
    AS unique_visitor_ids
    FROM matomo_log_visit
    WHERE idsite = X
    AND visit_first_action_time >= 'YYYY-MM-DD hh:mm:ss'
    AND visit_first_action_time <= 'YYYY-MM-DD hh:mm:ss';

注：Matomoデータベースに保存される値はすべてUTCで保存されるため、ウェブサイトのタイムゾーンを考慮してクエリを調整する必要があります。

指定されたIDサイトのユニークページビュー数（ページURLに基づく）をカウントするSQLクエリ。

この場合、IDサイトと日付範囲を更新するだけです。

    SELECT COUNT(DISTINCT(matomo_log_visit.idvisit)) as UNIQUE_PAGEVIEWS
    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 = 1
    AND type = 1
    AND matomo_log_link_visit_action.server_time >= '2023-08-01'
    AND matomo_log_link_visit_action.server_time < '2023-08-31';

このクエリは、特定のURL、または必要な文字列を含む任意のURLのユニークページビューの合計数を返します：

    SELECT COUNT(DISTINCT(matomo_log_visit.idvisit)) as UNIQUE_PAGEVIEWS
    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 = 1
    AND type = 1
    AND matomo_log_action.name LIKE '%example.org%'
    AND matomo_log_link_visit_action.server_time >= '2023-08-01'
    AND matomo_log_link_visit_action.server_time < '2023-08-31';

例えば、%example.org%は、指定されたID SiteのURLのうち、URLにそれを含むすべてのURLのユニークページビューの合計数を返します。URLをより具体的にするには、example.org/my-page（プロトコルを含まない）のように変更できます。

すべてのサイト内検索のリストを選択するSQLクエリ

アクションタイプが8で、サイト内検索を示すすべての訪問アクションを選択します：

    SELECT matomo_log_visit.idvisit, 
           server_time, 
           matomo_log_action.name as search, 
           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 ) 
    LEFT 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クエリ

以下のSQLクエリを実行して、”Event action “が特定の文字列（この例ではmy-event-action-to-delete）と一致するすべてのEventを選択します：

    SELECT *
    FROM matomo_log_link_visit_action llva 
    JOIN matomo_log_action as la  
    WHERE llva.idaction_event_action = la.idaction  
    AND la.name LIKE '%search%';

イベントアクションに’search’が含まれるすべてのイベント名を選択するSQLクエリ

    SELECT 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.idsite = 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 as page_title, COUNT(*) as 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.idsite = 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;

すべてのページURLのページビューを選択するSQLクエリ (各行はページ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';

各ページURLの総ページビュー数をカウントするSQLクエリ：

    SELECT name as page_url, COUNT(*) as 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_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;

特定のページのURLからすべてのアウトリンクをカウントして選択するSQLクエリ：

    SELECT lan.name, COUNT(*) FROM (

    SELECT lva.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'
    AND matomo_log_action.type = 1
    AND matomo_log_action.name LIKE
    '%example.org/homepage'

    ) AS pages LEFT JOIN matomo_log_link_visit_action lvn ON lvn.idpageview = pages.idpageview AND lvn.idvisit = pages.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 ORDER BY
    COUNT(*) DESC;

クエリーを正しく動作させるには、いくつかの調整が必要です：
– クエリー内の2つの位置で、IDサイトを正しいIDサイトに設定する必要があります。
– クエリがデータを検索する日付/時刻を次の2つの場所に設定する必要があります。
– 上記の例では、アウトリンクをチェックしたいURLを設定する必要があります。上記の例ではexample.org/homepageが使用されています。

これをURLプレフィックスなしのウェブサイトのURLに置き換えることができます。たとえば、https://example.org/homepageは「%example.org/homepage」に設定する必要があります。

URL の末尾に % を追加することで、Web サイトの特定のセクションのすべてのアウトリンクを取得することもできます。例: ‘%example.org/homepage%’

以下の例のページのすべてのアウトリンクを表示します：
https://example.org/homepage
https://example.org/homepage/section
https://example.org/homepage/abc
以外の接頭辞を使用する場合はmatomo_を使用している場合は、クエリでこれを更新する必要があります。

特定のOutlinksをカウントして選択するSQLクエリサイト内検索

    SELECT lan.name, COUNT(*) FROM (

    SELECT lva.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 = pages.idpageview AND lvn.idvisit = pages.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 ORDER BY
    COUNT(*) DESC;

このクエリがあなたのデータベースで動作するように調整する必要があります：
– クエリー内の2つの位置で、IDサイトを正しいIDサイトに設定する必要があります。
– クエリがデータを検索する日付/時刻を次の2つの場所に設定する必要があります。
– 検索したい文字列を設定する必要があります、'search'は上記の例で使用されており、データを取得したい検索語に置き換える必要があります。

全サイトの Matomo で追跡されたヒット数を ID ごとにカウントする SQL クエリ Site

    SELECT COUNT(*) AS hits, idsite, MIN(server_time) as oldest, MAX(server_time) AS newest      
    FROM matomo_log_link_visit_action
    GROUP BY idsite;

上記のクエリは、Matomoで追跡されているすべてのRAWデータのヒット数の合計を表示します。RAWデータの削除を有効にしている場合は、このカウントがどこまで遡るかを決定します。

クエリを特定の期間に制限して、特定の期間のヒット数を取得することができます：

    SELECT COUNT(*) AS hits,idsite
    FROM matomo_log_link_visit_action
    WHERE server_time > '2022-12-01'
    AND server_time < '2022-12-31'
    GROUP BY idsite;

ユーザーのアラートを削除する

ユーザがチームを離脱しましたか？ユーザのアラートが Matomo のダッシュボードから見えなくなったとしても、データベースからアクセスすることができます。SQL クエリを使用してユーザのアラートを削除します：

    SELECT * FROM matomo_alert
    WHERE login LIKE 'username_here';

その後、そのリストを使用して、設定されたアラートに必要な更新を行ったり、直接削除したりすることができます：

    UPDATE matomo_alert SET email_me = '0' 
    WHERE login LIKE 'username_here';

または、他のユーザーにメールが送信されないようにします：

    UPDATE matomo_alert SET additional_emails = '[]' 
    WHERE login LIKE 'username_here';

また、レポートを削除するには、次のように実行するだけでよいです：

    DELETE FROM matomo_alert 
    WHERE login LIKE 'username_here';

スーパーユーザ権限を持つユーザを特定するためのSQLクエリ

私たちが受け取ったEメールによると、よくある問題は、Matomoをインストールした人々が時々利用できなくなることです。

ダッシュボードへのログインパスワードを持っていた人たちが、Matomoへのログイン方法を教えないまま会社を辞めても、SQLデータベースにアクセスすることで、スーパーユーザーを見つけることができます。

    SELECT login, email, superuser_access 
    FROM matomo_user 
    WHERE superuser_access = 1;

未完了の無効化ジョブの数を測定するSQLクエリ

Matomo の新しいバージョンにアップデートし、データベースが大きくなった場合、無効化の数も大きくなる可能性があります。これはデータベース操作に過負荷をかけ、アーカイブが非常に遅くなることに気づくでしょう。

データベース・サーバーの無効化処理に時間がかかる場合は、SQLキューに無効化処理が殺到していないか確認してください：

    SELECT count(*), name, idsite 
    FROM matomo_archive_invalidations  
    GROUP BY name;


    SELECT count(*), name, idsite, date1, date2 
    FROM matomo_archive_invalidations 
    GROUP BY name;

データベース全体のサイズと個々のテーブルのサイズを測定するSQLクエリ

生データやレポートデータを削除する準備をしている場合、これは便利です。削除前と削除後のデータベース・サイズを確認することができます。(注意：我慢してください。Matomo では、削除はスケジュールされたタスクとして実行されます。core:run-scheduled-tasksを使えば、スケジュールタスクをすぐに実行できます)。

データベースのサイズを調べます：

    SELECT
    table_schema AS "Database",
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in MB"
    FROM
    information_schema.TABLES
    GROUP BY
    table_schema;

テーブルのサイズを調べます：

    SELECT
    table_schema as `Database`,
    table_name AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    FROM
    information_schema.TABLES
    ORDER BY
    (data_length + index_length) ASC;

その他のリソースとSQLのヒント

以下のリンクも参照のこと：

HTTPS API（代替）を介してRAW訪問者とアクションデータをエクスポートする

また、SQLクエリを使用する必要がないように、私たちのHTTPs APIを使用して、このすべてのRAWデータをエクスポートすることもできます。すべてのRAWデータをエクスポートするために使用されるAPIは、Live.getLastVisitsDetailsと呼ばれ、指定されたウェブサイトと指定された日付のすべてのユーザーとクリックストリームのデータをエクスポートすることができます：詳細はこちら。

技術情報

Matomo データベーススキーマ ドキュメント