This reference article will help you understand how to track your marketing KPIs in Google Sheets by connecting to your major marketing vendors via API (Facebook, Instagram, Google Analytics, Salesforce, Pardot, and others).
We will review high level how to set your KPIs and how to pull the relevant data into Google sheets. We won't delve into too much detail on any given analysis such as: SEO (search engine optimization), ROAS (return on ad spend), CAC (customer acquisition costs), LTV (life time value). But our goals is to give you the raw tools to import important marketing data into Google sheets so you can track your APIs and build Sheets based marketing dashboards.
Example of using SyncWith to pull in GA data to understand search trends and top landing pages from search.
Try Any API Live on the Web
Before installing the SyncWith plugin for Google sheets, or even before reading this article if you want to you can easily try any API live on the web and get back data live in your browser
- See our listing of supported marketing APIs
- Try the Google Ads API
- Try the Facebook Ads API
- Try the Google Analytics API
- Quick tutorial to Sync Salesforce Leads into Sheets
What Makes a Good KPI
- Your KPIs need to communicate something
- What ad campaigns are profitable vs unprofitable
- Which types of content my visitors are reading
- What content drives traffic to my competitors websites
- Your KPI helps you take action
- Remove unprofitable ad campaigns
- Write more content that your users are interested in
- Write more content that is effective for your competitors
- KPIs need are different than business metrics, they need to let you know if you're hitting a desired outcome
- Achieve a return on paid ad spend of 20%
- Achieve a return on content marketing of 20%
- Ensure that the articles that represent 50% of the aggregate traffic to competitors sites have equivalent articles
- What % is coming from search
- Which blog posts are over / under performing
- We could run our posts through various tools to see why they're under / over performing to try and hit our KPI
- Build a pivot table showing the % of traffic coming from each source
- Show our top 10 landing pages and what % of traffic the drive and what the split is between referral, search and direct
- How well does a page rank for a given search query
- How strong is our CTR when our page appeared in the search results and were viewed by a user.
- Improving the attractiveness of our listing in the search results → title, descriptions, etc.
- Improving our ranking for various keywords → Improving content, garnering relevant back links
- Click the Get Google Sheet
- Install the syncwith plugin
- Do some analysis:
- Filter which keywords have the highest impressions but low CTR - improve those pages in search
- Find pages with high CTR but low position - promote your content to others who might find it useful
- Landing page data from Google Analytics
- Landing page data from Google Search Console
- Traffic data from Google Analytics
- Event/Goal data from Google Analytics
- SEM paid spend from Google Ads
- SEM paid spend from Facebook Ads
- Search ranking data from Google Search Console
- Install the Syncwith Plugin for Google Sheets
- Play around and connect to some data sources into Google Sheets
- Sample Google Search Console Analysis
- Sample Top Content Analysis
Lets' sum that up:
Lets take a look at building KPIs for SEO
One thing that's important to understand for you SEO strategy is where you're currently getting search traffic fro
We're going to connect live in this tutorial to your Google Analytics account to get your top landing pages from search for the last 30 days.
Top Landing Pages from Search
Landing PageSourceMedium
SessionsPageviews
Preview
Expand nested data, rename and delete columns
Landing Page | Source | Medium | Sessions | Pageviews |
|---|---|---|---|---|
Get a live Google Sheet
Export your data to a google sheet, with the SyncWith Addon, or download to CSV
Automatically updated
Schedule your data to update hourly or daily
Add data sources
Import additional data from other apps
Build reports & dashboards
Easily build live reports within Google Sheets
So now we have our top landing pages from Google organic search, but is this actionable and to what end? Perhaps we should look at having a KPI that at least 30% of the traffic to our blog posts come from search. We could then look at all our landing pages to see:
Top Landing Pages by Traffic Source
The following api connection pulls the top landing pages by traffic source from Google Analytics v3:
Landing PageMediumSource
SessionsPageviews
Preview
Expand nested data, rename and delete columns
Landing Page | Medium | Source | Sessions | Pageviews |
|---|---|---|---|---|
Get a live Google Sheet
Export your data to a google sheet, with the SyncWith Addon, or download to CSV
Automatically updated
Schedule your data to update hourly or daily
Add data sources
Import additional data from other apps
Build reports & dashboards
Easily build live reports within Google Sheets
Now we can:
Performance of Content in Google Search
If content isn't ranking well we can try and understand why - perhaps it doesn't rank highly in Google for various terms we'd hope it would. We can investigate further with Google Search Console.
Here's a API connection to Google Search Console which pulls all the time our site was shown in the search results regardless of whether a user clicked to our website for the last 30 days.
PageQuery
Preview
Expand nested data, rename and delete columns
Page | Query | clicks | impressions | ctr | position |
|---|---|---|---|---|---|
Get a live Google Sheet
Export your data to a google sheet, with the SyncWith Addon, or download to CSV
Automatically updated
Schedule your data to update hourly or daily
Add data sources
Import additional data from other apps
Build reports & dashboards
Easily build live reports within Google Sheets
Manipulating Search Console Data
Now that we have our search console data we can understand:
This is great data because it can inform the following actions:
Try it yourself
Joining Data
Joining data means to take data from different APIs and join the data together, for example:
So instead of having two Sheets each with different data:
We get one nice sheet with the data consolidated:
We can even join in more than two sources for our key landing pages: