Adobe Campaign Classic Business Practitioner Expert: The Segmentation That Slowed to a Crawl

The Segmentation That Slowed to a Crawl

A retailer's 'high-value lapsed' segment is defined as: recipients whose lifetime order total exceeds 500, whose last order date is older than 90 days, and who opened at least one email in the last 30 days. The workflow queries nms:recipient and joins to a custom cus:orders schema and to nms:trackingLog. In staging with 50,000 recipients it runs in under a minute. In production with 4.2 million recipients it times out after 40 minutes and is killed by the operator. The DBA notes the query plan shows a full scan of the tracking log table joined in the outer query, and that the cus:orders table has no index on recipientId. The segment is needed weekly, and the marketing team wants it to refresh nightly.

1. Explain why the same workflow behaves so differently at production scale, focusing on the join to tracking logs and the missing index.

Read the worked answer

The staging run is fast because the data volume hides a bad plan: with 50k recipients, a full scan of the tracking log and a nested-loop join over an unindexed recipientId are cheap in absolute terms. At 4.2M recipients the same plan multiplies.

Tracking log join in the outer query: the tracking table is one of the largest in the instance (one row per open/click). Joining it to the recipient set inside the main selection forces the engine to consider a huge intermediate result. The 'opened in last 30 days' condition should be a semi-join / EXISTS-style sub-selection, not a row-expanding join - you only need to know whether the recipient opened, not enumerate every open.
Missing index on cus:orders.recipientId: the aggregate (lifetime total, last order date) requires grouping orders by recipient. Without an index, each recipient lookup scans the orders table - the classic N×M blowup. Adding the index turns it into an indexed lookup per recipient.

The 40-minute timeout is the symptom; the cause is a query shape that scales super-linearly plus an unindexed foreign key.

2. Redesign the workflow so it completes nightly. Describe the activity structure and the schema/index changes, and justify the ordering.

Read the worked answer

Restructure into staged, independently materialized steps rather than one monolithic query:

Pre-aggregate orders once. Add an index on cus:orders.recipientId (and consider a composite with order date if the aggregate is date-bounded). In the workflow, a first Query/aggregate step computes per-recipient SUM(amount) and MAX(orderDate) into a temporary work table - one pass over orders, grouped, rather than per-recipient scans.
Pre-aggregate opens once. A second step reduces nms:trackingLog to distinct recipientIds with an open in the last 30 days (a DISTINCT/grouped sub-selection). This collapses millions of log rows to a recipient set before it ever meets the recipient table.
Join the small sets. Query nms:recipient filtered to the lapsed window, join to the order aggregate (indexed), then use an EXISTS/semi-join against the open set - not a row join.
Materialize the result into a dedicated segment table (or a list) so downstream campaigns read a small, stable set instead of re-running the joins.

Justification for ordering: each step shrinks the data before the next expensive operation. The tracking log and orders tables are each touched exactly once in a grouped pass; the recipient table is filtered early. Indexes make the joins indexed lookups. This turns a super-linear plan into roughly linear passes and should complete in minutes at 4.2M recipients. Schedule nightly and let the campaign read the materialized segment.