How to build a stats bot with Zapier and Slack

Even though every project needs it’s own individual automations, there are a few that are always useful: no matter if it’s e-commerce, event registrations or marketing campaigns. One of them is the stats bot. It is nothing more than a simple message stating the latest stats about your product or project - but even though so simple, it turns out to be quite powerful.

Using a Zapier workflow, this bot sends a message to a Slack channel with stats about your project: of the current stats as well as how they differentiate since the day before. 

Even though the setup is quite simple, the outcome is extremely powerful: it helps every team member to understand how the project is performing and to set ongoing actions like the start of a marketing campaign in context. It also helps other teams to understand what you do on a daily basis - your sales team by example probably knows about the latest numbers, but the rest of the team doesn’t.

Put simple the stats bot is a simple way of giving people on your team the information they need. It also gives you the chance to react immediately, if needed. By example, when you want to host a webinar and aim for a specific number of signups, the stats bot will tell you exactly how you are performing every day and you can change your strategy, if needed. 

‍

Want more automation resources sent to your inbox? Join our newsletter.

‍

Setting up our Google Sheet

The basis of this zap is a Google Sheet - this is where we get our data from. Let’s say, we want to track the signups of a newsletter. The newsletter signups are being pushed into a Google Sheet in a tab that we rename to Signups. 

In a second tab, we’ll build the basis for our channel message. In column A, we set our variables: Current, Yesterday, Today and Difference. In column B we count all the values listed in our signup tab. You can add more columns if you also want to track other stats like the budget you spend on a campaign, google analytics or something else. So, in the cell B:2 we count all the current signups with the formula =COUNTA(Signups!B:B). 

The cells B:3 and B:4 will be filled with data from Zapier, so we don’t need a formula here. To calculate the difference between our signups yesterday and today, we subtract today from yesterday with the formula =B4-B3. 

Setting up our workflow in Zapier

Now that we set up our little stats table, we can create our zap. 

Since we want to receive our stats message once a day at the same time, our trigger is the scheduler by Zapier. In our case, we want to receive the message at the end of every workday, so I’m choosing daily as the trigger event and 6pm as the trigger time. 

Next, we want to look up the current number of signups. Therefore we add a step with Google Sheets as the app and Lookup Spreadsheet Row as the action. 

Then, we want to look up the today row. The field is empty when you set up the zap, but it will fill after the zap runs for the first time. 

Next, we want to update the yesterday row with the data we just got from the today row - since the data we find currently in today is actually the data from the last time, the zap ran - which was yesterday in our example, where we set the trigger event as daily. 

After updating yesterday with the numbers of today, we want to update today with the current number. 

To wrap it up, now we want to know how many more signups we got since yesterday by looking up the difference row. 

Sending an automated message to Slack

Now we have got all the data we need for our channel message. We set up another step to post the message to the application of our liking - in this case, we’ll post in our Slack workspace. The message could look like this: the total amount of signup in bold letters (simply add * around the words you want to highlight) and the difference since yesterday in brackets with a + added in front of the dynamic value. 

This is how the message will look like in your slack channel. 

Now we can either leave it at that, or we can add a step, where we put your data in a new tab in our google sheet to keep track on how the numbers developed over time (by example, you could build a chart based on this data to visualize the progress over time). 

For that step, we just copy the first row of our stats tab, paste it into a new tab (named Stats History) and rename the heading of the first column with date.  

Then, we add another step in our zap to create a new row in the Stats History tab and map the data from the first steps to the fields in the new tab. 

Now you’re ready to test the zap and turn it on. Although it takes a bit of time to set the first zap up, you can copy the zap for all your projects and send the message to a designated stats bot channel, where you can see the stats from all your projects. 

Of course, you can exchange the different apps we used in this zap with other ones: Instead of Google Sheets you can use Microsoft Excel or Airtable, instead of Slack you can use Microsoft Teams, Discord or any other messenger you use in your company. 

The end result - stats in Slack

Below, you’ll find examples of more complex stats bot to give you a feeling of what others used the stats bot for. 

Happy building! 

‍

Need the help of an automation expert? Contact us now.

‍

This post was written by Lilith Brockhaus