Problem

A client needed to build a simple data collection workflow on a very low budget. One of the requirements was to use as few apps as possible. The problem to solve was relatively simple – every month, the client needs to collect simple data from over 150 individuals. Until now, the process was 100% manual. The individuals would email the data to the client who always had to manually take the data from emails and enter it into a record keeping software. You can imagine how painful the old process was.

The goal of this project was to:

1. Create a custom form which would collect sales data for the previous month from the 150 people.
2. Send this form to all people on a list on a specific day of month.
3. Let the client easily manage the list of individuals who would be getting the prompts to fill out the form.
4. Every form submission is unique for each individual – so certain parts of the form cannot be editable, yet the form needs to show to whom it belongs. This ownership also needs to be transferred when the form data is submitted.
5. Have up to 3 reminders to go out on specific days of month. If the targeted day number falls to weekend, the reminder email needs to be postponed till Monday.
6. Prevent people from resubmitting the form over and over.
7. Store logs for every single interaction with the form – even if the form was just opened and not submitted.
8. Create a nice overview of the submitted data.

Solution

The project was solved with Make.com by using:

1. Gmail app
2. Google Sheets app
3. Little bit of custom HTML (Bootstrap)
4. BigQuery app

The solution consists of 4 scenarios:

1. The first scenario is sending out the initial notifications asking the individuals to fill out the form. The notification is a simple email which contains a link to a form for the particular person.
   1. The mailing list is stored in Google Sheets.
   2. Emails are sent via Gmail.
2. The second scenario is the coolest part of the solution – it’s a scenario which is generating the unique forms when the link from the email is opened.
3. The third scenario is a continuation of the second one. It’s storing the results from the form in case the person decides to submit the form. The results are stored into Google Sheets.
4. And finally, the fourth scenario is simply sending reminders with the link to the form to people who have not submitted the data for the preceding month yet.

Scenarios and Outcomes

This is the first scenario sending the emails with unique links to the forms:

---

This is the second scenario generating form interface by utlitizing Webhook app in Make. This scenario is also checking whether a submission of the particular form has been already made and if yes, a rejection message is shown. The form itself is an HTML code generated via the Webhook response modules.

---

This is how an annonymized form looks. Notice the Store ID and Form ID – those are automatically filled out when the form is opened and cannot be edited. So once the form is submitted, the client knows who made the submission for which month based on these 2 IDs.

---

Once the form is submitted, it’s processed by this scenario – all submissions are stored into a Google Sheets spreadsheet.

---

And finally, those who have not submitted yet are reminded on specific days of month by this last scenario:

---

All the collected data then flows to a pivot table in Google Sheets where the client can see submitted sales numbers by each invidiual:

Conclusion

By implemeting this solution, the client will save at least 10 hours of manual work every month. Besides saving time, the client is also eliminating errors resulting from manually copying the sales data from emails in the previous setup.

All that thanks to using very basics apps in a smart way.

Are you facing similar challenges?
HIRE ME

Or maybe you want to try yourself?
GO TO MAKE