Anja's Google Analytics

W3Schools has a good page that talks about ways to create tables. Since I already went through how to make a new dataset (with a schema) in “Setting up Dataset” drop down section, here I wanted to go through creating a new table (from an existing one) and changing some data types.

To create a new table from an existing one, use “AS” and “SELECT” what you want “FROM” an existing table.

In BigQuery you can use the SAFE prefix. According to the documentation: if you begin a function with the SAFE. prefix, it will return NULL instead of an error.

Here is the sample code from my school board achievement and progress table:
CREATE TABLE
school_board.achievement_progress AS
SELECT
p.Board_Number AS board_number,
p.Board_Name AS board_name,
SAFE_CAST(p.Grade_6_EQAO_Reading_Results AS FLOAT64)*100 AS grade_6_EQAO_reading_results_percent,
SAFE_CAST(p.Progress_in_Grade_6_EQAO_Reading_Results AS FLOAT64)*100 AS progress_in_grade_6_EQAO_reading_results_percent,
p.Extract_Date AS extract_date
FROM
school_board.progress p

As you may have noticed, I chose to shorten the name of my previous table to “p” to make it simpler to reference the columns I wanted.

Looker Studio (formerly known as Google Data Studio) allows quick creation of visualization from tables. There are many data sources it can be connected from, one of which is BigQuery. Here we will look at how to connect the two.

First, you go on Looker Studio. Select the "Create" button (top left corner). Then select "Data Source".

There are many data sources to select from, here is a small list of ones you can select! In this case I selected "BigQuery".

Once you select BigQuery, you have to select your project…

And select your dataset and table:

Once you have connected it, you will see all you columns as data you can work with for visuals:
BigQuery has a function that is pretty cool: it allows you to connect a Gsheet with a BigQuery table! Why is this awesome? Well it allows you to use Sheets on very large datasets and do things like make charts, pivot tables, functions, filter columns, etc. This is a very powerful tool, especially if you are used to working in Sheets.

Here is how your table in GSheets will look like after you connect the two:

How to do it

Once you have your table in BigQuery, open a new GSheet. Select: Data -> Data connectors -> Connect to BigQuery

Then select the project, dataset, and table you want to connect:
->

It will take some time to connect (depending on the size of your table) and here is how the connected sheet will look:

Creating BigQuery Table using Google Sheet Table

On your BigQuery homepage for SQL workspace, you can add data:

BigQuery gives lots of options for what kind of data you can add. For myself, I had my data as a GSheet, so I clicked Google Drive:

Here is how creating a table using Google Drive looks like:

For the source section:

Get your google sheet shareable link
If you want, you can specify the cells of data to import (if there are things you want to discard). There are also “Advanced options” that let you take away certain rows/columns (such as the header rows, or any row that doesn't have the same number of columns like the rest of the dataset).

For the destination section:

If you have already created a dataset, this is where you can access it. In my case, I needed to create the dataset.

For creating the dataset:

Create a dataset ID that will be used to reference the tables that will be listed under the dataset
I did not want the table to expire because I knew it was a small amount of data and I wanted to keep it as my project to reference for future uses

After the dataset is created, we need to specify the table name for the table we are currently adding.

From here on, if you wanted to add more tables to the same dataset, then you just need to select the correct dataset (not create a new one).

Next you can either autodetect the schema or you can specify it:

For my dataset I chose to try the “Auto detect” because I wanted to practice my data cleaning skills using SQL as opposed to having BigQuery do it for me!

I thought that this would be a lot harder than it actually was. All that you need to do, once you have a data studios account, is click:

+ Create
Then Data Source
Then select “Google Analytics”
Finally, select whichever website of yours you want to add

I am the type of person who likes to explore and try things out, so I wasn’t a huge fan of this academy course. It gave a lot of information, most of which I did not find that useful. Below you will find the bits that I pulled out from the whole course that I found useful for myself.

Definitions

Dimensions: data categories that can be any type value (including unaggregated numbers)
Metrics: calculations of various aspects of dimensions (aggregated numbers)
Data set: data living outside of Data Studio
Data source: Data Studio’s representation of the data set(s)
A bullet chart (or “thermometer graph”): visualization for comparing a given metrics performance against target benchmarks (usually used for KPIs)

I had no problem understanding how sharing view/edit rights for the report work - as it is like all other Google products - but for some reason I had a hard time wrapping my brain around credentials.

Credentials

These are just for the data source, not the report.

Owner’s credentials: this needs to be set if the people who will be viewing the report DO NOT have their own access to the data set.
Viewer’s credentials: this needs to be set if the people who will be viewing the report DO have their own access to the data set.

Useful Information

Within your data source, you can quick add aggregates (count and count distinct) for dimensions you have:

Or you can even create your own formula by clicking add field:

Then you can type in the formula you want and use the available fields from the left hand column:

The most useful information I got out of the whole course was section 4.1:

Foundational principles of visual design

Start with a quick simple overview of the big picture of the report.
Then, allow them to help themselves to more detail as needed. This might come in the form of filter controls, additional report pages, or hyperlinks that will load more detail when the viewer is ready for it.
Visual elements that are similar in shape, size, and/or color are perceived to be more closely related.
Color is a powerful tool to guide viewers through your report.
- Use transparency to create visual hierarchy: A sliding scale allows you to make component colors more or less transparent.
- Use saturation to draw attention: Intense colors draw the eye, so use to highlight key aspects.
- Don’t rely on color: Too much colour can detract from the main focus points. Some people might also have trouble reading visualizations with too much colour.
Pick (or create) Theme inorder to ensure that the report is stylized the same throughout. It also includes a setting for how colors should apply consistently:
- By series order: The color depends on the order of the item in a series.
- By dimension values: The same value maintains a fixed color.
- Finally, you can also choose colors to represent increasing or decreasing metrics across your report.

Template Use

If you plan on using your report layout more than once, you can:

Simply copy a report OR
Create a template from the report: Simply take the URL of the report, and append “/preview” to the end of the URL. This will prompt the report to appear with the “USE AS TEMPLATE” button, and click yes

More tips to look at.

I decided to make a sample report with the metrics that I wanted to know about my website. I often, at night, check to see if anyone has gone on the website and if so there are certain things I care about: how did they get to my website, which pages did they go on, how long did they spend there, and where is the user located. Normally, it would be a good idea to put the higher level summary first, but I was building this report for myself, so I wanted the daily digest first. With both of these facts in mind, I have my report broken into Today and YTD.

Since Google Analytics sunsetted UA, I re-did my report using the GA4 data: which can be found here!

My very first sample report for this website can be found here!

Setting it up was not as easy as I thought it would be because there was more than one option and initially I didn’t know which one to pick. Upon further research I found out the difference between the new and the old GA:

New (Google Analytics 4): If you created your website using one of the following: Awesome Motive, Drupal, Duda, Google Sites, Site Kit, Squarespace, TYPO3, Wix, WooCommerce, WordPress.com, Blogger, Cart.com, GoDaddy, HubSpot, Magento, One.com, PrestaShop, Salesforce, Shopify, TYPO3, VTEX, Weebly.
Old (Universal Analytics): If your created website does not fall into one of the above categories (as mine did not - I have a custom HTML hosted through GoDaddy) you need to use the old version of GA.

Setting up starts the same for both versions -> click “Start measuring”

Step 1:

You must pick an account name - this will be seen on all pages of the GA

Step 2:

Selecting Data Sharing: there are several to pick from. I just chose technical support based on the fact that I wanted to just measure views on my own personal website, on which I wasn’t selling anything.

Step 3:

Property setup: this is where the difference occurs for the New (G-) version and the Old (UA-) version

New Version (G-)

Old Version (UA-)

Property Setup: Create a Google Analytics 4 property - give it a name (something that will describe what the website is about - I chose “personal website”)
Click “Next”

Step 4:

Choose your platform - for my example I chose “Web”

Step 5:

Enter your website information

Step 6:

Include the gtag.js in the < head> section of your website.

And you are all done!
Property Setup: Click “Show advanced options”
Select Universal Analytics property only

Step 4:

Once it has been created, you can find the information you need to add to the top of your website under "Tracking Code"
It should look something like this:

Step 5:

Copy and paste this code as the first item into the < HEAD> of every webpage you want to track.

Yay! You are all done! Enjoy the data :)

As I used GA for my website, I soon got very frustrated about the view times I was seeing (majority of the time being an average of 0 seconds). Since this seemed like way too small of a number I decided to check out if other people had this experience. Sure enough, a quick search led me to finding that if someone only visited one page and then left, GA would not be able to tell me the time they spent on the page. This was due to the set up of the monitoring by GA, where an action (such as clicking on a new page) must happen for GA to pick an end time from the entrance point.

Since I really wanted to know how long people spent on each page of my website, I spent quite a bit of time researching what to do. After my research, I decided the solution I liked the most was from a blog post I found.

Side note: I did try the other code that they mentioned in the blog, but the website link no longer existed and the copying of the code did not work for me.

All I did was add the following code to my GA code at the top of my pages (after my GA global site tag < /script>):

< script type="text/javascript">
function timer11(){gtag('event','1', {'event_category':'pagename_TimeOnPage','event_label':'11-30 seconds','non_interaction':true});}
function timer31(){gtag('event','2', {'event_category':'pagename_TimeOnPage','event_label':'31-60 seconds','non_interaction':true});}
function timer61(){gtag('event','3', {'event_category':'pagename_TimeOnPage','event_label':'61-180 seconds','non_interaction':true});}
function timer181(){gtag('event','4', {'event_category':'pagename_TimeOnPage','event_label':'181-360 seconds','non_interaction':true});}
function timer361(){gtag('event','5', {'event_category':'pagename_TimeOnPage','event_label':'361-600 seconds','non_interaction':true});}
function timer601(){gtag('event','6', {'event_category':'pagename_TimeOnPage','event_label':'601-1800 seconds','non_interaction':true});}
function timer1801(){gtag('event','7', {'event_category':'pagename_TimeOnPage','event_label':'1801+ seconds','non_interaction':true});}
gtag('event','0', {'event_category':'pagename_TimeOnPage','event_label':'0-10 seconds','non_interaction':true});
setTimeout(timer11,11000);
setTimeout(timer31,31000);
setTimeout(timer61,61000);
setTimeout(timer181,181000);
setTimeout(timer361,361000);
setTimeout(timer601,601000);
setTimeout(timer1801,1801000);
< /script>

When tracking your views, you might want to remove your views so that your views won’t be inflated. There is a super simple way to do it:

Go to “Admin”

Click “All Filters”

→ +Add Filter

In order to remove your specific IP address, select:

Exclude
traffic from the IP addresses
that are equal to
Select the webpages you want to exclude yourself from and move them to “Selected views”

Now enter your IP address. I found the best way to exclude your devices was by looking up what your public IP is. You can do this by simply googling “what is my IP address”.

Anja Wu

BigQuery

Looker Studio