Experience the difference of "Elite".

How to import (simple) legacy data

Rich S
Mar 7, 2019 1:01 PM
Joined Dec, 2018 301 posts

I have years of data with the cols below. I can massage an excel spreadsheet into whatever format is needed.

(listing columns as rows for clarity)
A Unit = 1234
B Arrival Date = 1/1/2017
C Departure Date = 1/3/2017
D Total Paid by Guest = $331.02
E Commission Paid to Rental Service = $151.03
F Rental Service Name = Mountain Resorts, LLC
G Booking ID = 3G93849

Seems like there are imports for Payments, Charges and Bookings.... Here's my assumptions, please comment

(UPDATED - I realized there are reqd columns)
1) Please confirm I should only use Import Bookings, not the other two
2) Please confirm these mappings:

Your Booking ID -> G
OwnerRez Property ID -> G (same as 'Your Booking ID' ????)
BookedDate -> B (I don't have this field, so seems best to make it obviously fake to avoid confusion in reports)
Adults -> ??? don't have this data, can I use zero? Or best estimate (2 adults)
Children - > same question as for adults ??
First Name -> should I use same name for all (eg: FakeFirst)? Or uniquify? (eg: FakeA, FakeB, etc)?
Last Name -> should I use same name for all (eg: FakeLast)?
ArrivalDate -> B
DepartureDate -> C
Total -> D
??? -> E *** see below
ListingSite -> F ???

*** Column D (Total Paid by Guest) is useful to me because it will help in future rate setting
Column E is about 45% which is what I paid for a rental service for years (was fully staffed service with front desk, maintenance team, etc)
I'd really like to preserve Column D, but also account for Column E as a sunk cost. Maybe use DiscountTotal? Hostfee? GuestFee?

Chris Hynes
Mar 7, 2019 7:07 PM
OR Team Member Joined Oct, 2012 1400 posts

Sure thing, some answers:

- "Your Booking ID" is just a reference code for later. It's not required, but if you do a bookings import first and then a charges or payment import later, that's an easy way to link them up -- looks like that's your column G
- OwnerRez Property Id is the OwnerRez ORP number found in the Properties list. You can also use the name of the property in OwnerRez as long as it exactly matches
- BookedDate is required, if you don't have it you could use the arrival or pick some other date
- Adults and Children you can leave 0 if you don't have them
- First/Last Names are marked as required on that sheet because they're highly recommended but you can leave them blank if you want
- If all you have is Total and Commission, I'd set Rent and Total columns to the Total (your column D) and leave the Surcharge/Discount etc columns blank and and then set the HostFee to the commission amount.
- Host fee is used to track stuff like booking site commissions so it's not exactly matching a management fee but it's the best way to keep those separate. If you put in a discount, you'd end up with a lower total showing up in reports later.
- Listing Site can be the Rental Service Name (column F). First go to Settings > Channel Connections and add a Custom Source for that -- it'll match up by name.

The Charges import lets you get more detailed on an invoice. It's basically doing the same thing as the Rent/Discount/Total columns in the Booking import, but lets you specify names, tax status, etc. You don't need the Charges part if you don't have detailed information -- just use what's in the booking import.

You do probably want to do a Payments import. If you don't the bookings will all show as unpaid because they have an invoice but no payments. Easiest thing to do here is to put one payment for each booking, link by Your Booking ID (your column G), and put the full amount and use the payment date the same as the booked date you pick.

Rich S
Mar 7, 2019 7:11 PM
Joined Dec, 2018 301 posts

Thanks for the quick reply Chris !! All makes sense

Rich S
Mar 8, 2019 7:15 PM
Joined Dec, 2018 301 posts

Chris - Just getting around to this, I pasted my column headings below, are these sufficient? I kept them in the same relative column order I think, and I have all of the required (colored) columns.

Your Booking Id OwnerRez Property Id BookedDate ArrivalDate DepartureDate Adults Children FirstName LastName RentTotal Total HostFee ListingSite Notes

Chris Hynes
Mar 8, 2019 7:38 PM
OR Team Member Joined Oct, 2012 1400 posts

The columns need to match exactly, so start from our template, clear out all of the data, and then copy/paste from yours into the standard template.

Rich S
Mar 9, 2019 3:10 PM
Joined Dec, 2018 301 posts

I emailed y'all the test file I'm trying. I used the template, kept all of the column headers as is and just added my own info, using just 2 rows as a test.

The import failed with status 'Invalid' for each of the two rows, and the issue is listed as 'Charges aren't allowed for blocked-off time'

I don't have any blocked off time in 2014 (or anywhere else for that matter) so not sure what the issue is.

I have currency values (eg: $123.34) in three columns: RentTotal;Total;HostFee

Chris Hynes
Mar 9, 2019 6:00 PM
OR Team Member Joined Oct, 2012 1400 posts

Oof. I forgot about that 😬​ -- if you don't put any name or contact info, the import will treat it as a blocked off time.

To fix this, put in Imported as the last name.

Rich S
Mar 10, 2019 1:58 PM
Joined Dec, 2018 301 posts

Getting close to completing the first round of imports (260 rentals across 5 years)

I've had a variety of (mostly self-inflicted) issues, here's a list for others to watch out for:

- Must have all template columns, and (I think) the headers must match exactly
- Must have the sheet named same as the template (eg: Bookings)
- Don't have garbage data on trailing rows (my replacement operation accidentally added thousands of zeroes in otherwise empty rows)
- Must have a last name (first name can be empty)
- Double check for having duplicate Booking IDs (my mgmt company split bookings that spanned end of month)
- Payments must be no less than $.01 (I had a bunch of $0.00 bookings for some reason, probably personal use)

I sure appreciate having all my historical data in OR !!

Paul W
Mar 12, 2019 8:56 PM
OR Team Member Joined Jun, 2009 825 posts

Thanks for the pro tips, Rich!