Using Airtable to manage a trends-tracking email newsletter

For the last cou­ple of years part of my job has been to keep my col­leagues and employ­er up to date on tech­nol­o­gy trends; to make sure that every­one knows the moves in the tech­nol­o­gy land­scape, and to try to fol­low trends in the mar­ket to help the com­pa­ny posi­tion itself well to meet them. This post is about a new process I’m imple­ment­ing on a cou­ple of aspects of that part of my role.

I have two prob­lems that I want this process to solve. The first is that I want to be able to bet­ter track trends in tech­nol­o­gy, to under­stand new prod­uct releas­es and actions with bet­ter ref­er­ence to what’s hap­pened before. The sec­ond is that I send a week­ly email newslet­ter with curat­ed links to the most rel­e­vant and use­ful sto­ries about the tech­nol­o­gy land­scape, but my cur­rent method of assem­bling it’s cum­ber­some. I’m exper­i­ment­ing with a solu­tion to both of these prob­lems using Airtable.

The Airtable setup

Airtable is a kind of hybrid of a spread­sheet and a rela­tion­al data­base; it’s very pow­er­ful with a wide range of fea­tures, but I’m just dip­ping my toe in by using it to com­bine both the trends track­ing and the email pub­lish­ing. I save links to tech­nol­o­gy sto­ries in a base with mul­ti­ple data columns. There’s a unique ref­er­ence for each entry, a note, which is my descrip­tion or com­ment, then the URL of the link to the sto­ry, and a cat­e­go­ry for… well, cat­e­gori­sa­tion. There are also a cou­ple of columns just for the trend track­ing: the name of the com­pa­ny and prod­uct involved in the news (where rel­e­vant); these allow me to, for exam­ple, fil­ter the results to find all the sto­ries about XR fea­tur­ing Apple.

It’s too soon to say if my first prob­lem, that of track­ing trends over time, will be solved with this approach; I need some time to see how that unfolds. But in the rest of this arti­cle I’ll explain in more detail how I’m using this base as a poten­tial solu­tion for automat­ing the way I pub­lish the week­ly email newslet­ter.

Email publishing process

Until this week, when I took it pub­lic, I’d been run­ning the email newslet­ter most­ly as an inter­nal tool, and my gen­er­al pro­duc­tion work­flow had three stages:

  1. Find the links using a com­bi­na­tion of RSS, Twit­ter, and email newslet­ters, and book­mark them using Feed­ly’s Read Lat­er, Twitter’s Book­marks, or Google Keep for every­thing else.
  2. Paste them into chan­nels set up on the com­pa­ny Slack: #trends-machine­learn­ing, #trends-mes­sag­ing, and so on, with some com­ment and insight where appro­pri­ate.
  3. Every Mon­day, copy and paste the links from each chan­nel into a doc­u­ment, for­mat­ted with Mark­down, cat­e­gorised by the chan­nels I past­ed 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 newslet­ter tagged with the cat­e­gories, com­pa­nies and prod­ucts they relate to for the trends track­ing.

The biggest change to my process is in step 3: there’s no more man­u­al copy­ing and past­ing. Instead I export the con­tent of that week’s table as a CSV file then use a script I wrote to con­vert that to JSON, parse it, process it, and out­put it as Mark­down-for­mat­ted con­tent. I host the script as a web app on Glitch, of course, because Glitch is bril­liant.

What the script does is:

  1. Make an object of all the cat­e­gories that con­tain sto­ries that week, with an array of all the sto­ries for each cat­e­go­ry.
  2. For every cat­e­go­ry which only con­tains a sin­gle sto­ry, delete the cat­e­go­ry and move all those sto­ries to anoth­er cat­e­go­ry called ‘Every­thing Else’.
  3. Out­put a string of Mark­down-for­mat­ted con­tent with a sec­tion head­ing for every cat­e­go­ry and a list of all sto­ries and links for those sto­ries 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 real­ly does is remove the hour or so of work I used to do on copy­ing and past­ing all the links from Slack into the email tem­plate. There’s still a lit­tle man­u­al work to do: writ­ing a short intro, and giv­ing a final pre-launch check. But the long block of time that made up step 3 of my orig­i­nal process has now been almost entire­ly absorbed into step 2.

Next steps

I used the new process for the first time this week and it went well (here’s the newslet­ter, for your ref­er­ence), but there’s still more work to do. For a start, I’m 100% pos­i­tive I could improve my JSON-to-Mark­down script, as I wrote it fair­ly quick­ly and with­out con­sid­er­ing effi­cien­cy and opti­mi­sa­tion. I could also look at run­ning a cloud func­tion to auto­mat­i­cal­ly detect a new CSV file uploaded to a cloud host­ing buck­et and trig­ger the con­ver­sion and for­mat­ting process. Final­ly, with a paid Airtable account I could look at using Blocks to auto­mate the export.

I’d love to hear any sug­ges­tions of how I could improve my process—especially if you’ve been exper­i­ment­ing with a sim­i­lar work­flow.


Also pub­lished on Medi­um.