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

In this post, you’ll learn how to build an automated ConvertKit Report tool in Google Sheets.

Every morning I have this report waiting for me in my inbox:

Opening the PDF attachment shows me the key growth metrics for my email list:

There’s also a link in the email to go direct to the Google Sheet, in case I want to ever make changes.

In this post you’ll see how to:

• Set up this template for yourself
• Connect to the ConvertKit API with Apps Script
• Retrieve list data into your Sheet
• Make projections about list growth
• Create a report that is emailed to you daily

ConvertKit Report Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

Google Sheet Template Setup

Once you’ve copied the template above, open the script editor where the code lives.

Step 1: Go to the menu Extensions > Apps Script

Step 2: Navigate to the script settings area:

At the bottom of the settings page, you’ll notice a place to add script properties.

Next, you need to get your API Key and Secret from ConvertKit.

ConverKit API Details

Step 3: Back in ConvertKit, go to Settings > Advanced

Here you’ll find your API Key and API Secret:

** Warning: Don’t share these with anyone! They give access to your whole ConvertKit account. **

Apps Script Setup

Step 4: Back in your script editor, click to add a Script Property.

Call the first one CK_API_KEY and paste the API Key value.

Then call the second one CK_API_SECRET and paste your API Secret.

Your settings page should now look like this:

Continue on to step 5 below.

ALTERNATIVE Apps Script Setup

If you don’t see the script properties on your settings page, don’t panic. Follow these instructions instead:

Step 4 ALTERNATIVE: Add your ConvertKit Key and Secret to lines 30 and 31 of the code in the setScriptProperties function, like the X’s in this code:

function setScriptProperties() {
  const scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperties({
    'CK_API_KEY': 'XXXXXXXXXXX',
    'CK_API_SECRET': 'XXXXXXXXXXX'
  });
}

In the menu bar above your code, select the setScriptProperties function and hit run to set your script properties.

Follow the prompts to grant permissions to your script.

Once you’ve done this, you should delete the Key and Secret from lines 30 and 31 of your code and just leave them blank again.

Step 5: Back in your script editor window, replace the “example@example.com” email on line 17 with your own email.

Add multiple emails by using a comma to separate them, e.g. “example1@example.com,example2@example.com,etc.”.

Step 6: Next, select the onOpen function and hit run, as shown in this image:

Follow the prompts to grant permissions to your script.

Step 7: Test your script.

Back in your Google Sheet, you’ll notice a new menu called “ConvertKit Menu”.

Click ConvertKit Menu > Get ConvertKit data

Following that, click on ConvertKit Menu > Email ConvertKit report

You should get data in your Google Sheet and a report in your inbox.

** One important thing to keep in mind is that it requires 7 days’ worth of data for the projection calculations. Initially your dashboard will show the dummy data included in the report template, or possibly #NUM! errors if you’ve cleared that data out (these will resolve once you get 7 or more days of data). Feel free to manually change the first 7 days of data to match your list size.**

Step 8: The final step is to set a daily trigger, so the script runs while you sleep!

Go to the Triggers menu:

Add two new time-driven triggers set to “Day timer”.

1. Set the “postConvertKitDataToSheet” function to run between 1am and 2am, and
2. the “exportAndSendPDF” function to run between 2am and 3am.

It’s important to run them in this order and 1 hour apart so that the data is retrieved before the report is generated.

Voila! You should now receive a daily ConvertKit report!

This report will help you plan future product launches because you can predict how many subscribers you’ll have on your email list on specific days.

For the remainder of this article, we’ll explore how the report tool works.

How To Connect To The ConvertKit API With Apps Script

Obviously, the key step in all of this is the connection between your Google Sheet and ConvertKit. The report is useful because it automatically retrieves data from ConvertKit for me, and relieves me from having to manually download and import CSVs of data.

The ConvertKit API is easy to work with and lets your perform typical CRUD operations as well as giving you access to list and broadcast data.

Rather than reproduce the entire script here, I’m going to highlight the key sections and refer you to this GitHub repo for the full script.

Global Variables

If you followed the setup instructions above, then you’ll have entered your ConvertKit API Key and Secret as script properties.

The script uses the getScriptProperties method to access them in the code:

/**
 * Global variables
 */
const API_KEY = getApiKey();
const API_SECRET = getApiSecret();
const RECIPIENTS = 'example@example.com'; // add extra emails with commas e.g. 'one@example.com,two@example.com,etc.'

/**
 * function to set ConvertKit API Key and Secret in properties service
 * 
 * USE THIS IF YOU DON'T SEE SCRIPT PROPERTIES IN SETTINGS
 * IF YOU CAN ADD THEM MANUALLY IN THE SETTINGS, YOU CAN IGNORE THIS FUNCTION
 * DELETE THE KEY AND SECRET VALUES AFTER USING THIS FUNCTION
 * 
 */
function setScriptProperties() {
  const scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperties({
    'CK_API_KEY': '',
    'CK_API_SECRET': ''
  });
}

/**
 * function to get my ConvertKit API Key from properties service
 */
function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty("CK_API_KEY");
}

/**
 * function to get my ConvertKit API Secret from properties service
 */
function getApiSecret() {
  return PropertiesService.getScriptProperties().getProperty("CK_API_SECRET");
}

Getting Data From The ConvertKit API

Here’s the code to call the ConvertKit API subscribers endpoint. You’ll notice how I include the API secret in the query parameter string.

/**
 * function to retrieve ConvertKit List Size
 */
function getConvertKitTotalSubs() {

  // URL for the ConvertKit API
  const root = 'https://api.convertkit.com/v3/';  
  const endpoint = 'subscribers';
  const query = `?api_secret=${API_SECRET}`;
  
  // check api
  console.log(root + endpoint + query);
  
  // call the ConvertKit API
  const response = UrlFetchApp.fetch(root + endpoint + query);
  
  // parse data
  const data = response.getContentText();
  const jsonData = JSON.parse(data);
  const totalSubs = jsonData.total_subscribers;
  
  // check data
  console.log(totalSubs)
  
  // return total new subscribers yesterday
  return totalSubs;
}

Finally, we need one more function to paste this data into the Google Sheet, into a sheet called “listData”.

/**
 * add the data to our sheet
 */
function postConvertKitDataToSheet() {
  
  // Get Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = ss.getSheetByName('listData');
  const lastRow = listSheet.getLastRow();

  // get yesterday date
  const yesterday = getYesterday();

  // get data
  const totalSubs = getConvertKitTotalSubs();

  // paste list growth results into Sheet
  listSheet.getRange(lastRow+1,1).setValue(yesterday);
  listSheet.getRange(lastRow+1,2).setValue(totalSubs);
  listSheet.getRange(lastRow+1,3).setFormulaR1C1("=R[0]C[-1]-R[-1]C[-1]");
  
}

This calls a helper function called getYesterday() shown here:

/**
 * get yesterday's date in correct format
 */
function getYesterday() {

  // get yesterday's date
  const today = new Date();
  const yesterday = new Date(today);
  yesterday.setDate(yesterday.getDate() - 1);
  const formatYesterday = formatDate(yesterday);
  
  // return formatted yesterday date YYYY-MM-DD
  return formatYesterday;

}

/**
 * format date to YYYY-MM-DD
 */
function formatDate(date) {

  // create new date object
  const d = new Date(date);

  // get component parts
  let month = '' + (d.getMonth() + 1);
  let day = '' + d.getDate();
  const year = d.getFullYear();

  // add 0 to single digit days or months
  if (month.length < 2) 
      month = '0' + month;
  if (day.length < 2) 
      day = '0' + day;

  // return new date string
  return [year, month, day].join('-');
}

How To Build The ConvertKit Report In Sheets

After the script runs for a few days, it builds up an archive of your total subs, like this:

This is the dataset we use to create the report in a new sheet.

And this is how I’ve setup the report Sheet:

Let’s see the formulas I’ve used to generate the data and projections in this Sheet.

Add this formula, using the SEQUENCE function and ArrayFormula, to cell A3 to create an automatic list of the previous 7 days’ dates, up to yesterday:

In the adjacent cell, B3, add this formula, which uses a cool INDEX:INDEX trick to return the most recent 7 days worth of list growth data from your listData sheet:

This formula will get the current list size in cell B12:

And this formula calculates the average 7-day growth rate:

Projections

Once you have the 7-day average growth rate, you can use it to project forwards.

For example, you can calculate how big your list will be by the end of the year, using this formula:

I.e. the number of days remaining in the year multiplied by the number of new daily subscribers.

With the date “12/31/2022” in cell B20, I use this formula in cell B21 to calculate the number of days left in the year:

And then this formula gives me the projected list size by multiplying the number of days by the growth rate:

Similarly, if you want to know how long it will take to hit a target list size, then you can figure that out:

Or what growth rate do you need to hit to reach your list size in a given number of days, e.g. how many new subscribers do I need per day to reach 50,000 in 100 days:

All of these formulas are shown in the template (linked above) so I encourage you to download a copy.

The final piece of the puzzle is to add the code to send the report to ourselves daily:

How To Create The Daily ConvertKit Report

The final piece of code is used to convert the spreadsheet to a PDF and email that to the email addresses you added to the global variables.

Here’s the code:

/**
 * send pdf of sheet to stakeholders
 */
function exportAndSendPDF() {

  // get today's date
  const d = formatDate(new Date());

  // get the spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const reportUrl = ss.getUrl();

  // make copy of Sheet
  const copiedSheet = ss.copy(`Copy of  ${ss.getName()} ${d}`);

  // copy - paste report as values to avoid broken links when sheets are deleted
  const copiedSheetReport = copiedSheet.getSheetByName('Report');
  const vals = copiedSheetReport.getRange(1,1,copiedSheetReport.getMaxRows(),copiedSheetReport.getMaxColumns()).getValues();
  copiedSheetReport.getRange(1,1,copiedSheetReport.getMaxRows(),copiedSheetReport.getMaxColumns()).setValues(vals);

  // delete redundant sheets
  const sheets = copiedSheet.getSheets();
  sheets.forEach(function(sheet){
    if (sheet.getSheetName() != copiedSheetReport.getSheetName()) {
      copiedSheet.deleteSheet(sheet);
    }
  });

  // create email
  const body = `A pdf copy of your ConvertKit report is attached.`; // the code in the template includes the link to the original Google Sheet, but caused issues here in WordPress so I removed it here only.

  // send email
  GmailApp.sendEmail(RECIPIENTS,`ConvertKit Report ${d}`,'',
    {
      htmlBody: body,
      attachments: [copiedSheet.getAs(MimeType.PDF)],
      name: 'ConvertKit Sheet Bot'
    });

  // delete temporary sheet
  DriveApp.getFileById(copiedSheet.getId()).setTrashed(true);

}

And there you have it!

Each day you’ll get an automated ConvertKit report so you can track your email growth and have a good sense of future projections.

See Also

You might also enjoy these posts, which show you how to connect other popular creator APIs to Google Sheets using Apps Script:

How To Connect To The Teachable API With Apps Script

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