Marketplace Revenue Alert

-- models/marketplace_revenue_alert.sql
{{ config(materialized='view') }}

-- Step 3, timeframe aggregates for 4 week window and last week window
select
    x.created_date,
    x.account_id,
    x.brand_profile_id,
    x.marketplace_revenue,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 6 preceding and current row) as marketplace_revenue_7_days,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 34 preceding and 7 preceding) / 4 as marketplace_revenue_prev_4_wk_avg
from
-- Step 1, zero fill calendar dates per brand profile
(
  select
      z.created_date,
      z.account_id,
      z.brand_profile_id,
      coalesce(y.marketplace_revenue,0) as marketplace_revenue
  from
  (
    select
      a.created_date,
      b.account_id,
      b.brand_profile_id
    from
    (
      select
        current_date - cast((row_number() over (order by null) - 1) as integer) as created_date
      from
        articles
    ) as a
    cross join
    (
      select
      c.id as account_id,
      b.id as brand_profile_id
      from
      profiles as b
    left join
      client_accounts as c
        on b.account_id = c.id
      group by 1,2
    ) as b
    where
      a.created_date >= current_date - 34
   ) as z
  left join
  -- Step 2, aggregate financial_transactions to match calendar zero fill per brand profile
  (
    select
      cast(a.created_at as date) as created_date,
      c.id as account_id,
      b.id as brand_profile_id,
      (
        -1.0 * (SUM(CASE WHEN a.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') AND (case
          when a.user_id is null
          then 0
          else 1
        end
         = 0) THEN (CASE a.type
          WHEN  'Debit'
          THEN a.amount / -100
          ELSE a.amount / 100
        END
        ) ELSE NULL END))
      ) AS marketplace_revenue
    from
      financial_transactions AS a
    inner join
      profiles as b
        on a.brand_profile_id = b.id
        and b.created_at < current_date - 60
    left join
      client_accounts as c
        on b.account_id = c.id
    group by 1,2,3
  ) as y
    on z.created_date = y.created_date
    and z.account_id = y.account_id
    and z.brand_profile_id = y.brand_profile_id
) as x

# models/schema.yml
version: 2

models:
  - name: marketplace_revenue_alert
    columns:
      - name: created
      - name: account_id
      - name: brand_profile_id
      - name: marketplace_revenue
      - name: marketplace_revenue_7_days
      - name: marketplace_revenue_prev_4_wk_avg
      - name: marketplace_revenue_creation_alert

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

-- Step 3, timeframe aggregates for 4 week window and last week window
select
    x.created_date,
    x.account_id,
    x.brand_profile_id,
    x.marketplace_revenue,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 6 preceding and current row) as marketplace_revenue_7_days,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 34 preceding and 7 preceding) / 4 as marketplace_revenue_prev_4_wk_avg
from
-- Step 1, zero fill calendar dates per brand profile
(
  select
      z.created_date,
      z.account_id,
      z.brand_profile_id,
      coalesce(y.marketplace_revenue,0) as marketplace_revenue
  from
  (
    select
      a.created_date,
      b.account_id,
      b.brand_profile_id
    from
    (
      select
        current_date - cast((row_number() over (order by null) - 1) as integer) as created_date
      from
        articles
    ) as a
    cross join
    (
      select
      c.id as account_id,
      b.id as brand_profile_id
      from
      profiles as b
    left join
      client_accounts as c
        on b.account_id = c.id
      group by 1,2
    ) as b
    where
      a.created_date >= current_date - 34
   ) as z
  left join
  -- Step 2, aggregate financial_transactions to match calendar zero fill per brand profile
  (
    select
      cast(a.created_at as date) as created_date,
      c.id as account_id,
      b.id as brand_profile_id,
      (
        -1.0 * (SUM(CASE WHEN a.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') AND (case
          when a.user_id is null
          then 0
          else 1
        end
         = 0) THEN (CASE a.type
          WHEN  'Debit'
          THEN a.amount / -100
          ELSE a.amount / 100
        END
        ) ELSE NULL END))
      ) AS marketplace_revenue
    from
      financial_transactions AS a
    inner join
      profiles as b
        on a.brand_profile_id = b.id
        and b.created_at < current_date - 60
    left join
      client_accounts as c
        on b.account_id = c.id
    group by 1,2,3
  ) as y
    on z.created_date = y.created_date
    and z.account_id = y.account_id
    and z.brand_profile_id = y.brand_profile_id
) as x

view: marketplace_revenue_alert {
  derived_table: {
    sql: -- Step 3, timeframe aggregates for 4 week window and last week window
select
    x.created_date,
    x.account_id,
    x.brand_profile_id,
    x.marketplace_revenue,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 6 preceding and current row) as marketplace_revenue_7_days,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 34 preceding and 7 preceding) / 4 as marketplace_revenue_prev_4_wk_avg
from
-- Step 1, zero fill calendar dates per brand profile
(
  select
      z.created_date,
      z.account_id,
      z.brand_profile_id,
      coalesce(y.marketplace_revenue,0) as marketplace_revenue
  from
  (
    select
      a.created_date,
      b.account_id,
      b.brand_profile_id
    from
    (
      select
        current_date - cast((row_number() over (order by null) - 1) as integer) as created_date
      from
        articles
    ) as a
    cross join
    (
      select
      c.id as account_id,
      b.id as brand_profile_id
      from
      profiles as b
    left join
      client_accounts as c
        on b.account_id = c.id
      group by 1,2
    ) as b
    where
      a.created_date >= current_date - 34
   ) as z
  left join
  -- Step 2, aggregate financial_transactions to match calendar zero fill per brand profile
  (
    select
      cast(a.created_at as date) as created_date,
      c.id as account_id,
      b.id as brand_profile_id,
      (
        -1.0 * (SUM(CASE WHEN a.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') AND (case
          when a.user_id is null
          then 0
          else 1
        end
         = 0) THEN (CASE a.type
          WHEN  'Debit'
          THEN a.amount / -100
          ELSE a.amount / 100
        END
        ) ELSE NULL END))
      ) AS marketplace_revenue
    from
      financial_transactions AS a
    inner join
      profiles as b
        on a.brand_profile_id = b.id
        and b.created_at < current_date - 60
    left join
      client_accounts as c
        on b.account_id = c.id
    group by 1,2,3
  ) as y
    on z.created_date = y.created_date
    and z.account_id = y.account_id
    and z.brand_profile_id = y.brand_profile_id
) as x ;;
  }

  dimension: created {
    type: time
    sql: ${TABLE}.created_date ;;
  }

  dimension: account_id {
    type: number
    hidden: yes
    sql: ${TABLE}.account_id ;;
  }

  dimension: brand_profile_id {
    type: number
    hidden: yes
    sql: ${TABLE}.brand_profile_id ;;
  }

  dimension: marketplace_revenue {
    type: number
    sql: ${TABLE}.marketplace_revenue ;;
  }

  dimension: marketplace_revenue_7_days {
    type: number
    sql: ${TABLE}.marketplace_revenue_7_days ;;
  }

  dimension: marketplace_revenue_prev_4_wk_avg {
    type: number
    sql: ${TABLE}.marketplace_revenue_prev_4_wk_avg ;;
  }

  dimension: marketplace_revenue_creation_alert {
    sql: case
  when ${entities.created_date} <= current_date - 60
    and ${marketplace_revenue_7_days} < ${marketplace_revenue_prev_4_wk_avg} / 2
    and ${marketplace_revenue_7_days} < 1000
  then 1
  else 0
end ;;
  }

  measure: avg_rev_7_days {
    type: avg
    sql: ${marketplace_revenue_7_days} ;;
  }

  measure: avg_rev_4_week {
    type: avg
    sql: ${marketplace_revenue_prev_4_wk_avg} ;;
  }

  measure: rolling_weekly_percent {
    type: number
    sql: 100.0*${avg_rev_7_days}/NULLIF(${avg_rev_4_week},0) ;;
  }
}

- view: marketplace_revenue_alert
  derived_table:
    sql: -- Step 3, timeframe aggregates for 4 week window and last week window
select
    x.created_date,
    x.account_id,
    x.brand_profile_id,
    x.marketplace_revenue,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 6 preceding and current row) as marketplace_revenue_7_days,
    sum(x.marketplace_revenue) over (partition by x.account_id,x.brand_profile_id order by x.created_date
    rows between 34 preceding and 7 preceding) / 4 as marketplace_revenue_prev_4_wk_avg
from
-- Step 1, zero fill calendar dates per brand profile
(
  select
      z.created_date,
      z.account_id,
      z.brand_profile_id,
      coalesce(y.marketplace_revenue,0) as marketplace_revenue
  from
  (
    select
      a.created_date,
      b.account_id,
      b.brand_profile_id
    from
    (
      select
        current_date - cast((row_number() over (order by null) - 1) as integer) as created_date
      from
        articles
    ) as a
    cross join
    (
      select
      c.id as account_id,
      b.id as brand_profile_id
      from
      profiles as b
    left join
      client_accounts as c
        on b.account_id = c.id
      group by 1,2
    ) as b
    where
      a.created_date >= current_date - 34
   ) as z
  left join
  -- Step 2, aggregate financial_transactions to match calendar zero fill per brand profile
  (
    select
      cast(a.created_at as date) as created_date,
      c.id as account_id,
      b.id as brand_profile_id,
      (
        -1.0 * (SUM(CASE WHEN a.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') AND (case
          when a.user_id is null
          then 0
          else 1
        end
         = 0) THEN (CASE a.type
          WHEN  'Debit'
          THEN a.amount / -100
          ELSE a.amount / 100
        END
        ) ELSE NULL END))
      ) AS marketplace_revenue
    from
      financial_transactions AS a
    inner join
      profiles as b
        on a.brand_profile_id = b.id
        and b.created_at < current_date - 60
    left join
      client_accounts as c
        on b.account_id = c.id
    group by 1,2,3
  ) as y
    on z.created_date = y.created_date
    and z.account_id = y.account_id
    and z.brand_profile_id = y.brand_profile_id
) as x
  fields:
  - dimension: created
    type: time
    sql: ${TABLE}.created_date
  - dimension: account_id
    type: number
    hidden: true
    sql: ${TABLE}.account_id
  - dimension: brand_profile_id
    type: number
    hidden: true
    sql: ${TABLE}.brand_profile_id
  - dimension: marketplace_revenue
    type: number
    sql: ${TABLE}.marketplace_revenue
  - dimension: marketplace_revenue_7_days
    type: number
    sql: ${TABLE}.marketplace_revenue_7_days
  - dimension: marketplace_revenue_prev_4_wk_avg
    type: number
    sql: ${TABLE}.marketplace_revenue_prev_4_wk_avg
  - dimension: marketplace_revenue_creation_alert
    sql: case
  when ${entities.created_date} <= current_date - 60
    and ${marketplace_revenue_7_days} < ${marketplace_revenue_prev_4_wk_avg} / 2
    and ${marketplace_revenue_7_days} < 1000
  then 1
  else 0
end
  - measure: avg_rev_7_days
    type: avg
    sql: ${marketplace_revenue_7_days}
  - measure: avg_rev_4_week
    type: avg
    sql: ${marketplace_revenue_prev_4_wk_avg}
  - measure: rolling_weekly_percent
    type: number
    sql: 100.0*${avg_rev_7_days}/NULLIF(${avg_rev_4_week},0)