Email Audience Segmentation Without Schema Pollution

At Homi, we use Loops for transactional emails and marketing campaigns. As we grew, we wanted more sophisticated audience targeting—sending emails to users with specific behaviors like "users with empty collections" or "power users with 10+ properties."

The challenge? We didn't want to pollute our user database schema with computed properties that change over time, and we didn't want to scatter one-off Loops updates throughout our codebase.

Here's how we solved it with a batch sync script that computes dynamic segments on-demand and updates our entire Loops audience before campaigns.

The Problem: Where Do Segments Live?

When you want to send targeted emails, you need user properties that Loops can filter on. The naive approach is to add these properties to your user table:

// ❌ Don't do this
const userTable = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull(),
  name: text("name"),
  // ... other fields

  // Computed properties that change over time
  collectionsCount: integer("collections_count"), // ❌ Needs updates everywhere
  hasEmptyCollections: boolean("has_empty_collections"), // ❌ Stale data risk
  totalPropertyListings: integer("total_property_listings"), // ❌ Maintenance nightmare
});

Problems with this approach:

1. Schema Pollution: Every new segmentation idea requires a migration
2. Stale Data Risk: Properties can become outdated if not updated everywhere
3. Update Complexity: Finding all places where collections/listings change and adding updates
4. Performance: Keeping computed fields in sync adds overhead to every mutation

The Alternative: Update Loops Directly?

Another approach is to update Loops contacts whenever relevant data changes:

// ❌ Also not great
async function createCollection(userId: string, data: CreateCollectionInput) {
  const collection = await db.insert(collectionTable).values({...});

  // Update Loops every time something changes
  await createOrUpdateLoopsContact(user.email, {
    collectionsCount: await getUserCollectionsCount(userId),
    // ... compute other segments
  });

  return collection;
}

Problems with this approach:

1. Code Pollution: Every mutation needs Loops update logic
2. Performance: Extra API calls on every operation
3. Error Handling: Loops failures shouldn't break core functionality
4. Inconsistency: Hard to ensure all updates happen everywhere

Our Solution: Batch Sync Before Campaigns

We built a sync script that runs before campaigns and computes all segments fresh from the database. This gives us:

• ✅ Clean Schema: No computed properties in our database
• ✅ Fresh Data: Segments computed from current database state
• ✅ Centralized Logic: One place for all segment computation
• ✅ Campaign-Ready: Audience is always up-to-date when we need it

The Implementation

Computing Dynamic Segments

Our computeUserSegments function queries the database to calculate properties on-demand:

async function computeUserSegments(
  userId: string,
): Promise<UserSegmentProperties> {
  // Query collections owned by the user
  const collections = await db.query.collectionTable.findMany({
    where: (collections, { eq, isNull }) =>
      sql`${eq(collections.ownerId, userId)} AND ${isNull(collections.deletedAt)}`,
    with: {
      propertyListings: {
        where: (listings, { isNull }) => isNull(listings.deletedAt),
      },
    },
  });

  const collectionsCount = collections.length;
  const hasCollections = collectionsCount > 0;

  // Check if user has any empty collections
  const hasEmptyCollections = collections.some(
    (col) => col.propertyListings.length === 0,
  );

  // Count total property listings across all collections
  const totalPropertyListings = collections.reduce(
    (sum, col) => sum + col.propertyListings.length,
    0,
  );

  // Get last activity date
  const lastActivity = await db.query.activityTable.findFirst({
    where: (activities, { eq }) => eq(activities.actorId, userId),
    orderBy: (activities, { desc }) => [desc(activities.createdAt)],
  });

  return {
    collectionsCount,
    hasCollections,
    hasEmptyCollections,
    totalPropertyListings,
    lastActivityDate: lastActivity?.createdAt.toISOString() ?? null,
  };
}

Key benefits:

• Uses Drizzle's relational queries for efficient data loading
• Respects soft deletion (deletedAt checks)
• Single source of truth: database state at sync time
• Easy to extend with new segment properties

The Sync Script

The main sync function processes all users and updates Loops:

export async function syncLoopsContacts(
  options: SyncLoopsContactsOptions = {},
): Promise<SyncLoopsContactsResult> {
  const { includeSegments = false } = options;

  // Get all active users
  const users = await db.query.userTable.findMany({
    where: (users, { isNull }) => isNull(users.deletedAt),
  });

  let successCount = 0;
  let errorCount = 0;
  let skippedCount = 0;

  // Process each user
  for (const user of users) {
    try {
      if (!user.email) {
        skippedCount++;
        continue;
      }

      // Transform user data to Loops format
      const { name, ...restTraits } = user;
      const loopsTraits: Record<string, unknown> = { ...restTraits };

      // Split name into firstName/lastName (Loops format)
      if (name) {
        const nameParts = name.trim().split(/\s+/);
        loopsTraits.firstName = nameParts[0];
        loopsTraits.lastName =
          nameParts.length > 1 ? nameParts.slice(1).join(" ") : "";
      }

      // Compute and include segment properties if requested
      if (includeSegments) {
        const segments = await computeUserSegments(user.id);
        Object.assign(loopsTraits, segments);
      }

      // Sanitize and sync to Loops
      const sanitizedTraits =
        sanitizePropertiesForVercelAndLoops(loopsTraits);
      await createOrUpdateLoopsContact(user.email, {
        ...sanitizedTraits,
        userId: user.id,
      });

      successCount++;

      // Rate limiting: 100ms delay between requests
      await new Promise((resolve) => setTimeout(resolve, 100));
    } catch (error) {
      errorCount++;
      console.error(`✗ Failed to sync user ${user.email}:`, error);
    }
  }

  return {
    success: errorCount === 0,
    totalUsers: users.length,
    successCount,
    errorCount,
    skippedCount,
    message: `Sync completed! Successfully synced: ${successCount} users, Failed: ${errorCount}, Skipped: ${skippedCount}`,
  };
}

Design decisions:

• Optional Segments: includeSegments flag lets us run quick syncs (basic user data) or full syncs (with segments)
• Error Resilience: Individual failures don't stop the sync
• Rate Limiting: 100ms delay prevents API throttling
• Sanitization: Uses shared sanitization utility for consistent property formatting

The API Endpoint

For easy campaign management, we exposed the sync as an admin API endpoint:

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const password = searchParams.get("password");

  // Simple password-based auth for admin tool
  if (password !== ADMIN_PASSWORD) {
    return NextResponse.json(
      { error: "Unauthorized - Invalid password" },
      { status: 401 },
    );
  }

  // Check if segments should be included
  const includeSegments = searchParams.get("segments") === "true";

  // Run the sync (up to 5 minutes)
  const result = await syncLoopsContacts({ includeSegments });

  return NextResponse.json({
    timestamp: new Date().toISOString(),
    includeSegments,
    ...result,
  });
}

Usage:

# Quick sync (basic user data only)
GET https://www.homi.so/api/admin/tools/loops/sync?password=loops-sync-2025

# Full sync with segments (for campaigns)
GET https://www.homi.so/api/admin/tools/loops/sync?password=loops-sync-2025&segments=true

Using Segments in Loops

Once synced, segments become available as custom contact properties in Loops. You can create audience filters like:

• collectionsCount = 0 → Users with no collections
• hasEmptyCollections = true → Users with empty collections
• totalPropertyListings > 10 → Power users
• lastActivityDate < "2025-01-01" → Inactive users

These filters work seamlessly in Loops' campaign builder, giving you powerful audience targeting without any database changes.

Workflow: Running Campaigns

Our typical workflow for targeted campaigns:

1. Run Full Sync: Click the URL with segments=true to compute and sync all segments
2. Verify in Loops: Check that segment properties are populated correctly
3. Build Campaign: Use Loops filters to target specific audiences
4. Send: Campaign goes out with accurate, up-to-date segment data

For simple transactional emails or non-segmented campaigns, we skip the sync (or use the quick sync without segments).

Key Takeaways

1. Separate Concerns: Database vs. Analytics

Your database schema should represent your domain model, not your analytics needs. Computed properties that change over time belong in analytics systems, not in your primary database.

2. Batch Updates Are Simpler Than Real-Time

For campaign targeting, you don't need real-time segment updates. Batch syncing before campaigns ensures:

• Fresh data when you need it
• No performance overhead on regular operations
• Centralized logic that's easier to maintain

3. Make It Easy to Run

An admin API endpoint (or CLI script) makes syncing a one-click operation. No SSH, no complex setup—just a URL bookmark in your browser.

4. Design for Extensibility

Adding new segment properties is straightforward:

• Add computation logic to computeUserSegments
• Run sync with segments=true
• New properties appear in Loops automatically

5. Error Resilience Matters

Individual sync failures shouldn't break the entire process. Log errors, continue processing, and return a summary so you know what succeeded and what failed.

The Trade-offs

Pros:

• ✅ Clean database schema
• ✅ Fresh data at sync time
• ✅ No code pollution in mutations
• ✅ Easy to extend with new segments

Cons:

• ⚠️ Segments can be stale between syncs (acceptable for campaigns)
• ⚠️ Sync takes time for large user bases (but runs async)
• ⚠️ Requires manual sync before campaigns (but this is intentional)

For our use case—campaign targeting—these trade-offs are perfect. We get clean architecture and fresh data when we need it, without the complexity of real-time updates.

Future Enhancements

We're considering:

1. Scheduled Syncs: Automatically run segment syncs weekly or daily
2. Selective Updates: Only sync users whose data has changed since last sync
3. More Segments: Add engagement metrics, feature usage, subscription status, etc.
4. Webhook Integration: Optional real-time updates for critical segments

But for now, the batch sync approach gives us exactly what we need: campaign-ready audiences without schema pollution.

---

The moral: Don't pollute your database schema with analytics properties. Compute segments on-demand and sync them to your analytics systems when you need them. Your schema stays clean, your code stays simple, and your campaigns get fresh data.

---

Want to see how we handle user data? Check out our GDPR deletion implementation or try Homi yourself.