Product-Led Growth Engineering: Activation Tracking, Viral Loops, and Freemium Gates

Product-Led Growth (PLG) means the product itself drives acquisition, retention, and expansion — not just the sales team. The engineering investment is in features that create genuine value, make sharing frictionless, and convert free users at the moment they're most engaged.

The difference between PLG that works and PLG that doesn't is almost always in the measurement. If you can't measure where users drop out of activation, you can't fix it.

---

The PLG Engineering Stack

Acquisition Layer:   Referral links → viral coefficient tracking
Activation Layer:    Onboarding funnel → time-to-value measurement  
Retention Layer:     Feature flags → engagement tracking → churn signals
Expansion Layer:     Usage limits → upgrade prompts → seat growth

---

Activation Funnel Tracking

The activation funnel is the sequence of steps between signup and "aha moment" — the point where users understand the product's core value.

// src/services/activation/funnel-tracker.ts

// Define your activation steps as an ordered enum
// These should represent genuine value moments, not arbitrary checkboxes
export const ACTIVATION_STEPS = [
  "account_created",
  "profile_completed",
  "first_project_created",
  "first_collaborator_invited",
  "first_integration_connected",
  "first_export_completed",        // The "aha moment" for most products
] as const;

export type ActivationStep = (typeof ACTIVATION_STEPS)[number];

interface ActivationEvent {
  userId: string;
  tenantId: string;
  step: ActivationStep;
  completedAt: Date;
  metadata?: Record<string, string | number>;
}

// Track an activation step — idempotent
export async function trackActivationStep(
  event: ActivationEvent
): Promise<void> {
  // Upsert to avoid duplicate tracking on retries
  await db.query(
    `INSERT INTO activation_events
     (user_id, tenant_id, step, completed_at, metadata)
     VALUES ($1, $2, $3, $4, $5)
     ON CONFLICT (user_id, step) DO NOTHING`,
    [
      event.userId,
      event.tenantId,
      event.step,
      event.completedAt,
      JSON.stringify(event.metadata ?? {}),
    ]
  );

  // Update cached activation score for real-time gating decisions
  await updateActivationScore(event.userId);

  // Fire downstream events (email sequences, sales alerts for high-value users)
  await publishActivationEvent(event);
}

// Get user's current activation state
export async function getActivationState(userId: string): Promise<{
  completedSteps: ActivationStep[];
  nextStep: ActivationStep | null;
  activationScore: number; // 0-100
  isActivated: boolean;    // Reached the "aha moment"
}> {
  const { rows } = await db.query<{ step: ActivationStep }>(
    "SELECT step FROM activation_events WHERE user_id = $1",
    [userId]
  );

  const completedSteps = rows.map((r) => r.step);
  const completedSet = new Set(completedSteps);

  const nextStep =
    ACTIVATION_STEPS.find((step) => !completedSet.has(step)) ?? null;

  const activationScore = Math.round(
    (completedSteps.length / ACTIVATION_STEPS.length) * 100
  );

  // "Activated" = completed the aha moment step
  const ahaStep: ActivationStep = "first_export_completed";
  const isActivated = completedSet.has(ahaStep);

  return { completedSteps, nextStep, activationScore, isActivated };
}

Funnel Analysis SQL

-- Activation funnel: how many users complete each step?
WITH cohort AS (
  SELECT DISTINCT user_id
  FROM users
  WHERE created_at >= NOW() - INTERVAL '30 days'
),
step_completion AS (
  SELECT
    ae.step,
    COUNT(DISTINCT ae.user_id) AS completed_count
  FROM activation_events ae
  JOIN cohort c ON c.user_id = ae.user_id
  GROUP BY ae.step
)
SELECT
  s.step,
  COALESCE(sc.completed_count, 0) AS completed,
  (SELECT COUNT(*) FROM cohort) AS total_users,
  ROUND(
    100.0 * COALESCE(sc.completed_count, 0) / (SELECT COUNT(*) FROM cohort),
    1
  ) AS completion_pct
FROM (
  SELECT UNNEST(ARRAY[
    'account_created', 'profile_completed', 'first_project_created',
    'first_collaborator_invited', 'first_integration_connected',
    'first_export_completed'
  ]::TEXT[]) AS step,
  GENERATE_SERIES(1, 6) AS step_order
) s
LEFT JOIN step_completion sc ON sc.step = s.step
ORDER BY s.step_order;

-- Time-to-activation: how long does it take users to reach the aha moment?
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (ae.completed_at - u.created_at)) / 3600
  ) AS p50_hours_to_activation,
  PERCENTILE_CONT(0.9) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (ae.completed_at - u.created_at)) / 3600
  ) AS p90_hours_to_activation,
  COUNT(*) AS activated_users
FROM activation_events ae
JOIN users u ON u.id = ae.user_id
WHERE ae.step = 'first_export_completed'
  AND ae.completed_at >= NOW() - INTERVAL '30 days';

---

Viral Loop Engineering

A viral loop is a mechanism where existing users naturally refer new users as a side effect of using the product. The engineering goal: measure and optimize the viral coefficient (K-factor).

K-factor = (invitations sent per user) × (conversion rate of invitations)
K > 1: Exponential growth
K = 0.5-1: Supplement acquisition
K < 0.5: Not adding meaningful virality

Referral System

// src/services/referral/referral.service.ts
import { customAlphabet } from "nanoid";

const generateReferralCode = customAlphabet(
  "ABCDEFGHJKLMNPQRSTUVWXYZ23456789", // No confusable chars
  8
);

interface ReferralLink {
  code: string;
  referrerId: string;
  url: string;
  createdAt: Date;
}

export async function createReferralLink(userId: string): Promise<ReferralLink> {
  // Idempotent: each user gets one referral code
  const { rows } = await db.query<{ code: string; created_at: Date }>(
    `INSERT INTO referral_codes (user_id, code)
     VALUES ($1, $2)
     ON CONFLICT (user_id) DO UPDATE SET user_id = referral_codes.user_id
     RETURNING code, created_at`,
    [userId, generateReferralCode()]
  );

  const code = rows[0].code;
  return {
    code,
    referrerId: userId,
    url: `${process.env.NEXT_PUBLIC_APP_URL}/signup?ref=${code}`,
    createdAt: rows[0].created_at,
  };
}

// Track referral conversion
export async function processReferral(
  referralCode: string,
  newUserId: string
): Promise<void> {
  const { rows } = await db.query<{ user_id: string }>(
    "SELECT user_id FROM referral_codes WHERE code = $1",
    [referralCode]
  );

  if (rows.length === 0) return; // Invalid code — ignore

  const referrerId = rows[0].user_id;

  // Record the referral
  await db.query(
    `INSERT INTO referrals (referrer_id, referred_user_id, referral_code, referred_at)
     VALUES ($1, $2, $3, NOW())
     ON CONFLICT (referred_user_id) DO NOTHING`, // Each user referred at most once
    [referrerId, newUserId, referralCode]
  );

  // Schedule reward when referred user activates (not on signup — prevents abuse)
  await scheduleReferralRewardCheck(referrerId, newUserId);
}

// Grant rewards when referral activates
export async function checkAndGrantReferralReward(
  referrerId: string,
  referredUserId: string
): Promise<void> {
  const activationState = await getActivationState(referredUserId);

  if (!activationState.isActivated) return; // Not yet activated

  // Check if reward already granted
  const { rows } = await db.query(
    "SELECT id FROM referral_rewards WHERE referrer_id = $1 AND referred_user_id = $2",
    [referrerId, referredUserId]
  );

  if (rows.length > 0) return; // Already rewarded

  // Grant reward: 30 days free for referrer, 14 days free for referred
  await Promise.all([
    extendTrialDays(referrerId, 30),
    extendTrialDays(referredUserId, 14),
    db.query(
      `INSERT INTO referral_rewards (referrer_id, referred_user_id, reward_type, granted_at)
       VALUES ($1, $2, 'trial_extension', NOW())`,
      [referrerId, referredUserId]
    ),
  ]);

  // Notify referrer
  await sendReferralRewardEmail(referrerId, { extraDays: 30 });
}

K-Factor Measurement

-- Viral coefficient (K-factor) for last 30 days
WITH senders AS (
  -- Users who sent at least one invitation
  SELECT referrer_id, COUNT(DISTINCT referred_user_id) AS invites_sent
  FROM referrals
  WHERE referred_at >= NOW() - INTERVAL '30 days'
  GROUP BY referrer_id
),
conversions AS (
  -- Invitations that resulted in active users (completed signup)
  SELECT r.referrer_id, COUNT(*) AS converted
  FROM referrals r
  JOIN users u ON u.id = r.referred_user_id
  WHERE r.referred_at >= NOW() - INTERVAL '30 days'
    AND u.status = 'active'
  GROUP BY r.referrer_id
)
SELECT
  ROUND(AVG(s.invites_sent), 2) AS avg_invites_per_referrer,
  ROUND(
    100.0 * SUM(COALESCE(c.converted, 0)) / NULLIF(SUM(s.invites_sent), 0),
    1
  ) AS invitation_conversion_pct,
  ROUND(
    AVG(s.invites_sent) * (SUM(COALESCE(c.converted, 0))::float / NULLIF(SUM(s.invites_sent), 0)),
    3
  ) AS k_factor
FROM senders s
LEFT JOIN conversions c USING (referrer_id);

---

Freemium Gate Engineering

Freemium gates convert free users to paid by limiting access to high-value features. The engineering challenge: gates that motivate conversion without feeling punitive.

// src/services/limits/plan-gate.service.ts

interface PlanLimits {
  maxProjects: number;
  maxTeamMembers: number;
  maxExportsPerMonth: number;
  maxStorageGb: number;
  hasApiAccess: boolean;
  hasCustomDomain: boolean;
  hasPrioritySupport: boolean;
}

const PLAN_LIMITS: Record<string, PlanLimits> = {
  free: {
    maxProjects: 3,
    maxTeamMembers: 1,         // Solo only — inviting = upgrade trigger
    maxExportsPerMonth: 5,
    maxStorageGb: 1,
    hasApiAccess: false,
    hasCustomDomain: false,
    hasPrioritySupport: false,
  },
  starter: {
    maxProjects: 10,
    maxTeamMembers: 5,
    maxExportsPerMonth: 50,
    maxStorageGb: 10,
    hasApiAccess: false,
    hasCustomDomain: false,
    hasPrioritySupport: false,
  },
  growth: {
    maxProjects: Infinity,
    maxTeamMembers: 25,
    maxExportsPerMonth: Infinity,
    maxStorageGb: 100,
    hasApiAccess: true,
    hasCustomDomain: true,
    hasPrioritySupport: false,
  },
};

export interface GateCheckResult {
  allowed: boolean;
  limit: number | boolean;
  current: number | boolean;
  upgradeReason?: string;
  upgradeUrl: string;
}

export async function checkPlanGate(
  tenantId: string,
  plan: string,
  action:
    | "create_project"
    | "invite_member"
    | "export"
    | "use_api"
    | "custom_domain"
): Promise<GateCheckResult> {
  const limits = PLAN_LIMITS[plan] ?? PLAN_LIMITS.free;
  const upgradeUrl = `${process.env.NEXT_PUBLIC_APP_URL}/billing/upgrade`;

  switch (action) {
    case "create_project": {
      const { rows } = await db.query<{ count: string }>(
        "SELECT COUNT(*)::text FROM projects WHERE tenant_id = $1 AND is_deleted = false",
        [tenantId]
      );
      const current = parseInt(rows[0].count);
      const allowed = current < limits.maxProjects;
      return {
        allowed,
        limit: limits.maxProjects,
        current,
        upgradeReason: allowed
          ? undefined
          : `You've reached the ${limits.maxProjects}-project limit on the ${plan} plan. Upgrade to create unlimited projects.`,
        upgradeUrl,
      };
    }

    case "invite_member": {
      const { rows } = await db.query<{ count: string }>(
        "SELECT COUNT(*)::text FROM team_members WHERE tenant_id = $1 AND status = 'active'",
        [tenantId]
      );
      const current = parseInt(rows[0].count);
      const allowed = current < limits.maxTeamMembers;
      return {
        allowed,
        limit: limits.maxTeamMembers,
        current,
        upgradeReason: allowed
          ? undefined
          : `Team collaboration requires a paid plan. Upgrade to invite team members.`,
        upgradeUrl,
      };
    }

    case "use_api": {
      return {
        allowed: limits.hasApiAccess,
        limit: limits.hasApiAccess,
        current: limits.hasApiAccess,
        upgradeReason: limits.hasApiAccess
          ? undefined
          : "API access is available on the Growth plan and above.",
        upgradeUrl,
      };
    }

    default:
      return { allowed: true, limit: Infinity, current: 0, upgradeUrl };
  }
}

Upgrade Prompt Timing

The timing of upgrade prompts matters as much as the copy:

// src/hooks/useUpgradePrompt.ts
"use client";

import { useState, useCallback } from "react";
import { useQuery } from "@tanstack/react-query";

// Show upgrade prompt BEFORE blocking, not AFTER
// "You've used 4 of 5 exports this month" is better than
// "You've hit your limit — upgrade to continue"
export function useProactiveUpgradePrompt(
  tenantId: string,
  action: string
) {
  const { data: gateStatus } = useQuery({
    queryKey: ["gate", tenantId, action],
    queryFn: () => checkGate(tenantId, action),
    staleTime: 1000 * 60 * 5, // Check every 5 minutes
  });

  // Show warning at 80% of limit
  const isApproachingLimit =
    typeof gateStatus?.limit === "number" &&
    typeof gateStatus?.current === "number" &&
    gateStatus.current / gateStatus.limit >= 0.8;

  const isAtLimit = gateStatus?.allowed === false;

  return {
    isApproachingLimit,
    isAtLimit,
    limitMessage: isApproachingLimit && !isAtLimit
      ? `${gateStatus?.current} of ${gateStatus?.limit} used this month`
      : null,
    blockingMessage: gateStatus?.upgradeReason ?? null,
    upgradeUrl: gateStatus?.upgradeUrl,
  };
}

---

PLG Metrics Dashboard

// src/services/analytics/plg-metrics.service.ts

export async function getPLGMetrics(period: "week" | "month") {
  const interval = period === "week" ? "7 days" : "30 days";

  const [activation, virality, conversion] = await Promise.all([
    // Activation rate
    db.query(`
      SELECT
        COUNT(*) FILTER (WHERE is_activated) AS activated,
        COUNT(*) AS total,
        ROUND(100.0 * COUNT(*) FILTER (WHERE is_activated) / COUNT(*), 1) AS rate
      FROM (
        SELECT u.id, EXISTS (
          SELECT 1 FROM activation_events ae
          WHERE ae.user_id = u.id AND ae.step = 'first_export_completed'
        ) AS is_activated
        FROM users u
        WHERE u.created_at >= NOW() - INTERVAL '${interval}'
      ) sub
    `),

    // K-factor
    db.query(`
      SELECT
        ROUND(AVG(invites)::numeric, 2) AS avg_invites,
        ROUND(100.0 * SUM(converted)::numeric / NULLIF(SUM(invites), 0), 1) AS conversion_pct
      FROM (
        SELECT
          referrer_id,
          COUNT(*) AS invites,
          COUNT(*) FILTER (WHERE u.status = 'active') AS converted
        FROM referrals r
        LEFT JOIN users u ON u.id = r.referred_user_id
        WHERE r.referred_at >= NOW() - INTERVAL '${interval}'
        GROUP BY referrer_id
      ) sub
    `),

    // Free-to-paid conversion
    db.query(`
      SELECT
        COUNT(*) FILTER (WHERE plan != 'free') AS paid_conversions,
        COUNT(*) AS total_signups,
        ROUND(
          100.0 * COUNT(*) FILTER (WHERE plan != 'free') / COUNT(*), 1
        ) AS conversion_rate
      FROM users u
      JOIN tenants t ON t.id = u.tenant_id
      WHERE u.created_at >= NOW() - INTERVAL '${interval}'
        AND u.role = 'owner'
    `),
  ]);

  return {
    activationRate: activation.rows[0],
    kFactor: virality.rows[0],
    freeToPaidConversion: conversion.rows[0],
  };
}

---

See Also

• SaaS Onboarding UX Engineering — onboarding design
• SaaS Churn Prediction with Machine Learning — retention signals
• Feature Flags: Advanced Patterns — gating with flags
• SaaS Pricing Strategy Engineering — monetization design

---

Working With Viprasol

PLG is engineering-intensive: activation funnels, referral mechanics, gate design, and the analytics to measure all of it. Our team has built PLG infrastructure for B2B SaaS products that reduced time-to-activation by 60% and improved free-to-paid conversion by 2-3x through data-driven funnel optimization.

SaaS engineering services → | Start a project →