E-Commerce: Sales by pages reports shows wrong orders

[utrautmann]

Context

It is about analyzing which pages (e.g. entry pages) lead to how much sales. In the e-commerce reports, I analyzed which entry pages led to how many orders.

Expected Behavior

The expectation is that the sales per pages reports will give me the same number of orders as anywhere else in the sales reports.

Current Behavior

Instead, I'm being issued far more orders than were placed in the selected time period. See the screenshots here:

Sales by referrers

This report shows me correct order values.

Sales by entry pages

Here we will see to much orders:

As a result, the revenue is also much too high.

Possible Solution

Your Environment

Matomo Cloud / Matomo on-premise 4.14.2

[sgiehl]

@bx80 any idea what could cause this?

[bx80]

@sgiehl Interestingly the order figures seem to be accurate when viewed under Goals > Overview > Goals by Pages > Entry pages (Ecommerce order column). Perhaps the reports being shown under the eCommerce sales menu are using the wrong metrics for pages? For other page/goal metrics linear attribution is being applied to the values, if this isn't happening for the order count then it would cause the numbers to be inflated by the number of pages viewed before each conversion.

It also looks like eCommerce is missing detailed tests for Sales by pages reports similar to the ones that were added for goals by pages It would probably make sense to add these tests as a starting point to recreate this issue as part of any fix.

[hoji1]

Hi!

We also have problem with wrong figures at Sales by pages > Page Url, so I checked into it in a controlled test environment.

For us, it looks like the value under column "e-commerce orders" are identical with the value of number of page views - but these page views are only counted if a user bought something. The actual order value is then multiplied with the number of page views (under column ecommerce-orders) which makes us get some crazy values.

Any page view for user who did not buy anything is not counted.

[MatomoForumNotifications]

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/discrepancy-in-total-revenue/53937/6

[vadym-perenesenko-moc]

@bx80 Hi, do you have any plans to fix this in the next sprint? This is a very critical issue for me

[michalkleiner]

Hi @vadym-perenesenko-moc, the product team has a backlog of issues and they will prioritise it accordingly.

[vadym-perenesenko-moc]

@bx80 @michalkleiner

Hi there. Sorry to bother you, could you tell me if there is any progress on this issue? This issue is critical for us because in this case, revenue is calculated absolutely incorrectly

[tsteur]

For us in our account it's the other way around that it doesn't show any ecommerce orders in the "Sales by Pages - Entry Pages" report:

But it shows the correct data in the "By channels" report

[vadym-perenesenko-moc]

@mattab Hi. Could you please advise if there's any way to escalate the priority of this bug? Due to this bug, e-commerce tracking makes no sense, as the revenue is completely unrealistic.

My ticket on the forum https://forum.matomo.org/t/discrepancy-in-total-revenue/53937

[taras-turchenko-moc]

@michalkleiner @bx80 Hi. Seems like the cause of an issue is queryConversionsByPageView method of core/DataAccess/LogAggregator.php. I have a single record in matomo_log_conversion table however one of inner queries returns multiple rows thats why all metrics increases

Can you help with the issue? We need to fix it as soon as possible because it breaks a lot of metrics and makes analytics useless

[taras-turchenko-moc]

[taras-turchenko-moc]

[taras-turchenko-moc]

Metric names

    // Goal reports
    const INDEX_GOAL_NB_CONVERSIONS = 1;
    const INDEX_GOAL_REVENUE = 2;
    const INDEX_GOAL_NB_VISITS_CONVERTED = 3;
    const INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL = 4;
    const INDEX_GOAL_ECOMMERCE_REVENUE_TAX = 5;
    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;

[taras-turchenko-moc]

@michalkleiner @bx80 The issue reproduces when we have a multiple matomo_log_link_visit_action for single matomo_log_conversion. Adding DISTINCT here fixes the issue but I don't know what issues it can bring and I believe it's better to modify join conditions to filter out extra visit actions but I don't have enough understanding to do it

[atom-box]

A user reported this same problem on their site. It is a major problem for them.

In the screenshot below, only (1) gives accurate numbers. The other parts (2)(3)(4)(5) give multiples of the correct data; the data is double or triple what it should be.

[taras-turchenko-moc]

In matomo v5 each page view creates new matomo_log_link_visit_action (event page reload). Therefore when matomo makes join matomo_log_conversion on matomo_log_link_visit_action a database returns duplicated log conversion rows for each page view

Here is raw sql query

SELECT /* sites 2 */ /* 2024-05-23,2024-05-24 */ /* Actions ActionReports */ /* trigger = CronArchive */
    log_conversion.idvisit AS idvisit,
    0 AS idgoal,
    1 AS `type`,
    lac.idaction AS idaction,
    COUNT(*) AS `1`,
    ROUND(SUM(log_conversion.revenue),2) AS `2`,
    COUNT(log_conversion.idvisit) AS `3`,
    ROUND(SUM(1 / log_conversion.pageviews_before * log_conversion.revenue_subtotal),2) AS `4`,
    ROUND(SUM(1 / log_conversion.pageviews_before * log_conversion.revenue_tax),2) AS `5`,
    ROUND(SUM(1 / log_conversion.pageviews_before * log_conversion.revenue_shipping),2) AS `6`,
    ROUND(SUM(1 / log_conversion.pageviews_before * log_conversion.revenue_discount),2) AS `7`,
    SUM(1 / log_conversion.pageviews_before * log_conversion.items) AS `8`,
    log_conversion.pageviews_before AS `9`,
    SUM(1 / log_conversion.pageviews_before) AS `10`,
    COUNT(*) AS `12`,
    ROUND(SUM(1 / log_conversion.pageviews_before * log_conversion.revenue),2) AS `15`
FROM matomo_log_conversion AS log_conversion
     RIGHT JOIN matomo_log_link_visit_action AS logva ON log_conversion.idvisit = logva.idvisit
     LEFT JOIN matomo_log_action AS lac ON logva.idaction_url = lac.idaction
WHERE
    log_conversion.server_time >= '2024-05-23 21:00:00'
  AND log_conversion.server_time <= '2024-05-24 20:59:59'
  AND log_conversion.idsite IN (2)AND log_conversion.idgoal = 0
  AND logva.server_time <= log_conversion.server_time
  AND lac.type = 1
GROUP BY
    log_conversion.idvisit, lac.idaction;

[goochj03]

I have another customer being affected by this same issue.

[KarthikRaja1388]

Another customer having the same issue.