Побудова ETL pipeline з Matomo Cloud в BigQuery для подальшої аналітики в Looker/Power BI

«Немає нічого веселіше, ніж вигружати 100 тисяч рядків логів за добу через API Matomo Cloud».

В якийсь момент, коли GA4 під кукі-банером перестає давати достатньо даних для аналізу, а клієнту/бізнесу/тобі треба якісний збір даних з точністю до кожного кліку — Matomo здається гарним варіантом. Але якщо говорити про аналітику серйозно, особливо з подальшою обробкою, сегментацією, інтерпритацією даних… Представлення оброблених даних в Power BI або Looker, чи іншому BI, то базових дашбордів у Matomo буде замало. Доведеться лізти в сирі логи. А вони — або в API, або взагалі ніде. Сценарій такий:

• Матомо стоїть у хмарі (Matomo Cloud, не On-Premise).
• Потрібно витягнути сирі логи (log_visit, log_link_visit_action, log_action, log_conversion тощо).
• Далі — запхати це все в BigQuery.
• Побудувати нормальні моделі даних.
• І підключити до Power BI або Looker, або ще десь.

Розповідаю, як це зробив. І де було боляче.

Що дає Matomo Cloud по API

Matomo Cloud не дає прямого доступу до бази. Є тільки API. А нам треба зберігати дані десь у власному сховищі. Можна зробити так, якщо не хочеш доплачувати за платну функцію експорту:

Варіанти:

• Live.getLastVisitsDetails — повертає сирі сесії (до ~1000 за раз).
• Actions.getPageUrls — агрегація по сторінках.
• Goals.getConversions — всі конверсії.

Є нюанс: якщо ти хочеш ріал-тайм логи, треба юзати Live.getLastVisitsDetails, і пагінацію через &filter_limit + &filter_offset.

Приклад запиту:

GET https://{your-subdomain}.matomo.cloud/index.php
?module=API
&method=Live.getLastVisitsDetails
&idSite=1
&period=day
&date=2025-04-30
&format=JSON
&filter_limit=100
&filter_offset=0
&token_auth={your_token}

Обмеження:

• максимум 100 візитів за раз.
• throttle rate — якщо ти задовбав API, то сервер тимчасово банить твій IP або обмежить відповіді. Тобто проксі не допоможуть.

Архітектура ETL по API

Matomo API → Cloud Function (Python) → GCS → BigQuery → Power BI / Looker

• Cloud Function — щоденний крон, який тягне логи по API, обробляє і кладе файли в GCS.
• GCS (Google Cloud Storage) — тимчасове сховище для JSON/CSV.
• BigQuery — основне джерело правди.
• Power BI / Looker — аналітика вже звідси.

Але краще цим не страждати. Бо факт-чекінг даних буде постійний. І повірте, похибки в експорті будуть.
Але не біда. Matomo Cloud викатив ще в 2024 році можливість експорту даних з їх хмари в вашу хмару (BigQuery).

Скрипт на Python для вигрузки

Основна логіка:

• проходимось по кожному дню (&date=YYYY-MM-DD)
• робимо пагінацію поки не отримаємо менше 100 записів
• парсимо JSON, зберігаємо у файл (CSV або JSON Lines)
• пушимо у GCS

import requests
import json
from google.cloud import storage

SITE_ID = 1
TOKEN = 'your_token_auth'
BASE_URL = 'https://your-subdomain.matomo.cloud'
BUCKET_NAME = 'your-bucket'
PREFIX = 'matomo_raw/'

def fetch_visits(date):
    results = []
    offset = 0
    while True:
        resp = requests.get(f"{BASE_URL}/index.php", params={
            'module': 'API',
            'method': 'Live.getLastVisitsDetails',
            'idSite': SITE_ID,
            'period': 'day',
            'date': date,
            'format': 'JSON',
            'filter_limit': 100,
            'filter_offset': offset,
            'token_auth': TOKEN
        })
        data = resp.json()
        if not data:
            break
        results.extend(data)
        if len(data) < 100:
            break
        offset += 100
    return results

def upload_to_gcs(data, date_str):
    client = storage.Client()
    bucket = client.get_bucket(BUCKET_NAME)
    blob = bucket.blob(f"{PREFIX}{date_str}.json")
    blob.upload_from_string(json.dumps(data), content_type='application/json')

# main
from datetime import date, timedelta

day = (date.today() - timedelta(days=1)).isoformat()
data = fetch_visits(day)
upload_to_gcs(data, day)

Імпорт в BigQuery

В GCS лежить JSON — можна налаштувати автоматичний імпорт у таблицю BigQuery або зробити пайплайн через Cloud Composer / Dataform.

⚠️ Важливо: структура у Matomo API максимально кривенька і глибока. Багато customDimensions, actionDetails і вкладених списків.

Що робити:

• або розплющувати (flatten) в ETL скрипті перед імпортом,
• або завантажувати як JSON (тип RECORD), і вже в SQL писати UNNEST() для кожного поля.

Побудова моделі даних

В BigQuery я створюємо такі таблиці:

• matomo_visits — з основною інформацією по візитах
• matomo_actions — UNNEST(actionDetails) із кожного візиту
• matomo_custom_dimensions — якщо використовуються customDimensions

Ключові поля для зв’язку — idVisit і idAction.

Що не працювало з першого разу

• API throttle — довелося вставити sleep() між запитами.
• Поля типу “array of arrays” в JSON — погано імпортуються. Треба flatten вручну.
• Деякі поля мають null або змінний тип — наприклад, customVariables іноді рядок, іноді об’єкт.
• Датафрейм важко дебажити в Power BI, якщо він raw. Краще одразу робити view з нормальними назвами полів і типами.

(платно) Альтернатива: офіційний експорт Matomo Cloud → BigQuery

Якщо коротко: Matomo Cloud підтримує експорти у BigQuery офіційно, але треба писати їм в саппорт.

У цьому FAQ Matomo відкрито пише, що може автоматично пушити ваші дані в BigQuery. Хоча мені здається там можна все підключити і до Redshift-а чи Snowflake.

Це платна фіча, але:

• для великих проектів — must-have;
• не треба писати свої парсери і костилі;
• приходить структурований, нормалізований дамп з усіма стандартними таблицями (log_visit, log_link_visit_action, log_action, log_conversion, etc.).
• ціна смішна. Я не пишу її тут, бо можливо вона опціональна. Але якщо є гроші на cloud – то на офіційний експорт точно гроші будуть. Це дешевше ніж платити за костилі.

Як увімкнути

1. Пишемо на support@matomo.cloud:
   “Hi, I’d like to activate Data Warehouse export for BigQuery…”
2. Далі чекаємо відповіді, вони попередять про прайс. До речі в них після оплата, тому платити одразу не треба.
3. Після погодженя умов, в адмінці з’явиться новий пункт меню, де можна буде налаштувати експорт.

Що ти отримуєш

• Всі основні таблиці: log_visit, log_action, log_link_visit_action, log_conversion, goal, etc.
• Поля вже структуровані і з нормальними типами
• Схеми не треба вигадувати самому
• Оновлення щоденні (за вчора) і нема проблем, як буває при ексопрті GA4 -> BigQuery коли не експортується один день і треба ручками перезавантажувати.

Порівняння: API vs Data Warehouse Export

Що обрати?

Тут усе просто:

• Малий проєкт / бюджет 0 / треба тут і зараз — робимо ETL сам через API (приклад вище).
• Середній / великий проєкт, де важлива стабільність, час і не хочеш гратися з JSONами — пишимо в саппорт і юзаємо офіційний data warehouse експорт.

Підсумок

Matomo Cloud може віддавати всі сирі логи — і через API, і офіційно в BigQuery. Якщо часу більше, ніж грошей — береш API. Якщо грошей більше, ніж терпіння — береш офіційний data warehouse export. За 9 місяців був лише один збій, по вині Matomo. Треба було перепідключати планувальник експорту з під оновлену адресу їх сервера

Ну і бонусом модель під PowerBI

• Підключення через офіційний Google BigQuery connector.
• Рекомендую імпорт (Import mode), а не DirectQuery, бо інакше — гальма і навантаження на BI-cервер буде величезним.
• Моделюємо зв’язки між таблицями через idVisit і idAction. Таблиця дат – для зручності.

Це базова модель, але її вичтачить для більшості аналітичних рішень.