The quality of your analysis depends on the quality of your source data. Well-designed source reports enable powerful snapshotting, accurate change tracking, and seamless blending with other data sources. This guide covers best practices for creating reports that work optimally with Scoop.

Core Principles

Essential Fields to Include

Unique Identifiers

Every report should include a column that uniquely identifies each record:

Why it matters: Without a unique identifier, Scoop cannot:

• Track how individual records change over time
• Enable process analysis (Sankey diagrams)
• Calculate accurate conversion rates

Date Fields

Include all relevant dates for comprehensive time analysis:

Status/Stage Fields

For process analysis, include fields that track lifecycle:

• Current status or stage
• Previous status (if available)
• Substatus or reason codes

Owner/Assignment Fields

For workload and performance analysis:

• Assigned user or team
• Created by
• Last modified by

Report Design Patterns

For Snapshot Datasets

When tracking entities that change over time (deals, tickets, inventory):

Recommended Filter Pattern

Include records that are either:

1. Currently active/open, OR
2. Recently modified (last 1-7 days)

Example: Salesforce Opportunities
Filter: IsClosed = FALSE OR LastModifiedDate >= LAST_N_DAYS:2

Why: This captures both current state and recent transitions.

Include All Lifecycle Stages

Don't filter out closed/completed records entirely—you need to see the final transition:

❌ Bad:  Stage != "Closed Won" AND Stage != "Closed Lost"
✅ Good: IsOpen = TRUE OR CloseDate >= LAST_N_DAYS:7

For Transactional Datasets

When tracking events/transactions that don't change:

Include Transaction Date

Always include when the transaction occurred:

Consider Date Range

For large transaction volumes:

• Filter to relevant time period
• Use incremental loading for ongoing ingestion

For Reference Data

When loading lookup tables or master data:

Include All Records

Don't filter reference data—you need complete lookup coverage:

✅ Include: All products, all regions, all categories
❌ Don't filter: Only active products (breaks historical lookups)

Add Effective Dates

If values change over time, include effective dates:

Column Best Practices

Naming Conventions

Data Types

Ensure columns have appropriate data types:

Avoid These Patterns

Application-Specific Guidance

Salesforce

Essential fields for Opportunities:

• Id (unique key)
• StageName
• Amount
• CloseDate
• OwnerId or Owner.Name
• CreatedDate
• LastModifiedDate

Recommended filter:

(IsClosed = FALSE) OR (LastModifiedDate >= LAST_N_DAYS:7)

HubSpot

Essential fields for Deals:

• hs_object_id (unique key)
• dealstage
• amount
• closedate
• hubspot_owner_id
• createdate
• hs_lastmodifieddate

Jira

Essential fields for Issues:

• key (unique key)
• status
• issuetype
• priority
• assignee
• created
• updated
• resolutiondate

NetSuite/ERP

Essential fields for Transactions:

• internalid (unique key)
• type
• trandate
• status
• entity (customer/vendor)
• amount

Testing Your Report

Before automating, verify your report works well:

Check for Unique Key

1. Export a sample
2. Count total rows
3. Count distinct values in your ID column
4. If counts don't match, investigate duplicates

Verify Filter Coverage

1. Manually close/complete a test record
2. Run report the next day
3. Confirm the transition is captured

Validate Data Types

1. Check dates parse correctly
2. Verify numbers don't have formatting issues
3. Confirm IDs are consistent format

Common Pitfalls

Pitfall: Missing Transitions

Symptom: Process analysis shows gaps or missing conversions

Cause: Recently closed items not in report

Fix: Adjust filter to include recently modified records

Pitfall: Duplicate Records

Symptom: Counts are inflated, unique key not detected

Cause: Report includes line items or is at wrong granularity

Fix: Aggregate to correct level or filter to parent records

Pitfall: Changing Column Names

Symptom: New table created, historical data disconnected

Cause: Column renamed in source report

Fix: Keep column names consistent; use calculated columns to map if needed

Pitfall: Inconsistent Filters

Symptom: Data volume fluctuates unexpectedly

Cause: Dynamic filters that change over time

Fix: Use absolute date ranges or relative ranges consistently

Related Topics

• Snapshot Datasets - Track entity changes
• Handling Report Changes - When reports change
• Adding Calculated Columns - Transform and enrich data
• Blending Datasets - Combine multiple sources