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.