Data Import

Overview of the data structures within the Retreat Guru software, and details of the format and content of the files that may be imported prior to the initial go-live.

Scope and Limitations

A data import is available as a fee-for-service for centers subscribed to an Enterprise subscription plan. Contact your friendly support guru for more information about this service.

Import of historical data is typically limited to first name, last name, email, program name, program start date, and program end date. This data must be submitted in CSV format using the templates provided by RG.

Import of current and future data may contain additional fields, including transactions and data that will populate custom questions.

Import of data is subject to the following conditions:

Data must be imported prior to your go-live date.
Files must use the templates provided by RG and be submitted in CSV format.
The data structure of the import must match the structure of the data within the RG application.

The only files that can be imported are registrations, programs, teachers, transactions (charges and payments), rooms, and lodging types.
Each entry in the registration file must be for a single individual attending a single program with a contiguous date range.
If a room is assigned it must be the same room for the entire stay.

You will have the opportunity to validate the accuracy of imported data before going live.

Data imports must be completed prior to your go-live date.
No data imports will be conducted post go-live.

Retreat Guru engineering services are limited to the performance of the actual import. Data scrubbing, reformatting, and other pre-import services are not available.

Overview of the data structure

The core RG data component is a registration.

A registration is specific to a single individual attending a single program for a single, contiguous date range.

Registrations are stored in the registrations table, with the registration_id as the key field that uniquely identifies a registration. All transactions associated with the registration (charges and payments) are stored in a transactions table, linked to the registration record by this registration_id field.

Each registration must contain a program_id (a field that references the programs table). If accommodation is associated with the registration, then the registration must also have a lodging_id
(a field that references the lodgings table) and room_id (references the rooms table).

When the multiple registrations feature is enabled in RG, a single guest can book multiple people during the online registration. The resulting registrations differ from the single registrations described above:

The first guest in a multiple registration is the group lead. All transactions (charges and payments) for all the guests in the group are linked to this group lead registration.
Although no transactions are associated with group members (all transactions are associated with the group lead), group members do have contact, room, and lodging information.
The group leader’s registration_ID is used to link the member's registration to the leader's registration.

Files for Import

Files must use the templates provided by RG and be submitted in CSV format, with individual files for registrations, programs, teachers, transactions (charges and payments), rooms, and lodging types.

Field names are case-sensitive.

If you choose to create programs, lodgings, and rooms manually in RG prior to the import, the import data will include fields for program_id, lodging_id, room_id (the existing id’s in RG).

If you choose to import programs, lodgings, and rooms, then the related import files must include fields for program_id_original, lodging_id_original, and room_id_original. These id fields allow the import process to match the imported registrations to the appropriate programs, lodgings, and rooms.

See accompanying templates (CSV files) for examples of import files:

registrations_example.csv
programs_example.csv
transactions_example.csv
teachers_example.csv
lodgings_example.csv
rooms_example.csv

The tables below detail the data stored in RG, and how the data relates to the import files.

We may be able to import additional data to populate custom questions in your RG configuration. Please explore this option with your RG representative before creating your import files.

Historical Data

The import of historical data is limited to first name, last name, email, and program.

Registrations File Record
Field	Description	Required for import?
status	Options: reserved, pending, need-approval, unconfirmed, canceled, arrived, checked-out, duplicate.	Required
time_submitted	YYYY-MM-DD (the registration date)	Required
arrive_date	YYYY-MM-DD	Required
depart_date	YYYY-MM-DD	Required
first_name		Required
last_name		Required
email		Optional
registration_id_original	The key, a unique field in the legacy import registration data. Import files do not supply the registration_id - it is automatically assigned (sequential) during the import process.	Optional
program_id_original OR program_id	If programs are imported, then the registration records need to reference the imported program. If programs are not being imported and are manually created in RG then the file must reference the existing program_id.	Required

Future Data

The data included in the future date import is subject to the structure and format of the data within RG.

Registrations File Record
Field	Description	Required for import?
registration_id	the key, unique field. This is automatically, sequentially assigned (not used in an import. See registration_id_original below)	n/a
program_id	references the program that is associated with the registration (for programs existing in RG)	Required (If program is already created in RG)
status	Options: reserved pending need-approval unconfirmed canceled arrived checked-out duplicate	Required
time_submitted	YYYY-MM-DD (the registration date)	Required
arrive_date	YYYY-MM-DD	Required
depart_date	YYYY-MM-DD	Required
first_name		Required
last_name		Required
Built-in address, contact info, and registration question fields	address, address2,city,state,zip, country. email, phone, birth-date, etc IMPORTANT: the field name must be the “slug” displayed in the Questions list in RG	Optional
Other fields as needed	Custom questions already created in RG. IMPORTANT: the field name must be the “slug” displayed in the Questions list in RG	Optional
room_id	references the room that is associated with the registration (for rooms existing in RG)	Optional (use when a room is already created in RG)
lodging_id	references the lodging type that is associated with the registration (for lodgings existing in RG).	Optional (use when lodging is already created in RG)
Specific to import data
registration_id_original	The key, unique field in the legacy import registration data. This is required to match up with imported Transactions. Import files do not supply the registration_id, which is automatically, sequentially assigned during the import process	Required
group_lead_id_original	Where you are importing group members, this field is required to link the group member record with the group leader. If this is blank, then the registration is a single registration. If a value is provided then the registration becomes a member of the group of which the group_lead_id_original is the leader.	Optional
program_id_original	If programs are imported, then the registration records need to reference the imported program. Note that in an import some registrations may need to supply a program_id (where the programs have been manually created in RG. Usually for future programs) and others a program_id_original (programs are to be imported. Usually for past programs)	Required (If a program is being created in RG during the import)
room_id_original	If rooms are imported, then the registration records need to reference the imported room. Note that in an import some registrations need to supply a room_id (where the rooms have been manually created in RG) and others a room_id_original (rooms are to be imported).	Optional (use when a room is being created in RG during the import)
lodging_id_original	If lodgings are imported, then the registration records need to reference the imported lodging. Note that in an import some registrations need to supply a lodging_id (where the lodgings have been manually created in RG) and others a lodging_id_original (lodgings are to be imported).	Optional (use when lodging is being created in RG during the import)
parent_registration_id_original	Needed for Groups. In a Group, a single registration record (Group Leader) contains the transactions for all members of the Group. The group member's registration records need to reference the registration_id_original of the group’s leader in the parent_registration_id_original field.	Optional

Teachers File Record
Field	Description	Required for import?
name	The teacher's full name. This must be unique	Required
description		Optional
featured_image	Specify a direct URL to an image to download and use it for this teacher (image file is imported into Media)	Optional
categories	Teacher category slugs must match existing teacher categories, separate multiple with \| pipe	Optional
sort_order	The order in which to display teachers on the teacher list (in ascending order)	Optional

Transactions File Record
Field	Description	Required for import?
object_type	Normally registration.	Required
transaction_id	the key, unique field, not used for import. Auto sequentially assigned during the import process.	n/a
registration_id	The registration that the transaction is associated with (not used in an import. See registration_id_original below)	n/a
status	Options: complete pending cancel fail. (Defaults to complete).	Required
trans_date	YYYY-MM-DD	Required
description	short text describe transaction	Required
charge_amount	$ amount when category is: refund other-refund cancel donate-reg extra-nights lodging other-charge program-addon program transfer-to-personal-credit or transport.	Required One of charge_amount or credit_amount
credit_amount	$ amount when category is: payment other-payment applied-personal-credit discount financial-aid or other-credit	Required One of charge_amount or credit_amount
category	See options in charge_amount and credit_amount	Required
fund_method	Options: cash check credit-card bank-transfer PayPal debit-card existing-credit other	Required
Specific to import data
registration_id_original	The key, unique field in the import registration data. This is required to match up with imported Transactions.	Required

Programs File Record
Field	Description	Required for import?
program_id	the key, unique field (not used in an import. See program_id_original below)	n/a
Title		Required
description		Optional
date_type	fixed, hotel, package, dateless	Required
date_start	YYYY-MM-DD	Required (for fixed)
date_end	YYYY-MM-DD	Required (for fixed)
categories	Program categories will be added on import, separate multiple with \| pipe	Optional
Specific to import data
program_id_original	The key, unique field in the import Program data. This is required to match up with imported Registrations.	Required

Lodgings File Record
Field	Description	Required for import?
lodging_id	the key, unique field (not used in a Lodging import. See lodging_id_original below)	n/a
lodging_name		Required
description		Optional
hotel_or_shared	"hotel", "shared" or empty. Hotel-style: people that know each other in a room, price is different per person. Shared style: strangers can share a room. Defaults to shared if left empty.	Optional
shared_max_occupancy	Max number a room can hold (for Shared style lodging type)	Optional
shared_price	Price per person	Optional
hotel_max_occupancy	Max number a room can hold (for Hotel-style lodging type)	Optional
hotel_pricing	The 2nd, 3rd, etc. prices are the DIFFERENCE to the one before it. So if your prices are 1st person: $100, 2nd person: $120, 3rd: $130, then you must enter "100 \| 20 \| 10".	Optional
order	Use to order the lodging types. Tip: count by 10s to insert others later	Optional
Specific to import data
lodging_id_original	The key, unique field in the import Lodging data. This is required to match up with imported Rooms and Registrations.	Required

Rooms File Record
Field	Description	Required for import?
room_id	the key, unique field (not used in a Room import. See lodging_id_original below)	n/a
room_name		Required
lodging_id	Where the Lodging is manually created in RG before the import. Note that in an import some rooms will need to supply a lodging_id (where the lodgings have been manually created in RG) and others a lodging_id_original (lodgings are to be imported).	Required (if Lodging is already created in RG)
Specific to import data
room_id_original	The key, unique field in the import Room data. This is required to match up with imported Registrations.	Required
Lodging_ids_original	If lodgings are imported, then the room records need to reference the imported lodging. Separate multiple lodging ids with a \|. The first is the primary, others are secondary.	Required (if Lodging is being created in RG during the import)