Bigquery GA4 revenue not matching session source / medium

[jmbbosaka]

Hi,

Does anyone know how to get revenue displaying with non-null source, medium channels?

Thank you in advance!

Simple SQL syntax below:

SELECT
    ((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source')) AS source,
    ((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium')) AS medium,
    SUM(ecommerce.purchase_revenue) AS revenue,
    SUM(ecommerce.purchase_revenue_in_usd) AS revenue_usd
  FROM
    `dataset`
  GROUP BY
    source,
    medium

Result returns:

Rowsourcemediumrevenuerevenue_usd

1	null	null	10000	100
2	google	cpc	null	null
3	adroll	Email	null	null
4	Instagram	display	null	null
5	google	organic	null	null
6	m.facebook.com	referral	null	null
7	ads.as.criteo.com	referral	null	null

Am trying to get something like this instead:

Rowsourcemediumrevenuerevenue_usd

1	null	null	100	1
2	google	cpc	200	2
3	adroll	Email	400	4
4	Instagram	display	500	5
5	google	organic	6000	60
6	m.facebook.com	referral	700	7
7	ads.as.criteo.com	referral	15000	150

[Poala_Tenorio]

To achieve the desired result where revenue and revenue_usd are displayed only when both source and medium are non-null, you can use a WHERE clause to filter out rows where either source or medium is null. You can add this to your SQL query:

Show More

WHERE
source IS NOT NULL AND medium IS NOT NULL