Import contacts into CampaignMonitor

[jamezpolley]

Import all active users

• [x] First up: need to export from civicrm.

In-built reports don't work. Grab full list of contacts from Civi SQL:

SELECT distinct "1" as "CiviCRM", email.email, contact.display_name 
FROM `civi_production`.`civicrm_contact` as contact, civi_production.civicrm_email as email
WHERE contact.id = email.contact_id 
    and contact.is_opt_out = 0 
    and contact.do_not_email = 0 
    and email.on_hold = 0;

SELECT email.email, MAX(contrib.receive_date) as civicrm_contrib
FROM civicrm_email as email, civicrm_contact as contact, civicrm_contribution as contrib
WHERE contact.id = email.contact_id
    AND contrib.contact_id = contact.id
GROUP BY email.email;

SELECT email.email, MIN(contact.created_date) as civicrm_created, MAX(contact.modified_date) as civicrm_modified
FROM civicrm_email as email, civicrm_contact as contact
WHERE contact.id = email.contact_id
GROUP BY email.email;

• [x] Pull current list from PA

  SELECT email, COUNT(email) as alerts, MAX(last_sent) as PlanningAlerts_lastsent  
  FROM `pa-production`.`alerts` 
  WHERE confirmed = 1 AND unsubscribed = 0
  GROUP BY email;

• [x] Pull current lists from RTK

  SELECT name, email, confirmed_at 
  FROM `tvfy-production`.`users` 
  WHERE confirmed_at is not null;

• [ ] Pull current lists from OA

  SELECT firstname, lastname, email, registrationtime as oa 
  FROM `oa-production`.`users` 
  WHERE confirmed = 1 and deleted = 0;

• [x] Pull current lists from TVFY

  SELECT name, email, confirmed_at as tvfy 
  FROM `tvfy-production`.`users` 
  WHERE confirmed_at is not null;

• [ ] Pull current lists form Stripe if charge.outcome.network_status == "not_sent_to_network -> fraud list, just record email. if charge.outcome.network_status == "approved_by_network" -> record email and max charge.created

• Each import only needs to have email address + names + a field to indicate source -> segmenting

Prepare suppression list

• [ ] Grab the opted-out list from civicrm

  SELECT distinct email.email 
  FROM `civi_production`.`civicrm_contact` as contact, civi_production.civicrm_email as email
  WHERE contact.id = email.contact_id 
  and (contact.is_opt_out = 1 OR contact.do_not_email = 1 OR email.on_hold = 1);

• [ ] Add all emails in the removal list to the suppression list

  • Removing them this way will make sure they never get emailed again, even if we import them again later.