Allow to backfill Goal conversion data after creating a goal

mattab commented 10 years ago

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: #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: #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

tsteur commented 4 years ago

refs https://github.com/matomo-org/matomo/issues/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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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.

9joshua commented 5 months ago

Changing the definition of a Goal deletes historical conversions. A customer has this issue and would like the ability to retrospectively track goals.

nicaille commented 5 months ago

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!

Is it plan to migrate that plugin for Matomo 5.0.3?

48design commented 5 months ago

I'm sorry but I don't have the time to do so at the moment.

enual commented 2 months ago

Adding comment on this topic because a user is looking for this feature.

matomo-org / matomo

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

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.