LinkedIn-Analytics-Dashboard/visitor_history.py

"""Persists every weekly Visitors export's category breakdown (Location/Seniority/Company
size) as an append-only observation log, so a location that drops out of one week's export
(LinkedIn only shows a category if it clears a minimum-visits threshold that week) isn't lost
just because a later export didn't happen to include it.

Each observation is one (file date, category type, category value, count) reading. Nothing
is ever overwritten — a category's running total "as of" any given month is the sum of every
observation for it up to that date. This is a running additive tally, not a deduplicated
unique-visitor count: LinkedIn's per-export numbers are a rolling window (not a fresh daily
count), so consecutive weekly exports likely overlap in which underlying visits they're
counting. Treat the totals here as a cumulative activity signal, not a precise headcount.

Zero import-time dependency on app.py, same reason growth.py has none: app.py runs as
"__main__", so `import app` here would re-execute it as a second module copy still mid-import
when these functions get called.
"""
import json
import os

import pandas as pd

_SHEET_TYPES = {
    "location": "Location",
    "seniority": "Seniority",
    "company_size": "Company size",
}


def _history_path(upload_dir):
    return os.path.join(upload_dir, "visitor_category_history.json")


def load_history(upload_dir):
    path = _history_path(upload_dir)
    if not os.path.exists(path):
        return []
    with open(path) as f:
        return json.load(f)


def _save_history(upload_dir, history):
    with open(_history_path(upload_dir), "w") as f:
        json.dump(history, f, indent=2)


def backfill_history(upload_dir, visitor_files):
    """Scans every visitor export file and appends any observation not already recorded
    (keyed by exact file date + category type + category value, so re-running this on
    every app refresh never double-counts the same file twice)."""
    history = load_history(upload_dir)
    seen_keys = {(h["file_date"], h["type"], h["category"]) for h in history}
    added = 0

    for fp in visitor_files:
        file_date = pd.Timestamp(os.path.getmtime(fp), unit="s").strftime("%Y-%m-%d")
        for type_key, sheet_name in _SHEET_TYPES.items():
            try:
                df = pd.read_excel(fp, sheet_name=sheet_name)
            except ValueError:
                continue  # sheet missing from this particular export, skip it
            col = df.columns[0]
            for _, row in df.iterrows():
                category = str(row[col])
                key = (file_date, type_key, category)
                if key in seen_keys:
                    continue
                history.append({
                    "file_date": file_date,
                    "type": type_key,
                    "category": category,
                    "count": int(row["Total views"]),
                })
                seen_keys.add(key)
                added += 1

    if added:
        _save_history(upload_dir, history)
    return history


def rollup_as_of(history, type_key, on_or_before=None):
    """Running additive total per category for one breakdown type, summed across every
    observation on or before `on_or_before` (a 'YYYY-MM-DD' string or None for everything)."""
    rows = [h for h in history if h["type"] == type_key]
    if on_or_before is not None:
        rows = [h for h in rows if h["file_date"] <= on_or_before]
    if not rows:
        return pd.DataFrame(columns=["Category", "Total views"])
    df = pd.DataFrame(rows)
    out = df.groupby("category", as_index=False)["count"].sum()
    out.columns = ["Category", "Total views"]
    return out.sort_values("Total views", ascending=False).reset_index(drop=True)


def available_months(history):
    """Every (year, month) with at least one observation, sorted oldest first."""
    months = {tuple(int(p) for p in h["file_date"].split("-")[:2]) for h in history}
    return sorted(months)