Correct Sales attribution

[TomaszE]

Is there an existing feature request for this?

• [X] I have searched the existing issues

Describe the Feature

Volume of sales attributed to campaign atm base on last touch campaign. This takes to considiration last campaign customer was part of , and give us very incorrect numbers ( ie on one of our campaigns converted sales in Klaviyo is 4500 GBP but id klavyio dbt package it's 15000 GBP ) the difference comes from the fact you are looking at last touch campaign but not take to considiration event type. In Klavyio in order to sale to be attributed to campaign there are few steps - user needs to receive an email, open an email and click into the email - if after that step the sale occured - customer will be assigned to that Campaign - without clicked an email event type - sales is being assigned without the campaign id - and it's not counted against the sales. Would it be possible to add that step in dbt modelled data ? So we can see sum_revenue_placed_order only from customer who clicked in campaign emal?

How would you implement this feature?

I am not sure, there would have to be a condition /macro / if statement - if there is a value in sum_placed_order value filter out all persons that does not have "Clicked Email" event type in campaign? somthing like :

select * from analytics.analytics_crm.klaviyo__person_campaign_flow where last_touch_campaign_id = '01J7N9WNT6KT2M3NKGXZ73DGD6' and sum_revenue_placed_order > 0
and person_id in (select  distinct person_id from analytics.analytics_crm.klaviyo__events where campaign_id = '01J7N9WNT6KT2M3NKGXZ73DGD6' and type = 'Clicked Email')

Describe alternatives you've considered

building own model

Are you interested in contributing this feature?

• [ ] Yes.
• [ ] Yes, but I will need assistance.
• [X] No.

Anything else?

No response

[fivetran-jamie]

Hey there @TomaszE, good to hear from you again!

First question -- do you have the klaviyo__eligible_attribution_events variable set to anything? By default, this includes email opens, email clicks, and sms clicks. I wonder if configuring it to not include email opens would resolve these discrepancies (unless there are cases where a user can click an email without opening it 🤔)

Otherwise, are you looking for the package to support more of a funnel/list of attribution-eligible event types rather than individual ones?

[fivetran-jamie]

Just following up here @TomaszE 🙂

[TomaszE]

Hi Jamie,

Sorry to follow up here so late, not sure why I haven't received notification about your first reply.

here is my current set up:

klaviyo__email_attribution_lookback: 72 klaviyo__sms_attribution_lookback: 0 klaviyo__eligible_attribution_events: ['clicked email'] # this is case-SENSITIVE and should be in all lower-case!!

but it's being completely ignored - no matter what i put here, or eevn if I remove that congif part - it always count's open emails .

We had an extended conversation with Klaviyo team about attributtion. Looks like it's base on 3 event's in order for sale to be converted (treated as converted ) 3 event's needs to happen within 72 hours:

1. Email Sent ( count start )

2. Email opened

   3. Email clicked

   4. Placed order

The 3 in bold ( 1,3,4 ) need to happen within first 72 hours from first event - only then klaviyo dashboard will attribute this sale to that particular campaign if even one is missing - then sale is not attributed to any campaign .

Atm there is no change in our numbers - no matter what i put in klaviyo__eligible_attribution_events

Answering your question - we have build Marketing performance dashboard - which is sent to exec team with some klavyio information - we have match on sent, received, opened and clicked emails per campaign but we are completely off when we try to report on revenue / number of people converted brought by klaviyo - dbt package in current state - like I mentioned above overstate the revenue ( and number of customer who placed the order ) by 300-400% compared to the same metrics dashboard directly in Klaviyo

[fivetran-jamie]

Regarding your configs being ignored, I wonder if its a scoping issue...Do you have the variable scoped specifically under klaviyo in your dbt_project.yml? So like

vars:
  klaviyo:
    klaviyo__eligible_attribution_events: ['clicked email']

Either way, it sounds like we need to realign the package's attribution with Klaviyo to support this 3-step 72-hour funnel. Currently, we attribute events to campaigns/flows by looking for just one of these attribution-eligible events in the previous 72 hours.

Do you know how customizable that attribution funnel is, or does Klaviyo apply the same criteria to everyone's conversions?

[TomaszE]

HI Jamie, Thanks for that, I've checked our vars it did not have it scoped under klaviyo: it got lost among all other packages we have from you guys.

This is how it was

vars:
   .
   .
   .
   klaviyo_database: raw
   klaviyo_schema: klaviyo
   klaviyo__email_attribution_lookback: 72
   klaviyo__sms_attribution_lookback: 0
   klaviyo__eligible_attribution_events: ['clicked email'] # this is case-SENSITIVE and should be in all lower-case!!
   .
   .
   .

I have now updated it to :

  .
  .
  .
  klaviyo_database: raw
  klaviyo_schema: klaviyo
  klaviyo:
    klaviyo__email_attribution_lookback: 72
    klaviyo__sms_attribution_lookback: 0
    klaviyo__eligible_attribution_events: ['clicked email'] # this is case-SENSITIVE and should be in all lower-case!!
   .
   .
   .

this have not changed reported revenue, for reference - one of our campaigns revenue reported by klaviyo is £4505 where klaviyo__campagins table give us £18620 - no matter what I would put in the klaviyo scope. Am I doing something wrong ?

I've asked the support team about your last question and got answer:

Hi Tomasz, the model is the same for all customer. The only things that each brand can change are: Metrics which count towards attribution (Open and Click or just Click) Attribution days (we use 3 day window)

[fivetran-jamie]

Interesting -- would you mind sharing the compiled SQL output of int_klaviyo__event_attribution? I'm curious if the variable isn't getting picked up, or if this is a reflection of greater attribution issues.

[fivetran-jamie]

Friendly bump @TomaszE

[TomaszE]

Hi Jamie, again haven't received notification on your request - sorry for that..

This is odd, I can't run the complie sql on that table - gives me blank

and when i select whole code and click run selection i get this :

[fivetran-jamie]

Huh that's very odd but perhaps a quirk of dbt cloud.... If you execute dbt run, does dbt cloud surface the target folder for you? If so, perhaps we can find the compiled code in target/compiled/klaviyo/models/intermediate/

[fivetran-jamie]

@TomaszE 😄

[TomaszE]

Hi Jamie,

Only way to get the code is to run dbt run command on that table - and detailed log i get this :

create or replace temporary table ANALYTICS.dbt_teitner_staging.int_klaviyo__event_attribution__dbt_tmp
         as
        (

with events as (

    select 
        *,
        -- no event will be attributed to both a campaign and flow
        coalesce(campaign_id, flow_id) as touch_id,
        case 
            when campaign_id is not null then 'campaign' 
            when flow_id is not null then 'flow' 
        else null end as touch_type -- defintion: touch = interaction with campaign/flow

    from ANALYTICS.dbt_teitner_staging.stg_klaviyo__event

    -- grab **ALL** events for users who have any events in this new increment
    where person_id in (

        select distinct person_id
        from ANALYTICS.dbt_teitner_staging.stg_klaviyo__event

        -- most events (from all kinds of integrations) at least once every hour
        -- https://help.klaviyo.com/hc/en-us/articles/115005253208
        where _fivetran_synced >= cast(coalesce( 
            (
                select 

    dateadd(
        hour,
        -1,
        max(_fivetran_synced)
        )

                from ANALYTICS.dbt_teitner_staging.int_klaviyo__event_attribution
            ), '2012-01-01') as timestamp ) -- klaviyo was founded in 2012, so let's default the min date to then
    )

),

-- sessionize events based on attribution eligibility -- is it the right kind of event, and does it have a campaign or flow?
create_sessions as (
    select
        *,
        -- default klaviyo__event_attribution_filter limits attribution-eligible events to to email opens, email clicks, and sms opens
        -- https://help.klaviyo.com/hc/en-us/articles/115005248128

        -- events that come with flow/campaign attributions (and are eligible event types) will create new sessions.
        -- non-attributed events that come in afterward will be batched into the same attribution-session
        sum(case when touch_id is not null

            and lower(type) in ('clicked email')

            then 1 else 0 end) over (
                partition by person_id, source_relation order by occurred_at asc rows between unbounded preceding and current row) as touch_session 

    from events

),

-- "session start" refers to the event in a "touch session" that is already attributed with a campaign or flow by Klaviyo
-- a new event that is attributed with a campaign/flow will trigger a new session, so there will only be one already-attributed event per each session 
-- events that are missing attributions will borrow data from the event that triggered the session, if they are in the lookback window (see `attribute` CTE)
last_touches as (

    select 
        *,
        -- when did the touch session begin?
        min(occurred_at) over(partition by person_id, source_relation, touch_session) as session_start_at,

        -- get the kind of metric/event that triggered the attribution session, in order to decide 
        -- to use the sms or email lookback value. 
        first_value(type) over(
            partition by person_id, source_relation, touch_session order by occurred_at asc rows between unbounded preceding and current row) as session_event_type

    from create_sessions
),

attribute as (

    select 
        *,
        -- klaviyo uses different lookback windows for email and sms events
        -- default email lookback = 5 days (120 hours) -> https://help.klaviyo.com/hc/en-us/articles/115005248128#conversion-tracking1
        -- default sms lookback: 1 day (24 hours -> https://help.klaviyo.com/hc/en-us/articles/115005248128#sms-conversion-tracking7

        coalesce(touch_id, -- use pre-attributed flow/campaign if provided
            case 
            when datediff(
        hour,
        session_start_at,
        occurred_at
        ) <= (
                case 
                when lower(session_event_type) like '%sms%' then 0
                else 72 end
            ) -- if the events fall within the lookback window, attribute
            then first_value(touch_id) over (
                partition by person_id, source_relation, touch_session order by occurred_at asc rows between unbounded preceding and current row)
            else null end) as last_touch_id -- session qualified for attribution -> we will call this "last touch"

    from last_touches 
),

final as (

    select
        *,

        -- get whether the event is attributed to a flow or campaign
        coalesce(touch_type, first_value(touch_type) over(
            partition by person_id, source_relation, touch_session order by occurred_at asc rows between unbounded preceding and current row)) 

            as session_touch_type -- if the session events qualified for attribution, extract the type of touch they are attributed to

    from attribute 
)

select * from final
        );