Chinh (lelouvincx) / [Internal] Customers' MRR (Exchange Rate)

• Why does MRR on bi.holistics.io differ from Zoho Billing?
• Which exchange rate methodology should we use for multi-currency subscriptions?
• How do we handle customers with multiple tenants or multi-region setups?

flowchart TD
    subgraph Sources["Data Sources"]
        Holistics["Holistics Backend DB\n(Tenants, Users, Trial Submissions)"]
        Zoho["Zoho Billing\n(Subscriptions, Invoices, Exchange Rates)"]
        HubSpot["HubSpot CRM\n(Companies)"]
    end

  subgraph Pipeline["dbt Pipeline"]
      stg_tenants["stg_holistics__tenants"]
      stg_users["stg_holistics__users"]
      stg_trials["stg_holistics__trial_submissions"]
      stg_zoho["stg_zoho__subscriptions"]
      stg_hs["stg_hubspot__companies"]

      domain["Domain Mapping\n(tenant → domain_name)"]
      customer_id["itg_mappings__customer_identities\n(Holistics ↔ Zoho ↔ HubSpot)"]
      dim_customer["dim_customers\n(primary tenant selection)"]
      fct_mrr["fct_mrr\n(MRR calculation)"]
  end

  subgraph Issues["🔴 Known Issues (9 problems)"]
      I1["1.1/1.2: Exchange Rate Errors\n(static rate, multi-step conversion)"]
      I2["2.1-2.4: Wrong Tenant↔Company Mapping\n(region bug, domain mismatch,\nduplicates, multi-domain)"]
      I3["3.1/3.2: Wrong Tenant↔Zoho Mapping\n(manual errors, multi-tenant sub)"]
      I4["4: Active but Unpaid Customers\n(Zoho subscription logic bug)"]
  end

  subgraph Output["Reports"]
      bi_h["bi.holistics.io\nMRR Overview Dashboard"]
      monitor["Customer Identity\nMonitoring Dashboard"]
  end

  Holistics --> stg_tenants & stg_users & stg_trials
  Zoho --> stg_zoho
  HubSpot --> stg_hs

  stg_tenants & stg_users & stg_trials --> domain
  domain --> customer_id
  stg_hs --> customer_id
  stg_zoho --> customer_id
  customer_id --> dim_customer
  dim_customer --> fct_mrr
  fct_mrr --> bi_h
  customer_id --> monitor

    I1 -.->|affects| fct_mrr
    I2 -.->|affects| customer_id
    I3 -.->|affects| customer_id
    I4 -.->|affects| stg_zoho

Data Sources

• HubSpot CRM: Companies.
• Holistics Backend DB: Tenants, Users, Trial Submissions.
• Zoho Billing: Zoho Customers, Subscriptions, Payments, Invoices, Exchange Rates.

dbt Pipeline Flow

• Staging: stg_holistics__tenants, stg_holistics__users, stg_zoho__subscriptions, stg_hubspot__companies
• Mapping: itg_mappings__customer_identities (Holistics ↔ Zoho ↔ HubSpot)
• Dimensions: dim_customers (primary tenant selection logic)
• Facts: fct_mrr (final MRR calculation)

Identity Resolution

• Currently relies on domain_name (extracted from tenant uname or email). This is fragile.
• Proposed long-term fix: a canonical customer_skey shared across HubSpot and Zoho.

ER Diagram

• dbdiagram.io — Concepts

Key Datasets & Dashboards

• Datasets:
  • DTS 100: Customer and Revenue
• Dashboards:
  • MRR Overview: https://bi.holistics.io/dashboards/v4/28300-0-mrr-overview
  • Customer Identity Monitoring: https://bi.holistics.io/dashboards/v4/45480-customer-identity-monitoring
  • MRR Bucket Breakdown: https://bi.holistics.io/dashboards/v4/41188-adhoc-mrr-bucket-breakdown

• map_holistics_zoho.csv: maps specific tenants (e.g., Datacubed) to shared Zoho accounts.
• partner_programs.csv: excludes freemium plan IDs from MRR.
• dom_holistics__internal_tenants.sql: filters out internal Holistics testing tenants.