Repository files navigation

• Accelerated Time to Value
• Expertise of Google + Looker data teams
• Accessibility
• Best Practice Design Patterns
• Leveraging Most of BQ Investment

• Google Analytics Premium (GA360) data is exported (in this case, throughTransfer Services) in the format of a single flat table with a new entry for each session. Rather than creating new tables for each entity attribute, Google places aggregate or attribute information in nested fields in the single tabel. For more information on Nested Fields, and why Google chooses to use them, please refer to this overview onWhy Nesting is so Cool.

• There are two types of nested fields: repeated fields and non-repeated fields. In Standard SQL, both are stored asARRAY's, which can contain bothsimple and complex data types. Non-repeated fields can be unnested, and joined using aone_to_one relationship. Repeated fields are unnested, and joined on through aone_to_many join (see lines 30-34 of thega_block view for an example). Please take note that brackets are used for non-repeated fields, and not used for repeated fields, in the join syntax. This is a critical element to working with nested fields in BigQuery.

• Google's documentation on the data included in the export can befound here.

• ga_block contains all join logic and all individual view files, dimensions, and measures. You'll notice each view file has anextension: required parameter, which is also why each table has a_base suffix. This means each file must be appended to another table to show up on the Explore page. These other tables are located in thega_customize view. Documentation on extends can befound here.

• ga_customze: contains all the extended view files. If your organization uses any custom fields with Google Analytics, this is where you can insert those fields. In-line directions are provided in the code.It's important to note that, if you want to Explore a table, there must be an extends parameter under the correspdoning table in this view file. For example, if I wanted to exploretrafficsource, I would add anextends: [trafficsource_base] parameter underview: trafficsource.

• View:ga_customize line 19 parameter forsql_table_name must be customized to the usersdata_set.ga_session_*.

• Dashboards: Replacemodel: ga_connectors360 with your chosen name.

• Available Explores: Recall from above, that eachextends field but must declared to allow users to Explore on a view.

The image below depicts the possible linkes between various Google and external data sources. Documentation on how to link each of these sources can also be found below.

• Doubleclick Campaign (Bid) Manager: Requires admin permissions for the Google users.Follow the documentation here.

• DoubleClick for Publishers: This integration includes bothAdSense andAd Exchange, and requires work on the side of the Google Admin, as well as the creation of a tagging system using either self-built or Google service-provided tag managers. After tagging is enabled, the following steps can be found inGoogle's Documentation. Once you've done this, be sure to include the AdWord data by adding theextends: [hits_publisher_base] beneathview: hits_publisher (on line 90 inga_customize out-of-the-box)

• Adwords: Connecting AdWords is fairly straightforward. Follow the instructions provided inGoogle's documentation. Once you've done this, be sure to include the AdWord data by adding theextends: [adwordsClickInfo_base] beneathview: adwordsClickInfo (on line 86 inga_customize out-of-the-box)

• YouTube: Youtube integration can be configured via the GUI in the GA360 console, though only data around the Youtube content can be linked, not user-level or client-speficic data.

• CRMs or Other Sources Google does not capture any PII, which means thatuser_id and/orclient_id is unique to only Google Analytics Premium. This key is not shared, by default, across any of your CRM data, or any other data sources you're pulling from. To join this data, a common key must be created. There are several methods to accomplishing this, one of which Google has provided somedocumentation around.