We conduct app log analysis using data from Google Analytics 4 and Firebase. It includes useful content for practical use, such as arrays, window functions, funnels, retention, Google Sheets, and more.
730 learners
Level Intermediate
Course period Unlimited
Reviews from Early Learners
5.0
흰임금펭귄
*𝓑𝓮𝓼𝓽 𝓢𝓠𝓛 𝓒𝓸𝓾𝓻𝓼𝓮 𝓸𝓷 𝓘𝓷𝓯𝓵𝓮𝓪𝓻𝓷 𝓲𝓷 2024* Those who have read the basic course reviews will know that this is a really, really good lecture for SQL users. 🥺ྀི Personally, Inflearn Number One~ A lecture that I would listen to even if it were twice the price Aren't there always people who have grown into wild queries by using SQL written by others in a position other than an analyst? If I were to recommend just one lecture to my past self, it would be Kyle School... Use BigQuery at your company! (I will explain in detail how to use BigQuery) I can run it by slightly modifying the conditions in the query that has been prepared, but I can't do it alone! I know how to prepare it, but I don't know how it goes through the refining process! I've been waiting for you. I will serve you properly. Buy it now🏃🏃 Then I'll hold my breath until the next part comes out (sigh~)
5.0
망고맛있어
I took this course because I participated in the quick study group 💪 I was able to complete the course in a few weeks thanks to the study group:) [Course review] 1. You can experience practical data analysis : The course focuses on data analysis using BigQuery, which is not well explained in existing courses. You can learn about retention, funnel analysis, and cohort analysis, which are widely used but not well covered in other courses. I think this course is the best course to experience how to use BigQuery in the field and practical use. 2. Smooth communication with experienced lecturers The course also operates a Discord room, and another advantage is that you can communicate through the lecture community > Q&A. Compared to other courses, I felt that they tried to listen to the opinions and questions of the students, and it was good to be able to hear advice from someone with experience in data analysis. In particular, upon completion of the course, you can get a one-time consultation ticket, so it feels like you have a mentor. 3. Difficult grammar made easy! : Personally, it was hard for me to learn Windows functions on my own, but it was good that you explained it easily and that I could see how it is used in actual log data. Also, it was good that I could learn about the usage and utilization of unnest and array, which are often used in the field. 4. You can apply what you learned to data similar to the field! : It is often difficult to apply what you learned to actual data, but it was good that there was a curriculum that applied what you learned to log data similar to the field data every time you learned a technology or grammar. [Recommended for] 1. Those who want to experience SQL-based data analysis 2. Those who want to experience practical data analysis 3. Those who dream of becoming a data analyst and those preparing for a job change
5.0
homebrew1
I am a student who completed the basic lecture. - The contents are largely 1. BIGQUERY advanced grammar 2. PM-like thinking. - 1 was what I originally wanted, so I was really satisfied. I think it is definitely something that marketers who will analyze data with GA4 should know. - 2 was something I already knew, but it is something that those who have not actually worked at a company or have worked at a company should know, so I think it will be useful for those who are not familiar with terms such as growth hacking/retention. - Outside of the lecture, what impressed me the most was that you also use Discord, continuously monitor feedback, and even provide 1:1 coaching. Regardless of the lecture fee, I can feel that you are trying to care about your students.
BigQuery
SQL
Google Sheets
Window function
Handling ARRAY, STRUCT (UNNEST)
App Log Data Analysis
Retention analysis
Funnel analysis
When I was a new employee, I first encountered Google Analytics and Firebase data. At that time, there was almost no data on user log data, and I had no idea how to handle this data. While looking at various materials, I learned how to load user log data generated from GA4/Firebase into BigQuery and how to analyze the loaded data. As I accumulated more and more experience, I have now created my own pattern. I would like to share the contents.
In the BigQuery utilization section, you will learn functions that were not covered in the BigQuery (SQL) introduction for beginners while conducting funnel analysis and retention analysis. The difficult parts when approaching BigQuery are ARRAY and STRUCT, and I will share how to approach these parts. After that, I will introduce window functions. If you learn window functions well from the beginning, it will be very convenient when writing SQL in the future. I will introduce window functions based on cases that I have experienced in the field.
Google Analytics 4 and Firebase have retention features, but they are difficult to use in practice due to functional limitations.
Limitations of functionality: It is difficult to sample data and set detailed conditions on retention.
For this reason, you need to export data to BigQuery and write retention yourself. If you are in that situation, you can apply it right away by taking this course. It covers in detail what criteria to use to write retention and how to interpret it.
You can solve the basic part of SQL coding test by looking at the introduction, and in-depth content of SQL coding test often comes up with problems that extract data by giving window functions or specific logic. It is possible to solve these problems many times, but it requires a process of thinking about difficult logic yourself.
The lecture is designed so that you can solve it by presenting various practice problems in the lecture. You can get a feel for the ARRAY and window function parts by solving practice problems. Most of the problems in the window function are given with similar patterns (only the data is different). You can look at these problems and think about how to solve them.
In the retention part or custom session creation part, you specify specific logic and write that part as a query. Think about the input of the data and the final desired output form, and write the query while thinking about the intermediate output of the process . This part may feel difficult at first, but it's okay. This lecture is a lecture to maximize your ability, so it's natural to feel that way at first (I did too). However, if you take the lecture well, I think your SQL ability will improve a lot.
There is a student who took the course up to the utilization level and left comments about the coding test. He said that he passed the coding test of a domestic unicorn company that we all know.
We thought about how to make it so that even people who have been using BigQuery for more than 3-4 years can get help, and included the contents.
Even if your company doesn't necessarily use BigQuery (even if you use Spark or another DW), you will still get something out of this course. The flow of writing queries, what problems are you writing queries to solve, and how should you think? This part will be helpful to you.
We are sharing reviews left by our students.
I'm sharing some of the reviews left by other students (you can read more about them in the article below)
Please look at the two questions below and think about whether the correct answer is obvious.
The first problem is to think specifically about what action to take , and the goal is to present specific action items rather than simply ending with one or two lines.
The second problem is the problem of handling NULL in the Windows function ( Windows function practice problem: You need to give a specific option, not just the LAG function, to get a normal value)
Interpreting the Weekly Retention Curve from Lectures: How to Interpret It?
Windows Function Practice Problem: It needs to be run with specific options, not just the LAG function
📌 I created the app log data myself. It is about 700,000 rows, and I created the app data for the delivery service.
📌 We share BigQuery advanced grammar (ARRAY, STRUCT, UNNEST, window functions, DECLARE, etc.) and how and when to use it . We only include the content that is absolutely necessary in practice.
📌 I will share with you how to interpret funnel analysis/retention analysis , not just writing queries and ending. When I was a junior, it was really difficult to just write queries and think about what to do at the company. I will share my experience in this area.
📌 We will share a simple visualization method using Google Sheets , and also share useful content that can be used in practice, such as schedule queries and VIEWs.
📌 There are practice problems and assignments . For the parts where you need to get used to grammar, we provide practice problems (ARRAY: 4 problems, PIVOT: 3 problems, Window function: 9 problems), and for the retention analysis and overall assignments, we provide assignments based on tasks that you can experience in the field. If you solve these parts and post them on the bulletin board, we will give you feedback.
📌 This lecture is not for beginners. It is for those who have taken the BigQuery (SQL) introductory course for beginners or know about SQL JOIN.
Anyone who needs to analyze app logs
Those who want to analyze app logs and derive action items rather than simply writing queries
For those who need in-depth learning of BigQuery
Anyone who uses BigQuery at work and needs more in-depth learning
People in the Product Analyst position
If you are a product analyst, you are curious about funnel analysis and retention analysis, which you must experience.
You will be able to use BigQuery to do funnel analysis . As you think about what parts are important in funnel analysis, you will also be able to write queries for the funnel.
You will be able to use BigQuery window functions . You can become familiar with the different types of window functions and when to use them by solving practice problems.
You can handle ARRAY and STRUCT in BigQuery.
You can now do PIVOT in BigQuery
You will learn how to conduct retention analysis and how to write queries.
You can save and use UDF.
Understand how to leverage log data from Google Analytics 4 and Firebase
You can export and visualize data from BigQuery using Google Sheets .
Cloud GDE (Google Developer Expert)
Google BigQuery Complete Guide Translator
Boostcamp AI Tech Instructor - Product Serving/Learning Master (2022 ~ Present)
Kyle School (July 2022 ~ Present): Data Coaching, Consulting (DA/DS/DE), Education
Socar Data Scientist (2018.09 ~ 2022.07)
Retrica Data Analyst & Data Engineer (2017.02 ~ 2018.04)
Tech Blog: https://zzsza.github.io/
YouTube: Kyle School
Instagram: @data.scientist
Big Flow Diagram of Funnel Analysis
Big Flow Diagram of Retention Analysis
Summary of Windows Function Core
Handling Array Data: Flattening with UNNEST
BigQuery runs on a web browser, so you can take the course on either Mac or Windows.
You can come here after creating a project as shown in the BigQuery (SQL) introductory course for beginners.
It is provided in PDF format, and we also run a Discord channel, so feel free to ask questions if you have any.
You should take the introductory course on BigQuery (SQL) for beginners to understand it smoothly.
However, for those who did not take the course, I will summarize the lecture and mention only the necessary parts so that you can take the course in part.
If you want to leave a lecture record on your blog, you can do so by leaving the lecture URL. However, sharing the data or PDF file itself is not possible.
However, uploading most of the lecture may cause copyright issues. I recommend that you write an article by adding your own thoughts and the key points you want to remember from the lecture.
If you look at the blog posts, you can think about it from the perspective of whether it is okay to not take the class.
Who is this course right for?
For those who have learned the Introduction to BigQuery (SQL) for beginners and wish for additional learning
Data analysts looking to improve SQL skills
Product Analysts: Funnel/Retention Analysis in BigQuery required.
For those who want to learn BigQuery array data, window functions
Need to know before starting?
BigQuery(SQL) Lecture for Beginners
Basic SQL syntax must be known (SELECT, FROM)
Inflearn Verified
Career Verified
16,692
Learners
690
Reviews
452
Answers
4.9
Rating
7
Courses
I have worked as a data scientist, data engineer, and machine learning engineer for 10 years, and I have developed data analysis, data engineering, and machine learning algorithms at Socar and Tada.
I am uploading videos related to data careers on the Kyle School YouTube channel, and I am creating materials while constantly thinking about how to help those who take my lectures perform well at their companies.
I am active as a Google GDE (Cloud).
Kyle School YouTube: https://www.youtube.com/c/kyleschool
Tech Blog: https://zzsza.github.io/
Instagram: https://www.instagram.com/data.scientist/
Featured Content: https://github.com/Team-Neighborhood/I-want-to-study-Data-Science
Various efforts made to become a data scientist: https://zzsza.github.io/diary/2019/04/05/how-to-study-datascience/
All
62 lectures ∙ (10hr 35min)
Course Materials:
All
81 reviews
5.0
81 reviews
Reviews 2
∙
Average Rating 5.0
5
I am a student who completed the basic lecture. - The contents are largely 1. BIGQUERY advanced grammar 2. PM-like thinking. - 1 was what I originally wanted, so I was really satisfied. I think it is definitely something that marketers who will analyze data with GA4 should know. - 2 was something I already knew, but it is something that those who have not actually worked at a company or have worked at a company should know, so I think it will be useful for those who are not familiar with terms such as growth hacking/retention. - Outside of the lecture, what impressed me the most was that you also use Discord, continuously monitor feedback, and even provide 1:1 coaching. Regardless of the lecture fee, I can feel that you are trying to care about your students.
Hello, homebrew1! Thank you for taking the basic and application courses. If you have any questions while taking the course, I want to help you out, so I run a 1:1 coaching and Discord. We will work together to solve problems and continue to create education that is actually helpful. Thank you!
Reviews 1
∙
Average Rating 5.0
5
Hello, Kyle School! I'm a long-time fan. I'm on my way to landing a job as a data analyst, and Kyle School has been a great help. I hope data engineering lectures will be released soon. I'm currently studying AIRFLOW, and it's my first time, so I'm struggling a lot. I've completed all the lectures, and I'm planning to create my own blog and implement GA in the process. Now, I'm going to do a deep dive and analyze the existing data. I'm currently going through a tough time preparing for a job, but thanks to Kyle School's lectures, my thoughts on how I can contribute to the company are expanding more and more. In the process, I'm dreaming and working hard today. Thank you! P.S. If you release lectures in the future, will there be a discount event for job seekers? 😭😭
Reviews 5
∙
Average Rating 5.0
5
Thanks to Kyle, I passed the SQL Test and job interview at a domestic unicorn company! Even though I'm an experienced worker, it was my first time taking a coding test, so I was actually quite nervous, but I solved it so easily. The retention problem that you said "comes out when you put in the effort" really came out😆😆 I solved it the easiest thanks to the logic that we covered a lot in the lecture! To be honest, I don't think that "just listening will guarantee you to pass". I participated in a study group, worked hard on solving problems, analyzed other people's solutions in the lecture community, and made my own problems using the data sets you provided and solved them... I tried my best to do as much as I learned. <Tips for getting the most out of the lecture> Beginner: Just listen to the lecture Intermediate: Solve the homework yourself and practice Advanced: Use the community and don't hesitate to get 1:1 coaching (+ I got a lot of advice 1:1 and passed the job interview🤙) Also, many people don't know about the Inflearn roadmap feature, so check it out. https://www.inflearn.com/roadmaps/4639 I've received a lot of help from your blog a few years ago to this lecture, but this is my first time leaving a review. Thank you for being my mentor outside of my company, from my first job search to my work and when I changed jobs🙏 I'm looking forward to the next lecture that you announced at the end!!!
Reviews 18
∙
Average Rating 5.0
5
I took this course because I participated in the quick study group 💪 I was able to complete the course in a few weeks thanks to the study group:) [Course review] 1. You can experience practical data analysis : The course focuses on data analysis using BigQuery, which is not well explained in existing courses. You can learn about retention, funnel analysis, and cohort analysis, which are widely used but not well covered in other courses. I think this course is the best course to experience how to use BigQuery in the field and practical use. 2. Smooth communication with experienced lecturers The course also operates a Discord room, and another advantage is that you can communicate through the lecture community > Q&A. Compared to other courses, I felt that they tried to listen to the opinions and questions of the students, and it was good to be able to hear advice from someone with experience in data analysis. In particular, upon completion of the course, you can get a one-time consultation ticket, so it feels like you have a mentor. 3. Difficult grammar made easy! : Personally, it was hard for me to learn Windows functions on my own, but it was good that you explained it easily and that I could see how it is used in actual log data. Also, it was good that I could learn about the usage and utilization of unnest and array, which are often used in the field. 4. You can apply what you learned to data similar to the field! : It is often difficult to apply what you learned to actual data, but it was good that there was a curriculum that applied what you learned to log data similar to the field data every time you learned a technology or grammar. [Recommended for] 1. Those who want to experience SQL-based data analysis 2. Those who want to experience practical data analysis 3. Those who dream of becoming a data analyst and those preparing for a job change
Thank you for leaving a detailed review of the course after the introduction and the utilization course. I think Mangomatgae has improved through this experience. I hope you will continue to improve in the future! Please come to Coaching Kwon when you have any concerns..!
Reviews 1
∙
Average Rating 5.0
5
*𝓑𝓮𝓼𝓽 𝓢𝓠𝓛 𝓒𝓸𝓾𝓻𝓼𝓮 𝓸𝓷 𝓘𝓷𝓯𝓵𝓮𝓪𝓻𝓷 𝓲𝓷 2024* Those who have read the basic course reviews will know that this is a really, really good lecture for SQL users. 🥺ྀི Personally, Inflearn Number One~ A lecture that I would listen to even if it were twice the price Aren't there always people who have grown into wild queries by using SQL written by others in a position other than an analyst? If I were to recommend just one lecture to my past self, it would be Kyle School... Use BigQuery at your company! (I will explain in detail how to use BigQuery) I can run it by slightly modifying the conditions in the query that has been prepared, but I can't do it alone! I know how to prepare it, but I don't know how it goes through the refining process! I've been waiting for you. I will serve you properly. Buy it now🏃🏃 Then I'll hold my breath until the next part comes out (sigh~)
Wow, this is such a touching review. There was a time when I had a hard time looking at the queries that I received from my company. I hope that many people will not have to go through this part. Thank you so much for taking the class!!
Check out other courses by the instructor!
Explore other courses in the same field!
25% off for new members
$57.00
25%
$77.00
![[Renewed] First-Time SQL and Database (MySQL) Bootcamp [From Beginner to Practical Applications]Course Thumbnail](https://cdn.inflearn.com/public/courses/324208/cover/85872a8e-d2bb-4c43-82fc-d55fa067746e/324208.png?w=420)
![59th Session (25.11.16) Reconstruction of Past Exam Questions Evaluated as Relatively Easy - All Questions by Subject and Chapter [SQLD Past Questions are the Answer!]Course Thumbnail](https://cdn.inflearn.com/public/files/courses/339593/cover/01kky9mwapgkp8097vy2bgwqgq?w=420)
![[MMORPG Game Development Series with C# and Unity] Part 5: DatabaseCourse Thumbnail](https://cdn.inflearn.com/public/courses/325283/course_cover/afb413e6-681e-47cd-808a-099c79fed112/rookiss-rpg-5.jpg?w=420)
![[Unity] Implementing Essential Features for Multiplayer Games - From Server & Data Management!Course Thumbnail](https://cdn.inflearn.com/public/courses/336269/cover/7376c9d2-0ad7-430e-824d-3c1f5170a96e/336269.png?w=420)
