Replacing Segment Computed & SQL Traits With dbt & RudderStack Warehouse Actions

with cte_domain_rank as (
        -- Find the Domain Rank of each user
        select user_id
            , first_identify_call as first_conversion_date
            , case when lower(split_part(user_id,'@',2))   like any ('%gmail%','%yahoo', '%qq%' , '%outlook%','%hotmail%','%rediff%') then user_id else lower(split_part(user_id,'@',2)) end as user_domain
            , dense_rank () OVER (Partition by
                                    case when lower(split_part(user_id,'@',2))   like any ('%gmail%','%yahoo', '%qq%' , '%outlook%','%hotmail%','%rediff%') then user_id else lower(split_part(user_id,'@',2)) end order by first_identify_call) as domain_rank
        From (  Select user_id, min(SENT_AT) as first_identify_call
                From "RUDDER_WEBAPP_DATA"."RUDDERWEBAPP"."IDENTIFIES"
                where user_id like '%@%' -- exclude junk since all user_id's are now email addresses
                Group by user_id
            ) as user_firsts
        Order by 3, 4
   ),

cte_engagement as (
        -- Find engagement metrics for each user
        Select user_id
            ,sum(case when lower(EVENT_TEXT) = 'transformation created' then 1 else 0 end) as transformations_created
            ,sum(case when lower(EVENT_TEXT) = 'new source created' then 1 else 0 end) as sources_created
            ,sum(case when lower(EVENT_TEXT) = 'new destination created' then 1 else 0 end) as destinations_created
            ,sum(case when lower(EVENT_TEXT) = 'connection created' then 1 else 0 end) as connections_created
        From "RUDDER_WEBAPP_DATA"."RUDDERWEBAPP"."TRACKS"
        where lower(EVENT_TEXT) like '%created%'
        group by user_id
),

cte_sf_lead as (
        -- Find the Salesforce Lead for each user
        Select ID as LEAD_ID
                , lower(EMAIL) as EMAIL
                , FIRSTNAME
                , LASTNAME
                , IFF(MQL__C = 'TRUE', 1, 0) as MQL__C
                , INDUSTRY
                from "RUDDER_SALESFORCE"."PUBLIC"."LEAD"
                where email is not null
)

-- Bring it all together and calculate a lead score, giving added weight to Marketing Qualified Leads in Salesforce as well as Connections Created
Select r.*
        , e.transformations_created, e.sources_created, e.destinations_created, e.connections_created
        , s.*
        , case when s.MQL__C = 1 Then 100 else 1 End
            + coalesce(sources_created, 0)
            + coalesce(destinations_created, 0)
            + (coalesce(connections_created, 0)) * 10
         as lead_score
From cte_domain_rank r
    Left outer join cte_engagement e on r.user_id = e.user_id
    left outer join cte_sf_lead s on r.user_id = s.email

SELECT * FROM "DEMO_DB"."dbt_USE_CASES"."CUSTOMER_IO_DEMO"

export function rebuild_object(input_string){
        const base_array = JSON.parse(input_string);
        const new_object = base_array.reduce((obj,key) => {
            if( key[1] ){
                obj[key[0]] = key[1];
            }
            return obj;
        }, {});
        return new_object;
}

export function compare_truths(new_truth, old_truth){
        let has_to_be_synced = false;
        Object.keys(new_truth).forEach(key => {
            /**
             * We have to sync if
             *  -- new_truth contains a key old_truth does not
             *  -- the value of a given key in new_truth != old_truth
             */
            const new_key = !(key in old_truth);
            const changed_values = old_truth[key] !== new_truth[key];
            if ( new_key || changed_values ){
                log(`have to sync because key ${key} is either not in old_truth or has changed from  ${old_truth[key]} to ${new_truth[key]}`);
                has_to_be_synced = true;
            }
        })

        return has_to_be_synced;
}

import { rebuild_object, compare_truths } from 'warehouseActionsUtils';

export function transformEvent(event, metadata) {

    const new_truth_obj = rebuild_object(event.traits.new_truth);
    const old_truth_obj = rebuild_object(event.traits.old_truth);

    if ( event.traits.sfdc_id ) {
        event.context = event.context || {};
        event.context.externalId = event.context.externalId || [];
        event.context.externalId.push({
            type: 'Salesforce-Account',
            id: event.traits.sfdc_id
        });
        // make sure sfdc gets events too (no worries there is a filter on the SFDC destination to prevent spam)
        event.integrations = {...event.integrations, ...{'Salesforce': true, 'All': true}};
    }

    if ( compare_truths(new_truth_obj, old_truth_obj) ){
        event.traits = new_truth_obj;

        event.groupId = event.traits.group_id;
        event['type'] = 'group';
        delete event.messageId;

        return event;
    } else {
        return;
    }

}