📊 Chapter 3.4: Advertising & Marketing — SQL-Based KPI Engineering

👉 Previous: Measuring Customer Experience

This chapter focuses on deriving marketing KPIs using SQL from the thelook_ecommerce dataset. Each metric is engineered for reusability in analysis and dashboarding.

🛒 Session-Based Conversion Rate: Purchases / Total Sessions

Measures the proportion of sessions that result in a purchase.

Formula:

SQL Logic: Aggregate events per session_id, flag sessions with ‘purchase’ events, then compute average.

      WITH sessions AS (
  SELECT 
    session_id,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchase_flag
  FROM `bigquery-public-data.thelook_ecommerce.events`
  GROUP BY session_id
)
SELECT 
  AVG(purchase_flag) AS conversion_rate,
  COUNT(*) AS total_sessions
FROM sessions;
    

• ✅ Insight: 26.6% of sessions convert to purchases — a strong baseline for funnel optimization.

🧮 Event-Based Conversion Rate: Purchases / Total Events

Measures how frequently all user interactions lead to a purchase.

Formula:

SQL Logic: Count total events and purchase events directly.

      SELECT
  COUNTIF(event_type = 'purchase') / COUNT(*) AS conversion_rate,
  COUNT(*) AS total_events
FROM `bigquery-public-data.thelook_ecommerce.events`;
    

• ⚠️ Note: Lower than session-based rate — expected, as most events are non-transactional (e.g., views).

🛍️ Upstream Funnel Metrics: Cart & Product View Rates

Extend the session model to track engagement depth.

      WITH sessions AS (
  SELECT 
    session_id,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchase_flag,
    MAX(CASE WHEN event_type = 'cart' THEN 1 ELSE 0 END) AS cart_flag,
    MAX(CASE WHEN event_type = 'product' THEN 1 ELSE 0 END) AS product_flag
  FROM `bigquery-public-data.thelook_ecommerce.events`
  GROUP BY session_id
)
SELECT
  AVG(purchase_flag) AS conversion_rate,
  AVG(cart_flag) AS cart_rate,
  AVG(product_flag) AS product_view_rate,
  COUNT(*) AS total_sessions
FROM sessions;
    

• 🔍 Insight: 63.3% of sessions include cart interaction — opportunity to improve cart-to-purchase flow.

📌 Summary

This chapter demonstrates how to engineer key marketing KPIs using SQL on the thelook_ecommerce dataset. It covers the calculation of session-based and event-based conversion rates, along with upstream funnel metrics like cart and product view rates. Each query is designed for reusability in both analytical workflows and dashboard development, providing a solid foundation for data-driven marketing insights.

👉 Next: Chapter 3.5 – Measuring Inbentory and Supply Chain