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 raised the priority of this task from to Needs Triage.
awight updated the task description. (Show Details)
awight added subscribers: awight, ellery.

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"

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

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

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?

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 subscribed.

moving this back to somewhere visible.

@Pcoombe Anything we need to do around this in advance of the upcoming NL campaign?

The Online team results scripts don't use the contribution_source table (they just pull directly from contribution_tracking), so this isn't a big problem for me. However it sounds like the analytics tools rely on it. Adding @JMando

We are mostly using civicrm.civicrm_contribution_tracking these days, so I am not worried about it.