The Chorus import process has 3 components to it. The process can import emails addresses into newsletters, add emails to a welcome journey, and sends out a double opt-in message to add phone numbers to HBEATS. The Automation for this process is in Community Outreach Business Unit.
The Chorus import process starts with team Krystal exporting a Chorus registration.
UPMC goes to the Chorus backend to get a list of registrants for a specific event. They can export at the earliest as when the event is over.
The UPMC team will then match the fields with the following file tab delimited Excel file.
The following is a list of rules for configuring the import file:
- The Email field and the Opt_in field are the only 2 fields that are required in order for the file to be processed. The Opt_in field needs to have anything with words “I Agree” in the column. For example, “<b>I Agree</b>” will work. Opt_in will prevent any of the following steps from being processed
- If the list in the imported file has HealthBeat_Opt in field with a case-insensitive value of TRUE, the email addresses will get imported into the HealthBeat Newsletter
- The Newsletter_1 and Newsletter_2 are used if UPMC wants to add the emails to additional newsletters. The list of selectable newsletters is below.
- Lastly Registration Event, Phone, and First Name are required for adding people to the SMS HBEATS Welcome Opt-in Journey
The following is a list of Newsletter that can be added to Newsletter_1 and Newsletter_2 columns
BodyChangers
Brain Health
Cancer Care
CHP eNews
Community Outreach
COVID-19 Updates
CRS
Family Health
General Physician Education
Health and Wellness
Heart Health
Hillman
LGBTQIA+ Health
Living Donor Transplant Monthly Newsletter
Living-Donor Liver Transplant Newsletter
Men’s HealthBeats
Mental Health
New Here
Ortho
Pediatric Health
Pediatric Insights
Pinnacle COVID-19 Updates
Pinnacle eNews and Events
Pinnacle Healthier You
Pregnancy and Childbirth
South Hills Newsletter
Sports Medicine
Transplant Services
UPMC Children’s Hospital Physician Relations Program
UPMC Digital Marketing News
UPMC Nutrition
UPMC Passavant
UPMC Policy Matters
UPMC Urgent Care and Carefinder
Wellness Corner
Women’s HealthBeats
Here are all Fields in the template file
Registrant PIN,Registrant Date,First Name,Last Name,Email,Phone,Zip Code,”In the last 6 months, have you seen a UPMC provider?”,Registration Event,Service Line from Domo,Opt_in,HealthBeat_Opt in,Newsletter_1,Newsletter_2
The Import Process
The automation is called “Chorus_Import_List – IMPORT” and lives in the Community Outreach business unit. The automation runs when a file is placed. There are 11 steps in the automation.
- Chorus_Import_List – IMPORT to Holding V3: imports the file called Chorus_Import_List.txt into the data extension Shared Data Extensions>Community Outreach>Chorus Call>Chorus_Import_List_HOLDING
- Get Active HB emails: Get the current List of HB emails
- Chorus_Import_List_HOLDING – to HB: Appends a list of new HB emails where opt_in contains “i agree” AND healthbeat_opt_in = true. ‘chorus_import’ = category, registration_event = subcategory
- Chorus_Import_Welcome_Journey – Import: adds emails to the Data Extension Chorus_Import_Welcome_Journey
- Chorus_Import_List_HOLDING – to Consumer Master: Updates their profile data in Consumer_Master_DE
- Chorus_Import_List_HOLDING – to Causeway Export: updates the PII data in Causeway Export Holding SDE
- Chorus_SMS_OptIn – import – Adds the phone_number data into PRE_Chorus_SMS_OPT_IN Data Extension
- PRE_Chorus_SMS_OPT_IN_Import: Imports the phone numbers into a PreSMSoptin for HBEATS in Mobile Connect
- Chorus add to Newsletter 1 and 2: is a script that added emails addresses to additional newsletters
- INSERT – Chorus_Import_List_Processed: Moves the processed records into the Processed data extension Chorus_Import_List_Processed
- Chorus_Import_List – bring into safehouse: moves the Chorus_Import_List.txt into the safehouse
Download the Pre-Registration File:
1. The Chorus Call Webcast Event Summary will be forwarded by a UPMC team member
2. In the email, click on the “View Pre-Registration List” link to open the pre-registration excel file. This file will have all event/user information.
- Event name can be copied from this forwarded email
Upload to UPMC Basecamp:
1. Rename the file to “Chorus_Import_List DATE” and add it to the UPMC BC folder located: https://3.basecamp.com/3766011/buckets/14176071/vaults/3666917855
- Example File Name: Chorus_Import_List 051221″
2. Open the file “Chorus Call Event Tracker.xlsx” located: https://3.basecamp.com/3766011/buckets/14176071/uploads/3764143717
- Add the Event information to this file
- If the service line is unknown reach out to Brittany to verify
- Save this file and replace the existing event tracker with the updated excel doc
Format Data File Before Uploading to FTP
1. Copy event data from “Chorus_Import_List DATE” to the excel template “Chorus_Import_List.xls”. Data fields should include the following column headers:
- Registrant PIN
- Registrant Date
- First Name
- Last Name
- Phone
- Zip Code
- In the last 6 months, have you seen a UPMC provider?
- Registration Event
- Service Line from Domo
- See list of DOMO service line names for exact naming structure
- Opt_in
- This field denotes an opt-in for marketing communications
- HealthBeat_Opt in
- value = TRUE
- Newsletter_1
- If newsletter is unknown reach out to Brittany to verify
- Newsletter_2
- If newsletter is unknown reach out to Brittany to verify
2. Save this file as a text delimited file with the file name “Chorus_Import_List“
Upload Data File to FTP
1. The Chorus Call data files as uploaded to the Community Outreach BU FTP.
- Host, Username, Password for FTP provided by request
- Add file to “Import” folder
- Import will start once file is dropped in the Import folder
2. A transactional triggered import notification email will be sent to confirm upload was successful or failed.
- If import has failed contact Mike Yates
Domo Service Line Names
Bariatrics
Behavioral and Mental Health Services
Brand
CHP
CRS
Cancer
Care Connect
CareFinder
Concussion
Critical Intensive Care
Dental
Dermatology
Diabetes
Digestive Disorders Center
ENT
Emergency Medicine
Endocrinology
Enterprises
Esophageal Lung Surgery Institute
Eye
Gastroenterology
General Internal Medicine
General Surgery
HVI
HealthBeat
Healthy Lifestyles
Home Health
Hospice
Hospitalist
ITTC
Imaging
Infectious Diseases
Inside UPMC
Integrative Medicine
Kidney
LDLT
Media
Medical Reputation
Mercy Burn
Neurology
Neurosurgery
Newborn
Nutrition
Ortho
Outpatient Center
Outreach
Pain Management
Palliative Institute
Pathology
Patient Blood Medicine
Pharmacy
Plastic Surgery
Podiatry
Poison
Primary Care
Psychiatry
Pulmonology
Regenerative Medicine
Rehab
Rheumatology
Robotic Surgery
Senior Services
Sleep Medicine
Sports Medicine
Stroke
Talent Acquisition
Thyroid
Transplant
Trauma
Urgent Care
Urology
Women’s Health
Wound