Data axle import addresses
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Eileenmcnaughton
	Feb 12 2024, 8:37 PM

Description

Nora & Melanie & I discussed this but Nora & Melanie wanted to give some more thought to how to deal with these (e.g should we have a custom field on addresses denoting the source / reliability of the data) so creating a phab to track that is still outstanding

Note the queries I'm using to get the data from the existing import tables (this is the updated version, based on the cumulate efforts in the comments

Query 1 - same as query 2 except for FROM

FROM civicrm_tmp_d_dflt_cb3073f1f3fbd3546df65be0db883c97

Query 2

SELECT
    _entity_id as contact_id,
    'NOCA_update' as data_source,
    CONCAT( ah_mb_coa_coa_effective_move_date_68, '01')  as update_date,
    if (ah_mb_coa_primary_number_is_a_box_84 = 'Y',
        CONCAT_WS(' ',
                  IF (LENGTH(ah_mb_coa_pre_direction_71), ah_mb_coa_pre_direction_71, NULL),
                  IF (LENGTH(ah_mb_coa_primary_name_72), ah_mb_coa_primary_name_72, NULL),
                  IF (LENGTH(ah_mb_coa_primary_number_70), ah_mb_coa_primary_number_70, NULL),
                  IF (LENGTH(ah_mb_coa_street_suffix_73), ah_mb_coa_street_suffix_73, NULL),
                  IF (LENGTH(ah_mb_coa_post_direction_74), ah_mb_coa_post_direction_74, NULL)
        )
        ,
        CONCAT_WS(' ',
                  IF (LENGTH(ah_mb_coa_primary_number_70), ah_mb_coa_primary_number_70, NULL),
                  IF (LENGTH(ah_mb_coa_pre_direction_71), ah_mb_coa_pre_direction_71, NULL),
                  CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 1), 2))),
                  IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 2)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 1)),
                     CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 2), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 2), ' ', -1), 2))),
                     NULL),
                  IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 3)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 2)),
                     CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 3), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 3), ' ', -1), 2))),
                     NULL),
                  IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 4)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 3)),
                     CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 4), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 4), ' ', -1), 2))),
                     NULL),
                  IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 5)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 4)),
                     CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 5), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_primary_name_72, ' ', 5), ' ', -1), 2))),
                     NULL),
                  IF (LENGTH(ah_mb_coa_street_suffix_73),
                      CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 1), 2)))
                      , NULL),
                  IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 2)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 1)),
                     CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 2), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 2), ' ', -1), 2))),
                     NULL),
                  IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 3)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 2)),
                     CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 3), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 3), ' ', -1), 2))),
                     NULL),
                  IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 4)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 3)),
                     CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 4), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_street_suffix_73, ' ', 4), ' ', -1), 2))),
                     NULL),
                  IF (LENGTH(ah_mb_coa_post_direction_74), ah_mb_coa_post_direction_74, NULL)

        )
    ) as street_address,

    CONCAT_WS(' ',
              IF (LENGTH(ah_mb_coa_unit_type_75),
                  CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 1), 2)))
              , NULL),
              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 2)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 1)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 2), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 2), ' ', -1), 2))),
                 NULL),

              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 3)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 2)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 3), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 3), ' ', -1), 2))),
                 NULL),
              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 4)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 3)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 4), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 4), ' ', -1), 2))),
                 NULL),
              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 5)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 4)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 5), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_unit_type_75, ' ', 5), ' ', -1), 2))),
                 NULL),
              IF (LENGTH(ah_mb_coa_unit_number_76), ah_mb_coa_unit_number_76



              , NULL)
    ) as supplemental_address_1,

    CONCAT_WS(' ',
              CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 1), 2))),
              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 2)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 1)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 2), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 2), ' ', -1), 2))),
                 NULL),
              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 3)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 2)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 3), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 3), ' ', -1), 2))),
                 NULL),
              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 4)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 3)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 4), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 4), ' ', -1), 2))),
                 NULL),
              IF(LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 5)) > LENGTH(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 4)),
                 CONCAT(UPPER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 5), ' ', -1), 1, 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(ah_mb_coa_city_name_77, ' ', 5), ' ', -1), 2))),
                 NULL)
    )
        as city,
    ah_mb_coa_state_abbreviation_78 as state_province,
    ah_mb_coa_zip_code_79 as postal_code,
    ah_mb_coa_zip_addon_80 as postal_code_suffix,
    'United States' as country
FROM civicrm_tmp_d_dflt_422d456c84c29150e615ba3c33566152
         INNER JOIN civicrm_contact c ON c.id = _entity_id
WHERE ah_mb_coa_coa_effective_move_date_68 != ''
  AND ah_mb_coa_primary_name_72 != ''
;

Details

Subject	Repo	Branch	Lines +/-
Rename noca address source option to ncoa	wikimedia/fundraising/crm	master	+20 -11
Fix mishandling of > & < when importing from sql	wikimedia/fundraising/crm	master	+5 -1
Add handling to keep old address when a new NCOA address comes in	wikimedia/fundraising/crm	master	+253 -0
Superficial clean up in Test class	wikimedia/fundraising/crm	master	+36 -51
Add new address custom fields	wikimedia/fundraising/crm	master	+44 -2

Customize query in gerrit

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Resolved		Eileenmcnaughton	T341902 Data Axle Project - Re-import to Civi
		Resolved		Eileenmcnaughton	T357345 Data axle import addresses

Event Timeline

Eileenmcnaughton created this task.Feb 12 2024, 8:37 PM

AKanji-WMF moved this task from Triage to Next on the Fundraising-Backlog board.Feb 12 2024, 9:25 PM

AKanji-WMF moved this task from Next to Sprint +1 on the Fundraising-Backlog board.Feb 13 2024, 9:25 PM

XenoRyet moved this task from Sprint +1 to Current Sprint on the Fundraising-Backlog board.Mar 12 2024, 7:09 PM

XenoRyet added a project: Fundraising Sprint: didAnyoneTryThis().Mar 12 2024, 7:19 PM

XenoRyet added a project: Fundraising Sprint: explain($LikeImFive).Mar 12 2024, 7:26 PM

Eileenmcnaughton moved this task from Backlog to Doing on the Fundraising Sprint: explain($LikeImFive) board.Mar 13 2024, 7:57 PM

Custom field ideas for addresses

Source:

- NOCA update
- Staff supplied
- iWave
- Donor supplied

Updated date (no required)

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

[wikimedia/fundraising/crm@master] Add new address custom fields

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

gerritbot added a project: Patch-For-Review.Mar 19 2024, 1:18 AM

Patch to add the new custom fields is ready for review

AKanji-WMF edited projects, added Fundraising Sprint: fridayFridayGottaGetDownOnFriday($fun,$fun,$fun); removed Fundraising Sprint: explain($LikeImFive).Mar 26 2024, 8:51 PM

Change #1012457 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add new address custom fields

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

Maintenance_bot removed a project: Patch-For-Review.Mar 28 2024, 1:30 AM

AKanji-WMF edited projects, added Fundraising Sprint: gone(); removed Fundraising Sprint: fridayFridayGottaGetDownOnFriday($fun,$fun,$fun).Apr 9 2024, 8:09 PM

AKanji-WMF edited projects, added Fundraising Sprint: hammertime($touch_this=false); removed Fundraising Sprint: gone().Apr 23 2024, 8:59 PM

@MDemosWMF @NNichols - let's chat about this - probably best if we tee up a hangout

I took a look at the data from the first file & https://civicrm.frdev.wikimedia.org/civicrm/contact/view?reset=1&cid=235 looks like a contact with a real update whereas https://civicrm.frdev.wikimedia.org/civicrm/contact/view?reset=1&cid=260 does not - it's probably easier to share screens & look.

Tech note - these are the queries I'm working with

 select count(*) FROM civicrm_tmp_d_dflt_422d456c84c29150e615ba3c33566152
    -> INNER JOIN civicrm_contact c ON c.id = _entity_id
    -> WHERE street_address_3 <> ah_mb_mailing_address_local_address_line__131
    -> LIMIT 5;
+----------+
| count(*) |
+----------+
|   244125 |

SELECT
_entity_id as contact_id,
'dataaxle' as data_source,
'2023-07-31' as update_date,
first_name_1 as first_name,
last_name_2 as last_name,
list__address__street_address_3 as street_address,
list__address__city_4 as city,
list__address__state_province_5 as state,
list__address__postal_code_6 as postal_code,
ah_mb_zip_state_abbreviation_18 as state_abbreviation,
ah_mb_zip_primary_number_10 as postal_code_suffix,
ah_mb_zip_pre_direction_11 as pre_directional,
ah_mb_zip_primary_name_12,
ah_mb_zip_street_suffix_13,
ah_mb_zip_post_direction_14,
ah_mb_zip_unit_type_15,
ah_mb_zip_unit_number_16,
ah_mb_zip_city_name_17,

ah_mb_zip_zip_code_19,
ah_mb_zip_zip_code_20,
ah_mb_zip_advanced_bar_code_and_check_digit_21,
ah_mb_zip_carrier_code_22,
ah_mb_zip_zip_match_level_23,
ah_mb_zip_primary_number_is_a_box_24,
ah_mb_zip_zip_code_status_25,
ah_mb_zip_city_name_has_changed_26,
ah_mb_zip_line_of_travel_information_27,
ah_mb_zip_lot_sortation_number_28,
ah_mb_zip_state_code_29,
ah_mb_zip_county_code_30,
ah_mb_zip_lacs_indicator_31,
ah_mb_zip_urbanization_code_for_puerto_rico_32,
ah_mb_zip_unit_return_code_from_finalist_33,
ah_mb_zip_filler_34,
ah_mb_zip_vendor_source_35,
ah_mb_zip_city_type_indicator_36,
ah_mb_zip_address_type_indicator_37,
ah_mb_zip_footnotes_footnote_aa_38,
ah_mb_zip_footnotes_footnote_a_39,
ah_mb_zip_footnotes_footnote_a_40,
ah_mb_zip_footnotes_footnote_a_41,
ah_mb_zip_footnotes___expansion_42,
ah_mb_zip_footnotes_footnote_d_43,
ah_mb_zip_footnotes_footnote_e_44,
ah_mb_zip_footnotes_footnote_f_45,
ah_mb_zip_footnotes___expansion_46,
ah_mb_zip_footnotes_footnote_h_47,
ah_mb_zip_footnotes___expansion_48,
ah_mb_zip_footnotes_footnote_j_49,
ah_mb_zip_footnotes_footnote_k_51,
ah_mb_zip_footnotes_footnote_k_52,
ah_mb_zip_footnotes_footnote_l_53,
ah_mb_zip_footnotes_footnote_m_54,
ah_mb_zip_footnotes_footnote_m_55,
ah_mb_zip_footnotes_footnote_n_56,
ah_mb_zip_footnotes_footnote_n_57,
ah_mb_zip_footnotes_footnote_p_58,
ah_mb_zip_footnotes_footnote_p_59,
ah_mb_zip_footnotes_footnote_q_60,
ah_mb_zip_footnotes_footnote_q_61,
ah_mb_zip_footnotes_footnote_m_62,
ah_mb_zip_footnotes_footnote_m_63,
ah_mb_zip_footnotes___expansion_64,
ah_mb_zip_footnotes_ews_65,
ah_mb_coa_match_level_66,
ah_mb_coa_coa_move_type_67,
ah_mb_coa_coa_effective_move_date_68,
ah_mb_coa_delivery_code_69,
ah_mb_coa_primary_number_70,
ah_mb_coa_pre_direction_71,
ah_mb_coa_primary_name_72,
ah_mb_coa_street_suffix_73,
ah_mb_coa_post_direction_74,
ah_mb_coa_unit_type_75,
ah_mb_coa_unit_number_76,
ah_mb_coa_city_name_77,
ah_mb_coa_state_abbreviation_78,
ah_mb_coa_zip_code_79,
ah_mb_coa_zip_addon_80,
ah_mb_coa_delivery_point_and_check_digit_81,
ah_mb_coa_carrier_route_code_82,
ah_mb_coa_zip_match_level_83,
ah_mb_coa_primary_number_is_a_box_84,
ah_mb_coa_urbanization_code_85,
ah_mb_coa_record_type_86,
ah_mb_coa_multi_source_match_87,
ah_mb_coa_reserved_88,
ah_mb_coa_individual_match_logic_required_89,
ah_mb_coa_ncoalink_return_code_90,
ah_mb_coa___expansion_91,
ah_mb_mailing_address_address_source_code_128,
ah_mb_mailing_address_address_status_delivery_code_129,
ah_mb_mailing_address_pander_code_130,
ah_mb_mailing_address_local_address_line__131,
ah_mb_mailing_address_unit_information_line_132,
ah_mb_mailing_address_secondary_address_line_133,
ah_mb_mailing_address_long_city_name_134,
ah_mb_mailing_address_short_city_name_135,
ah_mb_mailing_address_state_136,
ah_mb_mailing_address_zip_code_137,
ah_mb_mailing_address_zip_four_138,
ah_mb_mailing_address___expansion_139,
ah_mb_mailing_address_mailability_score_140,
ah_mb_mailing_address___expansion_141,
ah_mb_mailing_address_military_zip_code_142,
ah_mb_mailing_address_opac_match_indicator_143,
ah_mb_mailing_address_ndi_affirmed_apt_indicator_144,
ah_mb_mailing_address_secondary_address_indicator_145,
ah_mb_mailing_address_state_code_146,
ah_mb_mailing_address_county_code_147,
ah_mb_mailing_address_long_city_indicator_148,
ah_mb_mailing_address_carrier_route_code_149,
ah_mb_mailing_address_line_of_travel_information_150,
ah_mb_mailing_address_lot_sortation_number_151,
ah_mb_mailing_address_prestige_city_name_152,
ah_mb_mailing_address_zip_addon_delivery_point_153
FROM civicrm_tmp_d_dflt_cb3073f1f3fbd3546df65be0db883c97
INNER JOIN civicrm_contact c ON c.id = _entity_id

WHERE c.id BETWEEN 200 AND 300

SELECT
_entity_id as contact_id,
'dataaxle' as data_source,
'2023-07-31' as update_date,
first_name_1 as first_name,
last_name_2 as last_name,
street_address_3 as street_address,
city_4 as city,
state_5 as state,
zip_6 as postal_code,
ah_mb_zip_state_abbreviation_18 as state_abbreviation,
ah_mb_zip_primary_number_10 as postal_code_suffix,
ah_mb_zip_pre_direction_11 as pre_directional,
ah_mb_zip_primary_name_12,
ah_mb_zip_street_suffix_13,
ah_mb_zip_post_direction_14,
ah_mb_zip_unit_type_15,
ah_mb_zip_unit_number_16,
ah_mb_zip_city_name_17,
ah_mb_zip_zip_code_19,
ah_mb_zip_zip_code_20,
ah_mb_zip_advanced_bar_code_and_check_digit_21,
ah_mb_zip_carrier_code_22,
ah_mb_zip_zip_match_level_23,
ah_mb_zip_primary_number_is_a_box_24,
ah_mb_zip_zip_code_status_25,
ah_mb_zip_city_name_has_changed_26,
ah_mb_zip_line_of_travel_information_27,
ah_mb_zip_lot_sortation_number_28,
ah_mb_zip_state_code_29,
ah_mb_zip_county_code_30,
ah_mb_zip_lacs_indicator_31,
ah_mb_zip_urbanization_code_for_puerto_rico_32,
ah_mb_zip_unit_return_code_from_finalist_33,
ah_mb_zip_filler_34,
ah_mb_zip_vendor_source_35,
ah_mb_zip_city_type_indicator_36,
ah_mb_zip_address_type_indicator_37,
ah_mb_zip_footnotes_footnote_aa_38,
ah_mb_zip_footnotes_footnote_a_39,
ah_mb_zip_footnotes_footnote_a_40,
ah_mb_zip_footnotes_footnote_a_41,
ah_mb_zip_footnotes___expansion_42,
ah_mb_zip_footnotes_footnote_d_43,
ah_mb_zip_footnotes_footnote_e_44,
ah_mb_zip_footnotes_footnote_f_45,
ah_mb_zip_footnotes___expansion_46,
ah_mb_zip_footnotes_footnote_h_47,
ah_mb_zip_footnotes___expansion_48,
ah_mb_zip_footnotes_footnote_j_49,
ah_mb_zip_footnotes_footnote_k_51,
ah_mb_zip_footnotes_footnote_k_52,
ah_mb_zip_footnotes_footnote_l_53,
ah_mb_zip_footnotes_footnote_m_54,
ah_mb_zip_footnotes_footnote_m_55,
ah_mb_zip_footnotes_footnote_n_56,
ah_mb_zip_footnotes_footnote_n_57,
ah_mb_zip_footnotes_footnote_p_58,
ah_mb_zip_footnotes_footnote_p_59,
ah_mb_zip_footnotes_footnote_q_60,
ah_mb_zip_footnotes_footnote_q_61,
ah_mb_zip_footnotes_footnote_m_62,
ah_mb_zip_footnotes_footnote_m_63,
ah_mb_zip_footnotes___expansion_64,
ah_mb_zip_footnotes_ews_65,
ah_mb_coa_match_level_66,
ah_mb_coa_coa_move_type_67,
ah_mb_coa_coa_effective_move_date_68,
ah_mb_coa_delivery_code_69,
ah_mb_coa_primary_number_70,
ah_mb_coa_pre_direction_71,
ah_mb_coa_primary_name_72,
ah_mb_coa_street_suffix_73,
ah_mb_coa_post_direction_74,
ah_mb_coa_unit_type_75,
ah_mb_coa_unit_number_76,
ah_mb_coa_city_name_77,
ah_mb_coa_state_abbreviation_78,
ah_mb_coa_zip_code_79,
ah_mb_coa_zip_addon_80,
ah_mb_coa_delivery_point_and_check_digit_81,
ah_mb_coa_carrier_route_code_82,
ah_mb_coa_zip_match_level_83,
ah_mb_coa_primary_number_is_a_box_84,
ah_mb_coa_urbanization_code_85,
ah_mb_coa_record_type_86,
ah_mb_coa_multi_source_match_87,
ah_mb_coa_reserved_88,
ah_mb_coa_individual_match_logic_required_89,
ah_mb_coa_ncoalink_return_code_90,
ah_mb_coa___expansion_91,
ah_mb_mailing_address_address_source_code_128,
ah_mb_mailing_address_address_status_delivery_code_129,
ah_mb_mailing_address_pander_code_130,
ah_mb_mailing_address_local_address_line__131,
ah_mb_mailing_address_unit_information_line_132,
ah_mb_mailing_address_secondary_address_line_133,
ah_mb_mailing_address_long_city_name_134,
ah_mb_mailing_address_short_city_name_135,
ah_mb_mailing_address_state_136,
ah_mb_mailing_address_zip_code_137,
ah_mb_mailing_address_zip_four_138,
ah_mb_mailing_address___expansion_139,
ah_mb_mailing_address_mailability_score_140,
ah_mb_mailing_address___expansion_141,
ah_mb_mailing_address_military_zip_code_142,
ah_mb_mailing_address_opac_match_indicator_143,
ah_mb_mailing_address_ndi_affirmed_apt_indicator_144,
ah_mb_mailing_address_secondary_address_indicator_145,
ah_mb_mailing_address_state_code_146,
ah_mb_mailing_address_county_code_147,
ah_mb_mailing_address_long_city_indicator_148,
ah_mb_mailing_address_carrier_route_code_149,
ah_mb_mailing_address_line_of_travel_information_150,
ah_mb_mailing_address_lot_sortation_number_151,
ah_mb_mailing_address_prestige_city_name_152,
ah_mb_mailing_address_zip_addon_delivery_point_153
FROM civicrm_tmp_d_dflt_422d456c84c29150e615ba3c33566152
INNER JOIN civicrm_contact c ON c.id = _entity_id
LIMIT 5

;

Eileenmcnaughton moved this task from Backlog to Doing on the Fundraising Sprint: hammertime($touch_this=false) board.Wed, May 1, 1:38 AM

AKanji-WMF edited projects, added Fundraising Sprint: ick(); removed Fundraising Sprint: hammertime($touch_this=false).Tue, May 7, 8:02 PM

Eileenmcnaughton moved this task from Backlog to Doing on the Fundraising Sprint: ick() board.Tue, May 7, 8:09 PM

We discussed & we will only bring in the coa address & only if

ah_mb_coa_coa_effective_move_date_68 is not empty.

We will import to primary but will rename the existing addresses to old 2023 first

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

[wikimedia/fundraising/crm@master] Superficial clean up in Test class

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

gerritbot added a project: Patch-For-Review.Wed, May 15, 11:17 PM

Updated query 1

SELECT
  _entity_id as contact_id,
'NOCA_update' as data_source,
CONCAT( ah_mb_coa_coa_effective_move_date_68, '01')  as update_date,
  CONCAT_WS(' ',
    IF (LENGTH(ah_mb_coa_primary_number_70), ah_mb_coa_primary_number_70, NULL),
    IF (LENGTH(ah_mb_coa_pre_direction_71), ah_mb_coa_pre_direction_71, NULL),
    IF (LENGTH(ah_mb_coa_primary_name_72), ah_mb_coa_primary_name_72, NULL),
    IF (LENGTH(ah_mb_coa_street_suffix_73), ah_mb_coa_street_suffix_73, NULL),
    IF (LENGTH(ah_mb_coa_post_direction_74), ah_mb_coa_post_direction_74, NULL)
  ) as street_address,
  CONCAT_WS(' ',
    IF (LENGTH(ah_mb_coa_unit_type_75), ah_mb_coa_unit_type_75, NULL),
    IF (LENGTH(ah_mb_coa_unit_number_76), ah_mb_coa_unit_number_76, NULL)
  ) as supplemental_address_1,
   ah_mb_coa_city_name_77 as city,
   ah_mb_coa_state_abbreviation_78 as state_province,
   ah_mb_coa_zip_code_79 as postal_code,
   ah_mb_coa_zip_addon_80 as postal_code_suffix,
 'United States' as country
FROM civicrm_tmp_d_dflt_cb3073f1f3fbd3546df65be0db883c97
INNER JOIN civicrm_contact c ON c.id = _entity_id

WHERE ah_mb_coa_coa_effective_move_date_68 != ''
AND ah_mb_coa_primary_name_7 != '';

Query2

SELECT
    _entity_id as contact_id,
    'NOCA_update' as data_source,
    CONCAT( ah_mb_coa_coa_effective_move_date_68, '01')  as update_date,
    CONCAT_WS(' ',
              IF (LENGTH(ah_mb_coa_primary_number_70), ah_mb_coa_primary_number_70, NULL),
              IF (LENGTH(ah_mb_coa_pre_direction_71), ah_mb_coa_pre_direction_71, NULL),
              IF (LENGTH(ah_mb_coa_primary_name_72), ah_mb_coa_primary_name_72, NULL),
              IF (LENGTH(ah_mb_coa_street_suffix_73), ah_mb_coa_street_suffix_73, NULL),
              IF (LENGTH(ah_mb_coa_post_direction_74), ah_mb_coa_post_direction_74, NULL)
    ) as street_address,
    CONCAT_WS(' ',
              IF (LENGTH(ah_mb_coa_unit_type_75), ah_mb_coa_unit_type_75, NULL),
              IF (LENGTH(ah_mb_coa_unit_number_76), ah_mb_coa_unit_number_76, NULL)
    ) as supplemental_address_1,
    ah_mb_coa_city_name_77 as city,
    ah_mb_coa_state_abbreviation_78 as state_province,
    ah_mb_coa_zip_code_79 as postal_code,
    ah_mb_coa_zip_addon_80 as postal_code_suffix,
    'United States' as country
FROM civicrm_tmp_d_dflt_422d456c84c29150e615ba3c33566152
INNER JOIN civicrm_contact c ON c.id = _entity_id
WHERE ah_mb_coa_coa_effective_move_date_68 != '';
AND ah_mb_coa_primary_name_7 != ''

& screenshot to show usage

{F53422241}
{F53422727}

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

[wikimedia/fundraising/crm@master] Add handling to keep old address when a new NCOA address comes in

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

@MDemosWMF @NNichols I have put some code onto staging to test out the handling we talked about for keeping the old address & have run the import of the contacts in the first file

https://civicrm.frdev.wikimedia.org/civicrm/group/search?force=1&context=smog&gid=1953

If this looks good I can look to run on production

Hmm there is the odd one where the NCOA address has LESS data - eg https://civicrm.frdev.wikimedia.org/civicrm/contact/view?reset=1&cid=11887 - I can exclude any with no street address

Eileenmcnaughton moved this task from Doing to Ready for Review on the Fundraising Sprint: ick() board.Thu, May 16, 4:07 AM

@Eileenmcnaughton I noticed that too, let's do that so we are not updating addresses with blank street addresses. Also, is there a way to not have the addresses in all caps? It doesn't match our formatting when we use addresses for mailings. Thanks!

@MDemosWMF it's probably quite a chunk of mucking around to change the capitalisation - why did they give it us that way? It seems odd if that is not the expected mailing usage

I'm not sure, possibly USPS provided it that way? There is a formula called 'Proper' in excel that can capitalise first letter of each word and lowercase for the rest, but you would have to re-import. Or do you know of a way we could easily clean it up after import in Civi?

I can have a go at doing it in the sql query

@MDemosWMF - should city also be mixed case (Seattle) rather than upper (SEATTLE)

Yes, thanks!

@MDemosWMF @NNichols we created the data source option value as NOCA - but I *think* it should be *NCOA* - ie national change of address - can you confirm?