Building a cross-affiliate customer-interest layer with LLM labelling, affinity scores and Streamlit

Each business in a group holds data on products, customers, content, purchases and service usage, but a single business's data never shows the whole person. A customer who buys barrier-repair skincare in one place may watch wellness documentaries in another and buy probiotics in a third — a pattern that is invisible inside any one company. The obvious fix, mapping categories to categories, fails quickly: the businesses share no product codes or category names, their category trees differ in depth and meaning, and streaming content has no retail category at all.

In this post we walk through the proof of concept we built instead. It extracts monthly aggregates from three affiliates with SQL and asks an LLM to label every product and content item with one to five interests from a shared 100-interest taxonomy, dropping anything outside it. Labelled activity rolls up into a customer × interest affinity mart in pandas; segment-level scores blend intensity and penetration and must pass t-tests and two-proportion z-tests before they are shown; segments are stored as rules; an LLM turns a segment's profile into a persona; and a Streamlit dashboard puts it all in front of marketing and planning teams.

Solution overview

The PoC is a monthly batch pipeline feeding an interactive dashboard. Extraction, labelling and the affinity mart run offline and write their results to parquet and pickle files. The dashboard reads those files, recomputes segments when a screen opens, and scores and tests interests for whatever segment the user is looking at.

The numbered steps in the diagram:

1. Purchases, viewing, subscriptions and demographics are aggregated per month in SQL for each of the three affiliates, together with item and content metadata.
2. Item metadata — name, category path, brand, genre, director, synopsis — goes to an LLM with the taxonomy and its definitions; it returns one to five labels, and labels outside the taxonomy are dropped.
3. Labels are melted to rows and joined to activity in pandas; each customer gets an interest share and a within-label percentile, written to parquet.
4. For a segment, each interest gets a 0–10 score blending intensity and penetration, and is kept only if a t-test says the difference is real; categories, brands and items are compared with two-proportion z-tests.
5. Segments are stored as rules — named conditions plus a boolean expression — and recomputed on open; an LLM writes a persona from a segment's top interests and demographics.
6. A Streamlit app with a login, AG Grid tables, plotly charts and word clouds covers affiliate insight, persona analysis, content features and product attributes.

Technology stack

Step 1: Extract monthly aggregates from each affiliate

Each affiliate's data stays in its own systems and its own shape. The PoC does not try to merge raw logs; it pulls monthly aggregates per customer and item — purchases from the retail businesses, viewing and subscriptions from the streaming business — plus the demographics every analysis needs (age band and gender) and the metadata that describes each item: name, category hierarchy and brand for products; genre, director and synopsis for content.

A monthly grain keeps extracts comparable across businesses whose raw event volumes differ widely, while still letting interests be followed over time. The metadata is the input to the next step; the activity is what the labels will later be attached to.

-- One row per customer, item and month for one affiliate.
SELECT a.customer_id,
       a.item_id,
       DATE_TRUNC('month', a.event_time) AS month,
       COUNT(*)                          AS events,
       SUM(a.amount)                     AS spend
FROM   activity AS a
WHERE  a.event_time >= :month_start
  AND  a.event_time <  :month_end
GROUP  BY a.customer_id,
          a.item_id,
          DATE_TRUNC('month', a.event_time);

Simplified. Table and column names are generic; each affiliate has its own extract.

Step 2: Label every item with one shared interest taxonomy

The common language is a taxonomy of 100 interests in 11 groups, plus an "unrelated" label for items that say nothing about a person's interests. Every interest has a written definition. Interests describe the person, not the shelf, which is why the same label can attach to a cream, a humidifier and a documentary.

An LLM receives an item's metadata together with the taxonomy and its definitions and returns one to five labels. The answer is then validated against the taxonomy: any label not on the list — a synonym, a near-miss spelling, an invented interest — is dropped. Constrained output plus a set-membership check is what keeps labels consistent across businesses and across runs.

import json

TAXONOMY = load_taxonomy()     # {interest: definition}: 100 interests + "unrelated"
ALLOWED = set(TAXONOMY)
FIELDS = ("name", "category_path", "brand", "genre", "director", "synopsis")

def label_item(item, llm, max_labels=5):
    """Return up to five taxonomy labels for one product or content item."""
    meta = {k: item[k] for k in FIELDS if item.get(k)}
    raw = llm.generate(render_prompt(meta, TAXONOMY))      # template not shown
    try:
        labels = json.loads(raw)["labels"]
    except (ValueError, KeyError, TypeError):
        return []                                          # unparseable: no labels
    valid = [lab for lab in dict.fromkeys(labels) if lab in ALLOWED]   # dedupe + validate
    return valid[:max_labels]

Simplified. llm.generate stands for an LLM call and render_prompt for a template that is not shown.

Why a fixed taxonomy? Free-form tags drift: the same idea gets three names and nothing joins. A closed list with definitions turns labelling into classification, makes validation a set lookup, and gives every downstream score the same columns in every business.

Step 3: Build the customer × interest affinity mart

With labels in place, every activity row inherits its item's labels. The mart melts the label columns into rows, joins them to monthly activity, and computes two numbers per customer and interest:

n(c, l)     = activity rows of customer c whose item carries label l
share(c, l) = n(c, l) / Σ_l' n(c, l')                   how much of c's activity is about l

pct(c, l)   = 100 · (N_l − rank_l(c) + 1) / N_l          N_l = customers holding l
                                                         rank_l(c) = 1 for the largest share

The share says how much of a customer's activity is about an interest; the within-label percentile says how strong that is compared with everyone else who holds the interest at all. Percentiles put interests with very different base rates on one scale: a 5% share can be unremarkable for one interest and exceptional for another.

import pandas as pd

def affinity(activity, item_labels):
    """activity: customer, item, month rows; item_labels: item + label_1..label_5."""
    long = (item_labels.melt(id_vars="item", value_name="label")
            .dropna(subset=["label"]).drop(columns="variable"))
    rows = activity.merge(long, on="item")             # one row per activity x label
    n = rows.groupby(["customer", "label"]).size()
    share = (n / n.groupby(level="customer").transform("sum")).rename("share")
    aff = share.reset_index()
    by_label = aff.groupby("label")["share"]
    N = by_label.transform("size")                     # customers holding the label
    rank = by_label.rank(method="min", ascending=False)   # 1 = largest share
    aff["pct"] = 100 * (N - rank + 1) / N
    return aff                                         # long: customer x label

aff = affinity(activity, item_labels)
aff.to_parquet("affinity.parquet", index=False)

Simplified. activity is the monthly aggregate from Step 1; item_labels holds the validated labels from Step 2.

Step 4: Score interests for a segment and keep only real differences

Marketing teams ask a simple question — what does this segment care about, more than everyone else? — and the answer has to be both readable and not an artefact of segment size. Each interest gets a 0–10 score that blends intensity (the segment's mean percentile score) with penetration (the share of the segment holding the interest), each min-max scaled to 0–10 across interests:

score(l | S) = 0.25 · MM₁₀( mean pct(c, l), c ∈ S )  +  0.75 · MM₁₀( |{c ∈ S : c holds l}| / |S| )

MM₁₀(v)      = 10 · (v − min v) / (max v − min v)                      across interests

keep l       if a one-sample or Welch t-test on the segment's scores gives p < 0.05
categories, brands, items:   two-proportion z-test, segment vs the rest

The weighting favours penetration — how many in the segment share an interest — over how intensely a few hold it. The significance filter then removes interests that look different only because the segment is small. Categories, brands and items are counts of buyers rather than scores, so they are compared with a two-proportion z-test instead.

import numpy as np
import pandas as pd
from scipy import stats

def mm10(v):
    span = v.max() - v.min()
    return 10 * (v - v.min()) / span if span > 0 else v * 0.0

def segment_scores(aff, segment, alpha=0.05):
    """aff: long customer x label with 'pct'; segment: set of customer ids."""
    rows = []
    for label, g in aff.groupby("label"):
        inside = g["customer"].isin(segment)
        a, b = g.loc[inside, "pct"], g.loc[~inside, "pct"]
        if len(a) < 2 or len(b) < 2:
            continue
        p = stats.ttest_ind(a, b, equal_var=False).pvalue         # Welch
        rows.append({"label": label, "intensity": a.mean(),
                     "penetration": len(a) / len(segment), "p": p})
    t = pd.DataFrame(rows)
    t["score"] = 0.25 * mm10(t["intensity"]) + 0.75 * mm10(t["penetration"])
    return t[t["p"] < alpha].sort_values("score", ascending=False)

def two_proportion_z(x1, n1, x2, n2):
    """Segment vs the rest for a category, brand or item: x buyers out of n."""
    p = (x1 + x2) / (n1 + n2)
    z = (x1 / n1 - x2 / n2) / np.sqrt(p * (1 - p) * (1 / n1 + 1 / n2))
    return z, 2 * stats.norm.sf(abs(z))

Simplified. Welch's test compares the segment with everyone else; the one-sample variant compares the segment with the overall mean.

Step 5: Store segments as rules and turn profiles into personas

A segment is saved as a rule, not as a list of customer IDs: a set of named conditions — an interest percentile above a threshold, an age band, activity in a given business — and a boolean expression that combines them. The rule is evaluated again whenever a dashboard screen opens, so segments follow new monthly data without anyone rebuilding them.

from dataclasses import dataclass
import pandas as pd

@dataclass
class Segment:
    name: str
    conditions: dict      # condition name -> query over the customer table
    expr: str             # boolean combination of condition names

    def members(self, customers: pd.DataFrame) -> set:
        """Evaluated when a screen opens, against the latest monthly data."""
        masks = {k: customers.eval(q) for k, q in self.conditions.items()}
        hit = pd.eval(self.expr, local_dict=masks)
        return set(customers.loc[hit, "customer"])

example = Segment(
    name="Skin health, plus documentaries or 30s-40s",
    conditions={"skin": "pct_skin_health >= 80",
                "docs": "pct_documentaries >= 80",
                "age": "age_band in ['30s', '40s']"},
    expr="skin & (docs | age)")

def persona(profile: pd.DataFrame, demographics: dict, llm) -> str:
    """Short persona text from a segment's top interests and demographics."""
    return llm.generate(render_persona_prompt(profile.head(10), demographics))

Simplified. Column names and the example segment are invented; the persona prompt template is not shown.

Personas are where the LLM comes back. A segment's top interests and its demographics go to an LLM, which writes a short persona description a planner can read at a glance — moving the conversation from "which products sell" to "who these customers are".

Step 6: Serve it as a self-serve dashboard

The front end is a Streamlit app behind a login built with streamlit-authenticator. Its screens follow the questions the business asked: affiliate-level insight, persona analysis, content feature extraction and product attribute analysis. Tables use AG Grid for sorting and filtering, charts use plotly, and word clouds give a quick visual summary.

import pandas as pd
import plotly.express as px
import streamlit as st
import streamlit_authenticator as stauth
from st_aggrid import AgGrid, GridOptionsBuilder

auth = stauth.Authenticate(load_credentials(), "insight_app", cookie_key(), 1)
auth.login()
if not st.session_state.get("authentication_status"):
    st.stop()

aff = pd.read_parquet("affinity.parquet")
customers = pd.read_parquet("customers.parquet")
seg = st.selectbox("Segment", saved_segments(), format_func=lambda s: s.name)
ids = seg.members(customers)                           # re-evaluated on every open
scores = segment_scores(aff, ids)

st.metric("Customers in segment", f"{len(ids):,}")
st.plotly_chart(px.bar(scores.head(15), x="score", y="label", orientation="h"),
                use_container_width=True)
grid = GridOptionsBuilder.from_dataframe(scores)
grid.configure_pagination(paginationAutoPageSize=True)
AgGrid(scores, gridOptions=grid.build())

Simplified. Credentials, cookie settings and file paths are placeholders; segment_scores is the function from Step 4.

The point of the dashboard was not the charts but the change in question: from product- and content-level descriptive metrics to customer behaviour and persona-based understanding across businesses.

Results

The project built an integrated analytics dashboard that enabled cross-affiliate customer insight. It shifted analysis from product- and content-level descriptive metrics to customer behaviour and persona-based understanding, and it made the value case concrete enough to support a group-level data-platform investment and a service launch.

Because the shared layer is a taxonomy rather than a mapping between particular category trees, a new business can join by labelling its items against the same list.

Lessons learned

• Label the person, not the shelf. Interests that describe people made items from unrelated catalogues comparable where category-to-category mapping could not.
• Close the LLM's vocabulary. A fixed taxonomy with definitions, plus validation that drops anything else, turned open-ended tagging into consistent classification.
• Blend, then test. A readable 0–10 score is only useful when it is filtered for significance; small segments otherwise produce confident-looking noise.
• Store segments as rules. Rules recomputed on open keep segments current as monthly data arrives.
• The taxonomy caps everything. Labelling quality limits every number downstream, and the definitions matter as much as the model.

Conclusion

Customers of different businesses become comparable once their items speak one language. An LLM constrained to a 100-interest taxonomy provided that language; an affinity mart, significance-filtered scores, rule-based segments and LLM personas turned it into answers a marketing team could use; and a Streamlit dashboard made those answers self-serve.

The same approach — a closed taxonomy, LLM labelling with validation, and simple, tested affinity scores — applies wherever several catalogues describe the same customers in incompatible terms.

Limitations

• Insight is only as good as the labelling; ambiguous items and thin metadata produce noisy interest profiles.
• Cross-business comparison needs customers who appear in more than one business, and that overlap is uneven.
• Many interests, categories and brands are tested at once, so a p < 0.05 filter is a screen rather than proof; results are leads for follow-up.
• In the live model the item labels are prepared in advance and the persona text is templated, where production used an LLM for both; the cohort-lift view was added for this site, and the numbers come from a generator that plants interest structure, so they say nothing about real customers.

About the demo and confidentiality

Businesses, items, categories, labels and customers in the embedded model are generated. No customer, product, database, table or column name, prompt, model configuration, data volume or affiliate-level result from the real project appears in this post. Code is simplified and written for illustration.