How to lookup and SUM URLs with different paths but same ID in Big Query?

user_2023739 0
Viewed 73

I need to lookup URLs with the same 'productid=' in Big Query but they may have a different path based on a redesign and new URL format. I'm looking to SUM the clicks for the specific ID if possible.

Example:

  • /path-a/productid=12345 (clicks 10)
  • /path-b/productid=12345 (clicks 15)
  • /path-c/productid=12345 (clicks 2)

productid=12345 = 27 clicks

I need to match the "productid=" number but unsure of the regex. There's around 9,000 DISTINCT IDs and the integers can range from one digits to six digits.

SELECT path, sum(clicks) as clicks, sum(impressions) as impressions
FROM `ecommerce_table` 
WHERE DATE(_PARTITIONTIME) >= "2021-04-01" 
   AND path LIKE '/%/productid=%'
GROUP BY path
ORDER BY clicks DESC
google-bigquery sql
2 Answers

Try r'=(\d+)':

select regexp_extract('/path-a/productid=12345' , r'=(\d+)')

Example of usage:

SELECT regexp_extract(path, r'=(\d+)'), sum(clicks) as clicks, sum(impressions) as impressions
FROM `ecommerce_table` 
WHERE DATE(_PARTITIONTIME) >= "2021-04-01" 
   AND path LIKE '/%/productid=%'
GROUP BY 1
ORDER BY clicks DESC
user_134735250

I'd give it the following approach (SQL Fiddle for reference):

WITH final AS (
  SELECT
    id,
    SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', -1), '/', 1), 11) AS product_id,
    url
  FROM your_data
)
SELECT 
    product_id,
    COUNT(DISTINCT id) AS clicks
FROM final
GROUP BY 1

Understanding the id column as click events, for example, you get to access the URL using SUBSTRING_INDEX() and establishing that you are interested in the data between slashes. Then, you apply the SUBSTR() function to grab the numbers after product_id.

OUTPUT:

product_id  clicks
----------  ------
12345       5
12346       2
12347       1
user_115329190
Related