Inflearn brand logo image
Inflearn brand logo image
Inflearn brand logo image
BEST
Data Science

/

Data Analysis

BigQuery(SQL) Application (Funnel Analysis, Retention Analysis)

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.

(5.0) 49 reviews

496 learners

  • kyleschool
데이터분석
bigquery
데이터시각화
SQL
Firebase
Google Sheets
Google Analytics

Reviews from Early Learners

What you will learn!

  • BigQuery

  • SQL

  • Google Sheets

  • Window function

  • Handling ARRAY, STRUCT (UNNEST)

  • App Log Data Analysis

  • Retention analysis

  • Funnel analysis

How to do funnel analysis/retention analysis using data from Google Analytics 4 and Firebase?

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.

🎈 Recommended for those who use Google Analytics 4 and Firebase 🎈

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.

🚀 Also recommended for those preparing for SQL coding tests 🚀

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.

🎁 I recommend it to those who have used BigQuery quite a bit or those who use other DBs 🎁

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)

What if someone with 5 years of SQL experience takes a BigQuery course?

Questions to determine if you should take this course


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

If you don't have an immediate answer to the question above, I recommend taking this course.

Features of this course

📌 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.


I recommend this to these people

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.

After class

  • 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 .


🔥 After class, coaching event 🔥

After you have taken more than 75% of the lecture, if you leave a survey, we will conduct coaching. The coaching topic will be based on the assignment in the BigQuery utilization section, and we will provide detailed feedback on how to write it better. Or, you can just talk lightly about this assignment and talk about your personal concerns.

For more details, please refer to lectures 0-5.

Who created this course

  • 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

Learn about these things.

Big Flow Diagram of Funnel Analysis

Big Flow Diagram of Retention Analysis


Summary of Windows Function Core


Handling Array Data: Flattening with UNNEST


Things to note before taking the class

Practice environment

Learning Materials

  • It is provided in PDF format, and we also run a Discord channel, so feel free to ask questions if you have any.

Player Knowledge and Notes

  • 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.

Recommended for
these people

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)

Hello
This is

12,727

Learners

388

Reviews

351

Answers

4.9

Rating

5

Courses

9년차 데이터 과학자, 데이터 엔지니어, 머신러닝 엔지니어로 근무했으며, 쏘카와 타다에서 데이터 분석, 데이터 엔지니어링 개발, 머신러닝 알고리즘을 개발했습니다.

카일스쿨 유튜브에 데이터 커리어 관련 영상을 올리고 있으며, 어떻게 해야 강의를 수강하신 분들이 회사에서 일을 잘할 수 있을까?를 고민하며 자료를 만들고 있어요.

Google의 GDE(Cloud)로 활동하고 있어요.

 

카일스쿨 유튜브 : https://www.youtube.com/c/kyleschool
기술 블로그 : https://zzsza.github.io/
인스타그램 : https://www.instagram.com/data.scientist/
대표 컨텐츠 : https://github.com/Team-Neighborhood/I-want-to-study-Data-Science
데이터 과학자가 되기 위해 진행한 다양한 노력들 : https://zzsza.github.io/diary/2019/04/05/how-to-study-datascience/

Curriculum

All

62 lectures ∙ (10hr 35min)

Course Materials:

Lecture resources
Published: 
Last updated: 

Reviews

All

49 reviews

5.0

49 reviews

  • hbrew0015966님의 프로필 이미지
    hbrew0015966

    Reviews 2

    Average Rating 5.0

    5

    79% enrolled

    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.

    • kyleschool
      Instructor

      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!

  • meta025519님의 프로필 이미지
    meta025519

    Reviews 1

    Average Rating 5.0

    5

    32% enrolled

    *𝓑𝓮𝓼𝓽 𝓢𝓠𝓛 𝓒𝓸𝓾𝓻𝓼𝓮 𝓸𝓷 𝓘𝓷𝓯𝓵𝓮𝓪𝓻𝓷 𝓲𝓷 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~)

    • kyleschool
      Instructor

      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!!

  • leona님의 프로필 이미지
    leona

    Reviews 5

    Average Rating 5.0

    5

    100% enrolled

    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!!!

    • tett77님의 프로필 이미지
      tett77

      Reviews 18

      Average Rating 5.0

      5

      100% enrolled

      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

      • kyleschool
        Instructor

        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..!

    • jaanepark님의 프로필 이미지
      jaanepark

      Reviews 3

      Average Rating 5.0

      5

      100% enrolled

      Since it is a small startup and there is no data analyst, PMs often directly extract data through queries. However, I have not attempted retention and funnel analysis because the queries are complicated. Since the lecture title was about utilizing BigQuery, I took the lecture without hesitation! If you are already proficient in SELECT and JOIN to some extent, it will be easy to follow. If you are new to SQL, I recommend taking the basics class and then the utilization class. The window functions I learned here were so useful that I could use them right away in the field. I will also try retention analysis and funnel analysis soon. Thank you for opening a great lecture :)

      • kyleschool
        Instructor

        Thank you for your course review, Jae-eun. It's been 3 months. I wonder if you are making good use of what you have learned. I hope you continue to improve in the future!!

    $77.00

    kyleschool's other courses

    Check out other courses by the instructor!

    Similar courses

    Explore other courses in the same field!