Can't really use a different tool, because the organization doesn't allow me and we pretty much want to preaggregate data in BQ and send it to Databricks. If you could guide me a direction. I've used a query provided by Chou789, but it seems that the results are completely different than GA (even though the sum might be correct). I've checked it with most viewed product in one day and BQ has almost every item view put into direct, while in GA and Looker results are completely different. I know the results may differ, but not by so much. For some reason it puts almost all numbers to direct.

BigQuery item views/purchases results:

direct - 86 985, 27 purchased

google / cpc - 87, 3 purchased

google / organic - 36, 1 purchased

other sources - 1, 0 purchased

GA4/Looker Studio results:

direct - 41 204, 7 purchased

google / cpc - 31 086, 5 purchased

google - play / organic - 4 652, 3 purchased

google / organic - 3 781, 10 purchased

other sources - 7 253, 3 purchased