Comprehensive Guide: Tracking Ad Performance with Make.com for a Gym Owner

As a business owner, keeping track of new leads and potential revenue from various ad sources can be a daunting task, especially when done manually. Good news! With Make.com (formerly Integromat), we can automate these processes, saving both time and energy, and ensuring accuracy. Let's walk through how an automated system can handle weekly tracking for new gym members and potential revenue.

Overview of the Automation Scenario

The automation described here leverages Make.com to:

1. Query GoHighLevel for contacts added within the past week.
2. Filter and segregate contacts by their ad sources.
3. Check VirtualGym (the gym's management software) to see if these contacts have signed up.
4. Fetch training sessions data from GoHighLevel to understand lead behavior.
5. Aggregate this data into a Google Sheets document.

This robust system runs every week, providing a detailed breakdown of leads, consultations booked, sign-ups, and potential revenue from various source platforms like Google Ads, Meta (Facebook), and referrals.

Key Steps in the Automation

Get Contacts from GoHighLevel

• The automation kicks off by making an HTTP request to GoHighLevel to fetch new contacts added within the past week.
• GET https://services.leadconnectorhq.com/contacts/ {query parameters for date and location}

Iterate Over Contacts

• The fetched contacts are then iterated over, and each contact’s source is formatted to lowercase for consistency.
• The sources are then aggregated into variables such as GooglePaidEmails or GoogleOrganicIDs.

Router: Segregate by Source

• This is the heart of the automation where the contacts are divided into different paths based on their source.

Check VirtualGym Sign-Up Status

• For each source, check in VirtualGym whether the contact has an active membership. This ensures that leads that turned into paying members are tracked.
• Query: {VirtualGym API for checking active membership status}.

Query GoHighLevel for Consultations

• For leads, check GoHighLevel to see if they have booked any consultations within the past week.
• Query: GET https://services.leadconnectorhq.com/contacts/{contact_id}/appointments.

Update Google Sheets

• After aggregating the necessary data, update the predefined cells in Google Sheets with the collected information like new leads, consultations booked, no-show, cancellations, actual sign-ups, and potential revenue.

While setting up the automation, we encountered a couple of significant challenges that highlighted some limitations within the platform and the client's specific data requirements.

Challenges Faced During the Automation Setup

1. Limitations with the map Function in Make.com

One of the hurdles we faced was the restricted functionality of the map function in Make.com. The map function in Make.com allows for filtering complex arrays, but it has notable limitations:

• Equating Two Values: The map function only allows filtering by equating two values. This means that we couldn't use more complex Boolean functions as we would in standard programming languages.
• Complex Arrays Handling: Due to these limitations, handling complex arrays required us to create multiple branching paths for various conditions. Each source of contacts had to be checked against specific criteria, making the scenario appear more convoluted and less efficient than it ideally could be.

Resultantly, this led to what can be seen as "ugly branching," where the workflow has a higher number of conditional paths than is typically necessary. Although the automation works perfectly, these branches add visual and logic complexity to the scenario.

2. Updating a Column in Google Sheets

Another significant challenge was tied to the client's specific data setup. The client had their data organized vertically, meaning a new column was required each week with the updated data rather than updating a row. This setup presented the following issues:

• Google Sheets API Limitation: Google Sheets does not offer a direct API endpoint to update a column. This required us to individually update each cell in the column.
• Inefficient Workflow: To work around this limitation, we had to create multiple branches in the automation to update each cell of the new column individually. While effective, it resulted in a visually cluttered and less straightforward flow.

One workaround to this would have been to use an API batch request. We ultimately decided it was not worth the increased complexity. We also offered to transpose the sheet but the client decided to keep it how it was.

3. Converting Column Number to Column Letter in Google Sheets

A final challenge involved needing a column letter when adding data to Google Sheets:

• Column Letter Requirement: Google Sheets requires a letter for the column being targeted. We needed to calculate which column to update by identifying the last filled column and then convert its number to a letter.
• Conversion Process: We accomplished this by fetching the first row of the sheet, delimiting it by commas, removing non-empty values, and calculating the length of the resulting array to determine the column number. We then used a custom function to convert this number to a column letter, handling up to three-letter columns.

Benefits of This Automation

• Time Savings: What used to take hours every week has been reduced to a scheduled automation that works while you focus on your core business.
• Accurate Data: Eliminates human error in manually tracking and calculating leads and sign-ups.
• Insightful Reporting: Weekly, automated updates ensure you always have the latest data at your fingertips.

Tools Used

• Make.com: The primary automation tool to create these workflows.
• GoHighLevel: For managing and tracking lead data.
• Google Sheets: For aggregating and visualizing the data.
• VirtualGym: For gym membership management.

Ready to set up your automation? Visit Make.com to get started. If you need assistance, feel free to email us or hire us from Upwork.

Happy automating!🚀