Allow to backfill Goal conversion data after creating a goal #6183

mattab · 2014-09-10T02:35:53Z

Edit: ideally the backfill would work automatically from the UI as well (eg. a scheduled task running would backfil data for 1 month back), so it's a seamless experience for users.

The goal of this issue to let a user back-fill the goal conversions data for days in the past after having created a new goal. The benefits will be that Goal reports will be available for days in the past, even for new created goals: awesome!

Here is an idea of a solution: we need a new console command:

given a date range,
given a goal ID,
./console goals:backfill-conversions --date-range="2014-01-01,2014-02-01" --idgoal=1
it goes through the visits and actions log and look only for visits or actions that trigger a goal conversion for any of the goals.

Notes

invalidate the archived data after re-creating goal conversions in past dates. (will benefit from: invalidateArchivedReports should not delete the data but invalidate it until re-processed #5932).
if manually running a console command is not user friendly enough it may be possible to schedule the Backfill automatically as a scheduled task, eg. a setting for a goal Backfill data for this goal of a given goal or so.
add new FAQ to document command
add mention in Goal Tracking user guide
a console command working on logs already exists as geoIpUpdateRows and will be deprecated by new: New console command to replace script geoipUpdateRows.php #6146

Tasks

building an easy to use console command,
refactor code to re-use the Tracker logic with existing log,
test it on large instance,
write automated tests,
create user guide and faq

The text was updated successfully, but these errors were encountered:

tsteur · 2020-09-06T20:02:30Z

refs #11974

I think this one could now be done the following way:

When creating a new goal, then we trigger the rearchive logic that was added in Matomo 4 for that specific goal
The archiver can react to this in a custom way and populate data before the archiving (similar to Funnels)
It would basically fetch visits or actions that match the criteria, and create the corresponding conversion entries and rearchive the data for that goal
- It be easier to develop if we rearchived all goals data but more efficient to only rearchive the data for the specific goal
Any newly data would be collected live through the tracker

Note that this might be quite intense on the DB.

Same procedure could technically also work for updating goal but things get more complicated since there's existing data.

atom-box · 2023-08-30T22:31:05Z

A user requested this feature today:

Question: What are your biggest problems or pain points with Matomo and why?
Answer:
Goals / Conversions werden nicht für vergangene Ereignisse ermittelt. (are not determined for past events.)

ampaze · 2023-08-31T07:00:44Z

For anyone waiting for this feature, here are two MySQL queries that may work as a starting point for your own needs. I only needed regex goals, so thats what I hardcoded.

First you need to determine the actual conversions, in my case it took around 2 minutes to check 1000000 visits for 3 goals. I recommend to run this query in chunks.

INSERT INTO `matomo_log_conversion` (`idvisit`, `idsite`, `idvisitor`, `server_time`, `idaction_url`, `idlink_va`, `idgoal`, `buster`, `idorder`, `items`, `url`, `visitor_returning`, `visitor_count_visits`, `referer_keyword`, `referer_name`, `referer_type`, `config_device_brand`, `config_device_model`, `config_device_type`, `location_city`, `location_country`, `location_latitude`, `location_longitude`, `location_region`, `revenue`, `revenue_discount`, `revenue_shipping`, `revenue_subtotal`, `revenue_tax`, `custom_var_k1`, `custom_var_v1`, `custom_var_k2`, `custom_var_v2`, `custom_var_k3`, `custom_var_v3`, `custom_var_k4`, `custom_var_v4`, `custom_var_k5`, `custom_var_v5`, `campaign_content`, `campaign_id`, `campaign_keyword`, `campaign_medium`, `campaign_name`, `campaign_source`, `visitor_seconds_since_first`, `visitor_seconds_since_order`, `custom_dimension_1`, `custom_dimension_2`, `custom_dimension_3`, `custom_dimension_4`, `custom_dimension_5`, `config_browser_name`, `config_client_type`, `campaign_group`, `campaign_placement`)

SELECT va.`idvisit`, va.idsite, va.`idvisitor`,va.`server_time`, va.`idaction_url`, va.idlink_va, g.`idgoal`, 
va.idlink_va 'buster', null 'idorder', null 'items',
CONCAT('https://www.', a.name) url,
v.`visitor_returning`, v.`visitor_count_visits`,
v.`referer_keyword`, v.`referer_name`, v.`referer_type`,
v.`config_device_brand`, v.`config_device_model`, v.`config_device_type`,
v.`location_city`, v.`location_country`, v.`location_latitude`, v.`location_longitude`, v.`location_region`,
0 'revenue', null 'revenue_discount', null 'revenue_shipping', null 'revenue_subtotal', null 'revenue_tax',
v.`custom_var_k1`, v.`custom_var_v1`, v.`custom_var_k2`, v.`custom_var_v2`, 
v.`custom_var_k3`, v.`custom_var_v3`, v.`custom_var_k4`, v.`custom_var_v4`, 
v.`custom_var_k5`, v.`custom_var_v5`,
v.`campaign_content`, v.`campaign_id`, v.`campaign_keyword`, v.`campaign_medium`, v.`campaign_name`, v.`campaign_source`,
v.`visitor_seconds_since_first`, v.`visitor_seconds_since_order`,
v.`custom_dimension_1`, v.`custom_dimension_2`, v.`custom_dimension_3`, v.`custom_dimension_4`, v.`custom_dimension_5`,
v.`config_browser_name`, v.`config_client_type`, v.`campaign_group`, v.`campaign_placement`

FROM `matomo_log_link_visit_action` va 
JOIN `matomo_log_action` a ON a.type = 1 AND a.`idaction` = va.`idaction_url`
JOIN matomo_goal g ON g.deleted = 0 AND g.`match_attribute` = 'url' AND g.pattern_type = 'regex' AND g.allow_multiple = 1 AND CONCAT('https://www.', a.name) REGEXP g.pattern
JOIN `matomo_log_visit` v ON v.idvisit = va.`idvisit`
WHERE va.idsite = 1
-- AND va.idvisit > 12000000 AND va.idvisit <= 13000000 

ORDER BY va.idvisit ASC

Second the visit logs need to be updated to let the archiver know, if there are conversions for a visit:

UPDATE `matomo_log_visit` v
JOIN  `matomo_log_conversion` c ON c.idvisit = v.idvisit
SET v.`visit_goal_converted` = 1

Finally, invalidate all reports, or the date range you need, and after re-archiving the conversions will be available in the UI.

Good luck.

48design · 2023-09-05T11:47:52Z

Hi!

I created a Matomo plugin based on this issue and the code found here. You can check out BackfillGoals here and tell me if it is working for you, too.

Best regards!

atom-box · 2023-10-19T23:11:26Z

A user:

Is it possible to retroactively write to the goals module based on historical visits? I imported data from GA3 to Matomo, and then installed the tracker on my website. However, I didn't recreate the goals in matomo so I haven't been tracking visits to the sites I need from my goals. If I can go back and retroactively identify visits to certain pages that would be great.

48design · 2023-10-20T06:32:10Z

If I understand this correctly you like to create goals based on (old) statistics, right?
Since the Goals are just Regex parsed URLs you have to write a Regex (using Regex101 for example) for relevant URLs you like to handle as Goals and run BackfillGoals afterwards.

mattab added the Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. label Sep 10, 2014

mattab added this to the Mid term milestone Sep 10, 2014

czolnowski mentioned this issue Sep 10, 2014

New console command to replace script geoipUpdateRows.php #6146

Closed

mattab modified the milestones: Mid term, Long term Oct 11, 2014

mattab modified the milestones: Long term, Mid term Dec 23, 2015

mattab added Lower priority and removed Lower priority labels Dec 5, 2016

mattab modified the milestones: Long term, Mid term Dec 5, 2016

mattab removed the Lower priority label Sep 13, 2018

mattab added the Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical. label Jul 19, 2019

mattab modified the milestones: Backlog (Help wanted), Priority Backlog (Help wanted) Jul 19, 2019

mattab mentioned this issue Dec 10, 2023

for newly created Goals, calculate their historical reports from the historical data (when possible) #1550

Closed

mattab added the c: Consistent Reports & Analytics UX For bugs and features that make Analytics reporting UI behave more consistently. label Dec 10, 2023

mattab added c: Data Integrity & Accuracy c: Goals For bugs and features related to goals tracking & reporting. labels Dec 10, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Allow to backfill Goal conversion data after creating a goal #6183

Allow to backfill Goal conversion data after creating a goal #6183

mattab commented Sep 10, 2014 •

edited

tsteur commented Sep 6, 2020

atom-box commented Aug 30, 2023

ampaze commented Aug 31, 2023

48design commented Sep 5, 2023

atom-box commented Oct 19, 2023

48design commented Oct 20, 2023

Allow to backfill Goal conversion data after creating a goal #6183

Allow to backfill Goal conversion data after creating a goal #6183

Comments

mattab commented Sep 10, 2014 • edited

Edit: ideally the backfill would work automatically from the UI as well (eg. a scheduled task running would backfil data for 1 month back), so it's a seamless experience for users.

tsteur commented Sep 6, 2020

atom-box commented Aug 30, 2023

ampaze commented Aug 31, 2023

48design commented Sep 5, 2023

atom-box commented Oct 19, 2023

48design commented Oct 20, 2023

mattab commented Sep 10, 2014 •

edited