Hi, I'm @shoinwolfe. I'm the co-founder of Hatch and the maker of the Donald Trump Simulator. I’m also known in Japan as DJ Sicks By Proxy. Watch my Tedx talk here. 日本語のブログは こちら.

Last year, I started a mini Airbnb real investment trust called REAT. I find sub-leasable properties, borrow it with investor capital, rent it out on Airbnb and split the profit with the investors. It now has 10 houses under management, and generates five-figures a month in revenue and I’m still the only employee. One of my biggest headaches was organizing cleanings via house cleaning services. Today, I’ll share how I fixed all of that.

The problem with manual entry

Manually ordering cleanings cost me a fortune. It cost me my time by having to do a repetitive almost every week. It cost me reviews, because there were occasional times when I forgot to reserve a cleaning. It cost me emotional drain from worrying if I had entered any data wrong. Worst of all, it cost me hundreds of dollars from ordering cleanings on the wrong day, no matter how careful I felt I was booking cleanings.

That’s more than enough reason to make a robot do it.

Time to automate

NOTE: The quickest solution for me was using the Tokyo Airbnb cleaning service, HouseCare (Yes, that’s me as a quote bubble on their website, I was their first customer). They let users send them the Airbnb generated iCal link, and they automatically book cleanings from that day on. Super simple. I love them.

But, HouseCare charges a lot for big houses. To be cost effective, it made sense for me to order my big houses through a different cleaning service called, Yadokari.

However, Yadokari requires users to book via Google Cal events into a specific calendar. I used to save up reservation confirmations in my Gmail, and create entries once a week.

Screen Shot 2016-04-22 at 3.04.20 PM

If Airbnb had an open API, we’d be able to connect everything directly, but they don’t.
Lucky for us, Airbnb’s reservation confirmation email for hosts always looks the same, so it’s easy to create a system around it.

Let’s dive in.

Gmail forwarding

First, we need to create a Gmail filter that automatically labels anything in that matches the filter conditions, and forwards anything with that label.

Screen Shot 2016-04-22 at 3.46.52 PM

The above photo’s filter means, if there is an email with a subject that contains “Reservation Confirmed” and has the words “4BR” or “6BR” or “Cozy Loft near Asakusa” (which are all parts of the names of my Airbnb listings), the filter will activate. Now we click, “Continue”.

Screen Shot 2016-04-22 at 3.41.06 PM

We then specify the actions for the filter. The filter will apply the label “Airbnb Zapier”, then forward the email to an email generated by Zapier’s amazing Email Parser. Now let’s go get the @robot.zapier.com email address.

Parsing the email

Zapier’s Email Parser let’s you define patterns in an email and automatically extract the data from any email you send it, that matches that pattern. The catch is, the emails you send it have to be in a similar format. But for our purposes, its perfect.

Generate a robot.zapier.com and send the parser a few template emails from your inbox. The more you feed it, the smarter it gets.

Define any data you want to extract, but for our goal, we only need the title of the listing and the checkout date.

(I’m also collecting the link that goes directly to the chat between me and the guest. I might use it to automatically scrape the email of the user, which I use to send them the directions in a PDF file. That’s a task for another day.)

Connecting the parser to Google Spreadsheets with Zapier

Screen Shot 2016-04-23 at 5.51.28 PM

Zapier is a service that let’s you connect APIs together, without coding. That means you can setup your favorite services to talk to each other with a few clicks.

First, we set up the Email Parser as the trigger. It should fire when it receives new emails.

Screen Shot 2016-04-23 at 4.57.07 PM

Next, we set up Google Spreadsheets as the action. You could skip this step, and connect it directly to Google Cal, but then you’ll never know if there was an error. I also like using Spreadsheets as a database to keep track of all events fired. 

Set the action as the creation of spreadsheet row.

Screen Shot 2016-04-23 at 5.09.37 PM

Make sure you’ve already created a spreadsheet with dummy data. Write the desired category of each column in the first row, too.

Screen Shot 2016-04-23 at 5.21.22 PM

From the drop down, connect the appropriate column category to the parsed data. Zapier makes it magically easy.

Screen Shot 2016-04-23 at 5.22.45 PM

For me, I needed to change the title of the listing into something my cleaning company understands (E.g. “6BR Home near Asakusa /6mins to JR” -> “両国”). So, I wrote a spreadsheet function.

Screen Shot 2016-04-23 at 5.30.42 PM

Code:
=IF(RegExMatch(INDIRECT(ADDRESS( ROW(),COLUMN()-4)),”6BR”),”両国”, IF(RegExMatch(INDIRECT(ADDRESS( ROW(),COLUMN()-4)),”Cozy Loft”), “東上野”, IF(RegExMatch(INDIRECT(ADDRESS( ROW(),COLUMN()-4)),”4BR”), “池袋”, “error”)))

To explain:

  • RegExMatch(INDIRECT(ADDRESS( ROW(),COLUMN()-4)) means that the function needs to find the value of what ever the value is 4 cells to the left, hence the column()-4.
  • IF that cell contains “6BR”, then the output is set to be “両国”. If it is, then “両国” is outputted. If not, it runs the next IF statement, to check the next similar statement.

If it doesn’t equal any of them, it writes an “error” into the cell. This is crucial, because if the parser isn’t perfect. If it outputs the wrong data, you need to know immediately. More on that in the next step.

Error notification zap

Screen Shot 2016-04-23 at 5.50.13 PM

Automation is great, but if we don’t know when the system has failed, we’ll always have to check if the cleaning orders have been made. So we need an error checker.

This time, make the newly created spreadsheet row from the last step, the trigger. (If you have a premium Zapier account, you could just make one long Zap, but I’m still clinging onto the free account for as long as I can.)

Screen Shot 2016-04-23 at 5.50.31 PM

Add a filter to check to see if “error” is contained in the output of the cell from the last step with all the coding.

Screen Shot 2016-04-23 at 5.50.49 PM

Make the action Zapier’s Outbound Email. We’ll send out an email if there’s ever an error, with the subject “Error with Airbnb Zapier.” Now if there’s ever a parsing problem, we’ll know! The less we have to use our brains to worry about things in daily life, the better :)

Google Spreadsheet to Google Calendar

Screen Shot 2016-04-23 at 6.02.28 PM

Finally, we’ll make a Zap to connect every new spreadsheet row to make a Google Cal event.

Screen Shot 2016-04-23 at 6.05.18 PM

Once you’ve connected the correct spreadsheet and calendar, what you put in is up to you. Google Calendar can understand the date string you parsed (“May, 7”) as a date input. Pretty neat, right?

No error check here, although I should add one. Honestly, the parser is the only section that worries me for errors.

Conclusion

That’s it! So much repetition, frequent worrying and mistakes now automated to run faster and more accurately than I could ever do.

And more importantly, the guests will be happier.

I'm on Twitter @shoinwolfe if you ever want to talk.

Shoin Wolfe

Hi I'm @shoinwolfe. I design and code digital products during the day, and DJ around Tokyo at night.

read more