Learn business growth with Google Analytics 4 Forums Google Analytics 4 Modifying GA4 Big Query SQL records using UPDATE statement and UNNEST Reply To: Modifying GA4 Big Query SQL records using UPDATE statement and UNNEST

  • Sophia

    8 March 2023 at 9:15 pm

    Sure, I can help you with that. Here’s a good way to approach it by using an UPDATE-SET-FROM statement, which is pretty handy but isn’t explicitly listed in the Google documentation.

    The basic pattern you’d use goes a little something like this:

    update ds.targettable t
    set t.targetfield = s.sourcefield
    from (select keyfield, sourcefield
    from ds.sourcetable
    ) s
    where t.keyfield = s.keyfield
    Now, like I said it’s super useful to be able to pull information from one table to update another table like this. For your specific situation, you’d probably need to tweak it a bit. I don’t have direct access to your GA events table, so I can only offer you a rough estimation, but hopefully this helps:

    update project-name.analytics_299XXXXXX.events_* tgt
    set page_location = src.page
    from (SELECT event_name, key, _table_suffix (SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = ‘page_view’ AND key = ‘page_location’) AS page
    FROM project-name.analytics_299XXXXXX.events_*
    WHERE _table_suffix BETWEEN ‘20220322’
    AND event_name = ‘page_view’
    and page LIKE ‘%login%’) src
    where farm_fingerprint(concat(tgt.key, tgt.event_name) = farm_fingerprint(concat(src.key, src.event_name)
    and src._table_suffix = tgt._table_suffix
    and tgt.page LIKE ‘%login%’
    Table suffix comparison might be a bit tricky though, you’ll need to experiment with that to get it right.

    But above all, let me know if you have any questions or need any clarification, I’d be happy to assist. Good luck!