Power BI Gateway · Documento de referência

RFM — as regras, lado a lado

Comparação fato a fato do cálculo de Recência, Frequência, Valor, Nota e Segmento entre as três referências que temos. Documento descritivo — nada foi alterado no backend.

Planilha (VendaMais / “RFM - Erlan.xlsx”) BI do TRON (HorusBI) Nosso gateway (implementação atual)

Estado em jul/2026. Fontes: fórmulas do Excel (aba “Carteira de Clientes”), dado real do BI revertido da tabela T_RFV_027713, e o código RfmScore.php + RfmRepository.php.

01 — Resumo

Veredito rápido

Onde as três concordam, onde divergem, e por quê.

Cada linha é um “fato” do RFM. A última coluna diz a situação do nosso gateway hoje.
FatoPlanilha (Erlan)BI do TRON (dado real)Nosso gatewaySituação
Recência Dias sem compra → 30/60/90/120 Segue 30/60/90/120 (legenda de ≤3d é falsa) 30/60/90/120 bate · 28/29
Frequência Nº de meses c/ compra em 12m Opaco (ETL do DW) Nº de meses c/ compra em 12m só planilha · 15/29
Valor Faixa fixa de R$ (fat. 12m) Opaco (nem quintil, nem faixa) Faixa fixa de R$ — cortes do TRON (200k/140k/80k/20k) resolvido
F+M médio (FM) ROUNDUP((F+M)/2) (entra no segmento) ROUND((F+V)/2) bate
Nota Não existe soma; saída = segmento R+F+V (3–15) R+F+V (3–15) bate BI
Segmento Matriz 5×5 R × FM 11 segmentos, matriz R × FM Matriz 5×5 R × FM (idêntica à planilha) bate
Janela / população R = histórico · F/M = 12m Estático (pré-calculado no DW) R = histórico · F/V = 12m · população = quem faturou no período alinhado
02 — O achado que muda tudo

A legenda do BI está errada

Legenda na tela ≠ dado no banco

A tela de RFM do BI exibe uma legenda (R5 ≤3 dias, Frequência por “pedidos/semana”, Valor por “quintis 20%”). Mas quando revertemos o dado real da tabela do DW (T_RFV_027713), ele não segue essa legenda.

Prova na Recência — dias reais por score do BI: R5 3–25 · R4 33–54 · R3 61–75 · R2 97–111 · R1 112+. Isso é a régua 30/60/90/120 da planilha, não o “≤3 dias” da legenda.

Por isso a orientação foi: “siga na planilha e veja o quanto bate com o BI — a legenda pode estar errada.” E bateu (28 de 29 clientes na Recência).

03 — Fato a fato

Cada dimensão em detalhe

A régua da planilha, o que o BI faz de verdade, e o que está no nosso gateway.

Recência (R)

Dias desde a última compra (todo o histórico) até hoje.

Planilha R5 ≤30 · R4 31–60 · R3 61–90 · R2 91–120 · R1 >120
BI mesmo comportamento no dado (legenda ≤3/7/14/30 é falsa)
Nosso 30/60/90/120 · 28/29

Frequência (F)

Nº de meses distintos com compra nos últimos 12 meses.

Planilha F5 =12 · F4 9–11 · F3 6–8 · F2 3–5 · F1 ≤2
BI opaco — há F5 sem pedido no ano e cliente com 140+ notas em F2
Nosso igual à planilha · 15/29

F+M médio (FM) — eixo Y da matriz

Média de Frequência e Valor, arredondada pra cima.

Planilha ROUNDUP((F+M)/2)
Nosso ROUND((F+V)/2) — idêntico p/ meios-inteiros · bate

Nota

Pontuação geral do cliente.

Planilha não tem soma 3–15 — a saída é o segmento
BI R+F+V (vai até 15)
Nosso R+F+V · bate BI

Valor (M) — resolvido (cortes do TRON)

As três concordam no método: faixa fixa de R$ sobre o faturamento dos últimos 12 meses, sem quintil (confirmado na planilha; e o V do BI não muda ao filtrar data → é estático). O que faltava eram os cortes certos: os do Erlan (1M/500k/200k/50k) são de outro cliente e no TRON jogavam quase tudo em V1. O PO definiu os cortes do TRON (jul/2026):

Cortes do TRON (em vigor) V5 ≥200k · V4 140k–199.999,99 · V3 80k–139.999,99 · V2 20k–79.999,99 · V1 ≤19.999,99
BI do TRON segue opaco — nem monotônico com faturamento (000285 = V5 com R$50k; 000682 = V2 com R$365k); não reproduzível (vem do ETL do DW)

Distribuição agora (população da tela — quem faturou no mês, ~350 clientes): V1 40% · V2 36% · V3 9% · V4 7% · V5 8% — o Valor voltou a discriminar. (Sobre a base inteira, incluindo dormentes, o V1 fica pesado — mas a tela só mostra a população do período.)

04 — Segmentação

A matriz 5×5 (R × F+M)

Idêntica na planilha e no nosso gateway. Eixo X = Recência (1→5), eixo Y = F+M médio (5→1). Campeões exige R5 e FM5 (a versão antiga do nosso doc marcava R5·FM4 como Campeões — está corrigido no código).

R1
R2
R3
R4
R5
FM 5
Não pode
perdê-los
Não pode
perdê-los
Clientes
leais
Clientes
leais
Campeões
FM 4
Em risco
Em risco
Clientes
leais
Clientes
leais
Clientes
leais
FM 3
Em risco
Em risco
Precisam de
atenção
Potenciais
leais
Potenciais
leais
FM 2
Perdidos
Hibernando
Prestes a
dormir
Potenciais
leais
Potenciais
leais
FM 1
Perdidos
Perdidos
Prestes a
dormir
Promissores
Clientes
recentes

→ Recência aumenta para a direita · ↑ Frequência+Valor aumenta para cima

05 — Por que F e V do BI não são reproduzíveis

O que mora no DW e não no Protheus

Recência e Nota do BI batem porque são deriváveis das vendas. Já Frequência e Valor do BI vêm de colunas pré-calculadas na tabela T_RFV_027713 (MySQL do HorusBI), populadas por um ETL fora do Protheus, ao qual não temos acesso. Sinais de que são opacos:

Decisão de projeto (registrada): o gateway define a sua própria versão, calculada do Protheus e responsiva aos filtros (requisito do PO), seguindo a planilha. R e Nota coincidem com o BI; F e V seguem a planilha e por isso divergem dos números do BI — o que é esperado e transparente.

06 — Validação ao vivo

Quanto bate com o BI hoje

Cruzamento por cliente (n = 29) do gateway contra o dado do BID do TRON.

DimensãoAcerto vs BILeitura
Recência28 / 29 (97%)Régua da planilha = dado do BI. ok
NotabateSoma R+F+V, vai até 15. ok
SegmentobateMatriz 5×5 célula a célula. ok
Frequência15 / 29BI opaco; seguimos a planilha. nossa versão
Valordiverge do BIV do BI é opaco (ETL); com os cortes do TRON a distribuição na tela é saudável (V1 40%…V5 8%). cortes TRON
07 — Extra da planilha

Classificação de Status (ainda não usamos)

A planilha também classifica cada cliente por atividade — pode virar um card/filtro futuro:

StatusRegra (planilha)
Sem compraFaturamento 12m = 0
InativoDias sem compra > 90
Pré-InativoDias sem compra entre 60 e 90
AtivoCaso contrário
08 — Verbatim da planilha

As regras, exatamente como estão no arquivo

Extraído da aba Carteira de Clientes (4.358 clientes) do “RFM - Erlan.xlsx”. Fórmulas literais.

Tabela de faixas (células U:AB)

Um score (5→1) por faixa, em três colunas paralelas — Recência, Frequência, Monetária.
ScoreR — Dias sem compraF — Nº meses c/ compra (12m)M — Fat. últimos 12m (R$)
5≤ 30= 12≥ 1.000.000
431 – 609 – 11500.000 – 999.999,99
361 – 906 – 8200.000 – 499.999,99
291 – 1203 – 550.000 – 199.999,99
1≥ 121≤ 2≤ 49.999,99

⚠️ Os cortes de R$ do Valor (coluna M) acima são do Erlan (outro cliente). No nosso gateway o Valor usa os cortes do TRON (definidos pelo PO): V5 ≥200k · V4 ≥140k · V3 ≥80k · V2 ≥20k · V1 <20k. As réguas de R e F são as mesmas.

Fórmulas (colunas de cálculo)

Dias sem compra = data do relatório − data da última compra
R = faixa de “dias sem compra” → ≤30 = R5 … ≥121 = R1
F = faixa de “nº de meses com compra em 12m”
M = faixa fixa de R$ de “fat. últimos 12 meses” (sem quintil)
Média F+M = ROUNDUP((F + M) / 2)
Status = Fat12=0 → Sem compra · Dias > 90 → Inativo · 60–90 → Pré-Inativo · senão Ativo

Os 11 segmentos (rótulos numerados do arquivo)

Mesma matriz da seção 04, com os nomes exatos e a regra (R, Média F+M):

#SegmentoRegra (R · FM)
01CampeõesR5 · FM5
02Clientes LeaisR3-5 · FM4-5 (exceto R5·FM5)
03Potenciais Clientes LeaisR4-5 · FM2-3
04Clientes RecentesR5 · FM1
05PromissoresR4 · FM1
06Precisam de AtençãoR3 · FM3
07Prestes a DormirR3 · FM1-2
08Em RiscoR1-2 · FM3-4
09Não Pode Perdê-losR1-2 · FM5
10HibernandoR2 · FM2
11PerdidosR1 · FM1-2 · e R2·FM1

Colunas extras que a planilha calcula

Além do RFM: Qtd. de SKUs (36–25m / 24–13m / 12m), Partic. % do faturamento 12m, Comparativo de faturamento de 2 anos com flag Crescendo/Caindo (faturamento e SKUs), Soma de faturamento 36m, Soma de positivação 36m, flags Compraram por janela, e Tipo de Cliente (ex.: Recorrente).

09 — Teste de mesa

Como conferir você mesmo

Três passos, repetíveis em qualquer cliente, para garantir que a régua foi lida certo.

  1. Pegue os 3 insumos do cliente: dias sem compra · nº de meses com compra em 12m · faturamento em 12m.
  2. Aplique a tabela de faixas (seção 08) → R, F, M. Calcule FM = ROUNDUP((F+M)/2) e leia o segmento na matriz (seção 04).
  3. Compare com a planilha (colunas R/F/M/AE/AF), o nosso gateway e o BI.

Exemplo A — na própria planilha (1 por segmento)

Insumos crus → faixas aplicadas na mão → segmento. Bate 100% com as colunas calculadas do arquivo.
Cód.DiasMeses 12mFat. 12mR · F · MFMSegmento (planilha)
85210114.134.6125 · 4 · 5501-Campeões
649114.905.2034 · 4 · 5502-Clientes Leais
833105964.3585 · 2 · 4303-Potenciais Leais
112543229.0465 · 1 · 1104-Clientes Recentes
1251551218.2844 · 1 · 1105-Promissores
15843675903.9853 · 2 · 4306-Precisam de Atenção
23387673143.1273 · 2 · 2207-Prestes a Dormir
1237411441.411.3052 · 2 · 5408-Em Risco
16533992228.8272 · 1 · 3210-Hibernando
10516451331.3971 · 1 · 3211-Perdidos

Exemplo B — no nosso gateway (clientes reais do TRON, hoje)

Insumos do Protheus (hoje) — um cliente por faixa de Valor. O V espalha V5→V1 com os cortes do TRON, e o segmento fecha na mão.
ClienteDiasMeses 12mFat. 12mR · F · VNotaSegmento (gateway)
00260646281.1415 · 3 · 513Clientes leais
00073637144.7455 · 3 · 412Clientes leais
001236310135.3025 · 4 · 312Clientes leais
0005263149.7045 · 1 · 28Potenciais leais
0005904215.7975 · 1 · 17Clientes recentes

Reproduzir no gateway (backend, WSL)

Imprime insumos + scores de 10 clientes para conferir na mão — php artisan tinker:

use App\Support\Protheus\RfmScore as S; $fat = App\Support\Protheus\FiltroFaturamento::existsSql('010'); $i12 = "CONVERT(varchar(8),DATEADD(month,-12,GETDATE()),112)"; $rows = DB::connection('protheus')->select(" SELECT TOP 10 cli, DATEDIFF(day,CONVERT(date,ultima,112),CAST(GETDATE() AS date)) dias, meses12, fat12 FROM (SELECT RTRIM(D2_CLIENTE) cli, MAX(D2_EMISSAO) ultima, COUNT(DISTINCT CASE WHEN D2_EMISSAO>=$i12 THEN LEFT(D2_EMISSAO,6) END) meses12, SUM(CASE WHEN D2_EMISSAO>=$i12 THEN D2_VALBRUT ELSE 0 END) fat12 FROM SD2010 D2 WHERE D2.D_E_L_E_T_<>'*' AND $fat GROUP BY RTRIM(D2_CLIENTE)) vc WHERE meses12>=1 ORDER BY fat12 DESC"); foreach ($rows as $x) printf("%s dias=%d m12=%d fat=%s => R%d F%d V%d\n", $x->cli,$x->dias,$x->meses12,number_format($x->fat12), S::recencia($x->dias),S::frequencia($x->meses12),S::valor($x->fat12));

No BI (cruzamento final)

Abra o report da matriz RFM (traz Código + R/F/V por cliente), escolha um cliente e compare: Recência e Nota batem; Frequência e Valor divergem — vêm do ETL do DW, são opacos (esperado, ver seção 05).

10 — A matriz do BI engana

Conta notas duplicadas, não clientes

A peça que fecha a leitura da tela. Os rótulos da matriz do BI somam ~6.226, mas ao clicar num segmento o BI traz muito menos clientes. O rótulo conta linhas duplicadas (cliente × período no T_RFV_027713), não clientes distintos.

Clientes distintos (nossa contagem) × notas por cliente. Quanto mais o cliente compra, mais o rótulo do BI infla.
SegmentoClientes distintosNotas / cliente (hist.)
Campeões44238
Clientes leais83104
Potenciais leais66740
Prestes a dormir30625
Perdidos47015
Promissores1528
Clientes recentes1427

Rótulo inflado × cliente real

Ao clicar no segmento, o BI mostra o nº real (drill-down) — próximo da nossa contagem distinta.
SegmentoRótulo matriz (BI)Drill-down real (BI)Nosso (distinto)
Campeões4632544V/F opaco
Potenciais leais2.402645667≈ bate
Total~6.226~2.0162.030≈ bate

Conclusão

Não há erro na nossa contagem — a nossa matriz já conta clientes distintos (COUNT(DISTINCT cliente)), que é o número real que o BI só revela no drill-down. O rótulo 6.226 é artefato de contagem (notas duplicadas). Nosso total (2.030) bate com o real do BI (~2.016); as diferenças por segmento (ex.: Campeões 44 × 25) são a divergência de F/V já conhecida (ETL do DW).

11 — Contagem por segmento: é F e V, não população

Estudo de caso: “Precisam de Atenção”

O BI traz 70 nesse segmento; nós 36. Cruzando a lista exata dos 70 do BI com o nosso cálculo, a diferença fica clara — e não é população nem Recência.

Os 70 clientes que o BI classifica como “Precisam de Atenção” (todos R3 no BI), no nosso cálculo.
DimensãoResultadoVeredito
Na nossa base?70 de 70 presentesok
Compraram em 2026?70 de 70 (nenhum “+1 ano sem comprar”)população ok
Recência70 de 70 = R3 (idêntico ao BI)R bate
Frequência (F3·F2·F1)BI 3·26·41  Ã—  nós 14·34·22diverge
Valor (V5·V4·V3·V2·V1)BI 22·25·22·1·0  Ã—  nós 0·0·2·39·29diverge muito

Motivo da diferença de contagem (registrado)

Não é população (os ~2.031 do nosso RFM batem com os ~1.968 do Horus) nem Recência (bate cliente a cliente). A divergência está em Frequência e Valor, porque no BI elas vêm pré-calculadas do ETL do DW (T_RFV_027713) e não da planilha aplicada ao Protheus.

Valor: nós usamos faixa fixa nos cortes do TRON (200k/140k/80k/20k). Esses cortes são altos para a escala do TRON (faturamento 12m médio ~R$27k), então quase todo cliente cai em V1–V2 — enquanto o V do BI é espalhado (V3–V5), com cara de quintil. Testei cortes fixos e quintil em 12m/24m/histórico: nenhuma janela reproduz o V do BI.

Frequência: nossa contagem de meses com compra em 12m dá mais alta que a do BI (o ETL conta menos meses).

Decisão: seguimos a planilha 100% (R, F, V faixa-fixa, FM, segmento). Os números divergem do BI porque o BI ≠ planilha em F e V. Para bater exato com o BI, o único caminho é espelhar a T_RFV do DW (ler os scores prontos) — o que abandona o cálculo pela planilha e vira um espelho do BI.

Nada foi alterado para produzir este documento — é uma leitura do estado atual (código backend/app/Support/Protheus/RfmScore.php e RfmRepository.php), das fórmulas da planilha “RFM - Erlan.xlsx” e do dado revertido do BI (T_RFV_027713).

Atualização (jul/2026): os cortes do Valor passaram para os do TRON (200k/140k/80k/20k, definidos pelo PO) — RfmScore::VALOR_FAIXAS, teste unitário e o backend/docs/regras-metricas.md já estão sincronizados com este relatório.