Cohort and Funnel Analysis

The previous lesson showed that a coefficient changes direction by breakdown, but the breakdown was always the region and time never entered the work. Subscribers do not join the network at the same time and are not observed for the same length of time. Still being able to read a subscriber one period after the registration opened is a success, and the rate of this success is called retention.

The number of this lesson is the retention rate that the cohort definition moves. A cohort is the set built by entry period, and its definition contains two decisions: how wide the entry window is and where the observation window closes. Both decisions are defensible, and both produce a different rate from the same dataset. The step computation of the conversion funnel is not repeated here; step conversion, drop-off rate and cumulative conversion were measured in the Conversion Funnel lesson of the User Experience course. The funnel enters this lesson only as far as the aggregation reversal.

• AS36. The dataset is inherited from K03 and is fictional: 3,199 readings, 1,329 subscribers, three periods, five regions.
• AS37. The entry week field is fictional too: the measurement network did not reach the regions at the same time, so the distribution of the entry period depends on the region.
• AS38. In this lesson the reading index gives the period after entry; the calendar period is the sum of the entry period and the index minus one, and the calendar closes at three periods.
• AS39. Retention is the share of the subscribers of a cohort who still produce a reading $K$ periods after entry. If the period $K$ later falls outside the calendar, the rate cannot be observed.
• AS40. Two rules can be applied to an unobservable subscriber: the zero-counting rule counts the subscriber as not retained, the excluding rule removes the subscriber from the denominator.
• AS41. The margin of variation is produced by drawing with replacement: the cohort list is redrawn 1,000 times.
• AS42. The reading method field is fictional too and carries two relationships together: remote reading has become widespread in the late periods and has fewer interruptions.

The Observation Window Cuts the Cohort

The calendar closes at three periods. For a subscriber who joined the network in the third period, the answer to the question “can this subscriber still be read one period later” is not in the dataset. This subscriber has not failed to be retained; the subscriber has not been measured yet.

# cohort.py — MODEL. The fictional dataset of K03 is produced with the same seed. The entry
# week field is FICTIONAL too: the measurement network did not reach the regions at the same
# time, so the distribution of the entry period depends on the region. The reading index gives
# the period after entry; calendar period = entry period + index - 1 and the calendar closes
# at three periods.
import math

SEED, RAW, M32 = 20260218, 1400, 0xFFFFFFFF
REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26),
          ("west", 0.14, 14), ("central", 0.18, 23)]
ENTRY = {"east": [0.60, 0.28, 0.12], "central": [0.52, 0.30, 0.18],
         "north": [0.40, 0.34, 0.26], "south": [0.28, 0.36, 0.36],
         "west": [0.16, 0.32, 0.52]}


def rng(t):
    x = ((t ^ (t >> 16)) * 2246822507) & M32
    x = ((x ^ (x >> 13)) * 3266489909) & M32
    s = [(x ^ (x >> 16)) & M32]

    def next_value():
        s[0] = (s[0] * 1664525 + 1013904223) & M32
        return s[0] / 4294967296
    return next_value


def pick(u, w):
    t = 0.0
    for i, x in enumerate(w):
        t += x
        if u < t:
            return i
    return len(w) - 1


SUBSCRIBERS, DROPPED = [], []
for i in range(RAW):
    r = rng(SEED + i)
    b = REGION[pick(r(), [x[1] for x in REGION])]
    pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05])
    pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17])
    (DROPPED if r() < 0.046 else SUBSCRIBERS).append(
        {"subscriber_id": 10001 + i, "region": b[0], "base": b[2]})
VALID = {k["subscriber_id"] for k in SUBSCRIBERS}
READINGS = {}
for k in SUBSCRIBERS + DROPPED:
    r = rng(SEED + 7000 + k["subscriber_id"])
    for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])):
        t = 0.0 if r() < 0.038 else math.floor(
            k["base"] * math.exp((r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100
            + 0.5) / 100
        if k["subscriber_id"] in VALID:
            READINGS.setdefault(k["subscriber_id"], []).append(t)
        r()

JOINED = []
for k in SUBSCRIBERS:
    if k["subscriber_id"] not in READINGS:
        continue
    r = rng(SEED + 61000 + k["subscriber_id"])
    e = pick(r(), ENTRY[k["region"]])
    week = e * 13 + 1 + int(r() * 13)
    JOINED.append({"id": k["subscriber_id"], "region": k["region"], "week": week,
                   "cohort": e + 1, "block": (week - 1) // 5 + 1,
                   "n": len(READINGS[k["subscriber_id"]])})

WINDOW = 3


def observable(x, K):
    """Does the cohort stay inside the three-period calendar K periods later."""
    return x["cohort"] + K <= WINDOW


def retained(x, K):
    return x["n"] >= K + 1 and observable(x, K)


def rate(group, K, rule):
    denominator = group if rule == "zero-counting" else [x for x in group if observable(x, K)]
    if not denominator:
        return None, 0
    return sum(1 for x in denominator if retained(x, K)) / len(denominator), len(denominator)


print(f"fictional dataset (seed {SEED}): {len(SUBSCRIBERS)} subscribers, "
      f"{sum(len(v) for v in READINGS.values())} readings")
print("entry cohort (period): " + ", ".join(
    f"{k}: {sum(1 for x in JOINED if x['cohort'] == k)}" for k in (1, 2, 3))
    + f"   ({len(JOINED)} subscribers with at least one reading)")
print(f"readings outside the three-period window: "
      f"{sum(max(0, x['cohort'] + x['n'] - 1 - WINDOW) for x in JOINED)}"
      f" / {sum(x['n'] for x in JOINED)}")

for K in (1, 2):
    print(f"\nretention ({K} period after entry)")
    print("cohort      n     observable   zero-counting   excluding")
    for coh in (1, 2, 3):
        group = [x for x in JOINED if x["cohort"] == coh]
        o1, n1 = rate(group, K, "zero-counting")
        o2, n2 = rate(group, K, "excluding")
        print(f"{coh:>6} {len(group):>6} {n2:>14}   {o1:>13.3f}   "
              + (f"{o2:>9.3f}" if o2 is not None else f"{'--':>9}"))
    o1, _ = rate(JOINED, K, "zero-counting")
    o2, n2 = rate(JOINED, K, "excluding")
    print(f"{'total':>6} {len(JOINED):>6} {n2:>14}   {o1:>13.3f}   {o2:>9.3f}")

fictional dataset (seed 20260218): 1329 subscribers, 3199 readings
entry cohort (period): 1: 488, 2: 412, 3: 360   (1260 subscribers with at least one reading)
readings outside the three-period window: 843 / 3199

retention (1 period after entry)
cohort      n     observable   zero-counting   excluding
     1    488            488           0.871       0.871
     2    412            412           0.862       0.862
     3    360              0           0.000          --
 total   1260            900           0.619       0.867

retention (2 period after entry)
cohort      n     observable   zero-counting   excluding
     1    488            488           0.648       0.648
     2    412              0           0.000          --
     3    360              0           0.000          --
 total   1260            488           0.251       0.648

The number to enter the report is 0.619 or 0.867 for one-period retention. Both come from the same dataset and the same definition; the only place where they part is what the 360 unobservable subscribers are counted as. The zero-counting rule takes them as not retained and pulls the rate down by 0.248. For two-period retention the difference is even larger: 0.251 against 0.648.

Counting an unobservable subscriber as zero is not a measurement but an assumption, and this assumption is not met in the dataset. For the 360 subscribers of cohort 3, the reading of the next period has never been collected; had it been collected, it would be expected to give a number close to the rates 0.871 and 0.862 of the first two cohorts.

The third row gives the size of this cutting: 843 of the 3,199 readings fall outside the three-period calendar. The cohort definition cuts the dataset, and the part that is cut off is not small.

The Entry Window Changes the Number of Cohorts

The second decision of the cohort is the width of the entry window. If blocks of five weeks are taken instead of periods, the same subscribers distributed over eight cohorts. Next to the rate of every cohort, its margin is printed too.

# When the entry window changes, how many separate rates come out of the same dataset.
RESAMPLES = 1000
rs = rng(SEED + 44000)


def rate_interval(group, K):
    """Drawing with replacement: the cohort list is redrawn 1,000 times."""
    n, drawn = len(group), []
    for _ in range(RESAMPLES):
        drawn.append(sum(1 for _ in range(n) if retained(group[int(rs() * n)], K)) / n)
    drawn.sort()
    return drawn[24], drawn[974]


def scan(field, label):
    keys = sorted({x[field] for x in JOINED})
    print(f"\nentry window: {label} -> {len(keys)} cohorts   "
          "(excluding rule, 1 period)")
    print("cohort      n    retention   margin of variation   width")
    widths, rates = [], []
    for a in keys:
        group = [x for x in JOINED if x[field] == a and observable(x, 1)]
        if len(group) < 10:
            print(f"{a:>6} {len(group):>6}   {'unobservable':>12}")
            continue
        o = sum(1 for x in group if retained(x, 1)) / len(group)
        d, u = rate_interval(group, 1)
        widths.append(u - d)
        rates.append(o)
        print(f"{a:>6} {len(group):>6}   {o:>10.3f}   [{d:.3f}, {u:.3f}]   {u - d:>10.3f}")
    print(f"  rate range {max(rates) - min(rates):.3f}, "
          f"narrowest margin {min(widths):.3f}, widest margin {max(widths):.3f}")
    return rates


d3 = scan("cohort", "period (13 weeks)")
d8 = scan("block", "five weeks")
print(f"\nnumber of retention rates from the same dataset: {len(d3)} and {len(d8)}")
print(f"range with the period definition {min(d3):.3f} - {max(d3):.3f}, "
      f"with the five-week definition {min(d8):.3f} - {max(d8):.3f}")

entry window: period (13 weeks) -> 3 cohorts   (excluding rule, 1 period)
cohort      n    retention   margin of variation   width
     1    488        0.871   [0.840, 0.898]        0.057
     2    412        0.862   [0.828, 0.896]        0.068
     3      0   unobservable
  rate range 0.009, narrowest margin 0.057, widest margin 0.068

entry window: five weeks -> 8 cohorts   (excluding rule, 1 period)
cohort      n    retention   margin of variation   width
     1    181        0.878   [0.829, 0.923]        0.094
     2    185        0.854   [0.805, 0.903]        0.097
     3    184        0.891   [0.848, 0.935]        0.087
     4    174        0.856   [0.805, 0.908]        0.103
     5    155        0.852   [0.800, 0.903]        0.103
     6     21        0.857   [0.667, 1.000]        0.333
     7      0   unobservable
     8      0   unobservable
  rate range 0.040, narrowest margin 0.087, widest margin 0.333

number of retention rates from the same dataset: 2 and 6
range with the period definition 0.862 - 0.871, with the five-week definition 0.852 - 0.891

When the entry window narrows, the number of reportable rates rises from 2 to 6 and the range between the rates widens from 0.009 to 0.040. A person looking at a cohort matrix may read this widening as a pattern: the third block is 0.891, the fifth block 0.852, and the 4-point drop between them looks like a trend.

The column of margins closes this reading. The narrowest margin of variation is 0.087 wide; the whole of the 0.040 range between the six blocks fits inside the margin of a single cohort. The pairwise differences between the blocks also stay inside the margins. The thin cohort definition did not produce new information; it divided the same information into more numbers and enlarged the margin of each number.

The sixth block is the extreme example of this. The rate resting on twenty-one observable subscribers is 0.857 and its margin is [0.667, 1.000]; its width is 0.333, that is, more than three times the margin of the five cohorts. The seventh and eighth blocks stay entirely outside the observation window and produce no rate. As the cohort definition thins, the number of unobservable cohorts grows too.

The Aggregated Rate and the Rate of the Cohorts

What a cohort is good for shows when it is placed next to an aggregated rate. There are two reading methods in the dataset and the two steps of the funnel are these: entry to the cohort, reading in the next period.

# The reading method field is FICTIONAL too and carries two relationships together: remote
# reading has become widespread in the late periods and has fewer interruptions.
METHOD_BASE = {1: 0.28, 2: 0.55, 3: 0.80}
for x in JOINED:
    r = rng(SEED + 71000 + x["id"])
    p = METHOD_BASE[x["cohort"]] + (0.10 if x["n"] >= 2 else -0.10)
    x["method"] = "remote" if r() < p else "on-site"

METHODS = ("on-site", "remote")


def difference_interval(a, b, K):
    na, nb, drawn = len(a), len(b), []
    for _ in range(RESAMPLES):
        oa = sum(1 for _ in range(na) if retained(a[int(rs() * na)], K)) / na
        ob = sum(1 for _ in range(nb) if retained(b[int(rs() * nb)], K)) / nb
        drawn.append(ob - oa)
    drawn.sort()
    return drawn[24], drawn[974]


print("funnel: entry to the cohort -> reading in the next period")
print("\ncohort   on-site reading   remote reading   difference   margin of variation")
for coh in (1, 2, 3):
    group = [x for x in JOINED if x["cohort"] == coh]
    a = [x for x in group if x["method"] == "on-site"]
    b = [x for x in group if x["method"] == "remote"]
    if not observable(group[0], 1):
        print(f"{coh:>6}   {'--':>5} ({len(a):>3})       {'--':>5} ({len(b):>3})"
              f"              --   unobservable")
        continue
    oa = sum(1 for x in a if retained(x, 1)) / len(a)
    ob = sum(1 for x in b if retained(x, 1)) / len(b)
    d, u = difference_interval(a, b, 1)
    print(f"{coh:>6}   {oa:.3f} ({len(a):>3})       {ob:.3f} ({len(b):>3})"
          f"          {ob - oa:>+.3f}   [{d:+.3f}, {u:+.3f}]")
a = [x for x in JOINED if x["method"] == "on-site"]
b = [x for x in JOINED if x["method"] == "remote"]
oa = sum(1 for x in a if retained(x, 1)) / len(a)
ob = sum(1 for x in b if retained(x, 1)) / len(b)
d, u = difference_interval(a, b, 1)
print(f"{'total':>6}   {oa:.3f} ({len(a):>3})       {ob:.3f} ({len(b):>3})"
      f"          {ob - oa:>+.3f}   [{d:+.3f}, {u:+.3f}]")

print("\ncohort composition of the method (row share)")
for y in METHODS:
    group = [x for x in JOINED if x["method"] == y]
    print(f"  {y:<8} " + "  ".join(
        f"cohort {k}: {sum(1 for x in group if x['cohort'] == k) / len(group):.3f}"
        for k in (1, 2, 3)))

funnel: entry to the cohort -> reading in the next period

cohort   on-site reading   remote reading   difference   margin of variation
     1   0.843 (325)       0.926 (163)          +0.083   [+0.028, +0.139]
     2   0.844 (154)       0.872 (258)          +0.028   [-0.041, +0.099]
     3      -- ( 56)          -- (304)              --   unobservable
 total   0.755 (535)       0.519 (725)          -0.237   [-0.284, -0.184]

cohort composition of the method (row share)
  on-site  cohort 1: 0.607  cohort 2: 0.288  cohort 3: 0.105
  remote   cohort 1: 0.225  cohort 2: 0.356  cohort 3: 0.419

The aggregated row says a single sentence: retention is 0.237 points lower for remotely read meters and its margin is [-0.284, -0.184], away from zero. This number and this margin have been written in the form the rule of the course asks for, and they are wrong.

The cohort rows show the opposite. In the first cohort remote reading is 0.083 points ahead and its margin is [+0.028, +0.139], not containing zero. In the second cohort it is 0.028 points ahead and its margin contains zero. The third cohort cannot be observed. No cohort shows the direction the aggregated rate shows: two say the opposite, one says nothing.

This phenomenon is called the aggregation reversal, and its mechanism is in the last table. 60.7% of the on-site read meters are in the first cohort; 41.9% of the remotely read ones are in the third cohort. The third cohort is counted as zero because of the observation window, so the aggregated rate of remote reading comes mostly from subscribers who could not be measured. What has been measured is not the difference between the two methods but the cohort composition of the two methods.

An aggregated rate describes no cohort if there is both a rate difference and a composition difference between the cohorts. An aggregated rate reported without its composition written is not a measurement but a decision to take an average.

Summary

• The rule applied to the unobservable cohort produces two rates from the same dataset: one-period retention is 0.619 with the zero-counting rule and 0.867 with the excluding rule; for two periods it is 0.251 and 0.648.
• Outside the three-period observation window 843 of the 3,199 readings remain; the cohort definition cuts the dataset.
• When the entry window goes down from a period to five weeks, the number of reportable rates rises from 2 to 6 and the rate range from 0.009 to 0.040; since the narrowest margin of variation is 0.087, the whole range fits inside the margin of a single cohort.
• In the sixth block, which rests on twenty-one subscribers, the rate is 0.857, the margin [0.667, 1.000] and the width 0.333; the seventh and eighth blocks are entirely outside the observation window.
• Aggregation reversal: while the aggregated difference is -0.237 and its margin is away from zero, the difference in the two observable cohorts is +0.083 and +0.028. 60.7% of those read on-site are in the first cohort, 41.9% of those read remotely are in the third cohort.

Next Step

In three lessons the margin was written next to the number and the three sources that enlarge the margin were counted: the number of combinations, the variable that is not in the model, the cohort definition. In all three the dataset was taken as given. The last lesson of the course removes this assumption: the dataset is made up of meters that could already be read and of subscribers who produced at least one reading, that is, what is measured is not the network itself but the visible part of the network. In the same lesson it is counted how many findings can be produced from noise, and this number is compared with the expected value.