Sending Subscribers Surveys with Ghost, PipeDream and AirTable
Tags: pipedream, nocode, automation, ghost
Published: 2021-06-29 06:10:12

---
title: Sending Subscribers Surveys with Ghost, PipeDream and AirTable
published: true
description: 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.
tags: pipedream, nocode, automation, ghost
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d6pe5lm69jd0y9mzo15w.png
---

##### 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](https://essentialrandomness.com) 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
![The Pipeline](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d6pe5lm69jd0y9mzo15w.png)

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](https://ghost.org):** 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](https://pipedream.com/):** 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](https://surveysparrow.com/):** A survey software. This is the first time I'm using it, moving away from Google Forms. Most important: supports webhooks and variables.
- **[MailerSend](https://mailersend.com):** Send email through API. I was already using this for [RobinBoob](https://robinboob.herokuapp.com), so adding a new template for the monthly survey was easy.
- **[AirTable](https://airtable.com/):** 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.