'Split credit for transactions and Revenue between clicks on events (UA GA)

In this task, the idea is to assign the sales credit (transactions and revenues) equally to the events that were clicked on during the user's session. The output table would look like this, except that the revenue and transaction are split if the user had two events, three events, etc.

Below are three scenarios -> "three scenarios" on how transactions and revenues should be shared between events. Does anyone have an idea how to customize the code?

I include a code that assigns sales, but without dividing the credit into Revenue and Transactions, and this code would need to be modified.

three scenarios

output table

Grateful in advance for any help

with event_home_page as (select q.* except(isEntrance), if (isEntrance = true, 'true', 'false') isEntrance
from (
select 
    PARSE_DATE('%Y%m%d', CAST(date AS STRING)) as true_date,
    hits.isEntrance,
    hits.eventInfo.eventCategory,
    hits.eventInfo.eventAction,
    hits.eventInfo.eventLabel,
    concat(fullvisitorid, cast(visitstarttime as string)) ID,
    count(*) click

FROM `ga360.123456.ga_sessions_*`, unnest (hits) as hits
WHERE
    _table_suffix = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    and hits.page.pagePath in ('www.example.com/')
    and regexp_contains(hits.eventInfo.eventCategory, '^clickable_element.*')
    group by 1,2,3,4,5,6) q
),

transactions as (
select 
    PARSE_DATE('%Y%m%d', CAST(date AS STRING)) as true_date,
    concat(fullvisitorid, cast(visitstarttime as string)) ID,
    sum(totals.totalTransactionRevenue/1000000) as all_revenue,
    sum(totals.transactions) all_transactions 
FROM `ga360.123456.ga_sessions_*`
 WHERE
    _table_suffix = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
group by 1,2
)

select hp.true_date, hp.isEntrance, hp.eventCategory, hp.eventAction, hp.eventLabel, hp.click, t.all_revenue revenue, t.all_transactions transactions
from event_home_page hp left join transactions t on hp.true_date=t.true_date and hp.id=t.id
order by revenue desc


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source