Seller Analytics

Seller Analytics Skill

Domain expertise for the Seller vertical - an acquired business with its own data architecture separate from the core realtor.com platform.

Why Seller Is Different

The Seller business unit was acquired and maintains separate:

• Database schemas (rdc_marketing.seller.*)
• Lead attribution models
• Revenue calculation methods (EFR v1 vs v2)
• Campaign naming conventions

Do not use generic RDC_ANALYTICS.LEADS for seller analysis - use the seller-specific tables documented here.

Core Workflow

When a seller analytics task is requested:

1. Identify the analysis type - Spend, leads, revenue, or funnel metrics
2. Start from the right anchor table:
   • Spend analysis → sell_spend
   • Lead analysis → sell_attribution
   • Revenue/EFR → sell_revenue_est
   • Quality metrics → sell_lead_quality
3. Apply standard filters - channel, target_customer, date range
4. Use campaign classification - Derive campaign types from naming patterns
5. Join carefully - Use request_id + transaction_type composite key

Quick Reference: Where to Start

Schema Overview

Primary Schema: rdc_marketing.seller

Supporting Tables

Key Concepts

Transaction Types

Leads are categorized by intent:

• 'buy' - Buyer intent leads
• 'sell' - Seller intent leads

Always segment or filter by transaction_type - these are fundamentally different lead types with different quality metrics available.

Composite Key Pattern

Most seller tables use a composite key:

request_id + transaction_type

Always join on BOTH fields, not just request_id.

EFR (Expected Future Revenue)

Two versions exist:

• EFR v1: sell_revenue_est.rep_efr - Original calculation
• EFR v2: seller_lead_efr_paid_search.rep_efr_v2 - Updated model for paid search

Best practice: Use COALESCE to prefer v2:

COALESCE(ra.rep_efr_v2, r.rep_efr) AS efr

Note: GQ/LQ coefficients impact EFR - GQ leads are valued at ~147% of baseline, LQ at ~75%.

Home Value

Use estimated_home_value from sell_revenue_est for property value analysis:

SELECT
    CASE 
        WHEN estimated_home_value < 150000 THEN 'Under $150K'
        WHEN estimated_home_value < 300000 THEN '$150K-$300K'
        WHEN estimated_home_value < 500000 THEN '$300K-$500K'
        WHEN estimated_home_value < 750000 THEN '$500K-$750K'
        WHEN estimated_home_value >= 750000 THEN '$750K+'
        ELSE 'Unknown'
    END AS home_value_bucket,
    COUNT(DISTINCT request_id) AS leads
FROM rdc_marketing.seller.sell_revenue_est
GROUP BY 1;

Lead Quality Metrics

Quality can be measured at different points in the lead lifecycle. Choose the right metric based on how quickly you need signal vs. how accurate you need it to be.

Quality Metrics Hierarchy

GQ/LQ Quality Scoring (Sell Leads Only)

The sell_lead_quality table provides immediate quality classification for sell intent leads only. Buy leads do not have GQ/LQ scores.

V0 Qualification Criteria (GQ = Good Quality):

• Property is residential (not land)
• Property is not a mobile home
• Estimated home value ≥ $150K
• Lead submitter matches property in CoreLogic data (deed or mortgage records)
• Lead passes S4 validation checks (email, phone, spam filtering)

Quality Level Values:

• 'GQ' - Good Quality: meets all criteria above
• 'LQ' - Low Quality: fails one or more criteria

Performance Difference:

• GQ leads: ~147% of baseline 180-day sold rate
• LQ leads: ~75% of baseline 180-day sold rate

Important: When analyzing quality for mixed buy/sell campaigns, GQ/LQ will be NULL for all buy leads. Filter to transaction_type = 'sell' for accurate GQ rates.

-- Correct: GQ rate for sell leads only
SELECT
    COUNT(DISTINCT CASE WHEN q.quality_level = 'GQ' THEN a.request_id END) AS gq_leads,
    COUNT(DISTINCT a.request_id) AS total_sell_leads,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN q.quality_level = 'GQ' THEN a.request_id END) 
          / NULLIF(COUNT(DISTINCT a.request_id), 0), 1) AS gq_rate
FROM rdc_marketing.seller.sell_attribution AS a
LEFT JOIN rdc_analytics.ons.sell_lead_quality AS q 
    ON a.request_id = q.request_id 
    AND a.transaction_type = q.transaction_type
WHERE a.transaction_type = 'sell'  -- Filter to sell leads for GQ analysis
  AND a.lead_date >= DATEADD('day', -30, CURRENT_DATE());

Downstream Quality Metrics (All Leads)

For buy leads, or when you need confirmation of lead quality beyond the initial GQ/LQ score, use downstream metrics from sell_downfunnel:

UCA 14d (Unconfirmed Award within 14 days):

COUNT(DISTINCT CASE 
    WHEN DATEDIFF('day', d.created_date, d.matchdate) <= 14 
    THEN d.request_id 
END) AS uca_14d

Awarded 30d:

COUNT(DISTINCT CASE WHEN d.awarded_30d = TRUE THEN d.request_id END) AS awarded_30d

When to Use Each Metric

Campaign Classification

There is no campaign_type field - derive from naming conventions:

CASE 
    WHEN LOWER(campaign_name) LIKE '%pmax%' THEN 'PMAX'
    WHEN LOWER(campaign_name) LIKE '%dsa%' THEN 'DSA'
    WHEN LOWER(campaign_name) LIKE '%brand%' THEN 'Brand'
    WHEN LOWER(campaign_name) LIKE '%longtail%' THEN 'Brand'
    WHEN LOWER(campaign_name) LIKE '%agent%' THEN 'Agent'
    WHEN LOWER(campaign_name) LIKE '%sell%' 
         OR LOWER(campaign_name) LIKE '%fsbo%' THEN 'Sell'
    ELSE 'Others'
END AS campaign_label

Channel & Audience Dimensions

The sell_spend table contains multiple marketing channels, audience segments, and ad partners.

Available Channels

Target Customer Segments

Ad Partners

Channel × Target Customer × Partner Matrix

Common Filter Patterns

B2C Paid Search (most common):

WHERE channel = 'paid search'
  AND target_customer = 'b2c'

All Paid Search (B2B + B2C):

WHERE channel = 'paid search'

Facebook/Social Only:

WHERE channel = 'display/social ads'

All B2B Marketing:

WHERE target_customer = 'b2b'

Cross-Channel Analysis:

SELECT 
    channel,
    target_customer,
    partner,
    SUM(spend) AS spend
FROM rdc_marketing.seller.sell_spend
WHERE calendar_date >= DATEADD('day', -30, CURRENT_DATE())
GROUP BY 1, 2, 3
ORDER BY spend DESC;

Common Query Patterns

Pattern 1: Spend + Leads Combined

The canonical join pattern for spend and lead metrics:

SELECT
    s.calendar_date AS event_date,
    s.campaign_name,
    SUM(s.spend) AS spend,
    SUM(s.clicks) AS clicks,
    SUM(leads.lead_count) AS leads,
    SUM(leads.sell_lead_count) AS sell_intent_leads,
    SUM(leads.efr) AS efr
FROM rdc_marketing.seller.sell_spend AS s
LEFT JOIN (
    -- Leads subquery aggregated to spend grain
    SELECT 
        a.lead_date,
        a.campaign_id,
        COALESCE(a.adgroup_id, '') AS adgroup_id,
        COUNT(DISTINCT r.request_id) AS lead_count,
        COUNT(DISTINCT CASE WHEN r.transaction_type = 'sell' THEN r.request_id END) AS sell_lead_count,
        SUM(COALESCE(ra.rep_efr_v2, r.rep_efr)) AS efr
    FROM rdc_marketing.seller.sell_attribution AS a
    JOIN rdc_marketing.seller.sell_revenue_est AS r 
        ON r.request_id = a.request_id 
        AND r.transaction_type = a.transaction_type
    LEFT JOIN rdc_analytics.revenue.seller_lead_efr_paid_search AS ra 
        ON ra.request_id = a.request_id 
        AND ra.transaction_type = a.transaction_type
    GROUP BY 1, 2, 3
) AS leads 
    ON s.campaign = leads.campaign_id 
    AND s.adgroup_id = leads.adgroup_id  
    AND s.calendar_date = leads.lead_date
WHERE s.channel = 'paid search'
  AND s.target_customer = 'b2c'
  AND s.calendar_date >= DATEADD('day', -30, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY 1 DESC;

Pattern 2: Lead Quality Analysis (Sell Leads)

SELECT
    DATE_TRUNC('week', a.lead_date) AS week,
    COUNT(DISTINCT a.request_id) AS total_sell_leads,
    COUNT(DISTINCT CASE WHEN q.quality_level = 'GQ' THEN a.request_id END) AS gq_leads,
    COUNT(DISTINCT CASE WHEN q.quality_level = 'LQ' THEN a.request_id END) AS lq_leads,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN q.quality_level = 'GQ' THEN a.request_id END) 
          / NULLIF(COUNT(DISTINCT a.request_id), 0), 1) AS gq_rate
FROM rdc_marketing.seller.sell_attribution AS a
LEFT JOIN rdc_analytics.ons.sell_lead_quality AS q 
    ON a.request_id = q.request_id 
    AND a.transaction_type = q.transaction_type
WHERE a.transaction_type = 'sell'  -- GQ/LQ only applies to sell leads
  AND a.lead_date >= DATEADD('day', -90, CURRENT_DATE())
GROUP BY 1
ORDER BY 1 DESC;

Pattern 3: Campaign Type Performance

WITH campaign_classified AS (
    SELECT
        s.*,
        CASE 
            WHEN LOWER(campaign_name) LIKE '%pmax%' THEN 'PMAX'
            WHEN LOWER(campaign_name) LIKE '%dsa%' THEN 'DSA'
            WHEN LOWER(campaign_name) LIKE '%brand%' THEN 'Brand'
            WHEN LOWER(campaign_name) LIKE '%longtail%' THEN 'Brand'
            WHEN LOWER(campaign_name) LIKE '%agent%' THEN 'Agent'
            WHEN LOWER(campaign_name) LIKE '%sell%' OR LOWER(campaign_name) LIKE '%fsbo%' THEN 'Sell'
            ELSE 'Others'
        END AS campaign_label
    FROM rdc_marketing.seller.sell_spend AS s
    WHERE channel = 'paid search'
      AND target_customer = 'b2c'
)
SELECT
    campaign_label,
    SUM(spend) AS total_spend,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    ROUND(SUM(spend) / NULLIF(SUM(clicks), 0), 2) AS cpc
FROM campaign_classified
WHERE calendar_date >= DATEADD('day', -30, CURRENT_DATE())
GROUP BY 1
ORDER BY total_spend DESC;

Pattern 4: Cross-Channel Performance Comparison

SELECT
    channel,
    target_customer,
    partner,
    SUM(spend) AS total_spend,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks,
    ROUND(SUM(spend) / NULLIF(SUM(clicks), 0), 2) AS cpc,
    ROUND(100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), 2) AS ctr
FROM rdc_marketing.seller.sell_spend
WHERE calendar_date >= DATEADD('day', -30, CURRENT_DATE())
GROUP BY 1, 2, 3
ORDER BY total_spend DESC;

Pattern 5: Full Quality Funnel Analysis

WITH lead_quality AS (
    SELECT 
        a.request_id,
        a.transaction_type,
        a.lead_date,
        q.quality_level,
        d.matchdate,
        d.awarded_30d,
        r.actualizedrev,
        CASE WHEN DATEDIFF('day', d.created_date, d.matchdate) <= 14 THEN 1 ELSE 0 END AS uca_14d_flag
    FROM rdc_marketing.seller.sell_attribution AS a
    JOIN rdc_marketing.seller.sell_revenue_est AS r
        ON r.request_id = a.request_id AND r.transaction_type = a.transaction_type
    LEFT JOIN rdc_analytics.ons.sell_lead_quality AS q
        ON a.request_id = q.request_id AND a.transaction_type = q.transaction_type
    LEFT JOIN rdc_marketing.seller.sell_downfunnel AS d
        ON d.request_id = a.request_id AND d.transaction_type = a.transaction_type
    WHERE a.lead_date >= DATEADD('day', -90, CURRENT_DATE())
)
SELECT
    transaction_type,
    COALESCE(quality_level, 'N/A (Buy)') AS quality_level,
    COUNT(DISTINCT request_id) AS leads,
    SUM(uca_14d_flag) AS uca_14d,
    COUNT(DISTINCT CASE WHEN awarded_30d THEN request_id END) AS awarded_30d,
    SUM(actualizedrev) AS actualized_revenue
FROM lead_quality
GROUP BY 1, 2
ORDER BY 1, 2;

Outage/Incident Analysis Pattern

For analyzing impact of site outages on seller leads:

-- Compare outage period to baseline
WITH daily_metrics AS (
    SELECT
        s.calendar_date,
        SUM(s.spend) AS spend,
        SUM(s.clicks) AS clicks,
        SUM(leads.lead_count) AS leads,
        -- Flag outage dates
        CASE WHEN s.calendar_date BETWEEN '2024-12-01' AND '2024-12-03' 
             THEN 'outage' ELSE 'normal' END AS period_type
    FROM rdc_marketing.seller.sell_spend AS s
    LEFT JOIN (
        -- leads subquery here
    ) AS leads ON ...
    WHERE s.calendar_date BETWEEN '2024-11-15' AND '2024-12-15'
    GROUP BY 1
)
SELECT
    period_type,
    AVG(leads) AS avg_daily_leads,
    AVG(leads / NULLIF(clicks, 0)) AS avg_conversion_rate
FROM daily_metrics
GROUP BY 1;

Metric Definitions

Data Freshness

Note: Most seller data has T-1 latency (yesterday's data available today).

Common Gotchas

1. Adgroup ID nulls: Use COALESCE(adgroup_id, '') when joining
2. Transaction type: Always include in joins and filters
3. EFR versions: Check if v2 exists before using v1
4. Campaign classification: Based on naming, not a field
5. Date alignment: Spend is calendar_date, leads is lead_date
6. Channel naming inconsistency: Note display/social ads vs display_social_advertising - different naming for Facebook vs Google display
7. EFR v2 scope: seller_lead_efr_paid_search only covers paid search leads - other channels use v1 only
8. GQ/LQ is sell-only: Buy leads will always have NULL in sell_lead_quality - don't include them in GQ rate calculations
9. Year in date filters: Double-check year when using date literals (e.g., '2025-11-15' vs '2024-11-15')