SEM & Ecommerce Metrics Reference

This skill provides domain knowledge for Search Engine Marketing (SEM), paid social, and ecommerce analytics. Use this to understand how metrics flow from ad platforms through analytics to business outcomes.

Core Data Sources

Product Feed (Merchant Center / Catalog)

The master product catalog containing:

Key Concepts:

• Variant vs Product: One product (shoes) has many variants (sizes)
• item_group_id: Groups all variants of same product
• Custom Labels: Used to segment products in ad campaigns

Google Analytics 4 (GA4)

User behavior and conversion data:

Key Concepts:

• Attribution: Revenue attributed to last-click item
• Item ID matching: GA4 uses item_id field
• Aggregated "(other)": Low-volume items grouped together
• Data freshness: 7-day refresh window

Google Ads

Ad performance and cost data:

Key Concepts:

• OfferId: Maps to product feed g_id
• Attribution window: Default 30 days
• Fractional conversions: Multi-touch attribution
• Data freshness: 60-day refresh window

Meta (Facebook/Instagram) Ads

Paid social performance:

Key Concepts:

• Product-level attribution: At item_group_id level
• Data freshness: 14-day retention window only
• Pixel attribution: 7-day click, 1-day view default

Key Metrics & KPIs

Revenue Metrics

Cost Metrics

Efficiency Metrics

Engagement Metrics (GA4)

Time Windows

Metrics are aggregated over different time periods:

Why multiple windows?

• Short window captures trends but has noise
• Long window is stable but masks recent changes
• Combined view enables trend detection

Product Performance Segmentation

By Revenue Contribution

By ROAS

By Stock Status

Multi-Channel Attribution

Channel Priority for Scoring

When a product performs differently across channels:

1. Google Ads - Most direct conversion attribution
2. GA4 - Captures all traffic sources
3. Meta - Social discovery, upper funnel

Top Seller Definition

A product is a "top seller" if it's in the top 10% for ANY channel:

CASE
  WHEN ga4_revenue_percentile >= 0.90
    OR gads_revenue_percentile >= 0.90
    OR meta_revenue_percentile >= 0.90
  THEN TRUE
END AS is_top_seller

ROAS Calculation by Channel

-- Google Ads ROAS
SAFE_DIVIDE(gads_conversion_value, gads_cost) AS gads_roas

-- Meta ROAS
SAFE_DIVIDE(meta_purchase_value, meta_spend) AS meta_roas

-- Blended ROAS
SAFE_DIVIDE(
  gads_conversion_value + meta_purchase_value,
  gads_cost + meta_spend
) AS blended_roas

Trend Detection

View Velocity

Compares recent views to historical views:

SAFE_DIVIDE(ga4_views_short, ga4_views_long) AS view_velocity
-- > 0.35 indicates upward trend (14d vs 60d)
-- < 0.15 indicates declining interest

Trending Product Criteria

CASE
  WHEN view_velocity > 0.35 AND atc_rate > 0.035
  THEN TRUE
END AS is_trending

Data Quality Considerations

ID Matching Issues

Metric Reliability

Metrics need sufficient volume to be meaningful:

Data Freshness

Business Context

Dynamic Remarketing Campaigns

Products are segmented into campaigns by performance:

Custom Labels (0-4)

Used to segment products in Google Merchant Center:

Analysis Patterns

Find Top Performers

SELECT item_group_id, SUM(revenue) AS total_revenue
FROM metrics
GROUP BY item_group_id
ORDER BY total_revenue DESC
LIMIT 100

Identify Wasted Ad Spend

SELECT
  item_group_id,
  SUM(CASE WHEN NOT is_in_stock THEN gads_cost ELSE 0 END) AS wasted_spend
FROM metrics
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY item_group_id
HAVING wasted_spend > 10
ORDER BY wasted_spend DESC

Channel Mix Analysis

SELECT
  item_group_id,
  SUM(ga4_revenue) AS ga4_revenue,
  SUM(gads_revenue) AS gads_revenue,
  SUM(meta_revenue) AS meta_revenue,
  SAFE_DIVIDE(SUM(gads_revenue), SUM(ga4_revenue)) AS gads_attribution_rate
FROM metrics
GROUP BY item_group_id