Goals per page - new reports and metrics for tracking page conversions (#18221)

* Goals per page - added new reports and metrics for tracking page conversions

* Added top entry page statistic, added entry titles related report, adjusted percent formatting

* Added datatable filter to remove goal revenue columns if there are no values, refactored goal visualisation classes to minimise duplicate methods

* Fixed conversion query matching issue

* Added system tests for page goals reports, rework of metrics and SQL to match expected calculations

* Added UI tests

* Remove unnecessary code, merge system tests

* Fix for archiving query error on MySQL

* Fix for query to work with MySQL 5.7

* Update core/API/DocumentationGenerator.php

Co-authored-by: Stefan Giehl <stefan@matomo.org>

* Revert unnecessary DocumentationGenerator change

* Fix incorrect row limit config setting names

* Remove special handling of pages reports

* Update plugins/Goals/Visualizations/GoalsPages.php

Co-authored-by: Stefan Giehl <stefan@matomo.org>

* Update plugins/Goals/Visualizations/GoalsEntryPages.php

Co-authored-by: Stefan Giehl <stefan@matomo.org>

* Tidy up visualization classes

* Fix for sorting

* Remove unnecessary columns from reports, fix tests

* Add new visualizations to Javascript checks

* Add BasePages parent report class to deduplicate new reports

* Reworked to add per-goal metric columns to the Actions_action and Action_action_url archives instead of generating separate page goal archives

* Added a filter to remove goal columns from Actions datatables by default and an optional parameter to include goals columns

* Improved remove goals actions filter to recurse subtables, get goals list for site

* Test fixes

* Move page goal metrics to nested column structure on actions and actions_url archives

* Fixes and updates for apiGetReportMetadata tests

* Test fixes

* Test fixes and updates

* More test updates

* Bug fix for entry page incorrectly aggregating data from different goals

* Backwards compatibility test fixes

* Test fix

* Update submodule

* Updated tests

* Updated tests

* Updated test

* built vue files

* Force sorting of scheduled reports list by unique id to avoid sorting inconsistencies between PHP7 and PHP8 tests

* Updated tests after sorting scheduled reports list

* Updated tests

* Test fixes

* Skip specific tests that pass with PHP7 but fail with PHP8

* Test updates

* Test updates

* UI test screenshot updates

* Slightly increased reasonable release total filesize test from 55mb to 56mb

* Test fix

* Disable test for PHP8

* Update submodule

* Update submodule

* Test updates

* Revert test changes

* Predictably sort scheduled reports

* Disable failing test for PHP 8

* Updated tests after conflict fix

* Update tests after conflict fix

* Update submodule

* Revert unnecessary change

* Rework to use a single goals visualisation and show goal metrics directly on the action page reports

* Update UI tests

* Update tests, fix for goals menu ordering issue

* Update tests

* Revert goal overview menu item ordering

* Do not add goal metadata to actions reports when the includeGoals API parameter is set to false

* Updated unit and UI tests

* Update UI tests

* Update submodule

* Update submodules

* Code improvements, handle ecommerce metrics for page goals, remove obsolete test expected xml files

* Remove unnecessary row properties after use

* fix phpcs

* Improve & refactor code

* Update system test

* Tweaked release reasonable size test from 55mb to 58mb to prevent test failure

* Update submodule

* Update system test

* Update UI test screenshots

* Update UI test screenshots

* Update submodule

* Update UI test screenshot

* update submodule

* test improvements

* updates expected UI files

* fix removing unused revenue columns from UI

* applies some psr12 code formatting

* Minor query optimisations

* Rework the conversions by pageview query to remove subquery and all grouping, add aggregation in code

* Test fixes, null checks for revenue metrics

* Expanded goal page tests to cover multiple goals converted in a single visit

* Updated UI test screenshots

* Expanded test to be multi-day, multi-goal, multiple conversion per visit. Fixed summary logic for multi-goal visits. Fixed calculation of viewed before page rate to get conversion total via API call

* Update system and UI screenshot tests

* Added method return type hint, ensure request parameters are blank on Goals.get filter API call

* Retrieve conversion totals from numeric archives directly instead of via API call

* Move goal conversion totals lookup from the CalculateConversionPageRate filter to the Actions API and then pass to the filter

* Tidy up unused namespaces

* Revert move of goal conversion total retrieval from filter to actions api. Included segment in archive build for goal conversions totals.

* fixes: date might be manipulated too often

* updates expected UI files

* avoid building archive too often

* updates expected test files

* fix ui tests

Co-authored-by: sgiehl <stefan@matomo.org>
Co-authored-by: bx80 <bx80@users.noreply.github.com>

1 files changed, 116 insertions, 0 deletions

diff --git a/core/DataAccess/LogAggregator.php b/core/DataAccess/LogAggregator.php
index cada4b1571..61479f39d0 100644
--- a/core/DataAccess/LogAggregator.php
+++ b/core/DataAccess/LogAggregator.php
@@ -1084,6 +1084,122 @@ class LogAggregator
     }
 
     /**
+     * Similar to queryConversionsByDimension and will return data in the same format, but takes into account pageviews
+     * leading up to a conversion, not just the final page that triggered the conversion
+     *
+     * @param string $linkField
+     * @param int $rankingQueryLimit
+     *
+     * @return \Zend_Db_Statement|array
+     */
+    public function queryConversionsByPageView(string $linkField, $rankingQueryLimit = 0)
+    {
+
+        $query = $this->generateQuery(
+        // SELECT ...
+            implode(
+                ', ',
+                array(
+                    'log_conversion.idgoal AS idgoal',
+                    sprintf('log_link_visit_action.%s AS idaction', $linkField),
+                    'log_action.type',
+                    'log_conversion.idvisit',
+                    sprintf('log_conversion.idvisit AS `%d`', Metrics::INDEX_GOAL_NB_VISITS_CONVERTED),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('log_conversion.revenue'), Metrics::INDEX_GOAL_REVENUE),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('log_conversion.revenue_subtotal'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('log_conversion.revenue_tax'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_TAX),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('log_conversion.revenue_shipping'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('log_conversion.revenue_discount'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT),
+                    sprintf('log_conversion.items AS `%d`', Metrics::INDEX_GOAL_ECOMMERCE_ITEMS),
+                    sprintf('1 AS `%s`', Metrics::INDEX_GOAL_NB_CONVERSIONS_PAGE_UNIQ),
+                )
+            ),
+            // FROM...
+            array(
+                self::LOG_CONVERSION_TABLE,
+                array(
+                    "table" => "log_link_visit_action",
+                    "joinOn" => "log_link_visit_action.idvisit = log_conversion.idvisit AND log_link_visit_action.server_time <= log_conversion.server_time AND log_link_visit_action.".$linkField." IS NOT NULL"
+                ),
+                array(
+                    "table" => "log_action",
+                    "joinOn" => "log_action.idaction = log_link_visit_action.".$linkField." AND ".($linkField == 'idaction_url' ? 'log_action.type = 1' : 'log_action.type = 4')
+                )
+            ),
+            // WHERE ... AND ...
+            implode(
+                ' AND ',
+                array(
+                    'log_conversion.server_time >= ?',
+                    'log_conversion.server_time <= ?',
+                    'log_conversion.idsite IN ('.Common::getSqlStringFieldsArray($this->sites).')',
+                    'log_conversion.idgoal >= 0'
+                )
+            ),
+
+            // GROUP BY ...
+            false,
+
+            // ORDER ...
+            'NULL'
+        );
+
+        return $this->getDb()->query($query['sql'], $query['bind']);
+    }
+
+    /**
+     * Query conversions by entry page
+     *
+     * @param string $linkField
+     * @param int $rankingQueryLimit
+     *
+     * @return \Zend_Db_Statement|array
+     */
+    public function queryConversionsByEntryPageView(string $linkField, int $rankingQueryLimit = 0)
+    {
+        $tableName  = self::LOG_CONVERSION_TABLE;
+
+        $select = implode(
+                ', ',
+                array(
+                    'log_conversion.idgoal AS idgoal',
+                    sprintf('log_visit.%s AS idaction', $linkField),
+                    'log_action.type',
+                    sprintf('COUNT(*) AS `%d`', Metrics::INDEX_GOAL_NB_CONVERSIONS),
+                    sprintf('COUNT(distinct log_conversion.idvisit) AS `%d`', Metrics::INDEX_GOAL_NB_VISITS_CONVERTED),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion.revenue)'), Metrics::INDEX_GOAL_REVENUE_ENTRY),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion.revenue_subtotal)'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion.revenue_tax)'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_TAX),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion.revenue_shipping)'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING),
+                    sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion.revenue_discount)'), Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT),
+                    sprintf('SUM(log_conversion.items) AS `%d`', Metrics::INDEX_GOAL_ECOMMERCE_ITEMS),
+                    sprintf('COUNT(*) AS `%d`', Metrics::INDEX_GOAL_NB_CONVERSIONS_ENTRY)
+                )
+            );
+
+        $from = array(
+            $tableName,
+                array(
+                    "table"  => "log_visit",
+                    "joinOn" => "log_visit.idvisit = log_conversion.idvisit"
+                ),
+                array(
+                    "table" => "log_action",
+                    "joinOn" => "log_action.idaction = log_visit.".$linkField
+                )
+        );
+
+        $where   = $linkField.' IS NOT NULL AND log_conversion.idgoal >= 0';
+        $where   = $this->getWhereStatement($tableName, self::CONVERSION_DATETIME_FIELD, $where);
+        $groupBy = 'log_visit.'.$linkField.', log_conversion.idgoal';
+        $orderBy = false;
+
+        $query   = $this->generateQuery($select, $from, $where, $groupBy, $orderBy);
+
+        return $this->getDb()->query($query['sql'], $query['bind']);
+    }
+
+    /**
      * Creates and returns an array of SQL `SELECT` expressions that will each count how
      * many rows have a column whose value is within a certain range.
      *