Page MenuHomePhabricator
Create Task
Maniphest T406193

CiviCRM / Analytics data clean up
Closed, ResolvedPublic
Actions

Description

This is a ticket just to track data clean up where data is being manipulated by @JMando to compensate for poor data hygeine in Civi

https://gerrit.wikimedia.org/r/plugins/gitiles/wikimedia/fundraising/analytics/+/refs/heads/master/dagster/fundraising_analytics_dbt/models/marts/core/intermediate/int_donations.sql

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
Fix Direct Mail option valuewikimedia/fundraising/crmmaster+1 -1
Insert rows for channel = Email where mailing_identifier is not nullwikimedia/fundraising/crmmaster+39 -0
Fix the last mis-labelled recurring giftswikimedia/fundraising/crmmaster+29 -0
Bump it into action againwikimedia/fundraising/crmmaster+4 -4
Kick start the upgraderwikimedia/fundraising/crmmaster+5 -5
Run recurring upgrade again with UPDATE ON DUPLICATEwikimedia/fundraising/crmmaster+7 -4
Insert gift data rows for subsequen recurrings without themwikimedia/fundraising/crmmaster+41 -0
Fix a few recurring gifts that got the wrong channelwikimedia/fundraising/crmmaster+25 -0
Fix ambiguous table namewikimedia/fundraising/crmmaster+8 -5
Set is_major_gift on ingress and backfill prior datawikimedia/fundraising/crmmaster+150 -1
Fix Direct_Mail to Direct Mailwikimedia/fundraising/crmmaster+14 -0
Remove extraneous commawikimedia/fundraising/crmmaster+2 -2
Update channel for bannerswikimedia/fundraising/crmmaster+74 -0
Switch to Direct Mail (space)wikimedia/fundraising/crmmaster+1 -1
Fix some missing mailing identifierswikimedia/fundraising/crmmaster+18 -0
Minor code tidy upwikimedia/fundraising/crmmaster+2 -4
Update utm_medium to Direct_Mailwikimedia/fundraising/crmmaster+48 -0
Still trying to catch themwikimedia/fundraising/crmmaster+3 -3
Try again to broaden the netwikimedia/fundraising/crmmaster+5 -5
Fix channel update for sparser datasetwikimedia/fundraising/crmmaster+5 -5
Increment upgrade numberwikimedia/fundraising/crmmaster+1 -1
Fix mishandling of NULLwikimedia/fundraising/crmmaster+1 -1
Retry recurring gift update with fixed startwikimedia/fundraising/crmmaster+39 -39
Schedule backfill on recurring gift channelwikimedia/fundraising/crmmaster+39 -0
Extend our channel backfillwikimedia/fundraising/crmmaster+27 -0
Update existing email donations to have email as channelwikimedia/fundraising/crmmaster+47 -0
Update channel comparison to be lowerwikimedia/fundraising/crmmaster+1 -1
Show related patches Customize query in gerrit

Event Timeline

Eileenmcnaughton created this task.Oct 2 2025, 4:22 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 2 2025, 4:22 AM
Eileenmcnaughton added a comment.EditedOct 2 2025, 4:25 AM

@JMando the very first clause you have is

when
              gift_source in ('Chapter Gift', 'Chapter')
              or channel = 'Chapter Gifts'
              then 'chapter gifts'

I think you can now change it to

when
             channel = 'Chapter Gifts'
              then 'chapter gifts'

There were 105 chapter gifts prior to the start of this fiscal year. In all cases channel was empty and now it is chapter_gifts

All the ones created since are correct so we don't need to fix incoming

Eileenmcnaughton renamed this task from CiviCRM / Acoustic data clean up to CiviCRM / Analytics data clean up.Oct 2 2025, 10:57 PM
Eileenmcnaughton added a comment.Oct 5 2025, 9:59 PM

The next clause

financial_type_name = 'Endowment Gift'
            and regexp_like(utm_source, '^sp')
            then 'endowment email'
  • I have confirmed that 100% of the time when regexp_like(utm_source, '^sp') the mailing identifier is correctly populated
select * FROM civicrm_contribution_tracking WHERE utm_source LIKE 'sp%' AND mailing_identifier NOT LIKE 'sp%' LIMIT 1\G
Empty set (8 min 15.303 sec)

select * FROM civicrm_contribution_tracking WHERE mailing_identifier LIKE '%'  AND mailing_identifier NOT LIKE 'sp%' LIMIT 1\G
Empty set (5 min 12.857 sec)
Eileenmcnaughton added a comment.EditedOct 5 2025, 10:19 PM

Further it looks like 100% of cases where mailing_identifier is populated have no entry for 'channel'

select channel, count(*) FROM civicrm_contribution_tracki
ng LEFT JOIN civicrm_value_1_gift_data_7 ON entity_id = contribution_id WHERE ma
iling_identifier LIKE '%' GROUP BY channel;
+---------+----------+
| channel | count(*) |
+---------+----------+
| NULL    | 24356733 |
|         |      183 |

SO I proposed that

  1. on ingress if mailing_identifier is set THEN channel = email
  2. I schedule an update to set channel to 'email'

UPDATE civicrm_value_1_gift_data_7 INNER JOIN civicrm_contribution_tracking ON entity_id = contribution_id SET channel = 'Email' WHERE mailing_identifier IS NOT NULL

Query OK, 16489818 rows affected (33 min 53.495 sec)
Rows matched: 16489818 Changed: 16489818 Warnings: 0

Eileenmcnaughton added a comment.Oct 6 2025, 12:31 AM

So this is how the php function is starting to look

public function getChannel(): string {
   if (!empty($this->message['Gift_Data.Channel'])) {
     return (string) $this->message['Gift_Data.Channel'];
   }
   if (!empty($this->message['channel'])) {
     return $this->message['channel'];
   }
   if (!empty($this->message['recipient_id'])) {
     return 'SMS';
   }
   $utmSource = $this->message['utm_source'] ?? '';
   
   if (str_starts_with($utmSource', 'B')) {
     return 'Banner';
   }
   if (str_starts_with($utmSource', 'sp')) {
     return 'Email';
   }
   return '';
 }
Eileenmcnaughton added a comment.EditedOct 6 2025, 12:33 AM

ALSO

UPDATE civicrm_value_1_gift_data_7 INNER JOIN civicrm_contribution_tracking ON entity_id = contribution_id SET channel = 'Banner' WHERE utm_source LIKE 'b%' AND (channel IS NULL OR channel = '');

Query OK, 44391685 rows affected (1 hour 8 min 48.069 sec)
Rows matched: 44391685 Changed: 44391685 Warnings: 0

todo - check if case matters for utm_source LIKE as our like is case insensitive

Eileenmcnaughton added a comment.Oct 6 2025, 3:03 AM
/* 35968301 is a corporate donor that often makes small transfers. To prevent small
    transfers from being tagged as Online gifts, this CID is identified as a MG donor. Other
    CIDs are MG-specific donors that usually make large gifts; all located together to
    easily identify when questions arise */
    when
        contact_id in (
            35968301
            , 3729480
            , 10988750
            , 27693813
        )
        then 'major gifts'
ContactLast donation
359683012021
3729480 (matching gifts?)sep 2025
109887502016
27693813never - related contact
AnnWF moved this task from Triage to Current Sprint on the Fundraising-Backlog board.Oct 6 2025, 8:29 PM
gerritbot added a comment.Oct 6 2025, 9:17 PM

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

[wikimedia/fundraising/crm@master] Set is_major_gift on ingress

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

gerritbot added a project: Patch-For-Review.Oct 6 2025, 9:17 PM
Eileenmcnaughton added a comment.EditedOct 7 2025, 3:01 AM

Re major gifts

UPDATE civicrm_value_1_gift_data_7 LEFT JOIN civicrm_contribution c ON c.id = entity_id SET is_major_gift = 1 WHERE total_amount >= 10000 ;

  • this is quick & easy but gets chaper gifts too

ditto this - 5 seconds

UPDATE civicrm_value_1_gift_data_7 LEFT JOIN civicrm_contribution_tracking ct ON contribution_id = entity_id SET is_major_gift = 1 WHERE utm_medium LIKE 'MG%';

AKanji-WMF added a project: Fundraising Sprint: Temp file pura.Oct 7 2025, 8:15 PM
JMando added a comment.Oct 8 2025, 9:32 PM

Screenshot 2025-10-08 at 4.31.21 PM.png (927×339 px, 56 KB)

Eileenmcnaughton added a project: FR-WMF-Audit.Oct 9 2025, 12:25 AM
gerritbot added a comment.Oct 9 2025, 9:16 PM

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

[wikimedia/fundraising/crm@master] Update channel comparison to be lower

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

gerritbot added a comment.Oct 9 2025, 9:49 PM

Change #1195061 merged by Eileen:

[wikimedia/fundraising/crm@master] Update channel comparison to be lower

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

gerritbot added a comment.Oct 9 2025, 10:47 PM

Change #1195075 had a related patch set uploaded (by Lars SG; author: Lars SG):

[wikimedia/fundraising/crm@master] Fix Direct Mail option value

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

Eileenmcnaughton moved this task from Backlog to Doing on the Fundraising Sprint: Temp file pura board.Oct 14 2025, 7:38 PM
gerritbot added a comment.Oct 16 2025, 6:39 PM

Change #1194788 had a related patch set uploaded (by Ejegg; author: Eileen):

[wikimedia/fundraising/crm@master] Update existing email donations to have email as channel

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

gerritbot added a comment.Oct 16 2025, 7:32 PM

Change #1194788 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Update existing email donations to have email as channel

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

AKanji-WMF edited projects, added Fundraising Sprint: UTM_key lime pie; removed Fundraising Sprint: Temp file pura.Oct 21 2025, 8:19 PM
AKanji-WMF moved this task from Backlog to Doing on the Fundraising Sprint: UTM_key lime pie board.
gerritbot added a comment.Oct 23 2025, 2:52 PM

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

[wikimedia/fundraising/crm@master] Extend our channel backfill

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

gerritbot added a comment.Oct 24 2025, 8:01 PM

Change #1198342 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Extend our channel backfill

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

gerritbot added a comment.Oct 29 2025, 3:49 PM

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

[wikimedia/fundraising/crm@master] Schedule backfill on recurring gift channel

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

gerritbot added a comment.Oct 30 2025, 2:30 AM

Change #1199817 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Schedule backfill on recurring gift channel

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

gerritbot added a comment.Nov 3 2025, 7:48 PM

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

[wikimedia/fundraising/crm@master] Retry recurring gift update with fixed start

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

gerritbot added a comment.Nov 3 2025, 8:12 PM

Change #1201188 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Retry recurring gift update with fixed start

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

gerritbot added a comment.Nov 3 2025, 8:33 PM

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

[wikimedia/fundraising/crm@master] Fix mishandling of NULL

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

gerritbot added a comment.Nov 3 2025, 8:51 PM

Change #1201229 merged by Eileen:

[wikimedia/fundraising/crm@master] Fix mishandling of NULL

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

gerritbot added a comment.Nov 3 2025, 9:09 PM

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

[wikimedia/fundraising/crm@master] Increment upgrade number

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

gerritbot added a comment.Nov 3 2025, 9:10 PM

Change #1201263 merged by Eileen:

[wikimedia/fundraising/crm@master] Increment upgrade number

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

gerritbot added a comment.Nov 3 2025, 9:57 PM

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

[wikimedia/fundraising/crm@master] Fix channel update for sparser dataset

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

gerritbot added a comment.Nov 3 2025, 10:18 PM

Change #1201289 merged by Eileen:

[wikimedia/fundraising/crm@master] Fix channel update for sparser dataset

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

gerritbot added a comment.Nov 3 2025, 11:14 PM

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

[wikimedia/fundraising/crm@master] Try again to broaden the net

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

gerritbot added a comment.Nov 3 2025, 11:32 PM

Change #1201303 merged by Eileen:

[wikimedia/fundraising/crm@master] Try again to broaden the net

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

gerritbot added a comment.Nov 4 2025, 12:33 AM

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

[wikimedia/fundraising/crm@master] Still trying to catch them

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

gerritbot added a comment.Nov 4 2025, 12:49 AM

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

[wikimedia/fundraising/crm@master] Update utm_medium to Direct_Mail

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

gerritbot added a comment.Nov 4 2025, 12:54 AM

Change #1201313 merged by Eileen:

[wikimedia/fundraising/crm@master] Still trying to catch them

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

gerritbot added a comment.Nov 4 2025, 3:35 AM

Change #1201317 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Update utm_medium to Direct_Mail

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

Eileenmcnaughton added a comment.Nov 4 2025, 3:49 AM

@JMando

I find we have a utm_medium pagelink that is getting swept into the email channel due to us doing a case insensitive search at that point.

  1. should we update mailing identifier for the approx 8000 that are oddly missing it
  2. should I explicitly code utm_medium = pagelink to Online Other channel?

SELECT utm_medium, LEFT(utm_source, 30), MIN(tracking_date), MAX(tracking_date), count(*) FROM civicrm_contribution_tracking WHERE utm_source LIKE 'sp%' AND mailing_identifier IS NULL GROUP BY utm_medium, LEFT(utm_source, 30);

utm_mediumLEFT(utm_source, 30)MIN(tracking_date)MAX(tracking_date)count(*)
NULLsplunge..cc2011-12-03 19:06:242011-12-03 19:06:241
emailsp734932452024-11-22 16:23:452024-11-22 16:23:451
emailsp746261752024-11-21 15:05:232024-11-24 22:33:082095
emailsp746262422024-11-21 15:05:042024-11-24 23:44:041477
emailsp746262492024-11-21 15:10:322024-11-24 23:28:513975
pagelinkSpecial:AbuseFilter/examine.de2024-07-11 13:03:082024-09-11 14:11:046
pagelinkSpecial:AbuseFilter/history.de2024-03-02 23:42:322024-03-02 23:42:321
pagelinkSpecial:AbuseLog.default~defau2024-01-23 14:07:112024-11-06 11:45:0119
pagelinkSpecial:BlockList.default~defa2024-02-27 05:55:082024-12-05 08:25:318
pagelinkSpecial:Categories.default~def2024-10-24 15:48:472024-10-24 15:48:471
pagelinkSpecial:CentralAuth.default~de2023-09-27 05:21:102023-09-27 05:21:392
pagelinkSpecial:Contributions.default~2024-06-04 07:00:462024-10-29 20:27:555
pagelinkSpecial:Contributions/FuzzyBot2024-07-24 23:07:012024-07-24 23:07:011
pagelinkSpecial:Contributions/GVarnum-2023-10-02 07:47:172024-01-02 20:10:183
pagelinkSpecial:Contributions/LincolnB2024-06-05 09:13:152024-09-08 14:48:312
pagelinkSpecial:Contributions/Nuengtsp2024-07-05 18:42:522024-07-05 18:43:403
pagelinkSpecial:Contributions/Phungdam2024-06-25 21:45:082024-06-25 21:45:081
pagelinkSpecial:Contributions/Steinspl2024-08-10 20:36:152024-08-10 20:36:151
pagelinkSpecial:CreateAccount.default~2023-09-24 06:31:012024-11-30 09:21:10106
pagelinkSpecial:EmailUser/Murbaut.defa2024-01-25 11:40:372024-01-25 11:40:371
pagelinkSpecial:History/Category:Delet2024-01-23 00:05:232024-01-23 00:05:231
pagelinkSpecial:History/Policy_talk:Te2023-12-15 06:47:022023-12-15 06:47:021
pagelinkSpecial:History/Policy:Cookie_2023-09-23 13:20:562024-01-20 10:36:569
pagelinkSpecial:History/Policy:Donor_p2023-11-05 18:23:282023-12-09 18:50:014
pagelinkSpecial:History/Policy:General2023-10-02 11:45:062023-10-02 11:45:061
pagelinkSpecial:History/Policy:Non-dis2024-01-08 14:29:412024-01-08 14:29:411
pagelinkSpecial:History/Policy:Privacy2023-08-24 01:38:022024-01-26 09:35:0211
pagelinkSpecial:History/Policy:Terms_o2023-09-22 19:16:132024-01-11 07:05:4115
pagelinkSpecial:History/Resolution:Lic2023-10-11 02:46:342023-10-11 02:46:341
pagelinkSpecial:History/Terms_of_Use.d2023-11-19 19:32:532023-11-19 19:40:472
pagelinkSpecial:History/Volunteer_Hour2023-10-26 02:53:452023-10-26 02:53:451
pagelinkSpecial:History/Wikimedia:Abou2023-10-09 05:24:192024-01-29 18:50:333
pagelinkSpecial:History/Wikimedia:Inte2023-10-10 19:38:472023-10-10 19:38:471
pagelinkSpecial:History/Wikimedia:Tran2024-01-23 23:29:022024-01-23 23:29:021
pagelinkSpecial:LanguageStats.default~2024-11-05 01:31:232024-11-14 23:55:392
pagelinkSpecial:ListUsers.default~defa2023-08-17 00:08:582024-03-03 10:58:482
pagelinkSpecial:Log.default~default~de2023-11-18 09:26:542024-11-10 02:08:5920
pagelinkSpecial:Log/block.default~defa2024-09-09 07:20:492024-09-09 07:20:502
pagelinkSpecial:MessageGroupStats.defa2024-10-04 00:53:402024-10-04 00:53:433
pagelinkSpecial:MobileDiff/115758.defa2023-10-19 16:40:312023-10-19 16:40:324
pagelinkSpecial:MobileDiff/116731.defa2023-11-26 14:36:462023-11-26 14:36:461
pagelinkSpecial:MobileDiff/117571.defa2023-11-07 15:43:082023-11-07 15:43:081
pagelinkSpecial:MobileDiff/150097.defa2023-11-18 07:04:372023-11-18 07:04:371
pagelinkSpecial:MobileDiff/180977.defa2024-01-14 15:42:582024-01-14 15:42:581
pagelinkSpecial:MobileDiff/187650.defa2023-10-12 15:05:052023-10-12 15:05:051
pagelinkSpecial:MobileDiff/258677.defa2023-09-22 15:59:302023-09-22 15:59:301
pagelinkSpecial:MobileDiff/261066.defa2023-09-26 08:40:452023-09-26 08:40:451
pagelinkSpecial:MobileDiff/267216.defa2023-10-14 12:35:352023-10-15 06:02:013
pagelinkSpecial:MobileDiff/277594.defa2023-12-09 03:12:332023-12-09 03:12:331
pagelinkSpecial:MobileDiff/282276.defa2023-11-03 09:36:432023-11-03 09:36:442
pagelinkSpecial:MobileDiff/284527.defa2023-10-13 11:11:412023-10-13 11:11:411
pagelinkSpecial:MobileDiff/285736.defa2023-10-05 20:45:442023-10-05 20:45:441
pagelinkSpecial:MobileDiff/288284.defa2023-10-13 14:59:092023-10-13 14:59:091
pagelinkSpecial:MobileDiff/291511.defa2023-11-03 17:28:422023-11-03 17:28:422
pagelinkSpecial:MobileDiff/291627.defa2023-11-02 00:03:382023-12-09 00:06:562
pagelinkSpecial:MobileDiff/291628.defa2023-11-19 23:22:072023-12-12 21:13:293
pagelinkSpecial:MobileDiff/293754.defa2023-11-06 14:58:312024-01-09 15:33:262
pagelinkSpecial:MobileDiff/294087.defa2024-02-13 18:25:502024-02-13 18:25:501
pagelinkSpecial:MobileDiff/295207.defa2023-12-22 18:53:042023-12-22 18:53:453
pagelinkSpecial:MobileDiff/297319.defa2023-10-30 19:23:022023-10-30 19:23:021
pagelinkSpecial:MobileDiff/303507.defa2024-01-30 18:54:422024-02-04 09:51:184
pagelinkSpecial:MobileDiff/304910.defa2024-02-16 11:32:262024-02-16 11:33:552
pagelinkSpecial:MobileDiff/309217.defa2024-01-18 17:25:532024-01-18 17:25:531
pagelinkSpecial:MobileOptions.default~2023-05-09 10:27:082024-12-05 14:26:04672
pagelinkSpecial:Nearby.default~default2023-05-24 21:37:002025-07-06 10:15:50298
pagelinkSpecial:Notifications.default~2024-05-29 23:29:392024-05-30 01:09:222
pagelinkSpecial:PasswordReset.default~2023-07-04 18:28:182024-12-01 16:34:3898
pagelinkSpecial:PrefixIndex.default~de2023-10-20 20:30:472024-08-10 19:02:542
pagelinkSpecial:Search.default~default2023-06-11 10:40:062024-12-05 03:49:18232
pagelinkSpecial:SearchTranslations.def2023-10-12 07:47:082024-10-18 21:15:518
pagelinkSpecial:Tags.default~default~d2024-09-08 05:22:132024-09-08 05:22:131
pagelinkSpecial:Translate.default~defa2023-07-26 12:30:512025-09-16 16:55:54356
pagelinkSpecial:Translate/Legal:Digita2024-10-04 19:29:412024-10-04 19:29:411
pagelinkSpecial:Translate/page-Templat2024-08-07 21:14:112024-11-07 14:20:364
pagelinkSpecial:Translate/Special:Crea2024-06-15 23:09:342024-09-20 21:21:152
pagelinkSpecial:Translate/Translations2024-08-04 10:58:402024-08-04 22:35:412
pagelinkSpecial:Translations.default~d2023-10-12 03:39:022024-11-24 03:06:0610
pagelinkSpecial:UserLogin.default~defa2023-05-24 17:53:092024-12-06 03:21:25390
pagelinkSpecial:WantedCategories.defau2024-02-24 09:40:102024-02-24 09:40:101
pagelinkSpecial:WhatLinksHere.default~2023-11-16 05:27:332024-10-02 22:04:593
pagelinkSpecial%3ANearby..rcc2024-04-10 10:03:152024-04-10 10:03:151

Proposed sql

UPDATE civicrm_contribution_tracking LEFT JOIN civicrm_value_1_gift_data_7 ON entity_id = contribution_id SET mailing_identifier = 'sp73493245', channel = 'Email' WHERE utm_source = 'sp73493245';
UPDATE civicrm_contribution_tracking LEFT JOIN civicrm_value_1_gift_data_7 ON entity_id = contribution_id SET mailing_identifier = 'sp74626175', channel = 'Email' WHERE utm_source = 'sp74626175';
UPDATE civicrm_contribution_tracking LEFT JOIN civicrm_value_1_gift_data_7 ON entity_id = contribution_id SET mailing_identifier = 'sp74626242', channel = 'Email' WHERE utm_source = 'sp74626242';
UPDATE civicrm_contribution_tracking LEFT JOIN civicrm_value_1_gift_data_7 ON entity_id = contribution_id SET mailing_identifier = 'sp74626249, channel = 'Email'' WHERE utm_source = 'sp74626249';
gerritbot added a comment.Nov 4 2025, 4:01 AM

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

[wikimedia/fundraising/crm@master] Update channel for banners

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

gerritbot added a comment.Nov 4 2025, 4:01 AM

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

[wikimedia/fundraising/crm@master] Minor code tidy up

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

Eileenmcnaughton added a comment.Nov 4 2025, 4:26 AM

@JMando also there is this for the bulk of them - Recurring Gift, Email, sidebar, direct mail are mostly already done

https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/1201331

Eileenmcnaughton added a comment.EditedNov 4 2025, 5:11 AM

Another question for you @JMando

I checked to see if the case is consistently upper on mg campaigns and other than the very old 'mgemail' I found 2 in-use exceptions mg.wikipedia.org and mg.wiktionary.org - do you know if these are Major Gift appeals?

SELECT DISTINCT utm_campaign, MAX(tracking_date), count(*) FROM civicrm_contribution_tracking WHERE utm_campaign LIKE 'MG%' GROUP BY utm_campaign;
+-----------------------------------------+---------------------+----------+

utm_campaignMAX(tracking_date)count(*)

...

mg.wikipedia.org2025-10-23 14:25:3414
mg.wiktionary.org2025-11-03 13:09:1252

....

Eileenmcnaughton added a comment.Nov 4 2025, 6:07 AM

ANother question - should our is_major_gift column be NO for subsequent recurrings - given the channel + major gifts thing...

Eileenmcnaughton added a comment.EditedNov 4 2025, 7:42 AM

OK & last for tonight - I've put up a patch that I think interprets your sql - I'm hoping to do the updates this week.... @JMando

https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/1193939

gerritbot added a comment.Nov 4 2025, 6:11 PM

Change #1201332 merged by Ejegg:

[wikimedia/fundraising/crm@master] Minor code tidy up

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

gerritbot added a comment.Nov 4 2025, 7:49 PM

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

[wikimedia/fundraising/crm@master] Fix some missing mailing identifiers

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

gerritbot added a comment.Nov 4 2025, 8:08 PM

Change #1201770 merged by Eileen:

[wikimedia/fundraising/crm@master] Fix some missing mailing identifiers

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

gerritbot added a comment.Nov 4 2025, 8:33 PM

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

[wikimedia/fundraising/crm@master] Switch to Direct Mail (space)

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

gerritbot added a comment.Nov 4 2025, 9:15 PM

Change #1201785 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Switch to Direct Mail (space)

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

AKanji-WMF edited projects, added Fundraising Sprint: Vixen; removed Fundraising Sprint: UTM_key lime pie.Nov 4 2025, 9:21 PM
AKanji-WMF moved this task from Backlog to Doing on the Fundraising Sprint: Vixen board.
AKanji-WMF added a project: fr-current-sprint.Nov 4 2025, 9:24 PM
gerritbot added a comment.Nov 4 2025, 9:33 PM

Change #1201331 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Update channel for banners

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

gerritbot added a comment.Nov 4 2025, 9:54 PM

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

[wikimedia/fundraising/crm@master] Remove extraneous comma

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

gerritbot added a comment.Nov 4 2025, 9:57 PM

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

[wikimedia/fundraising/crm@master] Fix Direct_Mail to Direct Mail

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

gerritbot added a comment.Nov 4 2025, 10:14 PM

Change #1201808 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove extraneous comma

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

gerritbot added a comment.Nov 4 2025, 10:15 PM

Change #1201809 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Fix Direct_Mail to Direct Mail

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

Eileenmcnaughton added a comment.Nov 4 2025, 11:17 PM

we spoke & decided

  1. Major gifts is oK to be yes on subsequent - need to consider that in accounts
  2. mg.wikipedia.org & mg.wiktionary.org do not have complete donations - but OK to make Major gifts per @JMando rule
  3. I updated the mailing idenfitiers per proposed sql in https://phabricator.wikimedia.org/T406193#11338570
  4. utm_medium pagelink -> channel = Other Online
gerritbot added a comment.Nov 5 2025, 4:22 AM

Change #1193939 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Set is_major_gift on ingress and backfill prior data

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

gerritbot added a comment.Nov 5 2025, 4:51 AM

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

[wikimedia/fundraising/crm@master] Fix ambiguous table name

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

gerritbot added a comment.Nov 5 2025, 5:09 AM

Change #1201862 merged by Eileen:

[wikimedia/fundraising/crm@master] Fix ambiguous table name

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

JMando added a comment.EditedNov 5 2025, 7:42 PM

Just wanted to clarify, for "Major gifts is oK to be yes on subsequent - need to consider that in accounts" in analytics for donation_type we do not mark the subsequent recurring as a major gift, similar to other donation_types.

Screenshot 2025-11-05 at 1.41.25 PM.png (359×430 px, 24 KB)

So only one time gifts and recurring sign ups fall into the major gift donation type (same for banners, etc.). But I am assuming through the combo of channel and is_major_gift we could still report on it this way?

gerritbot added a comment.Nov 12 2025, 3:49 AM

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

[wikimedia/fundraising/crm@master] Fix a few recurring gifts that got the wrong channel

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

gerritbot added a comment.Nov 12 2025, 4:23 AM

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

[wikimedia/fundraising/crm@master] Insert gift data rows for subsequen recurrings without them

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

gerritbot added a comment.Nov 12 2025, 7:17 PM

Change #1204103 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Fix a few recurring gifts that got the wrong channel

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

gerritbot added a comment.Nov 12 2025, 7:17 PM

Change #1204105 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Insert gift data rows for subsequen recurrings without them

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

gerritbot added a comment.Nov 12 2025, 9:21 PM

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

[wikimedia/fundraising/crm@master] Run recurring upgrade again with UPDATE ON DUPLICATE

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

gerritbot added a comment.Nov 12 2025, 10:08 PM

Change #1204675 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Run recurring upgrade again with UPDATE ON DUPLICATE

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

gerritbot added a comment.Nov 12 2025, 10:53 PM

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

[wikimedia/fundraising/crm@master] Kick start the upgrader

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

gerritbot added a comment.Nov 12 2025, 11:11 PM

Change #1204693 merged by Eileen:

[wikimedia/fundraising/crm@master] Kick start the upgrader

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

gerritbot added a comment.Nov 13 2025, 12:07 AM

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

[wikimedia/fundraising/crm@master] Bump it into action again

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

gerritbot added a comment.Nov 13 2025, 12:30 AM

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

[wikimedia/fundraising/crm@master] Fix the last mis-labelled recurring gifts

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

gerritbot added a comment.Nov 13 2025, 12:41 AM

Change #1204705 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Bump it into action again

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

gerritbot added a comment.Nov 13 2025, 12:52 AM

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

[wikimedia/fundraising/crm@master] Insert rows for channel = Email where mailing_identifier is not null

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

gerritbot added a comment.Nov 13 2025, 1:52 AM

Change #1204708 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Fix the last mis-labelled recurring gifts

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

gerritbot added a comment.Nov 13 2025, 3:40 AM

Change #1204712 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Insert rows for channel = Email where mailing_identifier is not null

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

Eileenmcnaughton moved this task from Doing to Done on the Fundraising Sprint: Vixen board.Nov 13 2025, 6:53 AM

I've moved this to Done just because I'm swapping out the old 'do everything' phabs for the more specific 'tasks still to be done' - I opened a separate one for getting major gifts flagged correctly which I'll look at once all the channels are assigned

Eileenmcnaughton moved this task from Backlog to Done on the FR-WMF-Audit board.Nov 13 2025, 6:54 AM
XenoRyet closed this task as Resolved.Nov 17 2025, 8:35 PM
XenoRyet set Final Story Points to 4.
gerritbot added a comment.Dec 15 2025, 9:14 PM

Change #1195075 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Fix Direct Mail option value

Reason:

otherwise merged

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

Maintenance_bot removed a project: Patch-For-Review.Dec 15 2025, 9:31 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits