SCITYLANA: “Hello BigQuery”

Sign-up
and get raw hit-level Google Analytics data in BigQuery

The clouds are coming in…

Well, it means we can extract hit-level / not sampled data from your favorite FREE GA view and upload it automatically to your BigQuery project. Just like the big boys do with GA 360 but just less expensive.

Swoosh… !

It updates the data automatically behind the scenes every morning. And with NO HANDS – YAY!

Now take that fine reporting tool of yours with a BigQuery connector and put it to use. Use it to connect to your SCITYLANA transformed FREE GA hit-level / not sampled BigQuery database and do reporting on it.

Let it be Microsoft Power BI, Tableau, Google Data Studio or some other tool.

NOTE: We offer a 100% hosted solution. Ask support for help!

Let’s start the engine…

To get started – log-in to your www.scitylana.com account and authorize with a BigQuery account with modify permissions (Owner, Editor or Admin) in your data extraction settings. Click the Authorize BigQuery Access button:

BigQueryAuthorize

Now enter your Google Project ID, in my case it is scitylana-1048

BigQueryProjectID

Save it – and off you go. If you already have extracted data on the disk, these files will immediately be uploaded to BigQuery.

Do note that the app will continue to download the files from GA on to your hard-drive. From here the SCITYLANA app will upload the files to BigQuery.

While we wait for the files to be uploaded – let’s set-up the first report.

Connect to BigQuery from Power BI

The easiest way to do this is through our Power BI template for BigQuery. Simply download the template, open it (you must have Microsoft Power BI) and sign in to BigQuery when prompted.

You can also build your report from scratch in the following way.

  1. Open PBI Desktop and click Get Data

PowerBIGetData

2. Select Google BigQuery (Beta) connector

PBIBigQuery

3. Click Connect

4. Expand from your project id to your tables and views.

PBIBQDataset

5. Select second view from the top and click Load

6. Select DirectQuery

PBIDirectQuery

7. Click OK

8. Power BI now creates a model and should end up listing all the dimensions and metrics in the Fields list.

PBIBQFields

9. Right-click the table called “VIEW ya-da ya-da” and select New Measure

10. Write M_users = DISTINCTCOUNT(VIEW ya-da ya-da…[sl_userId]) where “VIEW ya-da ya-da” is the id of your own view. E.g. M_users = DISTINCTCOUNT(VIEW136604982DAYS007[sl_userId])

11. Check your new view M_users in the fields list. (Find it using the search)

12. And VIOLÁ – you get your first chart

PBIBQChart

13. Now search for the date dimension. And check it. Now we get…

PBIBQChartTime

14. I hope you can take the rest from here. 🙂

Nerdy details for the interested

OK, what have we done with your BigQuery account?

Well in the BigQuery Cloud console you can see we have added the following:

BigQueryOnline

(The screenshot displays the output for Google Analytics View 136604982 –  scitylana.com)

For each view you’ll get a date partitioned table (the one with the blue icon) named after your Google Analytics view idThe reason why we partition the data by date is because Google recommends it for getting better query performance. Currently this is the only way BigQuery can partition a table.

Table Schema

Here is a snippet of the schema definition.

BigQuerySchema

The sl_ dimensions (e.g. sl_userId) are some of the extra stuff SCITYLANA adds to the dataset. _PARTITIONTIME is BigQuery internal column used for date partitioning. Read more about it here.

All Google Analytics metrics a re-named from ga: to M_ e.g. M_bounces is the re-named version of ga:bounces. All dimensions are re-named from ga: to E.g. ga:userType is called userType.

BigQuery Views to help

Additionally we created 7 BigQuery views. BigQuery Views are a subset of data, a stored SQL query if you like. We made these helper views for your convenience.

In the data view you get integration between the SCITYLANA hit-level table and BigQuery’s public date table called bigquery-public-data.common_us.date_greg.

This view comes in 6 variations. Latest 7 days and latest 14, 30, 90, 180, 365 days. This is to get better query performance when using them with a BigQuery DirectQuery connector in e.g. Microsoft Power BI. The views have the following format, VIEWXXXXXXXXXDAYSYYY, where XXXXXXXXX is the view id and YYY is the number of days it queries. E.g. VIEW120558169DAYS030 which is returning the last 30 days of hit-level data for GA view with id 120558169.

Another view lets you see the data partitions behind the partitioned table for your convenience. E.g. LIST120558169PARTITIONS

This view is not super important, but it’s practical when you need to get an idea of how many days have been uploaded to the partitioned table.

Please feel free to comment here or write us, support@scitylana.com

Sign-up
and get raw hit-level Google Analytics data in BigQuery

3 thoughts on “SCITYLANA: “Hello BigQuery””

  1. Pingback: Visualize your raw GA data instantly with this Power BI template – SCITYLANA

  2. Pingback: How to use custom segments in Google Analytics with Scitylana? – SCITYLANA

  3. Pingback: Funnel Based Attribution in a single SQL statement in BigQuery - Google Analytics foundation

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.