Page MenuHomePhabricator

contribution_source triggers failed for 3% of a sample of donations
Open, HighPublic2 Estimated Story Points

Description

Revisit the triggers that update the contribution_source table and look for holes that would make it possible to omit records. Ellery found that 174 of about 6000 donations are not in the contribution_source table,

SELECT *
FROM
        civicrm.civicrm_contribution co,
        drupal.contribution_tracking ct
LEFT JOIN drupal.contribution_source cs
        ON ct.id = cs.contribution_tracking_id
WHERE
        co.id = ct.contribution_id
        AND ts BETWEEN 20150410120000 AND 20150510000000
        AND SUBSTRING_INDEX(ct.utm_source, '.', 1) = 'B15_0410_enUS_dsk_pm_ctl'
        -- Select just rows missing a contribution_source
        AND cs.contribution_tracking_id is NULL

This is used by @ellery and the Online team to run queries and optimize our campaigns.

Event Timeline

awight created this task.May 8 2015, 9:43 PM
awight raised the priority of this task from to Needs Triage.
awight updated the task description. (Show Details)
awight added subscribers: awight, ellery.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 8 2015, 9:43 PM

Looks like it happens when there are too many terms in the utm_source: "B15_0410_enUS_dsk_pm_ctl.no-LP.paypal.no-LP.paypal.no-LP.paypal"

ellery added a comment.EditedAug 26 2015, 6:29 PM

@awight has this issue been resolved? drupal.contribution_source is a very useful table. It would be great if this bug got fixed.

atgo set Security to None.

@ellery: No work has been done. Thanks for the ping, I'd really like to fix this, too!

atgo triaged this task as High priority.Sep 11 2015, 6:34 PM
awight edited a custom field.Sep 15 2015, 5:14 PM
awight renamed this task from contribution_source triggers seem to be failing to contribution_source triggers failed for 3% of a sample of donations.Nov 29 2015, 12:20 AM

As @awight determined above, this does seem to happen when there are more than 3 terms in the utm_source. This can be due to an error like B15_0410_enUS_dsk_pm_ctl.no-LP.paypal.no-LP.paypal.no-LP.paypal but those should be eliminated now by a change in the banners.

However it can also happen when specifying a method plus 'submethod' e.g. B1617_0411_nlNL_ipd_p1_lg_pym_cnt.no-LP.rtbt.rtbt_ideal or B1617_120121_en6C_dsk_p1_lg_scr_lg2.no-LP.obt.bpay. Any thoughts on the best way forward here, should we pass only the submethod in utm_source?

mmodell removed a subscriber: awight.Jun 22 2017, 9:33 PM
EYener added a subscriber: EYener.Aug 14 2020, 7:34 PM

Hi all! I found this task because I am experiencing this same problem for the Netherlands banners. As @Pcoombe indicated in the last comment (back in '17!) this seems to be happening with long modifiers. For example, the B2021_0708_nlNL_dsk_p1_lg_txt_twin1_optIn1.no-LP.rtbt.rtbt_ideal utm_source does not join to drupal.contribution_tracking, while the B2021_0708_enNL_m_p1_lg_txt_twin1_optIn1.no-LP.paypal does.

All of our reporting cubes are relying on this join and pull heavily from drupal.contribution_source, so I would like to +1 a fix!

DStrine added a subscriber: DStrine.

moving this back to somewhere visible.