Just noting that this field was added to Acoustic the maintenance window before last on the expectation we needed to do the field add when we had the window - but how to get the data was not determined.

@JMando do you have any advice here - it looks like we have some data we can interpret in contribution_tracking - ie if mailing_identifier is not null it was a mailing & if banner is not null it is a banner - but beyond that I'm not too clear ....

Hey there, so I would maybe do something like this (contribution tracking fields):

CASE
    WHEN utm_medium IN ('email', 'endowment','MGEventEmail', 'MGAppeal','MGAnnual','MGFE','MGFE-Email')
               AND (LEFT(utm_source, 10) like 'sp%'
               OR LEFT(utm_source, 10) LIKE 'RML%') then 'email'
      WHEN (utm_medium = 'sitenotice'
            OR banner RLIKE '_dow_') then 'banner'
ELSE utm_medium
END

I have used something similar to set up filters to group email and banner more precisely. Then from there I think you could fall back on the utm_medium as it shows portal, app, sidebar, etc. itself.

@JMando so that doesn't feel great as an approach.

It looks to me like the field 'channel' that Nora was working on was intended for this purpose - but we do not seem to have started populating it in any serious way as yet. Perhaps we need to do that! - we would need to both populate on ingress & backfill

select channel, count(*) FROM civicrm_value_1_gift_data_7 GROUP BY channel;

Totally fair.

I was actually just looking at civicrm_value_1_gift_data_7.channel . If that was fully populated, analytics could rely on it and get rid of quite a few messy case statements. The one I shared above is an example, but my least favorite is:

CASE
         /* After 2023-07-01 */
        WHEN c.receive_date >= '2023-07-01' AND gift.campaign in ('Chapter Gift','Chapter') THEN 'Chapter Gifts'
        WHEN c.receive_date >= '2023-07-01' AND c.financial_type_id <> 26 AND c.total_amount >= 10000 THEN 'Major Gifts'
        WHEN c.receive_date >= '2023-07-01' AND c.contact_id IN (35968301,3729480,10988750,27693813) THEN 'Major Gifts' /* 35968301 is a corporate donor that often makes small transfers.
            To prevent small transfers from being tagged as Online gifts, this CID is identified systematically as a MG donor
            Other CIDs are MG-specific donors that usual make large gifts; all located together to easily identify when
            questions arise */
        WHEN c.receive_date >= '2023-07-01' AND c.financial_type_id = 26 AND t.utm_source REGEXP '^sp' THEN 'Endowment Email'
        WHEN c.receive_date >= '2023-07-01' AND c.financial_type_id = 26 AND t.utm_source REGEXP '^B' THEN 'Endowment Banner'
        WHEN c.receive_date >= '2023-07-01' AND c.financial_type_id = 26 THEN 'Endowment'
        WHEN c.receive_date >= '2023-07-01' AND gift.appeal LIKE 'MG%%' THEN 'Major Gifts'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'sitenotice' AND t.utm_campaign REGEXP '_m_' THEN 'Mobile Banner'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'sitenotice' AND t.utm_campaign REGEXP '_dsk_' THEN 'Desktop Banner'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'sitenotice' THEN 'Desktop Banner'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'email' AND t.utm_campaign REGEXP '^M' AND t.utm_campaign NOT LIKE "missedyou%%" THEN 'Major Gifts'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'email'  THEN 'Email'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'portal' AND t.utm_campaign = 'portalBanner' THEN 'Portal'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'portal' THEN 'Other Online Source'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium IN ('WikipediaApp','WikipediaAppFeed') THEN 'WikipediaApp'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium = 'sidebar' THEN 'Sidebar'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium IN ('google','facebook') THEN 'Social'
        WHEN c.receive_date >= '2023-07-01' AND t.utm_medium REGEXP '^MG' THEN 'Major Gifts'
        WHEN c.receive_date >= '2023-07-01' AND c.contribution_recur_id IS NOT NULL AND t.utm_medium IS NULL THEN 'Recurring'
        WHEN c.receive_date >= '2023-07-01' AND gift.campaign NOT IN ('Community Gift','Online Gift') THEN 'Major Gifts'
        WHEN c.receive_date >= '2023-07-01' AND (c.financial_type_id NOT IN (9,26) OR c.check_number IS NOT NULL OR (c.financial_type_id = 9 AND c.payment_instrument_id=5 AND gift.campaign in('Community Gift','Online Gift'))) THEN 'Offline'
        WHEN c.receive_date >= '2023-07-01' AND c.financial_type_id = 9 AND t.utm_medium NOT IN ('sitenotice','email','portal','WikipediaApp','WikipediaAppFeed','sidebar') THEN 'Other Online Source'
        WHEN c.receive_date >= '2023-07-01' AND c.financial_type_id = 9 AND t.utm_medium IS NULL THEN 'Unknown Online Source'
        /* Before 2023-07-01 */
        WHEN c.contact_id IN (35968301,3729480,10988750,27693813) THEN 'Major Gifts' /* 35968301 is a corporate donor that often makes small transfers.
            To prevent small transfers from being tagged as Online gifts, this CID is identified systematically as a MG donor
            Other CIDs are MG-specific donors that usual make large gifts; all located together to easily identify when
            questions arise */
        WHEN gift.appeal LIKE 'MGFAnnual%%' and c.total_amount <1000 THEN 'Major Gifts'
        WHEN t.utm_medium = 'sitenotice' AND t.utm_campaign REGEXP '_m_' AND c.total_amount < 1000 THEN 'Mobile Banner'
        WHEN t.utm_medium = 'sitenotice' AND t.utm_campaign REGEXP '_dsk_' AND c.total_amount < 1000 THEN 'Desktop Banner'
        WHEN t.utm_medium = 'sitenotice' AND c.total_amount < 1000 THEN 'Desktop Banner'
        WHEN t.utm_medium = 'email' AND t.utm_campaign REGEXP '^M' THEN 'Major Gifts'
        WHEN t.utm_medium = 'email' AND c.total_amount < 1000 THEN 'Email'
        WHEN t.utm_medium = 'portal' AND t.utm_campaign = 'portalBanner' AND c.total_amount < 1000 THEN 'Portal'
        WHEN t.utm_medium = 'portal' AND c.total_amount < 1000 THEN 'Other Online Source'
        WHEN t.utm_medium IN ('WikipediaApp','WikipediaAppFeed') AND c.total_amount < 1000 THEN 'WikipediaApp'
        WHEN t.utm_medium = 'sidebar' AND c.total_amount < 1000 THEN 'Sidebar'
        WHEN t.utm_medium IN ('google','facebook') AND c.total_amount < 1000 THEN 'Social'
        WHEN t.utm_medium REGEXP '^MG' THEN 'Major Gifts'
        WHEN c.financial_type_id = 9 AND c.contribution_recur_id IS NOT NULL AND t.utm_medium IS NULL AND c.total_amount < 1000 THEN 'Recurring'
        WHEN c.financial_type_id = 26 AND t.utm_source REGEXP '^sp' THEN 'Endowment Email'
        WHEN c.financial_type_id = 26 AND t.utm_source REGEXP '^B' THEN 'Endowment Banner'
        WHEN c.financial_type_id = 26 THEN 'Endowment'
        WHEN gift.campaign = 'Chapter Gift' THEN 'Chapter Gifts'
        WHEN gift.campaign NOT IN ('Community Gift') THEN 'Major Gifts'
        WHEN (c.financial_type_id NOT IN (9,26) OR c.check_number IS NOT NULL OR (c.financial_type_id = 9 AND c.payment_instrument_id=5 AND gift.campaign='Community Gift')) AND c.total_amount < 1000 THEN 'Offline'
        WHEN c.total_amount >= 1000 THEN 'Major Gifts'
        WHEN c.financial_type_id = 9 AND t.utm_medium NOT IN ('sitenotice','email','portal','WikipediaApp','WikipediaAppFeed','sidebar') AND c.total_amount < 1000 THEN 'Other Online Source'
        WHEN c.financial_type_id = 9 AND t.utm_medium IS NULL AND c.total_amount < 1000 THEN 'Unknown Online Source'
        ELSE 'Unknown'
    END AS donation_type

At the very least, we could condense it if civicrm_value_1_gift_data_7.channel was backfilled and had more "channels."

@JMando what could possibly be not to love about that sql :-)

Re "had more "channels." - have you got a full list?

The list above was something Nora was working on - but I don't know who owns that project now

@Eileenmcnaughton I could probably generate a list that works for reporting, but I am wondering if we should loop in some more people and standardize the business logic around channel before moving forward? Since I also do not know who is owning the project.

@JMando I agree we should figure out who owns this! Maybe @AKanji-WMF can help? But I think it's worth seeing what your list looks like too

Hi @MSuijkerbuijk_WMF - could you please remind me why we decided to create the "both_funds_latest_donation_source" field in Acoustic? (I believe you're the owner of this field addition work).

If my reading of old notes serves me, it was so that we could try to determine Grassroots Plus (GR+) behaviour - e.g. is a supporter email responsive? Is there a period of time when this segment comes in at a higher rate through banners?

Knowing this would help us confirm what channels we want to be able to select from - to support your future segmentation/targeting logic.

Hi all

@AKanji-WMF I'm adding below a few use cases, hope it helps.

• Exclude email donors from second gift asks
• Identify email responsive donors to upgrade them
• Identify banner donors to convert them to email (higher average gifts)
• Identify Direct mail donors

So initially banners, email and direct mail would be the key focus.
But having Planned Giving, DAF, and Workplace Giving will also support future marketing push we have in our roadmap.

Fr-Tech discussed tackling main parts of this, putting aside more challenging edge cases for Q3

Looking at your sql @JMando I see some sources that are not in the channel list & others that are 'subtypes'

Not in the list:
Major Gifts
Endowment
Portal
Other online source
Wikipedia App
Social

Subtypes

Looking at the list I feel like there are 2 concepts - one around our income streams & the other around how the donor actually gives - ie the donor gives as a response to an email - regardless of whether it is endowment, major gifts.

However, portal, wikipedia app, social and other online source feel like giving channels.

I'm inclined to think we should add those 4 options into channel (although with Nora gone I'm not sure who I would confirm with - maybe @MDemosWMF

I suspect separating out the different types of banners would not be good because it would be too easy for users to only select a subset of banner users when trying to target them.

In the short term Mariana will benefit from having information about who donated from banners & emails the most

It would be good to determine these the same way we do for contribution tracking - so I guess the question is whether looking at the contribution tracking table we can say

"All rows with a value in civicrm_contribution_tracking.banner should be banner & all rows with a value in civicrm_contribution_tracking.mailing_identifier should be email'

If the php code we are using to code those 2 fields is accurately identifying banners vs email donations then we could use that to populate channel for those 2 types

Agreed we do not need to have a separate channel for mobile and desktop or endowment and annual fund as we can do that in reporting easily. How are those two fields (civicrm_contribution_tracking.banner and civicrm_contribution_tracking.mailing_identifier ) being populated? I am still using my own set of SQL/Filters to get to email/banner (mostly utm_medium).

@JMando I think at a high level they are treating anything that is not clearly a mailing as a banner - but I can refine that if needed. It happens in the php layer. I think the RML emails might be incorrectly set to being 'banner' looking at the code. Perhaps you could do some checks on the accuracy

Looping in @EMartin as she has asked to know if there are open threads from Nora's work that we aren't sure who is covering. Evelyn do you know who will be taking on any Civi coding work?

@IAckerman-WMF + Ilse for awareness. This is something that will fall to the new CiviCRM role on the data team I expect.

Yes, thanks!

@AKanji-WMF Just checking on this > is this task moving forward or on hold?

Hi @MSuijkerbuijk_WMF - I've added a task in FR-Analytics' backlog to engage on this; once we coordinate with them we can continue work on this.

To check in on expectations, we're not sure this data can be backfilled during December - I can add it to the queue for Q3. Let me know if this poses a problem for any campaign requirements.

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

[wikimedia/fundraising/tools@master] Add gift_data table to our test schema

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

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

[wikimedia/fundraising/tools@master] Export contents of giftData.channel as donation source

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

Change #1102006 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Add gift_data table to our test schema

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

@AKanji-WMF Thanks. Please add me if needed for input.

Change #1102015 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Export contents of giftData.channel as donation source

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

Just noting that we are pushing up the content of 'channel' as the last gift data source now, However, not all gifts have it - the ones Melanie imports and the SMS ones generally do but @JMando & I need to finish this conversation https://phabricator.wikimedia.org/T379700#10342620 to get the banner & email ones populated & to clarify what degree of nuance we want - sadly this is one of those cases where Nora was expected to be playing a vital role in working through it all

Should we postpone this for the CRM role, or is it more urgent than that?

I think that if we can look at this over the offsite it would be best - the minimum requirement to address this if for @JMando & I to spend some time thrashing out the details - which would be good to do over a beer in-person meeting

Hi yall, thank you for the work and brainstorming around this so far! I was wondering what the status is of this task?

Erm - we haven't 'started' on it in a meaningful way - Anil is trying to figure out how to schedule

@Eileenmcnaughton are we waiting on anything from analytics? https://app.asana.com/1/3758245663860/project/1200434588018841/task/1208941372837831?focus=true

We have channel reliably populated in our Database now thanks to the finance reconciliation work - we should look at calling this field channel rather than source for clarity & consistency

Yes that sounds good - Please can you look at implementing that change and getting that criteria active in Acoustic? Thank you
FYI @Lars

@MSuijkerbuijk_WMF It looks like this is already being filled in Acoustic, using the data from channel as expected. I think Eileen implemented it in 2024 in the Acoustic export, but it wasn't being filled until channel was being filled. Now that we have channel in CiviCRM, we have this in Acoustic.

@MSuijkerbuijk_WMF We may want to think about backfilling this data in Acoustic, as it will only be updated for contacts who have been modified since late last year. Depends on how it will be used, can you provide details?

We want to tailor our journeys depending on the channel they last gave. For example, if we know a donor last gave in banners, but it's consecutive, we can send an earlier email to re-engage them in email (higher value) earlier. Or if they gave through social, we can create specific journeys.

What would entail backfilling? Can we do it at least for Active, New and Consecutive donors? Or all? @Lars

@MSuijkerbuijk_WMF We can definitely backfill at least those (we already have all the modified contacts since Nov, which is about 15%). What is the timeline for when you are hoping to make use of this? Ideally we'd be able to combine this backfill with backfilling the calendar year total field that we've discussed for Megan's recurring ask amounts, which would be in May for our next maintenance window, or possibly in July when we update all contacts for the new fiscal.

We can do loop it into other work - we want to do some testing ideally this fiscal year.
Thank you

Backfill to be done at the same time as T414372: Add calculated asks for Acoustic export for efficiency