PPC & SEO · 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 Slackbot 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 Slackbot 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.
There, choose the Webhooks by Zapier app, and select Catch Hook. Click Continue, then copy the Webhooks URL that Zapier gives you.
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.
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.
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.
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 click-through 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.
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:
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.
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 click-through rate, cost per click, or anything else you need.
Next time you’re discussing your ad campaigns in Slack, you can grab the latest data in seconds with your bot.
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 Supermetrics-powered spreadsheets even more powerful.
About Matthew Guay
Matthew 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.