Relacionamento tabela fato com tabela de mudança status cliente

[matheusmarchezi]

Boa noite pessoal!

Estou com um problema com uma base de dados, já procurei soluções na internet e no curso, mas não encontrei.

A minha situação é que tenho uma base de dados com uma tabela fato que tem valores de vendas, uma tabela dimensão de clientes, e uma outra tabela que traz os registros de mudanças de status do cliente (por exemplo, cliente ativo, cliente sem compra no período, cliente voltou a comprar, etc) de forma histórica (chamada "logsitcomercial"). Cada vez que o cliente muda de status, é inserida uma nova linha nessa tabela, com a data da alteração, o status anterior e o status atual do cliente.

Já pensei em utilizar tabela bridge ou uma surrogate key, mas não consegui fazer funcionar. Como eu poderia fazer o relacionamento dessas mudanças de status dos clientes com a tabela fato de vendas para conseguir analisar os valores de acordo com o status do cliente em determinado período?

Base - Mudança Status Cliente.pbix

[Michele]

Olá @matheusmarchezi!

Se consegui compreender o que você precisa acredito que seja isso ou, pelo menos, muito próximo:

• Criei uma coluna na sua Fato_Log para definir o status do cliente naquele MÊS e ANO, isso é bem importante, se por acaso a sua necessidade de análise/regra mudar, vai precisar mudar o código nessa coluna criada, mudar o contexto no caso.
• Com base nessa coluna criei medidas que contem os clientes Ativos e Inativos.
• Com base nessas duas medidas acima consegui montar as medidas que somam os valores vendidos naquele MÊS e ANO, pegando a lista de clientes Ativos e a lista de clientes Inativos e buscando se a venda é para um desses grupos.

Abaixo coloco imagens da coluna criada, das medidas e do resultado, além de anexar o .pbix com essa solução.

IMPORTANTE: Tem meses, pelo que analise, em que os clientes não possuem log, portanto existem vendas para clientes "SEM STATUS". Para que não fosse entendido como erro a soma das Vendas de Ativos e Inativos não baterem, criei uma medida para somar as vendas dos clientes SEM STATUS, assim fica mais fácil confirmar que, aparentemente, os valores parecem estar corretos. Também validei a situação de alguns clientes e pareceu estar correta dentro da regra passada.

373510098_Base-MudanaStatusCliente.pbix

[Bernardo Vitor]

Boa noite @matheusmarchezi!
Tudo bem?

Alterei os relacionamentos, cardinalidades e sentidos entre as tabelas. Deixei inativo o relacionamento entre a dCalendario e a Dim_logsitcomercial. Quando você for fazer a análise por status do cliente em determinado período, ative este relacionamento via DAX utilizando o USERELASHIONSHIP.



Faça o teste por favor.

Se esta resposta resolver seu problema, gentileza classificá-la como Solução ✅

Atenciosamente,

Bernardo Vitor
(31) 99116-6134
LinkedIn: https://www.linkedin.com/in/bernardo-vitor

373510098_Base-MudanaStatusCliente.pbix

[Michele]

Olá @matheusmarchezi!

Essa sua tabela que registra os status é uma tabela histórica, então a primeira coisa a mudar, a meu ver, é considerar ela como uma tabela Fato e não uma tabela Dimensão. Considerando isso, a princípio, os relacionamentos deveriam ficar da forma exemplificada na imagem mais abaixo.

Agora para saber como montar as medidas ou até talvez criar algum campo que referencie o "momento" do cliente dentro da tabela de vendas, você consegue compartilhar como pretende analisar os dados? Por ano, mês, dia, etc.? É que com base nisso muda o contexto e pode mudar a forma que precisa montar as medidas.

Se quiseres montar um exemplo básico, pode ser no Excel, no próprio PBI, e envia um print com um resultado esperado para esses dados que compartilhasse conosco até agora, assim a gente consegue simular bem certinho como você precisa.

Eu vi que você utilizou um campo "Situação Comercial" da Dimensão Cliente, mas não consegui compreender se essa é a situação atual, pois entendo que se a situação muda ao longo do tempo, vamos precisar considerar essa Situação da tabela de logs mesmo, não a da Dimensão entendeu.

Se puder compartilhar mais detalhes/exemplos do que você precisa.

[Michele]

Olá @matheusmarchezi!

Hoje acabei me lembrando de uma aula do curso sobre "Dimensões que mudam ao longo do tempo (Slowly Changing Dimensions)" e pensei que talvez esse seja o caso da sua Dimensão, chegou a dar uma olhada nessa aula já?

https://play.powerbiexperience.com/curso/tc-2-power-query-e-linguagem-m/5177/50872

Quando conseguires nos contar um pouco mais sobre a sua regra de negócio, como funciona esse seu log:

• Tem um todo dia para cada cliente?
• Por exemplo, se você quiser as comprar por Ano/Mês e o cliente tiver mais de um registro dentro desse mês, como fica? Vale o status igual ou menos à data de venda?

[matheusmarchezi]

Boa noite pessoal!

@Bernardo Vitor, testei aqui sua sugestão, mas não funcionou. Os valores não ficam corretos quando tento analisar, por exemplo, a quantidade histórica vendida para um cliente que hoje está classificado como "inativo".

@Michele, a coluna "Situação Comercial" da tabela de dimensão Clientes traz o status atual do cliente. Acontece que inicialmente eu apenas tinha a tabela de clientes, com essa informação nela, e depois eu consegui essa tabela com o histórico de mudanças dos status dos clientes. Talvez as informações de status atual do cliente não esteja "batendo" entre as duas tabelas em alguns casos devido a máscara que eu fiz nos dados.

Em relação às análises, eu preciso fazer elas por ano e mês, e seria basicamente a quantidade e/ou percentual de clientes que entraram no status de "inativo" no período atual (algo como churn rate). E em caso de houver mais de um registro no mesmo mês para um cliente, deverá ser considerado o mais recente. Em relação aos códigos da situação, o código "AICLP315147" representa um cliente inativo, sem compras a algum tempo, e os demais consideramos como ativos.

Após realizar a postagem aqui, também achei essa aula sobre Slowly Changing Dimensions, e dei uma pesquisa na internet, e acho que essa tabela se enquadraria melhor nesse escopo. Porém mesmo assim ainda estou tendo dificuldades para fazer medidas que deem de fato um valor de clientes inativos e ativos em um determinado mês, pois pode ocorrer de o cliente ter mais de uma alteração no próprio mês.

Em relação ao período da atualização do status do cliente, ele pode variar de cliente para cliente, pois cada cliente é enquadrado dentro de uma categoria (dependendo do tipo/porte da empresa), e cada categoria tem um período distinto para essa atualização.

Eu até criei uma coluna com a data fim da situação atual do cliente, com base na próxima atualização que ocorreu para o cliente, e quando é a última atualização coloquei uma data aleatória (31/12/9999).

Uma medida que criei para tentar calcular a quantidade de clientes inativos por mês / ano foi a abaixo, porém não deu certo.

Clientes Inativados =
CALCULATE(
    DISTINCTCOUNT(fato_logsitcomercial[idCliente]),
    fato_logsitcomercial[Sit. Comercial Atual] = "AICLP315147",
    dCalendario[Data] >= MIN(fato_logsitcomercial[Data Alt.]) && dCalendario[Data] <= MAX(fato_logsitcomercial[Data Fim Sit. Atual])
)

Editado Novembro 15, 2022 por matheusmarchezi

[Ayumi]

@matheusmarchezi

Olá tudo bem?

Talvez a sequencia de vídeos de "Modelagem Complexa de CRM" possa te ajudar:
https://play.powerbiexperience.com/curso/tc-5a-exercicios-e-desafios-com-dax/5183/25220

 

Por gentileza Define a Melhor resposta para o tópico, para isso basta ir naquela que mais te ajudou e clicar em uma das duas opções conforme print:

 

 

[matheusmarchezi]

Olá pessoal, boa noite!

Peço desculpa pela demora em responder, tive alguns problemas que me obrigaram a paralisar esse projeto por algum tempo, mas estou retornando agora.

@Michele a sua solução me ajudou com parte do problema: encontrar a quantidade de clientes que mudaram o status para INATIVO ou ATIVO em determinado mês. Mas um outro dado que eu preciso levantar é a relação / quantidade total de clientes que estão ativos ou inativos no período / mês, não apenas que tiveram a mudança de status.

Estou enviando em anexo um pbix com uma base menor, só com 3 clientes, caso auxilie.

Agradeço o apoio até aqui.

forum-powerbi.pbix