Business automation is a hot topic nowadays, and it should be. It’s very important to stay on track with the pace of today’s web era. Many business owners believe that learning or hiring someone to learn how to code is not an option for them because they don’t have the budget for this, or they just think it’s too complicated to do it themselves.
In this article, we will go through some simple steps that you can follow in order to automate your reporting processes, even if you’re a non-technical person. We’ll mainly focus on scheduling tasks and connecting services together so you can use reports from one tool instead of manually exporting all files from each tool and moving them around so you can have an overview of how well your business is performing.
As you might already know, there are plenty of tools out there that can help your business manage different aspects, like sales, marketing, or social media analytics. But if you’re like me, you’ll probably use at least 10 to 15 spreadsheets for reporting purposes. This means that each time something changes or an invoice is paid/issued you have to go back and create a new spreadsheet to track this data. It may not be too tedious if it’s just about one sheet, but imagine doing this multiple times per week/month! That’s quite a waste of time if I’m being honest…
You could organize all your invoices into one spreadsheet and then take 5 minutes every day, to sum up, all your sales, expenses, and profit. Or, you could create a report for each month and compare the data across multiple months if needed!
Another example would be social media reports: depending on what you do with it, you might have to create up to 10 different spreadsheets every week/month because of the different metrics you want to track for those channels. Well, there’s no need for this anymore as we’ll show below!
You don’t have to take my word for it though…
There are plenty of tools out there that let you export your data into very pretty and simplified spreadsheets or even PDFs (or any other format that’s supported). We can then connect those tools through Zapier which means that when one service triggers an event, it will send the data to another service.
I assume you have at least a basic idea of how Zapier works so I won’t go into much detail here, but if you don’t know it yet check out this article! It’s probably one of the most useful tools/services that are currently on the market.
Since we’re already talking about automation, let’s take a look at some examples of automating reporting workflows using these services:
1- Sales reports with Google Sheets and my favorite email tracking tool – Yesware
Google Sheets is great for creating reusable templates. To create your very own template in Google Sheets, go to File > Spreadsheet settings… and make sure you enable the ability to “Share as template”.
Now go back to the main page, create a new Spreadsheet, and click on Tools > Script editor. This will open up a blank script area where you can write your own scripts!
I’m using this example so I can show you how to easily integrate Yesware with Google Sheets. You have two options here:
a) Using Zapier – go to zapier.com/hello, log in with your account and select Make a Zap! then choose Google Sheets for the first step and Yesware for the second step. Follow the steps that are shown there, it’s very self-explanatory! Once everything is set up properly you’ll be able to trigger an event in Yesware every time you send an email (including tracking the open rate and where your recipient clicked on). You can even create a new spreadsheet with all these reports.
You can even set up email notifications so you’ll get a push notification every time someone opens your email.
2- Social media reports with Google Sheets and Facebook Page Insights
This is very similar to the previous example, it just has a different service involved. First of all, create a new Spreadsheet in Google Sheets and go to Tools > Script editor. This will open up your script area where you can start writing your custom scripts. Make sure that when you’re done creating your Zapier zap, make sure to add Yesware as one of the final steps because we need to send the data from Zapier over there too.
For this step, we’re going to use Facebook Page Insights as our service. In order to get your Page ID to go into the About section of your page and then click on Apps. You’ll see a list of all the apps that have access to this page. Select “Page permissions” from there and click on Edit settings. The first option should be your business page id.
You need two separate zaps for this:
one for fetching data from Zapier and updating a spreadsheet, and another one for sending over the data from Yesware so you can update yet another spreadsheet (ok, I know it’s getting complicated now but it’s actually very simple). For reference purposes, I’ve included links to my templates at the end of the article.
This one is a bit more complicated because you’ll need to create your own spreadsheet and script, but I’m going to give you some good starting points:
a) Creating a new sheet and populating it with data from Facebook – this part is pretty easy, you just need the right formula (see below). Just make sure that all the dates are properly formatted like =DATEVALUE(YEAR(C2), MONTH(C2), DAY (C2)). This will help you set up your integration accordingly.
b) Sending over the data from Yesware – as before, we’re going to use Zapier as our service and automate this step as well. Make sure to add Yesware as one final step on the Zapier zap.
For this part, we’ll use Google Sheets to grab all the data that has been passed over from Yesware. The first thing you’re going to need is your spreadsheet ID for this specific spreadsheet where you want to send your data. Now copy-paste =SPREADSHEET(“YOUR-SPREADSHEET-ID”) into your script (see below) and make sure it’s set up properly (it should be by default). Then hit Save, go back to your Zapier zap, and double-check if everything works accordingly.
Now that both of these are set up properly, you can sit back and wait until everything syncs! You’ll get a notification on your phone or in Gmail whenever something happens with your email or here’s the best part: every time someone clicks on any of your social media posts!
You can even get creative and use this system for many other applications. For example, you could create a new spreadsheet for every one of your clients that has its own script. This way you can get live updates from social media accounts, see what people are saying about them online, etc… pretty powerful stuff if you ask me!
3- Advanced Google Sheets integration through Zapier
If you don’t have enough Google Sheets experience so far, I’m going to go over some more advanced functions right now. Make sure to watch my video on how to automate workflows with Google Sheets below because I go over a lot of different examples.
In order to do this properly, we need to get your spreadsheet ID and create a new spreadsheet based on templates from Zapier.
You can then save the script and go to your zapier account where you can select Google Sheets as a service instead of FetchXML. Then simply paste in these two scripts separately on their respected fields and you’re done!
This is just one example of what you could automate with this type of integration. The possibilities are endless, so if anyone has any cool ideas or examples that I haven’t thought of yet, feel free to share them with me! Thanks for reading and happy automating!