Direct Attribution Model - Working

direct_attr_model_working.sql

{{
  config(
    tags=["looker", "business_insights", "daily", "admin", "fact"],
    persist_docs={"relation": true, "columns": true}
  )
}}

with 

campaigns as (

    select * from {{ ref('dim_campaign_service_campaigns') }}
    where
        campaign_type in ('DOUBLE_REWARDS', 'DEAL')

),

with 

campaign_direct_attribution as (
    -- This source doesn't exist yet, see https://hotelengine.atlassian.net/servicedesk/customer/portal/1544/DI-50
    select * from {{ bronze_airbyte_source('campaigns_service__campaign_direct_attribution') }}

),

transactions as (
    select * from {{ ref('stg_sparc__wallet_transactions') }}
    where type in ('CAMPAIGN_2XR', 'CAMPAIGN_DEAL')
),

bookings as (

    select
        contract_number,
        supplier,
        room_nights,
        hotel_subtotal
    from {{ ref('fct_booked_rooms') }}
    where
        supplier != 'Manual'
        and status in ('reserved', 'pending', 'booked', 'completed', 'visiting')
        -- Do not include bookings with refunded amounts which may be negative
        and hotel_subtotal >= 0

),

final as (

    select
        campaigns.hotel_id,
        campaigns.campaign_id,
        campaigns.campaign_detail_id,
        campaigns.campaign_type,
        campaign_direct_attribution.attributed_at as created_at,
        campaign_direct_attribution.contract_number,
        bookings.room_nights,
        bookings.hotel_subtotal,
        bookings.supplier,
        (transactions.transaction_amount * -1) as transaction_amount
    from campaign_direct_attribution
    inner join bookings
        on campaign_direct_attribution.contract_number = bookings.contract_number
    inner join campaigns
        on campaign_direct_attribution.campaign_id = campaigns.campaign_id
    left join transactions
        on campaign_direct_attribution.transaction_id = transactions.transaction_id
)

select * from final

{{
  config(
    post_hook=[
      "grant select on {{ this }} to role auditor_role",
      "grant select on {{ this }} to role sparc_role",
      "grant select on {{ this }} to role looker_role"
    ]
  )
}}