Para suprir o propósito de todas as regras especificadas no escopo do trabalho, estas tiveram que ser detalhadas com o auxílio de diversos analistas, principalmente o de Marketing, onde estes se reuniram em uma sala para auxiliar na criação das regras, possibilitando assim esclarecer as dúvidas existentes. Necessitou-se de analistas de outras áreas: Faturamento, Contas Médica e Cadastro. As regras foram detalhadas conforme está descrito a seguir.
Regra 1: Utilização: são todos os valores pagos aos médicos pelo atendimento dos clientes, seja este uma consulta, exame ou qualquer outra relação entre médico e cliente.
Após a conclusão da regra, para se extrair a utilização dos clientes, foi necessário entender o processo do departamento de Contas Médicas, sendo este o responsável por gerar o valor de utilização dos clientes para assim conseguir pesquisar as tabelas e campos necessários para está regra, sendo os passos detalhados a seguir:
- primeiramente foi necessário identificar quais tabelas gravam os registros que passam pelo departamento de Contas Médicas;
- utilizaram-se todos os procedimentos3, sendo que estes deveriam estar com valor acima de zero, pois os mesmos poderiam estar com glosa4, acarretando em valor zerado; e
- agruparam-se por meses, as informações dos clientes e dos procedimentos utilizados, somando-se a quantidade utilizada, deixando assim o resultado do script um pouco mais enxuto, ou seja, ao invés de trazer um resultado analítico por completo, trouxe os dados mais agregados, ou seja, a princípio apareceriam dois registros para o cliente Tiago no mês de novembro, em cada registro, ele utilizou uma vez o procedimento ‘1111’, após esse agrupamento, aparecerá que o Tiago utilizou no mês de novembro duas vezes o procedimento ‘1111’.
Após pesquisar os procedimentos com valor acima de 0 (zero) e agrupá-los pelos meses de utilização, pode-se ter o valor da utilização dos clientes. Passou-se então para a pesquisa da regra 2.
Regra 2: Receita: são os valores recebidos dos clientes/empresas referente às suas mensalidades;
3 Procedimento: considera-se uma única consulta, um exame ou um material/medicamento utilizado pelo cliente. 4 Glosa: procedimento não autorizado pela operadora para execução.
49
Para chegar ao script final que verifica a receita dos clientes Pessoa Física (PF) ou Pessoa Jurídica (PJ) foi necessário entender o negócio do departamento de Faturamento. Pois é esta área que gera as faturas enviadas aos clientes/ empresas, para assim conseguir pesquisar as tabelas e campos necessários para esta regra, cujos passos serão detalhados a seguir:
-primeiramente, o departamento de Faturamento informou que é diferenciada a geração das faturas. Para clientes pessoa física, as faturas são geradas diretamente para a própria pessoa e pessoa jurídica são provenientes do valor total utilizado por seus funcionários;
- foi necessário identificar quais tabelas gravam tais registros. Com isso foram criados dois
scripts separadamente, pois assim como a geração das faturas, o modo como ficam registrados na
base de dados para clientes PF é diferente dos registros para clientes PJ;
- identificou-se quais regras deveriam ser utilizadas para cada tipo de fatura (PF e PJ), sendo que o relacionamento entre a fatura e o cliente é diferente nas duas situações; e,
- na ferramenta ETL, os registros destes dois scripts foram unidos para melhorar a apresentação da tela desenvolvida para o usuário final.
Com os registros de receita formatados foi possível relacioná-los com os registros da regra 1, possibilitando a visualização de um determinado cliente juntamente com sua utilização e receita.
Regra 3: Clientes que tenham realizado um determinado procedimento (consulta/ exame) em um prazo de 12 meses, deverão aparecer com alerta para utilização:
- depois de criada a regra 1, será possível determinar quais clientes entraram na faixa de alerta quanto a quantidade de procedimentos utilizados.
Regra 4: Quantidade de clientes que foram incluídos na base de dados em um determinado período, para saber se esta quantidade está evoluindo conforme as metas da empresa.
Para se chegar a esta regra, foi necessário esclarecer com o departamento de Cadastro como deveriam ser consideradas as inclusões de novos clientes. Mesmo que eles existam em um determinado plano, poderiam ter sido migrados de outros, com isso é correto afirmar que estes clientes não são novos em nossa base dados; e
- Após possuir a quantidade de clientes inclusos em determinado período, pode-se criar um gráfico para facilitar a visualização do gestor.
50
Regra 5: Clientes excluídos, que tiveram suas faturas quitadas e ainda utilizaram o plano, estes clientes deverão ser avaliados, pois poderá ser feita uma ação para tentar trazê-los de volta à empresa:
- inicialmente, verificou-se como este tipo de informação estava armazenado no banco de dados. Nesta análise, percebeu-se que existe uma codificação para as exclusões efetuadas e que seria possível selecionar os clientes por um determinado tipo de exclusão;
- em seguida, verificou-se quais clientes estavam excluídos;
- separou-se os clientes que não haviam sido excluídos por inadimplência5; e, - após, analisou-se quais clientes possuíam utilização baixa do plano.
Regra 6: O motivo das exclusões está gravado em uma variável do banco de dados. Se esta for preenchida com uma codificação (número) e não em um campo texto (digitado), poderão ser analisados os principais motivos de exclusões, caso contrário não será possível garantir a consistência das informações, pois erros de digitação acontecem. Isto servirá para tentar trazer o cliente de volta à empresa:
- assim como na regra anterior, esta também utilizou os motivos de exclusões para selecionar os clientes; e,
- com a primeira análise concluída, foi possível demonstrar os variados tipos de exclusões. As regras descritas anteriormente estão apresentadas em algumas telas na ferramenta criada para o analista de Marketing, onde a visualização desta ferramenta facilita a busca de informações para tomada de decisão.
Com as regras de negócio esclarecidas pelo analista de Marketing juntamente com as demais áreas, foi possível dar início a fase de ETL, ou seja, o desenvolvimento dos scripts que farão as extrações dos registros necessários para compor o novo banco de dados.
51
Após o desenvolvimento dos scripts que fizeram a extração dos registros das bases de dados, identificou-se alterações na estrutura inicial da modelagem, onde alguns pontos tiveram que ser modificados, pontos estes que serão melhores detalhados mais adiante.
Para responder as necessidades da área foram utilizados diversos filtros, além desses, foi preciso incluir no script vários campos para disponibilizar as mais variadas visões. Esses filtros devem estar disponíveis tanto para o cadastro dos clientes quanto para seus endereços.
Os filtros possibilitam efetuar uma pesquisa mais detalhada, ou seja, analisar uma quantidade menor de clientes, sendo possível a criação de um produto ou propaganda com determinado foco. Para este script utilizam-se sete tabelas da base de dados, isto foi necessário pelas informações estarem gravadas em locais diferentes. A tela desenvolvida para os filtros de clientes refere-se à Figura 19 na página 66.
Já os filtros referentes aos endereços dos clientes servem para a área poder criar pesquisas por localidades, possibilitando saber o número de clientes em uma determinada cidade ou conhecer o e-mail de determinado cliente. Para este script, utilizam-se duas tabelas da base de dados. A tela desenvolvida para os filtros de endereços refere-se à Figura 20 na página 67.
Nos dois scripts citados anteriormente, existem campos que precisaram ser normalizados, ou seja, transformados de ‘A’ para ‘Ativo’, de ‘E’ para ‘Excluído’ e de ‘I’ para ‘Inativo’. Outros campos não necessitaram efetuar tal tratamento, pois existe na base de dados uma codificação específica, porém, por estarem em locais distintos, precisou-se ser criado um vínculo entre eles. Por exemplo, para determinados planos ao invés de aparecer o seu código, aparecerá somente o nome referente a ele, isto foi necessário para melhorar o entendimento no momento de efetuar os filtros. Estas informações estarão a disposição do gestor para utilizar da maneira que lhe convir.
Estes dois scripts estão relacionados pelo campo cod_endereco, desta forma quando o analista efetuar filtros na tela de filtros de clientes, ao clicar na tela de endereços, aparecerá os respectivos endereços dos clientes selecionados. Por exemplo, determinada área precisa enviar uma correspondência para alguns clientes, ou saber quais desses clientes possuem e-mail para divulgar um produto por meio eletrônico.
Estes scripts só puderam ser desenvolvidos após a ajuda da área de Cadastro, que mostrou em quais locais e de que forma as informações estavam gravadas no sistema de gestão, isto auxiliou a pesquisa na base de dados.
52
Para confeccionar o script com os contratos, extraiu-se o nome e código de cada contrato, possibilitando o vínculo das tabelas e também a visualização do analista a qual empresa determinado cliente está relacionado.
Para desenvolver o script que faria carga da tabela dimensão Tempo, selecionou-se uma tabela da base de dados um campo referente às datas, possuindo mês e ano, utilizando-se de um comando para não haver duplicidades. Esta dimensão se faz necessário para o analista saber de qual período precisa extrair as informações, possibilitando analisar informações atuais com o passado.
Para desenvolver o script com as utilizações dos clientes, selecionaram-se registros de cinco tabelas da base de dados. O relacionamento dessas tabelas foi necessário, pois se precisou buscar informações dos procedimentos utilizados pelos clientes. Este script foi concluído com o auxílio da área de Contas Médicas, esta ajuda foi de grande valia para entender como são gravadas as utilizações dos clientes na base de dados. Neste script necessitou selecionar apenas contas que possuíam valor pago maior que 0 (zero), onde os dados foram extraídos de forma mais analítica para ser ajustado na parte front-end da ferramenta, possibilitando responder a regra 1 – Utilização dos clientes e também a regra 3 – Alta utilização dos clientes.
Para selecionar os registros com a receita dos clientes, a qual se refere a regra de negócio 2, foram necessários desenvolver dois scritps, um que buscasse diretamente a receita dos clientes e outro para buscar a receita das empresas, pois estas informações estão gravadas na base de dados de forma distinta. Para os dois scripts foram considerados os grupos de rubricas: Mensalidade6 e Co- participação7. Também será considerado para os dois scritps que as fatura devam ter o campo dt_cancelamento em branco, caso contrário faturas cancelas serão selecionadas.
O primeiro script, receita dos clientes, foi desenvolvido buscando diretamente os registros onde estão gravados os valores pagos dos clientes, para isso, tanto o código do cliente, quanto de seu contrato deverá estar preenchido. Já no segundo script, receita das empresas, buscou-se os registros que continham o código do cliente em branco, diferenciando assim os dois tipos de receitas. No entanto, neste último script necessitou-se criar o campo cod_cliente com um valor genérico, pois precisaram ter os mesmos campos.
6 Mensalidade: valor pago pelo plano.
53
Depois dos dois scripts desenvolvidos, os mesmos foram unidos formando uma única tabela, dessa forma, todas as informações de receita, sejam elas de clientes ou empresas, estavam em um único lugar. Estas informações das receitas dos clientes respondem a regra de negócio 2.
Já na visão do gestor, estas informações de valores foram agrupadas por competência e clientes, possibilitando assim a criação da tela Receita x Utilização, facilitando a análise do valor de utilização e receita em uma única tela.
Para as regras de negócio de número 1 - Utilização e 2 - Receita, criou-se um relacionamento entre as mesmas, possibilitando criar a tela que mostra todos os clientes com seus respectivos valores para receita e utilização. Dessa forma, deverão aparecer todos os clientes, indiferentemente se tiveram ou não receita e utilização. Na tela desenvolvida para estas regras apresentaram-se os campos: mes_ano, numero_associado, nome_associado, valor_receita e valor_utilizacao. Esta tela é apresentada pela Figura 23 na página 69. Para facilitar a análise dos valores, definiu-se que deverá ser ordenado pelo valor de receita em ordem decrescente, dessa forma é possível analisar os clientes que pagaram suas faturas e utilizaram pouco ou não tiveram utilização de seus planos, podendo assim possibilitar algum brinde para tais clientes, criando um vínculo de fidelização entre eles e a empresa.
Para auxiliar a análise do gestor, outra tela foi desenvolvida, a Alerta Alta Utilização, onde as informações contidas na mesma são provenientes do script desenvolvido para regra de negócio 1 - Utilização. Para esta tela as informações foram agrupadas por clientes e procedimentos, somando a quantidade utilizada, isto possibilitou analisar os clientes que estão utilizando muito de um determinado procedimento, com esse novo agrupamento, responde-se a regra de negócio 3 – Alta utilização dos clientes, apresentada pela Figura 25 na página 71.
Durante a etapa de estudo, verificou-se que existe uma codificação para os motivos de exclusão na base de dados, sendo assim é possível analisar e quantificar os clientes pelos motivos de exclusões, possibilitando a área de criar maneiras para trazer os clientes com determinado tipo de exclusão a empresa novamente. A viabilidade de analisar tais informações responde a regra de negócio 6 – Analisar motivos de exclusões. O script para responder a esta regra de negócio foi criado a partir do cadastro dos clientes, onde o registro para tal possui um campo especifico para o motivo de exclusão quando o mesmo estiver excluído, caso contrário este registro estará em branco..
54
Seguindo com a regra de negócio 6, necessitou-se criar a visão do gestor, relacionando-se o cod_motivo_exclusao com a tabela motivo_exclusao para apresentar os nomes de tais motivos, ou seja, ao invés de apresentar ao gestor o código de exclusão, por exemplo: 1, 2, 3, etc., será apresentado o nome do motivo, por exemplo: Opção do cliente ou Opção da empresa. Em resposta a essa regra/necessidade, desenvolveu-se a tela apresentada pela Figura 24 na página 70, para facilitar a análise na mesma foi inserida um gráfico no formato pizza, quantificando os motivos de exclusão, assim é fácil verificar qual motivo possui uma quantidade maior de clientes excluídos.
Para responder a regra de negócio 5 – Clientes excluídos com faturas quitadas, deve-se selecionar os clientes excluídos na opção de situação dos clientes, isto existe na tela de filtros dos clientes, em seguida clicar na tela Receita x Utilização, assim serão apresentados todos os clientes que pagaram suas faturas e comparar se sua utilização foi baixa ou não. O gestor ainda terá a opção por escolher um determinado motivo de exclusão conforme já apresentado para a regra de negócio 6 Analisar motivos de exclusões.
Para desenvolver os scripts que responderão a regra de negócio 4 – Quantidade de clientes na base de dados, primeiramente confeccionou-se um script que trouxesse todos os clientes e seus respectivos contratos, porém utilizou-se um comando para não apresentar duplicidade. Juntamente com este primeiro script, outro também foi criado trazendo de uma tabela da base de dados um campo referente às datas, possuindo mês e ano, igualmente ao script da dimensão Tempo e da mesma forma que o anterior, não poderá apresentar duplicidade.
Após os dois últimos scripts desenvolvidos, foi criado um verificador para saber se em determinado mês o cliente está ativo ou não. Primeiro seleciona os clientes e meses, após isto é feito uma análise de forma que se o cliente possui data de inclusão menor que o mês posterior e possui a data de exclusão maior ou igual ao mês posterior, este cliente é considerado com a situação de ativo naquele mês. Com isso um campo de verificação será preenchido com o valor ‘1’, caso contrário, cliente inativo, este campo receberá o valor ‘0’. Esta verificação ocorrerá para todos os clientes em todos os meses.
Assim, na parte front-end da ferramenta, desenvolveu-se uma tela para apresentar esta quantidade, conforme mostra a Figura 26 na página 72. A tela demonstra a soma do campo verificador, onde este foi preenchido durante a extração das informações, ou seja, se o analista desejar analisar a quantidade de clientes em um determinado contrato, primeiramente selecionará o contrato e os meses que desejar analisar, automaticamente será somado a quantidade de clientes que
55
tenha o campo verificador igual a ‘1’ nos meses selecionados, em seguida será apresentado um gráfico de linhas com a quantidade de cliente ativos nos meses.
Finalizando o detalhamento dos scripts, é possível esclarecer que conforme identificado na etapa anterior, foi implementado o modelo Flocos de Neve (SnowFlake). No caso, a tabela Dimensão DIM_CLIENTE, que antes trazia dados de endereço e o motivo da exclusão dos clientes, foi alterada, gerando assim duas novas tabelas de dimensão, DIM_ENDERECO e DIM_MOTIVO_EXCLUSAO. Nessas tabelas ficaram somente os dados dos clientes que possuem endereço ou que já foram excluídos, respectivamente.
Com a conclusão dos scripts, foram retiradas da modelagem antiga, as tabelas de Dimensão DIM_MEDICOS e DIM_PROCEDIMENTO, pois se identificou que nenhuma das regras levantadas no escopo iria utilizar os dados contidos em tais tabelas, sendo que futuramente, essas informações poderão ser de grande utilidade para o departamento de Marketing.
Assim como a modelagem, outro ponto foi alterado, nas primeiras conversas com o analista de Marketing, foi informado que existiam várias fontes de dados. No entanto, após aprofundar os conhecimentos das fontes de dados e desenvolver os scripts que respondessem as regras de negócio, descobriu-se que existe uma única fonte de dados, sendo o sistema de gestão atual que a Operadora utiliza. Ou seja, para o analista, existem várias fontes de dados, pois recebe relatórios de áreas distintas, mas ao analisá-los fora do sistema de gestão, notou-se que existe uma única fonte de dados.
Tão logo os scripts foram concluídos e as visões para os gestores desenvolvidas, os testes puderam ser iniciados, pois quanto mais testes forem realizados, menores são as chances de aparecer erros futuros. Desta forma, alguns detalhes que não haviam sido levantados no início foram aparecendo, detalhes estes que se não fossem esclarecidos, poderiam gerar informações erradas aos tomadores de decisão. Sabendo disso, necessitou-se contatar outras áreas para auxiliar na criação das regras.
Ao finalizar a construção dos scripts, onde estes já na ferramenta QlikView, fazem a parte de extração, transformação e carga dos dados do sistema de gestão utilizado pela Operadora de Saúde. Deste modo, após a conclusão de todas as cargas e transformações necessárias, as telas que já haviam sido iniciadas, foram complementadas para possibilitar os testes finais da ferramenta.
56