-
Extract Daily Ad Revenue from GA4-linked BigQuery Using Query Language
Hey there! I’m trying to figure out a way for me to add up all the ad revenue for each day using a query within BigQuery. I’ve got GA4 and BigQuery all hooked up together doing their thing. I’ve got this huge set of events, but for the life of me, I’m not sure which one’s tracking the ad revenue, any ideas?
Also, I tried this LTV Revenue thing, but it just ended up returning NULL. Been scratching my head over this, hoping someone out there could enlighten me a bit!
-
2 Replies
-
You’ll need to look for the ‘ad_revenue’ event in GA4 for ad revenue details. However, GA4 doesn’t natively capture this data, so you might integrate it with a third-party tool. LTV revenue returning NULL might be due to the tool not capturing or processing this data right now.
-
In Google Analytics 4 (GA4), ad revenue can be associated with multiple event names depending on the attribution system you’re using. However, two most common events where you’ll likely see ad revenue are “ad_impression” and “ad_click”. If you wish to sum up the ad revenue for each day, you need to locate the ad_revenue event parameter for these events.
Here’s a simple BigQuery query that might help:
`
SELECT
event_date,
SUM(event_params.value.double_value) as total_ad_revenue
FROM
your-project.dataset.events,
UNNEST(event_params) AS event_params
WHERE
event_params.key = “ad_revenue”
GROUP BY
event_date
ORDER BY
event_date DESC
`Regarding the LTV (Lifetime Value) Revenue returning NULL, it might be due to the fact that LTV metrics are not pre-aggregated or calculated in the raw event data that’s exported to BigQuery from GA4. The calculation and population of user-level LTV metrics usually happen within the Google Analytics interface, which means it might not be directly available in the BigQuery dataset.
Log in to reply.