'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.
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 |
|---|
