#23 - The WHERE clause just got a lot smarter (and SELECT too) 🚀

Hi Folks,

A month has passed, and some interesting updates have come to light that are worth sharing. This email is a newsletter again, since the WHERE clause is probably known well enough and doesn't need a dedicated article. (For the time being, at least, until we build a section for SQL beginners.)

This time we'll look at the new "reusable column aliases" feature in detail, which lets us reference calculated columns by their name in the same query, without having to use a CTE (or a WITH expression). We can reference these columns in the WHERE clause (instead of repeating the whole calculation there), or even further down in the SELECT itself. This is a big, very welcome change that lets us write cleaner code. The WHERE expression recently got another use as well: now we can use it intuitively within aggregation functions.

But first, some time-sensitive updates.

Event coming up: Stape Relay (10th November)

Another global, online event is on the horizon, featuring many well-known and respected experts in our industry. I signed up a while ago, and I'm looking forward to listening to Simo Ahava, Julius Fedorovicius, Juliana Jackson, Matteo Zambon, Karolina Wrzask — just to throw in a few names. Since it's organized by Stape, most talks will revolve around server-side tagging, but they will cover various different areas such as:

• Predicting revenue and pushing it back to Google Ads
• Leveraging data enrichment
• Learnings from a use case with extremely high event volumes
• Power-ups to enhance advertising
• Server-side tagging on Shopify

Of course there are many more interesting talks lined up, and I'm already torn between some of them as there will be two tracks to choose from, and some talks I want to watch are in the same timeslot. You can secure your spot here: https://relay.stape.io/

GA4BigQuery update

On the GA4BigQuery platform, five premium articles are in the works:

1. Creating a comprehensive Search Term report for Google Ads (in a way that includes data from PMax campaigns as well)
2. A Merchant Center data pipeline to segment products based on profit margin, allowing to optimize Google Ads e-commerce campaigns for profit (an alternative to PoAS bidding)
3. An introduction to basic procedural language: creating a Campaign Manager / Display & Video 360 report from the native export
4. Busting Direct and Unassigned traffic: an investigative case study you can follow for your own channel attribution hurdles (a sort-of beginner level one, but still useful)
5. Finding out which organic keywords are driving conversions on your site (GA4 + Search Console data)

Let me know which one you'd be interested in the most! E.g. by replying to this email (even the number itself i.e. a single-character response is enough, but values outside the 1-5 range will not be evaluated 😉). Your vote will count regardless of whether you're a premium or free subscriber. I'll get to all of these eventually, but I'd love to prioritize between them based on actual interest.

Reusable column aliases

OK, let's cut to the chase already. If you've written a decent amount of SQL code, you most probably came across situations where you created a calculated column (based on one or more source columns) and you needed to use it later. Previously, you had these options:

• repeat the entire calculation (in subsequent SELECT statements or in the WHERE clause) — making the column definition (or the filter) difficult to follow beyond a certain level of complexity
• use a CTE (or perhaps multiple CTEs), making the code verbose and a little more difficult to test
• since this spring, you could also use the WITH expression, which was a step forward, but still "wasn't the real deal".

To see this in action, let's pick an example from one of the articles, where we had to reuse columns sequentially to get to a final "useful" column that shows a "robust" ratio of events between Android and iOS platforms. It's an app tracking health check use case, whereby one of the telltale signs of inadequate tracking is an imbalance of in-app events between the two platforms.

The goal here is to identify events where the event volume ratio between iOS and Android is suspiciously different from the user base ratio. For that, we first calculate said event volume ratio: but we need to make a few corrections to make sure it is calculated in a "robust" way and only where it makes sense. I won't go into a deeper the explanation of the query (I won't even paste the whole thing) — it's explained in the article anyway — let's just focus on the before/after view of the main logic. This is what the "body" of the iOS / Android comparison query looks like:

SELECT     COALESCE(android_event_name,ios_event_name) AS event_name
          ,CASE WHEN exists_in_android AND exists_in_android THEN 'both'
                WHEN exists_in_android THEN 'android_only'
                WHEN exists_in_ios THEN 'ios_only' 
                ELSE 'no_event' 
                END AS platform_status
          ,a.*
          ,b.*

          ,CASE WHEN exists_in_android AND exists_in_ios THEN true ELSE false END AS exists_in_both
          ,CASE WHEN GREATEST(max_android_event_date,max_ios_event_date) = @latest_date THEN true ELSE false END AS live_event
          ,CASE WHEN GREATEST(android_event_count,ios_event_count) > 1000 THEN true ELSE false END AS event_with_significant_volume
          ,CASE WHEN COALESCE(android_event_name,ios_event_name) NOT IN ('app_clear_data','app_exception','app_remove','app_update','dynamic_link_app_open','firebase_campaign','first_open',
        'os_update','screen_view','session_start','user_engagement') THEN true ELSE false END AS non_automatic_event
          ,DATE_DIFF(max_android_event_date,min_android_event_date,DAY) AS android_time_range
          ,DATE_DIFF(max_ios_event_date,min_ios_event_date,DAY) AS ios_time_range
          ,ROUND(ios_event_count / android_event_count,2) AS volume_ratio

--calculating our main metric
          ,WITH( android_time_range AS DATE_DIFF(max_android_event_date,min_android_event_date,DAY)
                ,ios_time_range AS DATE_DIFF(max_ios_event_date,min_ios_event_date,DAY)
                ,time_adjusted_ios_event_count AS (android_time_range / ios_time_range * ios_event_count) 
                ,time_adjusted_volume_ratio AS ROUND(time_adjusted_ios_event_count / android_event_count,2) 
                ,exists_in_both AS (CASE WHEN exists_in_android AND exists_in_ios THEN true ELSE false END)
                ,live_event AS (CASE WHEN GREATEST(max_android_event_date,max_ios_event_date) = @latest_date THEN true ELSE false END) 
                ,event_with_significant_volume AS (CASE WHEN GREATEST(android_event_count,ios_event_count) > 1000 THEN true ELSE false END) 
                ,non_automatic_event AS (CASE WHEN COALESCE(android_event_name,ios_event_name) NOT IN ('app_clear_data','app_exception','app_remove','app_update','dynamic_link_app_open','firebase_campaign','first_open','os_update','screen_view','session_start','user_engagement') THEN true ELSE false END) 
                ,CASE WHEN exists_in_both AND live_event AND event_with_significant_volume AND non_automatic_event THEN time_adjusted_volume_ratio ELSE NULL END
                ) AS time_adjusted_volume_ratio_final

FROM            android_base a
FULL OUTER JOIN ios_base b
  ON            a.android_event_name = b.ios_event_name
WHERE           (CASE WHEN COALESCE(android_event_name,ios_event_name) NOT IN ('app_clear_data','app_exception','app_remove','app_update','dynamic_link_app_open','firebase_campaign','first_open','os_update','screen_view','session_start','user_engagement') THEN true ELSE false END) = true
ORDER BY        1

(Didn't they teach me in kindergarten that we shouldn't use SELECT *??? Well in this case the *-s refer to columns from small CTEs so we're OK — the entire query is in the article. Also, this is an investigative query and not a production one on a schedule.)

Anyway.

You probably got tired just by trying to unpack what's happening in the above code (if you attempted it). I've already tried to streamline the code by using the WITH expression, but it has its limitations: although it became possible to reuse columns, but only within the WITH expression, and it only produces one output — the final calculated column — while the intermediate columns get thrown away. Also, the WHERE clause is ridiculously complex for what it tries to achieve: we just want to compare volumes for manually tracked (non-automatic) events. (This filter isn't there in the original article, just added it here to prove the point.)

With the new update, it becomes a whole lot simpler:

SELECT     COALESCE(android_event_name,ios_event_name) AS event_name
          ,CASE WHEN exists_in_android AND exists_in_ios THEN 'both'
                WHEN exists_in_android THEN 'android_only'
                WHEN exists_in_ios THEN 'ios_only' 
                ELSE 'no_event' 
                END AS platform_status
          ,a.*
          ,b.*

          ,CASE WHEN exists_in_android AND exists_in_ios THEN true ELSE false END AS exists_in_both
          ,CASE WHEN GREATEST(max_android_event_date,max_ios_event_date) = '2026-03-28' THEN true ELSE false END AS live_event
          ,CASE WHEN GREATEST(android_event_count,ios_event_count) > 1000 THEN true ELSE false END AS event_with_significant_volume
          ,CASE WHEN COALESCE(android_event_name,ios_event_name) NOT IN ('app_clear_data','app_exception','app_remove','app_update','dynamic_link_app_open','firebase_campaign','first_open',
        'os_update','screen_view','session_start','user_engagement') THEN true ELSE false END AS non_automatic_event
          ,DATE_DIFF(max_android_event_date,min_android_event_date,DAY) AS android_time_range
          ,DATE_DIFF(max_ios_event_date,min_ios_event_date,DAY) AS ios_time_range
          ,ROUND(ios_event_count / android_event_count,2) AS volume_ratio

--calculating our main metric
          ,android_time_range / ios_time_range * ios_event_count AS time_adjusted_ios_event_count 
          ,ROUND(time_adjusted_ios_event_count / android_event_count,2) AS time_adjusted_volume_ratio
          ,CASE WHEN exists_in_both AND live_event AND event_with_significant_volume AND non_automatic_event THEN time_adjusted_volume_ratio ELSE NULL END AS time_adjusted_volume_ratio_final

FROM            android_base a
FULL OUTER JOIN ios_base b
  ON            a.android_event_name = b.ios_event_name
WHERE           non_automatic_event
ORDER BY        1

Now that ugly WITH expression is gone, along with several redundant columns that needed to be repeated within the WITH block. (The reason I repeated them before was because I wanted to see them in the final output as well, not just use them as an interim calculation.)

We now just have a few sequential calculations in the same SELECT statement. The WHERE clause has become much more friendly too: we just refer the column we already calculated previously. For further information, see Google's own support page on reusable column aliases.

Switching on the reusable column alias feature

This feature is switched off by default. If we use it on a BigQuery project that doesn't have this enabled yet, we run into the well-known error of not recognizing the column:

In order to turn this on, we need to tweak some settings. This is easiest done via code:

ALTER ORGANIZATION
SET OPTIONS (
  `region-eu.reusable_column_aliases_mode` = "on"
);

This is on the organizatonal level, but it's also possible to tweak just the project settings:

ALTER PROJECT PROJECT_ID
SET OPTIONS (
  `region-eu.reusable_column_aliases_mode` = "on"
);

After the change, the same query passes the dry run and is good to go:

(The filter wasn't added to the query in the screenshot, but it works with that as well.)

I'm not usually a fan of shortcuts when it comes to writing SQL, but this is a really useful (and, I think, much awaited) feature. I don't see any downside to this, while it will makes it possible to write more concise, still equally (or even more) understandable code. Plus this feature has existed in other traditional database engines before (Teradata, Snowflake), so it is not breaking standards either, and it makes it migrations easier too. This was a good move, Google.

Another cool new way to use WHERE

There is a new way WHERE can be used: as an operator in the SELECT statement, in lieu of an IF or CASE WHEN logic. This will not make the code more concise, but it is a very intuitive way to "phrase" what we want to do.

Now let's see an example using the public open-source data, so that you can run this one yourself. Let's assume we want to calculate the total number of users, and the number of people who reached certain stages in the purchasing process (basically, a quick funnel, in a pivot table form). Plus the total revenue calculated from actual purchases only. Here is how we would do it historically:

SELECT   device.category AS device_category,
         COUNT(DISTINCT user_pseudo_id) AS total_users,
  
-- Funnel counts using conditional logic inside functions (IF)
         COUNT(DISTINCT IF(event_name = 'view_item', user_pseudo_id, NULL)) AS item_viewers,
         COUNT(DISTINCT IF(event_name = 'add_to_cart', user_pseudo_id, NULL)) AS cart_adders,
         COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS purchasers,

-- OR: the same using CASE WHEN
         COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN user_pseudo_id ELSE NULL END) AS item_viewers_2,
         COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_pseudo_id ELSE NULL END) AS cart_adders_2,
         COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id ELSE NULL END) AS purchasers_2,

-- Revenue aggregation with fallback handling
         SUM(IF(event_name = 'purchase', event_value_in_usd, 0)) AS total_revenue
FROM     `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY 1
ORDER BY total_revenue DESC;

In real life, or course, we wouldn't calculate it both with an IF and a CASE WHEN, but let's not get hung up on that. Now, this is where a WHERE can make it easier to read:

SELECT   device.category AS device_category,
         COUNT(DISTINCT user_pseudo_id) AS total_users,
  
-- Funnel counts using conditional logic inside functions (IF)
         COUNT(DISTINCT user_pseudo_id WHERE event_name = 'view_item') AS item_viewers,
         COUNT(DISTINCT user_pseudo_id WHERE event_name = 'add_to_cart') AS cart_adders,
         COUNT(DISTINCT user_pseudo_id WHERE event_name = 'purchase') AS purchasers,

-- Revenue aggregation with fallback handling
         SUM(IF(event_name = 'purchase', event_value_in_usd, 0)) AS total_revenue
FROM     `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY 1
ORDER BY total_revenue DESC;

Very self-explanatory, no? This will not "revolutionize" anything, but it's a nice addition, making code easier to follow.

Some more BigQuery updates

A few updates that have more chance of surfacing in the life of us data analysts:

• Conversational analytics can now be used by asking Gemini within Connected Google Sheets.
• A very fresh one: artificially geneated demo datasets in multiple verticals (e-commerce, lead gen etc), along with the data generator itself, made available very recently by the GA4Dataform (Superform Labs) team! You can subscribe to the datasets by the click of a button. We'll be switching to this dataset instead of the old ga4-ecommerce public dataset, as this one has all the fields and has newer patterns. (I didn't do this in the current article just yet.)

Finally... Happy Halloween folks 👻

Best regards

Balazs