Segment Management

Operational guide for managing customer segments.

Viewing Segments

List All Segments

SELECT id, name, type, member_count, refresh_frequency, last_refreshed_at
FROM "CustomerSegment"
WHERE tenant_id = 'TENANT_ID'
AND deleted_at IS NULL
ORDER BY name;

Check Segment Membership

SELECT cp.id, cp.email, cp.first_name, cp.last_name
FROM "CustomerSegmentMembership" csm
JOIN "CustomerProfile" cp ON csm.customer_id = cp.id
WHERE csm.segment_id = 'SEGMENT_ID'
LIMIT 100;

Refreshing Segments

Manual Refresh

# Via API
curl -X POST https://crm-api.digiwedge.dev/api/v1/segments/SEGMENT_ID/refresh \
  -H "Authorization: Bearer $TOKEN"

Check Refresh Status

SELECT id, name, is_refreshing, last_refreshed_at, refresh_error
FROM "CustomerSegment"
WHERE id = 'SEGMENT_ID';

Force Refresh All Segments

-- Mark all for refresh
UPDATE "CustomerSegment"
SET last_refreshed_at = NULL
WHERE refresh_frequency != 'MANUAL';

Troubleshooting

Segment Count is Zero

1. Check if rules are valid:

SELECT rules FROM "CustomerSegment" WHERE id = 'SEGMENT_ID';

2. Test rules manually:

-- Example: checking for active members
SELECT count(*) FROM "CustomerProfile"
WHERE tenant_id = 'TENANT_ID'
AND status = 'ACTIVE'
AND membership_status = 'ACTIVE';

Segment Not Syncing to Messaging

-- Check sync status
SELECT messaging_segment_id, last_synced_at, sync_error
FROM "CustomerSegment"
WHERE id = 'SEGMENT_ID';

Members Missing from Segment

Check individual customer:

-- Get customer data
SELECT * FROM "CustomerProfile" WHERE id = 'CUSTOMER_ID';

-- Compare against segment rules
SELECT rules FROM "CustomerSegment" WHERE id = 'SEGMENT_ID';

Common Operations

Clone a Segment

INSERT INTO "CustomerSegment" (
  tenant_id, name, type, rules, refresh_frequency
)
SELECT
  tenant_id,
  name || ' (Copy)',
  type,
  rules,
  refresh_frequency
FROM "CustomerSegment"
WHERE id = 'SOURCE_SEGMENT_ID';

Delete a Segment

-- Soft delete
UPDATE "CustomerSegment"
SET deleted_at = now()
WHERE id = 'SEGMENT_ID';

-- Clean up memberships
DELETE FROM "CustomerSegmentMembership"
WHERE segment_id = 'SEGMENT_ID';

Bulk Add to Static Segment

INSERT INTO "CustomerSegmentMembership" (segment_id, customer_id)
SELECT 'SEGMENT_ID', id
FROM "CustomerProfile"
WHERE tenant_id = 'TENANT_ID'
AND email IN ('user1@example.com', 'user2@example.com');

System Segments

Pre-defined segments that shouldn't be modified:

Name	ID Prefix
All Customers	sys_all_
Active Members	sys_active_
High Engagement	sys_high_eng_
At Risk	sys_at_risk_
Email Subscribers	sys_email_