Importing Data

Overview

Imports are one way to add data to your ActionKit database. Most data is added to your database from a user submitting on an action page. You can also add data using the APIs.

Imports are used to record an action or donation from outside ActionKit or to add or update user information. For example, you can:

  • Add new users.
  • Update or add new contact information for users.
  • Create and update custom user fields.
  • Associate users with a tag.
  • Add users to one of your mailing lists.
  • Bulk unsubscribe users from a list, or from all lists.
  • Set the language field for a list of users.
  • Import actions taken outside ActionKit (like a Change.org petition or an offline activity).
  • Create events within an Event Campaign for attendee recruitment.
  • Import donations or product orders.
  • Import the profile for a recurring donation created outside ActionKit.

You can't update action or donation data through an import. You can update some action data using the REST API.

How Imports Work

To import data, first create a CSV or TSV file, following specific rules for creating an import file. Your file can include both new and existing users. Then create or select an import page and upload your file. Read the how-to for details.

Imports create actions - there is no way to import without creating actions, even if you're just updating information for existing users.

Each time you upload a file, a row is added to core_action for each row in your file. The action is assigned to the user_id corresponding to the email (or akid or user_id) in each row.

Most imports are processed through the Import page type. Events are the only exception -- you can upload individual events to Event Create pages and upload individual event signups to Event Signup pages.

Import actions are much like other actions. For example, you can target a mailing to users who took action on an import page. Import actions count as actions in most built-in reports.

You can specify source and other action related data. For each new email in your file, a new user is created and the user_created field in core_action is set to 1. See the New User, imported report for a list of new users imported in the last week.

As with other actions, new information submitted overwrites existing data for fields in core_user, core_phone and core_userfield, but only if there is new data. You cannot overwrite existing data with an empty field. For example, if you include a zip column, the value in your file for a specific user will replace the user's current zip unless that column is empty.

Finally, by default each action subscribes the user to the list you select when creating the page, unless they're already on that list. Unlike with other page types, you can change this for import pages using the Subscriptions setting. It's important to set this intentionally before you upload your file to avoid subscribing users who have asked not to be emailed.

Importing Actions

At a minimum, you just need a file with a column that identifies the user to record an action on an import page. For example, if you're recording actions taken by users on one of your Change.org pages, you could create a file with just one column titled email.

You can save additional action-related info by including the following headers in your file:

  • source - Each import action has a source (as do all actions). Include a source column to save the value for each row to the source field in the core_action table. For new users, the source will also be saved to the core_user table.

    You can also set a default source for the page when you create it. The default applies to all actions imported through the page, unless you've got a different value for one or all rows in a source column in your file.

    For actions that don't have a value in a source column or a default page source, we'll assign a source of import.

  • mailing_id - If the action was prompted by an ActionKit mailing, you may also include a mailing_id column. The values are saved to the mailing_id field in the core_action table and the action source is set to mailing.

    If the user was never sent the mailing entered for a row, the import of that row will fail.

  • created_at - Sets the created_at date in the core_action table to the value in the row. For new users, the created_at date is also used as the user's creation date.

    For new or existing users who are newly subscribed to a list by your upload, the created_at date is also saved to core_subscription and core_subscriptionhistory to indicate when the user joined the list. Core_action.updated_at cannot be set. It shows the time of your import; this is also the default value used if you don't include a created_at column.

Importing Donations

You can import donations made outside of ActionKit, including donations made by check.

Each donation is recorded in the core_order table, and a placeholder row is added to the core_transaction table with the account field set to import.

Donations are a type of action so a row is also added to core_action and you can include values for any of the action-related data fields.

You must include a field for:

  • donation_amount - (Required) Amounts must be positive.

You can include:

  • donation_import_id - Used to identify a unique donation per payment account. Generate a unique identifier for each donation you import to avoid accidental duplication - you cannot import two donations with the same import_id within a given account. This column may contain any character data, up to 32 characters in width.

    We display a warning when we skip a row in an upload because it has a duplicate donation_import_id.

  • created_at - Use any of the supported date formats to set the created_at timestamp in core_order, core_transaction, and core_action. If you don't include this column, or it's empty for a given row, ActionKit uses the import timestamp.

  • donation_date - Use any of the supported date formats to set the created_at timestamp in core_order and core_transaction only. Core_action is set to the time of the import.

  • donation_card_num_last_four - The last 4 digits of the credit card number used.

  • donation_exp_date - The credit card expiration date in MMYY format.

  • donation_currency - Include a three-letter ISO code for the currency of the donation. You can import donations in currencies other than the ones you have payment accounts for, as long as the three-letter currency code is one known to ActionKit. Conversions into approximate US dollars will be done using ActionKit's exchange rate estimate at upload time, regardless of the actual date of the donation.

Product Orders

Product orders are a type of donation. The rules above for donations apply. To import sales of products, you must first create the product from the Pages Tab.

The following fields are required:

  • donation_product_ID - (Required) Where ID is replace by the ID of the product. For example, if you were importing sales of t-shirts and the ID of the t-shirt product was 75, the column name would be donation_product_75.

    Values for this column can be either a quantity number (ex. "10") or a pair of numbers giving quantity and total paid separated by a pipe (ex. "10|50.00").

    If the price for your product in ActionKit is accurate then you can use just a quantity and the amount paid will be calculated for you. However, if the price doesn't include everything (i.e., if the total also includes shipping charges), then use the compound format above and include the actual total amount paid by the user.

  • donation_amount - (Required) The value must be at least as much as the total of all product columns.

    The value can be more; that's how ActionKit stores tips on product pages. For example, if you have product orders for $5.00 and $10.00, donation_amount must be at least $15.00. You must include this column even if it equals the total for products.

Candidate Contributions

Just as with products, you must first add the candidate from the Pages Tab. Then your file must include:

  • donation_candidate_ID - (Required) Where ID is replaced by the ID of the candidate (e.g., donation_candidate_20 for candidate ID 20). Enter the amount of money donated to the candidate in this column (e.g., "9.99").
  • donation_amount - (Required) The value must be at least as much as the total of all product columns. The value can be more; that's how ActionKit stores tips on product pages. For example, if you have product orders for $5.00 and $10.00, donation_amount must be at least $15.00. You must include this column even if it equals the total for candidates.

Recurring Donation Profiles

You can import recurring donation profiles. You cannot import individual payments toward a recurring commitment.

Note

This does not create the profile in the payment processor; this is only for profiles that are already set up. You might use this if a third-party application is creating recurring donations and you want to use the data in ActionKit reports and mailing targeting.

In addition to the normal donation import fields, you must include fields for:

  • donation_amount
  • recurring_id - the profile ID used by the payment processor.
  • recurring_payment_account - If you have more than one payment processor configured you must include the name of the account which should be associated with the recurring profile. If you do not include this column or the value does not match one of your payment accounts an error is returned.

You may include:

  • recurring_period - Choices are years, quarters, months, weeks.
  • recurring_first_payment - By default an initial payment is created when you upload your recurring profile. If you do not want to create an initial payment at the time of your upload, put a 0 in this column.
  • recurring_start_date - the date of the first payment on the profile.

Importing User Data

You can include any of the available user fields while importing external actions or create a file that just includes user data to update or append to user records.

The header names below will save information to the core_user table.

  • name - Use this header if you'd like ActionKit to parse the entry into first, middle and last name based on spacing. If the field contains characters with no space the characters are saved as first_name. Or, use these headers to specify any or all of these individual parts of the user name: prefix, first_name, middle_name, last_name, suffix.
  • address1 - User's street address. For example, 123 ABC Street. ActionKit uses the address to find the user's plus4 if possible.
  • address2 - Second line of user's street address, if applicable. For example, Apartment A.
  • city - User's city.
  • state - Two character abbreviation for user state. For example, CA.
  • zip - Use to import user's zip or postal code. Entries with either 5-digits (ex. "10001") or 9-digits with a dash separating the two parts (ex. "10001-1234") are saved to core_user.zip (and core_user.plus4 if appropriate), unless the user's country is not``United States``. If the zip is not the correct number of digits, even if the country is United States, or if the country is not United States, the entry is saved to core_user.postal. Four-digit zip codes will have a zero pre-pended to help work around a common Excel error (i.e. 2905 is treated as 02905).

ActionKit validates the user city and state based on zip code.

  • postal- Use to import user's zip or postal code. If the user's country is "United States" and you enter a valid zip in the postal field, it will be saved to the zip field instead and used for address validation. If the zip is not the correct number of digits the row will error and your data will not be saved. If the country is not "United States", your entry is saved in the postal field; it is not validated.
  • country - User's country. Defaults to "United States". Also, "US" and "USA" are changed to "United States". Through the uploader you can change a user's country without additional address information.
  • region - User's region. This is a standard address field in some countries.

User Language

Languages must be added from the pages tab before they can be used. Read about setting up and using languages.

English is the default language. Set a different language for any user by including a column with the lang header.

ActionKit recognizes:

  • language name (e.g. "English", "Espanol", etc.),
  • ISO code (e.g. "en", "es", "ru", etc.),
  • ISO name (e.g. "Spanish", "Russian", etc.).

To find the 2 digit ISO code and the official name click Language and Languages from the Pages Tab, then click Edit and look at the Language code field. We suggest setting this code for all your languages to take full advantage of ActionKit's language functionality.

You can also change the default language for the page when you create it. The default applies to all users taking an import action through the page, unless you've got a different value for one or all rows in a lang column in your file.

If you don't want this import to affect the language entry for existing users, you must change language from English to ----------- when you create the page.

User Phones

Phone numbers are saved to the core_phone table. Users can have one phone number of any type per source. Uploaded entries have a source of admin. Your entry will overwrite any entry you made previously but not an entry from the user, which has the source user.

  • phone_type - (optional) Specify a type if desired. If you don't enter a type, the default is home. Phone types are:

    TYPE_CHOICES = (('home', 'Home'),
              ('work', 'Work'),
              ('mobile', 'Mobile'),
              ('emergency', 'Emergency'),
              ('home_fax', 'Home Fax'),
              ('work_fax', 'Work Fax'))
    
  • Alternatively you can include the phone type in the header. This is useful if you're only uploading numbers of one type in your file. The accepted phone type headers are: home_phone, work_phone, mobile_phone, emergency_phone, bat_phone.

  • phone - User's phone number. Entry is not validated. ActionKit will save the number with no letters or punctuation to the core_phone.normalized_phone field.

Custom Fields

You can import custom actions fields and custom user fields. You must use the correct syntax or your data will not be saved.

  • Action fields - Your column header must start action_, followed by the field name; an example is action_comment. The field name can only include letters, numbers, and _ (no spaces or other characters). You do not need to create the field first.
  • User fields - Your column header must start user_, followed by the field name; an example is user_occupation. The field name can only include letters, numbers, and _ (no spaces or other characters). You must create the user field from the link on the Users tab first, or leave the auto-create user fields option checked when performing your upload.

Custom user field validation and defaults are not applied to imported values.

Fast upload

The fast custom user-field upload checkbox provides a much faster option for adding or updating custom user fields for existing users. If you do regularly upload custom user fields this can save you a lot of time!

Your file can only contain columns for a user identifier (email, akid, or user_id) and one or more custom user fields. If your file has other columns and you skip them, fast upload still works. If your file has other columns and you don't skip them, ActionKit will default to a regular, slower upload.

If your file contains new users, those rows will fail. You will see an error message on the upload status screen and be able to download a file of all failed rows if you'd like.

Fast upload works by skipping some of the standard upload process. This means that with fast upload, no action is created for any user and subscriptions are not changed. In fact, none of the import options, language settings or tags you set when creating the import page will apply. The user's updated_at timestamp is not modified.

If you want this option to always be in effect for uploads of custom user fields, there is an option in your instance's configuration to apply it automatically.

Tagging Users

You can associate tags with an import page and all the users imported through the page will also be associated with the tag(s).

Note

Tags attach to the page, so users are associated with the currently selected tags only. If you change the tags for a page after some users have taken action, those users and any new users who take action will be associated with the current tag, not the old tag.

Setting Subscription Status

As with other actions, import actions subscribe users to a list by default.

  • Subscribe to list - By default when you create a new import page this is selected along with your default list in the List box. . Users subscribed by upload will have a change_id of 9 in their core_subscriptionhistory entry. The created_at timestamp in the core_subscription and core_subscriptionhistory tables will be set to match created_at for the action that subscribed the user.

You can change this. The choices are:

  • Don't change subscriptions
  • Unsubscribe from list - Select the appropriate name in the List box.
  • Unsubscribe from all lists

ActionKit Admins can also change the default setting for new Import pages from "Subscribe to list" to "Dont' change subscriptions". To do this, go to the CONFIG menu and edit the Uploader setting in the Pages section.

Note

You can resubscribe users who unsubscribed. You might use this if someone accidentally unsubscribed and then asked to be resubscribed. Be sure you only resubscribe those you intend. If you end up mailing a user who asked to be removed from your list this can quickly hurt your email reputation and your deliverability.

You'll receive an error message and the user will not be subscribed if you try to resubscribe users who bounced or marked your email as spam.

If you want to subscribe any new users, but leave other users as is, add a created_at column with a prior date. ActionKit won't resubscribe users who unsubscribed between the created_at date and the date of the import (which is saved in the updated_at column). For example, if you're importing a list of users who took action at an offline event 3 days ago, you'd use the date of the event as created_at.

If there are new email address in your upload file and you do not select the box to subscribe them to a list, the new users will have a subscription status of Never and no row is added to core_subscriptionhistory.

Uploads: How To

See a training video here: http://vimeo.com/18311853

Whether you are importing new users or importing updates to your users, the process is the same.

Configure the Import

1 Create your file for upload using the required formatting and headers. See the relevant section for header names for importing donations, user data, etc.

2 Click Import Users on the Users tab.

If you're making a new import page, enter the title and shortname.

Alternatively, you can use an existing import page. Follow the Click here link in the description text underneath the Import basics title and select the appropriate import page from the listing.

Generally, you'll want to create a new page if you want to change the settings and particularly the tags. You'll want to reuse a page if you want the new actions to show up on an existing page (for example, for targeting a mailing to actiontakers).

3 Confirm the settings described below.

Tags: Select if you want to tag users.

Subscriptions: Consider whether your file includes new and existing users and when they took action if that's applicable. Read about the options for setting subscription status.

Mailing List: This setting will display if you've chosen an option that subscribes users above.

Default Action source: Enter a source in the Default action source box to set a page-level default for this page. This will apply to all actions uploaded through this page unless you include a source column as noted under importing actions.

Include in Reports of Member Actions: Check this if you want actions on this page to be counted as member actions in reports.

This allows you to distinguish between actions generated from users interacting with your pages, versus administrative-type actions such as updating custom user fields.

By default, Import pages are not checked.

Language: Toggle open the Multilingual Options section to select the default language setting for this page. If only English is available, click the green + plus sign to add a new language.

You can set a default for this page, set language by row, or leave language as is for existing users as described under user language.

Multilingual campaign: Toggle open the Multilingual Options section to select a multilingual campaign from the dropdown list and to associate import actions through this page with a campaign. You can also click the green + plus sign to add a new campaign name.

After you've confirmed your settings, click Next: Upload file >>.

Upload Your File

1 Select the checkbox to confirm that these users have not been purchased or borrowed. You cannot email purchased or borrowed emails through ActionKit. This is important for maintaining a good reputation with ISPs and therefore good deliverability.

2 Before uploading, check the upload summary to verify that the actions will be processed as intended. If an Edit link is present then the value can be changed by configuring the page. The summary also lists the minimum required fields for your file; you might confirm that your file has a value for all required fields.

3 Uncheck the Auto-create user fields if you don't want ActionKit to create new custom user fields for any column with a header beginning user_. If you want to add values to an existing user field, uncheck this box to see a warning and have a chance to correct any misnamed user fields.

4 Click Browse to locate your import file then Start Upload. ActionKit displays your progress during the import.

Note

Uploads can take a while! Before doing a large import, try importing a few rows to make sure the format is correct. You can also compress your file using gzip or zip to reduce upload time.

5 Check for errors or warnings.

  • First, ActionKit will check your file format (csv or tsv only, no excel files) and your column headers. You'll be able to correct your headers on the screen directly instead of having to open and edit your file.

  • While your upload is running, you'll see a link next to upload status to stop your import.

  • When your import is done, you'll see a summary of your upload including the number of rows successfully imported, the number of rows that failed and the number that generated a warning.

  • Below the summary, are the details about problem rows. Click on details to see the problem value and the original data from your file.

    Warnings: Warnings tell you that we imported your row but something may be wrong with it. For example, a new US user with zip of "xyzzy" in the file will be created with a blank zip.

    If a row can't be parsed as UTF-8 it's interpreted as Latin-1, which often results in incorrect interpretation of special characters. You can re-import with corrected values or correct the values manually from the individual user record.

    Errors: Rows with errors are not imported. If some rows fail, you can download a csv of the failed rows, making it easier to correct and re-upload only the failed rows.

Import File Requirements

Your import file must:

  • Be formatted as a TSV or CSV;
  • Include a header row with the correct field name(s). If your file contains an unrecognized field name value in the header, you'll see a message and have a chance to correct it.
  • Include a column that identifies the user. The choices are email, user_id or akid. ActionKit will match data to existing users' based on this column.

Note

New users cannot be added to ActionKit without an email address, but you can use a fake email.

  • Saved in the UTF-8 encoding, if it includes accented characters or international names.
  • Specify dates in one of the following formats:
2010-10-28T13:01:59
2010-10-28 13:01:59
2010-10-28 13:01
2010-10-28
12/30/2010 13:01
12/30/2010 13:01:59
12/30/2010
12/30/10 13:01
12/30/10 13:01:59
12/30/10
2010-10-02T13:01:59-0500
2010-10-02 13:01:59-0500
  • Specify times in UTC (to see what time it is now in UTC: http://www.time.gov/timezone.cgi?UTC/s/0). The only exception is the last two formats above, which include a time zone offset ("-0500") and can therefore be in any time zone. Formats without a time (ex. "2010-10-28") have the time "00:00:000" assigned.

Skipping Columns In Your File

If you want ActionKit to ignore a column, pre-pend the field name with skip_column_. For example, skip_column_1, skip_column_2, etc. This can be helpful when you have a large file to upload and removing a column completely would be difficult but the data is not needed in ActionKit.