Standard SQL query in Google BigQuery on an advanced eCommerce data set

I need a query to extract the previous 10 values of a custom session level dimension before a purchase event and the value at time of purchase. This is from a Google 360 dataset running advanced eCommerce. Purpose is to track the accuracy of custom Audience modelling. From the results I need to: Validate product purchased matches Audience value Calculate / report on number of visits and time elapsed before a purchase is made Report on how the Audience values change over time before purchase Multiple products need to stringified or piped into a single field. Output will be extracted to Tableau so all results need to be unnested. Query needs to be in Standard Sql Please note that I cannot give access to the bigQuery instance. I expect that I will need these values: visitId, fullVisitorId, ClientId, UserId, session_id, [login to view URL], visitNumber, date, (yyyymmdd) visitStartTime, ("%Y-%m-%d %H:%M:%S", 'Australia/Sydney' ) transactionId, [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], (Note a single transactionId may have several different products) [login to view URL], where index=144 AS dimension144, visitNumber where transactionId is NOT NULL as t_visitNumber dimension144 where transactionId as t_dimension144 dimension144 where visitNumber is = t_visitNumber - 1 as minus1 dimension144 where visitNumber is = t_visitNumber - 2 as minus2 dimension144 where visitNumber is = t_visitNumber - 3 as minus3 etc to 10 first occurrence (or minus10) dimension144 value as first_ dimension144 time between visits in days and hours for visits t to minus10 calculated against transaction time time between first_ dimension144 to t_dimension144 TIMEFRAME: 1-2 HOURS