@LeanneS can you provide some information to help us prioritize this work?

1. this is the appeal landing page:

https://donate.wikimedia.org/w/index.php?title=Special:FundraiserLandingPage&appeal=Appeal-MGAnnual&form-countryspecific=Form-countryspecific-MGAnnual&utm_medium=MGAnnual&utm_source=MGAnnual_2015&utm_campaign=MGAnnual_2015

@CCogdill_WMF can you verify that link is correct?

2. We need donations made on this page to appear in Civi as Direct Mail Appeal: Fundraiser Mailing 2015

Timing: We need this today or tomorrow.

Thanks @CaitVirtue and @LeanneS. I'm hearing that this is high priority... and now see that it's "Unbreak Now!"
Right now, Tech is working on some critical issues for the online campaign.
We'll plan to triage this on Monday and get back to you about complexity. Moving to "high" priority.

If we're going to wait until Monday, we should add in a solution to bulk correct all the donations that are coming in without this designation. Given the volume of records, it will be very time consuming to correct all of them by hand.

@CaitVirtue they are being noted in the database with the utm_campaign (I just checked using mysql), so we probably just need to figure out how to surface this in the UI.

I can help @LeanneS get a list of all the donations from this utm_campaign,
if tech can help us import the data into Civi.

Timing: We need this today or tomorrow.

Geez. Okay, there are complications here. We used to dump utm_campaign into the Appeal (direct_mail_appeal) column, but we had to stop with the recent Civi upgrade because that's a multiple-choice column rather than freeform text.

For the short term, the best workaround is if you create a new option here:
https://civicrm.wikimedia.org/civicrm/admin/options?gid=39&reset=1
which exactly matches the utm_campaign string in your appeals. *However* that must happen before you send out the mailings, so it's probably too late for whatever you're trying to do.

The long-term solution is to create a new free-text custom fields, and we'll go back to storing utm_* data directly into these.

The data is all available by joining with contribution_tracking, where the raw utm_campaign is stored, so a backfill script would be easy to write.

@CaitVirtue does the ease of backfilling address your concerns?

@atgo All I understand in Adam's comments is "a backfill script would be easy to write." Does that mean this will be easy to correct this week? Both the past and future donations on this page?

@CaitVirtue - the donations are being tagged with the Campaign, just not in a way that we're able to surface in civi right now. What that means is that we should be able to correct the records that are wrong pretty easily with a script (yay!).

I'm not 100% confident in how easy it'll be to fix this such that it appears in Civi for new donations, say, by Monday. But knowing that we have the data tracked means that at a minimum we can pull that data for you daily or something until we have the fix out. I'm happy to do that manually with support from tech if that's what's needed.

@Eileenmcnaughton this is top priority to at least have a stop-gap solution in place. Thanks!

Change 258918 had a related patch set uploaded (by Eileen):
CRM-17700 allow contribution get to bypass permissions

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

Change 258919 had a related patch set uploaded (by Eileen):
T121284 fix direct_mail field check

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

Change 258920 had a related patch set uploaded (by Eileen):
Add tests for campaign field check.

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

The scripts transfer the information from the utm_campaign field to the custom field that shows the direct mail. Not all the utm_campaign possibilities are really direct mail campaigns but prior to the upgrade CiviCRM api would accept any data and store it in the custom field. After the upgrade CiviCRM started validating against the options configured here civicrm/admin/options?gid=39&reset=1

At first I though just enabling the options would cause them to start coming in but a minor error in early Nov added a check in the script but used the title rather than the name field, resulting in no values being passed through.

There is a patch for that here https://gerrit.wikimedia.org/r/#/c/258919/

I also added a test, but the test https://gerrit.wikimedia.org/r/#/c/258920/ depends on a CiviCRM fix https://issues.civicrm.org/jira/browse/CRM-17700 | https://gerrit.wikimedia.org/r/#/c/258918/ being merged & then a submodule commit.

Note that once the patch has been applied the correct options need to be configured. There are a lot of options we are currently ignoring and I presume that we want to continue to ignore most of them.

In terms of potentially missing data this query gives a list of contributions since the start of Oct which have a campaign which has not been put into the direct mail appeal field:

SELECT * FROM (SELECT min(ts), utm_campaign, count(*) as c FROM drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution c ON c.id = contribution_id LEFT JOIN civicrm.civicrm_value_1_gift_data_7 v ON v.entity_id = c.id WHERE ts > '20151001' AND c.id IS NOT NULL AND ts IS NOT NULL AND v.appeal IS NULL
GROUP BY utm_campaign ) as m ORDER BY c DESC;

+----------------+--------------------------------+--------+

+----------------+--------------------------------+--------+

+----------------+--------------------------------+--------+

Here is the query & result for those appeals that have been configured

SELECT * FROM (SELECT min(ts), utm_campaign, count(*) as c FROM drupal.contribution_tracking LEFT JOIN civicrm.civicrm_contribution c ON c.id = contribution_id LEFT JOIN civicrm.civicrm_value_1_gift_data_7 v ON v.entity_id = c.id WHERE ts > '20151001' AND c.id IS NOT NULL AND ts IS NOT NULL AND v.appeal IS NULL AND utm_campaign IN ( SELECT value COLLATE utf8_unicode_ci FROM civicrm.civicrm_option_value ov WHERE ov.option_group_id=39 AND is_active = 1 ) GROUP BY utm_campaign ) as m ORDER BY c DESC;

+----------------+----------------+-----+

+----------------+----------------+-----+

+----------------+----------------+-----+
2 rows in set (1 min 37.17 sec)

And assuming we configure the correct mail groups first then we can then run an upgrade script. It would be close to this

UPDATE
drupal.contribution_tracking

LEFT JOIN civicrm.civicrm_contribution c ON c.id = contribution_id
LEFT JOIN civicrm.civicrm_value_1_gift_data_7 v ON v.entity_id = c.id
SET v.appeal = utm_campaign

WHERE
ts > '20151001' AND
c.id IS NOT NULL
AND v.appeal IS NULL
AND utm_campaign IN (

SELECT value COLLATE utf8_unicode_ci FROM civicrm.civicrm_option_value ov WHERE ov.option_group_id=39 AND is_active = 1

)
;

However, that sql involves 2 DBs & I'm not sure if that is outside the scope of a drupal upgrade script to touch 2 dbs in one query like that - since I assume there are mysql user permission issues. I might discuss that with others tomorrow & switch to the other issue for now.

Re longer term - options include always adding the option for the direct mail before doing a new mailing, adding a hook to the option value so that a script like the above runs when an option value is enabled, adding options on-the-fly when data comes in or switching to a text field like awight suggested. Probably worth a conversation as it's mostly about what is most usable for people

Hey @Eileenmcnaughton, does this mean that the team will need to run sql
queries to get reports for this one? We can check in tomorrow if that's
easier.

So, I do have a habit of putting lots of info on tickets :-)

What it means is

1. the team should determine which of the above list of campaigns they want recorded as direct mailing options - & together we can add them to the configured options
2. once the code is reviewed & deployed real-time updates will happen for all the configured options from that point on
3. we need to run a script / query to backfill the missed data. I will check with the others the best way to do that.

We should be able to get that all sorted tomorrow.

Ok, let me confirm my understanding here, mostly for @CaitVirtue and
@CCogdill_WMF

We have these 2 campaigns: "MGAnnual_2015" and "MajorGifts2014". The team
needs to confirm that they want both of these to be possibilities for the
"Direct Mail Appeal" field.

Once that's decided, @Eileenmcnaughton + fr-tech will do some work to
update the old records and make sure the new ones come in labeled
appropriately. The new records fix will likely happen before the old ones
are backfilled.

yep

ok I've been talking to @awight. So the longer list is all the campaigns that are getting donations at all right now, which we haven't historically surfaced in the UI. We could change this, but I think that's out of scope for what @CaitVirtue and @CCogdill_WMF are looking for here. Can you 2 confirm?

I'm staying out of this decision and leaving it to @CaitVirtue. The
categorization of these campaigns doesn't impact my work — I was just
trying to help Leanne find the data she needed as a workaround.

Change 259161 had a related patch set uploaded (by Eileen):
CRM-17691 add the campaign id to selector even though it ? is not used?

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

Change 259185 had a related patch set uploaded (by Eileen):
Add script to fill missing appeal data

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

In discussion with Anne I renamed the option 'MGAnnual_2015' to "Fundraiser Mailing 2015" (the value needs to match utm_campaign - the label can be as desired). There was an existing option with that label but only 3 contributions had been given it. I updated those 3 to have the renamed field (via the UI).

The patch (waiting review) for bringing this data across correctly in future is https://gerrit.wikimedia.org/r/#/c/258919/

I'm still completing the script for filling missing entries but this is in the review column since the above patch needs review

@Eileenmcnaughton - now that I'm thinking about it, can you identify those
3 contributions for @LeanneS to confirm? Thanks

They are still the only 3 contributions with that option!

Haha oh, duh. Thank you :D

NB - I think the update script is OK now - I'm going to rebase it once the other is merged since it was being wierd on me. There wasn't as much data on staging to be updated but I found some evidence of success doing queries -

https://gerrit.wikimedia.org/r/#/c/259185/

Change 258919 merged by jenkins-bot:
Fix direct_mail field check

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

Change 259161 merged by jenkins-bot:
CRM-17691 add the campaign id to selector even though it ? is not used?

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

Clarification: The 3 records are tagged properly, but they did not get tagged that way via the online form. Leanne used them as test cases when she did the most recent Engage import.

A couple more are tagged now - I'll do some mysql checks tomorrow & fix up the issues from CR in the upgrade script

Change 259185 merged by jenkins-bot:
Add script to fill missing appeal data

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

The upgrade script is pending deploy now - @awight - I think it might be up to a 30 sec upgrade query - does that suggest I need to ? stop queues? It potentially locks the contribution_tracking table which I think is more serious than the Civi ones...

Numbers are looking good now - ok to close @CaitVirtue ?

I think so. When I search via Find Contributions for Direct Mail Appeal = Fundraising Mailing 2015, I'm seeing 1294 donations with that tag.

@LeanneS does that seem right to you?

Followup task: it would be great to have access to the contribution_tracking ID from Civi.

Change 258918 merged by jenkins-bot:
(merged into 4.7) CRM-17700 allow contribution get to bypass permissions

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

Change 258920 merged by Eileen:
Add tests for campaign field check.

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

Change 285805 had a related patch set uploaded (by Eileen):
CRM-17691 add the campaign id to selector even though it ? is not used?

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

Change 285816 had a related patch set uploaded (by Eileen):
(merged into 4.7) CRM-17700 allow contribution get to bypass permissions

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

Change 285805 merged by Eileen:
CRM-17691 add the campaign id to selector even though it ? is not used?

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

Change 285816 merged by Eileen:
(merged into 4.7) CRM-17700 allow contribution get to bypass permissions

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