Import and Bulk Operations Essentials

 Tasks related to adding records to your database, which include importing user lists; bulk operations such as batch status changes, product changes, unsubscribes, subscribes, and deletes; and adding records one at a time are completed in the USER OPERATIONS **module. Prior to working with USER OPERATIONS, it is important to understand basic concepts and rules relating to these operations.

DEFINITIONS

Import-Related Definitions

De-dupe - The ONEcount system checks records in import files against users currently in the ONEcount system. If you select to check for duplicates and a user already exists in the system and is matched, he/she will not be imported again as a new user. When importing, you will have the option to download the list of duplicate users or to proceed with the import and update current demographic information with the information contained in the import file and/or subscribe/renew subscription to a product(s).

Duplicate Records in the File - Based on the field(s) you select to check for duplicates, the number of records in an import file that match records in the ONEcount database.
THIS IS NOT A COUNT OF THE NUMBER OF RECORDS WITHIN THE IMPORT FILE THAT ARE DUPLICATES.
 For example, there is a user with email address of user123@bmail.com in the ONEcount database. There are also 25 users without an email address in the ONEcount database. An import file contains a record with the email user123@bmail.com and one empty email address. The number of duplicate records in the file would be two: one being the user123@bmail.com and the other being the empty email address. (Although there are 25 blank email addresses in ONEcount, there is only one in the file.)

Duplicates Matched in the Database - Based on the field(s) you select to check for duplicates, *t*he number of records in the ONEcount database that match a record(s) in the import file.
Using the same example as above in "Duplicate Records in the File", the number of Duplicates matched in the database would be 26: one being the record with the user123@bmail.com email address, and the 25 records without an email address.

Import File - A file to be imported into the ONEcount system. Import files must be in CSV format with each field separated by a comma and enclosed by double quotes with no spaces between fields

Import Mapping Template - A template for import mapping. If you do regular/frequent imports that use the same mapping of Question and CSV Headers, you can save the mapping into a template.

Mapping - Mapping is used to match the fields in an import file to fields in the ONEcount database so that the data in those fields can be imported from the import file into the ONEcount database.

Pre-Import Summary - Once import mapping is completed and prior to final import, a pre-import summary will show you the import title, description, source code, product(s), total new records to be imported, duplicate records in the file (based on the field you selected to check duplicates by, the number of records in the file that match records ONEcount database), and the number of duplicate records matched in the database (based on the field you selected to check duplicates by, the number of records in the ONEcount database that match records in the import file).

Source Code - Used to track the source of a transaction, ie the source of the user/name in the ONEcount database.* When assigned to a form, a source code can be used as a selection when generating lists and reports, to track what form a user/subscriber completed , to track what list a user came from when importing users, or to show which e-mail campaign a user came from . (A source code tag can be appended to links in a newsletter campaign to override a source code assigned when a form is created, which allows you to identify that a user was directed to a form and came into the system from a link in that campaign.) Source Codes are created using the Source Codes tool in the INVENTORY module.

*For audited publications, source codes that identify the source of user names in accordance with cagetories in Paragraph 3b reports should be created. Forms and imported lists should be assigned the appropriate source code for the source of the name. For example:
 1) You want to identify all Internet Direct Requests from a subscription form, so you would create a source code called, for example, IDRPrintForm and attach it to the subscription form. This source code will be attached to any user record created when a user subscribes using that form. The source codes indicates it was an Internet Direct Request from the form, and when a 3b report is generated those users will be included in the count for internet direct requests.
2) You want to identify all Telecommunications Direct Requests from a telemarketing renewal campaign, so you create source code called, for example, TDR05152104 to identify that renewal list imported on May 15, 2014. When doing the import to update/renew, select that source code and all records imported will have that source attached to their record in ONEcount. So, when generating a 3b report those renewed users will be included in the count for telecommunications direct requests.

If you have a master file including multiple codes for circulation sources, separate the file by source (e.g. a sub-file for each 3b category/source of circulation) and import each sub-file using the appropriate source code created in ONEcount.

Question Header - This is a list of all questions from your forms in the ONEcount database. Question headers are selected for mapping to CSV import file headers when importing.

CSV Header - This is a list of all the headers of fields in your CSV import file. CSV headers are selected for mapping to Question Headers when importing.

Active Question Header - These are the Question Headers selected to map to the Active CSV Headers when importing. Active Question Headers must be matched with Active CSV Headers in order to import.

Active CSV Header - These are the CSV Headers selected to map to the Question Headers when importing. Active CSV Headers must be matched with Active Question Headers in order to import.

Expiration Date - The date a subscription expires. This date is automatically calculated when a user subscribes online based on the transaction date (which is the date of the online subscription). If a subscription transaction is recorded in the system via an import, "Expiration Date" (or something similar) can be a field in the CSV import file. If you do not select this field to be recorded as the expiration date in the ONEcount system, the system will set the expiration date based on the transaction date. For example, a product has a term of one year. If a user subscribes online, their subscription will expire one year from the date of their submittal of the subscription form. If, however, a list of users who didn't subscribe to the product online is being imported, that import file should contain a field with the expiration date based on the actual subscription date and that field should be selected as the expiration date. Otherwise, the system will look at the date of import (transaction date) and set the expiration date as one-year from then. Note that if you select Expiration Date and select multiple products when importing, the value of that field for each row of the import file will be used to update the Expiration Date for all the products selected.

Request Date - The date a request for a subscription, renewal, unsubscribe is made by the subscriber. Requests may be via phone, email, subscription card, or some other form, and a list with these requests may not be imported into ONEcount until a later date. If requests were made at a date earlier than the date of import, "Request Date" (or something similar) should be a field in the CSV import file. If you do not select this field to be recorded as the request date in the ONEcount system, the system will set the request date for all records to the date of the import (transaction date). Note that if you select Request Date and select multiple products when importing, the value of that field for each row of the import file will be used to update the Request Date for all the products selected.

Transaction Date - This is the date a transaction (e.g. subscription, renewal, unsubscribe) is recorded in the ONEcount system, be it via online subscription using a ONEcount form, via import, or via administor action. When the transaction occurs via an online form, the request date and transaction date are the same. If a transaction is recorded in the system via an import, the transaction date may not be the same as the request date. If a transaction occurs as a result of administrator action (such as a batch operation) and a different request date isn't selected, the transaction date is the same as the request date. (See "Request Date" above.)
 

Bulk Operations Definitions

 Bulk Operation - An operation carried out on a batch of ONEcount users. Current operations include status change, product change, unsubscribe, subscribe, and delete.

Status Change - Allows you to change the product status of a batch of subscribers to a particular product. A product status defines that status of a user during the subscription process/term for a product. For example, if subscribesr must be qualified to receive your product for free, during your review of their qualifications the product status applied to the product/term may be "Pending Review"; and once you complete your review, you can change the status of those who do qualify to "Qualified Subscriber" and those who don't to "Not Qualified". Or, if anyone, regardless of qualifications, is eligible to subscribe to a product, upon subscription the status applied may be "Current Subscriber".

Product Statuses are created using the "Product Status" tool in the INVENTORY module. You can create as many statuses as you wish and whatever statuses apply to your publication.

Product Status for each product and transaction (if applied) can be seen in the Subscription History section of the User Detail Page. When generating lists using the List Wizard, you can use Product Status to filter subscribers to a product by a specified product status(es).

Product Change - Performing a batch product change will unsubscribe selected users from one product (Product A) and subscribe them to another (Product B).

Unsubscribe/Kill - A bulk Unsubscribe/Kill will unsubscribe selected users from a specified product. **If performing a bulk unsubscribe, there is no option to select a product status, as they only apply to products a user is subscribed to.

Subscribe - A bulk Subscribe will subscribe selected users to a specified product.

Merge - Combine multiple records into one by selecting the ocid/record to retain and those to merge into that record.

Delete - Selecting Delete will delete selected users from the database.

If using the ONEcount Email Engine, deleting a user subscribed to an email/newsletter list(s) will delete the user in the ONEcount database and remove the user from the list in the Email Engine.
Historical statistics for deleted users (e.g. opens, clicks) will be retained in the database, so when running newsletter statistics those historical actions will remain in the counts. It's important to note, however, that if you click on, e.g. the open count for a particular campaign in newsletter statistics, you will be brought to the List Wizard where the list of users can be saved, downloaded, or filtered further. The total number of users will be displayed in the List Wizard, and this number will be lower than the number displayed in the open count in the Newsletter Analytics detail because in the List Wizard the system only displays counts for Known users. Those users who have been deleted are no longer known to the system, so they won't be included in the List Wizard count.

If using MagnetMail or Silverpop, connected to ONEcount via API, deleting a user subscribed to an email/newsltter list(s) will delete the user in ONEcount and automatically move the user to a suppression list in MagnetMail or Silverpop.

ESSENTIALS

The purpose of importing a file into ONEcount is to subscribe users to (or renew a subscription) a product term(s). Each user is a record, and each record is comprised of fields (e.g. first name, last name, email, street address, etc.)

Note that the product terms are what appear in the list of products in the Import Engine. Because all products have a term(s) and if a product has multiple terms (i.e. a free print subscription and a paid print subscription), each term needs to appear in the "product" list so that a user can be subscribed to that specific term.

When importing to a product with a newsletter-type resource, the resource must be created and attached to a product prior to an import, individual record addition of a record, or user subscription via an online form. ONEcount won't push product subscribers to a newsletter resource created after the user subscribed to a product (either through import, individual record addition, or user action).

CSV Formatting and solutions to issues with saving as .csv in Excel

 ONEcount requires that all files for import be in .csv (Comma Separated Values) format. Each field should be separated by a comma and enclosed by double quotes with no spaces between fields. You can confirm the comma and quote delimiters are in the file by opening it in a text editing program such as Notepad, TextMate, TextWrangler.

In Excel you don't have the option of selecting your preferences for the CSV file format, i.e. to save it with fields separated by commas and double quotes around each value, so you may run into issues with csv formatting when using Excel, particularly with OSX as well as with fields containing leading zeroes (in all operating systems). Excel is known for not saving/exporting to csv format consistently and without error.

If using Excel, there are a few different options to save in .csv format and avoid issues:

1. Although it sounds counterintuitive, if you're using Excel on Mac OS X to open and edit the export .csv file, you'll want to make sure that you save your changes in the Format of "Windows Comma Separated (.csv)". If you save it as a regular .csv file inside Excel on Mac OS X, you may have trouble importing the .csv file. This is because Excel on Mac OS X adds old type of line ending that may be incompatible with the PHP configuration on your server.

2. Download LibreOffice (free, open source) and use LibreOffice Calc to open and save Excel and .csv files. This option virtually guarantees no issues with excel to .csv files, LibreOffice is free, open source, and you can have it downloaded and working in just a few minutes. LibreOffice Calc has an advanced .csv filter that lets you choose separators, formats and encodings. If you can use Excel, you can use LibreOffice. https://www.libreoffice.org/

3. Use this link - https://markinns.com/archive/export-excel-csvs-with-double-quotes.html for directions on how to insert a macro in Excel that will allow you to save your file as a comma-delimited and quote-separated CSV file.

Use caution when saving and/or editing .csv files in Excel/LibreOffice/OpenOffice as their default setting is to drop leading zeroes in numbers, and those leading zeroes may be essential to your data file. Be sure that all numbers with leading zeroes retain them in your import file.

Other Pre-Processing Essentials

> Date fields in your .csv import file can be of any standard format, e.g. 12/31/13; 12/31/2013; December 31, 2013; 12-31-13. Be sure to check date fields to ensure they are in a standard format.

> Prior to importing a .csv file, remove any empty columns.

> When importing, ONEcount form questions will be mapped to .csv file fields, so be sure that forms have “Question Text” for all corresponding fields in your .csv file.

> While ONEcount will import lists with spaces in the file name, to avoid any possible issues it is good practice not to include spaces (e.g. use New_CSV_Import_File rather than New CSV Import File).

> For optimum performance, keep the list size at 100,000 or fewer names. ONEcount will import larger lists, but your company server may have a timeout setting that is shorter than the time it takes to import very large lists.

Single-Select and Multi-Select Type Question Values

 > In "single select” type responses (radio button, select drop-down menu), be sure that the values in your CSV file match exactly the values in your form.

For Example:

• 
  
  • If the Country field value is “US” in your .csv file, but your form has a select-type response value “United States”, you will need to change the .csv file value to “United States” to allow for import; OR
  • If you have assigned numeric values in your Form to a radio button-type response, e.g. Male = 1 and Female = 2, but your import file uses “Male” and “Female”, you will need to change the csv file values to “1” or “2” to allow for import.

Don't change the values in the form; change them in the import file.

> In "multi select" type responses (checkbox list) values are stored in ONEcount as unique numeric values - not as text - so corresponding values in the import file will need to be changed to the numeric values to allow for import. You can find the values by selecting "Edit Form" in Forms then selecting "edit responses" for the question.

For Example:
 If the the possible responses to a check box-type question are MacPro, iPhone, and iTouch, ONEcount will assign unique numeric ID's to them, such as:

• 
  
  • response “MacPro” = value of 253
  • response “iPhone” = value of 254
  • response “iTouch” = value of 255
     Values entered as “MacPro”, “iPhone”, or “iTouch” in the CSV import file will need to be changed to the corresponding numeric values – 253, 254, or 255 – to allow for import.

Multiple responses/values for a check box-type question should all be in one field in your import file, separated by commas with no spaces and enclosed by double quotation marks. For example: “253,254,255” .

Don't change the values in the form; change them in the import file.

Blank Fields and "Null" Value

 If there is a blank field in an import file and the corresponding field in the ONEcount database contains data, that field in the ONEcount database will NOT be written over with a blank, even if the Request Date of the import is more recent than the date the information was entered into the ONEcount database.

If "Null" is inserted into a field in the import file and the corresponding field in the ONEcount database contains data, that field in the ONEcount database WILL BE written over with a blank if the request date of the import is more recent than the date the information was entered into the ONEcount database. If the request date is older than the date the information was entered in the database, the corresponding field will NOT be written over with a blank.

Importing an Expiration Date

The subscription expiration date is automatically calculated when a user subscribes online based on the transaction date (which is the date of the online subscription). If a subscription transaction is recorded in the system via an import, "Expiration Date" (or something similar) should be a field in the CSV import file. If you do not select this field to be recorded as the expiration date in the ONEcount system, the system will set the expiration date based on the transaction date.

For example:
 A product has a term of one year.
If a user subscribes online, their subscription will expire one year from the date of their submittal of the subscription form.
If, however, a list of users who didn't subscribe to the product online is being imported, that import file should contain a field with the expiration date based on the actual subscription date and that field should be selected as the expiration date. Otherwise, the system will look at the date of import (transaction date) and set the expiration date as one-year from then.

Checking Import Files for and Processing Duplicates

If you don't tell the system to check for duplicates when importing, a user on an import list and who is already in the ONEcount database will be added to the database again. This will create a duplicate user with the same demographic information but a different ONEcount ID (OCID) and subscribed to a different product(s). Each user in ONEcount should have only one OCID, so you should ALWAYS check for duplicates when importing a list.

To check for duplicates, check the box for the active question header you want to check for duplicates against.

You can select multiple headers, but be aware that ONEcount uses “AND” logic when doing so. For example, if you select to check email, first name, and last name fields for duplicates, ONEcount will look for records in the ONEcount database that have the same email AND first name AND last name as a record being imported. A record would be a duplicate only if all three fields match.

It is important to be sure that records in ONEcount and the import file contain data for the fields you are checking for duplicates against. If they don't, a record may not be matched as a duplicate. For example, you check for duplicates against email AND last name but the record in ONEcount doesn't include last name and the record in the import list does include last name. The record won't be counted as a duplicate because the email AND last name do not match.

When checking for duplicates, ONEcount is not checking duplicates within the import file but only between the ONEcount database and the file being imported. If your import file contains duplicates within it and no matching records are found in ONEcount, the duplicate records will be imported.

If importing a new list and email is a required field for all records, it is recommended to check for duplicates across email. Note that if a user has a different email in the import list and you check only by email, the record won't be identified as a duplicate. If the emails match (and the records are identified as duplicates) and you wish to import new data via the import file, you will need to select to process duplicates after new records are imported. This will process ALL duplicates.

If you are re-importing a list of existing users, for example to update information, the best field to check against is "User Account Number" (ONEcount ID or ocid) because each user can have only one OCID.
Note that at the current time, you can't name a column in your import file "ocid". If you do, the system will give you an error message telling you to use a variation such as "oc_id". So, if you have a column with ocid's in your import file, title it "oc_id" and map it to "User Account Number" to dedupe against ocid.

The system will alert you if there are records in your import file that match records in the ONEcount database based on the field(s) you select to check for duplicates. A pre-import summary screen will give you the number of new records to be imported, the number of duplicates (if any) in the import file, and the number of duplicates matched in the database.

The number of duplicates in the file is how many records within the import file are already in the ONEcount database based on your selected field(s) to check for duplicates.

The number of duplicates matched in the database is the number of records within the database that match the field(s) you selected to check for duplicates.

The number of duplicates in the file and matched in the database should be the same. If there is a difference in the numbers, this is an indicator that you may want to go back and select an additional field(s) to check for duplicates or download the duplicates for review prior to processing them. (See example below.)

EXAMPLE: If you check for duplicates across email and there is one record with a blank email field in the file being imported and 50 records with blank email fields in the ONEcount database, ONEcount will identify the one record in the import file and the 50 recordsin ONEcount as duplicates (because they all have a blank email which matches the record being imported). The pre-import summary would say: Duplicate records in file = 1 and Duplicate records matched in the database = 50. Remember that the number of duplicates in the file and matched in the database should be the same.

If you proceed with the import, all the new records will be imported, and a post-import summary screen will again indicate Duplicate records in file = 1 and Duplicate records matched in database = 50.

At this point you have an option to process the duplicates or download them for review. The download will show the duplicate records in the import file, so this is the recommended action to take in such a situation, as you will see a blank email field in the import record. (The import window will remain open so you can process the duplicates after reviewing the list. )
If you didn't set a Request Date or the Request Date you selected is newer than the information in the ONEcount database and then you opt to "Process Duplicates", all of the mapped fields for the 50 records with a blank email field in the ONEcount database will be written over with the information from the ONE record with the blank email field in the upload file. 
If there are, say, three records with a blank email field in the import file, then the mapped fields for the 50 records with a blank email field in the ONEcount database will be written over with the information from the last record with a blank email field in the import file.

If processing duplicates, mapping a request date ensures that only data in the ONEcount database that is older than data in the import file will be written over. ONEcount will compare the request date to the date of the most recent information in the database and only write over information that is older than the request date. This is done on a field-by-field basis within a record.

For example:
A list is being imported on 01/07/2013, and an import record is identified as a duplicate.
The mapped request date of the import record is 12/01/2012, and the user's last name in the file is Smith.
The user updated her profile in the ONEcount database by changing her name from Smith to Jones on 12/31/2012.
When duplicates are processed, her last name will NOT be written over, as she changed it AFTER (12/31/2012) the request date of the import record (12/01/2012).
 As of 11/30/2012 her job title in the ONEcount database is "Sales Associate".
 In the import file her job title is "Regional Sales Manager".
When duplicates are processed her job title WILL be written over, as the data as of the request date (12/01/2012) is more recent than the data in the ONEcount database.

If no request date is set when importing, the request date will be the date of the import. If this were the case in the example above, both the user's last name and job title would be written over as the request date (01/07/2013) would be more recent than the dates in the ONEcount database.

Quick Steps to Import a File

1) Pre-process the file following the rules above.
 2) Select "Import" from the User Operations sub-menu.
 3) Click the "Browse" button and locate and select your file.
 4) Click the "Upload" button.
 5) Complete Import Title and Import Description fields.
 6) Select Source Code to be applied to the import.
7) Select product to subscribe imported users to.
 8a) Select an Import Template to use to map ONEcount Question Headers to CSV Headers (optional)
 8b) If not using an import template, select Question Headers and CSV headers and activate them. They will appear in the "Active [ ] Headers" boxes when activated. Be sure that the Question and CSV headers are matched.
 9) Select field(s) to check duplicates by.
 10) Select the field in your import file to use for recording Request Date in the system. If there is no request date field or you wish the request date to be the current date, leave drop-down menu at "Current Date".
 11) Select the field in your import file to use for recording the Expiration Date in the system. If there is no expiration date field or you wish the expiration date to be based on the current date, leave the drop-down menu at "select one."
 12) Click the "Set" button to check and set the mapping.
 13) Click the "Import" button (or if you wish to reset the mapping, click "Reset").
 14) Check the Pre-Import Summary and Sample Mapping to be sure everything is set correctly and ready for final import.
 15) Click "Proceed".
 16a) If there were no duplicates, you will get a screen indicating "Import of New Users Successful".
 16b) If there are duplicates, you will get a screen indicating "Import of New Users Successful" and a summary of the number of duplicates.
 17) At this point, you can download the list of duplicates for review or process the duplicates. If you download the list, the import window will remain open so you can process the duplicates after reviewing the list. When you process duplicates, only information that is more recent than that in the database will be recorded in the database.