Matomo Database Unlocked: Learn to Extract & Analyze Data

Name: Matomo Database Unlocked: Learn to Extract & Analyze Data
Rating: 4.5 (9 reviews)

Master SQL Queries, Optimize Performance & Unlock Powerful Insights from Matomo’s Analytics Data

Created byRonan Chardonneau

Last updated 2/2025

English

English [Auto],

What you'll learn

Understand Matomo’s Database Structure – Learn how Matomo stores data and how different tables are connected.
Write SQL Queries – Extract visitor, event, and conversion data directly from the database.
Create Custom Reports – Go beyond Matomo’s built-in analytics to generate tailored reports.
Automate Data Extraction – Integrate Matomo data with BI tools, dashboards, or machine learning models.

Course content

1 section • 27 lectures • 1h 6m total length

Prerequisite0:15
Video - What is Matomo Analytics?3:49
Explore Matomo analytics and its core dependencies—web server, MySQL/MariaDB, PHP—and learn how to access, manipulate, and report on data using SQL.
What is Matomo Analytics?0:44
Video - What is a relational database?5:03
Learn how relational databases reduce data duplication by storing values once and connecting tables with IDs, enabling joins to retrieve linked information efficiently.
What is a relational database?0:44
Video - What are MariaDB and MySQL?3:43
Explore MySQL and MariaDB, two closely related DBMSs used to build Matomo, and understand why they are favored for on-premise, simple setups for small projects.
What are MariaDB and MySQL?1:17
Securing your Matomo's database0:17
Video - Why should you use Matomo's dabatabase rather than using Matomo UI?6:38
Use the Matomo database for advanced, customizable data exports with custom SQL queries, offering performance and data warehouse ready outputs beyond API limits.
Why should you use Matomo's dabatabase rather than using Matomo UI?2:00
Video - What does the Matomo's database look like?2:58
Explore the Matomo database schema, focusing on the log_visit table, how tables link to generate reports, and how on-prem installations reveal table structures and dimension definitions.
What does the Matomo's database look like?0:02
Matomo on-premise: testing your reports within phpMyAdmin2:52
Explore phpMyAdmin’s visual interface to view Matomo logs, inspect tables and fields, and generate a page URL and page view report using a SQL query with date and site ID.
Video - Debugging Matomo SQL queries3:11
Debug and test Matomo SQL queries in a safe environment using a database copy, and leverage ChatGPT and phpMyAdmin to craft and validate queries for visits and unique visitors.
Debugging Matomo SQL queries0:08
Video - How to export the data from phpMyAdmin0:42
Export data from phpMyAdmin by running an SQL query for 2025 and site 1, then choose CSV and export to obtain the report.
Matomo Cloud BigQuery & Database export0:05
Video - Matomo Cloud - Exporting data from AWS3:26
Access Mattermost Cloud's BigQuery and Data Warehouse to export a database copy. Configure AWS CLI, generate credentials, and use endpoints to download or backfill data.
Going further in using the Matomo's database0:23
Video - Using the MySQL database connector of Microsoft Power BI4:03
Connect Power BI to a MySQL server using the MySQL connector, install the Oracle component, and run SQL queries to import data and create a page URL hits report.
Using the MySQL database connector of Microsoft Power BI0:18
SQL with AI2:46
Discover how AI interacts with MySQL databases to generate SQL queries and reports for the Matomo database via a chatbot, using a connection string and available documentation.
Video - Matomo's database using CLI1:41
Access the Matomo database via a terminal using the CLI to connect to the MySQL server. Run SQL queries against matomo_db to quickly extract results.
Matomo's database using CLI0:13
Video - How to create a custom report with several dimensions5:20
Learn to build a multi-dimensional custom report in Matomo using MySQL, including four dimensions like the referral name, operating system, and browser, with SQL validation via ChatGPT and documentation.
Video - Matomo.cloud only how to send data to BigQuery9:02
Export Matomo data to BigQuery via the backfill export, create a project and dataset, enable the data transfer API, and load log_visit data from S3 for analysis.
Performing reverse engineering with AI in order to get a custom metric4:40
Discover how to derive the average time on page as a SQL metric by AI-assisted reverse engineering, using ChatGPT to infer the underlying query from limited data.
Let's see if you understood the main concepts of Matomo's database

Requirements

Matomo's knowledge, SQL very basics, to know what you are doing

Description

Sure! Here’s a compelling course description for "Matomo Database Unlocked: Learn to Extract & Analyze Data":

Course Description

Unlock the full potential of Matomo Analytics by diving deep into its database structure, SQL queries, and data extraction techniques. This course is designed for developers, analysts, and data enthusiasts who want to go beyond the Matomo UI and gain direct access to raw analytics data for deeper insights, custom reporting, and performance optimization.

What You’ll Learn:

Understand Matomo’s database schema and how data is stored
Write efficient SQL queries to extract visitor, event, and conversion data
Optimize database performance for faster data retrieval
Create custom reports that go beyond Matomo’s built-in analytics
Automate data extraction for BI tools, dashboards, and machine learning models
Debug tracking issues and ensure data accuracy

Why Take This Course?

Matomo’s UI is great for basic reporting, but direct database access unlocks limitless possibilities. Whether you want to speed up analytics, build advanced reports, or integrate with external tools, this course will equip you with the skills to master Matomo’s database like a pro.

No prior database experience? No worries! We’ll guide you through SQL basics before diving into Matomo-specific queries.

Join now and start leveraging Matomo’s database for smarter analytics and deeper insights!

Who this course is for:

Web analysts, data scientists