SfSkills Domains Docs GitHub

Home / data / sales-reporting-data-model

sales-reporting-data-model

Use when designing or troubleshooting Salesforce sales reporting — covers Historical Trend Reporting, Reporting Snapshots, and Custom Report Types for pipeline and opportunity analysis. Trigger keywords: historical trending, opportunity snapshot, reporting snapshot, pipeline history, custom report type join. NOT for CRM Analytics (Tableau CRM / Einstein Analytics) or Marketing Cloud reports — use admin/einstein-analytics-basics.

Open the skill folder on GitHub

Sales Reporting Data Model

This skill activates when a practitioner needs to design, configure, or troubleshoot sales reporting mechanisms in Salesforce — specifically Historical Trend Reporting (HTR), Reporting Snapshots, and Custom Report Types (CRTs) for pipeline analysis and opportunity reporting. It covers the data model constraints, activation requirements, scheduling, and architectural tradeoffs for each mechanism. It does NOT cover CRM Analytics / Tableau CRM, Marketing Cloud Datorama, or SOQL query optimization.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

Historical Trend Reporting (HTR)

Historical Trend Reporting is a declarative Salesforce feature that captures daily snapshots of selected field values on Opportunity (plus Cases, Forecasting Items, and up to 3 custom objects — Leads are not supported). It enables trend-style reports such as "show me how this Opportunity's Amount and Stage changed over the past 90 days" without a custom snapshot architecture.

How it works under the hood: Salesforce stores trending data in a separate, internal system data store — not in a custom object. The data appears in the report builder as a special "historical" row for each date in a date range. You select which fields to trend in Setup > Historical Trend Reporting.

Key constraints:

Reporting Snapshots

Reporting Snapshots are a platform mechanism that runs a source report on a schedule and writes the results as records into a custom object (the "target object"). This allows you to store unlimited historical pipeline data and report on it with any standard report type — including historical comparisons going back years.

How it works:

  1. Define a source report (Tabular or Summary; each run records at most 2,000 new target-object records).
  2. Create a target custom object with fields matching the source report columns.
  3. Map source report columns to target object fields in the Reporting Snapshot configuration.
  4. Set a schedule (hourly, daily, weekly).
  5. Salesforce runs the source report at the scheduled time and inserts records into the target object.

Key constraints:

Custom Report Types (CRTs)

Custom Report Types allow report builders to create report types that join up to 4 related objects (versus the limited set of standard report types), and to control which fields are available in the report. They also support "records without related records" joins — essential for exception and gap analysis (e.g., "Accounts with no Open Opportunities").

Key features:

CRTs are the right tool for:

Common Patterns

Pattern: Forward-Looking Pipeline Trend With HTR

When to use: A sales operations team wants a weekly view showing how Opportunity Stage and Amount have changed over the past 60–90 days for open deals. The pipeline scope fits within 2–3 months.

How it works:

  1. Enable Historical Trend Reporting in Setup > Historical Trend Reporting. Select "Opportunity" and check the fields to track (e.g., Amount, CloseDate, StageName, plus any custom forecast fields). Save.
  2. Wait for data to accumulate — at minimum 2 data points are needed before trends are visible.
  3. In the Report Builder, create a new report with type "Opportunities with Historical Trending."
  4. Add a "Historical Date" filter scoping the trend window (e.g., "Last 90 Days").
  5. Add historical field columns (e.g., "Amount (Historical)," "Stage (Historical)") alongside current columns.
  6. Group by Opportunity Name and Historical Date to see field changes per deal over time.

Why not just use Reporting Snapshots: HTR is fully declarative with no custom object, no scheduler configuration, and no storage overhead. For a rolling 3-month window on Opportunity fields, HTR is lower-maintenance.

Why not use HTR for multi-year history: Anything older than the previous 3 months plus the current month is purged. Use Reporting Snapshots for multi-year pipeline history.

Pattern: Long-Term Pipeline History Archive With Reporting Snapshots

When to use: Leadership wants a quarterly review comparing current pipeline to the same date last year. The window exceeds HTR's retention cap. The scoped pipeline report yields fewer than 2,000 new target-object records per run.

How it works:

  1. Create a source Summary report scoped to the relevant pipeline view (e.g., "Open Opportunities by Stage and Owner," fewer than 2,000 new records per run).
  2. Create a custom object (e.g., Pipeline_Snapshot__c) with fields matching the source report columns. Use Currency for amount, Date for dates, Percent for probability — never Text for these types.
  3. In Setup > Reporting Snapshots, create a new snapshot. Map source report columns to target object fields.
  4. Set a schedule (e.g., daily at midnight). Use a service account as the Running User.
  5. Build reports and dashboards on Pipeline_Snapshot__c, filtering by Snapshot_Date__c to compare pipeline at different points in time.

Why not just use HTR: HTR retention is limited to the previous 3 months plus the current month. Reporting Snapshots accumulate indefinitely.

Pattern: Gap / Exception Report With Custom Report Type

When to use: A sales manager wants a report of Accounts that have no Opportunities created in the last 90 days — cold accounts at risk of churn.

How it works:

  1. In Setup > Report Types, create a new Custom Report Type. Primary object: Account.
  2. Add related object: Opportunities. For the join type, select "A records may or may not have related B records" (outer join).
  3. Set CRT status to "Deployed."
  4. Build a report using the new CRT. Add a cross-filter: "Accounts without Opportunities where Created Date > 90 days ago."
  5. The report returns Accounts that have no Opportunities meeting the criteria.

Why not use a standard report type: Standard "Accounts with Opportunities" report types use inner join semantics — they only return Accounts that have at least one Opportunity. The "without" join in a CRT is required for exception/gap queries.

Decision Guidance

SituationRecommended ApproachReason
Need to see how deal stage and amount changed over last 3 monthsHistorical Trend ReportingDeclarative, zero storage cost, built for rolling trend windows on Opportunity
Need pipeline snapshot history going back more than 3 monthsReporting Snapshots to a custom objectHTR keeps only the previous 3 months plus the current month; Snapshots store indefinitely
Source report for Snapshot would insert more than 2,000 new rows per runSegment into multiple smaller source reports or use Apex-based snapshot logicA run records at most 2,000 new target-object records; the surplus is dropped and Run History shows a partial error
Need to join 3 or more related objects in a single reportCustom Report Type with multi-object chainStandard report types cap at 2 objects; CRTs support up to 4
Need to report on Accounts with NO related OpportunitiesCustom Report Type with "without" joinOnly CRTs offer outer-join / gap-analysis semantics
Need to track a 4th custom field on Opportunity in HTREvaluate field priority carefully; 8-field cap is hardHTR allows 5 standard + 3 custom Opportunity fields; remove a lower-priority field to add a new one
Need real-time intra-day pipeline snapshotsApex-based custom snapshot logic or Data CloudReporting Snapshots run at most hourly and record at most 2,000 new records per run
Need to report on historical pipeline across multiple currenciesUse Currency field type on Snapshot target objectMulti-currency orgs require Currency field type, not Text; HTR handles currency natively

Step-by-step instructions for an AI agent or practitioner working on sales reporting data model tasks:

  1. Clarify the time horizon and scope: Determine how far back history is needed (3 months or less vs. multi-year). Confirm whether the pipeline scope (filtered Opportunity count) yields fewer than 2,000 new target-object records per run if Reporting Snapshots are under consideration. This drives mechanism selection.
  2. Select the reporting mechanism: Use the Decision Guidance table to choose between Historical Trend Reporting, Reporting Snapshots, or Custom Report Types. In some designs, all three are combined (HTR for near-term trends, Snapshots for long-term archive, CRTs for exception analysis).
  3. Configure HTR if selected: In Setup > Historical Trend Reporting, enable Opportunity trending. Select up to 8 fields (5 standard + 3 custom). Save. Confirm data starts populating the next day. Build the "Opportunities with Historical Trending" report type in the Report Builder.
  4. Design and activate the Reporting Snapshot if selected: Create the target custom object with matching field types (Currency, Date, Percent — not Text). Create the source Tabular/Summary report and confirm the run will insert fewer than 2,000 new records. Configure the Reporting Snapshot field mappings in Setup > Reporting Snapshots. Set the schedule and activate. Monitor the first run for errors in the Reporting Snapshot run history.
  5. Create and deploy the Custom Report Type if selected: In Setup > Report Types, define the object chain (up to 4 objects). Set each join relationship to "with" (inner) or "without" (outer) semantics as required. Set status to "Deployed." Build and validate reports using the new CRT.
  6. Build and test reports and dashboards: Validate that filters, groupings, and date ranges return expected data. For Reporting Snapshots, test by comparing a current snapshot run to a known pipeline state. For HTR, confirm historical columns show expected values on deals with known history.
  7. Document retention limits and operational procedures: Record the HTR retention window (previous 3 months plus the current month) for stakeholders. For Reporting Snapshots, document storage growth rate and any archival/purge plan for old snapshot records. Confirm the Snapshot source report owner is an active service account user with ongoing data access.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. HTR data is not retroactive — Enabling Historical Trend Reporting today means trend data begins accumulating from today forward. Data from before activation is permanently unavailable. There is no way to backfill historical trending data.
  2. The Reporting Snapshot 2,000 cap is on target-object inserts per run, not on source report rows — A run adds at most 2,000 new records to the target object; anything past that is not recorded. Salesforce does flag it: the run notification and Run History report that some rows failed. The silent part is the unread notification, not the platform.
  3. HTR field cap of 8 cannot be worked around with formula fields — Formula fields are not eligible for HTR tracking. Only standard and custom non-formula fields can be tracked. To track computed values, track the underlying component fields and compute at report time.
  4. Reporting Snapshot records accumulate indefinitely — A daily snapshot of 1,500 Opportunity rows creates ~547,500 records per year in the target object. Without an archival or deletion strategy, the org will hit storage limits.
  5. CRT "without" join applies only at the immediately adjacent relationship step — In an Account > Opportunity > OLI chain, "without" at the Account-Opportunity step shows Accounts without Opportunities; "without" at the Opportunity-OLI step shows Opportunities without Line Items. Configuring the wrong step produces unexpected results.
  6. HTR trending columns only appear in the dedicated report type — The "Opportunities with Historical Trending" report type is required. Adding date filters to a standard Opportunities report type does not expose trending columns.
  7. Turning Historical Trending off is a one-way door — Disabling trending for an object deletes all of that object's historical data and configuration settings, including its historical trending report type and every report built on that report type. Re-enabling starts collection from zero. Treat enable/disable as an irreversible change and require a change-control approval for it.

Output Artifacts

ArtifactDescription
sales-reporting-data-model-template.mdWork template for designing and documenting sales reporting architecture decisions — captures mechanism choice rationale, field selections, Snapshot object design, and CRT configuration
check_sales_reporting_data_model.pystdlib Python checker that scans Salesforce metadata XML for common issues: Snapshot target object fields using wrong types, missing HTR field configuration, undeployed CRTs

Package files