Learn business growth with Google Analytics 4 Forums Google Analytics 4 Extract Daily Ad Revenue from GA4-linked BigQuery Using Query Language

  • Extract Daily Ad Revenue from GA4-linked BigQuery Using Query Language

    Posted by Presley on 27 January 2023 at 11:18 am

    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!

    Elijah replied 11 months, 1 week ago 3 Members · 2 Replies
  • 2 Replies
  • Olivia

    Member
    2 June 2023 at 1:38 am

    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.

  • Elijah

    Member
    29 June 2023 at 10:49 pm

    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.