Problem
The customer data had duplicates, inconsistent text, impossible values and incomplete records, making it hard to identify the factors associated with churn.
technical case study
Data Tool • Data cleaning and analysis
Python pipeline for cleaning, exploratory analysis and hypothesis testing on a customer churn dataset.
First look at the data: 500 duplicates, inconsistent text and impossible values, such as an age of 150 and spending of R$ 99,999.
Churn rate by range of each attribute, compared with the dataset average (34%).
Hypotheses tested: with each risk factor churn sits between 47% and 61%; without it, between 24% and 30%.
The customer data had duplicates, inconsistent text, impossible values and incomplete records, making it hard to identify the factors associated with churn.
A notebook workflow was built to clean the data, remove noise, visualize distributions and test hypotheses about the main churn drivers.
The analysis pointed to five relevant signals: age over 50, many calls to support, payment delays over 20 days, monthly contracts and low total spend.
POC completed, ready to evolve into a dashboard, a reusable pipeline or a predictive model.
The project started as an exploratory analysis of cancellations. The first step was cleaning the data, removing the customer ID and dropping incomplete rows. The analysis then compared the share of cancellations before and after filters based on behavioral hypotheses.
Matplotlib charts showing the churn rate by range of each attribute against the dataset average, plus a comparison with and without each risk factor.
Python workflow with Pandas: removing duplicates and the ID, standardizing text, handling impossible and missing values, churn rate by range and comparison with and without each factor.
Fictional CSV dataset (50,500 rows, generated by a script with a fixed seed) with customer, contract, usage, delay, support, total spend and churn attributes, including typical real-world data problems.
The customer ID was removed before analysis, reducing noise and avoiding a field with no direct analytical value.
Rows with missing data were dropped to avoid distorting calculations and charts.
Results are treated as exploratory analysis, not automatic proof of causation. The final reading needs business validation.
Reading the CSV file and a first inspection of the table with Pandas.
Removing duplicates and the CustomerID, standardizing text and dropping impossible or missing values.
Calculating the overall churn rate (33.6%) and the rate by range of each attribute.
Applying filters to test the impact of age, support calls, payment delay, contract type and total spend.
Summarizing the factors most associated with churn and pointing to ways to reduce it.
linhas_inicio = len(df)
#3.1 Remover clientes duplicados
df = df.drop_duplicates()
print("Clientes com ID repetido depois da limpeza:", df["CustomerID"].duplicated().sum())
#3.2 O Identificador unico de cada cliente nao ajudaria a explicar o cancelamento
df = df.drop(columns="CustomerID")
print(f"Removidas {linhas_inicio - len(df):,} linhas duplicadas")
#3.3 Padronizar os textos (tirar espaços sobrando e deixar so a primeira letra maiuscula)
for coluna in ["sexo", "assinatura", "duracao_contrato"]:
df[coluna] = df[coluna].str.strip().str.title()
for coluna in ["sexo", "assinatura", "duracao_contrato"]:
print(df[coluna].value_counts().to_dict())
#3.4 Valores impossiveis passam a ser tratados como "none" ou inexistentes
df.loc[~df["idade"].between(18, 100), "idade"] = None
df.loc[df["dias_atraso"] < 0, "dias_atraso"] = None
df.loc[~df["total_gasto"].between(1, 10_000), "total_gasto"] = None
df[["idade", "dias_atraso", "total_gasto"]].describe().round(1)
#3.5 Clientes com informacao faltando devem ser removidos
antes = len(df)
df = df.dropna()
print(f"Removidas {antes - len(df):,} linhas com informação faltando")
#3.6 Colunas numericas ganham numeros inteiros (facilita a analise la na frente)
colunas_inteiras = ["idade", "tempo_como_cliente", "frequencia_uso", "ligacoes_callcenter",
"dias_atraso", "meses_ultima_interacao", "cancelou"]
df[colunas_inteiras] = df[colunas_inteiras].astype(int)
print(f"\nLinhas no início: {linhas_inicio:,}")
print(f"Linhas após a limpeza: {len(df):,} ({len(df) / linhas_inicio:.1%} da base original)")
def taxa_por_faixa(dados, coluna):
# Taxa de cancelamento em cada faixa (ou categoria) de uma coluna
if coluna in FAIXAS:
bordas = FAIXAS[coluna]
rotulos = [f"{a}–{b - 1}" for a, b in zip(bordas[:-1], bordas[1:])]
grupos = pd.cut(dados[coluna], bins=bordas, right=False, labels=rotulos)
else:
grupos = dados[coluna]
taxa = dados.groupby(grupos, observed=True)["cancelou"].mean()
if coluna in ORDEM:
taxa = taxa.reindex(ORDEM[coluna])
return taxa
fig, eixos = plt.subplots(4, 3, figsize=(13, 14))
eixos = eixos.flatten()
for eixo, coluna in zip(eixos, TITULOS):
taxa = taxa_por_faixa(df, coluna)
eixo.bar(taxa.index.astype(str), taxa.values, color=AZUL, width=0.7)
eixo.axhline(taxa_geral, color=CINZA_TEXTO, linewidth=1)
eixo.set_title(TITULOS[coluna], loc="left")
eixo.set_ylim(0, 1)
eixo.yaxis.set_major_formatter(PercentFormatter(1, decimals=0))
eixo.tick_params(axis="x", labelsize=8, rotation=45 if len(taxa) > 5 else 0)
for eixo in eixos[len(TITULOS):]:
eixo.set_visible(False)
fig.suptitle("Taxa de cancelamento por característica do cliente", x=0.01, ha="left",
fontsize=14, fontweight="bold")
fig.text(0.01, 0.962, f"Linha cinza = taxa média de cancelamento ({taxa_geral:.0%})",
color=CINZA_TEXTO, fontsize=10)
fig.tight_layout(rect=(0, 0, 1, 0.96))
fig.savefig("../imagens/taxa_por_caracteristica.png", dpi=150, bbox_inches="tight")
plt.show()
hipoteses = {
"p1: mais de 50 anos": df["idade"] > 50,
"p2: mais de 4 ligações ao call center": df["ligacoes_callcenter"] > 4,
"p3: mais de 20 dias de atraso": df["dias_atraso"] > 20,
"p4: contrato mensal": df["duracao_contrato"] == "Monthly",
"p5: gasto total abaixo de R$ 500": df["total_gasto"] < 500,
}
linhas = []
for nome, tem_fator in hipoteses.items():
linhas.append({
"hipótese": nome,
"% dos clientes": tem_fator.mean(),
"cancelamento com o fator": df.loc[tem_fator, "cancelou"].mean(),
"cancelamento sem o fator": df.loc[~tem_fator, "cancelou"].mean(),
})
resultado = pd.DataFrame(linhas).set_index("hipótese")
resultado["diferença (p.p.)"] = (resultado["cancelamento com o fator"]
- resultado["cancelamento sem o fator"]) * 100
resultado.style.format({
"% dos clientes": "{:.1%}",
"cancelamento com o fator": "{:.1%}",
"cancelamento sem o fator": "{:.1%}",
"diferença (p.p.)": "{:+.1f}",
})
tem_algum_fator = pd.concat(hipoteses, axis=1).any(axis=1)
sem_nenhum_fator = df[~tem_algum_fator]
taxa_sem_fatores = sem_nenhum_fator["cancelou"].mean()
print(f"Clientes sem nenhum dos 5 fatores: {len(sem_nenhum_fator):,} ({(~tem_algum_fator).mean():.1%} da base)")
print(f"Cancelamento na base toda: {taxa_geral:.1%}")
print(f"Cancelamento nesse grupo: {taxa_sem_fatores:.1%}")
print(f"Diferença: {(taxa_sem_fatores - taxa_geral) * 100:+.1f} pontos percentuais")
The dataset's churn rate is 33.6%. Among customers with none of the five risk factors (24% of the base), churn drops to 8.6%, which shows the size of the opportunity.
The main takeaway was turning data cleaning and exploration into actionable insight, while being careful not to confuse correlation with causation.