Kapitel 4. Kohortenanalyse

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: translation-feedback@oreilly.com

In Kapitel 3 haben wir die Zeitreihenanalyse behandelt. Nachdem wir diese Techniken kennengelernt haben, wenden wir uns nun einer verwandten Analyseform zu, die in vielen Unternehmen und anderen Bereichen Anwendung findet: der Kohortenanalyse.

Ich erinnere mich an das erste Mal, als ich mit einer Kohortenanalyse in Berührung kam. Ich arbeitete in meinem ersten Job als Datenanalystin bei einem kleinen Start-up-Unternehmen. Ich besprach mit dem Geschäftsführer eine Kaufanalyse, an der ich gearbeitet hatte, und er schlug mir vor, den Kundenstamm nach Kohorten aufzuschlüsseln, um zu sehen, ob sich das Verhalten im Laufe der Zeit verändert. Ich nahm an, dass es sich dabei um ein ausgefallenes BWL-Ding handelte, das wahrscheinlich nutzlos war, aber er war der Geschäftsführer, also habe ich ihm natürlich zugehört. Es stellte sich heraus, dass es nicht nur ein Scherz war. Populationen in Kohorten aufzuteilen und sie über einen längeren Zeitraum zu verfolgen, ist eine gute Methode, um Daten zu analysieren und verschiedene Verzerrungen zu vermeiden. Kohorten können Aufschluss darüber geben, wie sich Teilpopulationen voneinander unterscheiden und wie sie sich im Laufe der Zeit verändern.

In diesem Kapitel werden wir zunächst einen Blick darauf werfen, was Kohorten sind und welche Bausteine bestimmte Arten von Kohortenanalysen enthalten. Nach einer Einführung in den Datensatz des Gesetzgebers, der für die Beispiele verwendet wird, lernen wir, wie wir eine Verbleibsanalyse erstellen und verschiedene Herausforderungen wie die Definition der Kohorte und den Umgang mit spärlichen Daten meistern. Als Nächstes befassen wir uns mit der Überlebensdauer, der Rückkehr und den kumulativen Berechnungen, die in der Art und Weise, wie der SQL-Code aufgebaut ist, der Verbleibsanalyse ähneln. Zum Schluss werden wir uns ansehen, wie man Kohortenanalysen mit Querschnittsanalysen kombiniert, um die Zusammensetzung von Populationen im Laufe der Zeit zu verstehen.

Kohorten: Ein nützlicher Analyserahmen

Bevor wir uns mit dem Code beschäftigen, werde ich definieren, was Kohorten sind, die Arten von Fragen betrachten, die wir mit dieser Art von Analyse beantworten können, und die Komponenten einer Kohortenanalyse beschreiben.

Eine Kohorte ist eine Gruppe von Personen, die zu dem Zeitpunkt, an dem wir mit der Beobachtung beginnen, ein bestimmtes, unten beschriebenes Merkmal teilen. Kohortenmitglieder sind oft Menschen, können aber auch jede andere Art von Einheit sein, die wir untersuchen wollen: Unternehmen, Produkte oder physikalische Phänomene. Einzelpersonen in einer Kohorte können sich ihrer Zugehörigkeit bewusst sein, so wie Kinder in einer ersten Klasse wissen, dass sie Teil einer Gruppe von Erstklässlern sind, oder die Teilnehmer einer Medikamentenstudie wissen, dass sie Teil einer Gruppe sind, die eine Behandlung erhält. In anderen Fällen werden Personen virtuell in Kohorten eingeteilt, z. B. wenn ein Softwareunternehmen alle in einem bestimmten Jahr gewonnenen Kunden zusammenfasst, um zu untersuchen, wie lange sie Kunden bleiben. Es ist immer wichtig, die ethischen Folgen zu bedenken, wenn Personen ohne ihr Wissen in Kohorten eingeteilt werden und eine andere Behandlung erhalten sollen.

Die Kohortenanalyse ist eine nützliche Methode, um Gruppen von Personen im Laufe der Zeit zu vergleichen. Viele wichtige Verhaltensweisen entwickeln sich über Wochen, Monate oder Jahre, und die Kohortenanalyse ist eine Möglichkeit, diese Veränderungen zu verstehen. Die Kohortenanalyse bietet einen Rahmen, um Korrelationen zwischen Kohortenmerkmalen und diesen langfristigen Trends zu erkennen, was zu Hypothesen über die kausalen Faktoren führen kann. Zum Beispiel können Kunden, die durch eine Marketingkampagne gewonnen wurden, ein anderes langfristiges Kaufverhalten aufweisen als diejenigen, die von einem Freund überredet wurden, die Produkte eines Unternehmens auszuprobieren. Mit Hilfe der Kohortenanalyse können neue Kohorten von Nutzern oder Kunden beobachtet und bewertet werden, wie sie sich von früheren Kohorten unterscheiden. Eine solche Überwachung kann ein frühzeitiges Warnsignal dafür sein, dass bei neuen Kunden etwas falsch (oder richtig) gelaufen ist. Die Kohortenanalyse wird auch zur Auswertung historischer Daten verwendet. A/B-Tests, die in Kapitel 7 besprochen werden, sind der Goldstandard für die Bestimmung der Kausalität, aber wir können nicht in der Zeit zurückgehen und jeden Test für jede Frage aus der Vergangenheit, die uns interessiert, durchführen. Wir sollten natürlich vorsichtig sein, wenn es darum geht, der Kohortenanalyse eine kausale Bedeutung beizumessen, und die Kohortenanalyse stattdessen als eine Möglichkeit nutzen, Kunden zu verstehen und Hypothesen aufzustellen, die in der Zukunft rigoros getestet werden können.

Kohortenanalysen haben drei Komponenten: die Gruppierung der Kohorte, eine Zeitreihe von Daten, über die die Kohorte beobachtet wird, und eine aggregierte Kennzahl, die eine von den Kohortenmitgliedern durchgeführte Aktion misst.

Die Einteilung in Kohorten basiert oft auf einem Anfangsdatum: dem Datum des ersten Kaufs oder Abonnements eines Kunden, dem Datum der Einschulung eines Schülers und so weiter. Kohorten können aber auch anhand anderer Merkmale gebildet werden, die entweder angeboren sind oder sich im Laufe der Zeit ändern. Zu den angeborenen Merkmalen gehören Geburtsjahr und Herkunftsland oder das Gründungsjahr eines Unternehmens. Zu den Merkmalen, die sich im Laufe der Zeit ändern können, gehören Wohnort und Familienstand. Wenn wir diese Merkmale verwenden, müssen wir darauf achten, dass die Kohorte nur den Wert des Anfangsdatums enthält, sonst können die Unternehmen zwischen den Kohortengruppen springen.

Kohorte oder Segment?

Diese beiden Begriffe werden oft ähnlich oder sogar austauschbar verwendet, aber es lohnt sich, der Klarheit halber einen Unterschied zwischen ihnen zu machen. Eine Kohorte ist eine Gruppe von Nutzern (oder anderen Entitäten), die ein gemeinsames Startdatum haben und im Laufe der Zeit verfolgt werden. Ein Segment ist eine Gruppe von Nutzern, die zu einem bestimmten Zeitpunkt ein gemeinsames Merkmal oder eine Reihe von Merkmalen aufweisen, unabhängig von ihrem Startdatum. Ähnlich wie bei Kohorten können Segmente auf angeborenen Faktoren wie dem Alter oder auf Verhaltensmerkmalen beruhen. Ein Segment von Nutzern, die sich im selben Monat anmelden, kann in eine Kohorte eingeordnet und über einen längeren Zeitraum verfolgt werden. Oder es können verschiedene Gruppen von Nutzern mit einer Kohortenanalyse untersucht werden, um zu sehen, welche die wertvollsten Merkmale aufweisen. Die Analysen, die wir in diesem Kapitel behandeln, wie z. B. die Kundenbindung, können dabei helfen, konkrete Daten für Marketingsegmente zu liefern.

Die zweite Komponente jeder Kohortenanalyse ist die Zeitreihe. Dabei handelt es sich um eine Reihe von Käufen, Anmeldungen, Interaktionen oder anderen Aktionen, die von den zu kohortenden Kunden oder Unternehmen durchgeführt werden. Es ist wichtig, dass die Zeitreihe die gesamte Lebensspanne der Entitäten abdeckt, da es sonst zu einer Überlebensverzerrung in frühen Kohorten kommt. Ein Survivorship Bias tritt auf, wenn nur Kunden, die geblieben sind, im Datensatz enthalten sind; abgewanderte Kunden werden ausgeschlossen, weil sie nicht mehr da sind, so dass der Rest der Kunden im Vergleich zu den neueren Kohorten von höherer Qualität oder Eignung zu sein scheint (siehe "Survivorship Bias"). Es ist auch wichtig, eine Zeitreihe zu haben, die lang genug ist, damit die Entitäten die interessierende Aktion abschließen können. Wenn Kunden zum Beispiel einmal im Monat einkaufen, wird eine Zeitreihe von mehreren Monaten benötigt. Wenn die Käufe hingegen nur einmal im Jahr stattfinden, wäre eine Zeitreihe von mehreren Jahren vorzuziehen. Es ist unvermeidlich, dass Kunden, die erst vor kurzem gekauft haben, nicht so lange Zeit hatten, um ihre Aktionen abzuschließen, wie Kunden, die schon länger im Geschäft sind. Um eine Normalisierung zu erreichen, wird bei der Kohortenanalyse in der Regel nicht die Anzahl der Kalendermonate, sondern die Anzahl der Zeiträume gemessen, die seit einem Anfangsdatum verstrichen sind. Auf diese Weise können Kohorten in Periode 1, Periode 2 und so weiter verglichen werden, um zu sehen, wie sie sich im Laufe der Zeit entwickeln, unabhängig davon, in welchem Monat die Aktion tatsächlich stattgefunden hat. Die Intervalle können Tage, Wochen, Monate oder Jahre sein.

Die aggregierte Kennzahl sollte sich auf auf die Aktionen beziehen, die für das Wohlergehen der Organisation wichtig sind, z. B. dass Kunden das Produkt weiterhin nutzen oder kaufen. Die Werte der Metrik werden über die gesamte Kohorte aggregiert, normalerweise mit sum, count oder average, aber jede relevante Aggregation ist möglich. Das Ergebnis ist eine Zeitreihe, die genutzt werden kann, um Veränderungen im Verhalten im Laufe der Zeit zu verstehen.

In diesem Kapitel gehe ich auf vier Arten der Kohortenanalyse ein: Retention, Survivorship, Returnship oder Wiederkaufverhalten und kumulatives Verhalten.

Rückhaltung
Bei der Verweildauer geht es darum, ob das Kohortenmitglied zu einem bestimmten Datum einen Eintrag in der Zeitreihe hat, ausgedrückt als Anzahl der Perioden ab dem Startdatum. Dies ist in jeder Art von Organisation nützlich, in der wiederholte Handlungen erwartet werden, vom Spielen eines Online-Spiels bis hin zur Nutzung eines Produkts oder der Verlängerung eines Abonnements, und es hilft bei der Beantwortung der Frage, wie "klebrig" oder "engagiert" ein Produkt ist und wie viele Entitäten zu erwarten sind, die an zukünftigen Daten erscheinen.
Survivorship
Bei der Überlebensrate geht es darum, wie viele Personen über einen bestimmten Zeitraum oder länger im Datensatz verblieben sind, unabhängig von der Anzahl oder Häufigkeit der Aktionen bis zu diesem Zeitpunkt. Die Überlebensrate ist nützlich für die Beantwortung von Fragen über den Anteil der Bevölkerung, von dem erwartet werden kann, dass er bleibt - entweder im positiven Sinne, indem er nicht abwandert oder stirbt, oder im negativen Sinne, indem er nicht abschließt oder eine Anforderung erfüllt.
Rücksendung
Wiederkehrendes oder wiederholtes Kaufverhalten befasst sich damit, ob eine Handlung mehr als eine bestimmte Mindestanzahl von Malen - oft einfach mehr als einmal - innerhalb eines festen Zeitfensters stattgefunden hat. Diese Art der Analyse ist in Situationen nützlich, in denen das Verhalten unregelmäßig und unvorhersehbar ist, wie zum Beispiel im Einzelhandel, wo sie den Anteil der Wiederholungskäufer in jeder Kohorte innerhalb eines festen Zeitfensters charakterisiert.
Kumulativ
Kumulative Berechnungen befassen sich mit der Gesamtzahl oder den Beträgen, die in einem oder mehreren festgelegten Zeitfenstern gemessen werden, unabhängig davon, wann sie in diesem Fenster stattgefunden haben. Kumulative Berechnungen werden häufig bei der Berechnung des Customer Lifetime Value (LTV oder CLTV) verwendet.

Die vier Arten der Kohortenanalyse ermöglichen es uns, Untergruppen zu vergleichen und zu verstehen, wie sie sich im Laufe der Zeit unterscheiden, damit wir bessere Produkt-, Marketing- und Finanzentscheidungen treffen können. Die Berechnungen für die verschiedenen Typen sind ähnlich, daher werden wir mit der Bindungsdauer beginnen und dann zeigen, wie wir den Code für die Berechnung der anderen Typen anpassen. Bevor wir mit dem Aufbau unserer Kohortenanalyse beginnen, werfen wir einen Blick auf den Datensatz, den wir für die Beispiele in diesem Kapitel verwenden werden.

Der Datensatz der Gesetzgeber

Die SQL-Beispiele in diesem Kapitel verwenden einen Datensatz mit ehemaligen und aktuellen Mitgliedern des Kongresses der Vereinigten Staaten, der in einem GitHub-Repository verwaltet wird. In den USA ( ) ist der Kongress für die Ausarbeitung von Gesetzen zuständig, weshalb seine Mitglieder auch als Gesetzgeber bezeichnet werden. Da es sich bei dem Datensatz um eine JSON-Datei handelt, habe ich einige Transformationen vorgenommen, um ein geeigneteres Datenmodell für die Analyse zu erstellen, und ich habe die Daten in einem Format bereitgestellt, das sich für die Beispiele im GitHub-Ordner legislators des Buches eignet.

Die Quelle verfügt über ein ausgezeichnetes Datenlexikon, daher werde ich hier nicht alle Details wiederholen. Ich werde jedoch ein paar Details nennen, die denjenigen, die sich nicht mit der US-Regierung auskennen, helfen sollen, den Analysen in diesem Kapitel zu folgen.

Der Kongress hat zwei Kammern, den Senat ("sen" im Datensatz) und das Repräsentantenhaus ("rep"). Jeder Staat hat zwei Senatoren, die für eine Amtszeit von sechs Jahren gewählt werden. Die Repräsentanten werden den Staaten auf der Grundlage der Bevölkerungszahl zugeteilt; jeder Repräsentant hat einen Bezirk, den er allein vertritt. Die Abgeordneten werden für eine Amtszeit von zwei Jahren gewählt. Die tatsächliche Amtszeit kann in beiden Kammern kürzer sein, wenn der Abgeordnete stirbt oder in ein höheres Amt gewählt oder berufen wird. Je länger die Abgeordneten im Amt sind, desto mehr Macht und Einfluss gewinnen sie durch Führungspositionen, weshalb es üblich ist, sich zur Wiederwahl zu stellen. Schließlich kann ein Abgeordneter einer politischen Partei angehören oder "unabhängig" sein. In der heutigen Zeit sind die meisten Abgeordneten Demokraten oder Republikaner, und die Rivalität zwischen den beiden Parteien ist bekannt. Gelegentlich wechseln Abgeordnete während ihrer Amtszeit die Partei.

Für die Analysen verwenden wir zwei Tabellen: legislators und legislators_terms. Die Tabelle legislators enthält eine Liste aller im Datensatz enthaltenen Personen mit Geburtstag, Geschlecht und einer Reihe von ID-Feldern, mit denen die Person in anderen Datensätzen nachgeschlagen werden kann. Die Tabelle legislators_terms enthält einen Datensatz für jede Amtszeit jedes Abgeordneten, mit Anfangs- und Enddatum und anderen Attributen wie Kammer und Partei. Das Feld id_bioguide wird als eindeutige Kennung für einen Abgeordneten verwendet und erscheint in jeder Tabelle. Abbildung 4-1 zeigt ein Beispiel für die Daten von legislators. Abbildung 4-2 zeigt ein Beispiel für die Daten von legislators_terms.

Abbildung 4-1. Beispiel für die Tabelle legislators
Abbildung 4-2. Beispiel für die Tabelle legislators_terms

Nachdem wir nun wissen, was eine Kohortenanalyse ist und welchen Datensatz wir für die Beispiele verwenden werden, wollen wir uns ansehen, wie man SQL für eine Bindungsanalyse schreibt. Die wichtigste Frage, die wir mit SQL beantworten können, lautet: Wie lange bleiben Abgeordnete nach ihrem Amtsantritt im Amt?

Rückhaltung

Eine der häufigsten Arten der Kohortenanalyse ist die Bindungsanalyse. Behalten bedeutet, etwas zu behalten oder fortzuführen. Viele Fähigkeiten müssen geübt werden, um beibehalten zu werden. Unternehmen wollen in der Regel, dass ihre Kunden weiterhin ihre Produkte kaufen oder ihre Dienstleistungen in Anspruch nehmen, denn es ist profitabler, Kunden zu halten, als neue Kunden zu gewinnen. Arbeitgeber wollen ihre Mitarbeiter/innen halten, weil es teuer und zeitaufwändig ist, neue Mitarbeiter/innen zu rekrutieren. Gewählte Beamte wollen wiedergewählt werden, damit sie sich weiterhin für die Prioritäten ihrer Wähler einsetzen können.

Die wichtigste Frage bei der Analyse der Kundenbindung ist, ob die Ausgangsgröße der Kohorte - die Anzahl der Abonnenten oder Mitarbeiter, der ausgegebene Betrag oder eine andere wichtige Kennzahl - im Laufe der Zeit konstant bleibt, abnimmt oder zunimmt. Wenn es zu einem Anstieg oder einem Rückgang kommt, sind auch der Umfang und die Geschwindigkeit der Veränderung von Interesse. Bei den meisten Bindungsanalysen nimmt die Ausgangsgröße im Laufe der Zeit ab, da eine Kohorte zwar Mitglieder verlieren, aber keine neuen hinzugewinnen kann, sobald sie einmal gebildet wurde. Der Umsatz ist eine interessante Ausnahme, da eine Kohorte von Kunden in den Folgemonaten mehr ausgeben kann als im ersten Monat zusammen, selbst wenn einige von ihnen abwandern.

Die Verbleibsanalyse verwendet die count der Entitäten oder sum der Gelder oder Aktionen, die im Datensatz für jeden Zeitraum ab dem Startdatum vorhanden sind, und normalisiert diese Zahl, indem sie durch die count oder sum der Entitäten, Gelder oder Aktionen im ersten Zeitraum geteilt wird. Das Ergebnis wird als Prozentsatz ausgedrückt, wobei die Aufbewahrung im Anfangszeitraum immer 100% beträgt. Im Laufe der Zeit nimmt die auf Zählungen basierende Zurückbehaltung in der Regel ab und kann nie mehr als 100 % betragen, während die auf Geld oder Aktionen basierende Zurückbehaltung zwar oft abnimmt, aber in einem bestimmten Zeitraum auch über 100 % liegen kann. Die Ergebnisse der Kundenbindungsanalyse werden in der Regel in Form einer Tabelle oder eines Diagramms dargestellt, das als Bindungskurve bezeichnet wird. Im weiteren Verlauf dieses Kapitels werden wir eine Reihe von Beispielen für Kundenbindungskurven sehen.

Diagramme von Verbleibskurven können verwendet werden, um Kohorten zu vergleichen. Das erste Merkmal, auf das du achten solltest, ist die Form der Kurve in den ersten paar Perioden, wo es oft einen steilen Abfall gibt. Bei vielen Verbraucher-Apps ist es üblich, dass die Hälfte einer Kohorte in den ersten Monaten verloren geht. Eine Kohorte, deren Kurve mehr oder weniger steil verläuft als andere, kann auf Veränderungen im Produkt oder in der Quelle der Kundenakquise hinweisen, die eine weitere Untersuchung verdienen. Ein zweites Merkmal, auf das du achten solltest, ist, ob die Kurve nach einer bestimmten Anzahl von Perioden abflacht oder schnell auf Null sinkt. Eine abflachende Kurve deutet darauf hin, dass es einen Zeitpunkt gibt, ab dem der größte Teil der verbleibenden Kohorte auf unbestimmte Zeit bleibt. Eine Bindungskurve, die sich nach oben wölbt, wird manchmal auch als Smile-Kurve bezeichnet und kann auftreten, wenn Mitglieder der Kohorte zurückkehren oder reaktiviert werden, nachdem sie für einen bestimmten Zeitraum aus dem Datensatz herausgefallen sind. Schließlich werden Kundenbindungskurven, die den Abonnementumsatz messen, auf Anzeichen für steigende Umsätze pro Kunde im Laufe der Zeit überwacht - ein Zeichen für ein gesundes SaaS-Softwaregeschäft.

In diesem Abschnitt wird gezeigt, wie du eine Verbleibsanalyse erstellst, Kohortengruppierungen aus der Zeitreihe selbst und anderen Tabellen hinzufügst und mit fehlenden und spärlichen Daten umgehst, die in Zeitreihendaten vorkommen können. Mit diesem Grundgerüst in der Hand erfährst du im nächsten Abschnitt, wie du Änderungen vornehmen kannst, um die anderen verwandten Arten von Kohortenanalysen zu erstellen. Daher wird dieser Abschnitt zum Thema Selbstbehalt der längste in diesem Kapitel sein, da du Code aufbaust und dein Gespür für die Berechnungen entwickelst.

SQL für eine grundlegende Retentionskurve

Für die Verbleibsanalyse benötigen wir, wie bei anderen Kohortenanalysen auch, drei Komponenten: die Definition der Kohorte, eine Zeitreihe von Aktionen und eine aggregierte Kennzahl, die etwas für die Organisation oder den Prozess Relevantes misst. In unserem Fall sind die Mitglieder der Kohorte die Abgeordneten, die Zeitreihe sind die Amtszeiten der einzelnen Abgeordneten und die Kennzahl, die uns interessiert, ist die count derjenigen, die in jedem Zeitraum ab dem Startdatum noch im Amt sind.

Wir beginnen mit der Berechnung der grundlegenden Selbstbehalte, bevor wir zu den Beispielen übergehen, die verschiedene Kohortengruppen umfassen. Der erste Schritt besteht darin, das erste Datum des Amtsantritts jedes Abgeordneten zu ermitteln (first_term). Anhand dieses Datums berechnen wir die Anzahl der Perioden für jedes weitere Datum in der Zeitreihe. Dazu nimmst du die min der term_start und GRUPPE BY jeder id_bioguide, der eindeutigen Kennung für einen Abgeordneten:

SELECT id_bioguide
,min(term_start) as first_term
FROM legislators_terms 
GROUP BY 1
;

id_bioguide  first_term
-----------  ----------
A000118      1975-01-14
P000281      1933-03-09
K000039      1933-03-09
...          ...

Der nächste Schritt besteht darin, diesen Code in eine Unterabfrage zu packen und sie mit den Zeitreihen zu JOINen. Die Funktion age wird angewendet, um die Intervalle zwischen term_start und first_term für jeden Gesetzgeber zu berechnen. Die Anwendung der date_part Funktionen auf das Ergebnis, mit Jahr, wandelt dieses in die Anzahl der jährlichen Perioden um. Da Wahlen alle zwei oder sechs Jahre stattfinden, verwenden wir Jahre als Zeitintervall für die Berechnung der periods. Wir könnten auch ein kürzeres Intervall verwenden, aber in diesem Datensatz gibt es kaum tägliche oder wöchentliche Schwankungen. Die count der Gesetzgeber mit Aufzeichnungen für diesen Zeitraum ist die Anzahl, die beibehalten wird:

SELECT date_part('year',age(b.term_start,a.first_term)) as period
,count(distinct a.id_bioguide) as cohort_retained
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms 
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
GROUP BY 1
;

period  cohort_retained
------  ---------------
0.0     12518
1.0     3600
2.0     3619
...     ...
Tipp

In Datenbanken, die die Funktion datediff unterstützen, können die date_part und age Konstruktion durch diese einfachere Funktion ersetzt werden:

datediff('year',first_term,term_start)

Einige Datenbanken, wie z. B. Oracle, platzieren die date_part zuletzt:

datediff(first_term,term_start,'year'

Jetzt, da wir die Zeiträume und die Anzahl der Gesetzgeber in jedem Zeitraum haben, besteht der letzte Schritt darin, die Gesamtzahl cohort_size zu berechnen und sie in jede Zeile einzutragen, so dass die cohort_retained durch sie geteilt werden kann. Die Fensterfunktion first_value gibt den ersten Datensatz in der PARTITION BY-Klausel zurück, entsprechend der in der ORDER BY-Klausel festgelegten Reihenfolge, eine praktische Methode, um die Kohortengröße in jeder Zeile zu erhalten. In diesem Fall stammt die cohort_size aus dem ersten Datensatz des gesamten Datensatzes, sodass die PARTITION BY-Klausel weggelassen wird:

first_value(cohort_retained) over (order by period) as cohort_size

Um den Prozentwert zu ermitteln, teilst du den cohort_retained Wert durch dieselbe Berechnung:

SELECT period
,first_value(cohort_retained) over (order by period) as cohort_size
,cohort_retained
,cohort_retained / 
 first_value(cohort_retained) over (order by period) as pct_retained
FROM
(
    SELECT date_part('year',age(b.term_start,a.first_term)) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms 
        GROUP BY 1
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    GROUP BY 1
) aa
;

period  cohort_size  cohort_retained  pct_retained
------  -----------  ---------------  ------------
0.0     12518        12518            1.0000
1.0     12518        3600             0.2876
2.0     12518        3619             0.2891
...     ...          ...              ...

Wir haben jetzt eine Berechnung der Beibehaltung und können sehen, dass es einen starken Abfall zwischen den 100 % der Abgeordneten, die in der Periode 0 oder an ihrem Anfangsdatum beibehalten werden, und dem Anteil mit einer weiteren Amtszeit, die ein Jahr später beginnt, gibt. Eine grafische Darstellung der Ergebnisse, wie in Abbildung 4-3, zeigt, wie die Kurve abflacht und schließlich gegen Null geht, da selbst die dienstältesten Abgeordneten irgendwann in den Ruhestand gehen oder sterben.

Abbildung 4-3. Beibehaltung ab Beginn der ersten Amtszeit für US-Abgeordnete

Wir können das Ergebnis der Kohortenbindung nehmen und die Daten so umgestalten, dass sie im Tabellenformat angezeigt werden. Pivotiere und verflache die Ergebnisse mit einer Aggregatfunktion und einer CASE-Anweisung; in diesem Beispiel wird max verwendet, aber auch andere Aggregate wie min oder avg würden das gleiche Ergebnis liefern. Der Selbstbehalt wird für die Jahre 0 bis 4 berechnet, aber weitere Jahre können nach demselben Muster hinzugefügt werden:

SELECT cohort_size
,max(case when period = 0 then pct_retained end) as yr0
,max(case when period = 1 then pct_retained end) as yr1
,max(case when period = 2 then pct_retained end) as yr2
,max(case when period = 3 then pct_retained end) as yr3
,max(case when period = 4 then pct_retained end) as yr4
FROM
(
    SELECT period
    ,first_value(cohort_retained) over (order by period) 
     as cohort_size
    ,cohort_retained 
     / first_value(cohort_retained) over (order by period)
     as pct_retained
    FROM
    (
        SELECT 
        date_part('year',age(b.term_start,a.first_term)) as period
        ,count(*) as cohort_retained
        FROM
        (
            SELECT id_bioguide, min(term_start) as first_term
            FROM legislators_terms 
            GROUP BY 1
        ) a
        JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
        GROUP BY 1
    ) aa
) aaa
GROUP BY 1
;

cohort_size  yr0     yr1     yr2     yr3     yr4
-----------  ------  ------  ------  ------  ------
12518        1.0000  0.2876  0.2891  0.1463  0.2564

Die Verweildauer scheint recht niedrig zu sein, und aus der Grafik können wir ersehen, dass sie in den ersten Jahren zerklüftet ist. Ein Grund dafür ist, dass die Amtszeit eines Abgeordneten zwei Jahre und die eines Senators sechs Jahre dauert, der Datensatz aber nur Aufzeichnungen über den Beginn einer neuen Amtszeit enthält; uns fehlen also Daten für Jahre, in denen ein Abgeordneter noch im Amt war, aber keine neue Amtszeit begonnen hat. In diesem Fall ist es irreführend, die Beibehaltung jedes Jahr zu messen. Eine Möglichkeit ist, den Verbleib im Amt nur im Zwei- oder Sechsjahresrhythmus zu messen, aber es gibt auch eine andere Strategie, mit der wir die "fehlenden" Daten auffüllen können. Darauf werde ich als Nächstes eingehen, bevor ich zum Thema Bildung von Kohortengruppen zurückkehre.

Zeitreihen anpassen, um die Genauigkeit der Aufbewahrung zu erhöhen

In Kapitel 2 haben wir Techniken zur Bereinigung "fehlender" Daten besprochen, und in diesem Abschnitt werden wir uns diesen Techniken zuwenden, um eine glattere und wahrheitsgetreuere Verbleibkurve für die Gesetzgeber zu erhalten. Bei der Arbeit mit Zeitreihendaten, wie z. B. bei der Kohortenanalyse, ist es wichtig, nicht nur die vorhandenen Daten zu berücksichtigen, sondern auch, ob diese Daten die An- oder Abwesenheit von Personen in jedem Zeitraum genau widerspiegeln. Das ist vor allem dann ein Problem, wenn ein in den Daten erfasstes Ereignis dazu führt, dass die Entität für einen Zeitraum besteht, der nicht in den Daten erfasst ist. Ein Kunde, der zum Beispiel ein Software-Abonnement kauft, ist zum Zeitpunkt der Transaktion in den Daten enthalten, hat aber das Recht, die Software über Monate oder Jahre hinweg zu nutzen, und ist in den Daten nicht unbedingt über diesen Zeitraum hinweg enthalten. Um dies zu korrigieren, brauchen wir eine Möglichkeit, die Zeitspanne zu ermitteln, in der die Entität noch vorhanden ist, entweder mit einem expliziten Enddatum oder mit dem Wissen über die Länge des Abonnements oder der Laufzeit. Dann können wir sagen, dass die Entität zu einem beliebigen Datum zwischen dem Start- und dem Enddatum anwesend war.

Im Datensatz der Abgeordneten haben wir einen Datensatz für den Beginn der Amtszeit, aber uns fehlt der Hinweis, dass dies einen Abgeordneten je nach Kammer zu einer Amtszeit von zwei oder sechs Jahren "berechtigt". Um dies zu korrigieren und die Kurve zu glätten, müssen wir die "fehlenden" Werte für die Jahre, in denen die Abgeordneten zwischen den neuen Amtszeiten noch im Amt sind, auffüllen. Da dieser Datensatz einen term_end Wert für jede Amtszeit enthält, zeige ich dir, wie du eine genauere Analyse der Kohortenbindung erstellen kannst, indem du die Daten zwischen den Anfangs- und Endwerten einfügst. Dann zeige ich dir, wie du Enddaten errechnen kannst, wenn der Datensatz kein Enddatum enthält.

Die Berechnung der Amtszeit anhand eines in den Daten definierten Anfangs- und Enddatums ist der genaueste Ansatz. In den folgenden Beispielen wird davon ausgegangen, dass Abgeordnete in einem bestimmten Jahr im Amt blieben, wenn sie am letzten Tag des Jahres, dem 31. Dezember, noch im Amt waren. Vor dem 20. Zusatzartikel zur US-Verfassung begann die Legislaturperiode am 4. März, danach wurde das Datum auf den 3. Januar verlegt oder auf einen anderen Wochentag, wenn der dritte auf ein Wochenende fällt. Gesetzgeber können an anderen Tagen des Jahres vereidigt werden, wenn besondere Wahlen außerhalb des Zyklus stattfinden oder freie Sitze neu besetzt werden. Daher häufen sich die Termine von term_start im Januar, sind aber über das ganze Jahr verteilt. Wir könnten zwar auch einen anderen Tag wählen, aber der 31. Dezember ist eine Strategie, um diese unterschiedlichen Anfangsdaten zu normalisieren.

Der erste Schritt besteht darin, einen Datensatz zu erstellen, der einen Datensatz für jeden 31. Dezember enthält, an dem die einzelnen Abgeordneten im Amt waren. Dazu wird die Subquery, die die first_term gefunden hat, mit der Tabelle legislators_terms gejoint, um die term_start und term_end für jede Amtszeit zu finden. Ein zweiter JOIN mit der Tabelle date_dim ruft die Daten ab, die zwischen dem Anfangs- und dem Enddatum liegen, und schränkt die zurückgegebenen Werte auf c.month_name = 'December' and c.day_of_month = 31 ein. period wird als die Jahre zwischen date aus date_dim und first_term berechnet. Beachte, dass das erste Jahr immer noch als 0 erscheint, obwohl zwischen der Vereidigung im Januar und dem 31. Dezember mehr als 11 Monate vergangen sein können:

SELECT a.id_bioguide, a.first_term
,b.term_start, b.term_end
,c.date
,date_part('year',age(c.date,a.first_term)) as period
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms 
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
and c.month_name = 'December' and c.day_of_month = 31
;

id_bioguide  first_term  term_start  term_end    date        period
-----------  ----------  ----------  ----------  ----------  ------
B000944      1993-01-05  1993-01-05  1995-01-03  1993-12-31  0.0
B000944      1993-01-05  1993-01-05  1995-01-03  1994-12-31  1.0
C000127      1993-01-05  1993-01-05  1995-01-03  1993-12-31  0.0
...          ...         ...         ...         ...         ...
Tipp

Wenn keine Datumsdimension verfügbar ist, kannst du eine Unterabfrage mit den erforderlichen Daten auf verschiedene Weise erstellen. Wenn deine Datenbank die generate_series unterstützt, kannst du eine Unterabfrage erstellen, die die gewünschten Daten zurückgibt:

SELECT generate_series::date as date
FROM generate_series('1770-12-31','2020-12-
31',interval '1 year')

Vielleicht möchtest du diese Daten als Tabelle oder Ansicht zur späteren Verwendung speichern. Alternativ kannst du auch den Datensatz oder eine andere Tabelle in der Datenbank abfragen, die einen vollständigen Satz von Daten enthält. In diesem Fall enthält die Tabelle alle notwendigen Jahre, aber wir werden mit der Funktion make_date für jedes Jahr ein Datum zum 31. Dezember erstellen:

SELECT distinct
make_date(date_part('year',term_start)::int,12,31)
FROM legislators_terms

Es gibt eine Reihe kreativer Methoden, um die benötigten Datenreihen zu erhalten. Verwende die Methode, die dir zur Verfügung steht und die am einfachsten in deine Abfragen passt.

Wir haben jetzt eine Zeile für jedes date (Jahresende), für das wir den Selbstbehalt berechnen möchten. Der nächste Schritt ist die Berechnung der cohort_retained für jede Periode, was mit einer count von id_bioguide geschieht. Eine coalesce Funktion wird auf period verwendet, um einen Standardwert von 0 zu setzen, wenn er nicht vorhanden ist. Damit werden die Fälle behandelt, in denen die Amtszeit eines Abgeordneten im selben Jahr beginnt und endet, so dass die Amtszeit in diesem Jahr angerechnet wird:

SELECT 
coalesce(date_part('year',age(c.date,a.first_term)),0) as period
,count(distinct a.id_bioguide) as cohort_retained
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms 
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
and c.month_name = 'December' and c.day_of_month = 31
GROUP BY 1
;

period  cohort_retained
------  ---------------
0.0     12518
1.0     12328
2.0     8166
...     ...

Der letzte Schritt besteht darin, cohort_size und pct_retained zu berechnen, wie wir es zuvor mit den first_value Fensterfunktionen getan haben:

SELECT period
,first_value(cohort_retained) over (order by period) as cohort_size
,cohort_retained
,cohort_retained * 1.0 / 
 first_value(cohort_retained) over (order by period) as pct_retained
FROM
(
    SELECT coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms 
        GROUP BY 1
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31
    GROUP BY 1
) aa
;

period  cohort_size  cohort_retained  pct_retained
------  -----------  ---------------  ------------
0.0     12518        12518            1.0000
1.0     12518        12328            0.9848
2.0     12518        8166             0.6523
...     ...          ...              ...

Die Ergebnisse, die in Abbildung 4-4 dargestellt sind, sind jetzt viel genauer. Fast alle Abgeordneten sind im Jahr 1 noch im Amt, und der erste große Einbruch erfolgt im Jahr 2, wenn einige Abgeordnete nicht wiedergewählt werden.

Abbildung 4-4. Verbleib der Abgeordneten im Amt nach Bereinigung um die tatsächlichen Jahre im Amt

Wenn der Datensatz kein Enddatum enthält, gibt es mehrere Möglichkeiten, ein solches zu ermitteln. Eine Möglichkeit ist, ein festes Intervall zum Startdatum hinzuzufügen, wenn die Länge eines Abonnements oder einer Laufzeit bekannt ist. Das geht mit der Datumsberechnung, indem du ein konstantes Intervall zu term_start hinzufügst. Hier übernimmt eine CASE-Anweisung die Addition für die beiden term_types:

SELECT a.id_bioguide, a.first_term
,b.term_start
,case when b.term_type = 'rep' then b.term_start + interval '2 years'
      when b.term_type = 'sen' then b.term_start + interval '6 years'
      end as term_end
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms 
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
;

id_bioguide  first_term  term_start  term_end
-----------  ----------  ----------  ----------
B000944      1993-01-05  1993-01-05  1995-01-05
C000127      1993-01-05  1993-01-05  1995-01-05
C000141      1987-01-06  1987-01-06  1989-01-06
...          ...         ...         ...

Dieser Code-Block kann dann mit dem Code für die Beibehaltung der Amtszeit verbunden werden, um die period und pct_retained zu erhalten. Der Nachteil dieser Methode ist, dass sie nicht die Fälle erfasst, in denen ein Abgeordneter nicht die gesamte Amtszeit vollendet hat, was im Falle eines Todes oder einer Ernennung in ein höheres Amt vorkommen kann.

Eine zweite Möglichkeit ist, das spätere Startdatum minus einen Tag als term_end Datum zu verwenden. Dieses kann mit der Fensterfunktion lead berechnet werden. Diese Funktion ähnelt der Funktion lag, die wir bereits verwendet haben, aber anstatt einen Wert aus einer Zeile zu einem früheren Zeitpunkt in der Partition zurückzugeben, gibt sie einen Wert aus einer Zeile zu einem späteren Zeitpunkt in der Partition zurück, wie in der ORDER BY-Klausel festgelegt. Die Standardeinstellung ist eine Zeile, die wir hier verwenden werden, aber die Funktion hat ein optionales Argument, das eine andere Anzahl von Zeilen angibt. Hier finden wir das term_start Datum des nachfolgenden Terms mit lead und subtrahieren dann das Intervall '1 day', um den term_end abzuleiten:

SELECT a.id_bioguide, a.first_term
,b.term_start
,lead(b.term_start) over (partition by a.id_bioguide 
                          order by b.term_start) 
 - interval '1 day' as term_end
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms 
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
;

id_bioguide  first_term  term_start  term_end
-----------  ----------  ----------  ----------
A000001      1951-01-03  1951-01-03  (null)
A000002      1947-01-03  1947-01-03  1949-01-02
A000002      1947-01-03  1949-01-03  1951-01-02
...          ...         ...         ...

Dieser Codeblock kann dann in den Retentionscode eingefügt werden. Diese Methode hat ein paar Nachteile. Erstens: Wenn es keine nachfolgende Amtszeit gibt, gibt die Funktion lead den Wert Null zurück, so dass diese Amtszeit ohne term_end bleibt. In solchen Fällen könnte ein Standardwert verwendet werden, z. B. ein Standardintervall wie im letzten Beispiel. Der zweite Nachteil ist, dass bei dieser Methode davon ausgegangen wird, dass die Amtszeiten immer aufeinander folgen und es keine Unterbrechungen gibt. Obwohl die meisten Abgeordneten ihre Amtszeit bis zum Ende der Legislaturperiode ununterbrochen fortsetzen, gibt es durchaus Beispiele für mehrjährige Lücken zwischen den Amtszeiten.

Jedes Mal, wenn wir Anpassungen vornehmen, um fehlende Daten zu ergänzen, müssen wir vorsichtig sein, welche Annahmen wir treffen. Im Kontext von Abonnements oder Laufzeiten sind explizite Start- und Enddaten in der Regel am genauesten. Eine der beiden anderen Methoden - das Hinzufügen eines festen Intervalls oder das Festlegen des Enddatums im Verhältnis zum nächsten Startdatum - kann verwendet werden, wenn kein Enddatum vorhanden ist und wir davon ausgehen können, dass die meisten Kunden oder Nutzer für die angenommene Dauer bleiben werden.

Nachdem wir nun gesehen haben, wie man eine grundlegende Verbleibskurve berechnet und fehlende Daten korrigiert, können wir mit dem Hinzufügen von Kohortengruppen beginnen. Der Vergleich des Verbleibs zwischen verschiedenen Gruppen ist einer der Hauptgründe für die Durchführung von Kohortenanalysen. Als Nächstes werde ich die Bildung von Gruppen aus der Zeitreihe selbst besprechen und danach die Bildung von Kohortengruppen aus Daten in anderen Tabellen.

Aus der Zeitreihe selbst abgeleitete Kohorten

Jetzt, wo wir den SQL-Code zur Berechnung des Verbleibs haben, können wir damit beginnen, die Entitäten in Kohorten aufzuteilen. In diesem Abschnitt zeige ich, wie wir Kohorten aus der Zeitreihe selbst ableiten können. Zunächst gehe ich auf zeitbasierte Kohorten ein, die auf dem ersten Datum basieren, und erkläre dann, wie man Kohorten auf der Grundlage anderer Attribute der Zeitreihe erstellt.

Die gängigste Art, Kohorten zu erstellen, basiert auf dem ersten oder minimalen Datum oder Zeitpunkt, an dem die Entität in der Zeitreihe erscheint. Das bedeutet, dass nur eine Tabelle für die Analyse der Kohortenbindung erforderlich ist: die Zeitreihe selbst. Die Kohortenbildung nach dem ersten Auftreten oder der ersten Aktion ist interessant, weil sich Gruppen, die zu unterschiedlichen Zeitpunkten beginnen, oft unterschiedlich verhalten. Bei Dienstleistungen für Verbraucher sind frühe Nutzer oft enthusiastischer und halten sich anders als spätere Nutzer, während bei SaaS-Software spätere Nutzer sich besser halten, weil das Produkt ausgereifter ist. Zeitliche Kohorten können nach jeder für das Unternehmen sinnvollen Zeitgranularität gruppiert werden, wobei wöchentliche, monatliche oder jährliche Kohorten üblich sind. Wenn du dir nicht sicher bist, welche Gruppierung du verwenden sollst, probiere die Kohortenanalyse mit verschiedenen Gruppierungen aus, ohne die Kohortengrößen zu klein zu machen, um zu sehen, wo sich aussagekräftige Muster ergeben. Wenn du einmal weißt, wie du die Kohorten und die Bindungsanalyse erstellst, ist es zum Glück ganz einfach, verschiedene zeitliche Granularitäten auszutauschen.

Im ersten Beispiel werden wir jährliche Kohorten verwenden, und dann werde ich den Austausch in Jahrhunderten demonstrieren. Die wichtigste Frage, die wir uns stellen werden, ist, ob die Ära, in der ein Abgeordneter sein Amt zum ersten Mal angetreten hat, mit seinem Verbleib im Amt zusammenhängt. Politische Trends und die öffentliche Stimmung ändern sich im Laufe der Zeit, aber um wie viel?

Um die jährlichen Kohorten zu berechnen, fügen wir zunächst das Jahr der zuvor berechneten first_term zu der Abfrage hinzu, die die period und cohort_retained findet:

SELECT date_part('year',a.first_term) as first_year
,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
,count(distinct a.id_bioguide) as cohort_retained
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms 
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
and c.month_name = 'December' and c.day_of_month = 31
GROUP BY 1,2
;

first_year  period  cohort_retained
----------  ------  ---------------
1789.0      0.0     89
1789.0      2.0     89
1789.0      3.0     57
...         ...     ...

Diese Abfrage wird dann als Unterabfrage verwendet, und die cohort_size und pct_retained werden in der äußeren Abfrage wie zuvor berechnet. In diesem Fall brauchen wir jedoch eine PARTITION BY-Klausel, die first_year einschließt, damit first_value nur innerhalb der Zeilen für diese first_year berechnet wird und nicht über die gesamte Ergebnismenge der Unterabfrage:

SELECT first_year, period
,first_value(cohort_retained) over (partition by first_year 
                                    order by period) as cohort_size
,cohort_retained
,cohort_retained / 
 first_value(cohort_retained) over (partition by first_year 
                                    order by period) as pct_retained
FROM
(
    SELECT date_part('year',a.first_term) as first_year
    ,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms 
        GROUP BY 1
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31
    GROUP BY 1,2
) aa
;

first_year  period  cohort_size  cohort_retained  pct_retained
----------  ------  -----------  ---------------  ------------
1789.0      0.0     89           89               1.0000
1789.0      2.0     89           89               1.0000
1789.0      3.0     89           57               0.6404
...         ...     ...          ...              ...

Dieser Datensatz umfasst mehr als zweihundert Anfangsjahre, zu viele, um sie einfach grafisch darzustellen oder in einer Tabelle zu untersuchen. Als Nächstes schauen wir uns ein weniger granulares Intervall an und kohorten die Gesetzgeber nach dem Jahrhundert der first_term. Diese Änderung lässt sich leicht vornehmen, indem man century für year in der Funktion date_part in der Unterabfrage aa ersetzt. Das 18. Jahrhundert dauerte von 1700 bis 1799, das 19. Jahrhundert von 1800 bis 1899 usw. Die Namen der Jahrhunderte sind gegenüber den Jahren, die sie repräsentieren, versetzt. Die Aufteilung in der Funktion first_value ändert sich in das Feld first_century:

SELECT first_century, period
,first_value(cohort_retained) over (partition by first_century 
                                    order by period) as cohort_size
,cohort_retained
,cohort_retained / 
 first_value(cohort_retained) over (partition by first_century 
                                    order by period) as pct_retained
FROM
(
    SELECT date_part('century',a.first_term) as first_century
    ,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms 
        GROUP BY 1
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31
    GROUP BY 1,2
) aa
ORDER BY 1,2
;

first_century  period  cohort_size  cohort_retained  pct_retained
-------------  ------  -----------  ---------------  ------------
18.0           0.0     368          368              1.0000
18.0           1.0     368          360              0.9783
18.0           2.0     368          242              0.6576
...            ...     ...          ...              ...

Die Ergebnisse sind in Abbildung 4-5 grafisch dargestellt. Die Verweildauer in den ersten Jahren war bei denjenigen höher, die erstmals im 20. oder 21. Das 21. Jahrhundert ist noch in vollem Gange und daher hatten viele dieser Abgeordneten noch nicht die Gelegenheit, fünf oder mehr Jahre im Amt zu bleiben, obwohl sie im Nenner enthalten sind. Vielleicht sollten wir das 21. Jahrhundert aus der Analyse herausnehmen, aber ich habe es hier belassen, um zu zeigen, wie die Beibehaltungskurve durch diesen Umstand künstlich sinkt.

Abbildung 4-5. Beibehaltung der Legislative nach Jahrhundert, in dem die erste Amtszeit begann

Kohorten können aus anderen Attributen in einer Zeitreihe als dem ersten Datum definiert werden, wobei die Optionen von den Werten in der Tabelle abhängen. Die Tabelle legislators_terms hat ein Feld state, das angibt, welchen Zustand die Person für diesen Begriff repräsentiert. Wir können dies nutzen, um Kohorten zu erstellen, und wir werden sie auf das erste Bundesland beziehen, um sicherzustellen, dass jeder, der mehrere Bundesländer vertreten hat, nur einmal in den Daten erscheint.

Warnung

Bei der Kohortenbildung anhand eines Attributs, das sich im Laufe der Zeit ändern kann, ist es wichtig, dass jeder Entität nur ein Wert zugewiesen wird. Andernfalls kann die Entität in mehreren Kohorten vertreten sein, was zu Verzerrungen in der Analyse führt. Normalerweise wird der Wert des frühesten Datensatzes im Datensatz verwendet.

Um das erste Bundesland für jeden Gesetzgeber zu finden, können wir die Fensterfunktion first_value verwenden. In diesem Beispiel verwandeln wir die Funktion min ebenfalls in eine Fensterfunktion, um eine lange GROUP BY-Klausel zu vermeiden:

SELECT distinct id_bioguide
,min(term_start) over (partition by id_bioguide) as first_term
,first_value(state) over (partition by id_bioguide 
                          order by term_start) as first_state
FROM legislators_terms 
;

id_bioguide  first_term  first_state
-----------  ----------  -----------
C000001      1893-08-07  GA
R000584      2009-01-06  ID
W000215      1975-01-14  CA
...          ...         ...

Diesen Code können wir dann in unseren Selbstbehalt-Code einfügen, um den Selbstbehalt durch first_state zu ermitteln:

SELECT first_state, period
,first_value(cohort_retained) over (partition by first_state 
                                    order by period) as cohort_size
,cohort_retained
,cohort_retained / 
 first_value(cohort_retained) over (partition by first_state 
                                    order by period) as pct_retained
FROM
(
    SELECT a.first_state
    ,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT distinct id_bioguide
        ,min(term_start) over (partition by id_bioguide) as first_term
        ,first_value(state) over (partition by id_bioguide order by term_start) 
         as first_state
        FROM legislators_terms 
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31
    GROUP BY 1,2
) aa
;

first_state  period  cohort_size  cohort_retained  pct_retained
-----------  ------  -----------  ---------------  ------------
AK           0.0     19           19               1.0000
AK           1.0     19           19               1.0000
AK           2.0     19           15               0.7895
...          ...     ...          ...              ...

Die Beibehaltungskurven für die fünf Bundesstaaten mit der höchsten Gesamtzahl an Abgeordneten sind in Abbildung 4-6 grafisch dargestellt. Die Abgeordneten in Illinois und Massachusetts haben die höchste Verweildauer, während die New Yorker die niedrigste haben. Es wäre ein interessanter Nebeneffekt dieser Analyse, die Gründe dafür zu ermitteln.

Abbildung 4-6. Beibehaltung der Abgeordneten nach Erststaat: Die fünf wichtigsten Staaten nach Gesamtzahl der Abgeordneten

Die Definition von Kohorten aus den Zeitreihen ist relativ einfach, indem man ein min Datum für jede Organisation verwendet und dieses Datum dann je nach Analyse in einen Monat, ein Jahr oder ein Jahrhundert umwandelt. Der Wechsel zwischen Monat und Jahr oder anderen Granularitätsebenen ist ebenfalls einfach und ermöglicht es, mehrere Optionen zu testen, um eine für die Organisation sinnvolle Gruppierung zu finden. Andere Attribute können mit der Funktion first_value window für die Kohortenbildung verwendet werden. Als Nächstes werden wir uns den Fällen zuwenden, in denen das Kohortenattribut aus einer anderen Tabelle als der der Zeitreihe stammt.

Definieren der Kohorte aus einer separaten Tabelle

Oft befinden sich die Merkmale, die eine Kohorte definieren, in einer anderen Tabelle als die, die die Zeitreihen enthält. In einer Datenbank kann es zum Beispiel eine Kundentabelle mit Informationen wie der Erwerbsquelle oder dem Registrierungsdatum geben, anhand derer die Kunden einer Kohorte zugeordnet werden können. Das Hinzufügen von Attributen aus anderen Tabellen oder sogar Unterabfragen ist relativ einfach und kann in der Bindungsanalyse und verwandten Analysen, die später im Kapitel besprochen werden, durchgeführt werden.

In diesem Beispiel wollen wir untersuchen, ob das Geschlecht der Abgeordneten einen Einfluss auf ihre Beibehaltung hat. Die Tabelle legislators hat ein Feld gender, in dem F für weiblich und M für männlich steht, das wir für die Kohorte der Abgeordneten verwenden können. Dazu JOINen wir die Tabelle legislators mit dem Alias d und fügen gender zur Berechnung von cohort_retained hinzu, anstelle von Jahr oder Jahrhundert:

SELECT d.gender
,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
,count(distinct a.id_bioguide) as cohort_retained
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms 
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
and c.month_name = 'December' and c.day_of_month = 31
JOIN legislators d on a.id_bioguide = d.id_bioguide
GROUP BY 1,2
;

gender  period  cohort_retained
------  ------  ---------------
F       0.0     366
M       0.0     12152
F       1.0     349
M       1.0     11979
...     ...     ...

Es ist sofort klar, dass viel mehr Männer als Frauen eine Legislaturperiode absolviert haben. Wir können nun die percent_retained berechnen, um den Verbleib der beiden Gruppen zu vergleichen:

SELECT gender, period
,first_value(cohort_retained) over (partition by gender 
                                    order by period) as cohort_size
,cohort_retained
,cohort_retained/ 
 first_value(cohort_retained) over (partition by gender 
                                    order by period) as pct_retained
FROM
(
    SELECT d.gender
    ,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms 
        GROUP BY 1
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31
    JOIN legislators d on a.id_bioguide = d.id_bioguide
    GROUP BY 1,2
) aa
;

gender  period  cohort_size  cohort_retained  pct_retained
------  ------  -----------  ---------------  ------------
F       0.0     366          366              1.0000
M       0.0     12152        12152            1.0000
F       1.0     366          349              0.9536
M       1.0     12152        11979            0.9858
...     ...     ...          ...              ...

Aus den in Abbildung 4-7 dargestellten Ergebnissen geht hervor, dass die Verweildauer der weiblichen Abgeordneten in den Zeiträumen 2 bis 29 höher ist als die ihrer männlichen Kollegen. Die erste weibliche Abgeordnete trat ihr Amt erst 1917 an, als Jeannette Rankin als republikanische Abgeordnete aus Montana in das Repräsentantenhaus einzog. Wie wir bereits gesehen haben, ist die Zahl der Abgeordneten in den letzten Jahrhunderten gestiegen.

Abbildung 4-7. Beibehaltung des Gesetzgebers nach Geschlecht

Um einen gerechteren Vergleich anzustellen, könnten wir die in die Analyse einbezogenen Abgeordneten auf diejenigen beschränken, deren first_term begonnen hat, seit es Frauen im Kongress gibt. Dies können wir tun, indem wir einen WHERE-Filter zur Unterabfrage aa hinzufügen. Auch hier werden die Ergebnisse auf diejenigen beschränkt, die vor dem Jahr 2000 angefangen haben, um sicherzustellen, dass die Kohorten mindestens 20 Jahre im Amt bleiben konnten:

SELECT gender, period
,first_value(cohort_retained) over (partition by gender 
                                    order by period) as cohort_size
,cohort_retained
,cohort_retained / 
 first_value(cohort_retained) over (partition by gender 
                                    order by period) as pct_retained
FROM
(
    SELECT d.gender
    ,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms 
        GROUP BY 1
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31
    JOIN legislators d on a.id_bioguide = d.id_bioguide
    WHERE a.first_term between '1917-01-01' and '1999-12-31'
    GROUP BY 1,2
) aa
;

gender  period  cohort_size  cohort_retained  pct_retained
------  ------  -----------  ---------------  ------------
F       0.0     200          200              1.0000
M       0.0     3833         3833             1.0000
F       1.0     200          187              0.9350
M       1.0     3833         3769             0.9833
...     ...     ...          ...              ...

Die Zahl der männlichen Abgeordneten ist immer noch höher als die der weiblichen, aber nicht mehr so hoch. Der Verbleib in den Kohorten ist in Abbildung 4-8 grafisch dargestellt. Bei den überarbeiteten Kohorten haben die männlichen Abgeordneten bis zum Jahr 7 eine höhere Verweildauer, aber ab dem Jahr 12 haben die weiblichen Abgeordneten eine höhere Verweildauer. Der Unterschied zwischen den beiden geschlechtsspezifischen Kohortenanalysen unterstreicht, wie wichtig es ist, geeignete Kohorten zu bilden und sicherzustellen, dass sie vergleichbar viel Zeit haben, um anwesend zu sein oder andere interessante Aktionen durchzuführen. Um diese Analyse weiter zu verbessern, könnten wir die Kohorten sowohl nach dem Anfangsjahr bzw. -jahrzehnt als auch nach dem Geschlecht unterscheiden, um zusätzliche Veränderungen bei der Verweildauer im 20. und im 21.

Abbildung 4-8. Verbleib der Abgeordneten in der Legislative nach Geschlecht: Kohorten von 1917 bis 1999

Kohorten können auf verschiedene Arten definiert werden, aus der Zeitreihe und aus anderen Tabellen. Mit dem von uns entwickelten Framework lassen sich Unterabfragen, Ansichten oder andere abgeleitete Tabellen einfügen, so dass eine ganze Reihe von Berechnungen die Grundlage für eine Kohorte bilden können. Es können mehrere Kriterien, wie z. B. das Anfangsjahr und das Geschlecht, verwendet werden. Eine Vorsicht bei der Einteilung von Populationen in Kohorten auf der Grundlage mehrerer Kriterien besteht darin, dass dies zu spärlichen Kohorten führen kann, bei denen einige der definierten Gruppen zu klein sind und nicht für alle Zeiträume im Datensatz vertreten sind. Im nächsten Abschnitt werden Methoden zur Überwindung dieser Herausforderung erörtert.

Umgang mit spärlichen Kohorten

Im idealen Datensatz hat jede Kohorte eine Aktion oder einen Datensatz in der Zeitreihe für jeden Zeitraum von Interesse. Wir haben bereits gesehen, dass "fehlende" Daten aufgrund von Abonnements oder Laufzeiten, die sich über mehrere Zeiträume erstrecken, auftreten können, und wir haben uns angeschaut, wie man diese mit Hilfe einer Datumsdimension korrigiert, um Zwischendatumswerte abzuleiten. Ein weiteres Problem kann auftreten, wenn die Kohorte aufgrund von Gruppierungskriterien zu klein wird und deshalb nur sporadisch in den Daten vertreten ist. Es kann sein, dass eine Kohorte aus der Ergebnismenge verschwindet, obwohl wir es lieber hätten, wenn sie mit einem Retentionswert von Null erscheinen würde. Dieses Problem wird als " sparse cohorts" bezeichnet und kann durch den sorgfältigen Einsatz von LEFT JOINsumgangen werden.

Um dies zu demonstrieren, wollen wir versuchen, die weiblichen Abgeordneten nach dem ersten Bundesstaat zu kohorten, den sie vertreten haben, um zu sehen, ob es Unterschiede bei der Beibehaltung gibt. Wir haben bereits gesehen, dass es relativ wenige weibliche Abgeordnete gab. Eine weitere Kohortenbildung nach Bundesstaaten wird höchstwahrscheinlich zu einigen spärlichen Kohorten führen, in denen es nur sehr wenige Mitglieder gibt. Bevor wir den Code anpassen, fügen wir first_state (berechnet im Abschnitt über die Ableitung von Kohorten aus den Zeitreihen) zu unserem vorherigen Geschlechterbeispiel hinzu und sehen uns die Ergebnisse an:

SELECT first_state, gender, period
,first_value(cohort_retained) over (partition by first_state, gender 
                                    order by period) as cohort_size
,cohort_retained
,cohort_retained / 
 first_value(cohort_retained) over (partition by first_state, gender 
                                    order by period) as pct_retained
FROM
(
    SELECT a.first_state, d.gender
    ,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT distinct id_bioguide
        ,min(term_start) over (partition by id_bioguide) as first_term
        ,first_value(state) over (partition by id_bioguide 
                                  order by term_start) as first_state
        FROM legislators_terms 
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31
    JOIN legislators d on a.id_bioguide = d.id_bioguide
    WHERE a.first_term between '1917-01-01' and '1999-12-31'
    GROUP BY 1,2,3
) aa
;


first_state  gender  period  cohort_size  cohort_retained pct_retained
-----------  ------  ------  -----------  --------------- ------------
AZ           F       0.0     2            2               1.0000
AZ           M       0.0     26           26              1.0000
AZ           F       1.0     2            2               1.0000
...          ...     ...     ...          ...             ...

Die grafische Darstellung der Ergebnisse für die ersten 20 Perioden, wie in Abbildung 4-9, zeigt die spärlichen Kohorten. In Alaska gab es keine weiblichen Abgeordneten, und in Arizona verschwindet die Kurve der weiblichen Abgeordneten nach Jahr 3. Nur Kalifornien, ein großer Staat mit vielen Abgeordneten, hat vollständige Verbleibskurven für beide Geschlechter. Dieses Muster wiederholt sich für andere kleine und große Staaten.

Abbildung 4-9. Beibehaltung von Abgeordneten nach Geschlecht und erstem Staat

Schauen wir uns nun an, wie wir einen Datensatz für jeden Zeitraum sicherstellen können, damit die Abfrage Nullwerte für die Aufbewahrung zurückgibt. Der erste Schritt ist die Abfrage aller Kombinationen von periods und Kohortenattributen, in diesem Fall first_state und gender, mit dem Startwert cohort_size für jede Kombination. Dies kann durch JOINing derUnterabfrage aa, die die Kohorte berechnet, mit einer Unterabfrage generate_series, die alle Ganzzahlen von 0 bis 20 zurückgibt, mit den Kriterien on 1 = 1 erfolgen. Dies ist eine praktische Methode, um einen Cartesian JOIN zu erzwingen, wenn die beiden Unterabfragen keine gemeinsamen Felder haben:

SELECT aa.gender, aa.first_state, cc.period, aa.cohort_size
FROM
(
    SELECT b.gender, a.first_state 
    ,count(distinct a.id_bioguide) as cohort_size
    FROM 
    (
        SELECT distinct id_bioguide
        ,min(term_start) over (partition by id_bioguide) as first_term
        ,first_value(state) over (partition by id_bioguide 
                                  order by term_start) as first_state
        FROM legislators_terms 
    ) a
    JOIN legislators b on a.id_bioguide = b.id_bioguide
    WHERE a.first_term between '1917-01-01' and '1999-12-31' 
    GROUP BY 1,2
) aa
JOIN
(
    SELECT generate_series as period 
    FROM generate_series(0,20,1)
) cc on 1 = 1
;

gender  state  period  cohort
------  -----  ------  ------
F       AL     0       3
F       AL     1       3
F       AL     2       3
...    ...     ...     ...

Der nächste Schritt ist die Verknüpfung mit den tatsächlichen Amtszeiten, wobei eine LINKS-Verknüpfung vorgenommen wird, um sicherzustellen, dass alle Zeiträume im Endergebnis enthalten sind:

SELECT aaa.gender, aaa.first_state, aaa.period, aaa.cohort_size
,coalesce(ddd.cohort_retained,0) as cohort_retained
,coalesce(ddd.cohort_retained,0) / aaa.cohort_size as pct_retained
FROM
(
    SELECT aa.gender, aa.first_state, cc.period, aa.cohort_size
    FROM
    (
        SELECT b.gender, a.first_state
        ,count(distinct a.id_bioguide) as cohort_size
        FROM 
        (
            SELECT distinct id_bioguide
            ,min(term_start) over (partition by id_bioguide) 
             as first_term
            ,first_value(state) over (partition by id_bioguide 
                                      order by term_start) 
                                      as first_state
            FROM legislators_terms 
        ) a
        JOIN legislators b on a.id_bioguide = b.id_bioguide 
        WHERE a.first_term between '1917-01-01' and '1999-12-31' 
        GROUP BY 1,2
    ) aa
    JOIN
    (
        SELECT generate_series as period 
        FROM generate_series(0,20,1)
    ) cc on 1 = 1
) aaa
LEFT JOIN
(
    SELECT d.first_state, g.gender
    ,coalesce(date_part('year',age(f.date,d.first_term)),0) as period
    ,count(distinct d.id_bioguide) as cohort_retained
    FROM
    (
        SELECT distinct id_bioguide
        ,min(term_start) over (partition by id_bioguide) as first_term
        ,first_value(state) over (partition by id_bioguide 
                                  order by term_start) as first_state
        FROM legislators_terms 
    ) d
    JOIN legislators_terms e on d.id_bioguide = e.id_bioguide 
    LEFT JOIN date_dim f on f.date between e.term_start and e.term_end 
     and f.month_name = 'December' and f.day_of_month = 31
    JOIN legislators g on d.id_bioguide = g.id_bioguide
    WHERE d.first_term between '1917-01-01' and '1999-12-31'
    GROUP BY 1,2,3
) ddd on aaa.gender = ddd.gender and aaa.first_state = ddd.first_state 
and aaa.period = ddd.period
;

gender  first_state  period  cohort_size  cohort_retained pct_retained
------  -----------  ------  -----------  --------------- ------------
F       AL           0       3            3               1.0000
F       AL           1       3            1               0.3333
F       AL           2       3            0               0.0000
...    ...           ...     ...          ...             ...

Dann können wir die Ergebnisse drehen und bestätigen, dass für jede Kohorte und jeden Zeitraum ein Wert existiert:

gender  first_state  yr0    yr2     yr4     yr6     yr8      yr10
------  -----------  -----  ------  ------  ------  ------  ------
F       AL           1.000  0.0000  0.0000  0.0000  0.0000  0.0000
F       AR           1.000  0.8000  0.2000  0.4000  0.4000  0.4000
F       CA           1.000  0.9200  0.8000  0.6400  0.6800  0.6800
...     ...          ...    ...     ...     ...     ...     ...

Beachte, dass der SQL-Code an dieser Stelle ziemlich lang geworden ist. Eine der schwierigsten Aufgaben beim Schreiben von SQL für die Kohortenanalyse ist es, die gesamte Logik und den Code übersichtlich zu halten - ein Thema, auf das ich in Kapitel 8 näher eingehen werde. Wenn ich den Code für die Verbleibsanalyse erstelle, finde ich es hilfreich, Schritt für Schritt vorzugehen und die Ergebnisse unterwegs zu überprüfen. Ich überprüfe auch stichprobenartig einzelne Kohorten, um sicherzustellen, dass das Endergebnis korrekt ist.

Kohorten können auf viele Arten definiert werden. Bisher haben wir alle unsere Kohorten auf das erste Datum normalisiert, an dem sie in den Zeitreihendaten auftauchen. Das ist jedoch nicht die einzige Option, denn interessante Analysen können auch ab der Mitte der Lebensspanne eines Unternehmens durchgeführt werden. Bevor wir unsere Arbeit an der Verbleibsanalyse abschließen, wollen wir uns diese zusätzliche Möglichkeit zur Definition von Kohorten ansehen.

Definieren von Kohorten aus anderen Daten als dem ersten Datum

Normalerweise werden zeitbasierte Kohorten ab dem ersten Auftreten des Unternehmens in der Zeitreihe oder ab einem anderen frühesten Datum, wie z. B. dem Registrierungsdatum, definiert. Eine Kohortenbildung zu einem anderen Datum kann jedoch nützlich und aufschlussreich sein. Wir könnten zum Beispiel die Kundenbindung aller Kunden, die einen Dienst ab einem bestimmten Datum nutzen, untersuchen. Diese Art der Analyse kann genutzt werden, um herauszufinden, ob sich Produkt- oder Marketingänderungen langfristig auf bestehende Kunden ausgewirkt haben.

Wenn wir ein anderes Datum als das erste Datum verwenden, müssen wir die Kriterien für die Aufnahme in jede Kohorte genau festlegen. Eine Möglichkeit besteht darin, die Personen auszuwählen, die an einem bestimmten Kalenderdatum anwesend sind. Das ist relativ einfach in SQL-Code umzusetzen, kann aber problematisch sein, wenn ein großer Teil der regelmäßigen Nutzer nicht jeden Tag erscheint, so dass die Zurückhaltung je nach dem gewählten Tag variiert. Eine Möglichkeit, dies zu korrigieren, besteht darin, den Datenerhalt für mehrere Starttermine zu berechnen und dann den Durchschnitt der Ergebnisse zu ermitteln.

Eine andere Möglichkeit ist die Verwendung eines Zeitfensters, z. B. einer Woche oder eines Monats. Jedes Unternehmen, das innerhalb dieses Zeitfensters im Datensatz auftaucht, wird in die Kohorte aufgenommen. Dieser Ansatz ist zwar oft repräsentativer für das Geschäft oder den Prozess, aber der Kompromiss besteht darin, dass der SQL-Code komplexer wird und die Abfragezeit aufgrund intensiverer Datenbankberechnungen langsamer sein kann. Es ist eine Kunst, das richtige Gleichgewicht zwischen Abfrageleistung und Genauigkeit der Ergebnisse zu finden.

Schauen wir uns an, wie eine solche Midstream-Analyse mit dem Datensatz der Gesetzgeber berechnet werden kann, indem wir den Verbleib der Gesetzgeber betrachten, die im Jahr 2000 im Amt waren. Wir beginnen mit dem term_type, der die Werte "sen" für Senatoren und "rep" für Abgeordnete hat. Die Definition umfasst alle Abgeordneten, die im Jahr 2000 im Amt waren: Diejenigen, die ihr Amt vor dem Jahr 2000 angetreten haben und deren Amtszeit während oder nach dem Jahr 2000 endete, sind ebenso qualifiziert wie diejenigen, die ihre Amtszeit im Jahr 2000 begonnen haben. Wir können ein beliebiges Datum im Jahr 2000 als first_term festlegen, da wir später überprüfen werden, ob sie zu einem bestimmten Zeitpunkt im Jahr 2000 im Amt waren. Die min_start der Amtszeiten, die in dieses Fenster fallen, wird ebenfalls berechnet und in einem späteren Schritt verwendet:

SELECT distinct id_bioguide, term_type, date('2000-01-01') as first_term
,min(term_start) as min_start
FROM legislators_terms 
WHERE term_start <= '2000-12-31' and term_end >= '2000-01-01'
GROUP BY 1,2,3
;

id_bioguide  term_type  first_term  min_start
-----------  ---------  ----------  ---------
C000858      sen        2000-01-01  1997-01-07
G000333      sen        2000-01-01  1995-01-04
M000350      rep        2000-01-01  1999-01-06
...          ...        ...         ...

Wir können dies mit zwei Anpassungen in unseren Code für den Selbstbehalt übernehmen. Erstens wird ein zusätzliches JOIN-Kriterium zwischen der Unterabfrage a und der Tabelle legislators_terms hinzugefügt, damit nur Begriffe zurückgegeben werden, die am oder nach dem Datum min_start begonnen haben. Zweitens wird ein zusätzlicher Filter zu date_dim hinzugefügt, damit nur dates im Jahr 2000 oder später zurückgegeben werden:

SELECT term_type, period
,first_value(cohort_retained) over (partition by term_type order by period) 
 as cohort_size
,cohort_retained
,cohort_retained / 
 first_value(cohort_retained) over (partition by term_type order by period) 
 as pct_retained
FROM
(
    SELECT a.term_type
    ,coalesce(date_part('year',age(c.date,a.first_term)),0) as period
    ,count(distinct a.id_bioguide) as cohort_retained
    FROM
    (
        SELECT distinct id_bioguide, term_type
        ,date('2000-01-01') as first_term
        ,min(term_start) as min_start
        FROM legislators_terms 
        WHERE term_start <= '2000-12-31' and term_end >= '2000-01-01'
        GROUP BY 1,2,3
    ) a
    JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    and b.term_start >= a.min_start
    LEFT JOIN date_dim c on c.date between b.term_start and b.term_end 
    and c.month_name = 'December' and c.day_of_month = 31 
    and c.year >= 2000
    GROUP BY 1,2
) aa
;

term_type  period  cohort_size  cohort_retained  pct_retained
---------  ------  -----------  ---------------  ------------
rep        0.0     440          440              1.0000
sen        0.0     101          101              1.0000
rep        1.0     440          392              0.8909
sen        1.0     101          89               0.8812
...        ...     ...          ...              ...

Abbildung 4-10 zeigt, dass trotz der längeren Amtszeit der Senatoren die Verweildauer in den beiden Kohorten ähnlich war und sich für die Senatoren nach 10 Jahren sogar verschlechtert hat. Eine weitere Analyse, bei der die verschiedenen Jahre, in denen sie zum ersten Mal gewählt wurden, oder andere Kohortenmerkmale verglichen werden, könnte interessante Erkenntnisse liefern.

Abbildung 4-10. Beibehaltung der Amtszeit nach Art der Amtszeit für die im Jahr 2000 amtierenden Abgeordneten

Ein häufiger Anwendungsfall für die Kohortenbildung anhand eines anderen Werts als dem Startwert ist die Analyse der Kundenbindung, nachdem ein Unternehmen einen Schwellenwert erreicht hat, z. B. eine bestimmte Anzahl von Käufen oder einen bestimmten Betrag. Wie bei jeder Kohorte ist es wichtig, sorgfältig zu definieren, was ein Unternehmen für eine Kohorte qualifiziert und welches Datum als Startdatum verwendet wird.

Die Kohortenbindung ist eine leistungsstarke Methode, um das Verhalten von Entitäten in einem Zeitreihendatensatz zu verstehen. Wir haben uns angeschaut, wie man die Retention mit SQL berechnet und wie man die Kohorte auf der Grundlage der Zeitreihe selbst oder anderer Tabellen und von Punkten in der Mitte der Lebensspanne der Entitäten bildet. Wir haben uns auch angesehen, wie man Funktionen und JOINsverwendet, um Daten innerhalb von Zeitreihen anzupassen und spärliche Kohorten auszugleichen. Es gibt mehrere Arten von Analysen, die mit der Kohortenbindung zusammenhängen: Analyse, Überlebensrate, Rückkehr und kumulative Berechnungen, die alle auf dem SQL-Code aufbauen, den wir für die Bindung entwickelt haben. Zu ihnen kommen wir als Nächstes.

Querschnittsanalyse durch eine Kohortenbetrachtung

Bislang haben wir uns in diesem Kapitel mit der Analyse von Kohorten befasst. Wir haben das Verhalten von Kohorten im Laufe der Zeit mit Verbleibs-, Überlebens-, Rückkehr- und kumulativen Verhaltensanalysen verfolgt. Eine der Herausforderungen bei diesen Analysen besteht jedoch darin, dass Veränderungen innerhalb von Kohorten zwar leicht zu erkennen sind, es aber schwierig sein kann, Veränderungen in der Gesamtzusammensetzung eines Kunden- oder Nutzerstamms zu erkennen.

Es kann auch zuMixverschiebungen kommen, d. h. zu Veränderungen in der Zusammensetzung der Kunden- oder Nutzerbasis im Laufe der Zeit, so dass sich spätere Kohorten von früheren unterscheiden. Diese Verschiebungen können auf die internationale Expansion, den Wechsel zwischen organischen und bezahlten Akquisitionsstrategien oder den Wechsel von einem Nischenpublikum zu einem breiteren Massenmarkt zurückzuführen sein. Die Bildung zusätzlicher Kohorten oder Segmente entlang einer dieser vermuteten Linien kann dabei helfen, festzustellen, ob eine Mixverschiebung stattfindet.

Die Kohortenanalyse kann mit der Querschnittsanalyse verglichen werden, bei der Einzelpersonen oder Gruppen zu einem einzigen Zeitpunkt verglichen werden. In Querschnittsstudien können zum Beispiel Bildungsjahre mit dem aktuellen Einkommen in Beziehung gesetzt werden. Positiv ist, dass die Erhebung von Datensätzen für Querschnittsanalysen oft einfacher ist, da keine Zeitreihen erforderlich sind. Querschnittsanalysen können aufschlussreich sein und Hypothesen für weitere Untersuchungen aufstellen. Auf der negativen Seite gibt es in der Regel eine Form von Selektionsverzerrung, die Survivorship Bias, die zu falschen Schlussfolgerungen führen kann.

Die Kohortenanalyse ist eine Möglichkeit, die Verzerrung durch Überleben zu überwinden, indem alle Mitglieder einer Ausgangskohorte in die Analyse einbezogen werden. Wir können eine Reihe von Querschnitten aus einer Kohortenanalyse nehmen, um zu verstehen, wie sich der Mix der Entitäten im Laufe der Zeit verändert haben könnte. Zu einem bestimmten Zeitpunkt sind Nutzer aus einer Vielzahl von Kohorten anwesend. Mit der Querschnittsanalyse können wir sie wie Sedimentschichten untersuchen, um neue Erkenntnisse zu gewinnen. Im nächsten Beispiel erstellen wir für jedes Jahr des Datensatzes eine Zeitreihe mit dem Anteil der Gesetzgeber aus jeder Kohorte.

Der erste Schritt besteht darin, die Anzahl der Abgeordneten zu ermitteln, die jedes Jahr im Amt sind, indem wir die Tabelle legislators mit der Tabelle date_dimVERBINDEN, wobei date aus der Tabelle date_dim zwischen dem Anfangs- und dem Enddatum der jeweiligen Amtszeit liegt. Hier verwenden wir den 31. Dezember für jedes Jahr, um die Anzahl der Abgeordneten zu ermitteln, die am Ende des Jahres im Amt sind:

SELECT b.date, count(distinct a.id_bioguide) as legislators
FROM legislators_terms a
JOIN date_dim b on b.date between a.term_start and a.term_end
and b.month_name = 'December' and b.day_of_month = 31
and b.year <= 2019
GROUP BY 1
;

date        legislators
----------  -----------
1789-12-31  89
1790-12-31  95
1791-12-31  99
...         ...

Als Nächstes fügen wir die Kriterien für die Kohortenbildung des Jahrhunderts hinzu, indem wir eineUnterabfrage mit dem berechneten first_term verknüpfen:

SELECT b.date
,date_part('century',first_term) as century
,count(distinct a.id_bioguide) as legislators
FROM legislators_terms a
JOIN date_dim b on b.date between a.term_start and a.term_end
 and b.month_name = 'December' and b.day_of_month = 31
 and b.year <= 2019
JOIN
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms
    GROUP BY 1
) c on a.id_bioguide = c.id_bioguide        
GROUP BY 1,2
;

date        century  legislators
----------  -------  -----------
1789-12-31  18       89
1790-12-31  18       95
1791-12-31  18       99
...         ...      ...

Schließlich berechnen wir den Anteil der Jahrhundertkohorte an der Gesamtzahl der legislators in jedem Jahr. Dies kann auf verschiedene Weise geschehen, je nachdem, welche Form der Ausgabe gewünscht ist. Die erste Möglichkeit ist, für jede Kombination aus date und century eine Zeile zu behalten und eine sum Fensterfunktion im Nenner der Prozentberechnung zu verwenden:

SELECT date
,century
,legislators
,sum(legislators) over (partition by date) as cohort
,legislators / sum(legislators) over (partition by date) 
 as pct_century
FROM
(
    SELECT b.date
    ,date_part('century',first_term) as century
    ,count(distinct a.id_bioguide) as legislators
    FROM legislators_terms a
    JOIN date_dim b on b.date between a.term_start and a.term_end
    and b.month_name = 'December' and b.day_of_month = 31
    and b.year <= 2019
    JOIN
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        GROUP BY 1
    ) c on a.id_bioguide = c.id_bioguide        
    GROUP BY 1,2
) a
;

date        century  legislators  cohort  pct_century
----------  -------  -----------  ------  -----------
2018-12-31  20       122          539     0.2263
2018-12-31  21       417          539     0.7737
2019-12-31  20       97           537     0.1806
2019-12-31  21       440          537     0.8194
...         ...      ...          ...     ...

Der zweite Ansatz ergibt eine Zeile pro Jahr, mit einer Spalte für jedes Jahrhundert, ein Tabellenformat, das einfacher nach Trends zu durchsuchen ist:

SELECT date
,coalesce(sum(case when century = 18 then legislators end)
          / sum(legislators),0) as pct_18
,coalesce(sum(case when century = 19 then legislators end)
          / sum(legislators),0) as pct_19
,coalesce(sum(case when century = 20 then legislators end)
          / sum(legislators),0) as pct_20
,coalesce(sum(case when century = 21 then legislators end)
          / sum(legislators),0) as pct_21
FROM
(
    SELECT b.date
    ,date_part('century',first_term) as century
    ,count(distinct a.id_bioguide) as legislators
    FROM legislators_terms a
    JOIN date_dim b on b.date between a.term_start and a.term_end
     and b.month_name = 'December' and b.day_of_month = 31
     and b.year <= 2019
    JOIN
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        GROUP BY 1
    ) c on a.id_bioguide = c.id_bioguide        
    GROUP BY 1,2
) aa
GROUP BY 1
;


date        pct_18  pct_19  pct_20  pct_21
----------  ------  ------  ------  ------
2017-12-31  0       0       0.2305  0.7695
2018-12-31  0       0       0.2263  0.7737
2019-12-31  0       0       0.1806  0.8193
...         ...     ...     ...     ...

Wir können die Ergebnisse grafisch darstellen, wie in Abbildung 4-13, um zu sehen, wie neuere Kohorten von Gesetzgebern ältere Kohorten allmählich überholen, bis sie selbst durch neue Kohorten ersetzt werden.

Abbildung 4-13. Prozentualer Anteil der Abgeordneten pro Jahr, nach Jahrhundert der Erstwahl

Anstatt nach first_term zu kohortieren, können wir stattdessen nach der Dauer der Kundenbeziehung kohortieren. Es kann aufschlussreich sein, den Anteil der Kunden zu ermitteln, die relativ neu sind, eine mittlere Betriebszugehörigkeit haben oder zu verschiedenen Zeitpunkten langjährige Kunden sind. Werfen wir einen Blick darauf, wie sich die Amtszeit der Abgeordneten im Kongress im Laufe der Zeit verändert hat.

Der erste Schritt besteht darin, für jedes Jahr die kumulierte Anzahl der Amtsjahre der einzelnen Abgeordneten zu berechnen. Da es zwischen den Amtszeiten Lücken geben kann, wenn Abgeordnete abgewählt werden oder aus anderen Gründen aus dem Amt scheiden, suchen wir in der Unterabfrage zunächst nach jedem Jahr, in dem der Abgeordnete am Ende des Jahres im Amt war. Dann verwenden wir eine count Fensterfunktion, wobei das Fenster die Zeilen unbounded preceding oder alle früheren Zeilen für diesen Abgeordneten und current row abdeckt:

SELECT id_bioguide, date
,count(date) over (partition by id_bioguide 
                   order by date rows between 
                   unbounded preceding and current row
                   ) as cume_years
FROM
(
    SELECT distinct a.id_bioguide, b.date
    FROM legislators_terms a
    JOIN date_dim b on b.date between a.term_start and a.term_end
     and b.month_name = 'December' and b.day_of_month = 31
     and b.year <= 2019
) aa
;

id_bioguide  date        cume_years
-----------  ----------  ----------
A000001      1951-12-31  1
A000001      1952-12-31  2
A000002      1947-12-31  1
A000002      1948-12-31  2
A000002      1949-12-31  3
...          ...         ...

Als Nächstes: count die Anzahl der Gesetzgeber für jede Kombination von date und cume_years, um eine Verteilung zu erstellen:

SELECT date, cume_years
,count(distinct id_bioguide) as legislators
FROM
(
SELECT id_bioguide, date
,count(date) over (partition by id_bioguide 
                   order by date rows between 
                   unbounded preceding and current row
                   ) as cume_years
FROM
(
    SELECT distinct a.id_bioguide, b.date
    FROM legislators_terms a
    JOIN date_dim b on b.date between a.term_start and a.term_end
     and b.month_name = 'December' and b.day_of_month = 31
     and b.year <= 2019
    GROUP BY 1,2
    ) aa
) aaa
GROUP BY 1,2
;

date         cume_years  legislators
-----------  ----------  ----------
1789-12-31   1           89
1790-12-31   1           6
1790-12-31   2           89
1791-12-31   1           37
...          ...         ...

Bevor wir den Prozentsatz für jede Amtszeit pro Jahr berechnen und das Präsentationsformat anpassen, sollten wir eine Gruppierung der Amtszeiten in Betracht ziehen. Ein kurzer Blick auf unsere bisherigen Ergebnisse zeigt, dass in manchen Jahren fast 40 verschiedene Amtszeiten vertreten sind. Das wird wahrscheinlich schwierig zu visualisieren und zu interpretieren sein:

SELECT date, count(*) as tenures
FROM 
(
    SELECT date, cume_years
    ,count(distinct id_bioguide) as legislators
    FROM
    (
        SELECT id_bioguide, date
        ,count(date) over (partition by id_bioguide 
                           order by date rows between 
                           unbounded preceding and current row
                           ) as cume_years
        FROM
        (
            SELECT distinct a.id_bioguide, b.date
            FROM legislators_terms a
            JOIN date_dim b 
             on b.date between a.term_start and a.term_end
             and b.month_name = 'December' and b.day_of_month = 31
             and b.year <= 2019
            GROUP BY 1,2
        ) aa
    ) aaa
    GROUP BY 1,2
) aaaa
GROUP BY 1
;

date         tenures
-----------  -------
1998-12-31   39
1994-12-31   39
1996-12-31   38
...          ...

Deshalb sollten wir die Werte gruppieren. Es gibt nicht die eine richtige Art, Besitzstände zu gruppieren. Wenn es organisatorische Definitionen von Besitzgruppen gibt, solltest du sie verwenden. Ansonsten versuche ich normalerweise, sie in drei bis fünf etwa gleich große Gruppen aufzuteilen. Hier werden wir die Betriebszugehörigkeit in vier Gruppen einteilen, wobei cume_years kleiner oder gleich 4 Jahre, zwischen 5 und 10 Jahren, zwischen 11 und 20 Jahren und gleich oder größer als 21 Jahre ist:

SELECT date, tenure
,legislators / sum(legislators) over (partition by date) 
 as pct_legislators 
FROM
(
    SELECT date
    ,case when cume_years <= 4 then '1 to 4'
          when cume_years <= 10 then '5 to 10'
          when cume_years <= 20 then '11 to 20'
          else '21+' end as tenure
    ,count(distinct id_bioguide) as legislators
    FROM
    (
        SELECT id_bioguide, date
        ,count(date) over (partition by id_bioguide 
                           order by date rows between 
                           unbounded preceding and current row
                           ) as cume_years
        FROM
        (
            SELECT distinct a.id_bioguide, b.date
            FROM legislators_terms a
            JOIN date_dim b 
             on b.date between a.term_start and a.term_end
             and b.month_name = 'December' and b.day_of_month = 31
             and b.year <= 2019
            GROUP BY 1,2
        ) a
    ) aa
    GROUP BY 1,2
) aaa
;

date        tenure    pct_legislators
----------  -------   ---------------
2019-12-31  1 to 4    0.2998
2019-12-31  5 to 10   0.3203
2019-12-31  11 to 20  0.2011
2019-12-31  21+       0.1788
...         ...       ...

Die grafische Darstellung der Ergebnisse in Abbildung 4-14 zeigt, dass in den Anfangsjahren des Landes die meisten Abgeordneten nur eine sehr kurze Amtszeit hatten. In den letzten Jahren ist der Anteil der Abgeordneten, die 21 oder mehr Jahre im Amt sind, gestiegen. Interessant ist auch, dass der Anteil der Abgeordneten mit einer Amtszeit von 1 bis 4 Jahren in regelmäßigen Abständen ansteigt, was auf Veränderungen in der politischen Entwicklung zurückzuführen sein könnte.

Abbildung 4-14. Prozentsatz der Abgeordneten nach Anzahl der Jahre im Amt

Ein Querschnitt einer Bevölkerung zu einem beliebigen Zeitpunkt setzt sich aus Mitgliedern mehrerer Kohorten zusammen. Die Erstellung einer Zeitreihe aus diesen Querschnitten ist eine weitere interessante Möglichkeit, Trends zu analysieren. Kombiniert man dies mit Erkenntnissen aus der Mitarbeiterbindung, erhält man ein aussagekräftigeres Bild der Trends in jeder Organisation.

Fazit

Die Kohortenanalyse ist eine nützliche Methode, um zu untersuchen, wie sich Gruppen im Laufe der Zeit verändern, sei es unter dem Gesichtspunkt des Verbleibs, des wiederholten Verhaltens oder der kumulativen Aktionen. Die Kohortenanalyse ist retrospektiv, d.h. sie betrachtet Populationen anhand von intrinsischen oder aus dem Verhalten abgeleiteten Merkmalen rückblickend. Mit dieser Art von Analyse lassen sich interessante und hoffentlich nützliche Korrelationen finden. Doch wie das Sprichwort sagt, bedeutet Korrelation nicht gleich Kausalität. Um die tatsächliche Kausalität zu ermitteln, sind randomisierte Experimente der Goldstandard. Kapitel 7 befasst sich ausführlich mit der Analyse von Experimenten.

Bevor wir uns dem Experimentieren zuwenden, müssen wir jedoch noch ein paar andere Arten der Analyse behandeln. Als Nächstes befassen wir uns mit der Textanalyse: Komponenten der Textanalyse tauchen oft in anderen Analysen auf und sind an sich schon eine interessante Facette der Analyse.

Get SQL für die Datenanalyse now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial