Goal: To understand how to operate our Google Sheets reporting template, which uses Blockspring to pull data from Google Analytics and produce reports on acquisition, SEO, paid search, and overall site performance. Grab the template from the CIFL Template Vault on Trello.
THE FUTURE OF CODE-FREE APPS
Blockspring has introduced a new template UI in the sidebar of their Google Sheets add-on, which allows you to easily build a spreadsheet template that can be copied and used by other Blockspring users (if you’re new to Blockspring, you can get started with this tutorial).
With it, you can essentially create a spreadsheet app, to be used by any of your friends, colleagues, or even grandparents with very little effort.
The first publicly released template from CIFL is for Google Analytics reporting, and comes out of the box with a number of analytics report templates covering acquisition efficiency, SEO, site performance and paid acquisition.
The most exciting new feature is the ability to set reports to refresh automatically on a timer, so that fresh data can be pulled into the sheet even when it’s not open. This opens up powerful possibilities to use data elsewhere, like in a Slackbot.
Let’s peek under the hood of how it’s built in this tutorial, so that you can get started building your own Blockspring template.
Template tab structure
This tab walks you through how to configure the necessary web services to run the report – in this case Blockspring and Google Analytics.
Once you’ve connected Blockspring and Google Analytics, select an account and view for which you’d like to run reports.
Based on Avinash Kaushik’s proposed dashboard design, the action dashboard pulls in the most important data from a given report, and is configured for sharing key insights with your team.
It’s divided into four sections:
- Graphics / Data – pulled in automatically from the selected report
- Key Trends – what do you identify from the data as important?
- Steps to Take – what would you want to do about that data?
- Impact on the Business – how do these insights affect the company at large?
In the graphics / data section, the left table automatically pulls in the top 10 grouped values (whether pages, channels, etc) from the selected report (based on the queries in the Analytics Settings tab).
The graphic on the left allows you to quickly identify over or under-performing groups: big green bars = good, big red bars = bad.
If you’re using Google Sheets like these at work, you should crack the CIFL Template Vault.
Based on the report name selected at the top of the sheet, the template will pull in the appropriate column structure and data from the Google Analytics API. Each report will refresh once per hour by default, which can be modified from the _BlockspringConfiguration tab.
Making a few modifications will allow you to customize the report for your company:
- Conversion goal: Signups (ie Goal Completions) or Revenue?
- Sorting: Choose a metric to sort by, and selection of ascending / descending
- Segmentation: Select from a list of default Google Analytics segments (one will be recommended to you based on the report)
- Time period: Select from a list of stock date ranges
- Filtering: Select a metric or dimension to filter by, and add an operator + value to run the report
Have fun with these, and play around – viewing the same report over different time frames or segments can be shockingly eye-opening.
Accounts (hidden by default)
One tab is generated for each service that you’ll need to connect for a set of reports. This tab looks up all possible account settings, and is used to populate dropdowns on the Account Settings tab.
If your accounts aren’t loading to the Accounts tab, it’s likely because one of them isn’t connected properly – open the tab from View -> Hidden in the menu to debug.
OPTIONS (hidden by default)
Contains the universe of possible Google Analytics report metrics, dimensions, segments, date ranges, and other analytics options to make sure that invalid reports aren’t created.
Selections (HIDDEN BY DEFAULT)
Pulls in active settings from each of the configured reports.
_BlockspringConfiguration (HIDDEN BY DEFAULT)
Controls the setup of individual Blocks – which actual Block queries will be run, on what timer, and how they can be called from buttons in the sidebar. Each Blockspring template configuration requires a tempate_uuid, template_name, selection_metadata (a JSON object that configures buttons in the sidebar), and one row per block.
Each Block’s configuration must contain:
- A unique query ID
- The sheet name and range: controls where the Block will be run (in this case, the corresponding report tab)
- Date created and last run: for tracking activity
- Selection: controls button setup
- Timed refresh: using cron syntax
- Run: if TRUE, Block is live
- Match headers: if TRUE, the Block won’t return data when the response doesn’t match the existing columns in the sheet (useful when appending data to an existing table)
- Block ID: the name of the Block, as you’d pass it in a regular Blockspring funtion (google-analytics-reports, in this case)
Dive in, play around with this template by grabbing an invite to the CIFL vault below.