subscription.mrr / subscription.exchange_rateflowchart 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
stg_holistics__tenants, stg_holistics__users, stg_zoho__subscriptions, stg_hubspot__companiesitg_mappings__customer_identities (Holistics ↔ Zoho ↔ HubSpot)dim_customers (primary tenant selection logic)fct_mrr (final MRR calculation)domain_name (extracted from tenant uname or email). This is fragile.customer_skey shared across HubSpot and Zoho.| Category | Problem | Root Cause |
|---|---|---|
| Exchange Rate | 1.1/1.2: Inaccuracies | Pipeline uses static rates; Zoho uses per-transaction rates set at plan creation |
| HubSpot Mapping | 2.1–2.4: Mapping Bugs | Missing regions, domain mismatches (e.g., raft.ai vs vector.ai), duplicate HubSpot companies |
| Zoho Mapping | 3.1/3.2: Link Errors | Manual errors and system inability to handle multi-tenant subscriptions |
| Subscription Logic | 4: Unpaid Actives | Zoho fails to re-activate tenants after past-due payments are settled |
vector.ai, app uses raft.ai.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.customer_skey for long-term identity resolution
SG-2163)[{'hubspot_company_id': 4018874875, 'all_domain_names': [togltechnology.com, togalimited.com]}, {'hubspot_company_id': 10207389977, 'all_domain_names': [togalimited.com]}]
MRR project transfer — status update after handoff from anh Hieu (due Apr 15). Any open questions or risks from the transfer.
Presales capacity & portfolio review — currently active on 5+ customer accounts (Showbie, Jonas Chorum, Basata, Innerspace, Superbexperience). Request alignment on which accounts to prioritize vs. hand off. Discuss whether the embed demo app should become an official presales tool.
Meeting context: anh Hieu left this period; presales surged to dominant project; DAT-576 MRR exchange rate is 5-week carry-over. Two backlog drops on the table. Bring 3 prioritization options for DAT-576 — this is a prioritization ask, not a scheduling ask.
const_zoho_internal_customers filter to stg_zoho__events (other Zoho staging models already apply it). Arden already confirmed Quinn US test + Holistics SGD test accounts are the missing ones. Owner: nobody since W7. Decision: do it now (~30 min) or drop formally. Linear DAT-524.conceptual-differences.md + migration-overview.md. Reusable. Should it become the template for future migration-flavored deals (Tableau, Looker, Sigma)?dbt deprecation 3-phase rule should land in holistics/dbt contributing guide. Customer-comms recipe pattern should land in presales/CS onboarding doc. Ask: is this welcome, or noise?| Item | Question | Default if no decision |
|---|---|---|
| DAT-576 MRR | Re-scope, hand off, or de-prioritize? | Continue slipping — bad outcome |
| DAT-524 Zoho | Drop or assign? | Drop |
| Career lane | Confirm hybrid direction? | Continue current pattern |
| Heuristics upstream | Welcome or noise? | Keep personal |
| Item | Since | Weeks | Action |
|---|---|---|---|
| DAT-576 MRR exchange rate | W16 (Apr 13) | 5 | Escalate as prioritization question |
| DAT-524 exclude internal Zoho accounts | W7 (Feb 9) | 14 | Drop formally |
| Showbie loss synthesis | W19 (May 4) | 2 | Owed — close this week |
| H1 self-reflection + CV refresh | W19 (May 4) | 2 | Lock May 23–24 |
| Wamly onboarding | W18 (Apr 27) | 3 | Customer-side block — Chukwudi re-engaging |
dbt #853, internal-aml-project #78, dbt #854, dbt #858, prefect #400, dbt #860, dbt #864, dbt #867, internal-aml-project #77fct_job_queue_performance, 4w), DAT-283 (Calendly, 8w)conceptual-differences.md + migration-overview.md), holistics-embed-demo continuingcurrent_price = promotional_price, else current_price = rrpcurrent_price should always track the Shelf Price, not the RRP, unless they happen to be identical.retailer_price1 - (current_price / NULLIF(was_price, 0))was_price - current_priceMIN(current_price)AVG(discount_pct)AVG(rrp)AVG(promotional_price)TODO Handle multi-currency exchange rates for MRR
Current pricing setup
Customer payment flow:
DONE Unsub Contabo VPS due to unused
DONE Continue Cross-Model Calculation
DONE Review AI sharing dashboard for Aurora
user_id only), NOT the production DB. This means user-friendly info (names, emails) and dashboard/dataset-level tracking aren’t directly available.user_id is fine, they’ll map it themselves.((69bcb599-a91e-49ea-bc3d-6a5a651f03df))