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”.
- Set the “postConvertKitDataToSheet” function to run between 1am and 2am, and
- 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:
=ArrayFormula(TEXT(SEQUENCE(7 , 1 , TODAY()-7) , "dddd mm/dd"))
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:
=INDEX(listData!C:C , COUNTA(listData!C:C) - 6):INDEX(listData!C:C , COUNTA(listData!C:C))
This formula will get the current list size in cell B12:
=INDEX(listData!B:B,COUNTA(listData!B:B))
And this formula calculates the average 7-day growth rate:
=AVERAGE(B3:B9)
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:
= ( End of the year - Today ) * Growth Rate
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:
=B20-TODAY()
And then this formula gives me the projected list size by multiplying the number of days by the growth rate:
=B12+(B21*B13)
Similarly, if you want to know how long it will take to hit a target list size, then you can figure that out:
= ( Target list size - Current list size ) * Growth Rate
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:
= ( Target list size - Current list size ) / Number of 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
Hi!
How would I change this so I am getting a report of each type of data I want for the entire previous month?
Currently, you have it set to update every day with subscribers, but I want opens and clicks per campaign, as well as how many newsletters were sent that month (not how many recipients, but how many newsletters), and unsubscribes per campaign.
My end game is a monthly report with:
Total subscribers gained that month
Total Campaigns sent
Total Opens per campaign
Total Clicks per campaign
Total Unsubscribers per campaign
Are you able to give me the info on how to code for that?