BigQuery - How to select the first campaign each user clicked on?

user_15594687 0
Viewed 51

Let's imagine that I have a website that sells electronics. In order to attract customers, I invested some money in online ad campaigns.

The following table has three columns: user_id (id that identifies each user), event_date (the date when the user clicked on the ad) and mkt campaign (the name of the ad campaign):

|user_id| event_date|mkt campaign
|   1   | 25-11-2021|FB - Black Friday
|   2   | 20-12-2020|G Display - Christmas campaign
|   1   | 22-12-2019|G Display - Christmas campaign 
|   3   | 12-10-2020|Whatsapp - July - campaign
|   2   | 01-08-2018|IG Post - Holidays campaign
|   3   | 30-08-2020|FB - Holidays campaign

I want to select only the rows that contain the first campaign that attracted each user to my website. In other words, I need to identify how each user got to my website for the first time.

The expected result would be like this:

 |user_id| event_date|mkt campaign
 |   1   | 22-12-2019|G Display - Christmas campaign
 |   2   | 01-08-2018|IG Post - Holidays campaign
 |   3   | 30-08-2020|FB - Holidays campaign

I know that I could use "ORDER BY event_date ASC" to order the rows by date. However, I need the result to show only one result per user_id (the one with the oldest campaign).

Please, do you know how to do this in BigQuery (BQ Standard SQL)?

Thank you very much.

google-bigquery sql
1 Answers

Consider below approach

select * from your_table where true
qualify 1 = row_number() over(partition by user_id order by parse_date('%d-%m-%Y', event_date))

if apply to sample data in your question as

with your_table as (
  select 1 user_id, '25-11-2021' event_date, 'FB - Black Friday' mkt_campaign union all
  select 2, '20-12-2020', 'G Display - Christmas campaign' union all
  select 1, '22-12-2019', 'G Display - Christmas campaign ' union all
  select 3, '12-10-2020', 'Whatsapp - July - campaign' union all
  select 2, '01-08-2018', 'IG Post - Holidays campaign' union all
  select 3, '30-08-2020', 'FB - Holidays campaign' 
)

output is

enter image description here

user_52219440
Related