Na minha comunidade, preciso segmentar minhas pessoas com base em:
Como posso fazer isso usando o explorador de dados?
Gostaria de ter uma consulta onde eu coloque esses parâmetros e ela liste as pessoas, para que eu possa adicioná-las manualmente a um grupo. Muito fácil.
Alguma dica? Por onde posso começar?
Eu acho que algo assim poderia funcionar:
-- [params]
-- int :likes_received
-- int :posts_read
SELECT
us.user_id,
us.likes_received,
us.posts_read_count
FROM user_stats us
JOIN users u on u.id = us.user_id
WHERE u.last_posted_at > CURRENT_DATE - INTERVAL '1 YEAR'
AND us.likes_received >= :likes_received
AND us.posts_read_count >= :posts_read
ORDER BY 2 DESC, 3 DESC
Isso é ótimo!
Como posso encontrar se a postagem foi feita pelo menos 10 vezes no último ano?
Não apenas uma como na sua consulta
Como posso integrar esta consulta? Posts created for period
Só para confirmar, você está procurando por Curtidas e Posts Lidos de todos os tempos ou essas contagens também são referentes ao último ano?
Estes parecem suspeitosamente com os grupos de Nível de Confiança existentes (e a população deles é automatizada por medidas semelhantes) - por que você não altera os limites existentes e deixa tudo feito para você?
/admin/site_settings/category/trust
por exemplo, para TL2 (membros estão em trust_level_2 ou equivalente em seu dialeto):
O script de automação agora adicionará pessoas a um grupo se elas receberem um distintivo. Se você puder usar SQL personalizado para distintivos, poderá automatizá-lo, mas parece ser sobre níveis de confiança.
Consigo ver as vantagens de criar grupos personalizados. Por exemplo, apenas o TL3 depende do engajamento mínimo ao longo do tempo. Portanto, algo assim também poderia remover pessoas de cada grupo personalizado se o engajamento delas diminuir ao longo do ano.
Eles também não estariam vinculados às habilidades padrão e poderiam aproveitar recursos habilitados para grupos ou categorias premium específicas.
No entanto, não sei qual é a configuração específica para estes, então isso pode ser alcançável através dos níveis de confiança.
Todos os tempos para curtidas e posts lidos (o primeiro é para focar em boas contribuições, não apenas em posts, o segundo é para equilibrar)
O mínimo de posts é apenas do ano passado, é um parâmetro para entender se os membros ainda estão consistentemente ativos.
Poderia ser uma boa maneira, mas no meu caso eu deveria modificar pesadamente TL1, TL2 e TL3 e preciso levar em conta as limitações abaixo
Desculpe, não entendi, devo usar um distintivo?
Uhm, como posso modificar a consulta acima para inseri-la em um distintivo?
Nesse caso, acho que algo como isto poderia fornecer a consulta manual:
-- [params]
-- int :likes_received
-- int :posts_read
WITH user_activity AS (
SELECT
p.user_id,
COUNT(p.id) as posts_count
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
GROUP BY 1
)
SELECT
us.user_id,
us.likes_received,
us.posts_read_count,
ua.posts_count
FROM user_stats us
JOIN user_activity ua ON UA.user_id = us.user_id
WHERE us.likes_received >= :likes_received
AND us.posts_read_count >= :posts_read
AND ua.posts_count >= 10
ORDER BY 2 DESC, 3 DESC, 4 DESC
E ajustá-lo/reduzi-lo apenas para nomes de usuário forneceria uma lista que você poderia copiar e colar na caixa ‘Adicionar Usuários’ na página do(s) grupo(s) se você exportasse os resultados como um csv (e o abrisse em algo como o notepad, por exemplo):
-- [params]
-- int :likes_received
-- int :posts_read
WITH user_activity AS (
SELECT
p.user_id,
COUNT(p.id) as posts_count
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
GROUP BY 1
)
SELECT
u.username
FROM user_stats us
JOIN user_activity ua ON UA.user_id = us.user_id
JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
AND us.posts_read_count >= :posts_read
AND ua.posts_count >= 10
ORDER BY 1
Isso também é possível. 
Você precisaria de um distintivo (e uma consulta de distintivo) para cada grupo, e uma automação correspondente usando o script ‘User Group Membership through Badge’. Você também poderia automatizar os distintivos em vez de concedê-los manualmente, habilitando os Custom Triggered Badges (Enable Badge SQL e Creating triggered custom badge queries)
Há muitas partes móveis, então você pode querer mantê-lo simples nesta fase.
Isso é incrível! Muito obrigado, Jammy.
Sem problemas. 
Espero que com o primeiro você possa verificar se está obtendo os resultados esperados, e o segundo deve facilitar a adição deles a um grupo. 
Me avise se algo precisar ser ajustado.
Eu os mesclei e melhorei (com minhas pobres habilidades de SQL), se precisar de nomes de usuário, basta baixar o CSV e copiar/colar a coluna de nome de usuário.
Adicionei likes_received_max para que eu possa dividir grupos, excluindo o grupo acima.
Por exemplo:
primeiros_passos: 5 curtidas (<30), 500 posts lidos, >5 posts no último ano,
iniciantes: 30 curtidas (<100), 1000 posts lidos, >10 posts no último ano
padawan: 100 curtidas, 2000 posts lidos, >10 posts no último ano
herói: 200 curtidas, 5000 posts lidos, >10 posts no último ano
-- [params]
-- int :likes_received
-- int :posts_read
-- int :likes_received_max
-- int :posts_count
WITH user_activity AS (
SELECT
p.user_id,
COUNT(p.id) as posts_count
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
GROUP BY 1
)
SELECT
us.user_id,
u.username,
us.likes_received,
us.posts_read_count,
ua.posts_count,
u.title
FROM user_stats us
JOIN user_activity ua ON UA.user_id = us.user_id
JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
AND us.posts_read_count >= :posts_read
AND ua.posts_count >= :posts_count
AND us.likes_received < :likes_received_max
ORDER BY 2 ASC, 3 ASC, 4 ASC
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.