Automated scholarship management, saving 8+ hours monthly while learning new skills

Project Description and Background:

- The Academy Scholarship Management Project automates the management of students’ study plans, replacing manual, error-prone processes and activities with an efficient mainly automated workflow using Airtable, Make.com, and AI tools. It streamlines data collection via Airtable Forms, automated fee calculation and invoicing via Airtable and Make, saving significant administrative time while ensuring data accuracy. Additional automations and tools enable personalized onboarding for new students, automated reminders, and insightful data analysis of Airtable data. By reducing manual effort and improving communication, the project enhances efficiency for administrators and students alike, enabling a more consistent and organized approach to managing academic activities for a diverse, global student base.

- The Academy relies on and is funded by donations and is supporting and educating young musicians with the goal of enabling them to secure a place in one of the big and popular orchestras or pursuing their careers in music in the long term. The students receive scholarships once accepted to the program. The instructors are also musicians that are mainly part of the orchestra attached to the Academy. In total there are about 130 instructors and 20 to 30 students/scholarships (duration of the program is 2 years)

- Why this project?

- I am not a Make or Airtable consultant (I got to know those tools during the course of the AI Mastery program and during AI summit last summer and was immediately impressed and interested to learn more). So I took this project in parallel with the AI Mastery program and my normal job as an employee to help a friend (who is working at the academy) and to get deeper into Airtable, Make and AI on which this project mainly relies on.

- Additional comments:

- The Airtable base and also make.com automations have been translated to English as the initial and “original” project is created in German language. I did my best to copy and translate everything (Database, forms, automations and also project documentation) and to set up “fake data” (I only created few sample students and instructors) in Airtable to show the process in English language —>I am sorry if some translations might not be 100% perfect, I mainly used AI to translate :)

- Tools used:

- Airtable (data base with multiple tables, Airtable forms, etc.)

- Make.com modules/APIs used over all 9 automations:

- Airtable (Search records, get a record, update a record, Delete a record)

- Tools (such as set variable, numeric aggregator),

- Flow control (such as array aggregator, iterator),

- Google Docs (Create a document from template, Insert paragraphs to a document);

- Webhook,

- Open AI (Create a completion),

- ElevenLabs (Create Speech Synthesis)

- Gmail (send an email)

- Fillout Forms (for updating data sent to Airtable) —>I could find this tool when doing research how to update Airtable records without having access to the database

- Custom GPT for data analysis —>Academy Analyzer (paid account is required as advanced data analysis is done)

- as the Academy has a Plus ChatGPT account, we switched to projects later on with same instructions prompt —>main advantage with project: all analyses can be kept in one project folder

1. Goal of the Project

The process/project serves to:

- Record and collect students' class data (student, instructor, category, date and time).

- Categories:

- Single lesson

- Coaching

- Chamber Music

- Orchestra (for this category the instructor is not relevant and no fee will be calculated or paid; instead the project will be entered instead of instructor)

- Data will all be collected in Airtable data base —>Link to data base: https://airtable.com/appRGGLysdhrgY6GO/shrdBxW7xY0Ki7IwX

- Calculate summarized class/teaching duration in units for each instructor - category combination and calculate instructor fees in automated way.

- Based on this, generate fee invoices documents in automated way.

- Keep the database up to date and consistent

- Analyze the data entered by students with a Custom GPT (Administrator is extracting data he or she wants to analyze as CSV) —>Academy Analyzer: https://chatgpt.com/g/g-67794496210881919e1a55574654c32f-academy-analyzer (switched to projects later on with same instructions and functionality)

- Automating Onboarding process for new students with addressing each student personally and sending a personalized Audio Welcome in the language spoken by each student (students are coming from all over the world)

- Automating Email reminder for students to check their data submitted

- Before implementing this process everything was done in a manual way —>estimated duration and hours spent for this task each month (according to administrator): at least 1 day (8 hours), in case of manual errors much more

- Process as it was:

- students (number of students vary between 20 to 30 for a time frame of 2 years) sent their monthly study plans as PDFs to the administrator (one PDF for each student for each month); reminders had to be sent manually to get all the PDFs

- data (Student name, category, instructor, start and end date and time) was then manually extracted from each PDF and entered manually in a Excel sheet to have all data together (main issue: each student entered data in different format —>some entered start and end time, some entered the duration of lessons in minutes, some entered the duration in hours…..—>lack of consistent data and manual follow up in case of questions)

- calculations for each instructor - category combination had to be done manually in excel (calculation of total duration, fee calculations etc…)

- fee invoices had to be created manually

- no analysis of data was done as too much work and time consuming

- Onboarding information and emails were sent manually; Emails were sent without addressing recipients personally

- Email reminders have been sent manually

- Main Goal:

- get consistent data for all students; all data will be entered in consistent way

- avoid errors by automating calculations and fee invoice creation

- save hours/time spent on those activities and calculations 

- Possibility to analyze data

- Improve Onboarding process by sending personalized Emails automatically based on Airtable data

2. Process Participants

- Students: Enter their class data and review it monthly.

- Administrator:

- Maintenance of database tables for instructors (including fees) and students

- Instructors table in case instructors leave or join —>not very frequently

- Students table —>at least every two years students leave and new students join

- Monitoring Airtable data and Make.com Automations.

- Finalize fee invoices (Address data, IBAN, BIC will be entered manually after fee invoice creation ) —>could be done as part of the automation but the request was to do it manually due to sensitive data

- Instructors: Receive fee invoices based on the recorded data by students.

3. Process Description

3.1. Submission of Study Plans

Data Entry: Students continuously enter their class data into an Airtable form or Fillout Form.

- Required Fields: Student name, instructor, category, Class duration (start and end time for specific date).

- Links to forms which can be used as an app icon on their smartphone:

- https://airtable.com/appRGGLysdhrgY6GO/pagSWVex1pwXT9VN8/form (each student will get a personalized link per mail with the student name being prefilled —>created by a formula in Airtable table “Students”)

- we might switch to Fillout Form to enter records in order to keep consistent forms (for updating records already entered by students a Fillout Form has been created, as this was not possible with Airtable —>see table “Overview all study plans” column “Update record” —>at the moment Airtable form for entering data and Fillout Form for updating records —>might switch to Fillout Form also for entering data (tbd))

Automatic Storage: Entries submitted by students are getting collected in the Airtable database in table “Overview all study plans”.

3.2. Automation 1: Monthly Reminder Email

Email Reminder:

- Automation 1 is running every month on the 1st and sends a reminder to all active students (—>checks Airtable table “Students” for column “Not active anymore”) with specific and personalized links to submit.

- Includes the link for each students view in order to check the entries submitted

- Content: Request to review and correct entries—> check if something is missing or wrong (to be done within next 5 days)

Corrections by Students: Students review their data and add missing data or correct wrong information.

3.3. Automation 2: Calculation of Teaching duration in units (for internal 45 minutes = 1 unit, for externals 60 minutes = 1 unit —>taken into account by a formula in Airtable)

Start: After the correction deadline, Automation 2 is initiated via Make.com.

Data Retrieval: The automation accesses and reads all data entered for the previous months in Airtable.

Calculation:

- Aggregation of teaching duration in units per instructor and category combination.

- Remark: Category “Orchestra” will not be considered for fee calculation, nevertheless the data needs to be collected for each project (instead of instructor)

- Result: Data set with instructor, category, and summarized number of teaching units provided in month X (=previous month).

Storage: Calculation results are output in table “Duration per instructor - category combination” in Airtable database.

3.4. Automation 3: Add Fees per Instructor in Results Table “Duration per instructor - category combination” plus automatic Fee Calculation in Airtable

Start: Automation 3 is initiated after the completion of Automation 2.

Data Retrieval: The automation reads the aggregated data from the results table of Automation 1 and adds the respective fee per unit for each instructor - category combination.

Add fee per unit per instructor to table “Duration per instructor - category combination”:

- A fixed hourly rate is applied per instructor —> maintained in Airtable table “Fee per instructor (internal and external)”.

- rates differ for external instructors (taken into account by a variable “external”)

- Automation checks table “Fee per instructor (internal and external)” and adds the respective fee value to “Duration per instructor - category combination” table

- Result: Fee per unit is added in table “Duration per instructor - category combination”.

Automatic Calculation of Fees for Each Instructor-Category Combination via Formula: A formula calculates the fee per “instructor - category” combination directly in Airtable —>”Calculation fee in EURO”

3.5. Automation 4: Calculates total fee over all categories for each instructor (this is only for information and not required for fee invoices as invoices will be created per “instructor - category” combination)

Start: Automation 4 is running after completion of Automation 3.

Data Retrieval: The automation reads the aggregated data from the results table of Automation 2 and 3

Calculation:

- Calculation of total fee for each instructor over all categories

- Result: Total fee for each instructor

Storage: Results are output in table “Total fee calculation” in Airtable.

3.6. Automation 5: Creation of fee invoices for each instructor - category combination

Start: Automation 5 is running after completion of Automation 4

Creation of Fee invoices:

- Compilation of data per instructor and category into a fee invoice document (Google Doc) which is stored in a specific folder in Google Drive.

- Add details such as address, IBAN, BIC, manually (due to data privacy and sensitive date the request was to do this still in a manual way but could also be done as part of the automation)

Send fee invoices to instructors: Fee invoice documents are sent to the instructors.This will be done manually in order to check each invoice document before sending it.

3.7. Regular Deletion of Old Entries

Deletion Automation 6 and 7 (both Automations have same Make Modules only with different settings and selections):

- Tables with all the calculations done are getting deleted once a month to be able to start new calculations and automation for the following month

Deletion Automation 8

- Entries (older than 2 months) entered by the students on regular basis are deleted once per month

Archiving: Before those deletion automations are executed, the data will be exported (export as CSV —> then save as Excel or Google Sheet) and archived —> request was to do it manually but could also be done with an automation to extract data from Airtable and transfer them into a Google Sheet or Excel).

3.8. Automation 9: Send personalized Onboarding and Welcome Email (including personalized Welcome Audio) for new students —>

Start: Automation 9 will be triggered by entering a new student with relevant data in “Students” table in the Airtable database —>webhook will be triggered by clicking on Button “Start Onboarding” in Students table

Onbaording Email with Welcome Audio will be created and sent:

- Airtable data on table “Students” will be entered by administrator:

- Name

- Email

- Links for personalized Airtable Form will be created automatically with a formula

- Country

- Link to the view for each student (view for checking their data that has been entered)

- By clicking on “Start Onboarding” the webhook and the automation 9 will be triggered

- Personalized Welcome Audio using ElevenLabs will be generated in the language spoken in the home country of the student (based on country information and student name)

- Email (the email should always be sent in english) with relevant information and personalized links will be created and sent to the email of the student

- Students table will be updated with date when Onbaording Email has been sent

3.9. Academy Analyzer Custom GPT (recommendation: run before Automations 2 to 5, as a check for duplicate entries will be done)

Drop the CSV file within the Academy Analyzer: https://chatgpt.com/g/g-67794496210881919e1a55574654c32f-academy-analyzer 

The following analyses will be done automatically without adding any comments or prompts:

- Check for duplicate entries (it check if there are any duplicates based on “Instructor/Project - Category Combination; Student; Start; End; Duration in minutes” combination

- Instructor Performance Analysis

- Category Based Insights

- Student Engagement Analysis

- Project Attendance

Table and charts and short written summary and observation will be provided in addition to a word document with main findings and tables

4. Process Frequency

- Ongoing: Students enter their data.

- Monthly:

- Reminder email to students every 1st of the month (done with automation)

- Students to review data entered and check if something is missing; if necessary correct (via Fillout Form —>table “Overview all students” column “Update record”) data entered

- Execution of the automations for fee calculation and fee invoice creation (Make.com),

- Archiving old data (done by administrator) and execution of deletion automations

- Check and analyze data entered with Academy Analyzer GPT (can also be done as year end review or at any other time whenever required)

- When new students join and button “Start Onboarding” in Airtable table “Students” will be pushed:

- Automation will be triggered to send onboarding emails with relevant information and personalized links and personalized welcome per Audio file

5. Documentation and Archiving

- Airtable: Contains all submitted and calculated data. Old data will be archived.

- Logs (Make.com): Records of the success or failure of automations.

- Fee Invoices: Archiving of documents and data in a defined format with a retention period of at least X years.

6. Future potential for optimization

- Fully automate invoice creation (also adding Address data and bank account data to the invoices instead of doing it manually —>request was to add those data manually due to sensitive data)

- Fully automate archiving and extracting of old data (request was to do it manually for now)

- Fully automate sending fee invoice per Email (for now they want to do manually)

7. Relevant Links

- Link for Airtable Data Base: Airtable - Study Plans english version

- Airtable Form Link (general form link): 

- https://airtable.com/appRGGLysdhrgY6GO/pagSWVex1pwXT9VN8/form

- Comment: each student will get a personalized link with student name prefilled (will be created automatically via formula in “Students” table)

- Links for Updating Records is available by clicking on “Update Records” in each view for each student (Fillout Form Link)

- Example for View Laura Beier: https://airtable.com/appRGGLysdhrgY6GO/shr18cemsjq83Spt9/tblQQ46JKB2xezXug 

- Link for Custom GPT “Academy Analyzer”: https://chatgpt.com/g/g-67794496210881919e1a55574654c32f-academy-analyzer 

- Loom video explaining the database, automations etc..: https://www.loom.com/share/f1a6d39947a044e98fadd1492be0d988?sid=159d78a5-0ae9-45ec-8bed-c897683faf29 

8. Learnings and conclusion

This project allowed me to learn so much about AI, Airtable and Make automations while building on and expanding what we learned during the AI Mastery Program. I got to know those tools such as Make and Airtable during the course of the AI Mastery program and during AI summit last summer (Make.com) and was immediately impressed and interested to learn more. With this project I was able to expand and apply the knowledge we gained during the AI Mastery program in regard to Airtable, Make and different AI tools, APIs etc. and to dive deeper into the specific tools. With the help of AI, and with a long trial and error process, I was able to handle even complex formulas and calculations in Airtable and Make. AI tools like ChatGPT and Claude acted as my sparring partners and guides throughout the process, helping with creating formulas in Airtable and Make and with creating the automations itself (for example by debugging errors). It’s amazing to see what is possible with AI—it enables you to learn and create things you previously knew nothing about. This is also a key take away from the AI Master Program: “Sky is the limit :)”(as Vyk always said). This makes the current times so exciting for me. The AI Mastery program as well as the project has also changed my mindset towards an "AI first" principle.

AI not only can help us master new tools and topics, but of course also becomes a powerful tool (better say tools as there so many powerful AI tools existing) itself in projects or daily live in order to increase productivity and efficiency. For me, and this is also what I learned form the AI Mastery Program, combining AI with automation is incredibly powerful and full of potential. I’m looking forward to create more projects and automations including AI tools. And I am currently "fighting" to get tools like Airtable, Make, and other AI tools implemented and used at the company I am working for.

Thank you Vyk and thanks to the whole AI Mastery program team for an amazing experience and learning. Before getting to know Mindvalley and the program, I knew nothing about Airtable and Make and now I feel very proud and thankful to be able to build such automations and Airtable “Apps” with the help of AI and with including AI tools.