Who is this Course For?
The course will suit you if you:
- Tired of constant limitations in the GA4 interface: sampling, thresholding, not being able to select data in custom reports for more than 14 months, etc.
- Want to start using raw data from marketing systems to make business decisions
- Want to learn how to combine raw data from different systems into one report
- Know SQL perfectly, but you don't understand the business context of GA4 data and Google Ads
- Know everything there is in the program, but you don't want to write SQL queries from scratch and are looking for ready-made solutions with detailed explanations of what and how it works, and how it can be customized
The course will NOT suit you if you:
- Don't know the basics of SQL. Yes, unfortunately, this course is designed for people who already know basic SQL statements and know how to configure GA4 data export to BigQuery. If you also want to raise your level to the desired level, you can take the PRO ANALYTICS course, or write to us in Telegram and we will tell you how you can raise your level of knowledge on free resources
Learning Experience
- 01
VIDEO LECTURES
10 lectures with all the theory. Access to course materials, as well as UPDATES, remains with you forever.
- 02
TEST TASKS
More than 40 test tasks for independent verification of theoretical knowledge.
- 03
PRACTICAL TASKS
20+ homework on the Google BigQuery test account, which will allow you to consolidate the acquired knowledge not only in theory, but also allow you to gain practical experience.
Course Syllabus
- Module 1.
Building and customizing standard GA4 reports based on raw data
What You Will Learn- Reasons why you need to build standard reports in BigQuery
- Building the Pages and Screens report
- Working with the UNNEST operator
- Why results in BigQuery do not match Google Analytics 4 reports
- Construction of the User Acquisition report
- Peculiarities of counting the number of sessions and users
- Familiarity with window functions in BQ
- Why is it important to use _table_suffix
- Working with Saved Queries
What You Will AchieveSince this course is designed for those who already know the basics of SQL, we will not delay and immediately start with practical problems. In this lecture, you will learn how to build Pages and Screens and User Acquisition reports based on raw GA4 export data to BigQuery and learn about the important nuances of working with this data
What You Will Learn- Why not work directly with data from GA4 exports
- Why don't we take source data from the event level
- Preparation of a table with traffic sources at the level of each session
- Last Click VS Last Non-direct Click attribution models
- The nuances of the Last Non-direct Click attribution model
- Building a report on traffic sources Traffic Acquisition in the Last Non-Direct Click attribution
- Resolving a problem with GCLID
- Request to check the correctness of UTM-marking
- Using bq_util functions to work with URLs
- Creating custom functions in BQ
- Building a Landing Page report
- Nuances with traffic sources, if there were several sources within the session
- Saving query results in BigQuery - View vs Table
- Scheduled queries (Scheduled query). What is the advantage and how to configure
What You Will AchieveAfter watching the lecture, you will learn how to build Traffic Acquisition reports in the Last Non-Direct Click and Landing Page attributions, and, of course, you will learn what you need to take into account when building them.
What You Will Learn- Why it's important to analyze the entire user journey to conversion
- Construction of a request for multi-channel sequences (chains of conversion)
- Let's deal with the LAG and ROW_NUMBER window functions. Window ranges (frame)
- Building a linear attribution model based on raw data
- Building attribution based on positions
- Working with arrays in BQ
What You Will AchieveAfter watching the lecture, you will understand why it is important to analyze not only the last interaction of the user before the conversion, but also the entire chain of sessions from the first visit of the visitor to the site. Well, of course, you will learn exactly how to analyze this chain of multi-channel sequences.
What You Will Learn- Differences of Ecommerce events from others
- What are Nested and Repeated fields and how to work with them
- How to correctly calculate Purchase Revenue when working with raw data
- Building a standard transaction report
- Construction of a standard report on goods
- Custom reports on Ecommerce in BigQuery
- Using variables when writing queries
- How BigQuery can help with Ecommerce data analysis
- How to calculate the coefficient of transition from stage to stage in BQ
What You Will AchieveIn this lecture, you will learn how to build an Ecommerce purchases report, learn how to work with ecommerce data that is available in the GA4 export, and understand how you can improve the standard reports available in the Monetization collection if you work with raw data and not in the GA4 interface.
- Module 2.
Working with advanced data analysis techniques: funnels, path analysis, cohorts
What You Will Learn- Why do you need to build funnels in BQ. Open Funnels VS Closed Funnels
- Construction of open pits in BQ
- Construction of closed funnels in BQ
- Construction of funnels by events and users
- Limiting the time between events at different stages of the funnel
What You Will AchieveThe topic of the lecture speaks for itself: you will learn to build any type of funnel you need on any data. Of course, we will also analyze the nuances of each type and analyze them in great detail, using practical examples.
What You Will Learn- Why do you need to build a user path to conversion
- Path to previous and next page
- Path Exploration 5 steps forward and backward from page-by-page conversion
- The most common paths along which users move in the section of page categories
- Path Exploration is 5 steps forward and backward from event conversion
What You Will AchieveYou will learn how to replicate the Path Exploration technique from the Data-Driven Exploration in BigQuery block. And, of course, about how it can be improved by working with raw data.
What You Will Learn- What is cohort analysis. What is it for. Limitations in the interface
- How to work with user_id in BigQuery
- Building classic cohorts from the first visit to the site to the purchase (for any conversion)
- Construction of cohorts by user_id
- Mapping of user ID user_id and device ID user_pseudo_id
- Loading data from BigQuery into Google Sheets
What You Will AchieveYou'll learn everything you need to know about cohort analysis based on data stored in BigQuery. And in addition to this, you will learn how to integrate BigQuery with Google Spreadsheets, which allows you to expand the possibilities of working with data and automate reporting.
- Module 3.
Working with data from Google Ads, CRM and other systems. Combining data from different systems with each other
What You Will Learn- What is data transfer to BigQuery
- Transfer of Google Ads. How to load historical data into BigQuery
- Building a report on expenses by campaign identifiers
- How to display costs in the section of the last name of the campaign
- Data breakdown to ad group and keywords
- The nuances of campaigns without ad groups in BigQuery
- Work with geo data
- Nuances of working with geographic location identifiers in BigQuery
What You Will AchieveYou will learn everything you need to know about working with Google Ads data in BigQuery: how to configure the transfer of this data to BigQuery, what data you can receive and in what form, and of course we will analyze practical examples of the nuances of working with the received data.
What You Will Learn- Two methods of combining data in SQL - JOIN and UNION
- Combining GA4 and Google Ads data to decipher gclid
- Combining data from CRM and GA4. We attach information about the chain of channels and the path to purchase to real purchases by client_id
- Combination of call tracking data and CRM by phone number
What You Will AchieveWorking with data from one system is cool, but BigQuery reveals itself the most when you start combining data from different systems in your reports. Thanks to many practical examples, this is exactly what you will learn in this lecture.
What You Will Learn- Using event_timestamp with local time zone instead of event_date in BigQuery. DATETIME and TIMESTAMP data types
- Quickly find out which events are configured on the project, on which pages they work and which additional parameters are transferred with them
- Export and streaming data, how to combine and when to use only streaming
- GA4 BigQuery export schema changelog
- Using user property data from GA4 exports
- System tables in BigQuery. An example of their use for updating reports
- Creating automatic backups in BigQuery
- Timetraveling in BigQuery
- Tools for auto-generation of queries
What You Will AchieveThis lecture is a collection of tips and life hacks that will help you write queries and work with data faster and more conveniently.
Course Fee
What You Will Gain
- 17+
hours of video materials on the platform are divided into short videos and conveniently structured to quickly return to them when solving specific tasks.
- 50+
test tasks on the platform that will help consolidate theoretical knowledge and develop web analytics skills on real examples.
- 10+
tasks on the Google test demo account, where you need to build a report yourself or find an answer when working with data.
- ∞
time to complete the course. You can safely take the course at a comfortable pace and manage your time, focusing more on tasks that are important to you.
- ∞
access to an alumni community that brings web analytics professionals together so you can continue to learn, network and discover new ideas.
- ∞
access to all materials, which will allow you not only to repeat the studied material, but also to get access to updates and additional content.
Maks Hapchuk
@maksgapchuk
/maks.hapchukWe are trusted by
Take a test drive of our courses right now - watch free review videos with a mentor.
I Want to Try
No worries, it happens! If you're stuck or have questions about our courses, just let us know. We're here to help you find the perfect course that suits you and your future goals.