technical case study

Data Tool • Data cleaning and analysis

Data Cleaner

Python pipeline for cleaning, exploratory analysis and hypothesis testing on a customer churn dataset.

Data analysis, cleaning, visualization and interpretation Completed Proof of concept completed
Python Pandas Matplotlib Jupyter Notebook Data Analysis
executive summary

Problem

The customer data had duplicates, inconsistent text, impossible values and incomplete records, making it hard to identify the factors associated with churn.

Solution

A notebook workflow was built to clean the data, remove noise, visualize distributions and test hypotheses about the main churn drivers.

Result

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.

Current stage

POC completed, ready to evolve into a dashboard, a reusable pipeline or a predictive model.

context

Turning raw data into business insight

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.

goals and constraints

Goals

  • Import and prepare a customer dataset for analysis
  • Remove irrelevant fields and incomplete records
  • Visualize the relationship between variables and churn
  • Test hypotheses about factors associated with churn
  • Produce a clear reading to guide business decisions

Constraints

  • Data analyzed in a notebook, with no final interface
  • Exploratory analysis, without definitive causal inference
  • Hypotheses based on manual filters
  • Future need to turn the workflow into a reusable pipeline
solution architecture

Front-end

Matplotlib charts showing the churn rate by range of each attribute against the dataset average, plus a comparison with and without each risk factor.

Back-end

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.

Database

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.

security and safeguards

ID removal

The customer ID was removed before analysis, reducing noise and avoiding a field with no direct analytical value.

Handling incomplete records

Rows with missing data were dropped to avoid distorting calculations and charts.

Interpretive limits

Results are treated as exploratory analysis, not automatic proof of causation. The final reading needs business validation.

process
01

Importing the data

Reading the CSV file and a first inspection of the table with Pandas.

02

Initial cleaning

Removing duplicates and the CustomerID, standardizing text and dropping impossible or missing values.

03

Exploratory analysis

Calculating the overall churn rate (33.6%) and the rate by range of each attribute.

04

Testing hypotheses

Applying filters to test the impact of age, support calls, payment delay, contract type and total spend.

05

Final reading

Summarizing the factors most associated with churn and pointing to ways to reduce it.

behind the scenes
notebooks/analise_cancelamentos.ipynb Python section 3 · cleaning
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)")
Removes duplicates and the ID, standardizes text, treats impossible values as missing and drops incomplete rows.
notebooks/analise_cancelamentos.ipynb Python section 5 · charts
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()
Calculates the churn rate by range for each column and draws the charts with the dataset average as a reference.
notebooks/analise_cancelamentos.ipynb Python sections 6 and 7 · hypotheses
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")
Each hypothesis becomes a filter: the rate with and without the factor shows its weight, and the group with no factors shows the size of the opportunity.
impact

Results

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.

Learnings

The main takeaway was turning data cleaning and exploration into actionable insight, while being careful not to confuse correlation with causation.