Hey there. I still have one data cube that is referencing drupal.contribution_tracking. I will switch it to the new one this month and comment here when it is done if that is okay?

Maybe related question. Will drupal.exchange_rates still be active?

Thanks @JMando

There is no plan to change drupal.exchange_rates ... yet - although we do need to make one - good to know you use that table

FYI @Pcoombe - do you still use this contribution tracking table?

Yes, I use that extensively. Didn't realise there was any desire to deprecate it.

@Pcoombe this might make you feel better https://phabricator.wikimedia.org/T182030#8773967

I can also work on updating to use the new table this month, but there's quite a few scripts will need to be edited so it may take longer

Can you clarify: is utm_source going to remain available in the new table? I see it's there at the moment but T182030#8773967 mentions it only being available in the interim view. If it's going away, that will make things considerably more complicated

Also I assume tracking_date is the replacement for ts?

@Pcoombe there is no current plan to drop utm_source - although ideally it would be unnecessary as the data is separately broken out into other fields. I think it would be good if you can confirm the way/s in which you do find you still need to use if (for documentation purposes at the very least). tracking_date is the replacement for ts as you say.

There isn't a payment_method field as described in the other task, only payment_method_id and payment_submethod_id. What are those supposed to join to?

@Pcoombe the values are

 select v.value,v.label FROM civicrm_option_value v INNER JOIN
 civicrm_option_group g ON g.id = v.option_group_id WHERE g.name ='payment_metho
d';

and

select v.value,v.label FROM civicrm_option_value v INNER JOIN civicrm_option_group g ON g.id = v.option_group_id WHERE g.name ='payment_instrument';

Be warned that the value in the above queries is a string and in civicrm_contribution_tracking it is in integer - that can cause slow queries but you need to do that join I think @JMando has a way of casting the value in SQL that avoids the slow indexing issue

Change 993101 had a related patch set uploaded (by Pcoombe; author: Pcoombe):

[wikimedia/fundraising/analytics@master] Update test_tools to use civicrm.civicrm_contribution_tracking

https://gerrit.wikimedia.org/r/993101

Change 993101 merged by Pcoombe:

[wikimedia/fundraising/analytics@master] Update test_tools to use civicrm.civicrm_contribution_tracking

https://gerrit.wikimedia.org/r/993101

Okay, that's all the test_tools scripts updated, and I think I've done all the ad hoc queries in Google Sheets (e.g. for sidebar, app menu etc tracking)

That change to payment methods would require extensive rewriting, so I'm going to continue using utm_source.

If it helps, for payment_method name I do:

SELECT
    c.payment_instrument_id,
    cov.name AS payment_method,
FROM civicrm.civicrm_contribution AS c
LEFT JOIN civicrm.civicrm_option_value AS cov ON CAST(c.payment_instrument_id AS UNSIGNED) = CAST(cov.value AS UNSIGNED) AND CAST(cov.option_group_id AS UNSIGNED) = CAST(10 AS UNSIGNED)

And I have migrated over to civicrm.civicrm_contribution_tracking in the relevant scripts as well now. I still use drupal.exchange_rates though.

Thanks @JMando, that is helpful. However it would still require significant rewriting of some of the test results scripts, especially methods.py which we use for monitoring method popularity and conversion rates. I don't have time to undertake that at the moment.

Just noting that before fr-tech closes this ticket we should ensure we have comments in our code that tell us to check in with Peter before any changes to utm_source are made based on ^^

@JMando @Pcoombe - just flagging that I'm now actively starting to remove the drupal table - I believe you have gotten yourselves weened off it...

Change 1002670 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Stop writing to contribution tracking table

https://gerrit.wikimedia.org/r/1002670

Change 1002670 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Stop writing to contribution tracking table

https://gerrit.wikimedia.org/r/1002670

Change 1005605 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Update AdyenResolveTest to not interact with drupal contribution_tracking

https://gerrit.wikimedia.org/r/1005605

Change 1005620 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Fix Drupal contribution tracking lookup

https://gerrit.wikimedia.org/r/1005620

Change 1005625 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Fix wmf_audit to use a Civi::statics not a global

https://gerrit.wikimedia.org/r/1005625

Change 1005633 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Fix IngenicoResolveTest to not use fake contribution IDs

https://gerrit.wikimedia.org/r/1005633

Change 1005620 merged by Cstone:

[wikimedia/fundraising/crm@master] Fix Drupal contribution tracking lookup

https://gerrit.wikimedia.org/r/1005620

Change 1005633 merged by Cstone:

[wikimedia/fundraising/crm@master] Fix IngenicoResolveTest to not use fake contribution IDs

https://gerrit.wikimedia.org/r/1005633

Change 1005640 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Remove test code to delete drupal contribution tracking

https://gerrit.wikimedia.org/r/1005640

Change 1005641 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Remove BaseTestCase code to delete drupal contribution tracking

https://gerrit.wikimedia.org/r/1005641

Change 1005640 merged by Eileen:

[wikimedia/fundraising/crm@master] Remove test code to delete drupal contribution tracking

https://gerrit.wikimedia.org/r/1005640

Change 1005641 merged by Eileen:

[wikimedia/fundraising/crm@master] Remove BaseTestCase code to delete drupal contribution tracking

https://gerrit.wikimedia.org/r/1005641

Change 1005642 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Can the worm (remove more contribution tracking refs)

https://gerrit.wikimedia.org/r/1005642

Change 1005642 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Can the worm (remove more contribution tracking refs)

https://gerrit.wikimedia.org/r/1005642

Change 1006587 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Really truly deeply remove contribution tracking

https://gerrit.wikimedia.org/r/1006587

@Dwisehaupt we now no longer write to drupal.contribution & have disabled the module on prod/staging

Can you back up & delete the contribution_tracking table in the drupal database?

Change 1005605 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Update AdyenResolveTest to not interact with drupal contribution_tracking

Reason:

https://gerrit.wikimedia.org/r/1005605

Change 1006587 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Really truly deeply remove contribution tracking

https://gerrit.wikimedia.org/r/1006587

@Eileenmcnaughton In addition to the contribution_tracking table, there are also contribution_tracking_owa_ref and contribution_tracking_view. Do those need to be backed up and dropped also?

@Dwisehaupt I think that is right - + contribution_source actually - @Ejegg can you confirm?

@Pcoombe @JMando just mentioning that we are also going to remove contribution_source

contribution_source has been backed up also. I will do the deletion tomorrow when it's not at the end of the work day.

Dropped the 3 tables and 1 view:

MariaDB [drupal]> drop view drupal.contribution_tracking_view;
Query OK, 0 rows affected (0.001 sec)

MariaDB [drupal]> drop table drupal.contribution_tracking_owa_ref;
Query OK, 0 rows affected (0.009 sec)

MariaDB [drupal]> drop table drupal.contribution_tracking;
Query OK, 0 rows affected (0.307 sec)

MariaDB [drupal]> drop table drupal.contribution_source;
Query OK, 0 rows affected (0.085 sec)

Backup files are currently on frdb1005 and frlog1002

dallas@frdb1005:/srv/archive/database_dumps$ ls
drupal.contribution_source_backup-20240305.sql.gz
drupal.contribution_tracking_backup-20240301.sql.gz
drupal.contribution_tracking_owa_ref_backup-20240301.sql.gz
drupal.contribution_tracking_view_backup-20240301.sql.gz