- Pricing Matrix: Spreadsheet Set Up
1.1 Open “Pricing Matrix (2017 Template) – basic
1.2 Go to File > Make a copy > Save the new copy with property’s name in parentheses, i.e. Pricing Matrix (San Fran Fun) – Basic
Note: Do not edit the Template version, only your new copy
1.3 Once the copy is made, start on the first tab (“Set Time Zone”) by setting the time zone which the property is in and hide this tab
2. Switch: Run “Pull” into Pricing Matrix
2.1 Open the Switch subdomain for the property (Note: All rooms should have already been set up in Switch)
2.2 Go to Google Revenue API > Click on “Add New Dynamic Pricing Sheet”
2.3 Type in the name of the Room, in the Room Type drop down pick the room you will be syncing to the corresponding spreadsheet, leave the availability range and price range alone, make sure the date range is 2017-05-01 to 2017-05-31 (shown above)
2.4 In the “Spreadsheet Key” put the strand of letters and numbers from the website address in the DATA SYNC tab in which you at trying to link to Switch for the specific room type. Starting after “d/” and ending before the next “/”.
2.5 In the “Sheet ID” put the numbers of the sheet you’re linking, starting after the “=” in the sheet domain
2.6 Your Switch Spreadsheet API should look like the picture below. (Please Note: Make sure you are using the correct sheet ID from the sheet you want to link).
Each room type should have its own Data SYNC tab in the Pricing Matrix. Each Dynamic Pricing Sheet in Switch should match the individual Pricing Matrix sheet ID.
2.7 Now you are ready to “Push” the details for that room type into the Pricing Matrix spreadsheet. Press “Push” next to the room type.
2.8 Repeat for additional room types and delete extra tabs in the Pricing Matrix (for example, I had two types of rooms to sync, so I deleted tabs 3 – 9 in the Pricing Matrix Spreadsheet)
2.8.1 After deleting extra tabs and syncing everything, you’ll notice a reference error #REF! in column K of the DATA SYNC tabs of the Pricing Matrix. To fix this go to the “Availability” tab and delete all the columns in between columns F and N that have a #REF! error
3. Pricing Matrix Spreadsheet: Price Set Up
3.1. On the “Based Prices” tab in the Pricing Matrix spreadsheet, you should now see #Ref errors for the spreadsheets you deleted in rows 4 – 12.
(For example, I only have two room types so there are #Ref errors in rows 6 – 12). Delete the #Ref error rows.
3.2 First you must update the total number of rooms in cell B2. Now you can update the base price in column B for each room type. You can also update the discount % when occupancy is BAD in cell D2 and the premium % when occupancy is GOOD in cell C2.
Next you will want to decide at what point occupancy is considered BAD for the property and at what point occupancy is considered GOOD
(For example, I set my prices to be 50% more when occupancy is at 4 or more guests and my prices to be 50% less when occupancy is less than 2 guests)
4. Switch: Run “Push” from Pricing Matrix
4.1 On Switch go to Account > Management > API Spreadsheet > Click on “Pull” for both room types
4.2 To ensure that the sync is working, change the price of both room types per the Pricing Matrix “Base Price” tab by $1 and press “Push” for both again in Switch. Make sure the prices have updated in the Calendar in Switch.
4.3 Once everything looks good, you can set the Synchronization to “Automatic” on the API Spreadsheet in Switch