Skip to main content

Analyze table schemas for insights

User

easymanage get and study all table schema and suggest what insights and trends can be derived from tables

Cursor

I'll fetch the full database schema first so I can map relationships and then outline the insights and trends you can derive.

I fetched the full schema. I’ll map key entities/relationships and then list high‑value insights you can derive.

Core entities and relationships​

  • Accounts (AccountsVw)
    • Attributes: type, industry, revenue, employees, location, website, parentId, campaignId
    • Links:
      • to Contacts via AccountsContactsVw (many‑to‑many)
      • to Opportunities via AccountsOpportunitiesVw (many‑to‑many)
      • to Quotes via AosQuotesVw.billingAccountId
  • Contacts (ContactsVw)
    • Attributes: names, phones, addresses, title, department, GDPR fields, leadSource, reportsToId, birthdate, campaignId
    • Links:
      • to Accounts via AccountsContactsVw
      • to Cases via ContactsCasesVw
      • to Case updates via AopCaseUpdatesVw.contactId
      • to Quotes via AosQuotesVw.billingContactId
  • Opportunities (OpportunitiesVw)
    • Attributes: opportunityType, leadSource, amount, dateClosed, salesStage, probability, campaignId, currency
    • Links:
      • to Accounts via AccountsOpportunitiesVw
      • to Quotes via AosQuotesVw.opportunityId
  • Quotes (AosQuotesVw)
    • Attributes: billing/shipping, expiration, number, totals, taxes, shipping, currency
    • Links:
      • to Opportunities via opportunityId
      • to Accounts via billingAccountId
      • to Contacts via billingContactId
      • to Quote line items via AosProductsQuotesVw.parentId (with parentType)
  • Quote line items (AosProductsQuotesVw)
    • Attributes: productId, qty, list/unit/discount/total prices, VAT, currency, groupId
    • Links:
      • to Quotes via parentId (and parentType)
  • Cases (CasesVw)
    • Attributes: caseNumber, type, status, priority, resolution, accountId, state
    • Links:
      • to Accounts via accountId
      • to Contacts via ContactsCasesVw
      • to Case updates via AopCaseUpdatesVw.caseId
  • Case updates (AopCaseUpdatesVw)
    • Attributes: caseId, contactId, internal flag
  • Bridge tables
    • AccountsContactsVw (accountId, contactId)
    • AccountsOpportunitiesVw (accountId, opportunityId)
    • ContactsCasesVw (contactId, caseId)
  • Sales pipeline and conversion
    • Stage distribution, velocity: avg days between stages, cycle time by segment
    • Win rates and conversion by leadSource, campaignId, industry, account tier
    • Forecast accuracy: compare probability*amount to realized closed‑won amount
    • Slippage: opportunities with dateClosed push counts and causes (nextStep text mining)
  • Revenue and bookings
    • Booked revenue by month/quarter from opportunities (closed‑won) and quotes (accepted)
    • Multi‑currency normalization via amountUsdollar and quote USD fields
    • Campaign ROI: opportunities/quotes amount attributed to campaignId vs cost (if available)
  • Pricing and discounting
    • Discount rate distributions from AosProductsQuotesVw (list vs unit vs total)
    • Approval issues: analyze AosQuotesVw.approvalIssue vs discount thresholds
    • Margin proxy: unit price − cost price; margin by product, segment, rep
  • Product mix and attach
    • Top products by revenue/qty; attach rates across opportunities/accounts
    • Bundles: groupId patterns revealing commonly co‑sold items
    • Price sensitivity: correlation of discount to close rate/time
  • Account segmentation and health
    • Revenue and pipeline by industry, accountType, employees, parent hierarchies
    • Geographic trends from billing/shipping address fields
    • Account depth: number of contacts, opportunities, quotes, and cases per account
  • Contact engagement
    • Buying roles: title/department patterns among deals won vs lost
    • GDPR readiness: lawfulBasis coverage and dateReviewed recency
    • Phone/email completeness and do-not-call coverage by segment
  • Quote funnel
    • Quote creation → acceptance conversion; time to expiration; expiration‑related loss
    • Leakage between opportunity and quote stages; multiple quotes per opportunity analysis
  • Support and retention
    • Case volume/severity trends by account/industry/region
    • SLA metrics: time to first update/closure using AopCaseUpdatesVw.dateEntered and CasesVw.dateModified
    • Internal vs external updates mix (internal) and impact on resolution times
    • Case‑to‑churn proxies: rising cases per account preceding lower pipeline or close rates
  • Operational performance
    • Rep workload: assignedUserId spread across opportunities, quotes, cases
    • Data quality KPIs: missing leadSource, address completeness, orphaned records, delete flags
  • Compliance and audit
    • Change activity: using dateEntered/dateModified for audit heatmaps
    • GDPR: lawful basis/source coverage by region

Example analytical queries to start​

  • Win rate by lead source and industry
    • Join OpportunitiesVw with AccountsOpportunitiesVw → AccountsVw; group by leadSource, industry, salesStage ∈ {Closed Won, Closed Lost}.
  • Discount impact on conversion
    • AosProductsQuotesVw aggregated to quote level; join AosQuotesVw → OpportunitiesVw outcome; model win vs discount rate.
  • Product attach bundles
    • Within AosProductsQuotesVw, compute frequent itemsets by parentId and groupId.
  • SLA performance
    • For each CasesVw, compute time to first AopCaseUpdatesVw entry and time to closure; segment by priority and accountId.
  • Forecast bias
    • Compare monthly sum of amountUsdollar * probability vs actual closed‑won amountUsdollar.
  • Campaign attribution
    • Use campaignId across OpportunitiesVw, ContactsVw, AccountsVw to estimate multi‑touch or last‑touch models.

Suggested dashboards​

  • Executive sales: bookings, pipeline by stage, win rate, forecast vs actual, top opportunities.
  • Pricing & margin: discount heatmap, margin by product, approvals, price realization trends.
  • Product analytics: revenue by product, attach bundles, cross‑sell matrix.
  • Account health: revenue, pipeline, support cases, contact coverage, data quality score.
  • Support ops: case volume, SLA, backlog aging, internal vs external updates.

Data quality checks to implement early​

  • Validate currency normalization fields; ensure parentType for line items is “AOS_Quotes”.

  • Enforce referential integrity across bridge tables; detect deleted vs active records.

  • Normalize addresses and phone formats; deduplicate contacts; fill missing leadSource.

  • I’ve reviewed your schema relationships; next I can propose concrete SQLs or build a quick BI model (e.g., dbt star schemas) if you share your warehouse target.

  • Key takeaways:

    • You can quantify end‑to‑end funnel from campaign/contact → opportunity → quote → product lines → revenue.
    • Strong levers include discount discipline, product bundling, and SLA adherence, all measurable from the current tables.