La tasa de retención se calcula contando la cantidad de usuarios que regresan regularmente, como cada semana o mes, y agrupándolos por la semana en que se registraron.
Supongamos que el usuario, que es estudiante de la escuela X, necesita iniciar sesión en el portal todos los días para acceder a los materiales de estudio que los maestros presentan y los detalles de cada usuario, como la hora de inicio de sesión, la identificación del usuario y la fecha, se actualizan en el servidor del portal cada hora en que inician sesión.
Ahora tenemos una tabla que almacena user_id, login_time y la fecha de la visita de cada usuario.
Paso 1: Cree un inicio de sesión de tabla que tenga 3 columnas ‘login_date, user_id, id’ y use la palabra clave IDENTITY para realizar un incremento automático en ‘id’, que se establece como clave principal, define que la columna se incrementará automáticamente en 1 cada vez que un nuevo registro se inserta.
Consulta:
CREATE TABLE login(login_date DATE, user_id INT, id INT not null IDENTITY, PRIMARY KEY(id));
Paso 2: inserte valores para cada fila en la tabla de inicio de sesión utilizando la consulta SQL,
Consulta:
INSERT INTO login(login_date,user_id)
VALUES('2022-01-01',10),('2022-01-02',12),('2022-01-03',15),
('2022-01-04',11),('2022-01-05',13),('2022-01-06',9),
('2022-01-07',21),('2022-01-08',10),('2022-01-09',10),
('2022-01-10',2),('2022-01-11',16),('2022-01-12',12),
('2022-01-13',10),('2022-01-14',18),('2022-01-15',15),
('2022-01-16',12),('2022-01-17',10),('2022-01-18',18),
('2022-01-19',14),('2022-01-20',16),('2022-01-21',12),
('2022-01-22',21),('2022-01-23',13),('2022-01-24',15),
('2022-01-25',20),('2022-01-26',14),('2022-01-27',16),
('2022-01-28',15),('2022-01-29',10),('2022-01-30',18);
Paso 3: vea la tabla de inicio de sesión después de insertar valores en las filas de cada columna.
Consulta:
SELECT * FROM login;
Producción:
Para calcular la tasa de retención, agruparemos cada visita por una semana de inicio de sesión. Microsoft SQL Server tiene una función DATEPART(), que devuelve una parte específica (año, trimestre, mes, semana, hora, minuto, etc.) de una fecha específica.
DATEPART() tiene dos argumentos que devuelven datos de la semana,
- semana
- fecha_de_inicio de sesión
Consulta:
SELECT user_id,DATEPART(week, login_date) AS login_week FROM login GROUP BY user_id, DATEPART(week, login_date);
Producción:
Ahora, calcularemos la primera semana de inicio de sesión para cada usuario usando la función MIN y GROUP BY para devolver la primera semana de inicio de sesión de cada usuario.
Consulta:
SELECT user_id, min(DATEPART (week, login_date)) AS first FROM login GROUP BY user_id;
Producción:
Usando el grupo INNER JOIN, los resultados ‘login_week y first’ para calcular la tasa de retención.
Consulta:
Select m.user_id,m.login_week,n.first as first from (SELECT user_id,DATEPART(week,login_date) AS login_week FROM login GROUP BY user_id,DATEPART(week,login_date)) m, (SELECT user_id,min(DATEPART(week,login_date)) AS first FROM login GROUP BY user_id) n where m.user_id = n.user_id;
Producción:
Aquí obtendremos la diferencia entre login_week y first para calcular el número de la semana (week number).
Consulta:
Select m.user_id,m.login_week,n.first as first, m.login_week-first as week_number from (SELECT user_id, DATEPART(week,login_date) AS login_week FROM login GROUP BY user_id, DATEPART(week,login_date)) m, (SELECT user_id, min (DATEPART(week,login_date)) AS first FROM login GROUP BY user_id) n where m.user_id=n.user_id;
Producción:
Después de esto, combine el resultado en una tabla de cohortes que tiene una fila para cada primera semana y una columna para cada número de semana que tiene una cantidad de usuarios retenidos después de ‘n’ semanas para iniciar sesión en el portal. Utilice la siguiente consulta para calcular la tasa de retención en SQL. Para el análisis de tablas de cohortes, utilice la herramienta de visualización de datos.
Consulta:
Select first, SUM(CASE WHEN week_number = 0 THEN 1 ELSE 0 END) AS week_0, SUM(CASE WHEN week_number = 1 THEN 1 ELSE 0 END) AS week_1, SUM(CASE WHEN week_number = 2 THEN 1 ELSE 0 END) AS week_2, SUM(CASE WHEN week_number = 3 THEN 1 ELSE 0 END) AS week_3, SUM(CASE WHEN week_number = 4 THEN 1 ELSE 0 END) AS week_4, SUM(CASE WHEN week_number = 5 THEN 1 ELSE 0 END) AS week_5, SUM(CASE WHEN week_number = 6 THEN 1 ELSE 0 END) AS week_6, SUM(CASE WHEN week_number = 7 THEN 1 ELSE 0 END) AS week_7, SUM(CASE WHEN week_number = 8 THEN 1 ELSE 0 END) AS week_8, SUM(CASE WHEN week_number = 9 THEN 1 ELSE 0 END) AS week_9 from (select m.user_id,m.login_week,n.first as first, m.login_week-first as week_number from (SELECT user_id, DATEPART(week,login_date) AS login_week FROM login GROUP BY user_id,DATEPART(week,login_date)) m,(SELECT user_id, min(DATEPART(week,login_date)) AS first FROM login GROUP BY user_id) n where m.user_id = n.user_id) as with_week_number group by first order by first;
Producción:
