BigQuery Essentials for SourceMedium

select
  date(order_processed_at_local_datetime) as order_date,
  count(distinct sm_order_key) as orders,
  count(distinct sm_customer_key) as customers,
  sum(order_gross_revenue) as gross_revenue,
  sum(order_total_discounts) as discounts,
  sum(order_net_revenue) as net_revenue
from `your_project.sm_transformed_v2.obt_orders`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1
order by 1 desc

select
  sm_channel,
  count(distinct sm_order_key) as orders,
  sum(order_net_revenue) as revenue,
  round(sum(order_net_revenue) / count(distinct sm_order_key), 2) as aov
from `your_project.sm_transformed_v2.obt_orders`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1
order by revenue desc

select
  date(order_processed_at_local_datetime) as order_date,
  order_sequence as customer_type,
  count(distinct sm_order_key) as orders,
  count(distinct sm_customer_key) as customers,
  sum(order_net_revenue) as revenue
from `your_project.sm_transformed_v2.obt_orders`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1, 2
order by 1 desc, 2

select
  product_title,
  sum(order_line_quantity) as units_sold,
  count(distinct sm_order_key) as orders,
  sum(order_line_net_revenue) as revenue
from `your_project.sm_transformed_v2.obt_order_lines`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1
order by revenue desc
limit 20

select
  coalesce(sm_utm_source, '(direct)') as source,
  coalesce(sm_utm_medium, '(none)') as medium,
  count(distinct sm_order_key) as orders,
  sum(order_net_revenue) as revenue
from `your_project.sm_transformed_v2.obt_orders`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1, 2
order by revenue desc
limit 20

with daily_spend as (
  select
    date as report_date,
    sm_channel,
    sum(ad_spend) as spend,
    sum(ad_impressions) as impressions,
    sum(ad_clicks) as clicks
  from `your_project.sm_transformed_v2.rpt_ad_performance_daily`
  where date >= date_sub(current_date(), interval 30 day)
  group by 1, 2
),
daily_revenue as (
  select
    date(order_processed_at_local_datetime) as order_date,
    sm_channel,
    sum(order_net_revenue) as revenue,
    count(distinct sm_order_key) as orders
  from `your_project.sm_transformed_v2.obt_orders`
  where is_order_sm_valid = true
    and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
  group by 1, 2
)
select
  coalesce(s.report_date, r.order_date) as date,
  coalesce(s.sm_channel, r.sm_channel) as channel,
  s.spend,
  r.revenue,
  r.orders,
  round(safe_divide(r.revenue, s.spend), 2) as roas
from daily_spend s
full outer join daily_revenue r
  on s.report_date = r.order_date
  and s.sm_channel = r.sm_channel
order by 1 desc, 2