How I Learned to Build Advanced Reports with Scoop and GPT (Without SQL)

How It All Started: My Plan to Build a Customer Journey Tracker

When we set out to build our marketing stack, I wanted everything centralized in Scoop—from Google Analytics and product behavior data to HubSpot CRM and Google Ads. My plan was to create a Customer Data Platform (CDP) within Scoop that tracks every step of the user journey. That meant pulling product data into Scoop, blending it with our marketing data, and creating new properties to track when users move from awareness to engagement, adoption, and eventually churn.

These calculated fields weren’t just for internal reporting. The goal was to sync them back into HubSpot so we could use them in our workflows, email campaigns, and lead scoring. Essentially, I wanted HubSpot to reflect the live data from Scoop in real time.

To do this, I:

1. Connected our HubSpot API to Scoop.
2. Used Scoop’s Google Sheets connector to bring in product event data.
3. Created calculated fields to track key moments, like when someone used our AI presentation feature.
4. Synced those calculated properties back to HubSpot to enrich contact records and trigger marketing automation.

But here’s the catch: I didn’t know how to write the spreadsheet logic to make this all work. Enter ChatGPT.

Tracking Feature Usage: My First Big Win

One of the first questions I wanted to answer was simple: How many days has it been since a user created a new canvas from one of our AI-generated presentations? But calculating "days since" using event logs isn’t as straightforward as it sounds.

The Formula:

What It Does:

• MAXIFS(): Finds the most recent time someone completed the "new canvas" event.
• NOW(): Grabs the current date and time.
• The math: Subtracts the most recent event date from the current date to give me the number of days since.
• Fallback logic: If the result is more than 1000 days (basically forever), it pulls a default value from J15.

What I Learned:

At first, I was stuck trying to build this manually. But after asking GPT how to calculate "days since the last event," I got this formula. When I added it to the calculated columns in the google sheet that I was blending hubspot and user events in Scoop and synced it back to HubSpot, I could instantly see the updated engagement data on each contact. This was a game changer—it meant I could create marketing campaigns targeting contacts who hadn’t used a feature in weeks.

Creating a Comma-Separated List of Connected Data Sources

Once I had the basics down, I got curious: What if I wanted a list of every data source a user had connected (like HubSpot, QuickBooks, and Google Analytics)? Instead of multiple rows of data, I wanted a single, clean string that looked like this:

‍The Formula:

What It Does:

• FILTER(): Pulls all data sources where the user completed an API connection.
• UNIQUE(): Removes duplicates.
• TEXTJOIN(): Joins the results into one line, separated by commas.
• IFNA(): If there’s no match, returns "No Match" instead of an error.

Why This Formula Blew My Mind:

Here’s the cool part: I didn’t even know this kind of formula was possible. When I asked GPT to help, it not only gave me the formula, but explained each function. It was a lightbulb moment—suddenly, I understood how array formulas work and how to build cleaner reports.

And because we synced this data back to HubSpot, we now had a detailed log of connected data sources for each contact. This helped us run more targeted re-engagement campaigns and even spot upsell opportunities.

‍

‍The “Aha” Moment: Spreadsheet Logic Over SQL

I’ve never used SQL, and I probably never will. I took a short class to understand the basics, but it still felt abstract. Spreadsheet logic, though? It just makes sense. I can see how each formula works step-by-step.

When I showed these formulas to Brad (Scoop’s CEO), he was impressed—especially with the array functions like FILTER() and TEXTJOIN(). These are the kinds of things that are extremely hard to replicate in SQL without complex nested queries or temporary tables.

Key Takeaways

Here’s what I learned along the way:

• Follow-up questions matter: GPT’s first answer isn’t always perfect, but asking follow-up questions helped me refine each formula.
• Arrays aren’t scary: I learned how to use functions like FILTER() and UNIQUE() to handle multiple rows of data at once.
• Spreadsheets over SQL: SQL requires nested queries and temp tables to do what this formula does in one go. Spreadsheet logic lets me see what’s happening step-by-step.
• Syncing live data to HubSpot makes everything actionable: By sending calculated fields back to HubSpot, I can trigger personalized workflows and outreach without lifting a finger.

Real-World Use Cases for These Formulas

Here’s how you can use these kinds of formulas in different roles:

• Marketing: Track ad performance and user engagement across campaigns and trigger follow-ups for inactive users.
• Customer Success: Measure time since the last user login and flag inactive accounts for re-engagement.
• Sales Ops: Summarize account activity and identify which integrations have been connected to suggest upsells.

Wrapping It Up

I’m still not a data scientist—and that’s okay. With Scoop and GPT, I’ve learned how to build reports that would normally require an SQL expert. If you’re juggling spreadsheets and trying to make sense of your data, give this a shot.

Also, if you’ve got a formula you’re proud of (or one you need help with), I’d love to see it—because honestly, learning this stuff has been pretty fun.

‍