Visualisation, databases, APIs, BI integration and first models in Python

Visualisation with matplotlib

import pandas as pd, matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt

orders = pd.read_csv("retail/orders.csv", parse_dates=["order_date"])
items = pd.read_csv("retail/order_items.csv")
lines = items.merge(orders[["order_id", "order_date", "status", "channel"]], on="order_id")
lines["rev"] = lines["quantity"] * lines["unit_price"] * (1 - lines["discount_pct"])
monthly = (lines[lines["status"] == "completed"]
           .groupby(lines["order_date"].dt.to_period("M"))["rev"].sum())

fig, ax = plt.subplots(figsize=(9, 3.5))
monthly.plot(ax=ax, color="#0B1530")
ax.set_title("Orion Retail — monthly net revenue")
ax.set_ylabel("Net revenue")
ax.spines[["top", "right"]].set_visible(False)
fig.tight_layout()
fig.savefig("/tmp/monthly_revenue.png", dpi=120)
print(monthly.idxmax(), round(monthly.max()), "| lowest:", monthly.idxmin(), round(monthly.min()))

2025-12 145452 | lowest: 2023-02 47929

The chart shows a growing trend with a December peak every year and a February trough. Principles from the dashboard lesson apply: title says what it is, axes labelled, no chart junk. seaborn and plotly offer higher-level statistical and interactive charts.

Databases and SQL integration

Python talks to databases through drivers (sqlite3, psycopg for PostgreSQL, pyodbc for SQL Server) or SQLAlchemy. Let the database do heavy filtering/aggregation and pandas the rest:

import sqlite3
con = sqlite3.connect("../../data/sqlite/telecom.sqlite")
query = """
SELECT contract_type, COUNT(*) AS subscribers, ROUND(AVG(churned) * 100, 1) AS churn_pct
FROM subscribers
GROUP BY contract_type
ORDER BY churn_pct DESC
"""
df = pd.read_sql_query(query, con)
print(df)
min_score = 70
high = pd.read_sql_query("SELECT source, COUNT(*) AS n FROM leads WHERE lead_score >= ? GROUP BY source", con, params=(min_score,))
print(high.sort_values("n", ascending=False).head(3))
con.close()

  contract_type  subscribers  churn_pct
0       monthly         2076       55.7
1      24-month         2088       37.8
2      12-month         4146       35.4
         source     n
2  partner_shop  1216
5       website   855
4    social_ads   795

Monthly contracts churn far more than 12- and 24-month contracts — a finding you will revisit in statistics (is the difference reliable?) and decision-making (is it causal?).

APIs and JSON

Many sources (CRMs, ad platforms, exchange rates) expose REST APIs returning JSON:

import json
payload = '{"campaigns": [{"id": 1, "name": "Spring", "metrics": {"clicks": 1200, "spend": 300.5}}, {"id": 2, "name": "Summer", "metrics": {"clicks": 2500, "spend": 610.0}}]}'
data = json.loads(payload)
flat = pd.json_normalize(data["campaigns"])
flat["cpc"] = flat["metrics.spend"] / flat["metrics.clicks"]
print(flat)

   id    name  metrics.clicks  metrics.spend       cpc
0   1  Spring            1200          300.5  0.250417
1   2  Summer            2500          610.0  0.244000

With a real API: requests.get(url, params=..., headers={"Authorization": f"Bearer {token}"}, timeout=30), check response.status_code, handle pagination and rate limits, and log failures.

Power BI integration

Prefer the database or file route: Power BI stays a presentation layer, and Python stays testable.

Version control with Git

Analysis code changes; Git records who changed what and why. Minimum workflow: git init, git add file.py, git commit -m "Add churn features", push to GitHub/Azure DevOps, use branches and pull requests for review. Add .gitignore for data files, credentials and .ipynb_checkpoints. Notebooks diff poorly — move stable logic into .py modules.

A first model: train, test, evaluate

Predict whether a telecom subscriber churned from signup-time attributes. The model must be evaluated on data it did not see during training.

import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score, accuracy_score

subs = pd.read_csv("telecom/subscribers.csv")
X = pd.get_dummies(subs[["contract_type", "payment_method", "region", "acquisition_channel"]], drop_first=True)
X["age"] = subs["age"]
y = subs["churned"]

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.25, random_state=42, stratify=y)
model = LogisticRegression(max_iter=1000).fit(X_train, y_train)
p_test = model.predict_proba(X_test)[:, 1]

print("baseline accuracy (always 'no churn'):", round(1 - y_test.mean(), 3))
print("model accuracy:", round(accuracy_score(y_test, p_test >= 0.5), 3))
print("test AUC:", round(roc_auc_score(y_test, p_test), 3))
coefs = pd.Series(model.coef_[0], index=X.columns).sort_values()
print(coefs.round(2).head(3)); print(coefs.round(2).tail(3))

baseline accuracy (always 'no churn'): 0.589
model accuracy: 0.638
test AUC: 0.635
acquisition_channel_referral       -0.76
acquisition_channel_social_ads     -0.76
acquisition_channel_door_to_door   -0.50
dtype: float64
acquisition_channel_shop        0.31
payment_method_cash_terminal    0.31
contract_type_monthly           0.82
dtype: float64

How to read this:

• Baseline first: always predicting the majority class already achieves some accuracy. A model is only useful if it beats the baseline.
• AUC (0.5 = random, 1.0 = perfect ranking) measures how well the model ranks churners above non-churners.
• Coefficients: positive = higher churn odds. Month-to-month contracts and cash-terminal payments increase churn odds; these are associations, not proof that changing contracts would reduce churn.
• Feature engineering — the features came from categorical columns turned into 0/1 indicators (get_dummies). Usage and ticket history would add signal, but only if measured before the churn decision; using post-churn data is leakage (Module 11).

Practice

Summary

• matplotlib for clear static charts; label and simplify.
• Query databases with parameters; aggregate in SQL, analyse in pandas.
• Flatten JSON with json_normalize; handle API pagination, limits and auth securely.
• Integrate with Power BI through governed tables or files.
• Version code with Git; evaluate models on held-out data against a baseline.