The humble spreadsheet is making a comeback.
Spreadsheet scripts have long been a business hacker’s favorite tool for automating work (if you’ve seen Amit Agarwal’s tutorials on Gmail automation and Twitter bots, you know what I’m talking about).
They both pull data from popular APIs (Google Analytics, Twitter, Slack, etc) directly into a Google or Excel spreadsheet.
This lets you build automated dashboards, research Twitter data, or program a Slackbot – all without writing code or bugging someone else to.
At the end of the post, you’ll be able to grab examples from the CIFL Template Vault.
But where to begin?
Both Blockspring and Supermetrics come with a learning curve, and are useful for slightly different jobs – diving straight into the deep end can come at the risk of bellyflopping.
In this post I’ll cover 5 ways I use Blockspring and Supermetrics:
- Dashboards and reporting
- SEO research
- Scraping, sourcing and lead scoring
- Team communication
- Social media automation
Note: If you’re ready to commit to learning Blockspring + Supermetrics, check out the new CIFL course The Lazy Toolbelt.
Both tools are spreadsheet add-ons, which means they come with a handy user interface in the sidebar of a Google or Excel sheet.
Connecting to services
They each allow you to select a data source and set data parameters, before pressing a button to populate data into your sheet.
Blockspring does connect to a wider array of data sources (105 vs 23 for Supermetrics) – but for nuts and bolts work, they both connect to most of the same APIs (Google Analytics, Adwords, Twitter, etc).
Setting up reports
For a peek at how they pull data, let’s compare a Google Analytics report setup from Blockspring (left) vs Supermetrics (right):
They both present the same rough options for configuring data – selecting columns (metrics & dimensions) to display in the report, dates for which to run the report, etc.
Supermetrics-only reporting features
But Supermetrics is tuned more specifically for digital marketing reporting, as it comes out of the box with the ability to:
- Select a dynamic date range for a report (last 7 days, last X months)
- Compare that date range to a previous period (for week-over-week, etc comparison)
- Group by date ranges (to view metrics summarized by month or year, useful if you need to backfill a lot of data)
- Visualize data in charts or heatmaps, vs the standard table view
Blockspring and Google Charts can be customized to do most of the same things, but it takes more setup than Supermetrics’ couple clicks. This is reflected in pricing – Supermetrics at $49/month for the Pro plan, vs $10/month for Blockspring.
Functionally, the data outputted by Google Analytics is essentially the same from each service:
If you’re creating a KPI dashboard using the raw data outputted by Supermetrics and Blockspring, there are limited differences between the two services.
But, if you’re directly creating individual marketing reports to share with your team, Supermetrics chart and date summary features can come in handy.
Now that you’ve got the lay of the land, let’s dive into how I use both services in my day-to-day marketing and business operations work.
How I use them
Both Blockspring and Supermetrics can support sophisticated spreadsheet dashboards, pulling all of the data from services you use (Google Analytics, Mailchimp, Facebook, Twitter, etc) into reports for your team.
I use Blockspring more specifically for research, since it allows you to chain data sources together. It’s invaluable to my process for SEO research, lead sourcing, and social media analysis.
Let’s take a look at a few specific examples, including how you can replicate my approach.
Dashboards + reporting
This is where Supermetrics and Blockspring both shine.
If you’re making one-off reports, I’d recommend starting with Supermetrics, but since I specialize in KPI dashboards, I use both of them interchangeably.
The big win is being able to pull all of your data into one spreadsheet. Use a number of different services, but you can hook them all together (screenshot of 4 blocks). Exporting and saving CSVs was just too much of a hassle – at this point, I won’t use a service unless they let me access data from a spreadsheet.
I’ve used them to create five types of dashboards:
Connect a database to run and visualize KPI queries for acquisition, activation, retention and revenue.
Salesforce pipeline (Blockspring-only)
Connect Salesforce to view current and closed pipeline status.
Web analytics dashboard
Connect Google Analytics to view data in a spreadsheet.
Content operations dashboard
Connect Mailchimp, Facebook, Twitter and GA to view content results alongside a content plan.
I pull live data into the same sheet I use to plan blog / social / newsletter posts + ads supporting them, in order to have a live view of how content is performing while planning new posts.
Paid acquisition dashboard
Connect Adwords, Facebook and Twitter ads to view campaign data inline with where copy + creative are drafted.
A note on dashboards: part of the fun of using these services is, once you’ve aggregate your data in one Google Sheet, you can send it out to other places where it’s more useful to you.
In the past, I’ve aggregated data in a Google Sheet before pushing results to Geckoboard to be displayed on their sexier and more team-friendly dashboard design. But with the release of Google Analytics Data Studio, you could send all of this data up to a live dashboard in GA.
If you’re thinking about creating a spreadsheet dashboard, I’d be happy to share my template – let me know what services you’d need connected here.
Both Blockspring and Supermetrics support both Moz and SEMRush for SEO research, but I prefer using Blockspring because of the ability to chain data together.
For example, if I’m researching link building opportunities for a group of keywords, I’ll combine a few blocks to come up with a list of potential targets for outreach:
- Block #1: Bing search on a main keyword, to pull the top 50 result URLs
- Block #2: Pass those URLs to SEMRush, to lookup backlinks to those pages (who might also potentially link to my page targeting that keyword)
- Block #3: Use SEMRush to lookup other keywords those same URLs rank for organically, to identify long-tail opportunities I might have missed.
You can repeat this process infinitely until you have a good grasp on keywords + related backlink opportunities.
There’s a number of other SEO research that can be done with Moz + SEMRush – searching keyword difficulty, or domain authority for a link target – the possibilities are really endless.
Scraping, sourcing and lead scoring
I use Blockspring as my prototype scraper, before getting my team involved to build out a production version.
When you’re doing research into a source of potential leads for a business, often you come up with an empty net – building software before you know what you want built is a pain, and something I’m way too lazy to inflict on people I work with.
So I mock up a process in a spreadsheet before passing it along to a developer. Let’s walk through one example: finding potential guest post opportunities.
One way to do this is to search for results that rank highly for a keyword, and check to see if they have author pages (indicating that they might accept guest posts). This can be done with three chained search calls in Blockspring:
- Block #1: Bing search for high-ranking results for a keyword
- Block #2: Get domain for each result
- Block #3: Run another bing search to see if there’s author pages on those domains
Results that have an /author page are worthy of further investigation, to see if you could contribute (or reach out to existing authors there to score a link).
I’ve also used similar chained-Block sourcing approaches for YouTube and Twitter research – being able to pass data around like this saves a *ton* of time when prototyping a new sourcing process.
Blockspring integrates with a handful of downstream communication services, so that you can take action based on data in your sheet.
I mainly use two blocks, for sending daily analytics summaries:
- Block #1: Send an email using Gmail
- Block #2: Post a Slack message
Supermetrics has a similar feature, which lets you share PDF report snapshots with your team over email.
Social media automation
I’m by no means a social media expert, but I do know it’s a great way to strengthen existing relationships with your customers (since they likely spend way more time on Twitter or Facebook more than they spend on your site).
Blockspring helps me understand who my customers are on social media, and quickly start engaging with them there. I chain a few blocks together to make this happen:
- Block #1: Run a Postgres query to find new users
- Block #2: Search for those users’ emails using the Fullcontact API, to pull social profiles
- Block #3: Add those users to a Twitter list
There’s a lot more Twitter automation that can be done with Blockspring – it allows you to publish tweets directly, or schedule social posts using Buffer.
Curious to get started automating your work with Blockspring or Supermetrics, but not sure where to jump in?
Access the CIFL template vault below to grab copies of our starter templates + get help from other CIFL members. Can’t wait to see what you build!