# Affiliate Data Glossary

**Last Updated:** 2026-04-10

Definitions of data terms used across the affiliate partner dashboard, APIs, and sync. Use this when implementing or debugging data displays to avoid semantic confusion.

## Partner Record Fields (affiliate_partners.json)

| Field | Definition | Source |
|-------|------------|--------|
| **partner_id** | Unique partner identifier (e.g. AP-20260224-ABC123). | `partner.partner_id` |
| **email** | Partner email address. Used for login (email/password) and account linking (OAuth). | `partner.email` |
| **password_hash** | bcrypt hash of password. Null for OAuth-only partners. | `partner.password_hash` |
| **oauth_provider** | OAuth provider (e.g. `google`, `microsoft`). Null for email/password-only partners. | `partner.oauth_provider` |
| **oauth_id** | Provider's stable user ID (sub claim). Used for OAuth lookup and account linking. | `partner.oauth_id` |
| **oauth_email_verified** | Whether the provider attests the email is verified. Required for auto-linking existing accounts. | `partner.oauth_email_verified` |
| **terms_accepted_at** | ISO 8601 timestamp when partner accepted Partner-Vereinbarung and Datenschutzerklärung. Required for both traditional and OAuth registration (GDPR). | `partner.terms_accepted_at` |
| **hubspot_affiliate_record_id** | Optional. HubSpot CRM **Affiliate Partners** custom object record id (string). Written on successful partner create in HubSpot and on sync when missing; used for deep links from Programm-Analyse. Legacy alias: `hubspot_object_id` (backfill script). | Registry + sync |

OAuth-only partners have `password_hash` = null and must sign in via the provider. OAuth can be linked to existing email/password accounts when the provider email matches and is verified.

## Lead-Related Terms

| Term | Definition | Source |
|------|------------|--------|
| **Leads** | Contacts in HubSpot associated with a partner via the Affiliate Partner ID property. All contacts referred by the partner. | `cache.leads[partnerId]` |
| **Qualifiziert** | Leads with Marketing Qualified status (e.g. `lifecyclestage` = marketingqualifiedlead, or `hs_lead_status` contains "qualified"). | `leadCounts.qualified` |
| **In Verhandlung** | Leads in the opportunity/deal stage – i.e. `lifecyclestage` = opportunity. Shown in funnel as "Leads im Deal-Stadium (Opportunity)". **Not** the same as a HubSpot **deal** object; real subscription deals and MRR appear under **Earnings**. | `leadCounts.deal` |
| **Gewonnen** | Leads with lifecycle "customer" – converted to customers. | `leadCounts.won` |

## UTM / Campaign Terms (Optional on Leads)

| Term | Definition | Source |
|------|------------|--------|
| **utm_source** | UTM source parameter (e.g. `affiliate`, `partner`). Optional; null when not set. | `lead.utm_source` (HubSpot `source__c`) |
| **utm_medium** | UTM medium parameter (e.g. `referral`, `email`, `social`). Optional; null when not set. | `lead.utm_medium` (HubSpot `utm_medium__c`) |
| **utm_campaign** | UTM campaign parameter (e.g. `january2026`, `newsletter`). Optional; null when not set. | `lead.utm_campaign` (HubSpot `utm_campaign__c`) |
| **utm_content** | UTM content parameter. Optional; null when not set. | `lead.utm_content` (HubSpot `content__c`) |
| **utm_term** | UTM term parameter. Optional; null when not set. | `lead.utm_term` (HubSpot `utm_term__c`) |

## Deal-Related Terms

| Term | Definition | Source |
|------|------------|--------|
| **Deals (HubSpot)** | All HubSpot deal records linked to the partner via Affiliate Partner ID. Includes won, lost, and in-progress deals. | `cache.deals[partnerId]` |
| **Deals (KPI)** | Count of **Closed Won** deals that contribute to MRR – i.e. deals with a valid MRR amount and subscription status (active, paused, or cancelled). | `mrrData.total_deals` |
| **Deals (KPI display)** | Partner **Dashboard** and **Programm-Analyse** show the KPI label **„Deals“** as **open / won**: **open** = pipeline deals (linked, not Closed Won, not Closed Lost via `affiliateDealPipelineCountsForPartner`); **won** = same as Deals (KPI) / `total_deals`. | `kpis.deals_open_pipeline` + `mrrData.total_deals` |
| **Abschlüsse** | Same as Deals (KPI) – Closed Won deals that generate partner MRR. | `mrrData.total_deals` |
| **Open pipeline (deals)** | HubSpot deals linked to the partner that are neither Closed Won nor Closed Lost. | `affiliateDealPipelineCountsForPartner()` → `open_pipeline` |
| **Closed Lost (deals)** | Closed Lost stage (label / pipeline IDs). Counted for **admin** Programm-Analyse KPIs only. | `affiliateDealPipelineCountsForPartner()` → `closed_lost` |
| **deals_count** (Admin) | Raw count of all deals in cache for the partner – includes any deal linked to the partner, regardless of stage. | `count($deals)` |
| **deals_count** (Dashboard API) | Still the raw cache row count for the partner (`count($deals)`). | `affiliate-dashboard-data.php` `kpis.deals_count` |
| **closed_deals_count** (Dashboard API) | Same as Deals (KPI) – Closed Won with MRR. | `mrrData.total_deals` |

**Important:** The Admin list can show raw deal counts; the Dashboard KPI **„Deals“** shows **Offen / Gewonnen** (pipeline vs closed-won MRR). **Earnings** lists pipeline, won, and lost rows; commission columns apply only to **won** (`pipeline_kind === 'won'`).

## MRR Terms

| Term | Definition | Source |
|------|------------|--------|
| **MRR** | Monthly Recurring Revenue – partner's share of recurring revenue from their referred customers. | `mrrData.total_mrr` |
| **total_mrr** | Sum of partner MRR across all Closed Won deals, weighted by partner level (MRR share %). | `calculatePartnerMRR()` |
| **active_mrr** | MRR from deals with subscription status `active`. | `mrrData.active_mrr` |
| **paused_mrr** | MRR from deals with subscription status `paused`. | `mrrData.paused_mrr` |
| **cancelled_mrr** | MRR from deals with subscription status `cancelled`. | `mrrData.cancelled_mrr` |
| **mrr_summary** | Cached MRR data per partner, written by sync. Used by Admin list. Dashboards/Earnings compute MRR on-the-fly from deals. | `cache.mrr_summary[partnerId]` |

## Level Terms

| Term | Definition | Source |
|------|------------|--------|
| **Level** | Partner tier: beginner, starter, partner, pro. Determines MRR share percentage. | `affiliate-config.php` |
| **effective_level** | Level used for display and MRR – either manual override (if set and not expired) or calculated from rolling 90-day deal count. | `getEffectivePartnerLevel()` |
| **calculated_level** | Level computed from deals closed in the past 90 days. Used when no override applies. | `calculatePartnerLevel()` |

## Conversion Funnel

**Partner dashboard** (`affiliate-dashboard.js`) may still show a **volume + subset** bar pattern for the conversion chart; align with this glossary when changing it.

**Programm-Analyse** (`/partner/program-analytics`) uses **mutually exclusive** stage counts from `calculateLeadCounts()` / `determineLeadStatus()`:

1. **Neu** – Referred contacts not in MQL / opportunity / customer per `determineLeadStatus()`
2. **Qualifiziert** – MQL-style lifecycle / lead status
3. **In Verhandlung** – `lifecyclestage` = opportunity
4. **Gewonnen** – Customer-style lifecycle / lead status

Each cached contact appears in **exactly one** bucket. `new + qualified + deal + won` equals `total` (= `leads_total_cache`). The UI shows these four bars plus optional **conversion rate** pills from `funnel_snapshot.rates`.

The funnel does **not** replace the **Deals (KPI)** card (Closed Won deals with MRR).

**Trend charts** (Empfehlungen / Abschlüsse / geschätzter Partner-Umsatz nach Abschluss) share the page date range but use a separate **aggregation** (`grain`: day / week / month in the API). Buckets are assigned in **UTC**; see [PROGRAM_ANALYTICS_GUIDE.md](./PROGRAM_ANALYTICS_GUIDE.md) for coercion rules on very long ranges.

## Related Scripts

- **validate-affiliate-data.php** – Cross-checks leads_count, deals_count, MRR, and duplicate partner IDs across data sources.
- **validate-program-analytics-consistency.php** – Asserts Programm-Analyse funnel `stage_sum` matches `total` and trend lead bucket sums match `leads_in_period`.
- **validate-affiliate-deal-pipeline-invariants.php** – Per partner, `open + lost + closed_won_stages` equals linked deal count.
- **diagnose-sync-frederik.php** – Sync diagnostic for a specific partner.

## Related Documentation

- [CRON_SYNC_RUNBOOK.md](CRON_SYNC_RUNBOOK.md) – Sync process and diagnostic output
- [LEVEL_CALCULATION.md](LEVEL_CALCULATION.md) – Level progression logic