Connecting Questionnaire and Booking Data in One Lead Tracker

1. The Problem: Data Lives in Too Many Places

Artist Alignment Academy's workflow looked like this: a potential lead fills out a Typeform questionnaire, then books a call through Calendly. Both platforms capture useful data like names, emails, time zones, and UTM parameters. But none of it talked to each other.

The team was manually copying details into a master Google Sheet. Rows had gaps. Some leads had a name but no call date. Others had UTM tracking but no time zone. It was time-consuming and error-prone.

The goal was one tracker that stays current with less copying.

2. Matching Records, Not Importing Everything

Instead of dumping raw data into the sheet, the script starts by scanning every existing row to see what is already there and what is missing.

• ●It builds an email index of all existing contacts in the sheet
• ●It checks which fields are empty: name, call date, time zone, UTM source
• ●It only fetches data from Typeform or Calendly when it's needed
• ●It matches records by email address, which avoids duplicates when the same email is used in both tools

This pre-scan approach means the script is efficient. It doesn't waste API calls pulling data it won't use.

3. Pulling Data from Typeform

The script connects directly to the Typeform API and paginates through all form submissions since a configurable cutoff date. For each response, it extracts:

• ●The respondent's name and email
• ●When the form was submitted
• ●UTM parameters (source, medium, campaign, term) from hidden fields

These details get stored in a lookup table keyed by email. Later, the script checks the master sheet and fills any gaps using this data.

4. Pulling Data from Calendly

Calendly works similarly but reveals different information. The script authenticates with a personal access token, identifies the correct event type (in this case, a 60-minute consultation), and retrieves all scheduled events.

For each event, it pulls the invitee's name, email, call date, booking date, and time zone. This fills in the fields that Typeform can't, especially the scheduled call date. The time zone comes from the booking and is a rough guide, not a verified location.

Between the two platforms, the tracker gets whichever fields each tool holds, with much less copying by hand.

5. Two-Pass Logic: Fix Existing Rows, Then Add New Ones

The sync runs in two deliberate passes:

Pass 1: Fill missing data. The script walks through every existing row in the sheet. If a row has an email but is missing a name, it checks Calendly first, then Typeform. If the call date is empty, it pulls from Calendly. If UTM data is blank, Typeform fills it in. Each field is filled from whichever source has it.

Pass 2: Add new leads. Any Typeform submission with an email that doesn't exist in the sheet yet gets added as a brand-new row. The script also cross-references Calendly to see if that person has already booked a call, so the new row starts as complete as possible.

6. A Scheduled Sync with Maintenance Responsibilities

The script runs on a five-minute schedule, using a time-based trigger in Google Apps Script, to retrieve available information, match records by email and update the tracker. It logs recoverable API errors for review. Missing source data, changed email addresses or changes to the connected services can still require attention.

The delivered workflow reduces routine copying between Typeform, Calendly and Google Sheets. Its purpose is to keep the tracker more current while giving the team a record of issues that need investigation. If an API is temporarily down or a rate limit is hit, the script logs the issue and the next run tries again.

7. What This Means for the Business

• ●Less copying between platforms
• ●Fewer incomplete rows, where the source tools hold the data
• ●UTM values carried into the tracker for marketing analysis
• ●New leads appear in the sheet on the next scheduled run after a form submission
• ●Less time on data entry, with a log of items to check

Before the sync, the team put the daily copying at about 30 minutes. That figure is their estimate.