Counting unique google analytics events in BigQuery

I have managed to calculate total events by ISOweek but not unique events for a given Google Analytics Event using BigQuery. When checking GA, total_events matches the GA interface on the dot but unique_events are off. Do you know how I can solve thi

Calculate the path of the previous page in BigQuery

I am pulling data for a homepage heat map and I would like to only see pageviews where the previous page was the homepage. My data is imported Google Analytics tables - the metric 'previous page path' does not exist in BQ, so how would I go about wor

How to keep a Firebase database synchronization with BigQuery?

We are working on a project where a lot of data is involved. Now we recently read about Google BigQuery. But how can we export the data to this platform? We have seen the sample of importing logs into Google BigQuery. But this does not contain inform

Google Standard SQL UDF - writing on BigQuery

My question is regarding writing to BigQuery from BigQuery Standard SQL UDF. I have a requirement that I have to do some calculations from within Standard SQL. And after those calculation I have to save those calculations data to BigQuery. What is th

Retrieve the first and last records of each group

Distance travelled between points: I have a set of gps points travelled by each vehicle. I am trying to retrieve the first and last records for each trip. Data: VehicleId TripId Latitude Longitude 121 131 33.645 -84.424 121 131 33.452 -84.409 121 131

Get non-zero value from other fields in aggregations

I want to aggregate on some fields and get any not null value on others. To be more precise the query looks something like: SELECT id, any_value(field1), any_value(field2) FROM mytable GROUP BY ID and the columns are like: ID | field1 | field 2 -----

Identify similar fields in a database (but not duplicates)

I'm dealing with a querying issue I'm struggling to work around. I have a database of names. What I'm looking to do is figure out those who have multiple names in the database tied to the same ID, where those names are very similar to each other: ID

Aggregate query between two tables in SQL?

I'm working in BigQuery. I've got two tables: TABLE: orgs code: STRING group: STRING TABLE: org_employees code: STRING employee_count: INTEGER The code in each table is effectively a foreign key. I want to get all unique groups, with a count of the o

Ignore unknown values ​​on the addition

I am running into a bit of an issue when appending a JSON blob to an existing table in BigQuery; specifically, the existence of new, but not necessary, leaf elements in the JSON being appended is preventing me from appending the data. Using the UI, I

BigQuery - extract text and subtext

I have a table in BigQuery about number of contracts sent out on day to day basis: date contract 2014-05-04 {jeans = 5, caps = 12, CDs = 1, Microwaves = 7, other = 6} 2014-05-05 {cups = 7, other = 5} I need to filer out how many not classified contra

BigQuery: TABLE_QUERY but columns differ between tables

We are loading csv files into BigQuery. Each file will create a separate table. When we select from these tables, we do this mostly with a table query liek so: SELECT foo, bar FROM TABLE_QUERY(name_stub,'table_id CONTAINS "_something" and msec_t

Request the last table in the BigQuery dataset

I have a dataset containing tables with similar table names ending in yyyymmdd. For example: myproject:mydataset.Sales20140815 myproject:mydataset.Sales20140816 myproject:mydataset.Sales20140817 myproject:mydataset.Sales20140818 ... myproject:mydatas

Returns a single result for each data partition

I'd like to be able to do some calculations by partition in BigQuery and then only output 1 row for each partition (rather than a row for every partition). E.g., if I had something like this table: Category | Location | Count A | 'home' | 20 A | 'wor

How to launch float to a chain without an exponent in BigQuery

I have float data in a BigQuery table like 5302014.2 and 5102014.4. I'd like to run a BigQuery SQL that returns the values in String format, but the following SQL yields this result: select a, string(a) from my_table 5302014.2 "5.30201e+06" 5102

How to set the deadline for BigQuery on Google App Engine

I have a Google App Engine program that calls BigQuery for data. The query usually takes 3 - 4.5 seconds and is fine but sometimes takes over five seconds and throws this error: DeadlineExceededError: The API call urlfetch.Fetch() took too long to re

BigQuery Pagination

How to do pagination using BigQuery ,when using javascript First I send request: var request ={ 'projectId': project_id, 'timeoutMs': '30000', 'query': query, 'maxResults' : 50, 'pageToken': pageToken }); This query w

weighted ranking / combined score in Google Big Query

...Spent several hours trying what not and researching this forum. Quite pessimistic at this point about the usefulness of Google Big Query (GBQ) for anything more than trivial queries, but here is one last desperate try, maybe someone has better ide

What are the OLAP features in BigQuery?

In the BigQuery documentation it is mentioned that it uses OLAP functions. But the developer documentation does not mention MDX query language, dimension or fact tables. What OLAP features, if any, are in BQ and how are theyBigQuery is not a traditio