Sending Subscribers Surveys with Ghost, PipeDream and AirTable

Note:

This is not an "how to" guide, but me explaining my own pipeline to myself as a coherence check, and so I can remember what I have running in the future. If you want to deploy a similar project and have questions, you know where the comment section is.

Problem

I've recently created a newsletter with a peculiar gimmick: every month(?) paid users (members) receive a survey to decide the topic of the following month. I want to automate this process as much as possible.

To solve:

Members should receive a new survey each month

When a user becomes a member mid-month, I want to send them any active survey.

Keep track of which members have answered which surveys.

Final Pipeline

Here's an overview of the pipeline. Unfortunately, Excalidraw's sharing features aren't working right now, so I can't link to a larger version.

Technologies

Ghost: My CMS of choice, with integrated membership support. I self-host it on my own server, and it connects directly to my Stripe account.

PipeDream: Makes it easy to create automations spanning a series of different APIs. Similar to Zapier, but you can run custom code if needed. Generous free tier.

SurveySparrow: A survey software. This is the first time I'm using it, moving away from Google Forms. Most important: supports webhooks and variables.

MailerSend: Send email through API. I was already using this for RobinBoob, so adding a new template for the monthly survey was easy.

AirTable: To save the data for future reference. I started with Google Sheets, but soon realized I needed a less-brittle solution to feel comfortable with this pipeline.

Getting Member Data into AirTable

1) When a new user signs up as a member, their data is stored in Ghost's own internal DB. Technically, a webhook event is emitted at this stage, but I couldn't get it to work for my purpose (mostly because I couldn't figure out whether they were a paying member upon subscription).
2) Every two hours, a scheduled PipeDream job uses the Ghost API to fetch members and store them in AirTable. This job is independent from the rest of the pipeline, so I can use this table for all future membership-based actions.

Sending Surveys

1) Every two hours, a scheduled PipeDream job fetches all surveys with status in progress, and all the members who are eligible to receive them. Then, it creates a list of all the non-surveyed members for each survey.
2) For each user, PipeDream uses the MailerSend API to send an email with the survey template. Surveys have a subscriber_email parameters, which is used to pre-fill (and skip) the email step of the survey.
3) For all successful emails sent, the member is then added in the sent_to column of the Surveys table. This prevents repeated emails.

Tracking Answers

1) SurveySparrow triggers a PipeDream webhook when a survey gets a new answer.
2) The webhook triggers the "Register Survey Answer" pipeline, which uses the subscriber_email param to retrieve the member that filled the survey. If the param is not present, the survey will display a question asking for the email explicitly, and that will be used instead.
3) The Member with the corresponding email is added to the answered_by column in the Surveys table.

Potential Improvements

Trigger the Update Members job some time after each webhook event is triggered.

Trigger the Send Survey job whenever an update is made in AirTable rather than (or in addition to) via cron.

While AirTable also has built-in automations, its runs-per-month are too few for my use on the free plan (100). The pipeline could be simplified if I ever switch to the Plus plan, which features 5000 per month.

It's possible for someone to use the subscriber_email param to fake submitting the survey as someone else. This isn't a huge deal for my use case, but a workaround would be to create a unique identifier per survey sent that can then be traced back to the original users.

Rather than make a new survey manually each month, the survey could be automatically generated through the SurveySparrow API.