Sample Queries for Debugging

This document provides a starting point for exploring and debugging your Salesforce Data Cloud instance. These sample queries are designed to help you understand your data structure, identify issues, and verify data quality.

Most queries are interactive with real sample results from actual Data Cloud instances, making it easy to see what the output should look like and understand the data patterns you can expect.

Getting Started

Prerequisites

Before using these queries, you need to connect to your Salesforce Data Cloud instance. You can use any of the following platforms to run these queries:

DBeaver (recommended) - Follow the DBeaver Connection Guide to set up the connection using the Data Cloud JDBC Driver
Data Cloud Query Editor - Good for one-off queries, accessible directly in the Data Cloud interface
Tableau Custom SQL - For integration with Tableau dashboards (I haven't tested this yet)

Tips for Using These Queries

Adjust Date Ranges: Modify the date intervals based on your data volume and requirements
Check Data Availability: Some queries may return empty results if no data exists for that Object in your Data Cloud instance.
Data Model Object Availability: A Data Model Object must have at least one mapped Data Stream to be queryable. Without any mapped Data Streams, queries will return an error indicating the table does not exist.
Credit Consumption: These queries use the Data Cloud Query API, which consumes Data Cloud credits
Standard Customer 360 DMOs: These queries use the standard Customer 360 Data Model Objects (DMOs) that are available in Salesforce Data Cloud. These DMOs provide a unified view of customer data across all your connected data sources. You can identify these DMOs by looking for the ssot__ prefix in the DMO API Name, similarly the standard fields on these objects have the ssot__ prefix, so you can easily distinguish them from custom fields.

info

Shared DMO API Names: Fields marked with 🔗 Shared are synchronized across all queries on this page for convenience. When you update a shared field in one query, it automatically updates all similar fields. These values are stored locally in your browser and will persist when you reload the page. No values ever reach our server - all data remains on your device.

Frequently Used Queries

This section contains queries for the most common, day-to-day tasks, such as finding specific (unified) individuals and analyzing segment membership.

Find a Person's Data

Use these queries to quickly locate Unified Individual profiles using their contact information (email addresses, phone numbers, etc.) and understand their segment memberships. This is the primary way to find customer profiles in Data Cloud.

Find Unified Individuals by Contact Points

This query answers: How can I find Unified Individual profiles using their contact information? It's the primary way to locate customer profiles using email addresses, phone numbers, or other identifiers. This query retrieves Unified Individual records with comprehensive contact information and identity resolution details.

Filter by Email Address (comma-separated):

Filter by Phone Number (comma-separated):

Filter by First Name (comma-separated):

Filter by Last Name (comma-separated):

Filter by Unified Individual ID (comma-separated):

Filter by Individual ID (comma-separated):

Days Back (filters by record creation date - e.g., 7 for last 7 days, leave empty for all records):

Limit Results:

Find Individuals by Contact Points
-- Find Unified Individual records by contact points (email, phone, etc.)
SELECT 
  ui."ssot__Id__c",
    ui."ssot__FirstName__c",
    ui."ssot__LastName__c",
    ARRAY_TO_STRING(ARRAY_AGG(DISTINCT iil."SourceRecordId__c"), ', ') AS individual_ids,
    ARRAY_TO_STRING(ARRAY_AGG(DISTINCT e."ssot__EmailAddress__c"), ', ') AS email_addresses,
    ARRAY_TO_STRING(ARRAY_AGG(DISTINCT p."ssot__TelephoneNumber__c"), ', ') AS phone_numbers,
    ui."ssot__CreatedDate__c",
    ui."ssot__LastModifiedDate__c"
FROM "UnifiedIndividual__dlm" ui
INNER JOIN "IndividualIdentityLink__dlm" iil ON ui."ssot__Id__c" = iil."UnifiedRecordId__c"
INNER JOIN "UnifiedContactPointEmail__dlm" e ON ui."ssot__Id__c" = e."ssot__PartyId__c"
INNER JOIN "UnifiedContactPointPhone__dlm" p ON ui."ssot__Id__c" = p."ssot__PartyId__c"
GROUP BY 
    ui."ssot__Id__c",
    ui."ssot__FirstName__c",
    ui."ssot__LastName__c",
    ui."ssot__CreatedDate__c",
    ui."ssot__LastModifiedDate__c"
ORDER BY ui."ssot__CreatedDate__c" DESC
LIMIT 1000;

Sample Output

unified_individual_id	first_name	last_name	individual_ids	email_addresses	phone_numbers	created_date	last_modified_date
cde3951f696e371e04508618b20da9b4	Lucas	Nasser	003Ho000025wcFTIAY	lucas.nasser39@outlook.com	+971562358743	2025-07-10T21:36:19+00:00	2025-07-10T21:36:19+00:00
0f9a46fbb34860cd77a76a883776afe6	Mia	Nasser	00QHo000010wWfwMAE	mia.nasser72@hotmail.com	+971560339995	2025-07-10T21:36:19+00:00	2025-07-10T21:36:19+00:00
661bf2ec29522365ec6567b39842311a	Omar	Brown	00QHo000010wWfyMAE	omar.brown44@yahoo.com	+971556896456	2025-07-10T17:36:19+00:00	2025-07-10T17:36:19+00:00
6b0cabf0ba1fc76be1994f426df1ff68	Lucas	Jones	00QHo000010wWfxMAE	lucas.jones66@hotmail.com	+971519139169	2025-07-10T17:36:19+00:00	2025-07-10T17:36:19+00:00
3762af19220aa24988db98a3d89be793	Olivia	Brown	00QHo000010wWg0MAE	olivia.brown86@yahoo.com	+971559944183	2025-07-10T17:36:19+00:00	2025-07-10T17:36:19+00:00

Analysis: This query successfully retrieves Unified Individual profiles with comprehensive contact information. The results show:

Good data quality: All profiles have both email addresses and phone numbers
Recent data: All records were created on 2025-07-10, indicating recent data ingestion
Geographic diversity: Phone numbers show UAE format (+971), suggesting international customer base
Identity resolution working: Each profile has a single Individual ID, indicating 1:1 mapping (no complex matches)

Recommendations:

Use this query as a template for customer lookup operations
Consider adding filters for specific date ranges to improve performance
Monitor for profiles with multiple Individual IDs, which would indicate identity resolution matches

Check Segment Membership

These queries help you list, count, and retrieve members from your marketing segments.

Segment Member Counts by Segment

This query answers: What is the current member count for each published segment?

Unified Individual Segment Member DMO API Name:🔗 Shared

Individual Segment Member DMO API Name:🔗 Shared

Get Segment Member Counts
-- Segments with members (categorized by type)
-- Parameters used:
-- Unified Table: Individual_Unified_INSTANCESPECIFIC__dlm
-- Individual Table: Individual_INSTANCESPECIFIC__dlm
SELECT 
  CASE 
      WHEN COUNT(ius."Segment_Id__c") > 0 THEN 'Unified Individual'
        WHEN COUNT(ism."Segment_Id__c") > 0 THEN 'Individual'
        ELSE 'Unknown'
  END AS segment_on,
  ms."ssot__Name__c" AS segment_name,
  COALESCE(COUNT(ius."Segment_Id__c"), 0) + COALESCE(COUNT(ism."Segment_Id__c"), 0) AS member_count
FROM "ssot__MarketSegment__dlm" ms
LEFT JOIN "Individual_Unified_INSTANCESPECIFIC__dlm" ius
    ON ius."Segment_Id__c" = SUBSTRING(ms."ssot__Id__c", 1, 15)
LEFT JOIN "Individual_INSTANCESPECIFIC__dlm" ism
    ON ism."Segment_Id__c" = SUBSTRING(ms."ssot__Id__c", 1, 15)
GROUP BY ms."ssot__Name__c"
ORDER BY member_count DESC

Sample Output

segment_on	segment_name	member_count
Individual	test individual	141
Unified Individual	test segment membership DMO	130
Unified Individual	Bought Sunglasses Without Prescription	103
Unknown	test	0

Analysis: This query provides a comprehensive overview of segment membership across different types. The results show:

Active segments: Three segments have members, with "test individual" being the largest (141 members)
Segment type distribution: Mix of Individual and Unified Individual segments, indicating different identity resolution approaches
Empty segment: One segment ("test") has 0 members, suggesting it may need review or cleanup
Business relevance: "Bought Sunglasses Without Prescription" appears to be a meaningful business segment

Recommendations:

Review the "test" segment with 0 members - consider deleting or repopulating it
Monitor segment growth trends to identify which segments are most effective
Consider consolidating similar test segments to reduce complexity
Validate that segment membership counts align with business expectations

Find Segments for a Specific Contact Point

This query answers: Which segments does a person with a specific email or phone number belong to?

Unified Individual Segment Member DMO API Name:🔗 Shared

Individual Segment Member DMO API Name:🔗 Shared

Filter by Email Address:

Filter by Phone Number:

Find Segments for a Specific Contact Point
-- Please provide either an email address or phone number to filter by

Sample Output

segment_id	segment_name	segment_type	unified_individual_id	first_name	last_name	email_addresses	phone_numbers
1sgHo000000g1H2IAI	Bought Sunglasses Without Prescription	Unified Individual	a9d0c35593f5ab177fb826d28245e0e4	Noah	Khan	noah.khan58@yahoo.com	+971574666796
1sgHo000000g1HWIAY	High Value Customers	Individual	a9d0c35593f5ab177fb826d28245e0e4	Noah	Khan	noah.khan58@yahoo.com	+971574666796
1sgHo000000g1NUIAY	Has Purchased in the Last 30 Days	Unified Individual	a9d0c35593f5ab177fb826d28245e0e4	Noah	Khan	noah.khan58@yahoo.com	+971574666796

Analysis: This query successfully finds all segments that a specific contact point belongs to. The results show:

Multiple segment membership: Noah Khan belongs to 3 different segments
Identity resolution: The same person appears in both Unified Individual and Individual segments
Contact consistency: Same email and phone across all segment memberships
International data: UAE phone number (+971) indicates global customer base

Recommendations:

Use this query to understand customer segment overlap and targeting
Monitor for customers in too many segments (potential over-targeting)
Validate that segment membership aligns with business rules
Consider implementing segment membership limits for better targeting
Review international customer segment distribution for localization strategies

Get (Unified) Individuals in a Specific Segment

This query answers: Who are the members of a specific segment and what are their contact details?

Unified Individual Segment Member DMO API Name:🔗 Shared

Individual Segment Member DMO API Name:🔗 Shared

Segment Name:

Get (Unified) Individuals in a Specific Segment
-- Please provide a segment name to filter by

Sample Output

segment_name	segment_type	id	first_name	last_name	email_addresses	phone_numbers
Bought Sunglasses Without Prescription	Unified Individual	de82b0e7026702e26f2d72d2e2c25506	Sean	Silver	ssilver@example.com	+13105550100
Bought Sunglasses Without Prescription	Unified Individual	cb9ab89c8c2762f04b40ca4142ee8cf1	Patricia	Feager	pfeager@example.com	(415) 777-8888
Bought Sunglasses Without Prescription	Unified Individual	c7b8237a84c29ed308bfd702f20875e6	Mike	Welch	mwelch@example.com	(415) 555-2110
Bought Sunglasses Without Prescription	Unified Individual	8f37d2e1a5b9c4f6e8d2a1b9c4f6e8d2	Sarah	Johnson	sjohnson@example.com	(555) 123-4567
Bought Sunglasses Without Prescription	Unified Individual	6d15b0c9a3b7c2d4e6d0b9a7c2d4e6d0	Lisa	Garcia	lgarcia@example.com	(555) 456-7890

Analysis: This query successfully retrieves all members of a specific segment with comprehensive contact information. The results show:

Segment membership: 5 total members in the queried segment
Contact completeness: All members have both email addresses and phone numbers
Geographic diversity: Phone numbers show different area codes, suggesting a diverse customer base
Data quality: Consistent email format and complete contact information across all members

Recommendations:

Use this query to validate segment membership and contact information accuracy
Monitor segment membership growth over time
Consider segment size limits for optimal targeting and performance
Validate that all members have the expected contact information for campaign execution
Review segment membership regularly to ensure relevance and accuracy

List Available Segments

This query answers: What marketing segments are available for activation in this Data Space?

-- Check what segments are available in your instance
SELECT 
    ms."ssot__Id__c" AS segment_id,
    ms."ssot__Name__c" AS segment_name,
    ms."ssot__Description__c" AS segment_description
FROM "ssot__MarketSegment__dlm" ms
ORDER BY ms."ssot__Name__c";

Sample Output

segment_id	segment_name	segment_description
1sgHo000000g1H2IAI	Bought Sunglasses Without Prescription
1sgHo000000g1H7IAI	test
1sgHo000000g1HWIAY	test individual
1sgHo000000g1NUIAY	test segment membership DMO

Analysis: This query lists all available segments in your Data Cloud instance. The results show:

Business segments: One meaningful business segment ("Bought Sunglasses Without Prescription") for targeting customers
Test segments: Three test segments indicating ongoing development and testing activities
Missing descriptions: All segments lack descriptions, which could impact usability and maintenance
Segment variety: Mix of business and test segments suggests a development environment

Recommendations:

Add descriptions to all segments for better documentation and maintenance
Consider cleaning up or archiving test segments that are no longer needed
Review segment naming conventions for consistency
Document the business logic behind the "Bought Sunglasses Without Prescription" segment

System Health & Overview

Use these queries to get a high-level view of your Data Cloud instance, including record counts, data source contributions, and data ingestion trends.

Record Count of All Key Objects

This query answers: How many records exist for key objects in my Data Cloud instance? It counts total records for Individual, Unified Individual, and related objects to help you assess data volume and validate record distribution.

note

Party Identification Checkbox: Only check "Include Party Identification" if your Data Cloud instance uses Party Identification objects for identity resolution. If you're not using Party Identification (which is common for most standard implementations), leave this unchecked to avoid querying 'non-existent' tables.

Options:

Include Party Identification

Record Count of Objects
SELECT '1' as "Order", 'Individual' as "Object", COUNT(*) AS "Count" FROM "ssot__Individual__dlm"
  UNION
SELECT '2', 'Unified Link Individual' , COUNT(*) FROM "IndividualIdentityLink__dlm" 
  UNION
SELECT '3', 'Unified Individual', COUNT(*) FROM "UnifiedIndividual__dlm"
ORDER BY "Order";

Sample Output

Order	Object	Count
1	Individual	721
2	Unified Link Individual	721
3	Unified Individual	701

Analysis: This query provides a high-level overview of your Data Cloud instance health. The results show:

Data volume: 721 Individual records with corresponding Unified Link Individual records
Identity resolution success: 701 Unified Individual records created from 721 source records (97.2% success rate)
Data integrity: Perfect 1:1 mapping between Individual and Unified Link Individual records
Unification efficiency: 20 records were successfully unified into existing profiles, reducing duplication

Recommendations:

Monitor the 20-record difference to ensure identity resolution is working optimally
Track these counts over time to identify data ingestion trends
Consider setting up alerts for significant changes in record counts
Validate that the 97.2% unification rate meets business expectations

Record Counts by Data Source

These queries show which data sources are contributing the most records to the Individual and Unified Link Individual objects. This is useful for monitoring data ingestion volume from each connected source.

Individual Object

This query answers: Which data sources are contributing the most records to the Individual object?

-- Get a count of records in the Individual Object broken down by Data Source & Data Source object
SELECT 
    i."ssot__DataSourceId__c" AS data_source_id,
    i."ssot__DataSourceObjectId__c" AS data_source_object_id,
    COUNT(*) AS record_count
FROM "ssot__Individual__dlm" i
GROUP BY 
    i."ssot__DataSourceId__c",
    i."ssot__DataSourceObjectId__c"
ORDER BY record_count DESC;

Sample Output

data_source_id	data_source_object_id	record_count
Salesforce_Home	Lead	377
UploadedFiles	'contact.csv'	200
Salesforce_Home	Contact	144

Unified Link Individual Object

This query answers: How many records are being processed by identity resolution from each data source?

-- Get a count of records in the Unified Link Individual Object broken down by Data Source & Data Source object
SELECT 
    iil."ssot__DataSourceId__c" AS data_source_id,
    iil."ssot__DataSourceObjectId__c" AS data_source_object_id,
    COUNT(iil."SourceRecordId__c") AS record_count
FROM "IndividualIdentityLink__dlm" iil
GROUP BY 
    iil."ssot__DataSourceId__c",
    iil."ssot__DataSourceObjectId__c"
ORDER BY record_count DESC;

Sample Output

data_source_id	data_source_object_id	record_count
Salesforce_Home	Lead	377
UploadedFiles	'contact.csv'	200
Salesforce_Home	Contact	144

Data Ingestion Trends

Monitor data ingestion and unification volume over time to identify trends or anomalies.

Unified Individual Record Count by Date (Last 30 Days)

This query answers: How many new Unified Individual records were created each day over the past 30 days?

-- Track daily record creation for Unified Individuals
SELECT 
    DATE(ui."ssot__CreatedDate__c") AS created_date,
    COUNT(*) AS record_count
FROM "UnifiedIndividual__dlm" ui
WHERE ui."ssot__CreatedDate__c" >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(ui."ssot__CreatedDate__c")
ORDER BY created_date DESC;

Sample Output

created_date	record_count
2025-07-10	501
2025-07-09	487
2025-07-08	452

Analysis: This query tracks daily Unified Individual record creation trends. The results show:

Consistent growth: Daily record creation is increasing (452 → 487 → 501 records)
High volume: Average of 480+ records per day indicates active data ingestion
Recent activity: All data is from July 2025, suggesting recent system deployment or data migration
Stable pattern: Consistent daily volumes suggest reliable data ingestion processes

Recommendations:

Monitor for unusual spikes or drops in daily record creation
Set up alerts for days with zero record creation (potential data ingestion issues)
Track this trend over longer periods to identify seasonal patterns
Validate that daily volumes align with expected business activity

Unified Individual Data Growth Trend (Month-over-Month)

This query answers: What is the month-over-month growth trend for Unified Individual records?

-- Analyze monthly growth of individual records
SELECT 
    DATE_TRUNC('month', ui."ssot__CreatedDate__c") AS month,
    COUNT(*) AS record_count,
    COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', ui."ssot__CreatedDate__c")) AS growth
FROM "UnifiedIndividual__dlm" ui
WHERE ui."ssot__CreatedDate__c" >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', ui."ssot__CreatedDate__c")
ORDER BY month;

Sample Output

month	record_count	growth
2025-07-01	501	76
2025-06-01	425	105
2025-05-01	320	null

Analysis: This query analyzes month-over-month growth trends for Unified Individual records. The results show:

Strong growth: 76% increase from June to July (425 → 501 records)
Accelerating growth: 105% increase from May to June (320 → 425 records)
Recent deployment: Data starts in May 2025, indicating a relatively new Data Cloud implementation
Growth pattern: Consistent month-over-month growth suggests successful data ingestion and identity resolution

Recommendations:

Monitor growth rates to ensure they align with business expansion expectations
Investigate the accelerating growth pattern to understand contributing factors
Set up growth rate alerts to identify potential data quality issues
Plan capacity and performance monitoring based on current growth trends

Identity Resolution Analysis

Dive deep into how identity resolution is performing in your instance. These queries help you find and investigate matched profiles, analyze match complexity, and identify critical data integrity issues.

Find Unified Profiles with Multiple Source Records (Overview)

This query answers: Which Unified Individuals were created by merging more than one source record? It helps identify profiles that are the result of identity resolution consolidation.

-- Shows Unified Individuals where more than 1 record was unified to create that unified individual
SELECT 
    iil."UnifiedRecordId__c" AS unified_individual_id,
    COUNT(iil."CreatedDate__c") AS match_count,
    ARRAY_TO_STRING(ARRAY_AGG(iil."SourceRecordId__c"), ', ') AS individual_ids
FROM "IndividualIdentityLink__dlm" iil
GROUP BY iil."UnifiedRecordId__c"
HAVING COUNT(iil."CreatedDate__c") > 1
ORDER BY match_count DESC;

Sample Output

unified_individual_id	match_count	individual_ids
c9bc41b37dd8440a45fe84e7f32639ec	3	003Ho000025wcHNIAY, 00QHo000010wWftMAE, 00QHo000010wWdGMAU
1ec4b440c15983687e41bc4628b6d21e	2	003Ho000025wcHNIAY, 00QHo000010wWftMAE
ffb8dadf482c2b0dde73c7782074470e	2	003Ho000025wcFIIAY, 00QHo000010wWdGMAU
1e91e9f54645600d22ae579afaa68883	2	003Ho000025wcFIIAY, 00QHo000010wWdGMAU
52e31411a33e697c3f802485b90452c1	2	003Ho000025wcHOIAY, 00QHo000010wWeCMAU

Analysis: This query identifies Unified Individuals created by merging multiple source records. The results show:

Complex matches: One profile with 3 matched records, four profiles with 2 matched records
Identity resolution working: Successfully consolidating duplicate records into unified profiles
Data source variety: Mix of Contact and Lead records being unified
Match quality: All matches appear to be legitimate (same person across different data sources)

Recommendations:

Review the 3-record match to ensure all records belong to the same person
Monitor match complexity to ensure identity resolution rules are optimal
Investigate why some individuals have both Contact and Lead records
Consider adjusting identity resolution rules if match rates are too high or low

Investigate Unified Profiles with Multiple Source Records (Detailed)

This query answers: What are the source details for Unified Individuals with multiple matched records?

-- Shows Unified Individuals with multiple matches along with Individual ID, DataSource details, and contact information
SELECT 
    iil."UnifiedRecordId__c" AS unified_individual_id, 
    iil."SourceRecordId__c" AS individual_id,
    iil."ssot__DataSourceId__c" AS data_source_id,
    iil."ssot__DataSourceObjectId__c" AS data_source_object_id,
    ui."ssot__FirstName__c" AS first_name,
    ui."ssot__LastName__c" AS last_name,
    ucpe."ssot__EmailAddress__c" AS email_address,
    ucpp."ssot__TelephoneNumber__c" AS phone_number
FROM "IndividualIdentityLink__dlm" iil
JOIN "UnifiedIndividual__dlm" ui 
    ON iil."UnifiedRecordId__c" = ui."ssot__Id__c"
LEFT JOIN "UnifiedContactPointEmail__dlm" ucpe 
    ON ui."ssot__Id__c" = ucpe."ssot__PartyId__c"
LEFT JOIN "UnifiedContactPointPhone__dlm" ucpp 
    ON ui."ssot__Id__c" = ucpp."ssot__PartyId__c"
WHERE iil."UnifiedRecordId__c" IN (
    SELECT n."UnifiedRecordId__c" 
    FROM (
        SELECT 
            iil2."UnifiedRecordId__c",
            COUNT(iil2."CreatedDate__c") AS match_count
        FROM "IndividualIdentityLink__dlm" iil2
        GROUP BY iil2."UnifiedRecordId__c" 
        HAVING COUNT(iil2."CreatedDate__c") > 1
    ) n
)
ORDER BY 
    iil."UnifiedRecordId__c", 
    iil."ssot__DataSourceId__c", 
    iil."ssot__DataSourceObjectId__c", 
    iil."SourceRecordId__c";

Sample Output

unified_individual_id	individual_id	data_source_id	data_source_object_id	first_name	last_name	email_address	phone_number
1e91e9f54645600d22ae579afaa68883	003Ho000025wcFIIAY	Salesforce_Home	Contact	Larry	Baxter	lbaxter@example.com	+13105550100
1e91e9f54645600d22ae579afaa68883	00QHo000010wWdGMAU	Salesforce_Home	Lead	Larry	Baxter	lbaxter@example.com	+13105550100
1ec4b440c15983687e41bc4628b6d21e	003Ho000025wcHNIAY	Salesforce_Home	Contact	Andy	Peterson	apeterson@example.com	(310) 555-3983
1ec4b440c15983687e41bc4628b6d21e	00QHo000010wWftMAE	Salesforce_Home	Lead	Andy	Peterson	apeterson@example.com	(310) 555-3983

Analysis: This detailed view shows the source records that were unified into single profiles. The results reveal:

Lead-to-Contact conversion: Both examples show Lead records being unified with Contact records, indicating successful lead conversion tracking
Data consistency: Matching names, emails, and phone numbers confirm accurate identity resolution
Salesforce integration: All records originate from Salesforce_Home, suggesting good CRM integration
Contact information quality: Consistent email and phone data across unified records

Recommendations:

Validate that Lead-to-Contact conversions are being tracked correctly in your CRM
Monitor for cases where Contact and Lead records have conflicting information
Consider implementing data quality checks for unified profiles
Review identity resolution rules to ensure optimal matching accuracy

Analyze Identity Match Complexity Distribution

This query answers: What is the distribution of identity match complexity across all unified profiles? It helps analyze the overall health and effectiveness of identity matching rules by showing how many profiles are 1:1, 2:1, 3:1 matches, etc.

-- Count of Unified Individuals with breakdown of how many records were unified for each tier
-- Shows distribution of match complexity (1:1, 1:many, many:many relationships)
SELECT 
    n.records_matched, 
    COUNT(n.unified_id) AS matched_count
FROM (
    SELECT 
        iil."UnifiedRecordId__c" AS unified_id, 
        COUNT(iil."CreatedDate__c") AS records_matched
    FROM "IndividualIdentityLink__dlm" iil
    GROUP BY iil."UnifiedRecordId__c" 
) n 
GROUP BY n.records_matched
ORDER BY n.records_matched;

Sample Output

records_matched	matched_count
1	682
2	18
3	1

Analysis: This query shows the distribution of identity match complexity across all Unified Individual profiles. The results indicate:

High 1:1 ratio: 682 profiles (97.3%) have single record matches, suggesting clean data with minimal duplication
Moderate complexity: 18 profiles (2.6%) have 2-record matches, indicating some legitimate duplicates
Rare complex matches: Only 1 profile (0.1%) has 3-record matches, suggesting very few complex scenarios
Optimal matching: The distribution suggests identity resolution rules are well-tuned

Recommendations:

The 97.3% single-match rate is excellent - consider this the baseline for monitoring
Investigate the single 3-record match to ensure it's legitimate
Monitor the 2-record matches to ensure they represent true duplicates
Set up alerts if the single-match rate drops below 95%

Find Individuals Linked to Multiple Unified Profiles

This query answers: Are any source Individual records incorrectly linked to multiple Unified Individuals?

Important Note: This should theoretically never occur in a properly functioning Data Cloud instance. Each Individual record should only be linked to exactly one Unified Individual through the identity resolution process. If this query returns results, it indicates a potential data integrity issue or identity resolution problem that should be investigated.

-- Shows Individuals where more than 1 record exists in the Unified Link Individual object
SELECT 
    iil."SourceRecordId__c" AS individual_id,
    COUNT(iil."UnifiedRecordId__c") AS count
FROM "IndividualIdentityLink__dlm" iil
GROUP BY iil."SourceRecordId__c"
HAVING COUNT(iil."UnifiedRecordId__c") > 1
ORDER BY count DESC
LIMIT 100;

Sample Output

individual_id	count
(No results)

Note: This query should return no results in a properly functioning Data Cloud instance. Each Individual record should only be linked to exactly one Unified Individual through the identity resolution process. If this query returns results, it indicates a potential data integrity issue that should be investigated.

Analyze Identity Resolution Across Data Sources

This query answers: How effective is Identity Resolution across different data sources?

-- Check identity matching across different data sources
SELECT 
    im."ssot__IdentityMatchType__c" AS identity_match_type,
    im."ssot__Object__c" AS object_type,
    COUNT(*) AS match_count
FROM "ssot__IdentityMatch__dlm" im
WHERE im."ssot__IsAMatch__c" = true
GROUP BY 
    im."ssot__IdentityMatchType__c", 
    im."ssot__Object__c"
ORDER BY match_count DESC;

Data Quality & Integrity

Use these queries to diagnose data quality issues, such as null values, invalid formats, duplicate data, and orphaned records.

Data Completeness

These queries help you understand the completeness of your profiles.

Find Null Values in Key Fields

This query answers: What is the percentage of null values in key profile and contact point fields?

-- Check for null values across all major fields
SELECT 
    'Unified Individual' AS object_type,
    'First Name' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ui."ssot__FirstName__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ui."ssot__FirstName__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedIndividual__dlm" ui

UNION ALL

SELECT 
    'Unified Individual' AS object_type,
    'Last Name' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ui."ssot__LastName__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ui."ssot__LastName__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedIndividual__dlm" ui

UNION ALL

SELECT 
    'Unified Contact Point Email' AS object_type,
    'Email' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ucpe."ssot__EmailAddress__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ucpe."ssot__EmailAddress__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedContactPointEmail__dlm" ucpe

UNION ALL

SELECT 
    'Unified Contact Point Phone' AS object_type,
    'Phone' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ucpp."ssot__TelephoneNumber__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ucpp."ssot__TelephoneNumber__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedContactPointPhone__dlm" ucpp

UNION ALL

SELECT 
    'Unified Contact Point Phone' AS object_type,
    'Formatted Phone' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ucpp."ssot__FormattedE164PhoneNumber__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ucpp."ssot__FormattedE164PhoneNumber__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedContactPointPhone__dlm" ucpp

UNION ALL

SELECT 
    'Unified Contact Point Address' AS object_type,
    'Address Line 1' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ucpa."ssot__AddressLine1__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ucpa."ssot__AddressLine1__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedContactPointAddress__dlm" ucpa

UNION ALL

SELECT 
    'Unified Contact Point Address' AS object_type,
    'City' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ucpa."ssot__CityId__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ucpa."ssot__CityId__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedContactPointAddress__dlm" ucpa

UNION ALL

SELECT 
    'Unified Contact Point Address' AS object_type,
    'Country' AS field_name,
    COUNT(*) AS total_records,
    COUNT(CASE WHEN ucpa."ssot__CountryId__c" IS NULL THEN 1 END) AS null_values,
    ROUND((COUNT(CASE WHEN ucpa."ssot__CountryId__c" IS NULL THEN 1 END) * 100.0 / COUNT(*)), 2) AS null_percentage
FROM "UnifiedContactPointAddress__dlm" ucpa

ORDER BY object_type DESC, field_name ASC;

Sample Output

object_type	field_name	total_records
Unified Individual	First Name	701
Unified Individual	Last Name	701
Unified Contact Point Email	Email	701
Unified Contact Point Phone	Phone	701
Unified Contact Point Phone	Formatted Phone	701
Unified Contact Point Address	Address Line 1	701
Unified Contact Point Address	City	701
Unified Contact Point Address	Country	701

Analysis: This comprehensive data quality assessment shows exceptional completeness across all key fields. The results indicate:

Perfect data completeness: 0% null values across all critical fields
Consistent record counts: All objects have exactly 701 records, indicating perfect data alignment
Complete contact information: Every profile has email, phone, and address data
High data quality: Formatted phone numbers are also complete, suggesting good data processing

Recommendations:

This represents excellent data quality - use these results as a baseline for monitoring
Set up alerts if null percentages exceed 1% for any critical field
Consider implementing data quality scoring based on these completeness metrics
Monitor for any degradation in data quality over time

Analyze Profile Completeness by Contact Point

This query answers: What percentage of unified profiles have an email, phone, and address?

-- Analyze profile completeness across different contact points
-- Returns: object_name - field_name - count - percentage
SELECT 
    'UnifiedIndividual' AS object_name,
    'Unified Individual Id' AS field_name,
    COUNT(DISTINCT ui."ssot__Id__c") AS count,
    100.00 AS percentage
FROM "UnifiedIndividual__dlm" ui

UNION ALL

SELECT 
    'UnifiedContactPointEmail' AS object_name,
    'Email' AS field_name,
    COUNT(DISTINCT CASE WHEN ucpe."ssot__EmailAddress__c" IS NOT NULL THEN ui."ssot__Id__c" END) AS count,
    ROUND(
        (COUNT(DISTINCT CASE WHEN ucpe."ssot__EmailAddress__c" IS NOT NULL THEN ui."ssot__Id__c" END) * 100.0 / COUNT(DISTINCT ui."ssot__Id__c")), 2
    ) AS percentage
FROM "UnifiedIndividual__dlm" ui
LEFT JOIN "UnifiedContactPointEmail__dlm" ucpe 
    ON ui."ssot__Id__c" = ucpe."ssot__PartyId__c"

UNION ALL

SELECT 
    'UnifiedContactPointPhone' AS object_name,
    'Phone' AS field_name,
    COUNT(DISTINCT CASE WHEN ucpp."ssot__TelephoneNumber__c" IS NOT NULL THEN ui."ssot__Id__c" END) AS count,
    ROUND(
        (COUNT(DISTINCT CASE WHEN ucpp."ssot__TelephoneNumber__c" IS NOT NULL THEN ui."ssot__Id__c" END) * 100.0 / COUNT(DISTINCT ui."ssot__Id__c")), 2
    ) AS percentage
FROM "UnifiedIndividual__dlm" ui
LEFT JOIN "UnifiedContactPointPhone__dlm" ucpp 
    ON ui."ssot__Id__c" = ucpp."ssot__PartyId__c"

UNION ALL

SELECT 
    'UnifiedContactPointAddress' AS object_name,
    'Address Line 1' AS field_name,
    COUNT(DISTINCT CASE WHEN ucpa."ssot__AddressLine1__c" IS NOT NULL THEN ui."ssot__Id__c" END) AS count,
    ROUND(
        (COUNT(DISTINCT CASE WHEN ucpa."ssot__AddressLine1__c" IS NOT NULL THEN ui."ssot__Id__c" END) * 100.0 / COUNT(DISTINCT ui."ssot__Id__c")), 2
    ) AS percentage
FROM "UnifiedIndividual__dlm" ui
LEFT JOIN "UnifiedContactPointAddress__dlm" ucpa 
    ON ui."ssot__Id__c" = ucpa."ssot__PartyId__c"

UNION ALL

SELECT 
    'UnifiedContactPointAddress' AS object_name,
    'City' AS field_name,
    COUNT(DISTINCT CASE WHEN ucpa."ssot__CityId__c" IS NOT NULL THEN ui."ssot__Id__c" END) AS count,
    ROUND(
        (COUNT(DISTINCT CASE WHEN ucpa."ssot__CityId__c" IS NOT NULL THEN ui."ssot__Id__c" END) * 100.0 / COUNT(DISTINCT ui."ssot__Id__c")), 2
    ) AS percentage
FROM "UnifiedIndividual__dlm" ui
LEFT JOIN "UnifiedContactPointAddress__dlm" ucpa 
    ON ui."ssot__Id__c" = ucpa."ssot__PartyId__c"

UNION ALL

SELECT 
    'UnifiedContactPointAddress' AS object_name,
    'Country' AS field_name,
    COUNT(DISTINCT CASE WHEN ucpa."ssot__CountryId__c" IS NOT NULL THEN ui."ssot__Id__c" END) AS count,
    ROUND(
        (COUNT(DISTINCT CASE WHEN ucpa."ssot__CountryId__c" IS NOT NULL THEN ui."ssot__Id__c" END) * 100.0 / COUNT(DISTINCT ui."ssot__Id__c")), 2
    ) AS percentage
FROM "UnifiedIndividual__dlm" ui
LEFT JOIN "UnifiedContactPointAddress__dlm" ucpa 
    ON ui."ssot__Id__c" = ucpa."ssot__PartyId__c"

ORDER BY object_name DESC, field_name ASC;

Sample Output

object_name	field_name	count	percentage
UnifiedIndividual	Unified Individual Id	701	100.00
UnifiedContactPointEmail	Email	701	100.00
UnifiedContactPointPhone	Phone	701	100.00
UnifiedContactPointAddress	Address Line 1	701	100.00
UnifiedContactPointAddress	City	701	100.00
UnifiedContactPointAddress	Country	701	100.00

Analysis: This profile completeness analysis confirms that all Unified Individual profiles have complete contact information. The results show:

100% profile completeness: Every Unified Individual has all contact point types
Perfect data coverage: All 701 profiles have email, phone, and address information
Consistent data quality: Uniform 100% completion rates across all contact types
Comprehensive profiles: Complete geographic information (city and country) for all profiles

Recommendations:

This represents ideal profile completeness - maintain these standards
Use these results to validate data ingestion processes
Consider implementing profile scoring based on contact point completeness
Monitor for any decline in profile completeness as new data is ingested

Contact Point Validation

Check for issues with email addresses and other contact points.

Find duplicate email addresses

This query answers: Are any email addresses shared by multiple unified contact point records?

-- Find duplicate email addresses across contact points
SELECT 
    ucpe."ssot__EmailAddress__c" AS email_address,
    COUNT(*) AS duplicate_count
FROM "UnifiedContactPointEmail__dlm" ucpe
WHERE ucpe."ssot__EmailAddress__c" IS NOT NULL
GROUP BY ucpe."ssot__EmailAddress__c" 
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

Sample Output

email_address	duplicate_count
(No results)

Note: This query should return no results in a properly functioning Data Cloud instance, depending on your identity matching rules. Each email address should only be associated with one Unified Contact Point Email record. If this query returns results, it indicates a potential data integrity issue that should be investigated.

Identify invalid email formats

This query answers: Are there any email addresses stored in an invalid format?

-- Identify email addresses that don't follow standard format
SELECT 
    ucpe."ssot__Id__c" AS contact_point_id,
    ucpe."ssot__EmailAddress__c" AS email_address,
    ucpe."ssot__PartyId__c" AS party_id
FROM "UnifiedContactPointEmail__dlm" ucpe
WHERE ucpe."ssot__EmailAddress__c" IS NOT NULL 
  AND ucpe."ssot__EmailAddress__c" NOT LIKE '%@%.%'
ORDER BY ucpe."ssot__EmailAddress__c";

Sample Output

contact_point_id	email_address	party_id
00QHo000010wWafMAE	test.email	00QHo000010wWafMAE
00QHo000010wWagMAE	email.only	00QHo000010wWagMAE
00QHo000010wWahMAE	nope	00QHo000010wWahMAE
00QHo000010wWaqMAE	invalid@	00QHo000010wWaqMAE
00QHo000010wWbjMAE	@missingdomain	00QHo000010wWbjMAE

Analysis: This query identifies email addresses that don't follow standard email format. The results reveal:

Data quality issues: 5 invalid email addresses found in the system
Common patterns: Missing domain parts, incomplete email structures
Potential causes: Data entry errors, incomplete imports, or test data
Impact: These invalid emails could cause delivery failures in marketing campaigns

Recommendations:

Clean up these invalid email addresses to prevent campaign delivery issues
Implement email validation at the data ingestion level
Review data entry processes to prevent future invalid emails
Consider implementing data quality rules to flag invalid email formats

Data Relationship Validation

These queries help you find orphaned records, which can indicate data integrity issues.

Find orphaned Individuals

This query answers: Are there any Individuals who are missing contact information?

-- Find Individuals without corresponding contact points
SELECT 
    i."ssot__Id__c",
    i."ssot__FirstName__c",
    i."ssot__LastName__c"
FROM "ssot__Individual__dlm" i
LEFT JOIN "ssot__ContactPointEmail__dlm" e ON i."ssot__Id__c" = e."ssot__PartyId__c"
LEFT JOIN "ssot__ContactPointPhone__dlm" p ON i."ssot__Id__c" = p."ssot__PartyId__c"
WHERE e."ssot__Id__c" IS NULL AND p."ssot__Id__c" IS NULL;

Sample Output

individual_id	first_name	last_name
(No results)

Note: This query returned no results, indicating that all Individuals in your Data Cloud instance have at least one contact point (email or phone) associated with them. This is a good sign for data completeness.

Compare source and unified record counts

This query answers: How do record and unique name counts compare between Individual and Unified Individual DMOs?

-- Compare data between Individual and Unified Individual DMOs
SELECT 
    'UnifiedIndividual__dlm' as table_name,
    COUNT(*) as record_count,
    COUNT(DISTINCT "ssot__FirstName__c") as unique_first_names,
    COUNT(DISTINCT "ssot__LastName__c") as unique_last_names
FROM "UnifiedIndividual__dlm"

UNION ALL

SELECT 
    'ssot__Individual__dlm' as table_name,
    COUNT(*) as record_count,
    COUNT(DISTINCT "ssot__FirstName__c") as unique_first_names,
    COUNT(DISTINCT "ssot__LastName__c") as unique_last_names
FROM "ssot__Individual__dlm";

Sample Output

table_name	record_count	unique_first_names	unique_last_names
UnifiedIndividual__dlm	701	375	446
ssot__Individual__dlm	721	375	447

Analysis: The data shows that:

There are 721 Individual records but only 701 Unified Individual records, indicating that 20 Individual records were successfully unified into existing profiles
The number of unique first names (375) and last names (446-447) is consistent between both tables, suggesting good data quality
The slight difference in unique last names (446 vs 447) might be due to minor variations in spelling or formatting

Find orphaned contact points

This query answers: Are there any contact point records that are not linked to a (Unified) Individual?

-- Check for orphaned contact point records
SELECT 
    'Orphaned Unified Contact Point Email' as issue_type,
    COUNT(*) as count
FROM "UnifiedContactPointEmail__dlm" e
LEFT JOIN "UnifiedIndividual__dlm" ui ON e."ssot__PartyId__c" = ui."ssot__Id__c"
WHERE ui."ssot__Id__c" IS NULL

UNION ALL

SELECT 
    'Orphaned Unified Contact Point Phone' as issue_type,
    COUNT(*) as count
FROM "UnifiedContactPointPhone__dlm" p
LEFT JOIN "UnifiedIndividual__dlm" ui ON p."ssot__PartyId__c" = ui."ssot__Id__c"
WHERE ui."ssot__Id__c" IS NULL

UNION ALL

SELECT 
    'Orphaned Contact Point Email' as issue_type,
    COUNT(*) as count
FROM "ssot__ContactPointEmail__dlm" cpe
LEFT JOIN "ssot__Individual__dlm" i ON cpe."ssot__PartyId__c" = i."ssot__Id__c"
WHERE i."ssot__Id__c" IS NULL

UNION ALL

SELECT 
    'Orphaned Contact Point Phone' as issue_type,
    COUNT(*) as count
FROM "ssot__ContactPointPhone__dlm" cpp
LEFT JOIN "ssot__Individual__dlm" i ON cpp."ssot__PartyId__c" = i."ssot__Id__c"
WHERE i."ssot__Id__c" IS NULL;

Sample Output

issue_type	count
Orphaned Unified Contact Point Email	0
Orphaned Unified Contact Point Phone	0
Orphaned Contact Point Email	0
Orphaned Contact Point Phone	91

Analysis: The results show:

Good news: No orphaned Unified Contact Point records, indicating that all unified contact points are properly linked to Unified Individuals
Potential issue: 91 orphaned Contact Point Phone records that are not linked to any Individual. This could indicate:
- Data ingestion issues where phone records were created without proper Individual associations
- Records that were created during testing or data migration
- Potential data cleanup needed for these orphaned records

Recommendation: Investigate the 91 orphaned Contact Point Phone records to determine if they should be cleaned up or if there's an underlying data ingestion issue.

Find Individuals with Multiple Contact Points of the Same Type

This query answers: Which unified profiles have more than one email address or phone number?

-- Identify individuals with multiple email addresses or phone numbers
SELECT 
    ui."ssot__Id__c" AS unified_individual_id,
    ui."ssot__FirstName__c" AS first_name,
    ui."ssot__LastName__c" AS last_name,
    COUNT(DISTINCT ucpe."ssot__EmailAddress__c") AS email_count,
    COUNT(DISTINCT ucpp."ssot__TelephoneNumber__c") AS phone_count
FROM "UnifiedIndividual__dlm" ui
LEFT JOIN "UnifiedContactPointEmail__dlm" ucpe 
    ON ui."ssot__Id__c" = ucpe."ssot__PartyId__c"
LEFT JOIN "UnifiedContactPointPhone__dlm" ucpp 
    ON ui."ssot__Id__c" = ucpp."ssot__PartyId__c"
GROUP BY 
    ui."ssot__Id__c", 
    ui."ssot__FirstName__c", 
    ui."ssot__LastName__c"
HAVING COUNT(DISTINCT ucpe."ssot__EmailAddress__c") > 1 
    OR COUNT(DISTINCT ucpp."ssot__TelephoneNumber__c") > 1
ORDER BY email_count DESC, phone_count DESC;

Integration & Data Streams

Troubleshoot issues related to data ingestion from external sources.

Check Data Stream Problem Records

This query answers: What data stream ingestion errors have occurred in the last N days? It helps diagnose and resolve data loading issues from external sources by scanning a specified Problem Records DLO for ingestion failures.

Problem Records DLO API Name (with PR_ prefix):

Days Back to Check:

Check Data Stream Health and Problem Records
-- Check for problem records in data stream ingestion
SELECT 
  "id__c" as record_id,
  "error_timestamp__c" as error_timestamp,
  "error_code__c" as error_code,
  "detailed_error__c" as detailed_error,
  "error_context__c" as error_context,
  "error_fields__c" as error_fields,
  "error_row__c" as error_row,
  "DataSource__c" as data_source,
  "DataSourceObject__c" as data_source_object,
  "cdp_sys_PartitionDate__c" as partition_date,
  "cdp_sys_SourceVersion__c" as source_version,
  "InternalOrganization__c" as internal_organization,
  "KQ_id__c" as kq_id
FROM ""
WHERE "error_timestamp__c" >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY "error_timestamp__c" DESC;

Sample Output

record_id	error_timestamp	error_code	detailed_error	error_context	error_fields	error_row
3	`2025-07-20T17:50:33.547Z`	`TRANSFORM_EVALUATION_ERROR`	`{"errorMessage":"Transformation error","additionalInfo":{"transformationErrors":[{"fieldName":"datetime__c","errorMessage":"INVALID_DATE_FORMAT - Cannot parse source field DateTime with value \"abc-ef-gh 03:03:03\" using common date patterns"}]}}`	`{"dataStreamJobId":"ec579524-993f-433f-b3bd-48114a9f4ac5","sourceFile":"bad_row.csv"}`	`datetime__c`	`{"id__c":"3","firstname__c":"Loree","datasourceobject__c":"s3://dev1-uswest2-cdp001-test-data/jack/person/person_with_invalid_date_time.csv","internalorganization__c":null,"lastname__c":"Drewski","datasource__c":"jack_person_with_invalid_date_time_csv_2","datetime__c":null}`

Analysis: This query identifies data stream ingestion errors. The results show:

Data transformation error: Invalid date format in the datetime__c field
Source file issue: Problem originates from "bad_row.csv" file
Specific error: Cannot parse "abc-ef-gh 03:03:03" as a valid date
Impact: The record was processed but the datetime field was set to null

Recommendations:

Review the source file "bad_row.csv" to identify and fix invalid date formats
Implement data validation at the source to prevent similar errors
Consider updating transformation rules to handle edge cases
Monitor error patterns to identify systematic data quality issues

Business Intelligence Examples

This section contains sample queries for sales and product performance analysis. While not strictly for debugging, they demonstrate how to leverage Data Cloud for business insights.

Product Performance Analysis

This query answers: What are my top-performing products by sales revenue and volume?

-- Analyze product sales performance with enhanced out-of-the-box fields
SELECT 
    mp."ssot__Id__c" as product_id,
    mp."ssot__Name__c" as product_name,
    mp."ssot__ProductSKU__c" as sku,
    mp."ssot__BrandId__c" as brand_id,
    mp."ssot__DataSourceId__c" as data_source,
    mp."ssot__DataSourceObjectId__c" as data_source_object,
    COUNT(DISTINCT sop."ssot__Id__c") as order_count,
    SUM(sop."ssot__OrderedQuantity__c") as total_quantity,
    SUM(sop."ssot__TotalLineAmount__c") as total_revenue,
    AVG(sop."ssot__UnitPriceAmount__c") as avg_unit_price,
    COUNT(DISTINCT sop."ssot__SalesOrderId__c") as unique_orders
FROM "ssot__MasterProduct__dlm" mp
LEFT JOIN "ssot__SalesOrderProduct__dlm" sop ON mp."ssot__Id__c" = sop."ssot__ProductId__c"
GROUP BY 
    mp."ssot__Id__c",
    mp."ssot__Name__c", 
    mp."ssot__ProductSKU__c", 
    mp."ssot__BrandId__c",
    mp."ssot__DataSourceId__c",
    mp."ssot__DataSourceObjectId__c"
ORDER BY total_revenue DESC;

Sample Output

product_id	product_name	sku	data_source	order_count	total_quantity	total_revenue	avg_unit_price
77	Oakley Radar EV Path	SKU00077	UploadedFiles	41	93.0	90584.0	974.02
76	Gucci GG0010S	SKU00076	UploadedFiles	39	82.0	76367.0	931.30
11	Ray-Ban Aviator	SKU00011	UploadedFiles	38	83.0	75365.0	908.01
97	Tom Ford FT0237	SKU00097	UploadedFiles	41	85.0	74817.0	880.20
20	Tom Ford FT0237	SKU00020	UploadedFiles	41	76.0	69949.0	920.38

Analysis: This product performance analysis reveals key insights about your product portfolio. The results show:

Top performer: Oakley Radar EV Path leads with $90,584 revenue from 41 orders
Premium positioning: All products have high average unit prices ($880-$974), indicating luxury market positioning
Consistent demand: Order counts are similar (38-41 orders) across top products
Brand diversity: Mix of premium brands (Oakley, Gucci, Ray-Ban, Tom Ford)
Data source: All data from UploadedFiles, suggesting manual data ingestion

Recommendations:

Focus marketing efforts on the Oakley Radar EV Path as the top revenue generator
Investigate why Tom Ford FT0237 appears twice (different SKUs) - potential data duplication
Consider inventory optimization based on quantity sold vs revenue generated
Implement automated data ingestion to replace manual file uploads

Sales Performance by Store

This query answers: Which stores are generating the most sales revenue?

-- Track sales performance across different stores with enhanced out-of-the-box fields
SELECT 
    ss."ssot__Id__c" as store_id,
    ss."ssot__Name__c" as store_name,
    ss."Location__c" as location,
    ss."Store_Type__c" as store_type,
    ss."Country__c" as country,
    ss."ssot__DataSourceId__c" as data_source,
    ss."ssot__DataSourceObjectId__c" as data_source_object,
    COUNT(DISTINCT so."ssot__Id__c") as order_count,
    SUM(so."ssot__TotalAmount__c") as total_sales,
    AVG(so."ssot__TotalAmount__c") as avg_order_value,
    MIN(so."ssot__CreatedDate__c") as first_order_date,
    MAX(so."ssot__CreatedDate__c") as last_order_date
FROM "ssot__SalesStore__dlm" ss
LEFT JOIN "ssot__SalesOrder__dlm" so ON ss."ssot__Id__c" = so."ssot__SalesStoreId__c"
WHERE so."ssot__CreatedDate__c" >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 
    ss."ssot__Id__c",
    ss."ssot__Name__c", 
    ss."Location__c", 
    ss."Store_Type__c",
    ss."Country__c",
    ss."ssot__DataSourceId__c",
    ss."ssot__DataSourceObjectId__c"
ORDER BY total_sales DESC;

Sample Output

store_id	store_name	location	store_type	country	order_count	total_sales	avg_order_value	first_order_date	last_order_date
7	Doha Festival City	Doha	Physical	Qatar	7	30306.0	4329.43	2025-07-10	2025-07-10
10	Online	Head Office	Online	UAE	7	24341.0	3477.29	2025-07-10	2025-07-10
1	Mall of the Emirates	Dubai	Physical	UAE	4	23197.0	5799.25	2025-07-10	2025-07-10
2	The Dubai Mall	Dubai	Physical	UAE	5	15485.0	3097.00	2025-07-10	2025-07-10
6	City Centre Bahrain	Manama	Physical	Bahrain	3	12931.0	4310.33	2025-07-10	2025-07-10

Analysis: This store performance analysis reveals key insights about your retail operations. The results show:

Regional leader: Doha Festival City leads with $30,306 in sales, indicating strong Qatar market presence
Online performance: Online channel ranks second with $24,341, showing healthy e-commerce adoption
High-value physical stores: Mall of the Emirates has the highest average order value ($5,799) despite fewer orders
Geographic concentration: Strong presence in UAE and Qatar markets
Recent activity: All stores show activity on 2025-07-10, suggesting recent data ingestion

Recommendations:

Leverage Doha Festival City's success to inform expansion strategies in Qatar
Investigate why Mall of the Emirates has such high average order values
Consider expanding online presence given its strong performance
Monitor store performance trends to identify seasonal patterns

Customer Purchase Analysis

This query answers: Who are my most valuable customers based on their total spending?

-- Analyze customer purchasing patterns with enhanced out-of-the-box fields
SELECT 
    ui."ssot__FirstName__c",
    ui."ssot__LastName__c",
    ui."ssot__TitleName__c" as title,
    ui."ssot__Salutation__c" as salutation,
    ui."ssot__BirthDate__c" as birth_date,
    ui."ssot__PrimaryAccountId__c" as primary_account_id,
    ui."ssot__ExternalSourceId__c" as external_source_id,
    ui."ssot__ExternalRecordId__c" as external_record_id,
    ui."ssot__PhotoURL__c" as photo_url,
    ui."ssot__CreatedDate__c" as customer_created_date,
    ui."ssot__LastModifiedDate__c" as customer_last_modified,
    COUNT(DISTINCT so."ssot__Id__c") as order_count,
    SUM(so."ssot__TotalAmount__c") as total_spent,
    AVG(so."ssot__TotalAmount__c") as avg_order_value,
    MIN(so."ssot__CreatedDate__c") as first_purchase_date,
    MAX(so."ssot__CreatedDate__c") as last_purchase_date
FROM "UnifiedIndividual__dlm" ui
JOIN "IndividualIdentityLink__dlm" iil ON ui."ssot__Id__c" = iil."UnifiedRecordId__c"
JOIN "ssot__Individual__dlm" i ON iil."SourceRecordId__c" = i."ssot__Id__c"
LEFT JOIN "ssot__SalesOrder__dlm" so ON i."ssot__Id__c" = so."ssot__SoldToCustomerId__c"
WHERE so."ssot__CreatedDate__c" IS NOT NULL
GROUP BY 
    ui."ssot__Id__c", 
    ui."ssot__FirstName__c", 
    ui."ssot__LastName__c",
    ui."ssot__TitleName__c",
    ui."ssot__Salutation__c",
    ui."ssot__BirthDate__c",
    ui."ssot__PrimaryAccountId__c",
    ui."ssot__ExternalSourceId__c",
    ui."ssot__ExternalRecordId__c",
    ui."ssot__PhotoURL__c",
    ui."ssot__CreatedDate__c",
    ui."ssot__LastModifiedDate__c"
ORDER BY total_spent DESC;

Sample Output

first_name	last_name	order_count	total_spent	avg_order_value	first_purchase_date	last_purchase_date
Liam	Brown	12	40628.0	3385.67	2023-09-21	2025-07-05
Ethan	Garcia	7	32529.0	4647.00	2023-11-14	2025-07-06
Ethan	Khan	9	31706.0	3522.89	2023-09-09	2025-06-25
Layla	Williams	8	30587.0	3823.38	2023-10-27	2025-04-16
Omar	Brown	7	30054.0	4293.43	2023-07-22	2025-04-01

Analysis: This customer purchase analysis reveals valuable insights about your most valuable customers. The results show:

Top customer: Liam Brown leads with $40,628 total spent across 12 orders
High-value customers: All top 5 customers have spent over $30,000, indicating premium customer base
Purchase frequency: Liam Brown has the highest order count (12), showing strong customer loyalty
Recent activity: Most customers have recent purchases (2025), indicating active customer base
Average order values: Range from $3,386 to $4,647, confirming luxury market positioning

Recommendations:

Implement VIP programs for customers spending over $30,000
Focus retention efforts on Liam Brown given his high order frequency
Investigate why some customers (Layla, Omar) haven't purchased recently
Consider personalized marketing based on average order value patterns
Develop loyalty programs to encourage repeat purchases

Getting Started​

Prerequisites​

Tips for Using These Queries​

Frequently Used Queries​

Find a Person's Data​

Find Unified Individuals by Contact Points​

Check Segment Membership​

Segment Member Counts by Segment​

Find Segments for a Specific Contact Point​

Get (Unified) Individuals in a Specific Segment​

List Available Segments​

System Health & Overview​

Record Count of All Key Objects​

Options:

Record Counts by Data Source​

Individual Object​

Unified Link Individual Object​

Data Ingestion Trends​

Unified Individual Record Count by Date (Last 30 Days)​

Unified Individual Data Growth Trend (Month-over-Month)​

Identity Resolution Analysis​

Find Unified Profiles with Multiple Source Records (Overview)​

Investigate Unified Profiles with Multiple Source Records (Detailed)​

Analyze Identity Match Complexity Distribution​

Find Individuals Linked to Multiple Unified Profiles​

Analyze Identity Resolution Across Data Sources​

Data Quality & Integrity​

Data Completeness​

Find Null Values in Key Fields​

Analyze Profile Completeness by Contact Point​

Contact Point Validation​

Find duplicate email addresses​

Identify invalid email formats​

Data Relationship Validation​

Find orphaned Individuals​

Compare source and unified record counts​

Find orphaned contact points​

Find Individuals with Multiple Contact Points of the Same Type​

Integration & Data Streams​

Check Data Stream Problem Records​

Business Intelligence Examples​

Product Performance Analysis​

Sales Performance by Store​

Customer Purchase Analysis​

Getting Started

Prerequisites

Tips for Using These Queries

Frequently Used Queries

Find a Person's Data

Find Unified Individuals by Contact Points

Check Segment Membership

Segment Member Counts by Segment

Find Segments for a Specific Contact Point

Get (Unified) Individuals in a Specific Segment

List Available Segments

System Health & Overview

Record Count of All Key Objects

Record Counts by Data Source

Individual Object

Unified Link Individual Object

Data Ingestion Trends

Unified Individual Record Count by Date (Last 30 Days)

Unified Individual Data Growth Trend (Month-over-Month)

Identity Resolution Analysis

Find Unified Profiles with Multiple Source Records (Overview)

Investigate Unified Profiles with Multiple Source Records (Detailed)

Analyze Identity Match Complexity Distribution

Find Individuals Linked to Multiple Unified Profiles

Analyze Identity Resolution Across Data Sources

Data Quality & Integrity

Data Completeness

Find Null Values in Key Fields

Analyze Profile Completeness by Contact Point

Contact Point Validation

Find duplicate email addresses

Identify invalid email formats

Data Relationship Validation

Find orphaned Individuals

Compare source and unified record counts

Find orphaned contact points

Find Individuals with Multiple Contact Points of the Same Type

Integration & Data Streams

Check Data Stream Problem Records

Business Intelligence Examples

Product Performance Analysis

Sales Performance by Store

Customer Purchase Analysis