Using Airtable to manage a trends-tracking email newsletter

A short note about process

For the last couple of years part of my job has been to keep my colleagues and employer up to date on technology trends; to make sure that everyone knows the moves in the technology landscape, and to try to follow trends in the market to help the company position itself well to meet them. This post is about a new process I’m implementing on a couple of aspects of that part of my role.

I have two problems that I want this process to solve. The first is that I want to be able to better track trends in technology, to understand new product releases and actions with better reference to what’s happened before. The second is that I send a weekly email newsletter with curated links to the most relevant and useful stories about the technology landscape, but my current method of assembling it’s cumbersome. I’m experimenting with a solution to both of these problems using Airtable.

The Airtable setup

Airtable is a kind of hybrid of a spreadsheet and a relational database; it’s very powerful with a wide range of features, but I’m just dipping my toe in by using it to combine both the trends tracking and the email publishing. I save links to technology stories in a base with multiple data columns. There’s a unique reference for each entry, a note, which is my description or comment, then the URL of the link to the story, and a category for… well, categorisation. There are also a couple of columns just for the trend tracking: the name of the company and product involved in the news (where relevant); these allow me to, for example, filter the results to find all the stories about XR featuring Apple.

It’s too soon to say if my first problem, that of tracking trends over time, will be solved with this approach; I need some time to see how that unfolds. But in the rest of this article I’ll explain in more detail how I’m using this base as a potential solution for automating the way I publish the weekly email newsletter.

Email publishing process

Until this week, when I took it public, I’d been running the email newsletter mostly as an internal tool, and my general production workflow had three stages:

1. Find the links using a combination of RSS, Twitter, and email newsletters, and bookmark them using Feedly’s Read Later, Twitter’s Bookmarks, or Google Keep for everything else.
2. Paste them into channels set up on the company Slack: #trends-machinelearning, #trends-messaging, and so on, with some comment and insight where appropriate.
3. Every Monday, copy and paste the links from each channel into a document, formatted with Markdown, categorised by the channels I pasted them into.

With my new approach step 1 remains the same, but step 2 changes: no more Slack, now each link goes into the Airtable base. Each week I add a new table into the base, to which I add the links for that week’s newsletter tagged with the categories, companies and products they relate to for the trends tracking.

The biggest change to my process is in step 3: there’s no more manual copying and pasting. Instead I export the content of that week’s table as a CSV file then use a script I wrote convert that to JSON, parse it, process it, and output it as Markdown-formatted content. I host the script as a web app on Glitch, of course, because Glitch is brilliant.

What the script does is:

1. Make an object of all the categories that contain stories that week, with an array of all the stories for each category.
2. For every category which only contains a single story, delete the category and move all those stories to another category called ‘Everything Else’.
3. Output a string of Markdown-formatted content with a section heading for every category and a list of all stories and links for those stories below it, as you can see in this extract:

## XR

Google’s ARCore library, which powers all AR apps on Android phones, has had a major update with new UI components and face detection (for AR masks).  
[developers.googleblog.com/2019/02/new-ui-tools-and-richer-creative-canvas.html](https://developers.googleblog.com/2019/02/new-ui-tools-and-richer-creative-canvas.html)

What the script really does is remove the hour or so of work I used to do on copying and pasting all the links from Slack into the email template. There’s still a little manual work to do: writing a short intro, and giving a final pre-launch check. But the long block of time that made up step 3 of my original process has now been almost entirely absorbed into step 2.

Next steps

I used the new process for the first time this week and it went well (here’s the newsletter, for your reference), but there’s still more work to do. For a start, I’m 100% positive I could improve my JSON-to-Markdown script, as I wrote it fairly quickly and without considering efficiency and optimisation. I could also look at running a cloud function to automatically detect a new CSV file uploaded to a cloud hosting bucket and trigger the conversion and formatting process. Finally, with a paid Airtable account I could also look at using Blocks to automate the export.

I’d love to hear any suggestions of how I could improve my process — especially if you’ve been experimenting with a similar workflow.

Originally published at Peter Gasston.