title: Trouble building an effective BigQuery CASE statement
body: <p>I am having difficultly building an effective BigQuery case statement for a fairly complex reporting query. The data set that I am using has been populated by Google Analytics. For this query, there are many cases, but this is one of the cases that I am looking at:</p>
  
  <pre><code>CASE when previous page="^/XXX/[^/]+/apps/booking/flight/traveler\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Traveler info 2.0" 
  when previous page="^/web/[^/]+/apps/booking/flight/traveler\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Traveler info 1.0"  when previous 
  </code></pre>
  
  <p>Note here that the XXX and the web are different cases. The wrinkle in this is that the one needs to determine whether the page path is a landing page or not, in order to deal with the cases. I currently have a separate question out in that area. For this specific issues, I have looked at many resources, including the following resource on SO, <a href="https://stackoverflow.com/questions/11647201/google-bigquery-case-function">which introduces the BigQuery case function.</a> To support the question, here is the full case statement that will be constructed.</p>
  
  <pre><code>Case when page = "^/XXX/[^/]+/default\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Homepage 2.0" 
  when page ="^/web/[^/]+/default\.aspx" and landing page = "^/web/[^/]+/default\.aspx" then "Homepage 1.0" 
  when page="^XXX/[^/]+/apps/booking?flight/(searchresult1|search(rt|ow|md))\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Rev inbound search 2.0" 
  when page="^web/[^/]+/apps/booking?flight/(searchresult1|search(rt|ow|md))\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Rev inbound search 1.0" 
  when page="^/XXX/[^/]+/apps/booking/flight/searchResult2\.aspx" and previous page ="^XXX/[^/]+/apps/booking?flight/(searchresult1|search(rt|ow|md))\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Rev outbound search 2.0" 
  when page="^/web/[^/]+/apps/booking/flight/searchResult2\.aspx" and previous page="^web/[^/]+/apps/booking?flight/(searchresult1|search(rt|ow|md))\.aspx" landing page="^/web/[^/]+/default\.aspx" then "Rev outbound search 1.0" 
  when page="^/XXX/[^/]+/apps/booking/flight/reviewRevenue\.aspx" and previous page="^/XXX/[^/]+/apps/booking/flight/searchResult2\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Rev review itinerary 2.0" 
  when page="^/web/[^/]+/apps/booking/flight/reviewRevenue\.aspx" and previous page="^/web/[^/]+/apps/booking/flight/searchResult2\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Rev review itinerary 1.0"  
  when page="^/XXX/[^/]+/apps/booking/flight/traveler\.aspx" and previous page="^/XXX/[^/]+/apps/booking/flight/reviewRevenue\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Rev traveler info 2.0" 
  when page="^/web/[^/]+/apps/booking/flight/traveler\.aspx" and previous page="^/web/[^/]+/apps/booking/flight/reviewRevenue\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Rev traveler info 1.0"  
  when page="^/XXX/[^/]+/apps/booking/flight/seatSelector\.aspx" and previous page="^/XXX/[^/]+/apps/booking/flight/traveler\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Rev seat selector 2.0" 
  when page="^/web/[^/]+/apps/booking/flight/seatSelector\.aspx" and previous page="^/web/[^/]+/apps/booking/flight/traveler\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Rev seat selector 1.0" 
  when page="^/XXX/[^/]+/apps/booking/flight/billingRevenue\.aspx" and previous page="^/XXX/[^/]+/apps/booking/flight/seatSelector\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Rev payment info 2.0" 
  </code></pre>
  
  <p>My first instinct here was to create a new column (potentially), that looks at the path the user took to determine which case the unique visit should be part of. This may not be the best direction and is something that I was exploring. The other, is to use the case function directly. Any initial support or insight one can shed on the best way to approach this part of the query would be exceptional.</p>