How To Get Fathom Analytics Data Into Google Sheets, Using Apps Script

In this post, we’re going to create a tool that calls the Fathom Analytics API and pastes website traffic data into Google Sheets:

But first, a quick backstory:

Earlier this year (2022), Google announced the sunsetting of the old implementation of Google Analytics, in favor of GA4.

At the time I was running the old Google Analytics software, implemented through Google Tag Manager (along with Facebook’s pixel tracker).

It was time for me to update my web analytics software.

But I didn’t want to just shove GA4 into my existing tag manager setup. From what I’d heard, GA4 was difficult to use and way overblown for my needs.

Also, I really wanted to remove the dependency on Tag Manager from my site, because it’s too complex for my use case and I’m not particularly familiar with it. Plus, it’s been years since I’ve used the Facebook analytics pixel so I wanted to get rid of that too. I wanted to improve my site speed, and removing all this javascript would help with that goal.

So I cast around for alternative analytics software and landed on Fathom.

Fathom Analytics is a lightweight, easy-to-use, privacy-focused analytics software that is perfect for my website.

It was ridiculously easy to set up and I’ve been delighted with how easy it is to use. I jump in and can quickly see everything I need to know for my website:

Introduction To The Fathom Analytics API

Fathom has an API and, although it hasn’t been publicly launched yet, it is available to existing customers.

It’s a powerful API that lets you control your Fathom account programmatically.

Besides the data you can access (which we’ll see in this post) you can also perform CRUD operations — Create, Read, Update, Delete – on your sites and events.

Connecting To The Fathom Analytics API With Apps Script

We’re going to use Google Apps Script to call the API.

To use Fathom’s API, you need to be an existing Fathom customer.

Set Up Your Google Sheet

To begin, create a new Google Sheet and open the Apps Script editor via the menu:

Extensions > Apps Script

Rename your Sheet and Apps Script file to something like “Fathom Analytics API Data Example”.

Get Your API Key

You access your API settings here.

On the API settings page, create a new API key and give it a memorable name:

When you click “Generate API Key”, you’ll see a popup with your API Key:

Copy this API Key. We’ll add it to the Apps Script project next.

Add Your API Key To Your Apps Script File

In your Apps Script file, go to the Project Settings, denoted by the gear wheel:

At the bottom of the settings page, click Add script property

Next, put the name “fathomKey” in the Property box and copy your Fathom Analytics API key to the value box:

Hit Save script properties.

Retrieve The API Key From Your Script

Back in the main editor window (the second link in the menu, denoted by the angle brackets) add the following lines of code:

/**
 * Global variable containing Fathom API Key
 */
const FATHOM_API_KEY = ScriptProperties.getProperty('fathomKey');

You can test this works by adding the following function:

function testScriptProperties() {
  console.log(FATHOM_API_KEY);
}

Select this function from the toolbar above your code, then hit Run:

Your script will output your API key in the logger if everything is working.

Add Code To Call The Fathom Analytics API

Underneath the API Key code above, add the following code, which calls the course endpoint of the Fathom API:

/**
 * function to retrieve list of sites from Fathom API
 */
function getFathomSites() {
  
  // URL for the Fathom API
  const endpoint = 'https://api.usefathom.com/v1/sites';
  
  // set params for API call
  const params = {
    method: 'GET',
    headers: {
      'Authorization': 'Bearer ' + FATHOM_API_KEY
    }
  };
  
  // call the Fathom API
  const response = UrlFetchApp.fetch(endpoint, params);
  const data = response.getContentText();
  const jsonData = JSON.parse(data);

  // log data output
  console.log(jsonData);

}

This code calls the List Sites endpoint, which returns a list of all the sites in your account. If you’re only using Fathom Analytics for a single site then you’ll only see one site listed here.

We set the parameters for the call, in the params object. Inside we have the headers object, and inside that we include the Fathom API Key, which we reference via the global variable FATHOM_API_KEY.

We use the UrlFetchApp class and the fetch method to fetch the Fathom Analytics API URL using the parameters we specified.

Finally, we parse the JSON data response and use the console.log method to display the output.

View this complete code on GitHub.

Run The Code

From the menu in the Apps Script editor, above your code window, select the getFathomSites function and press the Run button.

When you run the code for the first time, you have to grant permission to your script to access an external service.

In the first popup, click Review Permissions.

Then, in the second box, review the permissions and click Allow.

Once you grant permission, the script will call the Fathom API and return data, in this format:

{
  object: 'list',
  url: '/v1/sites',
  has_more: false,
  data: 
   [ { id: 'XXXXXXXX',
       object: 'site',
       name: 'Ben Collins',
       sharing: 'none',
       created_at: '2022-02-23 23:29:07' },
     {...},
     ...
   ]
}

Great work!

Getting Analytics Data From Fathom Into Google Sheets

You can either continue with the Google Sheet/Apps Script file from above or create a new one. (All the existing code can remain in place.)

Add The Site ID As A Global Variable

Since we’re going to use the Site ID to filter data from the API, it makes sense to create a global variable containing this value.

Firstly, copy the Site ID from your console when you run the script above (it’s the id: ‘XXXXXXXX’ above) or go to the menu:

Settings > Sites

Add this Site ID as a property in the Apps Script file settings (following the steps above with the API Key).

Then use this line of code to create the global variable:

const FATHOM_SITE_ID = ScriptProperties.getProperty('siteID');

Add A Custom Menu To The Sheet

Next, add a new function to the code with the special onOpen function.

This runs whenever your Sheet is opened or reloaded, to add a custom menu in your Google Sheet’s toolbar:

/**
 * setup menu to run Fathom function from Sheet
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Fathom Analytics Menu')
    .addItem('Get Fathom data', 'pasteFathomDataToSheet')
    .addToUi();

}

Call API For Site Traffic Data

Next, add a new function to call the aggregations endpoint of the API, to return website traffic metrics:

/**
 * function to retrieve fathom data
 */
function getFathomData() {
  
  // URL and params for the Fathom API
  const root = 'https://api.usefathom.com/v1/aggregations';
  const query = `?entity=pageview&entity_id=${FATHOM_SITE_ID}&aggregates=pageviews,uniques,visits,avg_duration&date_grouping=month`;
  
  const params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'Bearer ' + FATHOM_API_KEY
    }
  };
  
  // call the Fathom API
  const response = UrlFetchApp.fetch(root + query, params);
  const data = response.getContentText();
  const jsonData = JSON.parse(data);

  // create empty array to hold data for Sheet
  const unsortedArr = [];

  // loop over data and add to sheet array
  jsonData.forEach(month => {

    const mins = Math.floor(month.avg_duration / 60);
    const seconds = Math.floor(month.avg_duration % 60);
    const avg_duration_time = `${mins} minutes ${seconds} seconds`;

    unsortedArr.push([
      parseInt(month.date.substr(0,4)),
      parseInt(month.date.slice(-2)),
      parseInt(month.pageviews),
      parseInt(month.uniques),
      parseInt(month.visits),
      avg_duration_time
    ])

  })

  // return a sorted array, sorted by year then month
  return unsortedArr.sort((a,b) => b[1] - a[1]).sort((a,b) => b[0] - a[0]);
}

We use the same structure as the earlier example to list the site and used the params object to hold the API key so we could make the authenticated call.

Additionally, we’ve added a query string to the API endpoint, with the following details:

• entity=pageview – to specify that we want pageview data, not event data
• entity_id=${FATHOM_SITE_ID} – which site to retrieve data for, using the global variable
• aggregates=pageviews,uniques,visits,avg_duration – the metrics we want
• date_grouping=month – set the data to be grouped into months

See the Aggregation endpoint for a full list of query parameters.

Paste Data Into Google Sheets

The final part of the puzzle is to paste the website traffic data back into Google Sheets, with this code:

/**
 * function to paste list size metric into google sheets
 * setup trigger to run once a day
 */
function pasteFathomDataToSheet() {
  
  // set up sheet
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Sheet1');

  // retrieve site data from Fathom API
  const data = getFathomData();

  // paste to sheet
  sheet.getRange(2,1,data.length,6).setValues(data);

  // format numbers
  sheet.getRange(2,3,data.length,3).setNumberFormat("#,##0")

}

Here, we select the Sheet, retrieve the data from Fathom Analytics by calling the getFathomData function, and then paste the data into our Google Sheet. Notice how we use the number of entries in the Fathom data (which correspond to months) to determine the number of rows required in our Sheet.

Finally, we format the numbers with thousand separators to make them look more presentable.

View this complete code on GitHub.

Run The Code

Now, when you run the code, you’ll see a monthly report of your website’s data in your Google Sheet:

Nice!

How I Use Fathom Data

Unsurprisingly, I use a Google Sheet to plan the content for my website, newsletter, and courses.

One of the tabs contains a list of all my published blog posts.

I’ve added columns with the current 30-day pageviews, the prior 30-day pageviews, and a comparison for each post, so I can see at a glance which ones are doing well and which are not.

It’s been super helpful for me to know the “pulse” of my website.

Click to enlarge

In this example, my query string is:

const query = `?entity=pageview&entity_id=${SITE_ID}&aggregates=pageviews&field_grouping=pathname&sort_by=pageviews:desc&date_from=${startDate}&date_to=${endDate}&limit=${SITE_LIMIT}`;

Here I group on the pathname (i.e. by page or post of my website) and I’ve added date_from and date_to parameters to set the 30-day window.

View the full code for this example here.

Further Reading

This is my third post in a series about getting data from popular APIs used by creators. See posts 1 and 2:

How To Build An Automated ConvertKit Report In Google Sheets Using Apps Script

How To Connect To The Teachable API With Apps Script

See also:

Google Apps Script: A Beginner’s Guide

API Tutorial For Beginners With Google Sheets & Apps Script

Note: some of the links in this post are affiliate links, meaning I earn a small commission (at no extra cost to you) if you click a link and subsequently become a customer of that service.