This article describes the technical requirements associated with the bulk import of service request data into SeeClickFix 311 CRM from an external source using a CSV file format.
The bulk issue import process provides a mechanism to load service request data into the SeeClickFix 311 CRM database. The format and processing rules are dependent on the request types, questions, and assignment information defined in SeeClickFix 311 CRM. Accordingly, the import data can't be prepared until the appropriate settings have been configured within SeeClickFix 311 CRM.
This is an add-on product. If you are interested in adding this service to your contract, please contact your Customer Success Manager or Support.
There are different types of bulk imports into SeeClickFix 311 CRM. Our engineering team has developed specific importers for the default export of requests from Accela Public Stuff and Web Central's CRT. Imports from these two systems do not need to follow the requirements listed below. The data should be exported in the original type of CSV export from Accela Public Stuff or Web Central's CRT and sent to the SeeClickFix 311 CRM team without edits.
If the request data is from another system, then the CSV must match the requirements below.
Issue States
The bulk importer currently supports importing issues into the Acknowledged or Closed states. This is controlled by the closed_at column.
Issues that are imported in the Closed state will transition to the Archived state automatically.
Request Types
The import process is based on the request types defined within SeeClickFix 311 CRM.
Any mapping from the old request types to the SeeClickFix 311 CRM request types must be done while preparing the CSV file for import or alternatively by providing a CSV file that defines the mapping and has the following structure.
Column Heading | Required | Description |
|---|---|---|
old_request_type | yes | The title of the old request type associated with this issue |
request_type | yes | The title of the SeeClickFix 311 CRM request type associated with this issue |
Issues can be imported into a disabled SeeClickFix 311 CRM request type. This may be helpful in some cases to retain legacy request type information on imported issues. A disabled request type is not available for reporting new issues but is available for searching, reporting, and other analytics.
Import File
The issue import data must be prepared as one or more CSV files. The first line of each file must contain headers formatted as specified in the Import File Format table that follows.
The table describes the expected columns. Variations to this format will not be accepted by the import mechanism and will require reformatting before the file can be successfully imported.
Import Notes
Reporter: If reporter_email is blank, the Wrenchbot service user associated with the organization will be used as the reporter for the imported issues. If reporter_email is present, it will be used to associate an existing user or a newly created user with the issue as a reporter. Users created during the import can be sent an invitation to activate their SeeClickFix 311 CRM account as a separate task after the issues have been imported.
Generic Organization User: The import process posts various system messages to the issues as they are processed. We require a generic organization user to be listed as a member of the organization for this purpose. SeeClickFix 311 CRM will use the Wrenchbot user if none is provided.
Timestamps: All timestamps must be in full International Organization for Standardization (ISO) 8601 format with Emezone offset. For example 2015-12-05T13:11:59-05:00
Location: All coordinates (latitude, longitude) must be WGS84 coordinates (SRID 4326) and need to be contained within the geographic boundaries associated with the request type's agency. Issues without a specific location must be assigned to a specific location (such as City Hall) and presented to the import system with appropriate coordinates.
Questions: When importing questions, the issues must be split up into multiple files with one request type per file. Since each request type may have a different set of questions, this ensures that the format for each file is consistent for each issue. If there are ten different request types, ten separate files should be prepared for import. If an import file contains multiple request types, the question text columns must be unique for all request types that are present in the file.
Required Question: If a question is configured as required, there must be a response provided in the corresponding column in the import files.
Custom Data: Custom data elements must be prepared and presented as either a public comment or an internal comment for import. Some examples include:
Importing the original reporter's name, email, and more as an internal comment
Importing the original service request identifier as an internal or public comment
Bulk Issue Import Format
Column Heading | Required | Description |
|---|---|---|
lat | yes | The WGS84 latitude of the issue |
lng | yes | The WGS84 longitude of the issue |
address | yes | The text street address of the issue, no newlines |
request_type | yes | The title of the SeeClickFix 311 CRM request_type associated with this issue |
summary | yes | A synopsis of the issue: Generally the same as the request_type title |
description | yes | Additional description of the issue, newlines accepted |
reporter_email | no | The user to associate with this issue. If blank, the issue will be attributed to the default guest user associated with the organization. If present, the issue will be associated with an existing user with the same address or a new guest user with the address will be created. |
created_at | yes | Full ISO timestamp for issue creation event: This timestamp will also be used as the acknowledgment time for the issue. |
acknowledge_message | no | Message to post with acknowledgment event The default message is used if the column is blank: Issue Acknowledged |
closed_at | no | Full ISO timestamp for issue close event. If blank, the SeeClickFix 311 CRM issue will remain in the Acknowledged state. |
auto_assignment | no | Yes or No If Yes, the auto-assignment process will be triggered during import. |
public_comment_1 public_comment_2 ... public_comment_n | no | Public comments to be associated with the issue. Comments must be formatted as plain text, newlines are accepted. |
internal_comment_1 internal_comment_2 ... internal_comment_n | no | Internal comments to be associated with the issue. Comments must be formatted as plain text, newlines are accepted. |
Q: Question Text 1 Q: Question Text 2 ... Q: Question Text N | no | The response to Question Text 1, 2, N The column heading text must start with "Q:" and then exactly match the question title text for a question associated with request_type. For example, Q: What is the size of the pothole? |