SQL Server e Power BI – Como analisar e gerar estatísticas de conversas de grupos do Whatsapp

Visualizações: 2.614
Tempo de Leitura: 11 minutos

Fala pessoal!

Nesse blog post, eu gostaria de compartilhar com vocês uma solução que permite analisar e gerar estatísticas de conversas de grupos do Whatsapp utilizando SQL Server e Power BI. Diferente do Telegram, que possui uma API para integração e bots para automatização de tarefas, o Whatsapp é totalmente atrasado fechado e não permite qualquer tipo de integração legal na plataforma, o que torna a tarefa de administrar grupos muito mais onerosa para os administradores, especialmente em grupos grandes.

Não seria legal ter algumas estatísticas de quem são as pessoas que mais falam em um grupo, quais os dias e horários que as pessoas mais interagem e quem são os usuários que nunca falam nada ? Enquanto no Telegram existem bots (Combot, por exemplo) que fazem isso pra você, no Whatsapp não tem como gerar esse tipo de estatística. Quer dizer, não tinha!

Como exportar as conversas do grupo

Como não existe API de integração no Whatsapp, não tem como você acessar as conversas diretamente pela plataforma do Whatsapp. Entretanto, uma forma de se conseguir acessar os dados das conversas é exportando as conversas pelo seu celular (Whatsapp Web não possui esse recurso) e salvando no seu servidor.

Uma forma prática de se fazer isso, é salvando no seu Google Drive/Dropbox/E-mail pelo celular e baixando o arquivo no seu servidor.

Como importar as conversas do grupo

Visualizar conteúdo
Uma vez que você tenha gravado as conversas e baixado no servidor, agora precisamos importar o arquivo para o banco de dados.

Ao abrir o arquivo zip das conversas, verá que existem vários arquivos de contato (.vcf) e terá um arquivo .txt, que é o que estamos querendo importar:

Ao abrir o arquivo, vocês poderão visualizar que o encoding do mesmo é o UTF-8 (Codepage 65001) e o formato de quebra de linha é o Unix (Linefeed ou \n). Isso será muito importante para importar o arquivo corretamente.

Agora precisamos importar esse arquivo para o banco de dados. Escolha o método que melhor te atenda e passe para o próximo passo.

BULK INSERT (Não identificou a quebra de linha)

Resultado:

OPENROWSET (Não identificou a quebra de linha e nem a codificação)

Resultado:

BCP com xp_cmdshell (não identificou quebra de linha e nem a codificação)

Resultado:

OLE Automation (funcionou, mas não lê arquivos UTF-8)

Para visualizar o código-fonte da função fncLer_Arquivo_FSO, acesse o post SQL Server – Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET).

Resultado:

OPENROWSET com ACE OLEDB (funcionou)

Uma das opções onde realmente é possível importar o arquivo corretamente, inclusive, na codificação UTF-8, é utilizando o OPENROWSET e os drivers ACE OLEDB. Caso você não tenha os drivers ACE OLEDB instalados no seu servidor, instale-os utilizando o tutorial do post SQL Server – Como instalar os drivers Microsoft.ACE.OLEDB.12.0 e Microsoft.Jet.OLEDB.4.0.

Resultado:

SQLCLR (funcionou)

E por último, a solução que pra mim, é a melhor das opções, o SQLCLR permite importar arquivos de texto facilmente utilizando a Stored Procedure demonstrada abaixo:

Resultado:

Caso você queira implementar essa SP no seu banco de dados, basta utilizar o script abaixo:

Lembrando que, como essa SP exige acesso ao filesystem, ela deve ser criada utilizando a permissão EXTERNAL_ACCESS. Isso exige que o banco de dados tenha a propriedade TRUSTWORTHY setada para ON antes de criar o assembly e a Stored Procedure:

Para saber mais sobre o SQLCLR, não deixe de ler o artigo .

Como tratar os dados no SQL Server

Visualizar conteúdo
Para o tratamento dos dados, vou utilizar a Stored Procedure stpImporta_Txt_Encoding, utilizando OLE Automation, para importar os dados, conforme demonstrei no tópico anterior, e também a função fncSplitTexto, que compartilhei no post Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server.

Em resumo, fiz as seguintes ações para tratar as informações:

  • Gera a tabela que irá armazenar os dados
  • Importa o arquivo
  • Substitui a string “você” pelo seu usuário
  • Remove caractere “?”
  • Formata a data do evento
  • Remove linhas sem data formatada (provavelmente textos quebrados em várias linhas)
  • Remove a data do texto (já possui coluna específica para isso)
  • Identifica o usuário que enviou as mensagens comuns
  • Identifica o usuário que saiu do grupo
  • Identifica os usuários que foram removidos do grupo
  • Identifica os usuários que entraram no grupo através de link de convite
  • Identifica os usuários que foram adicionados ao grupo por outro usuário
  • Identifica as mensagens comuns
  • Cria as tabelas para usar no Power BI (Usuarios_no_Grupo, Usuarios_Nunca_Falaram, Palavras_Mais_Faladas e Palavras_Mais_Faladas_Por_Usuario)
  • Ignora algumas expressões comuns da língua portuguesa nas tabelas de palavras mais faladas

Código-fonte:

Como visualizar os dados no Power BI

Uma vez que já tratamos os dados no banco de dados (o que eu acho a forma mais correta e profissional), no Power BI teremos o trabalho apenas de exibir e visualizar os dados, que é o real objetivo da ferramenta (não é ferramenta de ETL).

Inicialmente, vamos carregar os dados do banco para o Power BI:

Uma vez que os dados foram importados (prefira o método Import ao invés do DirectQuery), vou precisar criar 2 novas colunas no Power BI, que poderia ter criado no banco de dados, mas quis mostrar como fazer isso no Power BI:

Coluna Horário:
DAX utilizado: Horário = HOUR(Conversas_Whatsapp[Dt_Evento])

Coluna Dia_Semana:
DAX utilizado: Dia_Semana = FORMAT(WEEKDAY(Conversas_Whatsapp[Dt_Evento]; 3); “dddd”)

Como vocês devem ter percebido, como estou utilizando a versão em inglês do Power BI Desktop, as datas ficam no formato inglês também. Para corrigir isso, utilize a versão em português do Power BI Desktop ou utilize suas próprias tabelas de data ou altere a localidade direto no arquivo PBIX.

Agora é a hora de montar os gráficos e visões no relatório:

E esse é o resultado final:

Bom pessoal, espero que tenham gostado desse artigo. Vou começar a postar mais conteúdo sobre BI e espero que estejam gostando dos últimos artigos.

Um abraço e até o próximo post.