Case Study: Noteefy Analytics

The new architecture centered around Snowflake, with MongoDB data replicated into Snowflake using Estuary. A Python FastAPI microservice sat between the frontend and Snowflake, providing a stable API boundary with request validation, response formatting, and error handling so the React dashboard never needed to know how the analytics layer worked. This created a cleaner separation of concerns. Snowflake handled the heavy analytics work. FastAPI provided a stable application boundary. React handled filtering, layout, visualization, reporting, and user experience.

Throughout implementation, I gathered context and feedback from Noteefy's Engineering team and joined their daily scrums to stay aligned on data availability, API contracts, and integration timing.

Moving analytics into Snowflake gave Noteefy a dedicated environment where expensive reporting queries could run without relying directly on the operational MongoDB database. Once the data was available in Snowflake, the next challenge was deciding how to expose it to the dashboard.

Views were not flexible enough because the dashboard needed to pass in dynamic parameters like start date, end date, and selected course IDs. Snowflake functions were also not the right fit. Scalar functions were useful for small reusable transformations, but they could not represent an entire dashboard response. Table functions were closer, but still awkward because each dashboard needed to return multiple kinds of data at once: KPI values, chart series, nested objects, product-specific metric groups, and report-ready structures.

Stored procedures gave me the flexibility I needed. They allowed me to accept parameters, run multi-step SQL logic, reuse intermediate result sets, build structured response objects, and return dashboard-ready payloads. Instead of duplicating calculations across MongoDB Charts, API routes, and React components, the most important business logic lived in one analytics layer.

Testing the stored procedures was its own challenge. Unit testing SQL logic in Snowflake was not straightforward, so I built integration tests that ran the full procedures against known datasets and validated the response payloads. This approach fit cleanly into the CI/CD workflow and gave me confidence that metric calculations stayed correct as the procedures evolved.

A key challenge was deciding how the stored procedure responses and API payloads should be structured. I decided early on that the response shape should match all the way from Snowflake through FastAPI to the React frontend, so that each layer could pass the data through without restructuring it.

Each product got its own response contract. Waitlist focused on demand and recovered opportunity: searches, golfer signups, recaptured revenue, and referrer attribution. Confirm focused on operational visibility and booking protection: text confirmations, communication demand, and rounds recaptured. Platform had its own set of metrics that brought the larger story together across products, communicating overall value without double-counting activity or flattening product-specific metrics into something less meaningful.

On the Platform overview page, the stored procedure actually fetches all product metrics, but the dashboard only surfaces the top-level KPIs for each product in side-by-side panes.

platform overview with product-specific metric panes

Many of the metrics already existed in some form through MongoDB Charts, but they lacked standardization. I worked with Product to define the exact metric definitions and with Engineering to validate the formulas against the underlying data.

That standardization process surfaced real complexity. The hardest problem was deduplication across products. Waitlist and Confirm were not independent data sources. They were part of the same user flow, but without explicit linkage in the data. A golfer who found a tee time through Waitlist might later confirm that same booking through Confirm. Both products would record activity for the same round, but there was no shared booking ID connecting them. At the same time, a different golfer might book the same tee time through a regular channel and also confirm through Confirm. The data was stored in nested documents, and the challenge was ensuring that overlapping bookings were not double-counted while still correctly summing spend across distinct golfers for the same tee time.

That kind of detail mattered because the dashboard was being used to make real business claims. A polished chart was not enough. The metrics had to be credible.

Performance was the most critical part of the project, as the analytics experience would be the first thing operators saw when they logged in. It needed to be fast and reliable for the largest multi-course operators.

The optimization goal was clear: page load time for lifetime metrics for the largest multi-course operator on each dashboard.

In the original MongoDB Charts implementation, individual charts loaded independently. Some metrics loaded in a few seconds, while others could take multiple minutes depending on the chart and the size of the operator.

After the first pass of the Snowflake stored procedure implementation, the dashboard experience was more unified, but it still was not fast enough. The initial stored procedure performance was roughly 5 seconds per course, which meant the query still scaled too linearly as more courses were added.

A lot of the optimization involved SQL query tuning. Here are a few representative tactics.

Because the source records retained their MongoDB document structure, fields like prices, timestamps, referrers, and entity names were stored as nested paths. The original queries passed raw records through the base CTE and re-extracted and cast those fields in every downstream branch that needed them:

-- Before: base CTE passes raw records through
base_events AS (
  SELECT e.*
  FROM events e
  JOIN selected_groups g ON e.group_id = g.group_id
),

-- Each downstream CTE re-extracts and casts the same fields
revenue_total AS (
  SELECT
    SUM((e."event_data/amount")::NUMBER) AS total_revenue
  FROM base_events e
  WHERE (e."event_data/event_time")::TIMESTAMP >= :start_ts
    AND (e."event_data/event_time")::TIMESTAMP <= :end_ts
),

revenue_by_source AS (
  SELECT
    (e."source_data/source_name")::STRING AS source,
    SUM((e."event_data/amount")::NUMBER) AS revenue
  FROM base_events e
  WHERE (e."event_data/event_time")::TIMESTAMP >= :start_ts
    AND (e."event_data/event_time")::TIMESTAMP <= :end_ts
  GROUP BY source
)

The fix was to project all needed fields as typed columns once in the base CTE, so every downstream reference was a simple column read:

-- After: base CTE projects typed columns once
base_events AS (
  SELECT
    e.group_id,
    e.created_at,
    (e."event_data/amount")::NUMBER AS amount,
    (e."event_data/event_time")::TIMESTAMP AS event_time,
    LOWER(TRIM(e."user_data/email"::STRING)) AS user_email,
    UPPER(TRIM(e."source_data/source_name"::STRING)) AS source_name
  FROM events e
  JOIN selected_groups g ON e.group_id = g.group_id
),

-- Downstream CTEs use typed columns directly
revenue_total AS (
  SELECT SUM(e.amount) AS total_revenue
  FROM base_events e
  WHERE e.event_time >= :start_ts AND e.event_time <= :end_ts
),

revenue_by_source AS (
  SELECT e.source_name, SUM(e.amount) AS revenue
  FROM base_events e
  WHERE e.event_time >= :start_ts AND e.event_time <= :end_ts
  GROUP BY e.source_name
)

This eliminated redundant JSON-path extraction and casting across every branch of the query. In stored procedures with many metric branches, the savings compounded significantly.

The monthly trend logic generated a list of month windows and then joined raw event data into those windows. That meant the query was fitting every row into a range predicate against generated month boundaries:

-- Before: generate month windows, join raw events into them
months AS (
  SELECT
    DATEADD(MONTH, -seq, DATE_TRUNC(MONTH, :end_ts)) AS month_start,
    DATEADD(MONTH, 1,
      DATEADD(MONTH, -seq, DATE_TRUNC(MONTH, :end_ts))) AS month_end
  FROM TABLE(GENERATOR(ROWCOUNT => :num_months)) g
),

events_by_month AS (
  SELECT
    m.month_start,
    (e."event_data/amount")::NUMBER AS amount
  FROM months m
  JOIN events e
    ON (e."event_data/event_time")::TIMESTAMP >= m.month_start
   AND (e."event_data/event_time")::TIMESTAMP <  m.month_end
)

The rewrite derived the month bucket directly from each event's timestamp, aggregated once by month, and then joined to the month list at the end to preserve the output shape:

-- After: derive month from events, aggregate once, join for shape
months AS (
  SELECT
    DATEADD(MONTH, -seq, DATE_TRUNC(MONTH, :end_ts)) AS month_start
  FROM TABLE(GENERATOR(ROWCOUNT => :num_months)) g
),

monthly_totals AS (
  SELECT
    DATE_TRUNC(MONTH, event_time) AS month_start,
    SUM(amount) AS total
  FROM base_events
  WHERE event_time >= :start_ts AND event_time <= :end_ts
  GROUP BY 1
),

trend AS (
  SELECT m.month_start, COALESCE(t.total, 0) AS total
  FROM months m
  LEFT JOIN monthly_totals t ON t.month_start = m.month_start
)

This changed the work from fitting raw rows into generated windows to deriving the month once and aggregating in a single pass.

Some metric paths involved two separate uniqueness-heavy operations. One user counting path first deduplicated records with GROUP BY and MIN(created_at) to find each user's earliest entry, then later counted with COUNT(DISTINCT user_id):

-- Before: GROUP BY + MIN for dedup, then COUNT(DISTINCT) later
users_all AS (
  SELECT
    group_id,
    user_id,
    MIN(created_at) AS first_seen
  FROM base_events
  GROUP BY 1, 2
),

active_users_total AS (
  SELECT CAST(COUNT(DISTINCT user_id) AS NUMBER) AS active_users
  FROM users_in_window
)

After refining the metric definition with Product, the underlying requirement shifted enough that a window function could handle both the deduplication and the ordering in one pass:

-- After: single window function dedupes and preserves earliest row
users_first AS (
  SELECT group_id, user_id, created_at AS first_seen
  FROM (
    SELECT group_id, user_id, created_at,
      ROW_NUMBER() OVER (
        PARTITION BY group_id, user_id ORDER BY created_at
      ) AS rn
    FROM base_events
    WHERE user_id IS NOT NULL
  )
  WHERE rn = 1
),

active_users_total AS (
  SELECT CAST(COUNT(*) AS NUMBER) AS active_users
  FROM users_in_window
)

This reduced two uniqueness-heavy operations to one.

Redis caching was added to the FastAPI microservice to cache frequently requested metrics. This reduced the load time for the most popular metrics and helped keep the page responsive even when the database was under heavy load. However, this was not enough to meet the performance goal. Another option was to precompute lifetime metrics, but this would only enhance the initial load time without addressing the scaling problem.

Because the source data came from MongoDB, many of the records synced into Snowflake retained their document structure with nested arrays and embedded objects. LATERAL FLATTEN was the natural way to work with this data in SQL, and the first implementation leaned on it heavily. That approach was correct for getting the metrics right, but it created performance problems that compounded at scale. We were able to avoid a few of these cases by refining metric definitions, but it wasn't enough to meet the performance goal. For cases where lateral flattening was still genuinely needed, I worked with the team to move that work out of the stored procedures entirely. By leveraging the Estuary data pipeline and writing trigger functions in Snowflake, I could flatten the nested document structures once at sync time rather than on every dashboard request. That way the stored procedures could query against pre-flattened tables without paying the flattening cost at read time.

The production queries involved more metric branches and nested output structures, but the same principles applied across all of the optimization work.

The integration tests I had built for the stored procedures were critical throughout this process. Each optimization pass changed how intermediate results were computed, and the tests ensured that the final metric values stayed accurate through every iteration.

Not all of the improvements were pure SQL. Some of the most impactful changes came from refining the metric requirements with Product, which eliminated unnecessary calculations entirely.

The largest benchmark was lifetime metric loading for the largest multi-course operator. Before the reimplementation, individual MongoDB Charts loaded independently and could range from a few seconds to multiple minutes depending on the metric. The first iteration of the stored procedures unified the experience but still scaled linearly with course count. After the optimization work, the major dashboards loaded in seconds.

In prose: Platform dropped from roughly sixty seconds to twelve. Waitlist dropped from roughly ninety seconds to twenty. Confirm dropped from roughly forty-five seconds to eight. The largest multi-course operator went from staring at a loading spinner to seeing meaningful data fast enough to use it during a live customer conversation.

The frontend was rebuilt as a custom React dashboard experience instead of a collection of embedded MongoDB Charts.

This gave me more control over layout, filtering, visual hierarchy, loading states, chart behavior, report generation, and product consistency.

The new dashboard experience included:

• KPI cards for headline metrics
• Product-specific dashboard sections
• Universal date and course filtering
• Reusable chart and table components
• MUI X Charts for visualizations
• Loading and empty states
• A unified structure across Platform, Waitlist, Confirm, and AI Assistant
• PDF report downloads
• A homepage-style admin experience that immediately showed operators the value Noteefy was creating

The AI Assistant dashboard was already implemented differently, so that work was more about bringing it into the same admin UI structure. Platform, Waitlist, and Confirm were the primary analytics reimplementation targets.

The Platform dashboard opened with headline KPI cards for total golfers served and total economic impact, then broke down product-specific metrics for Waitlist, Confirm, and AI Assistant side by side.

platform performance and product analytics

The Waitlist dashboard surfaced revenue recaptured, engaged users, and searches created as top-level KPIs, with deeper views into confirmed booking revenue by lead time, search demand across time slots, referrer attribution, and golfer engagement metrics.

waitlist KPIs and confirmed bookings revenue by lead time

search demand crystal ball, 0-30 and 30-90 day windows

traffic by referrer and engagement metrics

The Confirm dashboard led with revenue recaptured, rounds recaptured, and confirmations sent, then provided communication demand breakdowns by booking status and cancellation patterns by days before tee time.

confirm KPIs and communication crystal ball

cancellations by days before tee time and engagement metrics

The AI Assistant dashboard tracked conversation volume, session duration, calls saved, and topic distribution, along with session ratings and booking engagement metrics.

conversations, topics discussed, and engagement metrics

session ratings

Universal filtering was one of the most important UX improvements.

Instead of having each chart behave like its own separate report, the dashboard let users select a date range and course group once, then see the entire dashboard update around that context.

universal filtering: operator, course, and date selection

This was especially important for multi-course operators. A user could view one course, a subset of courses, or the full operator account without switching between disconnected reports.

It also made internal reporting easier. Sales and customer success teams could prepare a focused view for a customer conversation, then export that view into a report.

The backend complexity was hidden from the user. Different metrics used different event timestamps, but the UI presented one simple filtering experience.

That made the dashboard feel much more like a product and less like a reporting tool.

The PDF report feature turned the dashboard into a sales and customer success asset.

Reports were generated on the client using a custom function built with dom-to-image, pdfmake, and additional export logic.

That custom logic handled details like:

• Capturing dashboard sections as report-ready images
• Hiding or replacing interactive elements that did not belong in a PDF
• Preserving the selected filter context
• Anonymizing sensitive data when needed
• Formatting the report so it could be shared outside the live admin interface

This mattered because Noteefy wanted the dashboard to support both internal teams and external operators. The live dashboard helped users explore the data, while the PDF report helped turn that data into a shareable business review.

Each report below is a PDF snapshot of a dashboard filtered to a specific product. They include the same KPI cards, charts, and breakdowns visible in the live dashboard, formatted for print and external sharing with Noteefy branding applied.