Skip to content
Academy
Marketing Academy · Field Work●Email & Lifecycle
All projects·From: Segmentation
MiniBuild the Asset· 30 minutes

Build the First Three Segments: An Engagement-Tier Scheme

Nykaa (FSN E-Commerce Ventures)

Objective: Given a 12-row subscriber engagement export, build the lesson's three-segment engagement scheme (active/cold/lapsed) with real cutoffs and a send action for each.

You're the CRM analyst at Nykaa and the list has never been segmented; leadership wants the fastest-to-ship segmentation win before the next campaign.

Apply the lesson's engagement-tier framework to a real subscriber export and produce a segment assignment sheet plus the send action for each tier.

Before you start

What you'll need

Free path (everything below is enough to finish)

Google Sheets
FreeCalculate days-since-last-open and assign tiers

Free, no account friction, works from a plain CSV export

Paid upgrades (optional, faster/deeper)

Klaviyo(optional)
FreemiumBuild the same three tiers as live, auto-updating segments

Automates the day-count so subscribers move between tiers without manual re-export

No access? Google Sheets recalculated on a recurring schedule covers the exercise

The process

1 step

Step 01 of 01

Engagement segmentation: active, cold, lapsed

The lesson defines three engagement tiers by days-since-last-open: active (0-90), cold (90-180), lapsed (180+), each with a distinct send action.

Given a 12-subscriber export with last-open dates, which subscribers fall into each of the three tiers, and what does each tier receive?

Google Sheets— Import the subscriber export, add a 'days since last open' column, then a 'segment' column.

Procedure

  1. Calculate days-since-last-open for all 12 rows against today's date
  2. Bucket each row into active (0-90), cold (90-180), or lapsed (180+)
  3. Assign the send action per tier: full calendar, re-engagement sequence, or win-back then suppression
Sample output
SEGMENT ASSIGNMENT (12 subscribers)
ACTIVE (5): opened within 90 days -> full promotional calendar
COLD (4): 90-180 days -> 3-email re-engagement sequence
LAPSED (3): 180+ days -> win-back campaign, then suppress if no response

Healthy

All 12 rows land in exactly one tier with a matching send action, no subscriber left unassigned.

Unhealthy

Subscribers get bucketed by list join date instead of last-open date, or the tier boundaries don't match the 90/180-day cutoffs.

What this means

The scheme is only useful if the cutoffs are behavioral (last open), not static (join date).

So what do I do about it?

SymptomActionEffort
Every subscriber gets the same weekly promotional email regardless of engagementShip the three-tier segment split before the next campaign send30 min
YouYou can do this yourself, no engineering access required.

Final deliverable

A segment-assignment sheet mapping all 12 subscribers to active/cold/lapsed with the correct send action per tier.

See a reference example
Sample output
Casper Sleep engagement segments (excerpt)

ACTIVE (opened 40 days ago): full calendar
COLD (opened 142 days ago): re-engagement sequence
LAPSED (opened 210 days ago): win-back, then suppress

Success criteria

You're done when you can:

  • Correctly buckets all 12 rows using days-since-last-open, not join date
  • Assigns the correct send action to each tier
  • Flags at least one lapsed subscriber for suppression