gistfile1.txt

#standardSQL

SELECT 
       id.user as user_id_events,
       visitor_pk,
       time,
       referer.referer as referer,
       user_agent.device_category as device_category,
       native_app.name as native_app_name,
       native_app.version as native_app_version,
       is_robot,
       kv1.value as search_request_pk,
       service_pk,
       -- if available
       quote_pk,
       -- if available
       twoplus_customer_interactions_with_pro_response_to_first_contact_3d as one_plus_conversation 
FROM events.enriched e, UNNEST(kv_pairs) as kv1
INNER JOIN instant_results.search_results s ON CAST(kv1.value AS INT64) = search_request_pk
LEFT OUTER JOIN a.contacts c ON c.bid_pk = quote_pk
WHERE 
e._PARTITIONTIME >= "2018-03-01" AND e._PARTITIONTIME <= "2018-03-31"
AND s._PARTITIONTIME >= "2018-03-01" AND s._PARTITIONTIME <= "2018-03-31"
-- tracks the services that were viewed by the visitor in that search request
AND event_type='waypoint event/lp instant results service' 
AND kv1.key = "search_request_pk"
AND is_robot IS FALSE