Page MenuHomePhabricator

Finalise & test field mapping for targetsmart import
Closed, ResolvedPublic2 Estimated Story Points

Description

I'm thinking to create the field mapping through an extension & then @LeanneS & @NNichols can test it through the UI & provide feedback on any changes that are needed and (I have done a test on staging splitting off a small number of fields - as the file is . I think making the same mapping available through the UI &I for our code based import & using the uf_mapping, uf_field storage mechanism for it

Event Timeline

Eileenmcnaughton set the point value for this task to 1.
Eileenmcnaughton changed the point value for this task from 1 to 2.Jul 23 2019, 8:59 PM

@LeanneS there are a couple of columns that I don't see mapped in your image

voterbase_id
tsmart_sample_id
tsmart_full_address (I get this - we import the components instead)
tb.new_mover_flg
xpg.donor_contributes_to_charities
Discretionary Income Amount
party_score_rollup

Also there seems to be a key & a value for the net worth & income fields & some discrepancies on naming vs your pic - I think this is correct & we need to map one of each & ignore the word 'household'

Screen Shot 2019-07-24 at 12.52.41 PM.png (536×2 px, 98 KB)

Here is how I am mapping the fields at the moment - even though this is technically 'code' I don't think it's too hard to read.

$mappingFields = [
  [
    // contact_id
    'name' => 'Contact ID',
  ],
  [
    // first_name
    'name' => 'First Name',
    'column_number' => 1,
  ],
  [
    // last_name
    'name' => 'Last Name',
    'column_number' => 2,
  ],
  [
    // nick_name
    'name' => 'Nick Name',
    'column_number' => 3,
  ],
  [
    // street_address
    'name' => 'Street Address',
    'column_number' => 4,
    'location_type_id' => 'Old 2019',
  ],
  [
    // supplemental_address_1
    'name' => 'Supplemental Address 1',
    'column_number' => 5,
    'location_type_id' => 'Old 2019',
  ],
  [
    // supplemental_address_1
    'name' => 'Supplemental Address 2',
    'column_number' => 6,
    'location_type_id' => 'Old 2019',
  ],
  [
    // city
    'name' => 'City',
    'column_number' => 7,
    'location_type_id' => 'Old 2019',
  ],
  [
    // state_province
    'name' => 'State',
    'column_number' => 8,
    'location_type_id' => 'Old 2019',
  ],
  [
    // postal code
    'name' => 'Postal Code',
    'column_number' => 9,
    'location_type_id' => 'Old 2019',
  ],
  [
    // country
    'name' => 'Country',
    'column_number' => 10,
    'location_type_id' => 'Old 2019',
  ],
  [
    // voterbase_id
    'name' => '- do not import -',
    'column_number' => 11,
  ],
  [
    // tsmart_sample_id
    'name' => '- do not import -',
    'column_number' => 12,
  ],
  [
    // tsmart_full_address
    'name' => '- do not import -',
    'column_number' => 13,
  ],
  [
    // tsmart_city
    'name' => 'City',
    'column_number' => 14,
  ],
  [
    // tsmart_state
    'name' => 'State',
    'column_number' => 15,
  ],
  [
    // tsmart_zip
    'name' => 'Postal Code',
    'column_number' => 16,
  ],
  [
    // tsmart_zip4
    'name' => 'Postal Code Suffix',
    'column_number' => 17,
  ],
  [
    //tb.new_mover_flg
    'name' => '- do not import -',
    'column_number' => 18,
  ],
  [
    // voterbase_dob
    'name' => 'Birth Date',
    'column_number' => 19,
  ],
  [
    // deceased_flag_date_of_death
    'name' => 'Deceased Date',
    'contact_type' => 'Individual',
    'column_number' => 20,
  ],
  [
    // Household Income Range
    'name' => 'Income Range :: Prospect',
    'column_number' => 21,
  ],
  [
    // Household Income Range_key
    'name' => '- do not import -',
    'column_number' => 22,
  ],
  [
    // Household Net Worth
    'name' => 'Estimated Net Worth :: Prospect',
    'column_number' => 23,
  ],
  [
    // Household Net Worth Key
    'name' => '- do not import -',
    'column_number' => 24,
  ],
  [
    //xpg.donor_contributes_to_charities
    'name' => '- do not import -',
    'column_number' => 25,
  ],
  [
    // tb.charitable_contrib_decile
    'name' => 'Charitable Contributions Decile :: Prospect',
    'column_number' => 26,
  ],
  [
    // Discretionary Income Amount
    'name' => '- do not import -',
    'column_number' => 27,
  ],
  [
    // Discretionary Income Decile
    'name' => 'Disc Contributions Decile :: Prospect',
    'column_number' => 28,
  ],
  [
    // Family Composition Code - use next field.
    'name' => '- do not import -',
    'column_number' => 29,
  ],
  [
    // Family Composition.
    'name' => 'Family Composition :: Prospect',
    'column_number' => 30,
  ],
  [
    // vf_party.
    'name' => 'Voter Party :: Prospect',
    'column_number' => 31,
  ],
  [
    //party_score_rollup. (how does this related to vf_party?)
    'name' => '- do not import -',
    'column_number' => 32,
  ],
  [
    // Occupation.
    'name' => 'Occupation :: Prospect',
    'column_number' => 33,
  ],
  [
    // Occupation_key - assume same data as ^^ in diff format
    'name' => '- do not import -',
    'column_number' => 34,
  ],
  [
    // voterbase_gender.
    'name' => 'Gender',
    'column_number' => 35,
  ],
];

I've also attached a 2 line sample data file we can use. The contact IDs are real contact IDs in our database - but they have been created by fr-tech people doing testing so I have hijacked them. Note to major gifts - if you are looking for people to ask for large donations and find me in the data set please don't ask me. However, if you were in fact searching for people to invite to really cool exciting events and my name comes up take the data at face value....

This is great, thanks @Eileenmcnaughton! Yeah they do have some extra/unneeded columns in there (mostly duplicate type fields as you've seen).

The mapping looks good for me. I did notice that tsmart_full_address in your mapping is set to do not import, but should go to Primary address.

Noted on large donation vs. events! :D

@LeanneS the 'full_address' is just street_address+supplemental_address+ supplemental_address_2+city + etc isn't it?

@Eileenmcnaughton No, for some reason, the street address is called that in their file.

Also, do we need an extra "Supplemental Address" added for the Primary address to prevent the old ones from showing?

@LeanneS sigh - the options for the values have changed again

Currently for net works on live

1 => '$20 Million +',
2 => '$10 Million - $19.99 Million',
3 => '$5 Million - $9.99 Million',
4 => '$2 Million - $4.99 Million',
5 => '$1 Million - $1.99 Million',
6 => '$500,000 - $999,999',

The first 2 rows of the import have
$250,000 - $499,999
$750,000 - $999,999

  • I guess we will be overwriting all existing contacts with this data so we should set up all the new values in the DB & replace the existing ones? I don't have a full list of the new ones yet - does targetsmart provide that mapping? I expect it will apply to other fields too...

@Eileenmcnaughton Yes please overwrite the existing net worth field. Some of the ones you listed above were created before the Targetsmart data was being imported. They are not being used consistently at the moment and we'd rather have the up to date TS data instead.

@Eileenmcnaughton Hm that's odd! Those values ($250,000 - $499,999, $750,000 - $999,999) were used in the previous import this spring and are already in the drop down options for the Estimated Net Worth Field. Does that mean that only the old ones are reflected in the db? I see all the new ones I added under the multiple choice options, which should have all the needed mappings.

mepps renamed this task from Finalise & test field mapping to Finalise & test field mapping for targetsmart import.Aug 2 2019, 2:54 PM

Change 530212 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm/civicrm@master] Rekey mapping fields to base zero

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

Change 530212 merged by jenkins-bot:
[wikimedia/fundraising/crm/civicrm@master] Rekey mapping fields to base zero

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

@LeanneS I imported the first 5 rows into this group - civicrm/group/search?force=1&context=smog&gid=669 do you want to check it out?

I note that for some contacts their 'old' address is a subset of their new one - maybe having both at the point of import is fine as it gives us a good view on the data but we could look at a script at the end to remove any addresses that are a subset of the primary address

@Eileenmcnaughton Thanks! I just looked at them and it seems that the street address is missing from the Old 2019 address and all of the prospect fields are missing. Is it set up so that if there is a change in address, it would be fully included? I think we'd want both in there.

@LeanneS I don't see the prospecting fields being missing when I look - cid 601342

Screen Shot 2019-08-16 at 10.30.59 AM.png (336×688 px, 30 KB)

I just fixed the street address - it got 'lost' & re-did the 5 contacts

Ah I see those now! These look good to me.