Bigquery GA4 revenue not matching session source / medium

Google Cloud Data Analytics
1

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:

Rowsourcemediumrevenue****revenue_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:

Rowsourcemediumrevenue****revenue_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
1 Like
2

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

Show More

source IS NOT NULL AND medium IS NOT NULL

1 Like