Capítulo 4. Análisis de cohortes

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: translation-feedback@oreilly.com

En el Capítulo 3 tratamos el análisis de series temporales. Con esas técnicas en la mano, pasaremos ahora a un tipo de análisis relacionado con muchas aplicaciones empresariales y de otro tipo: el análisis de cohortes.

Recuerdo la primera vez que me encontré con un análisis de cohortes. Estaba trabajando en mi primer empleo de analista de datos, en una pequeña startup. Estaba revisando un análisis de compras en el que había trabajado con el director general, y me sugirió que dividiera la base de clientes por cohortes para ver si el comportamiento cambiaba con el tiempo. Supuse que se trataba de algo de la escuela de negocios y probablemente inútil, pero él era el director general, así que le seguí la corriente. Resultó que no era una tontería. Dividir las poblaciones en cohortes y seguirlas a lo largo del tiempo es una forma poderosa de analizar tus datos y evitar diversos sesgos. Las cohortes pueden proporcionar pistas sobre cómo difieren las subpoblaciones entre sí y cómo cambian con el tiempo.

En este capítulo, primero veremos qué son las cohortes y los componentes básicos de ciertos tipos de análisis de cohortes. Tras una introducción al conjunto de datos de legisladores utilizado para los ejemplos, aprenderemos a construir un análisis de retención y a afrontar diversos retos, como la definición de la cohorte y el manejo de datos dispersos. A continuación, trataremos los cálculos de supervivencia, retorno y acumulativos, todos ellos similares al análisis de retención en la forma en que está estructurado el código SQL. Por último, veremos cómo combinar el análisis de cohortes con el análisis transversal para comprender la composición de las poblaciones a lo largo del tiempo.

Cohortes: Un marco de análisis útil

Antes de entrar en el código, definiré qué son las cohortes, consideraré los tipos de preguntas que podemos responder con este tipo de análisis y describiré los componentes de cualquier análisis de cohortes.

Una cohorte es un grupo de individuos que comparten alguna característica de interés, descrita a continuación, en el momento en que empezamos a observarlos. Los miembros de la cohorte suelen ser personas, pero pueden ser cualquier tipo de entidad que queramos estudiar: empresas, productos o fenómenos del mundo físico. Los individuos de una cohorte pueden ser conscientes de su pertenencia, del mismo modo que los niños de una clase de primer grado son conscientes de que forman parte de un grupo de compañeros de primer grado, o los participantes en un ensayo farmacológico son conscientes de que forman parte de un grupo que recibe un tratamiento. Otras veces, las entidades se agrupan en cohortes de forma virtual, como cuando una empresa de software agrupa a todos los clientes adquiridos en un determinado año para estudiar cuánto tiempo siguen siendo clientes. Siempre es importante considerar las implicaciones éticas de agrupar entidades en cohortes sin que sean conscientes de ello, si se les va a aplicar un tratamiento diferente.

El análisis de cohortes es una forma útil de comparar grupos de entidades a lo largo del tiempo. Muchos comportamientos importantes tardan semanas, meses o años en producirse o evolucionar, y el análisis de cohortes es una forma de comprender estos cambios. El análisis de cohortes proporciona un marco para detectar correlaciones entre las características de la cohorte y estas tendencias a largo plazo, lo que puede conducir a hipótesis sobre los factores causales. Por ejemplo, los clientes captados a través de una campaña de marketing pueden tener pautas de compra a largo plazo diferentes de las de los que fueron persuadidos por un amigo para que probaran los productos de una empresa. El análisis de cohortes puede utilizarse para monitorear nuevas cohortes de usuarios o clientes y evaluar cómo se comparan con cohortes anteriores. Este monitoreo puede proporcionar una señal de alerta temprana de que algo ha ido mal (o bien) para los nuevos clientes. El análisis de cohortes también se utiliza para extraer datos históricos. Las pruebas A/B, tratadas en el Capítulo 7, son el patrón oro para determinar la causalidad, pero no podemos retroceder en el tiempo y realizar todas las pruebas para cada pregunta sobre el pasado que nos interese. Por supuesto, debemos ser cautos a la hora de atribuir un significado causal al análisis de cohortes y, en su lugar, utilizar el análisis de cohortes como una forma de comprender a los clientes y generar hipótesis que puedan probarse rigurosamente en el futuro.

Los análisis de cohortes tienen tres componentes: la agrupación de la cohorte, una serie temporal de datos a lo largo de la cual se observa la cohorte, y una métrica agregada que mide una acción realizada por los miembros de la cohorte.

La agrupación por cohortes suele basarse en una fecha de inicio: la fecha de la primera compra o suscripción del cliente, la fecha en que un estudiante empezó la escuela, etc. Sin embargo, las cohortes también pueden formarse en torno a otras características innatas o cambiantes con el tiempo. Las cualidades innatas incluyen el año de nacimiento y el país de origen, o el año en que se fundó una empresa. Las características que pueden cambiar con el tiempo incluyen la ciudad de residencia y el estado civil. Cuando se utilizan éstas, hay que tener cuidado de cohortar sólo en el valor de la fecha de inicio, o de lo contrario las entidades pueden saltar entre grupos de cohortes.

¿Cohorte o segmento?

Estos dos términos se utilizan a menudo de forma similar, o incluso intercambiable, pero merece la pena establecer una distinción entre ellos en aras de la claridad. Una cohorte es un grupo de usuarios (u otras entidades) que tienen una fecha de inicio común y son seguidos a lo largo del tiempo. Un segmento es una agrupación de usuarios que comparten una característica o conjunto de características comunes en un momento dado, independientemente de su fecha de inicio. De forma similar a las cohortes, los segmentos pueden basarse en factores innatos, como la edad, o en características de comportamiento. Un segmento de usuarios que se registra en el mismo mes puede incluirse en una cohorte y seguirse a lo largo del tiempo. O se pueden explorar diferentes agrupaciones de usuarios con el análisis de cohortes para que puedas ver cuáles tienen las características más valiosas. Los análisis que cubriremos en este capítulo, como el de retención, pueden ayudar a poner datos concretos detrás de los segmentos de marketing.

El segundo componente de cualquier análisis de cohortes es la serie temporal. Se trata de una serie de compras, inicios de sesión, interacciones u otras acciones realizadas por los clientes o entidades que se van a cohortar. Es importante que la serie temporal abarque toda la vida de las entidades, o habrá sesgo de supervivencia en las primeras cohortes. El sesgo de supervivencia se produce cuando sólo los clientes que han permanecido están en el conjunto de datos; los clientes que han abandonado se excluyen porque ya no están, de modo que el resto de los clientes parecen de mayor calidad o ajuste en comparación con las cohortes más recientes (véase "Sesgo de supervivencia"). También es importante disponer de una serie temporal lo suficientemente larga para que las entidades completen la acción de interés. Por ejemplo, si los clientes tienden a comprar una vez al mes, se necesita una serie temporal de varios meses. Si, por el contrario, las compras sólo se producen una vez al año, sería preferible una serie temporal de varios años. Inevitablemente, los clientes adquiridos más recientemente no habrán tenido tanto tiempo para completar las acciones como los clientes que fueron adquiridos más en el pasado. Para normalizar, el análisis de cohortes suele medir el número de periodos transcurridos desde una fecha inicial, en lugar de meses naturales. De este modo, las cohortes pueden compararse en el periodo 1, el periodo 2, etc., para ver cómo evolucionan en el tiempo, independientemente del mes en que se produjo realmente la acción. Los intervalos pueden ser de días, semanas, meses o años.

La métrica agregada debe estar relacionada con las acciones que importan para la salud de la organización, como que los clientes sigan utilizando o comprando el producto. Los valores de la métrica se agregan a través de la cohorte, normalmente con sum, count, o average, aunque cualquier agregación relevante funciona. El resultado es una serie temporal que puede utilizarse para comprender los cambios de comportamiento a lo largo del tiempo.

En este capítulo, trataré cuatro tipos de análisis de cohortes: retención, supervivencia, comportamiento de retorno o repetición de compra y comportamiento acumulativo.

Retención: La retención se refiere a si el miembro de la cohorte tiene un registro en la serie temporal en una fecha concreta, expresada como un número de periodos desde la fecha de inicio. Esto es útil en cualquier tipo de organización en la que se esperen acciones repetidas, desde jugar a un juego online hasta utilizar un producto o renovar una suscripción, y ayuda a responder preguntas sobre lo pegajoso o atractivo que es un producto y cuántas entidades cabe esperar que aparezcan en fechas futuras.
Supervivencia: La Supervivencia se ocupa de cuántas entidades permanecieron en el conjunto de datos durante un determinado periodo de tiempo o más, independientemente del número o la frecuencia de las acciones hasta ese momento. La Supervivencia es útil para responder a preguntas sobre la proporción de la población que cabe esperar que permanezca, ya sea en un sentido positivo al no agitarse o fallecer, o en un sentido negativo al no graduarse o cumplir algún requisito.
Devolución: Comportamiento de devolución o repetición de compra se ocupa de si una acción se ha producido más de un umbral mínimo de veces -a menudo simplemente más de una vez- durante una ventana de tiempo fija. Este tipo de análisis es útil en situaciones en las que el comportamiento es intermitente e impredecible, como en el comercio minorista, donde caracteriza la proporción de compradores que repiten en cada cohorte dentro de una ventana temporal fija.
Acumulativo: Cálculos acumulativos se refieren al número total o a las cantidades medidas en una o más ventanas temporales fijas, independientemente de cuándo hayan ocurrido durante esa ventana. Los cálculos acumulativos se utilizan a menudo en los cálculos de valor de vida del cliente (LTV o CLTV).

Los cuatro tipos de análisis de cohortes nos permiten comparar subgrupos y comprender cómo difieren a lo largo del tiempo para tomar mejores decisiones sobre productos, marketing y finanzas. Los cálculos de los distintos tipos son similares, así que prepararemos el terreno con la retención, y luego mostraré cómo modificar el código de retención para calcular los otros tipos. Antes de sumergirnos en la construcción de nuestro análisis de cohortes, echemos un vistazo al conjunto de datos que utilizaremos para los ejemplos de este capítulo.

El conjunto de datos de los legisladores

Los ejemplos SQL de este capítulo utilizarán un conjunto de datos de los miembros pasados y presentes del Congreso de los Estados Unidos que se mantiene en un repositorio de GitHub. En US, el Congreso es responsable de redactar las leyes o legislación, por lo que sus miembros también son conocidos como legisladores. Como el conjunto de datos es un archivo JSON, he aplicado algunas transformaciones para producir un modelo de datos más adecuado para el análisis, y he publicado los datos en un formato adecuado para seguir los ejemplos en la carpeta de legisladores de GitHub del libro.

El repositorio de fuentes tiene un excelente diccionario de datos, por lo que no repetiré aquí todos los detalles. Sin embargo, daré algunos detalles que ayudarán a quienes no estén familiarizados con el gobierno de EEUU a seguir los análisis de este capítulo.

El Congreso tiene dos cámaras, el Senado ("sen" en el conjunto de datos) y la Cámara de Representantes ("rep"). Cada estado tiene dos senadores, y son elegidos para mandatos de seis años. Los representantes se asignan a los estados en función de la población; cada representante tiene un distrito al que representa en solitario. Los representantes son elegidos para mandatos de dos años. Los mandatos reales en cualquiera de las cámaras pueden ser más cortos en caso de que el legislador fallezca o sea elegido o nombrado para un cargo superior. Los legisladores acumulan poder e influencia a través de puestos de liderazgo cuanto más tiempo permanecen en el cargo, por lo que es habitual que se presenten a la reelección. Por último, un legislador puede pertenecer a un partido político, o ser un "independiente". En la era moderna, la gran mayoría de los legisladores son demócratas o republicanos, y la rivalidad entre ambos partidos es bien conocida. En ocasiones, los legisladores cambian de partido mientras ocupan sus cargos.

Para los análisis, utilizaremos dos tablas: legislators y legislators_terms. La tabla legislators contiene una lista de todas las personas incluidas en el conjunto de datos, con fecha de nacimiento, sexo y un conjunto de campos de identificación que pueden utilizarse para buscar a la persona en otros conjuntos de datos. La tabla legislators_terms contiene un registro por cada legislatura de cada legislador, con la fecha de inicio y fin, y otros atributos como la cámara y el partido. El campo id_bioguide se utiliza como identificador único de un legislador y aparece en cada tabla. La Figura 4-1 muestra una muestra de los datos de legislators. La Figura 4-2 muestra una muestra de los datos de legislators_terms.

Ahora que ya sabemos qué es el análisis de cohortes y el conjunto de datos que utilizaremos para los ejemplos, vamos a ver cómo escribir SQL para el análisis de retención. La pregunta clave que SQL nos ayudará a responder es: una vez que los representantes toman posesión de su cargo, ¿cuánto tiempo conservan su empleo?

Retención

Uno de los tipos más comunes de análisis de cohortes es el análisis de retención. Retener es conservar o continuar algo. Hay que practicar muchas habilidades para retenerlas. Las empresas suelen querer que sus clientes sigan comprando sus productos o utilizando sus servicios, ya que retener a los clientes es más rentable que adquirir otros nuevos. Los empresarios quieren conservar a sus empleados, porque contratar sustitutos es caro y lleva mucho tiempo. Los cargos electos buscan la reelección para seguir trabajando en las prioridades de sus electores.

La pregunta principal en el análisis de la retención es si el tamaño inicial de la cohorte -número de abonados o empleados, importe gastado u otra métrica clave- permanecerá constante, decaerá o aumentará con el tiempo. Cuando se produce un aumento o una disminución, la cantidad y la velocidad del cambio también son cuestiones interesantes. En la mayoría de los análisis de retención, el tamaño inicial tenderá a decaer con el tiempo, ya que una cohorte puede perder pero no puede ganar nuevos miembros una vez formada. Los ingresos son una excepción interesante, ya que una cohorte de clientes puede gastar más en los meses siguientes de lo que gastó en el primer mes colectivamente, aunque algunos de ellos se den de baja.

El análisis de retención utiliza el count de entidades o sum de dinero o acciones presentes en el conjunto de datos para cada periodo desde la fecha de inicio, y lo normaliza dividiendo este número por el count o sum de entidades, dinero o acciones en el primer periodo de tiempo. El resultado se expresa en porcentaje, y la retención en el periodo inicial es siempre del 100%. Con el tiempo, la retención basada en recuentos suele disminuir y nunca puede superar el 100%, mientras que la retención basada en dinero o acciones, aunque suele disminuir, puede aumentar y ser superior al 100% en un periodo de tiempo. El resultado del análisis de la retención suele mostrarse en forma de tabla o gráfico, lo que se denomina curva de retención. Veremos varios ejemplos de curvas de retención más adelante en este capítulo.

Los gráficos de las curvas de retención pueden utilizarse para comparar cohortes. La primera característica a la que hay que prestar atención es la forma de la curva en los primeros periodos, en los que suele haber una caída inicial pronunciada. Para muchas aplicaciones de consumo, es habitual perder la mitad de una cohorte en los primeros meses. Una cohorte con una curva más o menos pronunciada que otras puede indicar cambios en el producto o en la fuente de adquisición de clientes que merecen una investigación más profunda. Una segunda característica que hay que buscar es si la curva se aplana después de cierto número de periodos o si sigue descendiendo rápidamente hasta cero. Una curva que se aplana indica que hay un punto en el tiempo a partir del cual la mayoría de la cohorte que permanece lo hace indefinidamente. Una curva de retención que se inflexiona hacia arriba, a veces llamada curva de la sonrisa, puede producirse si los miembros de la cohorte regresan o se reactivan tras salir del conjunto de datos durante algún periodo. Por último, las curvas de retención que miden los ingresos por suscripción se monitorean para detectar signos de aumento de los ingresos por cliente a lo largo del tiempo, un signo de un negocio de software SaaS saludable.

Esta sección mostrará cómo crear un análisis de retención, añadir agrupaciones de cohortes a partir de la propia serie temporal y de otras tablas, y manejar los datos perdidos y escasos que pueden darse en los datos de series temporales. Con este marco en la mano, en la sección siguiente aprenderás a hacer modificaciones para crear los otros tipos de análisis de cohortes relacionados. Como resultado, esta sección sobre la retención será la más larga del capítulo, ya que construirás código y desarrollarás tu intuición sobre los cálculos.

SQL para una curva de retención básica

Para el análisis de la retención , al igual que para otros análisis de cohortes, necesitamos tres componentes: la definición de la cohorte, una serie temporal de acciones y una métrica agregada que mida algo relevante para la organización o el proceso. En nuestro caso, los miembros de la cohorte serán los legisladores, la serie temporal serán los mandatos en el cargo de cada legislador, y la métrica de interés será el count de los que siguen en el cargo cada periodo desde la fecha de inicio.

Empezaremos calculando la retención básica, antes de pasar a ejemplos que incluyan diversas agrupaciones de cohortes. El primer paso consiste en hallar la primera fecha de toma de posesión de cada legislador (first_term). Utilizaremos esta fecha para calcular el número de periodos de cada fecha posterior de la serie temporal. Para ello, toma el min del term_start y AGRÚPALO POR cada id_bioguide, el identificador único de un legislador:

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
...          ...

El siguiente paso es poner este código en una subconsulta y UNIRLO a la serie temporal. Se aplica la función age para calcular los intervalos entre cada term_start y el first_term de cada legislador. Aplicando las funciones date_part al resultado, con el año, se transforma en el número de periodos anuales. Como las elecciones se celebran cada dos o seis años, utilizaremos años como intervalo de tiempo para calcular los periods. Podríamos utilizar un intervalo más corto, pero en este conjunto de datos hay poca fluctuación diaria o semanal. El count de legisladores con registros para ese periodo es el número retenido:

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
...     ...

Consejo

En las bases de datos que admiten la función datediff, las construcciones date_part y age pueden sustituirse por esta función más sencilla:

datediff('year',first_term,term_start)

Algunas bases de datos, como Oracle, colocan la date_part en último lugar:

datediff(first_term,term_start,'year'

Ahora que tenemos los periodos y el número de legisladores retenidos en cada uno, el paso final es calcular el total cohort_size y rellenarlo en cada fila para poder dividir el cohort_retained por él. La función de ventana first_value devuelve el primer registro en la cláusula PARTITION BY, según el orden establecido en ORDER BY, una forma cómoda de obtener el tamaño de la cohorte en cada fila. En este caso, el cohort_size procede del primer registro de todo el conjunto de datos, por lo que se omite la cláusula PARTITION BY:

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

Para hallar el porcentaje retenido, divide el valor cohort_retained por este mismo cálculo:

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
...     ...          ...              ...

Ahora tenemos un cálculo de retención, y podemos ver que hay una gran caída entre el 100% de los legisladores retenidos en el periodo 0, o en su fecha de inicio, y el porcentaje con otro registro de legislatura que comienza un año después. Si graficamos los resultados, como en la Figura 4-3, veremos cómo la curva se aplana y acaba llegando a cero, ya que incluso los legisladores que llevan más tiempo en el cargo acaban jubilándose o muriendo.

Podemos tomar el resultado de la retención de cohortes y remodelar los datos para mostrarlos en formato de tabla. Pivota y aplana los resultados utilizando una función de agregación con una sentencia CASE; en este ejemplo se utiliza max, pero otras agregaciones como min o avg devolverían el mismo resultado. La retención se calcula para los años 0 a 4, pero pueden añadirse años adicionales siguiendo el mismo patrón:

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

La retención parece ser bastante baja, y en el gráfico podemos ver que es irregular en los primeros años. Uno de los motivos es que el mandato de un diputado dura dos años, y el de los senadores seis, pero el conjunto de datos sólo contiene registros del inicio de los nuevos mandatos; por tanto, nos faltan datos de los años en que un legislador seguía en el cargo pero no iniciaba un nuevo mandato. Medir la retención cada año es engañoso en este caso. Una opción es medir la retención sólo en ciclos de dos o seis años, pero también hay otra estrategia que podemos emplear para completar los datos "que faltan". Me referiré a esto a continuación, antes de volver al tema de la formación de grupos de cohortes.

Ajustar las series temporales para aumentar la precisión de la retención

Ya hablamos de las técnicas para limpiar los datos "ausentes" en el Capítulo 2, y volveremos a ellas en esta sección para llegar a una curva de retención más suave y veraz para los legisladores. Cuando se trabaja con datos de series temporales, como en el análisis de cohortes, es importante tener en cuenta no sólo los datos presentes, sino también si esos datos reflejan con exactitud la presencia o ausencia de entidades en cada periodo de tiempo. Esto es especialmente un problema en contextos en los que un acontecimiento capturado en los datos hace que la entidad persista durante algún periodo de tiempo que no está capturado en los datos. Por ejemplo, un cliente que compra una suscripción de software está representado en los datos en el momento de la transacción, pero ese cliente tiene derecho a utilizar el software durante meses o años y no está necesariamente representado en los datos durante ese lapso. Para corregir esto, necesitamos una forma de deducir el lapso de tiempo en el que la entidad sigue presente, ya sea con una fecha de finalización explícita o con el conocimiento de la duración de la suscripción o plazo. Entonces podremos decir que la entidad estaba presente en cualquier fecha comprendida entre esas fechas de inicio y fin.

En el conjunto de datos de los legisladores, tenemos un registro para la fecha de inicio de un mandato, pero nos falta la noción de que esto "da derecho" a un legislador a ejercer durante dos o seis años, según la cámara. Para corregir esto y suavizar la curva, tenemos que rellenar los valores "que faltan" para los años en que los legisladores siguen en el cargo entre los nuevos mandatos. Dado que este conjunto de datos incluye un valor term_end para cada legislatura, mostraré cómo crear un análisis de retención de cohortes más preciso rellenando las fechas entre los valores inicial y final. Luego mostraré cómo puedes imputar las fechas finales cuando el conjunto de datos no incluya una fecha final.

Calcular la retención utilizando una fecha de inicio y una fecha final definidas en los datos es el planteamiento más exacto. Para los siguientes ejemplos, consideraremos que los legisladores han sido retenidos en un año concreto si seguían en el cargo el último día del año, el 31 de diciembre. Antes de la Vigésima Enmienda a la Constitución de EEUU, los mandatos comenzaban el 4 de marzo, pero después la fecha de inicio se trasladó al 3 de enero, o a un día laborable posterior si el tercero cae en fin de semana. Los legisladores pueden prestar juramento en otros días del año debido a elecciones especiales fuera de ciclo o a nombramientos para cubrir escaños vacantes. En consecuencia, las fechas de term_start se agrupan en enero, pero se reparten a lo largo del año. Aunque podríamos elegir otro día, el 31 de diciembre es una estrategia para normalizar en torno a estas fechas de inicio variables.

El primer paso es crear un conjunto de datos que contenga un registro por cada 31 de diciembre que cada legislador estuvo en el cargo. Esto se puede conseguir uniendola subconsulta que encontró la tabla first_term a la tabla legislators_terms para encontrar las tablas term_start y term_end de cada legislatura. Un segundo JOIN a la tabla date_dim recupera las fechas comprendidas entre las fechas de inicio y fin, restringiendo los valores devueltos a c.month_name = 'December' and c.day_of_month = 31. period se calcula como los años comprendidos entre date de date_dim y first_term. Ten en cuenta que aunque hayan transcurrido más de 11 meses entre la jura del cargo en enero y el 31 de diciembre, el primer año sigue apareciendo como 0:

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
...          ...         ...         ...         ...         ...

Consejo

Si no dispones de una dimensión de fecha, puedes crear una subconsulta con las fechas necesarias de un par de formas. Si tu base de datos admite la generate_series, puedes crear una subconsulta que devuelva las fechas deseadas:

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

Puede que quieras guardar esto como una tabla o vista para utilizarlo más adelante. Como alternativa, puedes consultar el conjunto de datos o cualquier otra tabla de la base de datos que tenga un conjunto completo de fechas. En este caso, la tabla tiene todos los años necesarios, pero haremos una fecha de 31 de diciembre para cada año utilizando la función make_date:

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

Hay varias formas creativas de obtener las series de fechas necesarias. Utiliza el método que esté disponible y sea más sencillo dentro de tus consultas.

Ahora tenemos una fila para cada date (final de año) para el que queremos calcular la retención. El siguiente paso es calcular el cohort_retained para cada periodo, lo que se hace con una función count de id_bioguide. Se utiliza una función coalesce de en period para establecer un valor por defecto de 0 cuando es nulo. Esto se ocupa de los casos en los que el mandato de un legislador comienza y termina en el mismo año, dando crédito por servir en ese año:

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
...     ...

El último paso es calcular las cohort_size y pct_retained como hicimos anteriormente utilizando las funciones de ventana first_value:

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
...     ...          ...              ...

Los resultados, graficados en la Figura 4-4, son ahora mucho más precisos. Casi todos los legisladores siguen en el cargo en el año 1, y la primera gran caída se produce en el año 2, cuando algunos representantes no serán reelegidos.

Si el conjunto de datos no contiene una fecha final, hay un par de opciones para imputar una. Una opción es añadir un intervalo fijo a la fecha de inicio, cuando se conoce la duración de una suscripción o plazo. Esto se puede hacer con la matemática de fechas añadiendo un intervalo constante a la term_start. Aquí, una sentencia CASE se encarga de la suma para las dos 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
...          ...         ...         ...

Este bloque de código puede introducirse en el código de retención para obtener period y pct_retained. El inconveniente de este método es que no recoge los casos en los que un legislador no completó un mandato completo, lo que puede ocurrir en caso de fallecimiento o nombramiento para un cargo superior.

Una segunda opción es utilizar la fecha de inicio posterior, menos un día, como fecha term_end. Esta puede calcularse con la función de ventana lead. Esta función es similar a la función lag que hemos utilizado anteriormente, pero en lugar de devolver un valor de una fila anterior de la partición, devuelve un valor de una fila posterior de la partición, según se determine en la cláusula ORDER BY. El valor por defecto es una fila, que utilizaremos aquí, pero la función tiene un argumento opcional que indica un número diferente de filas. Aquí hallamos la fecha term_start del término posterior utilizando lead y luego restamos el intervalo '1 day' para obtener term_end:

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
...          ...         ...         ...

Este bloque de código puede introducirse en el código de retención. Este método tiene un par de inconvenientes. En primer lugar, cuando no hay ningún mandato posterior, la función lead devuelve nulo, dejando ese mandato sin term_end. En esos casos podría utilizarse un valor por defecto, como el intervalo por defecto que se muestra en el último ejemplo. El segundo inconveniente es que este método supone que los mandatos son siempre consecutivos, sin tiempo de inactividad. Aunque la mayoría de los legisladores suelen ejercer de forma continuada hasta el final de su carrera parlamentaria, no cabe duda de que hay ejemplos de intervalos entre mandatos de varios años.

Siempre que hagamos ajustes para completar los datos que faltan, debemos tener cuidado con las suposiciones que hacemos. En contextos basados en suscripciones o plazos, las fechas de inicio y fin explícitas suelen ser las más precisas. Cualquiera de los otros dos métodos mostrados -añadir un intervalo fijo o establecer la fecha de finalización en relación con la siguiente fecha de inicio- puede utilizarse cuando no hay fecha de finalización y tenemos una expectativa razonable de que la mayoría de los clientes o usuarios permanecerán durante la duración supuesta.

Ahora que hemos visto cómo calcular una curva de retención básica y corregir las fechas que faltan, podemos empezar a añadir grupos de cohortes. Comparar la retención entre distintos grupos es una de las principales razones para hacer análisis de cohortes. A continuación, hablaré de la formación de grupos a partir de la propia serie temporal y, después, de la formación de grupos de cohortes a partir de datos de otras tablas.

Cohortes derivadas de la propia serie temporal

Ahora que tenemos el código SQL para calcular la retención, podemos empezar a dividir las entidades en cohortes. En esta sección, mostraré cómo derivar agrupaciones de cohortes a partir de la propia serie temporal. Primero hablaré de las cohortes temporales basadas en la primera fecha, y luego explicaré cómo hacer cohortes basadas en otros atributos de la serie temporal.

La forma más habitual de crear las cohortes se basa en la primera o mínima fecha u hora en que aparece la entidad en la serie temporal. Esto significa que sólo se necesita una tabla para el análisis de retención de cohortes: la propia serie temporal. La cohorte por la primera aparición o acción es interesante porque a menudo los grupos que empiezan en momentos diferentes se comportan de forma diferente. En el caso de los servicios al consumidor, los adoptantes tempranos suelen ser más entusiastas y retienen de forma diferente que los adoptantes tardíos, mientras que en el software SaaS, los adoptantes tardíos pueden retener mejor porque el producto está más maduro. Las cohortes temporales pueden agruparse por cualquier granularidad temporal que sea significativa para la organización, aunque son habituales las cohortes semanales, mensuales o anuales. Si no estás seguro de qué agrupación utilizar, prueba a ejecutar el análisis de cohortes con diferentes agrupaciones, sin que el tamaño de las cohortes sea demasiado pequeño, para ver dónde surgen patrones significativos. Afortunadamente, una vez que sabes cómo construir las cohortes y el análisis de retención, sustituir diferentes granularidades temporales es sencillo.

El primer ejemplo utilizará cohortes anuales, y luego demostraré el intercambio en siglos. La cuestión clave que consideraremos es si la época en la que un legislador tomó posesión de su cargo por primera vez tiene alguna correlación con su permanencia en el cargo. Las tendencias políticas y el estado de ánimo del público cambian con el tiempo, pero ¿en qué medida?

Para calcular las cohortes anuales, primero añadimos el año del first_term calculado anteriormente a la consulta que encuentra los period y cohort_retained:

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
...         ...     ...

A continuación, esta consulta se utiliza como subconsulta, y los valores cohort_size y pct_retained se calculan en la consulta externa como antes. En este caso, sin embargo, necesitamos una cláusula PARTITION BY que incluya first_year para que el first_value se calcule sólo dentro del conjunto de filas de ese first_year, en lugar de en todo el conjunto de resultados de la subconsulta:

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
...         ...     ...          ...              ...

Este conjunto de datos incluye más de doscientos años de inicio, demasiados para graficarlos fácilmente o examinarlos en una tabla. A continuación, examinaremos un intervalo menos granular y cohortaremos a los legisladores por el siglo del first_term. Este cambio se realiza fácilmente sustituyendo century por year en la función date_part de la subconsulta aa. Recuerda que los nombres de los siglos están desplazados respecto a los años que representan, de modo que el siglo XVIII duró de 1700 a 1799, el siglo XIX duró de 1800 a 1899, etc. La partición en la función first_value cambia al campo 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
...            ...     ...          ...              ...

Los resultados se representan gráficamente en la Figura 4-5. La retención en los primeros años ha sido mayor para los elegidos por primera vez en el siglo XX o XXI. El siglo XXI aún está en marcha, por lo que muchos de esos legisladores no han tenido la oportunidad de permanecer en el cargo durante cinco o más años, aunque siguen incluidos en el denominador. Podríamos plantearnos eliminar el siglo XXI del análisis, pero lo he dejado aquí para demostrar cómo la curva de retención desciende artificialmente debido a esta circunstancia.

Las cohortes pueden definirse a partir de otros atributos de una serie temporal, además de la primera fecha, con opciones que dependen de los valores de la tabla. La tabla legislators_terms tiene un campo state, que indica qué estado representa la persona para ese plazo. Podemos utilizarlo para crear cohortes, y las basaremos en el primer estado para asegurarnos de que cualquier persona que haya representado a varios estados aparezca en los datos sólo una vez.

Advertencia

Al cohortar en función de un atributo que puede cambiar con el tiempo, es importante asegurarse de que a cada entidad se le asigna un solo valor. De lo contrario, la entidad puede estar representada en múltiples cohortes, introduciendo un sesgo en el análisis. Normalmente se utiliza el valor del registro más antiguo del conjunto de datos.

Para encontrar el primer estado de cada legislador, podemos utilizar la función ventana first_value. En este ejemplo, también convertiremos la función min en una función ventana para evitar una larga cláusula GROUP BY:

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
...          ...         ...

A continuación, podemos introducir este código en nuestro código de retención para hallar la retención en first_state:

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
...          ...     ...          ...              ...

En la Figura 4-6 se representan gráficamente las curvas de retención de los cinco estados con mayor número total de legisladores. Los elegidos en Illinois y Massachusetts tienen la retención más alta, mientras que los neoyorquinos tienen la retención más baja. Determinar las razones sería una rama interesante de este análisis.

Definir cohortes a partir de las series temporales es relativamente sencillo utilizando una fecha min para cada entidad y convirtiendo luego esa fecha en un mes, año o siglo, según convenga para el análisis. Cambiar entre mes y año u otros niveles de granularidad también es sencillo, lo que permite probar múltiples opciones para encontrar una agrupación que sea significativa para la organización. Se pueden utilizar otros atributos para la cohorte con la función de ventana first_value. A continuación, pasaremos a los casos en los que el atributo de cohorte procede de una tabla distinta a la de la serie temporal.

Definir la cohorte a partir de una tabla separada

A menudo, las características que definen una cohorte existen en una tabla independiente de la que contiene las series temporales. Por ejemplo, una base de datos puede tener una tabla de clientes con información como el origen de la adquisición o la fecha de registro, por la que se puede cohortar a los clientes. Añadir atributos de otras tablas, o incluso subconsultas, es relativamente sencillo y puede hacerse en el análisis de retención y otros análisis relacionados que se tratan más adelante en el capítulo.

Para este ejemplo, consideraremos si el sexo del legislador tiene algún impacto en su retención. La tabla legislators tiene un campo gender, donde F significa mujer y M significa hombre, que podemos utilizar para cohortar a los legisladores. Para ello, uniremos la tabla legislators con el alias d para añadir gender al cálculo de cohort_retained, en lugar de año o siglo:

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
...     ...     ...

Inmediatamente queda claro que muchos más hombres que mujeres han cumplido legislaturas. Ahora podemos calcular la percent_retained para poder comparar la retención de estos grupos:

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
...     ...     ...          ...              ...

Podemos ver en los resultados graficados en la Figura 4-7 que la retención es mayor para las legisladoras que para sus homólogos masculinos en los periodos 2 a 29. La primera legisladora no tomó posesión de su cargo hasta 1917, cuando Jeannette Rankin se incorporó a la Cámara como representante republicana por Montana. Como hemos visto antes, la retención ha aumentado en los siglos más recientes.

Para hacer una comparación más justa, podríamos restringir los legisladores incluidos en el análisis a sólo aquellos cuyo first_term comenzó desde que hay mujeres en el Congreso. Podemos hacerlo añadiendo un filtro WHERE a la subconsulta aa. Aquí los resultados también se restringen a los que empezaron antes de 2000, para garantizar que las cohortes han tenido al menos 20 años posibles para permanecer en el cargo:

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
...     ...     ...          ...              ...

Los legisladores varones siguen superando en número a las legisladoras, pero por un margen menor. La retención de las cohortes se representa gráficamente en la Figura 4-8. Con las cohortes revisadas, los legisladores varones tienen mayor retención hasta el año 7, pero a partir del año 12, las legisladoras tienen mayor retención. La diferencia entre los dos análisis de cohortes basados en el género subraya la importancia de establecer cohortes adecuadas y de asegurarse de que disponen de cantidades de tiempo comparables para estar presentes o completar otras acciones de interés. Para mejorar aún más este análisis, podríamos establecer cohortes tanto por año o década de inicio como por género, a fin de controlar los cambios adicionales en la retención a lo largo del siglo XX y en el siglo XXI.

Las cohortes pueden definirse de múltiples formas, a partir de las series temporales y de otras tablas. Con el marco que hemos desarrollado, se pueden intercambiar subconsultas, vistas u otras tablas derivadas, abriendo toda una gama de cálculos para que sean la base de una cohorte. Pueden utilizarse múltiples criterios, como el año de inicio y el sexo. Una precaución al dividir poblaciones en cohortes basadas en criterios múltiples es que esto puede dar lugar a cohortes dispersas, en las que algunos de los grupos definidos son demasiado pequeños y no están representados en el conjunto de datos para todos los periodos de tiempo. En la siguiente sección se analizan métodos para superar este reto.

Tratar con cohortes dispersas

En el conjunto de datos ideal, cada cohorte tiene alguna acción o registro en la serie temporal para cada periodo de interés. Ya hemos visto cómo pueden producirse fechas "perdidas" debido a suscripciones o plazos que duran varios periodos, y vimos cómo corregirlas utilizando una dimensión de fecha para inferir fechas intermedias. Otro problema puede surgir cuando, debido a criterios de agrupación, la cohorte resulta demasiado pequeña y, como resultado, sólo está representada esporádicamente en los datos. Una cohorte puede desaparecer del conjunto de resultados, cuando preferiríamos que apareciera con un valor de retención cero. Este problema se denomina cohortes dispersas, y puede solucionarse con un uso cuidadoso de las JOIN IZQUIERDAS.

Para demostrarlo, intentemos agrupar a las legisladoras por el primer estado al que representaron para ver si hay diferencias en la permanencia. Ya hemos visto que ha habido relativamente pocas legisladoras. Cohortarlas aún más por estado es muy probable que cree algunas cohortes dispersas en las que haya muy pocos miembros. Antes de hacer los ajustes de código, añadamos first_state (calculado en la sección sobre derivar cohortes de las series temporales) a nuestro ejemplo anterior de género y observemos los resultados:

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
...          ...     ...     ...          ...             ...

Si se grafican los resultados de los 20 primeros periodos, como en la Figura 4-9, se observa la escasez de cohortes. Alaska no tuvo ninguna legisladora, mientras que la curva de retención femenina de Arizona desaparece después del 3er año. Sólo California, un estado grande con muchos legisladores, tiene curvas de retención completas para ambos sexos. Este patrón se repite para otros estados pequeños y grandes.

Veamos ahora cómo garantizar un registro para cada periodo, de forma que la consulta devuelva valores cero para la retención en lugar de nulos. El primer paso es consultar todas las combinaciones de periods y atributos de cohorte, en este caso first_state y gender, con el cohort_size inicial para cada combinación. Esto puede hacerse uniendola subconsulta aa, que calcula la cohorte, con una subconsulta generate_series que devuelve todos los enteros de 0 a 20, con los criterios on 1 = 1. Esta es una forma práctica de forzar una JOIN cartesiana cuando las dos subconsultas no tienen ningún campo en común:

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
...    ...     ...     ...

El siguiente paso es volver a UNIR esto a los periodos reales en el cargo, con una UNIÓN IZQUIERDA para garantizar que todos los periodos de tiempo permanecen en el resultado final:

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
...    ...           ...     ...          ...             ...

A continuación, podemos hacer pivotar los resultados y confirmar que existe un valor para cada cohorte en cada periodo:

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
...     ...          ...    ...     ...     ...     ...     ...

Observa que en este punto, el código SQL se ha hecho bastante largo. Una de las partes más difíciles de escribir SQL para el análisis de retención de cohortes es mantener toda la lógica recta y el código organizado, un tema que trataré con más detalle en el Capítulo 8. Cuando construyo el código de retención, me resulta útil ir paso a paso, comprobando los resultados por el camino. También compruebo al azar cohortes individuales para validar que el resultado final es exacto.

Las cohortes pueden definirse de muchas formas. Hasta ahora, hemos normalizado todas nuestras cohortes a la primera fecha en que aparecen en los datos de la serie temporal. Sin embargo, ésta no es la única opción, y se pueden realizar análisis interesantes a partir de la mitad de la vida de una entidad. Antes de concluir nuestro trabajo sobre el análisis de la retención, echemos un vistazo a esta forma adicional de definir las cohortes.

Definir cohortes a partir de fechas distintas de la primera cita

Normalmente las cohortes temporales se definen a partir de la primera aparición de la entidad en la serie temporal o a partir de alguna otra fecha más temprana, como la fecha de registro. Sin embargo, crear cohortes a partir de una fecha diferente puede ser útil y revelador. Por ejemplo, podríamos querer analizar la retención de todos los clientes que utilizan un servicio a partir de una fecha determinada. Este tipo de análisis puede utilizarse para comprender si los cambios de producto o de marketing han tenido un impacto a largo plazo en los clientes existentes.

Si utilizamos una fecha distinta de la primera, debemos tener cuidado de definir con precisión los criterios de inclusión en cada cohorte. Una opción es elegir las entidades presentes en una fecha concreta del calendario. Esto es relativamente sencillo de poner en código SQL, pero puede ser problemático si una gran parte de la población de usuarios habituales no se presenta todos los días, haciendo que la retención varíe en función del día exacto elegido. Una opción para corregir esto es calcular la retención para varias fechas de inicio y luego hacer una media de los resultados.

Otra opción es utilizar una ventana de tiempo, como una semana o un mes. Cualquier entidad que aparezca en el conjunto de datos durante esa ventana se incluye en la cohorte. Aunque este enfoque suele ser más representativo de la empresa o el proceso, la contrapartida es que el código SQL se volverá más complejo, y el tiempo de consulta puede ser más lento debido a los cálculos más intensos de la base de datos. Encontrar el equilibrio adecuado entre el rendimiento de la consulta y la precisión de los resultados es una especie de arte.

Veamos cómo calcular ese análisis intermedio con el conjunto de datos de los legisladores, considerando la retención de los legisladores que estaban en el cargo en el año 2000. Nos basaremos en term_type, que tiene los valores "sen" para los senadores y "rep" para los representantes. La definición incluirá a cualquier legislador en el cargo en cualquier momento del año 2000: los que empezaron antes de 2000 y cuyos mandatos terminaron durante o después de 2000 cumplen los requisitos, al igual que los que empezaron un mandato en 2000. Podemos codificar cualquier fecha del año 2000 como first_term, ya que después comprobaremos si estuvieron en el cargo en algún momento del año 2000. El min_start de los mandatos que caen en esta ventana también se calcula para utilizarlo en un paso posterior:

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
...          ...        ...         ...

A continuación, podemos introducir esto en nuestro código de retención, con dos ajustes. En primer lugar, se añade un criterio JOIN adicional entre la subconsulta a y la tabla legislators_terms para devolver sólo los términos que empezaron en la fecha min_start o después. En segundo lugar, se añade un filtro adicional a la date_dim para que sólo devuelva dates en 2000 o posteriores:

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
...        ...     ...          ...              ...

La Figura 4-10 muestra que, a pesar de los mandatos más largos de los senadores, la retención entre las dos cohortes fue similar, y de hecho fue peor para los senadores después de 10 años. Un análisis más detallado en el que se compararan los distintos años en que fueron elegidos por primera vez, u otros atributos de la cohorte, podría arrojar algunas conclusiones interesantes.

Un caso de uso común para la cohorte sobre un valor distinto de un valor inicial es cuando se intenta analizar la retención después de que una entidad haya alcanzado un umbral, como un cierto número de compras o una cierta cantidad gastada. Como con cualquier cohorte, es importante tener cuidado al definir qué califica a una entidad para estar en una cohorte y qué fecha se utilizará como fecha de inicio.

La cohorte de retención es una forma poderosa de comprender el comportamiento de las entidades en un conjunto de datos de series temporales. Hemos visto cómo calcular la retención con SQL y cómo hacer cohortes basadas en la propia serie temporal o en otras tablas, y a partir de puntos intermedios de la vida de las entidades. También hemos visto cómo utilizar funciones y JOINspara ajustar fechas dentro de series temporales y compensar cohortes dispersas. Hay varios tipos de análisis relacionados con la retención de cohortes: análisis, supervivencia, retorno y cálculos acumulativos, todos ellos basados en el código SQL que hemos desarrollado para la retención. Pasemos a ellos a continuación.

Análisis de cohortes relacionados

En la última sección, aprendimos a escribir SQL para el análisis de retención de cohortes. La retención capta si una entidad estaba en un conjunto de datos de series temporales en una fecha o ventana de tiempo específica. Además de la presencia en una fecha concreta, el análisis suele interesarse por cuestiones como cuánto duró una entidad, si una entidad realizó varias acciones y cuántas de esas acciones se produjeron. Todas ellas pueden responderse con un código similar al de la retención y que se adapta bien a casi cualquier criterio de cohorte que desees. Echemos un vistazo al primero de ellos, la supervivencia.

Supervivencia

La supervivencia, también llamada análisis de supervivencia, se ocupa de las preguntas sobre cuánto dura algo, o la duración del tiempo hasta un acontecimiento concreto, como la rotación o la muerte. El análisis de supervivencia puede responder a preguntas sobre la proporción de la población que probablemente permanecerá más allá de un determinado periodo de tiempo. Las cohortes pueden ayudar a identificar o al menos proporcionar hipótesis sobre qué características o circunstancias aumentan o disminuyen la probabilidad de supervivencia.

Esto es similar a un análisis de retención, pero en lugar de calcular si una entidad estaba presente en un periodo determinado, calculamos si la entidad está presente en ese periodo o más adelante en la serie temporal. A continuación, se calcula la proporción de la cohorte total. Normalmente, se eligen uno o varios periodos en función de la naturaleza del conjunto de datos analizado. Por ejemplo, si queremos conocer la proporción de jugadores que sobreviven una semana o más, podemos comprobar las acciones que se producen después de una semana desde el inicio y considerar a los jugadores que aún sobreviven. Por otra parte, si nos preocupa el número de estudiantes que siguen en la escuela después de un cierto número de años, podríamos buscar la ausencia de un evento de graduación en un conjunto de datos. El número de periodos puede SELECCIONARSEcalculando una vida media o típica o eligiendo periodos de tiempo que sean significativos para la organización o el proceso analizados, como un mes, un año o un periodo de tiempo más largo.

En este ejemplo, examinaremos la proporción de legisladores que sobrevivieron en el cargo durante una década o más tras el inicio de su primer mandato. Como no necesitamos conocer las fechas concretas de cada legislatura, podemos empezar calculando las fechas de la primera y la última term_start, utilizando las agregaciones min y max:

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

id_bioguide  first_term  last_term
-----------  ----------  ---------
A000118      1975-01-14  1977-01-04
P000281      1933-03-09  1937-01-05
K000039      1933-03-09  1951-01-03
...          ...         ...

A continuación, añadimos a la consulta una función date_part para encontrar el siglo del min term_start, y calculamos el tenure como el número de años entre los min y max term_starts encontrados con la función age:

SELECT id_bioguide
,date_part('century',min(term_start)) as first_century
,min(term_start) as first_term
,max(term_start) as last_term
,date_part('year',age(max(term_start),min(term_start))) as tenure
FROM legislators_terms
GROUP BY 1
;

id_bioguide  first_century  first_term  last_term  tenure
-----------  -------------  ----------  ---------  ------
A000118      20.0           1975-01-14  1977-01-04  1.0
P000281      20.0           1933-03-09  1937-01-05  3.0
K000039      20.0           1933-03-09  1951-01-03  17.0
...          ...            ...         ...         ...

Por último, calculamos el cohort_size con un count de todos los legisladores, además de calcular el número de los que sobrevivieron al menos 10 años utilizando una sentencia CASE y la agregación count. El porcentaje que sobrevivió se obtiene dividiendo estos dos valores:

SELECT first_century
,count(distinct id_bioguide) as cohort_size
,count(distinct case when tenure >= 10 then id_bioguide 
                     end) as survived_10
,count(distinct case when tenure >= 10 then id_bioguide end) 
 / count(distinct id_bioguide) as pct_survived_10
FROM
(
    SELECT id_bioguide
    ,date_part('century',min(term_start)) as first_century
    ,min(term_start) as first_term
    ,max(term_start) as last_term
    ,date_part('year',age(max(term_start),min(term_start))) as tenure
    FROM legislators_terms
    GROUP BY 1
) a
GROUP BY 1
;

century  cohort  survived_10  pct_survived_10
-------  ------  -----------  ---------------
18       368     83           0.2255
19       6299    892          0.1416
20       5091    1853         0.3640
21       760     119          0.1566

Como las legislaturas pueden ser consecutivas o no, también podemos calcular la proporción de legisladores de cada siglo que sobrevivieron durante cinco o más legislaturas en total. En la subconsulta, añade un count para hallar el número total de mandatos por legislador. Luego, en la consulta externa, divide el número de legisladores con cinco o más legislaturas por el tamaño total de la cohorte:

SELECT first_century
,count(distinct id_bioguide) as cohort_size
,count(distinct case when total_terms >= 5 then id_bioguide end) 
 as survived_5
,count(distinct case when total_terms >= 5 then id_bioguide end)
 / count(distinct id_bioguide) as pct_survived_5_terms
FROM
(
    SELECT id_bioguide
    ,date_part('century',min(term_start)) as first_century
    ,count(term_start) as total_terms
    FROM legislators_terms
    GROUP BY 1
) a
GROUP BY 1
;

century  cohort  survived_5  pct_survived_5_terms
-------  ------  ----------  --------------------
18       368     63          0.1712
19       6299    711         0.1129
20       5091    2153        0.4229
21       760     205         0.2697

Diez años o cinco periodos es algo arbitrario. También podemos calcular la supervivencia para cada número de años o periodos y mostrar los resultados en forma de gráfico o tabla. Aquí calculamos la supervivencia para cada número de periodos de 1 a 20. Esto se consigue mediante una JOIN cartesiana a una subconsulta que contiene los enteros derivados por la función generate_series:

SELECT a.first_century, b.terms
,count(distinct id_bioguide) as cohort
,count(distinct case when a.total_terms >= b.terms then id_bioguide 
                     end) as cohort_survived
,count(distinct case when a.total_terms >= b.terms then id_bioguide 
                     end)
 / count(distinct id_bioguide) as pct_survived
FROM
(
    SELECT id_bioguide
    ,date_part('century',min(term_start)) as first_century
    ,count(term_start) as total_terms
    FROM legislators_terms
    GROUP BY 1
) a
JOIN
(
    SELECT generate_series as terms 
    FROM generate_series(1,20,1)
) b on 1 = 1
GROUP BY 1,2
;

century  terms  cohort  cohort_survived  pct_survived
-------  -----  ------  ---------------  ------------
18       1      368     368              1.0000
18       2      368     249              0.6766
18       3      368     153              0.4157
...      ...    ...     ...              ...

Los resultados se representan gráficamente en la Figura 4-11. La supervivencia fue mayor en el siglo XX, resultado que concuerda con los que vimos anteriormente, en los que la retención también fue mayor en el siglo XX.

La supervivencia está estrechamente relacionada con la retención. Mientras que la retención cuenta las entidades presentes en un número determinado de periodos desde el principio, la supervivencia sólo tiene en cuenta si una entidad estaba presente a partir de un periodo concreto o más tarde. Como resultado, el código es más sencillo, ya que sólo necesita la primera y la última fecha de la serie temporal, o un recuento de fechas. La cohorte se realiza de forma similar a la cohorte para la retención, y las definiciones de cohorte pueden proceder de dentro de la serie temporal o derivarse de otra tabla o subconsulta.

A continuación consideraremos otro tipo de análisis que, en cierto modo, es el inverso de la supervivencia. En lugar de calcular si una entidad está presente en el conjunto de datos en un momento determinado o más tarde, calcularemos si una entidad vuelve o repite una acción en un periodo determinado o antes. Esto se denomina comportamiento de retorno o repetición de compra.

Comportamiento de devolución o repetición de compra

La supervivencia es útil para comprender cuánto tiempo es probable que permanezca una cohorte. Otro tipo útil de análisis de cohortes trata de comprender si cabe esperar que un miembro de la cohorte regrese en un plazo de tiempo determinado y la intensidad de la actividad durante ese plazo. Esto se denomina comportamiento de retorno o de compra repetida.

Por ejemplo, un sitio de comercio electrónico puede querer saber no sólo cuántos nuevos compradores se han conseguido mediante una campaña de marketing, sino también si esos compradores se han convertido en compradores habituales. Una forma de averiguarlo es calcular simplemente las compras totales por cliente. Sin embargo, comparar los clientes captados hace dos años con los captados hace un mes no es justo, ya que los primeros han tenido mucho más tiempo para volver. Es casi seguro que la cohorte más antigua parecería más valiosa que la más reciente. Aunque esto es cierto en cierto sentido, da una imagen incompleta de cómo es probable que se comporten las cohortes a lo largo de toda su vida.

Para hacer comparaciones justas entre cohortes con diferentes fechas de inicio, tenemos que crear un análisis basado en una caja de tiempo, o una ventana de tiempo fija de desde la primera fecha, y considerar si los miembros de la cohorte regresaron dentro de esa ventana. De este modo, cada cohorte tiene la misma cantidad de tiempo en consideración, siempre que incluyamos sólo las cohortes para las que haya transcurrido la ventana completa. El análisis de los retornos es habitual en las organizaciones minoristas, pero también puede aplicarse en otros ámbitos. Por ejemplo, una universidad podría querer ver cuántos alumnos se matriculan en un segundo curso, o un hospital podría estar interesado en saber cuántos pacientes necesitan tratamientos médicos de seguimiento tras un incidente inicial.

Para demostrar el análisis del retorno, podemos plantear una nueva pregunta al conjunto de datos de los legisladores: cuántos legisladores tienen más de un tipo de mandato y, concretamente, qué proporción de ellos empiezan como representantes y pasan a ser senadores (algunos senadores se convierten más tarde en representantes, pero eso es mucho menos frecuente). Como son relativamente pocos los que hacen esta transición, cohortaremos a los legisladores por el siglo en el que se convirtieron en representantes por primera vez.

El primer paso consiste en hallar el tamaño de la cohorte de cada siglo, utilizando la subconsulta y los cálculos de date_part vistos anteriormente, sólo para los que tienen term_type = 'rep':

SELECT date_part('century',a.first_term) as cohort_century
,count(id_bioguide) as reps
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms
    WHERE term_type = 'rep'
    GROUP BY 1
) a
GROUP BY 1
;

cohort_century  reps
--------------  ----
18              299
19              5773
20              4481
21              683

A continuación realizaremos un cálculo similar, con un JOIN a la tabla legislators_terms, para encontrar a los representantes que más tarde se convirtieron en senadores. Esto se consigue con las cláusulas b.term_type = 'sen' y b.term_start > a.first_term:

SELECT date_part('century',a.first_term) as cohort_century
,count(distinct a.id_bioguide) as rep_and_sen
FROM
(
    SELECT id_bioguide, min(term_start) as first_term
    FROM legislators_terms
    WHERE term_type = 'rep'
    GROUP BY 1
) a
JOIN legislators_terms b on a.id_bioguide = b.id_bioguide
and b.term_type = 'sen' and b.term_start > a.first_term
GROUP BY 1
;

cohort_century  rep_and_sen
--------------  -----------
18              57
19              329
20              254
21              25

Por último, unimos estas dos subconsultas y calculamos el porcentaje de representantes que se convirtieron en senadores. Se utiliza una JOIN IZQUIERDA; esta cláusula suele recomendarse para garantizar que se incluyen todas las cohortes, haya ocurrido o no el acontecimiento posterior. Si hay un siglo en el que ningún representante se convirtió en senador, seguiremos queriendo incluir ese siglo en el conjunto de resultados:

SELECT aa.cohort_century
,bb.rep_and_sen / aa.reps as pct_rep_and_sen
FROM
(
    SELECT date_part('century',a.first_term) as cohort_century
    ,count(id_bioguide) as reps
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        WHERE term_type = 'rep'
        GROUP BY 1
    ) a
    GROUP BY 1
) aa
LEFT JOIN
(
    SELECT date_part('century',b.first_term) as cohort_century
    ,count(distinct b.id_bioguide) as rep_and_sen
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        WHERE term_type = 'rep'
        GROUP BY 1
    ) b
    JOIN legislators_terms c on b.id_bioguide = b.id_bioguide
    and c.term_type = 'sen' and c.term_start > b.first_term
    GROUP BY 1
) bb on aa.cohort_century = bb.cohort_century
;

cohort_century  pct_rep_and_sen
--------------  ---------------
18              0.1906
19              0.0570
20              0.0567
21              0.0366

Los representantes del siglo XVIII tenían más probabilidades de convertirse en senadores. Sin embargo, aún no hemos aplicado una casilla temporal para garantizar una comparación justa. Aunque podemos suponer con seguridad que todos los legisladores que ejercieron sus funciones en los siglos XVIII y XIX ya no viven, muchos de los que fueron elegidos por primera vez en los siglos XX y XXI todavía están en mitad de sus carreras. Añadiendo el filtro WHERE age(c.term_start, b.first_term) <= interval '10 years' a la subconsulta bb se crea una ventana temporal de 10 años. Observa que la ventana puede ampliarse o reducirse fácilmente cambiando la constante del intervalo. Un filtro adicional aplicado a la subconsulta a, WHERE first_term <= '2009-12-31', excluye a los que llevaban menos de 10 años de carrera cuando se reunió el conjunto de datos:

SELECT aa.cohort_century
,bb.rep_and_sen * 100.0 / aa.reps as pct_10_yrs
FROM
(
    SELECT date_part('century',a.first_term)::int as cohort_century
    ,count(id_bioguide) as reps
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        WHERE term_type = 'rep'
        GROUP BY 1
    ) a
    WHERE first_term <= '2009-12-31'
    GROUP BY 1
) aa
LEFT JOIN
(
    SELECT date_part('century',b.first_term)::int as cohort_century
    ,count(distinct b.id_bioguide) as rep_and_sen
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        WHERE term_type = 'rep'
        GROUP BY 1
    ) b
    JOIN legislators_terms c on b.id_bioguide = c.id_bioguide
    and c.term_type = 'sen' and c.term_start > b.first_term
    WHERE age(c.term_start, b.first_term) <= interval '10 years'
    GROUP BY 1
) bb on aa.cohort_century = bb.cohort_century
;

Cohort_century  pct_10_yrs
--------------  ----------
18              0.0970
19              0.0244
20              0.0348
21              0.0764

Con este nuevo ajuste, el siglo XVIII sigue teniendo la mayor proporción de representantes que se convierten en senadores en un plazo de 10 años, pero el siglo XXI tiene la segunda mayor proporción, y el siglo XX tuvo una proporción mayor que el XIX.

Como 10 años es algo arbitrario, también podríamos querer comparar varias ventanas temporales. Una opción es ejecutar la consulta varias veces con intervalos diferentes y anotar los resultados. Otra opción es calcular varias ventanas en el mismo conjunto de resultados utilizando un conjunto de sentencias CASE dentro de las agregaciones de count distinct para formar los intervalos, en lugar de especificar el intervalo en la cláusula WHERE:

SELECT aa.cohort_century
,bb.rep_and_sen_5_yrs * 1.0 / aa.reps as pct_5_yrs
,bb.rep_and_sen_10_yrs * 1.0 / aa.reps as pct_10_yrs
,bb.rep_and_sen_15_yrs * 1.0 / aa.reps as pct_15_yrs
FROM
(
    SELECT date_part('century',a.first_term) as cohort_century
    ,count(id_bioguide) as reps
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        WHERE term_type = 'rep'
        GROUP BY 1
    ) a
    WHERE first_term <= '2009-12-31'
    GROUP BY 1
) aa
LEFT JOIN
(
    SELECT date_part('century',b.first_term) as cohort_century
    ,count(distinct case when age(c.term_start,b.first_term) 
                              <= interval '5 years' 
                         then b.id_bioguide end) as rep_and_sen_5_yrs
    ,count(distinct case when age(c.term_start,b.first_term) 
                              <= interval '10 years' 
                         then b.id_bioguide end) as rep_and_sen_10_yrs
    ,count(distinct case when age(c.term_start,b.first_term) 
                              <= interval '15 years' 
                         then b.id_bioguide end) as rep_and_sen_15_yrs
    FROM
    (
        SELECT id_bioguide, min(term_start) as first_term
        FROM legislators_terms
        WHERE term_type = 'rep'
        GROUP BY 1
    ) b
    JOIN legislators_terms c on b.id_bioguide = c.id_bioguide
    and c.term_type = 'sen' and c.term_start > b.first_term
    GROUP BY 1
) bb on aa.cohort_century = bb.cohort_century
;

cohort_century  pct_5_yrs  pct_10_yrs  pct_15_yrs 
--------------  ---------  ----------  ---------- 
18              0.0502     0.0970      0.1438
19              0.0088     0.0244      0.0409
20              0.0100     0.0348      0.0478
21              0.0400     0.0764      0.0873

Con este resultado, podemos ver cómo evolucionó a lo largo del tiempo la proporción de representantes que se convirtieron en senadores, tanto dentro de cada cohorte como entre cohortes. Además del formato de tabla, el gráfico del resultado revela a menudo tendencias interesantes. En la Figura 4-12, las cohortes basadas en el siglo se sustituyen por cohortes basadas en la primera década, y se muestran las tendencias a lo largo de 10 y 20 años. La conversión de representantes en senadores durante las primeras décadas de la nueva legislatura estadounidense fue claramente diferente de los patrones de los años posteriores.

Encontrar el comportamiento repetido dentro de un cuadro temporal fijo es una herramienta útil para comparar cohortes. Esto es especialmente cierto cuando los comportamientos son de naturaleza intermitente, como el comportamiento de compra o el consumo de contenidos o servicios. En la siguiente sección, veremos cómo calcular no sólo si una entidad tuvo una acción posterior, sino también cuántas acciones posteriores tuvo, y las agregaremos con cálculos acumulativos.

Cálculos acumulativos

Análisis de cohortes acumulativas puede utilizarse para establecer el valor acumulado de por vida, también llamado valor de por vida del cliente (las siglas CLTV y LTV se utilizan indistintamente), y para monitorear las cohortes más nuevas con el fin de poder predecir cuál será su LTV completo. Esto es posible porque el comportamiento inicial suele estar muy correlacionado con el comportamiento a largo plazo. Los usuarios de un servicio que vuelven con frecuencia en sus primeros días o semanas de uso tienden a ser los más propensos a quedarse a largo plazo. Los clientes que compran por segunda o tercera vez al principio tienen más probabilidades de seguir comprando durante más tiempo. Los suscriptores que renuevan después del primer mes o año suelen permanecer durante muchos meses o años.

En esta sección, hablaré principalmente de las actividades generadoras de ingresos de los clientes, pero este análisis también puede aplicarse a situaciones en las que los clientes o las entidades incurren en costes, como a través de las devoluciones de productos, las interacciones de asistencia o el uso de servicios sanitarios.

Con los cálculos acumulativos, nos preocupa menos si una entidad realizó una acción en una fecha concreta y más el total a partir de una fecha concreta. Los cálculos acumulativos utilizados en este tipo de análisis suelen ser counts o sums. Utilizaremos de nuevo el concepto de caja de tiempo para garantizar comparaciones entre cohortes. Veamos el número de legislaturas iniciadas en los 10 años siguientes a la primera term_start, cohortando a los legisladores por siglo y tipo de primera legislatura:

SELECT date_part('century',a.first_term) as century
,first_type
,count(distinct a.id_bioguide) as cohort
,count(b.term_start) as terms
FROM
(
    SELECT distinct id_bioguide
    ,first_value(term_type) over (partition by id_bioguide 
                                  order by term_start) as first_type
    ,min(term_start) over (partition by id_bioguide) as first_term
    ,min(term_start) over (partition by id_bioguide) 
     + interval '10 years' as first_plus_10
    FROM legislators_terms
) a
LEFT JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
and b.term_start between a.first_term and a.first_plus_10
GROUP BY 1,2
;

century  first_type  cohort  terms
-------  ----------  ------  -----
18       rep         297     760
18       sen         71      101
19       rep         5744    12165
19       sen         555     795
20       rep         4473    16203
20       sen         618     1008
21       rep         683     2203
21       sen         77      118

La cohorte más numerosa es la de los representantes elegidos por primera vez en el siglo XIX, pero la cohorte con mayor número de mandatos iniciados en un plazo de 10 años es la de los representantes elegidos por primera vez en el siglo XX. Este tipo de cálculo puede ser útil para comprender la contribución global de una cohorte a una organización. El total de ventas o el total de compras repetidas pueden ser métricas valiosas. Pero normalmente queremos normalizar para comprender la contribución por entidad. Los cálculos que podríamos querer hacer incluyen las acciones medias por persona, el valor medio de pedido (VAM), los artículos por pedido y los pedidos por cliente. Para normalizar por el tamaño de la cohorte, basta con dividir por la cohorte inicial, lo que hemos hecho anteriormente con la retención, la supervivencia y el retorno. Aquí lo hacemos y también pivotamos los resultados en forma de tabla para facilitar las comparaciones:

SELECT century
,max(case when first_type = 'rep' then cohort end) as rep_cohort
,max(case when first_type = 'rep' then terms_per_leg end) 
 as avg_rep_terms
,max(case when first_type = 'sen' then cohort end) as sen_cohort
,max(case when first_type = 'sen' then terms_per_leg end) 
 as avg_sen_terms
FROM
(
    SELECT date_part('century',a.first_term) as century
    ,first_type
    ,count(distinct a.id_bioguide) as cohort
    ,count(b.term_start) as terms
    ,count(b.term_start) 
     / count(distinct a.id_bioguide) as terms_per_leg
    FROM
    (
        SELECT distinct id_bioguide
        ,first_value(term_type) over (partition by id_bioguide 
                                      order by term_start
                                      ) as first_type
        ,min(term_start) over (partition by id_bioguide) as first_term
        ,min(term_start) over (partition by id_bioguide) 
         + interval '10 years' as first_plus_10
        FROM legislators_terms
    ) a
    LEFT JOIN legislators_terms b on a.id_bioguide = b.id_bioguide 
    and b.term_start between a.first_term and a.first_plus_10
    GROUP BY 1,2
) aa
GROUP BY 1
;

century  rep_cohort  avg_rep_terms  sen_cohort  avg_sen_terms
-------  ----------  -------------  ----------  -------------
18       297         2.6            71          1.4
19       5744        2.1            555         1.4
20       4473        3.6            618         1.6
21       683         3.2            77          1.5

Con los mandatos acumulados normalizados por el tamaño de la cohorte, ahora podemos confirmar que los representantes elegidos por primera vez en el siglo XX tuvieron el mayor número medio de mandatos, mientras que los que empezaron en el siglo XIX tuvieron el menor número medio de mandatos. Los senadores tienen menos mandatos pero más largos que sus homólogos representantes, y de nuevo los que empezaron en el siglo XX han tenido el mayor número de mandatos de media.

Los cálculos acumulativos se utilizan a menudo en los cálculos del valor de vida del cliente. El LTV suele calcularse utilizando medidas monetarias, como el total de dólares gastados por un cliente, o el margen bruto (ingresos menos costes) generado por un cliente a lo largo de su vida. Para facilitar las comparaciones entre cohortes, a menudo se elige el "tiempo de vida" para reflejar el tiempo medio de vida del cliente, o periodos que resulten cómodos de analizar, como 3, 5 ó 10 años. El conjunto de datos de los legisladores no contiene métricas financieras, pero cambiar los valores en dólares en cualquiera de los códigos SQL anteriores sería sencillo. Afortunadamente, SQL es un lenguaje lo suficientemente flexible como para que podamos adaptar estas plantillas para abordar una amplia variedad de cuestiones analíticas.

El análisis de cohortes incluye un conjunto de técnicas que pueden utilizarse para responder a preguntas relacionadas con el comportamiento a lo largo del tiempo y cómo diversos atributos pueden contribuir a las diferencias entre grupos. La supervivencia, el retorno y los cálculos acumulativos arrojan luz sobre estas cuestiones. Con una buena comprensión de cómo se comportan las cohortes, a menudo tenemos que volver nuestra atención a la composición o mezcla de cohortes a lo largo del tiempo, comprendiendo cómo eso puede influir en la retención total, la supervivencia, el retorno o los valores acumulativos de forma que estas medidas difieran sorprendentemente de las cohortes individuales.

Análisis transversal, a través de una lente de cohorte

Hasta ahora, en este capítulo, hemos examinado el análisis de cohortes. Hemos seguido el comportamiento de las cohortes a lo largo del tiempo con análisis de retención, supervivencia, retorno y comportamiento acumulativo. Sin embargo, uno de los retos de estos análisis es que, aunque facilitan la detección de cambios dentro de las cohortes, puede resultar difícil detectar cambios en la composición general de una base de clientes o usuarios.

También pueden producirsecambios en la mezcla, que son cambios en la composición de la base de clientes o usuarios a lo largo del tiempo, que hacen que las cohortes posteriores sean diferentes de las anteriores. Los cambios de mezcla pueden deberse a la expansión internacional, al cambio entre estrategias de adquisición orgánica y de pago, o al paso de un público entusiasta de nicho a otro más amplio de mercado de masas. Crear cohortes adicionales, o segmentos, a lo largo de cualquiera de estas líneas sospechosas puede ayudar a diagnosticar si se está produciendo un cambio de mezcla.

El análisis de cohortes puede contrastarse con el análisis transversal, que compara individuos o grupos en un único momento. Los estudios transversales pueden correlacionar los años de educación con los ingresos actuales, por ejemplo. En el lado positivo, la recopilación de conjuntos de datos para el análisis transversal suele ser más fácil, ya que no se necesitan series temporales. El análisis transversal puede ser esclarecedor y generar hipótesis para investigaciones posteriores. En el lado negativo, suele existir una forma de sesgo de selección denominada sesgo de supervivencia, que puede llevar a conclusiones falsas.

Sesgo de supervivencia

"Observemos a nuestros mejores clientes y veamos qué tienen en común". Esta idea aparentemente inocente y bienintencionada puede llevar a conclusiones muy problemáticas. El sesgo de supervivencia es el error lógico de centrarse en las personas o cosas que superaron algún proceso de selección, ignorando las que no lo hicieron. Normalmente esto se debe a que las entidades ya no existen en el conjunto de datos en el momento de la selección, porque han fracasado, se han batido en retirada o han abandonado la población por algún otro motivo. Concentrarse sólo en la población restante puede llevar a conclusiones demasiado optimistas, porque se ignoran los fracasos.

Se ha escrito mucho sobre algunas personas que abandonaron la universidad y crearon empresas tecnológicas de gran éxito. Esto no significa que debas abandonar inmediatamente la universidad, ya que la gran mayoría de las personas que la abandonan no llegan a ser directores ejecutivos de éxito. Esa parte de la población no ocupa titulares tan sensacionalistas, por lo que es fácil olvidarse de esa realidad.

En el contexto de los clientes exitosos, el sesgo de supervivencia podría manifestarse como una observación de que los mejores clientes suelen vivir en California o Texas y suelen tener entre 18 y 30 años. Se trata de una población grande para empezar, y puede resultar que estas características sean compartidas por muchos clientes que abandonaron antes de la fecha de análisis. Volver a la población original podría revelar que otros grupos demográficos, como los de 41 a 50 años de Vermont, se quedan y gastan más con el tiempo, aunque sean menos en términos absolutos. El análisis de cohortes ayuda a distinguir y reducir el sesgo de supervivencia.

El análisis de cohortes es una forma de superar el sesgo de supervivencia incluyendo en el análisis a todos los miembros de una cohorte inicial. Podemos tomar una serie de secciones transversales de un análisis de cohortes para comprender cómo puede haber cambiado la mezcla de entidades a lo largo del tiempo. En una fecha determinada, hay usuarios de diversas cohortes. Podemos utilizar el análisis transversal para examinarlos, como capas de sedimento, y revelar nuevas perspectivas. En el siguiente ejemplo, crearemos una serie temporal de la proporción de legisladores de cada cohorte para cada año del conjunto de datos.

El primer paso es hallar el número de legisladores en funciones cada año uniendola tabla legislators a la date_dim, DONDE date de date_dim está entre las fechas de inicio y fin de cada legislatura. Aquí utilizamos el 31 de diciembre de cada año para encontrar los legisladores en funciones al final de cada año:

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
...         ...

A continuación, añadimos los criterios de cohorte del siglo mediante un JOINa una subconsulta con el first_term calculado:

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
...         ...      ...

Por último, calculamos el porcentaje del total de legislators en cada año que representa la cohorte del siglo. Esto puede hacerse de un par de maneras, dependiendo de la forma de salida deseada. La primera forma es mantener una fila para cada combinación de date y century y utilizar una función de ventana sum en el denominador del cálculo del porcentaje:

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
...         ...      ...          ...     ...

El segundo enfoque da como resultado una fila por año, con una columna para cada siglo, un formato de tabla que puede ser más fácil de explorar en busca de tendencias:

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
...         ...     ...     ...     ...

Podemos representar gráficamente el resultado, como en la Figura 4-13, para ver cómo las nuevas cohortes de legisladores superan gradualmente a las antiguas, hasta que ellas mismas son sustituidas por nuevas cohortes.

En lugar de cohortar en first_term, podemos cohortar en función de la antigüedad. Encontrar la proporción de clientes que son relativamente nuevos, que tienen una antigüedad media o que son clientes de larga duración en distintos momentos puede ser revelador. Veamos cómo ha cambiado la permanencia de los legisladores en el Congreso a lo largo del tiempo.

El primer paso es calcular, para cada año, el número acumulado de años en el cargo de cada legislador. Como puede haber lagunas entre legislaturas cuando los legisladores son expulsados o dejan el cargo por otras razones, primero encontraremos cada año en el que el legislador estaba en el cargo al final del año, en la subconsulta. Luego utilizaremos una función de ventana count, con la ventana cubriendo las filas unbounded preceding, o todas las filas anteriores de ese legislador, y current row:

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
...          ...         ...

A continuación, count el número de legisladores de cada combinación de date y cume_years para crear una distribución:

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
...          ...         ...

Antes de calcular el porcentaje de cada titularidad por año y ajustar el formato de presentación, podríamos considerar la posibilidad de agrupar las titularidades. Un rápido perfil de nuestros resultados hasta ahora revela que en algunos años están representadas casi 40 tenencias diferentes. Es probable que esto resulte difícil de visualizar e interpretar:

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
...          ...

En consecuencia, es posible que queramos agrupar los valores. No hay una única forma correcta de agrupar tenencias. Si existen definiciones organizativas de grupos de tenencias, utilízalas. De lo contrario, suelo intentar dividirlos en tres o cinco grupos de aproximadamente el mismo tamaño. Aquí agruparemos las permanencias en cuatro cohortes, donde cume_years es igual o inferior a 4 años, entre 5 y 10 años, entre 11 y 20 años, e igual o superior a 21 años:

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
...         ...       ...

El gráfico de resultados de la Figura 4-14 muestra que en los primeros años del país, la mayoría de los legisladores tenían muy pocos años de mandato. En años más recientes, la proporción de legisladores con 21 o más años en el cargo ha ido en aumento. También hay interesantes aumentos periódicos de legisladores con una permanencia de 1 a 4 años, que pueden reflejar cambios en las tendencias políticas.

Una sección transversal de una población en un momento dado está formada por miembros de múltiples cohortes. Crear una serie temporal de estas secciones transversales es otra forma interesante de analizar las tendencias. Combinando esto con las percepciones de la retención se puede obtener una imagen más sólida de las tendencias en cualquier organización.

Conclusión

El análisis de cohortes es una forma útil de investigar cómo cambian los grupos a lo largo del tiempo, ya sea desde la perspectiva de la retención, la repetición de conductas o la acumulación de acciones. El análisis de cohortes es retrospectivo, y mira hacia atrás en las poblaciones utilizando atributos intrínsecos o derivados del comportamiento. Mediante este tipo de análisis pueden encontrarse correlaciones interesantes y, con suerte, útiles. Sin embargo, como dice el refrán, correlación no implica causalidad. Para determinar la causalidad real, los experimentos aleatorios son la regla de oro. El Capítulo 7 profundizará en el análisis de experimentos.

Sin embargo, antes de pasar a la experimentación, tenemos que tratar otros tipos de análisis. A continuación trataremos el análisis de texto: los componentes del análisis de texto suelen aparecer en otros análisis, y es una faceta interesante del análisis en sí misma.

Get SQL para análisis de datos 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

SQL para análisis de datos by Cathy Tanimura

Capítulo 4. Análisis de cohortes

Cohortes: Un marco de análisis útil

¿Cohorte o segmento?

El conjunto de datos de los legisladores

Figura 4-1. Muestra de la tabla `legislators`

Figura 4-2. Muestra de la tabla `legislators_terms`

Retención

SQL para una curva de retención básica

Consejo

Figura 4-3. Retención desde el inicio del primer mandato de los legisladores de EE.UU.

Ajustar las series temporales para aumentar la precisión de la retención

Consejo

Figura 4-4. Retención de legisladores tras ajustar por años reales en el cargo

Cohortes derivadas de la propia serie temporal

Figura 4-5. Retención de legisladores por siglo de inicio del primer mandato

Advertencia

Figura 4-6. Retención de legisladores por primer estado: los cinco primeros estados por número total de legisladores

Definir la cohorte a partir de una tabla separada

Figura 4-7. Retención de legisladores por sexo

Figura 4-8. Retención de legisladores por sexo: cohortes de 1917 a 1999

Tratar con cohortes dispersas

Figura 4-9. Retención de legisladores por sexo y primer estado

Definir cohortes a partir de fechas distintas de la primera cita

Figura 4-10. Retención por tipo de mandato de los legisladores en funciones durante el año 2000

Análisis de cohortes relacionados

Supervivencia

Figura 4-11. Supervivencia de los legisladores: proporción de la cohorte que permaneció en el cargo durante tantos mandatos o más

Comportamiento de devolución o repetición de compra

Figura 4-12. Tendencia de la proporción de representantes de cada cohorte, definida por la década inicial, que más tarde se convirtieron en senadores

Cálculos acumulativos

Análisis transversal, a través de una lente de cohorte

Figura 4-13. Porcentaje de legisladores cada año, por siglo elegido por primera vez

Figura 4-14. Porcentaje de legisladores por número de años en el cargo

Conclusión

Don’t leave empty-handed

It’s yours, free.

Check it out now on O’Reilly

Capítulo 4. Análisis de cohortes

Cohortes: Un marco de análisis útil

¿Cohorte o segmento?

El conjunto de datos de los legisladores

Figura 4-1. Muestra de la tabla legislators

Figura 4-2. Muestra de la tabla legislators_terms

Retención

SQL para una curva de retención básica

Consejo

Figura 4-3. Retención desde el inicio del primer mandato de los legisladores de EE.UU.

Ajustar las series temporales para aumentar la precisión de la retención

Consejo

Figura 4-4. Retención de legisladores tras ajustar por años reales en el cargo

Cohortes derivadas de la propia serie temporal

Figura 4-5. Retención de legisladores por siglo de inicio del primer mandato

Advertencia

Figura 4-6. Retención de legisladores por primer estado: los cinco primeros estados por número total de legisladores

Definir la cohorte a partir de una tabla separada

Figura 4-7. Retención de legisladores por sexo

Figura 4-8. Retención de legisladores por sexo: cohortes de 1917 a 1999

Tratar con cohortes dispersas

Figura 4-9. Retención de legisladores por sexo y primer estado

Definir cohortes a partir de fechas distintas de la primera cita

Figura 4-10. Retención por tipo de mandato de los legisladores en funciones durante el año 2000

Análisis de cohortes relacionados

Supervivencia

Figura 4-11. Supervivencia de los legisladores: proporción de la cohorte que permaneció en el cargo durante tantos mandatos o más

Comportamiento de devolución o repetición de compra

Figura 4-12. Tendencia de la proporción de representantes de cada cohorte, definida por la década inicial, que más tarde se convirtieron en senadores

Cálculos acumulativos

Análisis transversal, a través de una lente de cohorte

Figura 4-13. Porcentaje de legisladores cada año, por siglo elegido por primera vez

Figura 4-14. Porcentaje de legisladores por número de años en el cargo

Conclusión

Don’t leave empty-handed

It’s yours, free.

Check it out now on O’Reilly

Figura 4-1. Muestra de la tabla `legislators`

Figura 4-2. Muestra de la tabla `legislators_terms`