Note: this is the second entry in a two-part tutorial. If you haven’t read the first part yet, you can check it out here.
Last time we set up a nice, simple roster to track all of our clients. Using Zapier, we created a system to let our sales people easily add to the roster by filling out a web form. Now it’s time for the fun stuff.
If you’re like us, you probably provide some services to clients on a regular basis. Let’s call it housekeeping. As a digital marketing agency, our housekeeping entails website maintenance, monitoring our clients’ search engine performance and other similar tasks.
Since customer satisfaction is so important to us, we want to complete these regular housekeeping tasks promptly. But it’s very tedious adding dozens of Yalla priorities to someone’s board that all say “do monthly maintenance for x client.”
This tutorial will show you how to automate the whole process. Using Zapier, we’ll set up a funnel to create batches of Yalla priorities at the beginning of every month. These priorities will flow to each client’s account manager.
This process should only take about a half hour, and it’ll save many times that in redundant work for your organization. So let’s get started!
Fun with Formulas
We first need to work a little magic on our Google spreadsheet. We’re going to write a few formulas to help with the automation process (kudos to John for the algorithms, by the way). Open up your client roster and click the + sign on the bottom left to add a new sheet. Name it “Data” by right clicking it on the bottom left of the screen and choosing “Rename.”
This sheet is going to hide data the roster needs to dynamically populate a couple important cells. First, we’re going to add the Yalla ID for every team member we want to send monthly priorities to. Every Yalla account has a unique ID; to find it, just click to the profile of the relevant person. The ID is the string in the URL of the page after “user/”.
We also need a cell to track today’s date – that’s how we’ll update the “active month” cell in the client roster. Click over to cell E1 and enter “=NOW()”, then hit enter. You should get today’s date and time, and it should update automatically if you refresh the spreadsheet.
Flip back to the client roster sheet. Remember last time when I had you fill in the active month for the first client? We’re going to let the robots handle it from now on. Head to your first client’s “Active Month” cell and enter the following formula:
=IF(ISNUMBER(SEARCH("Yes",B2)),text(Data!$E$1,"MMMM"),)
This tells the spreadsheet, “Go look at cell B2, where we’re tracking a client’s active status. If that cell says ‘Yes,’ set this cell to the month value of the date in cell E1 of the Data sheet.”
Computers are very picky, so you’ll have to tweak this formula if any of the relevant cells are in different places on your roster.
That cell should now say whatever month it currently is. Big deal, right? That’s what it was when we wrote it by hand! Here’s the difference: click on the cell, then hover over its bottom-right corner until your mouse turns into a +. Click that, drag down through your other cells and release. You should have just copied the month into every other client’s cell. And this formula will automatically update the month for you – no need to come in and change it.
Let’s add one more formula, this time to dynamically pull every account manager’s Yalla ID. Go to the first cell in your “Acct Mgr Yalla” row and enter the following:
=VLOOKUP(E2, Data!$A$1:$B$100, 2, FALSE)
This one says, “Look at the account manager’s name in cell E2. Take that value and search for it in the list of names in row A of the Data sheet. If you find a match, grab the corresponding Yalla ID and put it here.”
Again, you’ll need to fiddle with your formula slightly if you ordered your spreadsheet a different way.
Use the same click-and-drag method you used on the active month to fill out the account manager Yalla ID for all of your clients. You should now have a robust client roster with all the information you need to monitor your business and all the information Zapier needs to make some cool stuff happen. Grab a coffee and take a break; we’re only half done.
The Zap Rises
Log into Zapier and click “Make a Zap!” Select Google Sheets as your trigger app and “Updated Spreadsheet Row” as your trigger action. Click through to the “Set up Google Spreadsheets Row” page, and let’s get to work.
Choose the appropriate Spreadsheet and Worksheet. For your trigger column, you’ll choose “gsx$activemonth” or something similar – the column corresponding to the active month cells we just filled out. Continue on to test the trigger, and make sure the test row Zapier pulls includes all the information from your client roster.
Time to tell Zapier what to do when it detects a new active month. Choose Yalla as your trigger app and “Create Priority” as the action you want the app to take. You’ll need to select the Yalla account you want to send priorities from. This isn’t super important; we’re mostly concerned with where the priorities go. You can use your account, a manager’s, whatever works best.
Click to the next screen to set up the Yalla priority. We’re going to use all the different cells of our client roster to build the tasks our account managers see. I named mine “[Active month] Monthly Service,” for example.
Because I track whether we provide a given service for each client, I added tasks to the priority that will say “yes” or “no” for each service. This way the account manager will see which things they have to work on for the month and which don’t apply to the given client.
Move down the page, filling out fields as you see fit. For example, I set the “Due” field to “+7d,” meaning every monthly service priority will show up as due 7 days after it appears. Stop on the “User” field; we need this one.
Click the arrow on the “User” field, scroll down and choose “Use a custom value.” A new field should pop up, click the button next to it to add the “Acct Mgr Yalla” field from your client roster. This is why we built that formula to generate user IDs – it’s how we tell Zapier where to send these priorities.
Choose a custom value for the “Client” field and select the spreadsheet row corresponding to the “Client Yalla” that populates when your sales team fills out the form (or you do it manually).
Click through to test your Zap. It should create the monthly service priority on the appropriate account manager’s board. Click “Finish,” name your zap, turn it on and you’re done!
We’ve now automated a huge part of the monthly service you do for clients. On the first of every month, account managers will get batches of priorities reminding them what they need to accomplish for each client. Thanks to the form-to-funnel input process we implemented in part one, new clients will automatically be added.
Because Zapier is trained to watch for changes to the “active month” column, you won’t see much happen until the first of a new month rolls around. But you can always test your zap by changing the date in your “Data” sheet to a new month. Wait 15 to 20 minutes and bang – all your account managers got maintenance priorities. (Be sure to tell them it’s just a test – and change the date back to the “=NOW()” formula we set earlier!)
This is the most involved Yalla tutorial I’ve written yet, so thanks for sticking with it to the end. Let’s use all the future time we just saved to take a nap.