@Eileenmcnaughton I am able to compare this column to analytic's donation type now in the data lake. See quick summary here: https://docs.google.com/spreadsheets/d/1GVJSFuBB76bAA1mCf2xoTh58fcG9iu_mW33ZGqoh_Ws/edit?gid=0#gid=0

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.

@Eileenmcnaughton Is it because WikipediaApp is capitalized in in the column but not the code? Not sure if medium here is still case sensitive?

@jgleeson thank you! Just confirmed everything looks good in the database from my end.

Hey there! Confirming I am good to be owner and have 2FA set up. We also accept the outlined risks with private repos and that name works for me. Question for @Jgreen and @Dwisehaupt before we move forward. Do we anticipate this change impacting srv/analytics or process-control? Anything that needs to be done there before we start using gitlab?

@Dzahn That's right! Let me know if i can provide any more details.

@IAckerman-WMF @Dzahn I believe it is wikimedia/fundraising/analytics, Link in gerrit here: https://gerrit.wikimedia.org/r/q/project:wikimedia/fundraising/analytics

Linked this task to the gerrit PR.

WIP here: https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/analytics/+/1122264

Confirming these look good on my side now. Thank you!

@Eileenmcnaughton gotcha! Thank you!

Analytics to continue with current in model solution and explore hard coded CSV in future.

@Arnis.dizdari @Eileenmcnaughton could we also fix this this for cancel_date:

@Ejegg another issue would be for holidays (assuming the same issue that markets are closed on holidays).

Note the cube fix will only apply to the email stats by send dashboard. For it to be fixed elsewhere, the columns need to be updated in contributions_tracking. CC @Cstone.

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

Hey there, have not had a chance to look yet. Will try to this week.

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

@Eileenmcnaughton I am exporting two files for Faircom each month. A transactional file (donations from the last month for US donors) as well as a contacts data table (a table with contact_id and other information requested by them for all US donors). This is just all US contacts/donations and not specific to who they are targeting necessarily. They are targeting a subset based on some criteria/what addresses they could confirm (not 100% sure on that though).

Totally fair.

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

@Eileenmcnaughton I have it shown in some views in some dashboards, and will look at them for some analysis when necessary. But I do not have any automation that pivots off of them.

This should be fine so long as none of the column names have changed. And I think let's do a backfill for the last year (or whatever the acoustic limit is).

@Dwisehaupt Same access as me should be correct (civicrm, drupal, all the analytics and analytics dev schemas). And same permissions as me as well (so read and write for the analytics schemas).

Awesome! I see them now! I added it to the teams dashboard and asked that they QA it. Will comment here if anything looks off from the acoustic UI.

@Cstone This should be fine for reporting, so long as

@AKanji-WMF The clicks columns are still not populating. clicked_total and clicked_unique in civicrm_mailing_stats.

Do we need to backfill for the time we were not getting data in civicrm_mailing_provider_data, civicrm_mailing, or civicrm_mailing_stats?

Just for more context:

Closing this as it is quite old now.

@Ejegg task we chatted about.

I just finished qa'ing and all the donors I checked (a few from each status and segment) were correct:

donor_segment_id
100 - checked and passed
200 - checked and passed
400 - checked and passed
500 - checked and passed
600 - checked and passed
900 - checked and passed
1000 - checked and passed

@Ejegg if this refers to the Blackbaud reports, it would be the same logic that determines new vs repeat in both their report and and in the query I provided in that task you linked. Blackbaud works on a data set I pull, clean, and provide to them.

Just checking in here. Let me know if anymore info is needed.

Did another round of QA.

@Eileenmcnaughton just wanted to check in here and see if the changes had happened and the data was ready to be QA'd again? @Ejegg mentioned the process may need to be re-ran.

@MSuijkerbuijk_WMF so sp73859020 and sp73859018 had donation asks/links and they both shared the same utm_campaign name (C2324_ThankYou-Recurring)?

@MSuijkerbuijk_WMF @RLopez-WMF do each of these individual emails ('sp73859018','sp73859020','sp73863609','sp73863614') have a different utm_campaign? If so, I can fake the mailing_identifier on the donations and bring them into email stats.

If it helps, for payment_method name I do:

@Eileenmcnaughton
See below for some possible errors I found. Note some of these could come down to what dates the donation history is being compared to (today, this month, this fiscal year, etc):

@Eileenmcnaughton I'll make a task for myself to get those into superset!

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?

Hey all, not sure this is fixed. I checked the query I provided in T351651 and still see them marked refund without a corresponding original charge:

@MDemosWMF I think that works. Could you add me to the invite?

Yeah, I think realized gifts do come into normal reporting cubes but I am not sure I am labeling them in such a way that surfaces them in views. @MDemosWMF could we meet and discuss?

Note I see paypal and adyen gateways in that query's output.

So here are the positive refunds I see since the start of this fiscal year:

It looks like this is happening for sp73528716 and sp73532353 as well (no clicks in pgehres.donatewiki_counts). Could we investigate if this is the same issue? @NNgu-WMF could you comment the links from the send as Noah did for the previous ones?

Preferably all changes would only go back as far as July 1, 2023 so as to not require anymore reporting code changes.

Currently I derive whether a recurring donation (gift/contribution/etc.) is a sign up or further recurring donation after the sign-up in my reporting cubes/queries using functions like:

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

@Ejegg That would be great! Thank you!

@Ejegg I think so long as I can see that an upgrade happened, the amount added, and currency then that should be enough. However is easiest for you to store, I can work with.

I just re-ran the provided queries and these are still showing low clicks compared to the donations.

That code looks correct to me. I do not see any data in the table, but I do see the new columns in there. Once there is data to look at, I can do a more thorough QA, but nothing is jumping out as wrong to me.

@Eileenmcnaughton are there any gifts currently imported with this new process in the database? I could do a normal data pull with our usual queries and make sure they look right for reporting.