How do I add additional info to my Salesforce opportunity data?

This transform enhances your opportunity data by joining in details about the account, recency of the opportunity, and the owner + manager of the opportunity.

with opportunity as (
    
  with opportunity_cte as (
      
      select 
  
          _fivetran_synced,
          account_id,
          amount,
          campaign_id,
          close_date,
          created_date,
          description,
          fiscal,
          fiscal_quarter,
          fiscal_year,
          forecast_category,
          forecast_category_name,
          has_open_activity,
          has_opportunity_line_item,
          has_overdue_task,
          id as opportunity_id,
          is_closed,
          is_deleted,
          is_won,
          last_activity_date,
          last_referenced_date,
          last_viewed_date,
          lead_source,
          name as opportunity_name,
          next_step,
          owner_id,
          probability,
          record_type_id,
          stage_name,
          synced_quote_id,
          type
  
      from salesforce.opportunity
  
  ), opportunity_calculated as (
          
      select 
          *,
          created_date >= date_trunc('month', current_timestamp()) as is_created_this_month,
          created_date >= date_trunc('quarter',current_timestamp()) as is_created_this_quarter,
          datediff('day', created_date, current_timestamp()) as days_since_created,
          datediff('day', created_date, close_date) as days_to_close,
          date_trunc('month', close_date) = date_trunc('month', current_timestamp()) as is_closed_this_month,
          date_trunc('quarter', close_date) = date_trunc('quarter', current_timestamp()) as is_closed_this_quarter
  
      from opportunity_cte
  
  )
  
  select * 
  from opportunity_calculated
  where not coalesce(is_deleted, false)

), user as (

  with user_cte as (
      
      select 
  
          _fivetran_deleted,
          _fivetran_synced,
          account_id,
          alias,
          city,
          company_name,
          contact_id,
          country,
          department,
          email,
          first_name,
          id as user_id,
          individual_id,
          is_active,
          last_login_date,
          last_name,
          last_referenced_date,
          last_viewed_date,
          manager_id,
          name as user_name,
          postal_code,
          profile_id,
          state,
          street,
          title,
          user_role_id,
          user_type,
          username 
      
      from salesforce.user
  
  )
  
  select * 
  from user_cte
  where not coalesce(_fivetran_deleted, false)
  
), account as (

  with account_cte as (
  
      select 
      
        _fivetran_synced,
        account_source,
        billing_city,
        billing_country,
        billing_postal_code,
        billing_state,
        billing_street,
        description,
        id as account_id,
        industry,
        is_deleted,
        last_activity_date,
        last_referenced_date,
        last_viewed_date,
        master_record_id,
        name as account_name,
        number_of_employees,
        owner_id,
        parent_id,
        shipping_city,
        shipping_country,
        shipping_postal_code,
        shipping_state,
        shipping_street,
        type,
        website
        -- You can add more fields from the Salesforce "account" table that haven't been included above here.
        
      from salesforce.account
  )
  
  select *
  from account_cte
  
  -- This excludes any results that have been deleted from the Transform output.
  where not coalesce(is_deleted, false)
  
), add_fields as (

    select 
      opportunity.*,
      account.account_source,
      account.industry,
      account.account_name,
      account.number_of_employees,
      account.type as account_type,
      opportunity_owner.user_id as opportunity_owner_id,
      opportunity_owner.user_name as opportunity_owner_name,
      opportunity_owner.city opportunity_owner_city,
      opportunity_owner.state as opportunity_owner_state,
      opportunity_manager.user_id as opportunity_manager_id,
      opportunity_manager.user_name as opportunity_manager_name,
      opportunity_manager.city opportunity_manager_city,
      opportunity_manager.state as opportunity_manager_state,
      case
        when opportunity.is_won then 'Won'
        when not opportunity.is_won and opportunity.is_closed then 'Lost'
        when not opportunity.is_closed and lower(opportunity.forecast_category) in ('pipeline','forecast','bestcase') then 'Pipeline'
        else 'Other'
      end as status,
      case when is_created_this_month then amount else 0 end as created_amount_this_month,
      case when is_created_this_quarter then amount else 0 end as created_amount_this_quarter,
      case when is_created_this_month then 1 else 0 end as created_count_this_month,
      case when is_created_this_quarter then 1 else 0 end as created_count_this_quarter,
      case when is_closed_this_month then amount else 0 end as closed_amount_this_month,
      case when is_closed_this_quarter then amount else 0 end as closed_amount_this_quarter,
      case when is_closed_this_month then 1 else 0 end as closed_count_this_month,
      case when is_closed_this_quarter then 1 else 0 end as closed_count_this_quarter

    from opportunity
    left join account 
      on opportunity.account_id = account.account_id
    left join user as opportunity_owner 
      on opportunity.owner_id = opportunity_owner.user_id
    left join user as opportunity_manager 
      on opportunity_owner.manager_id = opportunity_manager.user_id
)

select *
from add_fields
LIMIT 100

Learn more about the WITH clause and common table expressions (CTEs) through Snowflake (our data warehouse partner) or Mode (a business intelligence tool partner).