Conversion Tracking and Upgrade Analytics

The Feature

When an organizer upgrades to the Growth plan, Marketflow records what triggered the upgrade: hitting the vendor limit, reaching the market limit, or clicking the upgrade prompt from the billing page. The developer can query these records to understand what drives conversions and calculate SaaS metrics: conversion rate, time-to-conversion, and monthly recurring revenue.

The Decision

Build conversion tracking with Stripe metadata and a simple events table in PostgreSQL. Product analytics tools (Mixpanel, Amplitude, PostHog) provide richer analysis but add complexity and cost. At fewer than 1,000 users, a SQL query against the events table provides every insight the developer needs.

The Implementation

Upgrade Event Tracking

# backend/app/models/events.py
import uuid
from datetime import datetime

from sqlalchemy import String, JSON
from sqlalchemy.orm import Mapped, mapped_column

from app.database import Base


class AnalyticsEvent(Base):
    __tablename__ = "analytics_events"

    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
    organizer_id: Mapped[uuid.UUID]
    event_type: Mapped[str] = mapped_column(String(100))
    properties: Mapped[dict] = mapped_column(JSON, default=dict)
    created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)

# backend/app/services/analytics.py
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.events import AnalyticsEvent


async def track_event(
    db: AsyncSession,
    organizer_id: str,
    event_type: str,
    properties: dict | None = None,
) -> None:
    event = AnalyticsEvent(
        organizer_id=organizer_id,
        event_type=event_type,
        properties=properties or {},
    )
    db.add(event)
    await db.commit()

Recording Upgrade Triggers

# backend/app/services/usage.py (updated)

async def check_vendor_limit(db: AsyncSession, market: Market) -> None:
    limits = get_tier_limits(market.organizer.tier)
    vendor_count = await db.scalar(
        select(func.count(Vendor.id))
        .where(Vendor.market_id == market.id)
        .where(Vendor.status == "active")
    )

    if vendor_count >= limits.max_vendors:
        # Track the limit hit event
        await track_event(
            db,
            organizer_id=str(market.organizer_id),
            event_type="vendor_limit_reached",
            properties={
                "market_id": str(market.id),
                "vendor_count": vendor_count,
                "max_vendors": limits.max_vendors,
            },
        )
        raise UsageLimitError("vendors", vendor_count, limits.max_vendors)

Stripe Checkout with Conversion Metadata

# backend/app/services/billing.py (updated)

async def create_checkout_session(
    organizer: Organizer,
    upgrade_source: str,  # "vendor_limit", "market_limit", "billing_page", "banner"
    db: AsyncSession,
) -> str:
    # Track the upgrade initiation
    await track_event(
        db,
        organizer_id=str(organizer.id),
        event_type="upgrade_initiated",
        properties={"source": upgrade_source},
    )

    session = stripe.checkout.Session.create(
        customer=organizer.stripe_customer_id,
        line_items=[{"price": settings.stripe_price_id, "quantity": 1}],
        mode="subscription",
        success_url=f"{settings.frontend_url}/settings/billing?upgraded=true",
        cancel_url=f"{settings.frontend_url}/settings/billing",
        metadata={
            "organizer_id": str(organizer.id),
            "upgrade_source": upgrade_source,
        },
    )
    return session.url

Webhook for Upgrade Completion

# backend/app/routers/webhooks.py (addition)

async def handle_checkout_completed(session, db: AsyncSession):
    organizer_id = session["metadata"]["organizer_id"]
    upgrade_source = session["metadata"].get("upgrade_source", "unknown")

    # Update tier in database
    organizer = await db.get(Organizer, organizer_id)
    organizer.tier = "growth"
    organizer.stripe_subscription_id = session["subscription"]
    await db.commit()

    # Track conversion
    await track_event(
        db,
        organizer_id=organizer_id,
        event_type="upgrade_completed",
        properties={
            "source": upgrade_source,
            "subscription_id": session["subscription"],
            "amount_cents": session["amount_total"],
        },
    )

SaaS Metrics Queries

-- Monthly Recurring Revenue (MRR)
SELECT COUNT(*) * 29 AS mrr_dollars
FROM organizers
WHERE tier = 'growth'
  AND stripe_subscription_id IS NOT NULL;

-- Conversion rate (free to paid, last 30 days)
SELECT
  (SELECT COUNT(*) FROM analytics_events
   WHERE event_type = 'upgrade_completed'
     AND created_at > NOW() - INTERVAL '30 days') AS conversions,
  (SELECT COUNT(*) FROM organizers
   WHERE created_at > NOW() - INTERVAL '30 days') AS signups,
  ROUND(
    (SELECT COUNT(*) FROM analytics_events
     WHERE event_type = 'upgrade_completed'
       AND created_at > NOW() - INTERVAL '30 days')::numeric
    / NULLIF((SELECT COUNT(*) FROM organizers
              WHERE created_at > NOW() - INTERVAL '30 days'), 0) * 100,
    1
  ) AS conversion_rate_percent;

-- Average time to conversion (days from signup to upgrade)
SELECT AVG(
  EXTRACT(EPOCH FROM (e.created_at - o.created_at)) / 86400
) AS avg_days_to_conversion
FROM analytics_events e
JOIN organizers o ON o.id = e.organizer_id
WHERE e.event_type = 'upgrade_completed';

-- Upgrade sources breakdown
SELECT
  properties->>'source' AS upgrade_source,
  COUNT(*) AS conversions
FROM analytics_events
WHERE event_type = 'upgrade_completed'
GROUP BY properties->>'source'
ORDER BY conversions DESC;

-- Churn: subscriptions cancelled in the last 30 days
SELECT COUNT(*) AS churned
FROM analytics_events
WHERE event_type = 'subscription_cancelled'
  AND created_at > NOW() - INTERVAL '30 days';

The Trap

# TRAP: Tracking every user action for "future analysis"
await track_event(db, organizer_id, "page_viewed", {"page": "/dashboard"})
await track_event(db, organizer_id, "button_hovered", {"button": "add-vendor"})
await track_event(db, organizer_id, "tab_switched", {"from": "vendors", "to": "markets"})
# The events table grows to millions of rows
# Queries slow down
# Nobody ever analyzes the hover events

# SAFE: Track only events that inform decisions
# - Limit reached (tells you the free tier boundary is correct)
# - Upgrade initiated (tells you where users click to upgrade)
# - Upgrade completed (tells you the conversion rate)
# - Subscription cancelled (tells you the churn rate)
# 4 event types instead of 40

Track events that answer specific questions. “What percentage of free users upgrade?” “What triggers the upgrade?” “How long does it take?” “Why do they cancel?” Each event type should map to a question the developer needs to answer. Events without a corresponding question are noise.

The Cost

Component	Cost
PostgreSQL events table	~100 bytes per event
Analytics queries	Run manually via psql or Supabase SQL editor
Stripe metadata	$0 (included in Stripe)

At 100 conversions per month with 4 event types each, the events table grows by approximately 400 rows (40 KB) per month. After a year, that is 4,800 rows. PostgreSQL handles this without any performance consideration.