After casting about for better ways of managing a vacation rental in Yosemite, we finally stumbled on Streak and Zapier in the last year. This combination has kept us more organized, freed us from onerous data entry tasks and let us get back to the parts of our business and life that we enjoy.
How do you like to spend your time?
Do you prefer spending your time giving great customer service or spending it keeping your books? Would you rather be relaxing or keying in data?
If you would rather spend time on customer service and relaxing, this guide is for you.
A word of warning: this guide is long and it is still not a click-by-click tutorial. Streak and Zapier are both fairly intuitive, so I’m just trying to point people in the right direction. If you need more help, just ask your question in the comments and I will add detail or create a tutorial post and screencast.
Also, this guide just scratches the surface. I hope to automate more tasks, but the paradox is you have to find the time to set up automation!
Problem #1: I Hate Data Entry
I love most aspects of running a vacation rental. I like to meet our guests and help them plan their stay. I like the maintenance, especially the gardening. And I have even settled a few domestic disputes and saved a vacation if not a marriage! It’s been fun.
The part I don’t like? The record keeping and data entry. Getting the data from email and listing site dashboards into our main calendar and accounting system was always a pain point.
We tried Quickbooks and absolutely hated it.
One of the best decisions I’ve made in the last ten years was to quit listening to the people who told me how great Quickbooks is. I switched instead to a set of spreadsheets, a customer relations management (CRM) tool and a calendar, all tied together with Zapier (more on Zapier later). Since Zapier integrates with Quickbooks Online, if you disagree with me and actually find Quickbooks usable, the basic method I outline here should work for you as well.
Problem #2: Coordination
In addition to data entry, my wife and I needed a way to communicate what was done and not done. In particular, we needed a better way to know which inquiries and reservations needed attention.
This is especially a problem because inquiries are coming from multiple sources for multiple people and we just didn’t have a good way to show clearly who had handled what.
Solution: Streak + Zapier + Sheets
The short version: Streak organizes our guest information and Zapier whooshes all the data to the places we need it to go to manage our schedule and pay our taxes.
Organizing with Streak
We started by looking for a solution to the coordination problem. After a few false starts, we found Streak. Streak is a Customer Relations Management (CRM) tool that integrates with Gmail. That was perfect for us because we were already using Gmail extensively (a Googe Apps account actually). It lets you know at a glance what the status is for any given customer and also lets you see quickly what needs to be done today.
Streak has three basic elements (see screenshot):
- Pipelines represent a journey of some sort. In our case, a pipeline called Reservations tracks the guest journey from initial inquiry to post-stay thank you.
- Stages are just like they sound. They are stages along that journey that is defined by the pipeline. So in the Reservations pipeline, we have stages like Inquiry, Approve Reservation, Schedule Code, et cetera.
- Boxes contain the thing that is moving along the pipeline. In our case, boxes hold details for a given guest. If you were using Streak as a bug tracker, each box would contain a bug. If you were using it to manage job applications, each box would hold the details for an applicant.
How does this help manage reservations?
I wish I could say that I’ve written a script that runs complex regular expressions and parses the email and automatically fills in everything I need. However, every listing service uses a different format. And each one has several different types of email (inquiry, booking request, etc). No single email or set of emails contains all the information we need. Automation would be messy. I am sure it can be done though.
All by itself Streak does attempt to suggest a name for a new box and tries to match an email with existing boxes based on the sender and the subject. Again, these emails have too much variation for Streak to get it right, so normally we do this manually.
Back in the real world
So, yeah, back in the real world, we don’t have our reservations AI bot running yet. When a new inquiry comes in, our actual process looks like this:
- Create a box with the guest name and add the inquiry to it.
- Add that box to the Reservations pipeline.
- Assign the box to the appropriate stage (Inquiry if we the person hasn’t decided, Enter Data if it’s a Book It Now reservation that we approved on the fly).
We take whatever information is in that initial email and enter it right then and there. In most cases, we also have to check our dashboard to get the customer contact info (it’s how the services monitor whether owners are dormant or active). It just takes a minute or two. And the best part: we will never manually enter that data again. Zapier will do the rest (more on that in a second).
Grouping and Sorting
Now that the data is entered, Streak lets me group and sort by any field. So I can group by stage and sort by arrival date. That lets me see everyone who is in the Schedule Code stage and which ones are arriving soon. I can also group arrivals by month and sort so I can see everyone arriving in August and see which stage they’re in.
Now either my wife or I can log into Gmail and see at a glance who’s arriving soon, who needs codes, and so forth.
Guest Info All in One Place
When we look in our inbox, every email is labeled with the pipeline and stage it belongs to. And best of all, every time we open an individual email or a box, all the data for that customer is there.
Decide What Information Matters to You
Everyone runs their rental differently. The data you keep is entirely up to you. It is dead easy to add and delete fields in Streak. So we have Notes, Arrival Date and so forth, but with a couple of clicks you can add and field you want and they are all searchable, groupable and sortable. Streak is amazing.
So problem #2, the coordination problem, is solved already just by having Streak.
But we still have to get all of this data onto our calendar and into our spreadsheets. This is where Zapier comes to the rescue.
What is Zapier?
Zapier is a service that lets apps like Streak and Google Sheets talk to each other.
Imagine Streak and Google Sheets both have cans (your data) in their hands. They can’t communicate with cans. Put a little hole in the bottom of each can, attach a string through the hole, pull it tight and magic! Streak can whisper into one can and the string lets Google Sheets hear it in the other one.
Zapier is the string that lets Streak talk to Google Sheets (and hundreds of other apps) communicate. And it is also magic.
So I set up two zaps to move my Streak information to Google Sheets and Google Calendar.
What’s a zap, you ask?
A zap is a recipe for getting a task done such as “Copy guest information in Streak to Google Sheets when the guest reaches the Streak stage Verify Data.” Zaps have triggers and actions.
The trigger I use is “Box changes stage” in Streak. So when I move a box from Inquiry to Approve Reservation, that fires the trigger and tells Zapier to take an action.
At this point, the action I want Zapier to take is to copy my data from Streak to Google Sheets. But there’s a problem here. At this point, I still haven’t entered the data. I don’t want to upload data at every stage change, only when I change to the stage Verify Data. So I need to add a Zapier filter (Thanks to Andrew Stewart from Streak for this tip).
Basically, you tell Zapier “When the ‘stage changed’ trigger fires, check to the make sure the new stage is ‘Verify Data.'” You can make these filters much more complex with AND and OR operators. This is super powerful.
Let’s assume we’ve hit the Verify Data stage (or whatever stage you choose). Now it’s time to put the Streak data into Google Sheets. But how does Zapier know which Streak fields to put in which columns? You need to map your fields to your columns.
How does Zapier know which Streak fields to put in which columns? You need to map your fields to your columns.
Google Sheets Magic
Now comes the one part that’s a little complicated. When you work with spreadsheets, you probably just drag formulas. In other words, you pick a cell with the right formula and just drag down and that formula gets inserted into the next row and the next.
You can’t do that here.
If you’re like us, though, your spreadsheet has a lot of calculated values — bed tax and length of stay for example. So what do you do? Simple, in Google Sheets, I created an “automation row” that is locked to prevent edits and which uses ARRAYFORMULA() to apply the formula to all rows as data comes in, like this:
This looks complicated but never fear. The TOT Rent column is what we actually charge and what is in Streak. That comes straight in due to the mapping we did in the last step.
The others are calculated values for the Transient Occupancy Tax (TOT) and Tourism Business Improvement District (TBID) assessment, which are 10% and 1% respectively, and 11% in total TOT. To calculate these values for every row below the automation row, we just use this formula, where column R is the TOT Rent column:
That may look complicated if you’re not a spreadsheet person, so let’s take this one bit at a time.
This says “Start with cell R2 and go down as long as there are cells in the R column.” This is the range that will be fed to the ARRAYFORMULA in the end. A function that normally would have just one cell (like “R2”) works with a range (R2:R20) in an ARRAYFORMULA. By leaving off the number on the end of the range, we’re basically saying “We don’t know how many rows we’ll have, so if you find something, do your stuff.”
We have the $ sign there so that when we drag to other columns, the R is constant because it always refers to the TOT Rent column.
We test for this because we only want to make the calculation if we have a value for TOT Rent. This is not just a matter of keeping the sheet clean. If you don’t do this, it can autogenerate thousands of rows. You don’t want that.
Now we’re getting into the meat of it. An IF statement has three terms: condition, value if true, value if false. In our case:
- condition: if the R cell is blank
- if true: do nothing (that’s why we have two commas in a row — there’s no action to take if R is blank)
- if false: this means we DO have a value for R (rent charged) and we want to calculate the tax. TOT is 10%, so that’s R*0.1.
Finally, we wrap all of that in the ARRAYFORMULA() and that gives us our original formula which operates across the whole range.
Dates Are a Little More Complicated
You’re probably going to want to work with dates if you’re managing a vacation rental. We don’t enter the length of stay, for example. We enter arrival and departure and let Sheets calculate the number of nights.
This really isn’t any harder than the taxes, but you do need to know one more function: DATEDIF(). This used to be a huge pain because of the way Streak formatted dates. Now they’ve fixed it and you can just plug them straight into a cell just like we did for TOT Rent. Then you just take the difference between Arrival (column G) and Departure (column H) and you have length of stay:
Simple as that!
Wasn’t That Easy!
Just kidding! I know. This is a super long post. It probably makes this seem harder than it is.
In fact, the only part that is hard is getting your formulas right in Google Sheets. Streak, Zapier, Google Calendar, Gmail are all straightforward. Streak and Zapier have lots of contextual help to guide you. It will probably take longer to read this damn article than to get everything set up! If you run into trouble, ask questions in the comments and I’ll do my best to help.
What’s the Payoff?
Before Streak + Zapier + Sheets, we always had this mad rush at the end of the month and at tax time. We had a complicated process of reconciling the inputs from various rental services, getting those into accounting software and then running the calculations we needed for taxes.
Now, we just enter the data as it comes in. Everything we need for a customer is in one place — all emails, notes, transactions and trip details are in their box. When they reply to us, that reply is automatically part of the box too and so when we look at the reply, we have all our notes.
Then, at the end of the month, we just go to our spreadsheet which is fully maintained by Zapier. We don’t touch it until tax time. Then we go in and sort by arrival date because Zapier enters reservations in the order we take them. We pull the monthly total in two minutes and our taxes are done.
Would it be an exaggeration to say this is life changing? Perhaps, but I love this system. Quickbooks would have some advantages, but I absolutely hate that program. I use a lot of software and could never get my head around that one. Even as great as it may be, Streak still has some advantages — it keeps all customer notes and communication in one place, rather than email in one place and contact, accounting and other information in another.
Our pain point used to be getting the data from Streak into the spreadsheet for tax purposes. Now that we’ve automated it with Zapier, that friction is gone.
Overall, it has reduced our stress, given us more free time and helped us devote more time to things that actually matter to customers, like improving our rental (we just added an outdoor sitting area and air conditioning) and helping them plan their trips.
It’s not for everyone and if you have a system that you love, don’t change. But if you’re a small rental operation and you’re struggling with complex accounting software or you’re using expensive booking systems, you might consider this. Because there is one thing I forgot to mention: every single thing I’ve shown works with the free versions of Streak, Zapier and Google. To me, that’s fairly minor. The paid versions are not that expensive. But free is a nice bonus!