SQL Server and Power BI - How to Analyze and Generate Statistics from WhatsApp Group Conversations

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.

Passo 1

Passo 2

Passo 3

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)

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Linha VARCHAR(MAX)
)

BULK INSERT #Teste
FROM 'C:\Temporario\Conversas.txt'
WITH(
    ROWTERMINATOR = '\n', -- Unix
    CODEPAGE = '65001' -- UTF-8
)

SELECT * FROM #Teste

Resultado:

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

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Linha VARCHAR(MAX)
)

INSERT INTO #Teste
SELECT *
FROM OPENROWSET(BULK 'C:\Temporario\Conversas.txt', CODEPAGE='65001', SINGLE_CLOB) AS Arquivo(linhas)

SELECT * FROM #Teste

Resultado:

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

IF (OBJECT_ID('dirceuresende.dbo.Teste') IS NOT NULL) DROP TABLE dirceuresende.dbo.Teste
CREATE TABLE dirceuresende.dbo.Teste (
    Linha VARCHAR(MAX)
)

INSERT INTO dirceuresende.dbo.Teste
EXEC master.dbo.xp_cmdshell 'bcp dirceuresende.dbo.Teste IN "C:\Temporario\Conversas.txt" -T -Slocalhost\sql2016 -c'

SELECT * FROM dirceuresende.dbo.Teste

Resultado:

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

IF (OBJECT_ID('dirceuresende.dbo.Teste') IS NOT NULL) DROP TABLE dirceuresende.dbo.Teste
CREATE TABLE dirceuresende.dbo.Teste (
    Linha VARCHAR(MAX)
)

INSERT INTO #Teste
SELECT * FROM dbo.fncLer_Arquivo_FSO('C:\Temporario\Conversas.txt')

SELECT * FROM #Teste

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.

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Linha VARCHAR(MAX)
)

INSERT INTO #Teste
SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=C:\Temporario\;HDR=No;CharacterSet=65001',
    'SELECT [F1] FROM [Conversas.txt]'
) A

SELECT * FROM #Teste

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:

IF (OBJECT_ID('dirceuresende.dbo.Teste') IS NOT NULL) DROP TABLE dirceuresende.dbo.Teste
CREATE TABLE dirceuresende.dbo.Teste (
    Linha VARCHAR(MAX)
)

INSERT INTO #Teste
EXEC CLR.dbo.stpImporta_Txt_Encoding
    @caminho = N'C:\Temporario\Conversas.txt', -- nvarchar(max)
    @Ds_Encoding = N'utf-8' -- nvarchar(max)


SELECT * FROM #Teste

Resultado:

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

USE [dirceuresende]
GO

IF NOT EXISTS(SELECT NULL FROM sys.assemblies WHERE [name] = 'SQLCLR')
BEGIN
    CREATE ASSEMBLY [SQLCLR]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300FDFD775B0000000000000000E00022200B013000000C00000006000000000000B62A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000642A00004F000000004000006803000000000000000000000000000000000000006000000C0000002C2900001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000BC0A000000200000000C000000020000000000000000000000000000200000602E72737263000000680300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000982A0000000000004800000002000500182100001408000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3006009D00000001000011000F00281000000A0F01281000000A281100000A731200000A0A00178D16000001251672010000701F0C281300000A731400000AA2731500000A0B281600000A14FE010C082C02DE53281600000A076F1700000A002B1C000716066F1800000A6F1900000A00281600000A076F1A00000A0000066F1B00000A16FE0416FE010D092DD4281600000A6F1C00000A0000DE0B062C07066F1D00000A00DC2A0000000110000002001A007791000B000000002202281E00000A002A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000080020000237E0000EC020000B003000023537472696E6773000000009C0600001400000023555300B0060000100000002347554944000000C00600005401000023426C6F620000000000000002000001471502000900000000FA013300160000010000001B0000000200000002000000020000001E0000000F00000001000000010000000200000000007A020100000000000600820112030600EF0112030600A000E0020F00320300000600C8008D02060065018D02060046018D020600D6018D020600A2018D020600BB018D020600F5008D020600B400F30206009200F302060029018D020600100147020600670386020A00DF00BF020A00610241030600A70212000A004C00BF0206003E027F030A002800BF020A0080001C000A008B03BF020A007800BF020600B402120006005A0086020000000009000000000001000100010010005603000041000100010050200000000096002302650001000C21000000008618DA0206000300000001009F020000020017020900DA0201001100DA0206001900DA020A002900DA0210003100DA0210003900DA0210004100DA0210004900DA0210005100DA0210005900DA0210006100DA0215006900DA0210007100DA0210007900DA0210008900DA02060091000D022300A9003B0227009900DA022D00B100A5033400B100DA023800A100DA024000C1006F004700C9006E034C00D10066002300A1006B025200C90096034C00D10075025800C9003D000600D9008A0006008100DA02060020007B004E012E000B006D002E00130076002E001B0095002E0023009E002E002B00AB002E003300AB002E003B00AB002E0043009E002E004B00B1002E005300AB002E005B00AB002E006300C9002E006B00F3002E00730000011A00048000000100000000000000000000000000010000000400000000000000000000005C003400000000000400000000000000000000005C001C000000000000000053514C434C5232003C4D6F64756C653E0053797374656D2E494F0053797374656D2E446174610053716C4D65746144617461006D73636F726C69620053656E64526573756C7473456E640053716C446174615265636F72640049446973706F7361626C6500526561644C696E65006765745F506970650053716C506970650053716C44625479706500446973706F736500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C654174747269627574650053716C50726F63656475726541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650044735F456E636F64696E6700737470496D706F7274615F5478745F456E636F64696E6700476574456E636F64696E670053797374656D2E52756E74696D652E56657273696F6E696E670053716C537472696E6700536574537472696E67005065656B0053514C434C52322E646C6C0053797374656D0053797374656D2E5265666C656374696F6E0063616D696E686F0053747265616D5265616465720054657874526561646572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053746F72656450726F63656475726573004F626A6563740053656E64526573756C747353746172740053797374656D2E546578740053716C436F6E746578740053656E64526573756C7473526F77006765745F4D6178000000000011440073005F004C0069006E00680061000000296D52E3C4A8364896D45A12676954DA00042001010803200001052001011111042001010E0420010102080704124D125102020320000E05000112550E062002010E12550300000A072003010E115D0A062001011D1259040000126505200101125105200201080E0320000808B77A5C561934E08907000201114911490801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000C01000753514C434C5232000005010000000017010012436F7079726967687420C2A920203230313800002901002436626662343334372D636164302D346433372D616665642D65656234353063356538616400000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E362E310100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E362E3104010000000000000000FDFD775B00000000020000001C01000048290000480B000052534453B0EBB2DD9240B14AAA96BC6723A8AF2C01000000433A5C55736572735C646966696C5C446F63756D656E74735C56697375616C2053747564696F20323031375C50726F6A656374735C4461746162617365315C53514C434C52325C6F626A5C44656275675C53514C434C52322E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008C2A00000000000000000000A62A0000002000000000000000000000000000000000000000000000982A0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000000C03000000000000000000000C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0046C020000010053007400720069006E006700460069006C00650049006E0066006F0000004802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D0065000000000000000000380008000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0043004C00520032000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000C00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C00520032002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B007300000000000000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C00520032002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D00650000000000530051004C0043004C00520032000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B83A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = EXTERNAL_ACCESS
END
GO

IF (OBJECT_ID('dbo.stpImporta_Txt_Encoding') IS NOT NULL) DROP PROCEDURE stpImporta_Txt_Encoding
GO

CREATE PROCEDURE [dbo].[stpImporta_Txt_Encoding]
    @caminho [nvarchar](max),
    @Ds_Encoding [nvarchar](max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLCLR].[StoredProcedures].[stpImporta_Txt_Encoding]
GO

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:

USE [master]
GO

ALTER DATABASE [dirceuresende] SET TRUSTWORTHY ON
GO

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:

-----------------------------------------------------------------------------------------
-- GERA A TABELA QUE IRÁ ARMAZENAR OS DADOS
-----------------------------------------------------------------------------------------

IF (OBJECT_ID('dbo.Conversas_Whatsapp') IS NOT NULL) DROP TABLE dbo.Conversas_Whatsapp
CREATE TABLE dbo.Conversas_Whatsapp (
    Ds_Linha VARCHAR(MAX),
    Dt_Evento DATETIME,
    Ds_Situacao VARCHAR(50),
    Ds_Mensagem VARCHAR(MAX),
    Ds_Usuario_Removido VARCHAR(100)
)

-----------------------------------------------------------------------------------------
-- IMPORTA O ARQUIVO
-----------------------------------------------------------------------------------------

INSERT INTO dbo.Conversas_Whatsapp(Ds_Linha)
SELECT [F1]
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=C:\Temporario\;HDR=No;CharacterSet=65001',
    'SELECT [F1] FROM [Conversas.txt]'
) A


-----------------------------------------------------------------------------------------
-- REMOVE CARACTERE "?"
-----------------------------------------------------------------------------------------
     
UPDATE A
SET
    A.Ds_Linha = REPLACE(REPLACE(REPLACE(Ds_Linha, ' - ?', ' - '), '?:', ':'), '? saiu', ' saiu')
FROM
    dbo.Conversas_Whatsapp A

    
-----------------------------------------------------------------------------------------
-- FORMATA A DATA DO EVENTO
-----------------------------------------------------------------------------------------


UPDATE dbo.Conversas_Whatsapp
SET Dt_Evento = TRY_CONVERT(DATETIME, LEFT(Ds_Linha, 6) + '20' + SUBSTRING(Ds_Linha, 7, 8), 103)


-----------------------------------------------------------------------------------------
-- REMOVE LINHAS SEM DATA FORMATADA (PROVAVELMENTE TEXTOS QUEBRADOS EM VÁRIAS LINHAS)
-----------------------------------------------------------------------------------------


DELETE A
FROM
    dbo.Conversas_Whatsapp A
WHERE
    Dt_Evento IS NULL    



-----------------------------------------------------------------------------------------
-- REMOVE A DATA DO TEXTO (JÁ POSSUI COLUNA ESPECÍFICA PARA ISSO)
-----------------------------------------------------------------------------------------

UPDATE dbo.Conversas_Whatsapp
SET Ds_Linha = SUBSTRING(Ds_Linha, 18, LEN(Ds_Linha))


-----------------------------------------------------------------------------------------
-- IDENTIFICA O USUÁRIO QUE ENVIOU AS MENSAGENS COMUNS
-----------------------------------------------------------------------------------------

UPDATE dbo.Conversas_Whatsapp
SET Ds_Mensagem = LTRIM(RTRIM(SUBSTRING(Ds_Linha, CHARINDEX(':', Ds_Linha) + 1, LEN(Ds_Linha))))


UPDATE dbo.Conversas_Whatsapp
SET Ds_Linha = LTRIM(RTRIM(REPLACE(SUBSTRING(Ds_Linha, 1, CHARINDEX(':', Ds_Linha)), ':', '')))


-----------------------------------------------------------------------------------------
-- IDENTIFICA O USUÁRIO QUE SAIU DO GRUPO
-----------------------------------------------------------------------------------------

UPDATE dbo.Conversas_Whatsapp
SET 
    Ds_Linha = REPLACE(LEFT(Ds_Mensagem, LEN(Ds_Mensagem) - 5), '?', ''),
    Ds_Mensagem = 'saiu',
    Ds_Situacao = 'saiu',
    Ds_Usuario_Removido = REPLACE(LEFT(Ds_Mensagem, LEN(Ds_Mensagem) - 5), '?', '')
WHERE 
    Ds_Mensagem LIKE '% saiu'


-----------------------------------------------------------------------------------------
-- IDENTIFICA OS USUÁRIOS QUE FORAM REMOVIDOS DO GRUPO
-----------------------------------------------------------------------------------------

UPDATE dbo.Conversas_Whatsapp
SET 
    Ds_Linha = REPLACE(LEFT(Ds_Mensagem, CHARINDEX(' removeu ', Ds_Mensagem)), '?', ''),
    Ds_Usuario_Removido = REPLACE(SUBSTRING(Ds_Mensagem, CHARINDEX(' removeu ', Ds_Mensagem) + 9, LEN(Ds_Mensagem)), '?', ''),
    Ds_Situacao = 'saiu'
WHERE 
    Ds_Mensagem LIKE '% removeu %'


-----------------------------------------------------------------------------------------
-- IDENTIFICA OS USUÁRIOS QUE ENTRARAM NO GRUPO ATRAVÉS DE LINK DE CONVITE
-----------------------------------------------------------------------------------------

UPDATE dbo.Conversas_Whatsapp
SET 
    Ds_Linha = LTRIM(RTRIM(REPLACE(LEFT(Ds_Mensagem, CHARINDEX(' entrou usando o link de convite deste grupo', Ds_Mensagem)), '?', ''))),
    Ds_Mensagem = LTRIM(RTRIM(REPLACE(SUBSTRING(Ds_Mensagem, CHARINDEX(' entrou usando o link de convite deste grupo', Ds_Mensagem), LEN(Ds_Mensagem)), '?', ''))),
    Ds_Situacao = 'entrou'
WHERE 
    Ds_Mensagem LIKE '% entrou usando o link de convite deste grupo'
    

-----------------------------------------------------------------------------------------
-- IDENTIFICA OS USUÁRIOS QUE FORAM ADICIONADOS AO GRUPO POR OUTRO USUÁRIO
-----------------------------------------------------------------------------------------

INSERT INTO dbo.Conversas_Whatsapp
(
    Ds_Linha,
    Dt_Evento,
    Ds_Situacao,
    Ds_Mensagem,
    Ds_Usuario_Removido
)
SELECT 
    LTRIM(RTRIM(REPLACE(LEFT(A.Ds_Mensagem, CHARINDEX(' adicionou ', A.Ds_Mensagem)), '?', ''))) AS Ds_Linha,
    A.Dt_Evento,
    'entrou' AS Ds_Situacao,
    A.Ds_Mensagem,
    LTRIM(RTRIM(B.Palavra)) AS Ds_Usuario_Removido
FROM 
    dbo.Conversas_Whatsapp A
    CROSS APPLY dbo.fncSplitTexto(REPLACE(LTRIM(RTRIM(REPLACE(SUBSTRING(A.Ds_Mensagem, CHARINDEX(' adicionou ', A.Ds_Mensagem) + 11, LEN(A.Ds_Mensagem)), '?', ''))), ' e ', ','), ',') B
WHERE 
    A.Ds_Mensagem LIKE '% adicionou %' 
    AND Ds_Linha = ''

    
DELETE FROM dbo.Conversas_Whatsapp
WHERE Ds_Mensagem LIKE '% adicionou %' 
AND Ds_Linha = ''


-----------------------------------------------------------------------------------------
-- IDENTIFICA AS MENSAGENS COMUNS
-----------------------------------------------------------------------------------------

UPDATE dbo.Conversas_Whatsapp
SET Ds_Situacao = 'info'
WHERE Ds_Situacao IS NULL
AND Ds_Linha = ''


UPDATE dbo.Conversas_Whatsapp
SET Ds_Situacao = 'mensagem'
WHERE Ds_Situacao IS NULL


-----------------------------------------------------------------------------------------
-- SUBSTITUI A STRING "VOCÊ" PELO SEU USUÁRIO
-----------------------------------------------------------------------------------------
     
UPDATE A
SET
    A.Ds_Linha = REPLACE(A.Ds_Linha, 'Você', 'Dirceu Resende')
FROM
    dbo.Conversas_Whatsapp A
WHERE
    A.Ds_Situacao = 'mensagem'


UPDATE A
SET
    A.Ds_Usuario_Removido = REPLACE(A.Ds_Usuario_Removido, 'Você', 'Dirceu Resende')
FROM
    dbo.Conversas_Whatsapp A
WHERE
    A.Ds_Situacao IN ('entrou', 'saiu', 'info')


-----------------------------------------------------------------------------------------
-- CRIA AS TABELAS PARA USAR NO POWERBI
-----------------------------------------------------------------------------------------

IF (OBJECT_ID('dbo.Usuarios_No_Grupo') IS NOT NULL) DROP TABLE dbo.Usuarios_No_Grupo
SELECT DISTINCT
    A.Ds_Linha AS Ds_Usuario,
    CAST(NULL AS DATETIME) AS Dt_Entrada,
    CAST(NULL AS INT) AS Qt_Dias_Sem_Falar,
    CAST(NULL AS INT) AS Qt_Mensagens
INTO
    dbo.Usuarios_No_Grupo
FROM
    dbo.Conversas_Whatsapp A
    LEFT JOIN dbo.Conversas_Whatsapp B ON A.Ds_Linha = B.Ds_Usuario_Removido AND B.Ds_Situacao = 'saiu'
WHERE
    B.Ds_Usuario_Removido IS NULL
    AND A.Ds_Situacao IN ('entrou', 'mensagem')
    AND A.Ds_Linha IS NOT NULL

UNION

SELECT DISTINCT
    A.Ds_Usuario_Removido,
    CAST(NULL AS DATETIME) AS Dt_Entrada,
    CAST(NULL AS INT) AS Qt_Dias_Sem_Falar,
    CAST(NULL AS INT) AS Qt_Mensagens
FROM
    dbo.Conversas_Whatsapp A
    LEFT JOIN dbo.Conversas_Whatsapp B ON A.Ds_Usuario_Removido = B.Ds_Usuario_Removido AND B.Ds_Situacao = 'saiu'
WHERE
    B.Ds_Usuario_Removido IS NULL
    AND A.Ds_Situacao = 'entrou'
    AND A.Ds_Usuario_Removido IS NOT NULL
    

UPDATE A
SET
    A.Dt_Entrada = B.Dt_Evento,
    A.Qt_Dias_Sem_Falar = DATEDIFF(DAY, B.Dt_Ultimo_Evento, GETDATE())
FROM
    dbo.Usuarios_No_Grupo A
    JOIN (
        SELECT ISNULL(Ds_Usuario_Removido, Ds_Linha) AS Ds_Usuario, MIN(Dt_Evento) AS Dt_Evento, MAX(Dt_Evento) AS Dt_Ultimo_Evento
        FROM dbo.Conversas_Whatsapp
        WHERE Ds_Situacao IN ('entrou', 'mensagem')
        GROUP BY ISNULL(Ds_Usuario_Removido, Ds_Linha)
    ) B ON A.Ds_Usuario = B.Ds_Usuario



UPDATE A
SET
    A.Qt_Mensagens = ISNULL(B.Qt_Mensagens, 0)
FROM
    dbo.Usuarios_No_Grupo A
    LEFT JOIN (
        SELECT Ds_Linha AS Ds_Usuario, COUNT(*) AS Qt_Mensagens
        FROM dbo.Conversas_Whatsapp
        WHERE Ds_Situacao = 'mensagem'
        GROUP BY Ds_Linha
    ) B ON A.Ds_Usuario = B.Ds_Usuario



IF (OBJECT_ID('dbo.Usuarios_Nunca_Falaram') IS NOT NULL) DROP TABLE dbo.Usuarios_Nunca_Falaram
SELECT
    A.Ds_Usuario
INTO
    dbo.Usuarios_Nunca_Falaram
FROM
    dbo.Usuarios_No_Grupo A
    LEFT JOIN dbo.Conversas_Whatsapp B ON A.Ds_Usuario = B.Ds_Linha AND B.Ds_Situacao = 'mensagem'
WHERE
    B.Ds_Mensagem IS NULL



IF (OBJECT_ID('dbo.Palavras_Mais_Faladas') IS NOT NULL) DROP TABLE dbo.Palavras_Mais_Faladas
SELECT TOP 100
    LTRIM(RTRIM(B.Palavra)) AS Palavra,
    COUNT(*) AS Qt_Ocorrencias
INTO
    dbo.Palavras_Mais_Faladas
FROM
    dbo.Conversas_Whatsapp A
    CROSS APPLY dbo.fncSplitTexto(A.Ds_Mensagem, ' ') B
WHERE
    A.Ds_Situacao = 'mensagem'
    AND LEN(B.Palavra) >= 3
    AND B.Palavra NOT IN ( 'que', 'nao', '<Arquivo', 'mídia', 'oculto>', 'para', 'com', 'uma', 'tem', 'Mas', 'pra', 'mais', 'por', 'isso', 'Esta', 'muito', 'Vai', 'como', 'Bom', 'Boa', 'aqui', 'Esse', 'ser', 'vou', 'fazer', 'quem', 'ele', 'pode', 'dia', 'Foi', 'alguem', 'bem', 'estou', 'mesmo', 'essa', 'até', 'ter', '??????', 'tenho', '??????', 'pessoal', 'Acho', 'Sim', 'também', 'Ainda', 'sobre', 'kkk', 'galera', 'são', 'Cara', 'meu', 'Obrigado', 'agora', 'nos', 'dos', 'tudo', 'Quando', 'sem', 'PESSOAL,', 'pelo', 'Depois', 'então', 'todos', 'Dar', 'minha', 'ver', 'sempre', 'parabéns', 'Nem', 'gente', 'usar', 'algum', 'mensagem', 'pois', 'qual', 'for', 'pela', 'Sei', 'faz', 'eles', 'das' )
    AND B.Palavra NOT IN ( 'seu', 'sua', 'vamos', 'era', 'nada', 'problema', 'fiz', 'Pro', 'Show', 'Sou', 'apenas', 'seria', 'Valeu', 'assim', 'melhor', 'tipo', 'porque', 'coisa', 'fica', 'seja', 'falar', 'outro', 'quer', 'algo', 'hoje', 'Onde', 'sim,', 'usando', 'uns', 'tarde', 'será', 'vlw', 'estão', 'todo', 'top', 'deve', 'sabe', 'galera,', 'caso', 'cada', 'Fala', 'preciso', 'outra', 'tinha', 'precisa', 'pouco', 'tiver', 'fazendo', 'Nesse', 'ano', 'erro', 'deu', 'estava', 'ficar', 'qualquer', 'muita', 'noite', 'uso' , 'local', 'mundo', 'Olha', 'querendo', 'rapaz', 'Gostaria', 'foram', 'bastante', 'Excelente') 
    AND B.Palavra NOT IN ( 'alguma', 'fora', 'alguns', 'Existe', 'nome', 'Posso', 'saber', 'Estamos', 'Legal', 'quero', 'hein', 'Nosso', 'Nunca', 'achei', 'menos', 'anos', 'podem', 'forma', 'grande', 'Manda', 'usa', 'conta', 'fez', 'pessoas', 'tentar', 'entendi', 'mercado', 'verdade', '(arquivo', 'anexado)', 'desse', 'vindo', 'certo', 'entrar', 'estar', 'outros', 'parece', 'temos', 'próximo', 'entre', 'Esses', 'isso,', 'parte', 'algumas', 'daí', 'exatamente', 'favor', 'hora', 'maior', 'mandar', 'ninguém', 'realmente', 'sábado', 'tenha', 'teria', 'vem', 'vezes', 'vida', 'umas', 'desde', 'dias', 'direto', 'dois' )
    AND B.Palavra NOT IN ( 'Aos', 'nas', 'novo', 'tive', 'dele', 'deles', 'dela', 'delas', 'dessa', 'mesma', 'sendo', 'ele', 'ela', 'vez', 'final', 'quiser', 'vão', 'vários', 'díficil', 'duas', 'colocar', 'dentro', 'dizer', 'essas', 'Fui', 'Nessa', 'pegar', 'conseguir', 'dia.', 'outras', 'Poderia', 'todas', 'certeza', 'dia,', 'falando', 'muitos', 'olhada', 'pessoa', 'quase', 'questão', 'semana', 'tirar', 'amanhã', 'antes', 'não,', 'opção', 'talvez', 'coisas', 'daqui', 'massa', 'assunto', 'consigo', 'contato', 'faço', 'passar', 'Queria', 'somente', 'toda', 'vcs', 'Bora', 'enviar', 'isto', 'você', 'vc', 'vcs' )
    AND B.Palavra NOT LIKE '?%'
    AND B.Palavra NOT LIKE '..%'
    AND B.Palavra NOT LIKE 'rs%'
    AND B.Palavra NOT LIKE 'kkk%'
GROUP BY
    LTRIM(RTRIM(B.Palavra))
ORDER BY
    2 DESC


    
IF (OBJECT_ID('dbo.Palavras_Mais_Faladas_Por_Usuario') IS NOT NULL) DROP TABLE dbo.Palavras_Mais_Faladas_Por_Usuario
SELECT
    A.Ds_Usuario AS Ds_Usuario,
    A.Palavra,
    A.Qt_Ocorrencias
INTO
    dbo.Palavras_Mais_Faladas_Por_Usuario
FROM
    (
    SELECT
        A.Ds_Linha AS Ds_Usuario,
        LTRIM(RTRIM(B.Palavra)) AS Palavra,
        COUNT(*) AS Qt_Ocorrencias,
        ROW_NUMBER() OVER(PARTITION BY A.Ds_Linha ORDER BY COUNT(*) DESC) AS Ranking
    FROM
        dbo.Conversas_Whatsapp A
        CROSS APPLY dbo.fncSplitTexto(A.Ds_Mensagem, ' ') B
    WHERE
        A.Ds_Situacao = 'mensagem'
        AND LEN(B.Palavra) >= 3
        AND B.Palavra NOT IN ( 'que', 'nao', '<Arquivo', 'mídia', 'oculto>', 'para', 'com', 'uma', 'tem', 'Mas', 'pra', 'mais', 'por', 'isso', 'Esta', 'muito', 'Vai', 'como', 'Bom', 'Boa', 'aqui', 'Esse', 'ser', 'vou', 'fazer', 'quem', 'ele', 'pode', 'dia', 'Foi', 'alguem', 'bem', 'estou', 'mesmo', 'essa', 'até', 'ter', '??????', 'tenho', '??????', 'pessoal', 'Acho', 'Sim', 'também', 'Ainda', 'sobre', 'kkk', 'galera', 'são', 'Cara', 'meu', 'Obrigado', 'agora', 'nos', 'dos', 'tudo', 'Quando', 'sem', 'PESSOAL,', 'pelo', 'Depois', 'então', 'todos', 'Dar', 'minha', 'ver', 'sempre', 'parabéns', 'Nem', 'gente', 'usar', 'algum', 'mensagem', 'pois', 'qual', 'for', 'pela', 'Sei', 'faz', 'eles', 'das' )
        AND B.Palavra NOT IN ( 'seu', 'sua', 'vamos', 'era', 'nada', 'problema', 'fiz', 'Pro', 'Show', 'Sou', 'apenas', 'seria', 'Valeu', 'assim', 'melhor', 'tipo', 'porque', 'coisa', 'fica', 'seja', 'falar', 'outro', 'quer', 'algo', 'hoje', 'Onde', 'sim,', 'usando', 'uns', 'tarde', 'será', 'vlw', 'estão', 'todo', 'top', 'deve', 'sabe', 'galera,', 'caso', 'cada', 'Fala', 'preciso', 'outra', 'tinha', 'precisa', 'pouco', 'tiver', 'fazendo', 'Nesse', 'ano', 'erro', 'deu', 'estava', 'ficar', 'qualquer', 'muita', 'noite', 'uso' , 'local', 'mundo', 'Olha', 'querendo', 'rapaz', 'Gostaria', 'foram', 'bastante', 'Excelente') 
        AND B.Palavra NOT IN ( 'alguma', 'fora', 'alguns', 'Existe', 'nome', 'Posso', 'saber', 'Estamos', 'Legal', 'quero', 'hein', 'Nosso', 'Nunca', 'achei', 'menos', 'anos', 'podem', 'forma', 'grande', 'Manda', 'usa', 'conta', 'fez', 'pessoas', 'tentar', 'entendi', 'mercado', 'verdade', '(arquivo', 'anexado)', 'desse', 'vindo', 'certo', 'entrar', 'estar', 'outros', 'parece', 'temos', 'próximo', 'entre', 'Esses', 'isso,', 'parte', 'algumas', 'daí', 'exatamente', 'favor', 'hora', 'maior', 'mandar', 'ninguém', 'realmente', 'sábado', 'tenha', 'teria', 'vem', 'vezes', 'vida', 'umas', 'desde', 'dias', 'direto', 'dois' )
        AND B.Palavra NOT IN ( 'Aos', 'nas', 'novo', 'tive', 'dele', 'deles', 'dela', 'delas', 'dessa', 'mesma', 'sendo', 'ele', 'ela', 'vez', 'final', 'quiser', 'vão', 'vários', 'díficil', 'duas', 'colocar', 'dentro', 'dizer', 'essas', 'Fui', 'Nessa', 'pegar', 'conseguir', 'dia.', 'outras', 'Poderia', 'todas', 'certeza', 'dia,', 'falando', 'muitos', 'olhada', 'pessoa', 'quase', 'questão', 'semana', 'tirar', 'amanhã', 'antes', 'não,', 'opção', 'talvez', 'coisas', 'daqui', 'massa', 'assunto', 'consigo', 'contato', 'faço', 'passar', 'Queria', 'somente', 'toda', 'vcs', 'Bora', 'enviar', 'isto', 'você', 'vc', 'vcs' )
        AND B.Palavra NOT LIKE '?%'
        AND B.Palavra NOT LIKE '..%'
        AND B.Palavra NOT LIKE 'rs%'
        AND B.Palavra NOT LIKE 'kkk%'
    GROUP BY
        A.Ds_Linha,
        LTRIM(RTRIM(B.Palavra))
) A
WHERE
    A.Ranking <= 10
ORDER BY
    A.Qt_Ocorrencias DESC

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: