How to Build a Bot with Supermetrics and Google Sheets

PPC/SEO SLACK BOT  · 8-MINUTE READ · By  Matthew Guay on July 28 2016

 

Ever wished you could pull up your spreadsheet data right inside your favorite chat app? Perhaps you’re discussing this month’s sales with your boss, or are trying to remember what a recently popular Tweet said during a team chat. Supermetrics has already pulled that data into your spreadsheet—but you don’t want to go search through a spreadsheet right now.

What you need is a bot that’ll find the data for you, and put it right into your chat app. Bots are simple robots that, like Siri and Google Now, can find information for you on the fly. The only difference is, you can type in commands in chat apps like Slack or via SMS messages, and bots will find the text you need and send it back to you in seconds.

Here’s how you can build a Slack bot to find info from your analytics and social media data in Google Sheets without leaving your chat—and if you want, you can use the same steps to make a bot for Twitter, HipChat, SMS, or any other text-powered app.

 

Building a Slack Bot for Your Spreadsheets

You only need three things to build a bot:

– An app with your data
– A chat app to talk to the bot
– An integrations tool to connect your data to the chat app

You likely already have a Google Sheets spreadsheet filled with data from Supermetrics—that’s the perfect tool to use with your bot. And team chat app Slack is a free, easy-to-use tool for both staying in touch with your team and talking to bots.

Then, to connect the two, app integration tool Zapier is the perfect tool for the job. It can connect hundreds of apps—including Google Sheets and Slack—to save data to your spreadsheets, notify you when it’s changed, and find data in your spreadsheet without you having to open it.

Let’s use all three to build two bots that can find data from our spreadsheet: one to find social media info, and another to look up detailed analytics or ad data. We’ll use a Slack slash command bot—one of the types of bots you can build in Slack. Essentially, type a slash followed by your command—say, /sheet to search your spreadsheet—to talk to the bot.

For more details about Slack Bots, be sure to check out the full Slack Bot tutorial on the Zapier blog.

 

Find Social Media Data with a Bot

So, first, make sure you have a spreadsheet with data from Supermetrics that you want to search through from Slack. I’m using a spreadsheet with recent popular Tweets about Google Sheets.

 

img1

 

Then, open your Slack integration settings at slack.com/apps/build, click Make a Custom Integration, select Slash Commands, then add the command you want to use. I’ll use /sheet, but you can use any command you’d like.

 

img2

 

Slack will then open a settings page, which we’ll come back to in a second. First, though, we need to make a Zapier Webhooks integration that Slack can send your bot’s text to. For that, create a Zapier account, and click the Make a Zap button in the top right.

There, choose the Webhooks by Zapier app, and select Catch Hook. Click Continue, then copy the Webhooks URL that Zapier gives you.

 

img3

 

Now, go back to your Slack settings from before, and paste that Webhook link into the URL field on that page. If you want, you can add a name and icon to your bot as well. Then, save the settings.

 

img4

You’re ready to test out the bot. Just open Slack, type your slash command, followed by what you want to search for in your spreadsheet. I want to find recent Tweets from Google, so I typed /sheet Google.

img5

 

Now, go back to your Zapier integration page, and click Continue where you left off. It’s time to connect your spreadsheet to Google Sheets, to search for the item you want.

To do that, select Google Sheets in the app picker, then choose the Lookup Spreadsheet Row action. Connect your Google Sheets account, and choose the correct spreadsheet. Then, select the column of your spreadsheet you want to search, and click the + icon beside the Lookup Value field and select the Text element from Slack to give Zapier the item you’re searching for.

img6

 

Test that, and Zapier will find a row from your spreadsheet with the correct data. Now it’s time to send that data back to Slack, and make your bot actually work. Click the Add a step button, and this time select Slack as the app and Send Channel Message as the action.

Connect your Slack account, then it’s time to make your bot smart. In the Channel option, select the Use a Custom Value option then click the + icon beside Custom Value and select your channel name from the Slack Webhook.

Then, add your Message text. You can type in anything you want, or click the + icon to pull in data from your Google Sheets spreadsheet.

 

img7

 

Test the Zap and turn it on, and you’ll get notified of the item you were looking for in Slack.

Now, next time you need to find something from your spreadsheet, just type it with your command into Slack, and your Zapier-powered bot will find it for you.

 

img8

 

Dig Into Ads and Analytics Data With a Bot

Now, perhaps you don’t need to find a Tweet. Instead, you need to dig into your company’s analytics data and find out exactly how many pageviews you got in May last year, or the clickthrough rate on last month’s ad. To build that bot, you’ll use the exact same steps as above, only this time we’ll use a spreadsheet with analytics and ad data.

Say we want to search the sheet for the views on a specific month’s ad campaign. We’ll start out by pulling the ad and analytics data into the spreadsheet with Supermetrics. Make sure you have a consistent name for your months in the leftmost column, and that you’ve named each of your data columns to make them easy to lookup.

 

img9

 

Now, use the same steps as before to make a bot in Slack. Only this time, when you send a test message in Slack, we’ll be searching for a specific month from our spreadsheet. So, to find last month’s ad views, we’ll enter:

/sheet 2016|06

That uses the same year and month style that we’ve used in our spreadsheet, to make it easy to find the month we need.

Now, let’s find that in the spreadsheet. As before, add a Google Sheets Lookup Spreadsheet Row action to your Zap, and this time in the Column setting we’ll select Month column. In the Lookup Value field, click the + icon on the right as before, and this time select the Text field which has the month from our query.

 

img10

 

Then it’s time to send the correct data back to Slack. As before, add a Slack Send Channel Message action, and select a custom channel using your webhook data.

Now it’s time to write your Slack message. Add any text you want, and click the + icon to pull in the ad view data that Zapier just found from your spreadsheet. Or, you could bring in any extra info you want: the clickthough rate, cost per click, or anything else you need.

 

img11

 

Next time you’re discussing your ad campaigns in Slack, you can grab the latest data in seconds with your bot.

 

img12

 

 

Spreadsheets are powerful tools—especially when you combine them with add-ons like Supermetrics and integrations from Zapier. With built-in functions, you can use the data those tools put into your spreadsheet and build your own powerful tools without any code.

Want to turn your analytics data into a custom dashboard, or make a CRM that pulls in info about your contacts and sends emails automatically—all from a spreadsheet? Then check out Zapier’s new free eBook, The Ultimate Guide to Google Sheets. It’s everything you need to know about Google Sheets, from the beginner steps to sort and format your data to advanced tips that let you import data from websites, automatically format your spreadsheets with Google Apps Scripts, and much more.

Download a copy —and set up your own integrations to make your Supermetric-powered spreadsheets even more powerful.

 

 

About Matthew Guay

a8d41c6e-dad4-4cee-99bc-767d4d181d71Matthew Guay is a content marketer on the Zapier team. He lives in Bangkok, and loves writing, photography, information architecture, and finding new ways to get apps to work together. Get in touch with him on Twitter @maguay.