How to Use Google Sheets to Automate Data Workflows

One of the best ways to scale an agency is to focus on automating manual tasks that frequently recur for you or your team. And one of the most common, and sometimes most arduous, of those is client reporting.

With the number of platforms and channels available to agencies today, data workflows and reporting represent a significant opportunity for automation. From data collection to data visualization, these can be time-consuming tasks without the right processes and software in place.

If you haven't yet transitioned from Google Sheets to an automated reporting platform, that is a good place to start.

Automating client reporting can bring a number of key benefits to a marketing agency, such as increased efficiency (by eliminating the time-consuming task of copying and pasting data from multiple platforms), accuracy (by reducing human error rates, and enhanced security (no longer having to share client passwords across the broader team). Automation can also improve the client experience by reducing the time it takes to receive beautifully visualized reports, covering all of their vital metrics.

With AgencyAnalytics, we cut back the time to create great marketing reports by almost 60%. To be able to service more clients with the same people gives our agency the possibility to grow. - Nico de Jong, CEO of Forward Marketing

But even with the most robust reporting platforms, there are certain things that only Google Sheets can do.

Although the AgencyAnalytics platform has 80+ integrations already, there are often times when it makes sense to collect and store data in Google Sheets. That said, sharing this data with clients or reviewing it internally isn’t very convenient with Sheets alone. To solve this, our Customer Success team recently hosted a webinar discussing exactly how you can combine Google Sheets and AgencyAnalytics to automate your reporting process.

In this article, we’ll review the key concepts that were discussed during the webinar, including:

• Why Google Sheets?

• How AgencyAnalytics Helps with Automation

• How to Connect & Access Google Sheets Data

• Google Sheets Widget Types

• Embedding a Sheet in a Dashboard

• Using Zapier to Automate Data Collection

If you’d like to watch a replay of the webinar, you can find it on YouTube below:

Why Google Sheets?

The reason Google Sheets is such a core integration with AgencyAnalytics is that it allows agencies to add and display data from any third-party tool to a dedicated report or dashboard. AgencyAnalytics is designed to unify data from all channels into a single location. Sometimes, however, this data may come from a tool that isn’t currently integrated with the platform—this is where Google Sheets comes in.

 In particular, a few examples of Google Sheets use cases include:

• Data from unsupported integrations that you still want to monitor or report on

• In-house data that your agency has collected 

• Data that originates from custom or manual calculations

• Data that is being sent into a Sheet from an automation tool like Zapier

In short, Google Sheets can be incredibly useful for data collection, although it typically needs to be combined with data visualization to be valuable to clients.

How AgencyAnalytics Helps with Automation

At its core, AgencyAnalytics is designed to save agencies time and money by automating many of their manual reporting tasks. In the context of Google Sheets, this means that the platform will automatically pull in the latest Sheets data, meaning that as soon as you open a dashboard or web-based report, all of the data will be up-to-date.

Another way that the integration helps with automation is with the date range functionality that many of our Google Sheets have. Just like many of our other integrations, this means you can set a Google Sheets widget to automatically follow the date range of the dashboard or report. For example, if you’re sending a monthly report you can ensure that the Google Sheets widgets will only display the current month’s data, even if there is additional data in the Sheet outside of this date range.

How to Connect & Access Google Sheets Data

In order to connect your AgencyAnalytics account with Google Sheets, the first step is to click on “Integrations” in the sidebar and search for Google Sheets:

From there, you’ll be prompted to “Connect New Account” and then directed to login to the relevant Google Drive account:

After integrating the Google Sheets account, you can then navigate back to “Dashboards” and start adding the data to various widget types.

Google Sheets Widget Types

Now that we have integrated Google Sheets, we’ll review how to use this data with our main widget types. In order to add each of these widget types, you can simply click “Edit Dashboard” and then “Add Widget”:

All of the Google Sheets widgets can then be found under the “Misc” widget section:

After you choose a widget type, you’ll be prompted to select a Sheet to populate it with.

Table Widgets

This will display an entire Sheets tab in a table format, which can be useful to display goals, tasks, traffic, sales, or any other tabular data.

If you want to change the tab used, you can click on “Edit Widget” and then select from the “Spreadsheet Tab”:

Stat widgets

Stat widgets are the simplest widget option available and can be used to output the content of a specific cell, for example for showing totals, averages, and so on. To do so, we can simply add a new stat widget and then choose the cell in the settings:

Line charts

Line charts are useful if you want to visualize how data changes over time. To do so, we’ll need to edit the widget settings and indicate which column contains the date and which column to plot. 

Another core feature of line charts is the ability to add annotations and goal thresholds, for example below we’ve added a “PPC Campaign” annotation on a specific date and a target sales threshold:

Sparkline Widgets

Sparkline widgets are very similar to line charts in functionality, the only difference is that it will add a total of all the values in a stat widget above the plotted line:

It’s important to note that the statline widget will only display data from the date range selected in the top right of the dashboard, as opposed to all the data in the Sheet:

Pie charts

Pie charts are valuable if you want to display a breakdown of numeric values associated with metrics, categories, people, and so on. For example, in the pie chart below we have a breakdown of sales by person:

Bar Charts

Bar charts work largely the same way as pie charts and are useful for a breakdown of data across several categories:

Below you can see an example of a complete dashboard that has been branded and formatted entirely with data from Google Sheets:

Embedding Google Sheets in a Marketing Dashboard

A final use case of Google Sheets is the ability to embed and edit the Sheet from right inside the dashboard.

In order to embed a Sheet in a dashboard, all you need to do is click on “Share” from within the sheet and copy the link. Once the link is copied, you can click on add a new widget and you’ll find the “Embed” option within the “Misc” section.

The embed section allows you to embed any URL that’s publicly visible online. As you can see below, we now have an embedded Sheet that can be edited from directly inside the dashboard:

Using Zapier to Automate Data Collection

Until this point, we’ve reviewed how to add data that is already in a Sheet to a dashboard or report. In this section, we’ll look at how to automate the process of adding data into Sheets with Zapier. 

The example provided in the Webinar is using Zapier to create a social listening tool for Twitter. In particular, the tool is scanning for mentions of a brand name. As tweets mention the brand name, Zapier then automatically send the username, full name, post text and URL to a Google Sheet. From there, if we add this Sheet to a table widget, any new mentions of the brand will automatically appear in our dashboard in real-time:

Summary: Automating Workflows with Google Sheets & AgencyAnalytics

One of the main functions of the AgencyAnalytics platform is automating manual tasks for agencies in their data workflows and reporting. While we do have 80+ integrations and are consistently adding new ones, there are many times where a Google Sheet is still useful for collecting and managing data. 

Since sending a Google Sheet full of data to clients isn’t typically recommended, adding this data to a dedicated dashboard or report and using data visualization widgets adds a significant degree of professionalism to your reporting. What’s more, data from Google Sheets can be segmented based on the reporting period in order to maintain consistency with other data in the report. Finally, by combing AgencyAnalytics and Google Sheets with a tool like Zapier you can automate your entire workflow, all the way from data collection to client reporting.

Check out the Google Sheets Export Extension: The easiest way to get your marketing data from over 80 top marketing platforms into Google Sheets for extra handling and analysis. Start your Free Trial today!