RDC Snowflake Navigator Skill

This skill provides comprehensive knowledge of RDC's Snowflake data warehouse, including table schemas, business logic, attribution rules, and query patterns for marketing and consumer analytics.

Quick Reference

Core Tables

Key Identity Fields

• User ID: adjusted_uu_id (canonical across tables)
• Visit ID: visit_id (clickstream)
• Lead ID: submitted_lead_id or inquiry_lead_id
• GCLID: google_click_id (paid search attribution)

Key Date Fields

• Clickstream: event_date_mst (Mountain Time)

Workflow

When a query involves these tables:

1. Identify the question type:

   • User behavior → clickstream_detail
   • Lead/revenue metrics → marketing_conversion_detail
   • Campaign performance → sem_summary, app_summary, psocial_summary
   • Cross-channel spend analysis → spend

2. Load relevant reference docs:

   • Schema details → references/snowflake_core_tables.md
   • Business rules → references/business_logic_reference.md
   • Clickstream specifics → references/clickstream_view_annotated.md
   • EFR calculations → references/marketing_conversion_detail_annotated.md
   • Spend consolidation → references/spend_view_annotated.md

3. Apply correct filters:

   • Always filter on date first (these are large tables)
   • Use appropriate date field for each table
   • Apply business-specific exclusions as documented

4. Use proper metrics:

   • EFR for revenue attribution
   • IEFR for incrementality-adjusted revenue
   • Snapshot vs Cohort for app metrics

Critical Business Logic

Incrementality Multipliers (IEFR)

Different channels have different incrementality adjustments:

Product Verticals (Clickstream) - Field name: product_vertical

for_sale       → Buy intent pages
for_rent       → Rental pages  
not_for_sale_ldp → Off-market/recently sold
owner_seller   → Seller marketplace, My Home
sub_finance    → Mortgage/finance pages
realtorpage    → Agent pages

Lead Verticals (Conversions) - Field name: target_vertical

for_sale → Buy leads
for_rent → Rental leads
Seller   → Sell leads (or DELIVERED_TO_UPNEST/SELLER_PARTNER flags)
FAR      → Find a Realtor leads

EFR Components

Total EFR is calculated from multiple revenue sources:

Common Query Patterns

Daily Traffic by Platform

SELECT 
    event_date_mst,
    experience_type,
    platform,
    COUNT(DISTINCT adjusted_uu_id) as users,
    COUNT(DISTINCT visit_id) as sessions,
    SUM(is_pageview) as pageviews
FROM rdc_analytics.clickstream.clickstream_detail
WHERE event_date_mst >= DATEADD('day', -7, CURRENT_DATE())
GROUP BY 1, 2, 3
ORDER BY 1, 4 DESC;

Lead Volume by Channel

SELECT 
    event_date,
    last_touch_marketing_channel,
    submitted_lead_vertical,
    COUNT(*) as leads,
    SUM(estimated_future_revenue) as efr,
    MEDIAN(lead_listing_price) as median_price
FROM rdc_analytics.revenue.marketing_conversion_detail
WHERE event_date >= DATEADD('day', -30, CURRENT_DATE())
GROUP BY 1, 2, 3
ORDER BY 1, 4 DESC;

SEM Campaign Performance

SELECT 
    campaign_name,
    target_vertical,
    SUM(spend) as spend,
    SUM(leads) as leads,
    SUM(efr) as efr,
    SUM(iefr) as iefr,
    SUM(spend) / NULLIF(SUM(leads), 0) as cpl,
    SUM(iefr) / NULLIF(SUM(spend), 0) as iroas
FROM rdc_marketing.agg_reporting.sem_summary
WHERE event_date >= DATEADD('day', -30, CURRENT_DATE())
GROUP BY 1, 2
HAVING SUM(spend) > 1000
ORDER BY spend DESC;

App Install Efficiency

SELECT 
    media_source,
    platform,
    SUM(spend) as spend,
    SUM(ssot_installs) as installs,
    SUM(cohort_efr_for_sale) as cohort_efr,
    SUM(snapshot_iefr_for_sale) as iefr,
    SUM(spend) / NULLIF(SUM(ssot_installs), 0) as cpi
FROM rdc_marketing.agg_reporting.app_summary
WHERE date >= DATEADD('day', -30, CURRENT_DATE())
  AND is_app = 1
GROUP BY 1, 2
HAVING SUM(spend) > 100
ORDER BY spend DESC;

Cross-Channel Spend Summary

SELECT 
    channel,
    partner,
    target_vertical,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks
FROM rdc_marketing.agg_reporting.spend
WHERE event_date >= DATEADD('day', -30, CURRENT_DATE())
  AND target_customer = 'b2c'
GROUP BY 1, 2, 3
HAVING SUM(spend) > 1000
ORDER BY spend DESC;

User Journey to Lead

WITH lead_sessions AS (
    SELECT DISTINCT visit_id, inquiry_lead_id
    FROM rdc_analytics.clickstream.clickstream_detail
    WHERE event_date_mst >= DATEADD('day', -7, CURRENT_DATE())
      AND inquiry_lead_id IS NOT NULL
)
SELECT 
    c.product_vertical,
    c.page_type_group,
    COUNT(DISTINCT c.visit_id) as sessions,
    COUNT(DISTINCT l.inquiry_lead_id) as leads,
    COUNT(DISTINCT l.inquiry_lead_id)::FLOAT / NULLIF(COUNT(DISTINCT c.visit_id), 0) as conversion_rate
FROM rdc_analytics.clickstream.clickstream_detail c
LEFT JOIN lead_sessions l ON c.visit_id = l.visit_id
WHERE c.event_date_mst >= DATEADD('day', -7, CURRENT_DATE())
  AND c.product_vertical IN ('for_sale', 'for_rent')
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

Data Caveats

Historical Data Considerations

• SEM tracking codes changed multiple times (pre-Oct 2021, Oct-Jan 2022 issues, Jan 2022+)
• App data before June 2021 comes from separate historical table
• PSocial FB tracking broke in 2024 (special handling in view)

SKAN Privacy (iOS)

• iOS 14.5+ requires SKAdNetwork for attribution
• ssot_installs = SKAN + non-SKAN combined
• skan_multiplier adjusts for attribution gaps

Snapshot vs Cohort (App)

• Snapshot: Measured on event date (use for daily reporting)
• Cohort: Attributed to install date (use for LTV analysis)

Spend Table Considerations

• Linear TV (OceanMedia) has no impression/click data
• Recent TV data (last 7 days) may use pacing instead of actuals
• TikTok spend (bytedanceglobal_int) is zeroed after 2024-01-01
• App accounts are excluded from platform aggregates (Google, Facebook, Bing, LinkedIn) to prevent double-counting

EFR Calculation Notes

• EFR is lagged 30 days - Recent leads have no referral EFR (rolling avg uses days 30-36 back)
• High price tier ($30M+) gets $0 referral EFR (intentional exclusion)
• NY excluded from Veterans United per VU agreement
• Seller EFR capped at $25K to prevent outlier skew

Reference Documents

• snowflake_core_tables.md - Complete schema documentation for all tables
• business_logic_reference.md - Incrementality, attribution rules, metric definitions
• clickstream_view_annotated.md - Detailed clickstream field derivation logic
• marketing_conversion_detail_annotated.md - EFR calculation methodology and revenue sources
• spend_view_annotated.md - Unified spend aggregation across all marketing channels

Tips for Effective Queries

1. Always filter on date first - These tables have billions of rows
2. Use adjusted_uu_id for cross-table user joins
3. Check data freshness - Some tables are T-1 or T-2
4. Use IEFR for incrementality - Raw EFR overstates impact
5. Prefer MEDIAN over AVG for price metrics (skewed distributions)
6. Use CTEs for complex multi-step queries
7. Handle NULLs with NULLIF when dividing