SQL Example: Weekly Performance Metrics (ROAS, CTR, CVR…)

Overview

This query aggregates key performance metrics on a weekly basis, useful for evaluating weekly trends in spending, traffic, and conversions.

SQL Query

SELECT
  greatest(toStartOfWeek(event_date, 1), toDate(@start_date)) AS week_period,
  DATE_DIFF('day',COALESCE(greatest(toStartOfWeek(event_date, 1), toDate(@start_date)), toDate(@start_date)), COALESCE(least(toStartOfWeek(event_date, 1) + INTERVAL 7 DAY, toDate(@end_date) + INTERVAL 1 DAY), toDate(@end_date) + INTERVAL 1 DAY)) AS number_of_days_in_period,
  SUM(pjt.spend) AS spend,
  SUM(pjt.orders_quantity) AS orders_quantity,
  SUM(pjt.sessions) AS sessions,
  SUM(pjt.clicks) AS clicks,
  SUM(pjt.impressions) AS impressions,
  SUM(pjt.order_revenue) AS order_revenue,
  IFNULL(SUM(pjt.order_revenue)/nullIf(SUM(pjt.spend), 0), 0) AS pixel_roas,
  IFNULL(SUM(pjt.spend)/nullIf(SUM(pjt.new_customer_orders), 0), 0) AS ncpa,
  IFNULL(SUM(pjt.spend)/nullIf(SUM(pjt.clicks), 0), 0) AS cpc,
  IFNULL(SUM(pjt.clicks)/nullIf(SUM(pjt.impressions), 0), 0) AS ctr,
  IFNULL(SUM(pjt.website_purchases)/nullIf(SUM(pjt.sessions), 0), 0) AS pixel_conversion_rate,
  IFNULL(SUM(pjt.spend)/nullIf(SUM(pjt.impressions), 0) * 1000, 0) AS cpm
FROM
  pixel_joined_tvf AS pjt
WHERE
  event_date BETWEEN @start_date AND @end_date
GROUP BY
  GROUPING SETS ((toStartOfWeek(event_date, 1)), ())
ORDER BY
  week_period

Tables and Metrics Used

Table

pixel_joined_tvf (Pixel Joined Table)

Measures

spend
orders_quantity
sessions
clicks
impressions
order_revenue
new_customer_orders
website_purchases

Derived Metrics

pixel_roas = order_revenue / spend
ncpa = spend / new_customer_orders
cpc = spend / clicks
ctr = clicks / impressions
pixel_conversion_rate = website_purchases / sessions
cpm = spend / impressions * 1000

Customization Options

Switch to monthly aggregation

Replace toStartOfWeek(event_date, 1) with toStartOfMonth(event_date).

Filter by ad channel or campaign

Add conditions to the WHERE clause like channel = 'facebook-ads' or campaign_name LIKE '%prospecting%'.

Change first day of the week

toStartOfWeek(event_date, 0) → Week starts on Sunday
toStartOfWeek(event_date, 1) → Week starts on Monday (default)

Updated 3 months ago