Marketing

Creating Marketing KPIs

Alex Black

Colin Dellow

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.
notion image
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

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
    • 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

      Add data sources

      Build reports & dashboards

      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:
    • 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
    • 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

      Add data sources

      Build reports & dashboards

      Now we can:
    • 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
    • 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

      Add data sources

      Build reports & dashboards

      Manipulating Search Console Data

      Now that we have our search console data we can understand:
    • 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.
    • This is great data because it can inform the following actions:
    • 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
    • Try it yourself
    • Click the Get Google Sheet
      • notion image
    • 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

      Joining Data

      Joining data means to take data from different APIs and join the data together, for example:
    • Landing page data from Google Analytics
    • Landing page data from Google Search Console
    • 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:
    • 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
    • Getting Started on your First Marketing Dashboard

      1. Install the Syncwith Plugin for Google Sheets
      1. Play around and connect to some data sources into Google Sheets
        1. Sample Google Search Console Analysis
        2. Sample Top Content Analysis