Google Tag Manager

Forum Discussion

inespais's avatar
inespais
Mentor
2 months ago

Help with Query around custom event properties

Hey all!
Trying to run a query to output volume of a specific custom event (with a specific property) performed by users who entered a specific canvas. I'm using the USERS_BEHAVIORS_CUSTOMEVENT_SHARED table, which includes event "name" and "properties" (according to the documentation --- Custom properties of the event stored as a JSON encoded string).

I managed to output the total event occurrences but got stuck when it came to filtering by event property and pretty sure it's a syntax issue...

Query - Total Voucher Redemptions:
This looks at total "Voucher Redeem" events from users who entered the specified canvas.

WITH canvas_users AS (
  SELECT DISTINCT user_id
  FROM USERS_CANVAS_ENTRY_SHARED
  WHERE canvas_api_id = 'XXXXXXXXXXXXXXXXXXXXXXXX'
),
voucher_redeems AS (
  SELECT DISTINCT user_id
  FROM USERS_BEHAVIORS_CUSTOMEVENT_SHARED
  WHERE name = 'Voucher Redeem'
)
SELECT COUNT(DISTINCT cr.user_id) AS users_redeemed_voucher
FROM canvas_users cr
JOIN voucher_redeems vr ON cr.user_id = vr.user_id;

Query - Filter by specific property:
I need to basically filter above to only include event occurrences where the "Voucher Redeem" property "campaign_name" matches a specific string. Just can't figure out how to do it.
Chat GPT advised me the following, but I get zero results.

WITH canvas_users AS (
  SELECT DISTINCT user_id
  FROM USERS_CANVAS_ENTRY_SHARED
  WHERE canvas_api_id = 'XXXXXXXXXXXXXXXXXXXXXXXX'
),
voucher_redeems AS (
  SELECT DISTINCT user_id
  FROM USERS_BEHAVIORS_CUSTOMEVENT_SHARED
  WHERE name = 'Voucher Redeem'
    AND REGEXP_LIKE(
          PARSE_JSON(properties):campaign_name::STRING,
          '[XXXX]'
        )
)
SELECT COUNT(DISTINCT cr.user_id) AS users_redeemed_voucher
FROM canvas_users cr
JOIN voucher_redeems vr ON cr.user_id = vr.user_id;

Has anyone successfully queried event properties and can shed some light on the solution here?
Much appreciated!

3 Replies

  • Hello inespais​ I don't see issue with the query. I would try to run the main queries independently to see if there is an actual data returned by them.

     

  • Hello inespais could you please try this : 

    voucher_redeems AS (
    SELECT DISTINCT user_id
    FROM USERS_BEHAVIORS_CUSTOMEVENT_SHARED
    WHERE name = 'Voucher Redeem'
    AND PARSE_JSON(properties):campaign_name::STRING = 'XYZ_CAMPAIGN'
    )


    This directly extracts the campaign_name property as a string.

    Hope this works :)

  • WHERE NAME = 'Voucher Redeem' AND TRY_PARSE_JSON(PROPERTIES):campaign_name = xyz

    This might work also! Please keep us posted!