Skip to main content

Columns for Email File Uploads/SFTP

While each organization’s needs are unique, we’ve outlined some general guidelines for the fields we tend to find valuable in your email data. If you feel others may be relevant, please include them as well. More data is better than not enough. We are a SOC-2 compliant company and you can view that information here. If you have any questions, please contact us by the ticketing system for a quick response.

We have used the common column/field names for the data below. Your system may use a different column or field name. The list of email contact columns is not comprehensive because it does not include custom fields in your email system. Custom fields can be mapped and utilized in Avid, so include custom fields as needed.

Required Email Contact Columns

These columns are needed to match email data to CRM or other data within Avid. 

  • email - STRING - The individual email address
  • date_created - DATE - the original inception date when available
 

Recommended Email Contact Columns

The following columns are recommended to improve data matching to CRM or other data within Avid.

  • contact_id - STRING - System identifier for this contact 
  • donor_household_account_id - STRING - Household-level CRM ID, if available 
  • donor_individual_account_id - STRING - Individual/contact-level CRM ID, if available 

Optional Email Contact Columns

  • status - STRING - Email status (e.g. sendable, bounced, unsubscribed, etc.)
  • date_last_modified - DATE - Last updated date/timestamp for this record
  • is_marketable - BOOLEAN - Whether the contact is able to receive marketing emails
  • is_imported - BOOLEAN - Was this contact imported?
  • email_source_type - STRING - What type of source did this contact come from?
  • email_source - STRING - What was the source of this email?
  • first_name - STRING - The subscriber's first name
  • last_name - STRING - The subscriber's last name
  • timezone - STRING - The subscriber's timezone
  • original_traffic_source_type - STRING - The source type/category that first brought this signup
  • original_traffic_source - STRING - The source of traffic for the signup
  • original_traffic_subsource - STRING - The sub-soure of the signup
  • original_utm_campaign - STRING - The value of the utm_campaign parameter at time of signup
  • original_utm_medium - STRING - The value of the utm_medium parameter at time of signup
  • original_utm_source - STRING - The value of the utm_source parameter at time of signup
  • original_utm_content - STRING - The value of the utm_content parameter at time of signup
  • original_utm_term - STRING -The value of the utm_term parameter at time of signup
  • date_first_send - DATE - The date of the first email the subscriber received
  • date_last_send - DATE - The date of the most recent email the subscriber received
  • date_first_open - DATE - The date the subscriber first had a tracked open
  • date_last_open - DATE - The date the subscriber most recently had a tracked open
  • date_first_click - DATE - The date the subscriber first had a tracked click
  • date_last_click - DATE - The date the subscriber most recently had a tracked click
  • date_first_reply - DATE - The date the subscriber first replied to a message
  • date_first_bounce - DATE - The date the subscriber first had a tracked bounce/undeliverable
  • date_last_bounce - DATE - The date of the subscriber's most recent bounce/undeliverable
  • date_unsubscribe - DATE - The date the subscriber explicitly opted out
  • date_pause_until - DATE - The date the subscirber is paused until (vacation hold or similar)
  • date_last_activity - DATE - The date the subscriber last had a tracked activity (e.g. open or click)
  • messages_delivered - INTEGER - The total number of messages this subscriber has received
  • messages_bounced - INTEGER - The total number of times this subscriber had bounced/undeliverable mail
  • messages_opened - INTEGER - The number of unique messages the subscriber has opened
  • messages_clicked - INTEGER - The number of unique messages the subscriber has clicked
  • messages_replied - INTEGER - The number of unique messages the subscriber has replied to
  • messages_since_last_engagement - INTEGER - The number of messages sent since the last time this subscriber engaged
  • reason_unsubscribed - STRING - The reason given for the subscriber unsubscribing, if any
  • reason_bounced - STRING - The reason or code of the subscriber's most recent bounced message
  • reason_quarantined - STRING - The reason the subscriber was quarantined
  • phone - STRING - The email subscriber's phone number, if present in system
  • phone_type - STRING - The corresponding phone number type
  • address_city - STRING - The email subscriber's city
  • address_country - STRING - The email subscriber's country
  • address_state_province - STRING - The email subscriber's state/province
  • address_postal_code - STRING - The email subscriber's Zip/postal code
  • address - STRING - The email subscriber's street address
  • address_type - STRING - The email subscriber's street address type (e.g. home, work)
  • ip_geo_timezone - STRING - The auto-detected (IP address localization) timezone
  • ip_geo_country - STRING - The auto-detected (IP address localization) country
  • ip_geo_country_code - STRING - The auto-detected (IP address localization) country code
  • ip_geo_city - STRING - The auto-detected (IP address localization) city name
  • ip_geo_state_province - STRING - The auto-detected (IP address localization) state/province
  • ip_geo_state_code - STRING - The auto-detected (IP address localization) state/province code
  • ip_geo_postal_code - STRING - The auto-detected (IP address localization) Zip/postal code
  • ip_geo_latitude_longitude - STRING - The auto-detected (IP address localization) lat/long pair

Related articles