Most messaging measurement stops at the message: delivered, read, replied. That tells you the mechanics worked. It does not tell you the thing every business actually wants to know, which is whether this changed how customers behave over a year.
The shape of the analysis
- Define a cohort by when the customer first transacted — month is usually the right grain.
- Split it by whether they were messaged in their first 90 days.
- Measure repeat rate at 3, 6 and 12 months for each half.
- Compare the curves, not the endpoints. The shape tells you when the effect appears and whether it persists.
The selection problem, and the only honest fix
Customers who gave you a mobile number and consent were already more engaged than those who did not. Comparing messaged against not-messaged therefore overstates the effect, sometimes enormously. The fix is to compare *within* the consented group: message some of them and hold back a slice. That is the only version of this analysis that supports a causal claim.
The query
with cohorts as (
select
c.id,
date_trunc('month', c.first_transaction_at) as cohort_month,
exists (
select 1 from messages m
where m.contact_id = c.id
and m.sent_at < c.first_transaction_at + interval '90 days'
) as messaged_early
from contacts c
where c.messaging_consent -- consented only: removes the bias
and c.first_transaction_at < now() - interval '12 months'
),
repeats as (
select
ch.cohort_month,
ch.messaged_early,
count(*) as customers,
count(*) filter (
where exists (
select 1 from transactions t
where t.contact_id = ch.id
and t.occurred_at between
ch.cohort_month + interval '3 months'
and ch.cohort_month + interval '12 months'
)
) as returned
from cohorts ch
group by 1, 2
)
select
cohort_month,
messaged_early,
customers,
round(100.0 * returned / nullif(customers, 0), 1) as repeat_pct
from repeats
order by cohort_month, messaged_early;Retention by cohort month, split on whether the customer was messaged early. Restrict to consented customers to avoid the selection problem above.
Reading the result honestly
- A gap that widens over time is the strongest possible result — the channel is building a relationship, not just prompting a transaction.
- A gap that appears then closes means you prompted an earlier repeat purchase without changing lifetime behaviour. Still valuable, worth less.
- No gap is a real finding. It may mean your messages are logistics rather than relationship, which is fine — just do not claim retention benefits you cannot see.
- A negative gap means you are over-messaging. Check the opt-out rate for that cohort before you conclude anything else.
How long before this is worth running
You need twelve months of history and enough customers per cohort that the percentages are not noise — a few hundred per cohort half is a reasonable floor. Below that, run the simpler comparison instead and revisit this in a year. The A/B approach.
For recurring-service businesses this is the analysis that justifies the whole programme, because retention *is* the business. The recurring-service mechanics.