How to retrieve Funnels data for tables excluded from the Data Warehouse Connector export

The Matomo database tables used by the Funnels feature, namely the log_funnel and funnel_steps, are not included in the export via the Matomo Cloud Data Warehouse Connector. Only the main funnel table is exported.

The tables log_funnel and funnel_steps are not part of the export schema and are used for processed data that Matomo derives from these core tracking tables: log_conversion, log_visit, log_link_visit_action and funnel.

Query example for retrieving funnel data

You can use the following query to retrieve the equivalent processed funnel data from these Matomo tables, provided they are included in your export.

1. The Matomo export using the Data Warehouse Connector must include the log_conversion, log_visit, log_link_visit_action and funnel tables.
2. After loading the data into your data warehouse (for example, BigQuery or Snowflake, adapt and run this query against the exported tables. The example uses MySQL-style logic, so adjust the syntax as needed.

$sql = "SELECT lc.idvisit, 
               lc.idlink_va, 
               lc.idaction_url as idaction, 
               (SELECT lvaprev.idaction_url_ref 
                  FROM $visitActionTable lvaprev 
                 WHERE lc.idlink_va = lvaprev.idlink_va 
                 LIMIT 1) AS idaction_prev,
               NULL AS idaction_next
          FROM $visitTable lv USE INDEX (index_idsite_datetime)
          LEFT JOIN $conversionTable lc ON lv.idvisit = lc.idvisit 
         WHERE $visitorHadEnteredFunnelQuery
               lv.idsite = ? 
           AND lv.visit_last_action_time >= ? 
           AND lv.visit_last_action_time <= ?
           AND lc.idgoal = ?
           AND NOT EXISTS (
                 SELECT 1 
                   FROM $funnelTable lf 
                  WHERE lf.idfunnel = ? 
                    AND lf.idvisit = lv.idvisit 
                    AND lf.step_position = ?
               )
      GROUP BY idvisit";

This workaround lets you reproduce processed funnel data that is not included in the Data Warehouse Connector export. The exported funnel table combined with the other tracking tables provides the data to rebuild funnel performance reports within your data warehouse.

You can also use the Funnels reporting API available in Matomo to extend your funnel analysis and reporting.