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>

5 files changed, 196 insertions, 3 deletions

diff --git a/core/API/Inconsistencies.php b/core/API/Inconsistencies.php
index 7cc75081fb..26d94bbbdd 100644
--- a/core/API/Inconsistencies.php
+++ b/core/API/Inconsistencies.php
@@ -37,7 +37,6 @@ class Inconsistencies
             'bounce_rate_returning',
             'nb_visits_percentage',
             '/.*_evolution/',
-            '/goal_.*_conversion_rate/',
             '/step_.*_rate/',
             '/funnel_.*_rate/',
             '/form_.*_rate/',
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.
      *
diff --git a/core/DataTable.php b/core/DataTable.php
index 62ebc6db18..16a5dbf64f 100644
--- a/core/DataTable.php
+++ b/core/DataTable.php
@@ -1702,6 +1702,25 @@ class DataTable implements DataTableInterface, \IteratorAggregate, \ArrayAccess
     }
 
     /**
+     * Deletes a metadata property by name.
+     *
+     * @param bool|string $name The metadata name (omit to delete all metadata)
+     * @return bool True if the requested metadata was deleted
+     */
+    public function deleteMetadata($name = false) : bool
+    {
+        if ($name === false) {
+            $this->metadata = [];
+            return true;
+        }
+        if (!isset($this->metadata[$name])) {
+            return false;
+        }
+        unset($this->metadata[$name]);
+        return true;
+    }
+
+    /**
      * Returns all table metadata.
      *
      * @return array
diff --git a/core/DataTable/Filter/AddColumnsProcessedMetricsGoal.php b/core/DataTable/Filter/AddColumnsProcessedMetricsGoal.php
index 9840c9c6b6..07873abdaa 100644
--- a/core/DataTable/Filter/AddColumnsProcessedMetricsGoal.php
+++ b/core/DataTable/Filter/AddColumnsProcessedMetricsGoal.php
@@ -14,10 +14,17 @@ use Piwik\Piwik;
 use Piwik\Plugin\Metric;
 use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\AverageOrderRevenue;
 use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\ConversionRate;
+use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\ConversionPageRate;
+use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\ConversionEntryRate;
+use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\ConversionsAttrib;
+use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\ConversionsEntry;
 use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\Conversions;
 use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\ItemsCount;
 use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\Revenue;
 use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\RevenuePerVisit as GoalSpecificRevenuePerVisit;
+use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\RevenuePerEntry as GoalSpecificRevenuePerEntry;
+use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\RevenueAttrib;
+use Piwik\Plugins\Goals\Columns\Metrics\GoalSpecific\RevenueEntry;
 use Piwik\Plugins\Goals\Columns\Metrics\RevenuePerVisit;
 
 /**
@@ -58,6 +65,27 @@ use Piwik\Plugins\Goals\Columns\Metrics\RevenuePerVisit;
  */
 class AddColumnsProcessedMetricsGoal extends AddColumnsProcessedMetrics
 {
+
+    /**
+     * Process metrics for entry page views, with ECommerce
+     */
+    const GOALS_ENTRY_PAGES_ECOMMERCE = -6;
+
+    /**
+     * Process for page views, with ECommerce
+     */
+    const GOALS_PAGES_ECOMMERCE = -5;
+
+    /**
+     * Process metrics for entry page views
+     */
+    const GOALS_ENTRY_PAGES = -4;
+
+    /**
+     * Process for page views
+     */
+    const GOALS_PAGES = -3;
+
     /**
      * Process main goal metrics: conversion rate, revenue per visit
      */
@@ -129,14 +157,27 @@ class AddColumnsProcessedMetricsGoal extends AddColumnsProcessedMetrics
                 // When the table is displayed by clicking on the flag icon, we only display the columns
                 // Visits, Conversions, Per goal conversion rate, Revenue
                 if ($this->processOnlyIdGoal == self::GOALS_OVERVIEW) {
-                    continue;
+                   continue;
                 }
 
                 $extraProcessedMetrics[] = new Conversions($idSite, $idGoal); // PerGoal\Conversions or GoalSpecific\
                 $extraProcessedMetrics[] = new GoalSpecificRevenuePerVisit($idSite, $idGoal); // PerGoal\Revenue
                 $extraProcessedMetrics[] = new Revenue($idSite, $idGoal); // PerGoal\Revenue
 
-                if ($this->isEcommerce) {
+                if ($this->processOnlyIdGoal == self::GOALS_PAGES || $this->processOnlyIdGoal == self::GOALS_PAGES_ECOMMERCE) {
+                    $extraProcessedMetrics[] = new ConversionsAttrib($idSite, $idGoal); // PerGoal\Conversions or GoalSpecific\
+                    $extraProcessedMetrics[] = new RevenueAttrib($idSite, $idGoal); // PerGoal\Revenue attrib
+                    $extraProcessedMetrics[] = new ConversionPageRate($idSite, $idGoal); // PerGoal\ConversionRate for page uniq views
+                }
+
+                if ($this->processOnlyIdGoal == self::GOALS_ENTRY_PAGES || $this->processOnlyIdGoal == self::GOALS_ENTRY_PAGES_ECOMMERCE) {
+                    $extraProcessedMetrics[] = new ConversionsEntry($idSite, $idGoal); // PerGoal\Conversions or GoalSpecific\
+                    $extraProcessedMetrics[] = new GoalSpecificRevenuePerEntry($idSite, $idGoal); // PerGoal\Revenue entries
+                    $extraProcessedMetrics[] = new RevenueEntry($idSite, $idGoal); // PerGoal\Revenue entrances
+                    $extraProcessedMetrics[] = new ConversionEntryRate($idSite, $idGoal); // PerGoal\ConversionRate for entrances
+                }
+
+                if ($this->isEcommerce || $this->processOnlyIdGoal == self::GOALS_PAGES_ECOMMERCE || $this->processOnlyIdGoal == self::GOALS_ENTRY_PAGES_ECOMMERCE) {
                     $extraProcessedMetrics[] = new AverageOrderRevenue($idSite, $idGoal);
                     $extraProcessedMetrics[] = new ItemsCount($idSite, $idGoal);
                 }
diff --git a/core/Metrics.php b/core/Metrics.php
index cde226b608..a4d347ea1c 100644
--- a/core/Metrics.php
+++ b/core/Metrics.php
@@ -97,6 +97,15 @@ class Metrics
     const INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING = 6;
     const INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT = 7;
     const INDEX_GOAL_ECOMMERCE_ITEMS = 8;
+    const INDEX_GOAL_NB_PAGES_UNIQ_BEFORE = 9;
+    const INDEX_GOAL_NB_CONVERSIONS_ATTRIB = 10;
+    const INDEX_GOAL_NB_CONVERSIONS_PAGE_RATE = 11;
+    const INDEX_GOAL_NB_CONVERSIONS_PAGE_UNIQ = 12;
+    const INDEX_GOAL_NB_CONVERSIONS_ENTRY_RATE = 13;
+    const INDEX_GOAL_REVENUE_PER_ENTRY = 14;
+    const INDEX_GOAL_REVENUE_ATTRIB = 15;
+    const INDEX_GOAL_NB_CONVERSIONS_ENTRY = 16;
+    const INDEX_GOAL_REVENUE_ENTRY = 17;
 
     public static $mappingFromIdToName = array(
         Metrics::INDEX_NB_UNIQ_VISITORS                      => 'nb_uniq_visitors',
@@ -162,6 +171,15 @@ class Metrics
         Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING => 'revenue_shipping',
         Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT => 'revenue_discount',
         Metrics::INDEX_GOAL_ECOMMERCE_ITEMS            => 'items',
+        Metrics::INDEX_GOAL_NB_PAGES_UNIQ_BEFORE       => 'nb_conv_pages_before',
+        Metrics::INDEX_GOAL_NB_CONVERSIONS_ATTRIB      => 'nb_conversions_attrib',
+        Metrics::INDEX_GOAL_NB_CONVERSIONS_PAGE_RATE   => 'nb_conversions_page_rate',
+        Metrics::INDEX_GOAL_NB_CONVERSIONS_PAGE_UNIQ   => 'nb_conversions_page_uniq',
+        Metrics::INDEX_GOAL_NB_CONVERSIONS_ENTRY_RATE  => 'nb_conversions_entry_rate',
+        Metrics::INDEX_GOAL_REVENUE_PER_ENTRY          => 'revenue_per_entry',
+        Metrics::INDEX_GOAL_REVENUE_ATTRIB             => 'revenue_attrib',
+        Metrics::INDEX_GOAL_NB_CONVERSIONS_ENTRY       => 'nb_conversions_entry',
+        Metrics::INDEX_GOAL_REVENUE_ENTRY              => 'revenue_entry',
     );
 
     protected static $metricsAggregatedFromLogs = array(