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

  • Modifying GA4 Big Query SQL records using UPDATE statement and UNNEST

    Posted by Owen on 10 May 2022 at 3:44 pm

    Hey, can you help me out? I’m trying to update records in my database, namely those that have an event_name as ‘page_view’ and a key of ‘page_location’. I want to target only those where ‘page_location’ contains a specific pattern. I’ve managed to write a query that makes the correct selection, but I’m stuck on how to actually incorporate an UPDATE statement to alter the ‘page_location’ values. Here’s the query I’ve got so far. Any suggestions?

    Ethan replied 10 months, 3 weeks ago 3 Members · 2 Replies
  • 2 Replies
  • Sophia

    Member
    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:

    `sql
    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:

    `sql
    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 FORMAT_DATE(‘%Y%m%d’,DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    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!

  • Ethan

    Member
    13 April 2023 at 10:53 pm

    Sure, but it would be useful to view your current query to give a more precise direction. Generally speaking, in SQL, if you want to implement an UPDATE operation based on a selection, it would look something like this:

    `sql
    UPDATE your_table
    SET page_location = ‘new value’
    WHERE event_name = ‘page_view’ AND page_location LIKE ‘%specific pattern%’
    `

    Please replace ‘your_table’ with the name of your table, ‘new value’ with the new page_location value you want to set, and ‘%specific pattern%’ with the specific pattern you mentioned. This would set the ‘page_location’ to ‘new value’ for all records where ‘event_name’ is ‘page_view’ and ‘page_location’ contains the specific pattern.

Log in to reply.