-
-
Notifications
You must be signed in to change notification settings - Fork 2.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Allow to backfill Goal conversion data after creating a goal #6183
Comments
refs #11974 I think this one could now be done the following way:
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. |
A user requested this feature today: Question: What are your biggest problems or pain points with Matomo and why? |
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. |
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! |
A user:
|
If I understand this correctly you like to create goals based on (old) statistics, right? |
Changing the definition of a Goal deletes historical conversions. A customer has this issue and would like the ability to retrospectively track goals. |
Is it plan to migrate that plugin for Matomo 5.0.3? |
I'm sorry but I don't have the time to do so at the moment. |
Adding comment on this topic because a user is looking for this feature. |
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:
./console goals:backfill-conversions --date-range="2014-01-01,2014-02-01" --idgoal=1
Notes
Backfill data for this goal
of a given goal or so.Tasks
The text was updated successfully, but these errors were encountered: