<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Andy Guliev on Medium]]></title>
        <description><![CDATA[Stories by Andy Guliev on Medium]]></description>
        <link>https://medium.com/@andy-guliev?source=rss-444fdbdeed0f------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*wA5Kde3BSu-xFVGZ.jpg</url>
            <title>Stories by Andy Guliev on Medium</title>
            <link>https://medium.com/@andy-guliev?source=rss-444fdbdeed0f------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 02:07:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@andy-guliev/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Analysis of events inside the game application]]></title>
            <link>https://andy-guliev.medium.com/analysis-of-events-inside-the-game-application-64cca3bda47?source=rss-444fdbdeed0f------2</link>
            <guid isPermaLink="false">https://medium.com/p/64cca3bda47</guid>
            <category><![CDATA[google-data-studio]]></category>
            <category><![CDATA[google-big-query]]></category>
            <category><![CDATA[game-analytics]]></category>
            <dc:creator><![CDATA[Andy Guliev]]></dc:creator>
            <pubDate>Sun, 28 Mar 2021 16:26:42 GMT</pubDate>
            <atom:updated>2021-04-10T15:22:00.132Z</atom:updated>
            <content:encoded><![CDATA[<h3>Mobile Game Analytics</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/637/1*Yr7CHCfmhAFwCQ7sAs0FCA.png" /></figure><p>How to understand what the user’s behavior is inside the game application, what motivates to continue playing and what is an obstacle.</p><p>Let’s analyze the behavior of users of the game, as an example, let’s take the <a href="https://console.cloud.google.com/bigquery?p=firebase-public-project&amp;d=analytics_153293282&amp;t=events_20180720&amp;page=table">public dataset of the game Flood it</a>.</p><p>I did not find the game itself on Google Play, available for download, but to understand what kind of application it is, what is the logic and mechanics of the game itself, below I give a link to an application very close to the data from which is in the dataset.</p><p><a href="https://play.google.com/store/apps/details?id=com.wetpalm.colorflood2">Game: <strong>Color Flood 2</strong></a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/205/1*WX5vRtm0V2H-2AEn6lri_Q.png" /></figure><p>Tables with lots of data and chunks of SQL code are great. <br>But without correct data visualization, it is difficult to see the whole picture of what is happening, any patterns. What users are doing inside your game.<br>Therefore, let’s start by looking at the Dashboard, with the visualization of user behavior data.</p><figure><img alt="Dashboard for in-game events of the application Flood It" src="https://cdn-images-1.medium.com/max/1024/1*YGS9Gv0YDhr-T4ZTp4U_Uw.png" /><figcaption>Dashboard for in-game events of the application Flood It</figcaption></figure><p>On the dashboard, we can see:<br>a) how many times users have launched the game in quickplay mode / on what size boards<br>b) the average number of launches per user, on each type of game board<br>c) detailed table of how many times in total, how many steps the players were able to pass one or another board, for example: the bulk of the players, 452 times were able to pass the board of size S, in 22 steps. At the same time, there are supermen among the players who were able to complete this board in 15 steps, although there were only 4 such successful quickplay endings.<br>d) the same detail as in the table above, with a breakdown of the type of board / the number of steps for which it was possible to pass the board — but the indicator here is already different, how many, on average, there were successful passes per unique user level in a certain number of steps.</p><blockquote>Judging by the data on the dashboard, we see that the main life in the game is centered around the board of size S, the huge difference in the total number of starts on the board S in comparison with the rest can say one thing, when the size of the board is changed, the game process is significantly complicated, which obviously stops the players … if find a way to soften the transition to the next level — you can increase the user’s lifetime in the game, as a result) success is not far off.</blockquote><p>The basis for the dashboard in Google Data Studio is a table obtained as a result of executing SQL code in Bigquery:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/351/1*OEggw1GdNCYuAmUdll_sAQ.png" /></figure><p>The onion structure code for building this table:</p><pre>-- ( 4 ) START Fourth piece of code <br>--       summary results for each type of board</pre><pre>SELECT board_type,<br>       score,<br>       SUM(Count) AS Sum_count,<br>       AVG(Count) AS Avg_count<br>       FROM (</pre><pre>-- ( 3 ) START Third piece of code<br>--       the sum of the results of a unique player</pre><pre>SELECT user_pseudo_id,<br>       board_type, <br>       score,<br>       COUNT(score) AS Count<br>       FROM(</pre><pre>-- ( 2 ) START Second piece of code<br>--       board type and unique user result</pre><pre>SELECT user_pseudo_id,<br>       FORMAT_TIME(&#39;%T&#39;, <br>       TIME(TIMESTAMP_MICROS(event_timestamp))) AS Event_time,<br>       event_name,<br>       MAX ( if ( param.key = &quot;value&quot;,<br>             param.value.int_value, NULL )) AS score,<br>       MAX ( if ( param.key = &quot;board&quot;, <br>             param.value.string_value, NULL )) AS board_type</pre><pre>FROM (</pre><pre>-- ( 1 ) START First piece of code / QuickPlay End Event</pre><pre>SELECT event_name,<br>       event_timestamp, <br>       user_pseudo_id,<br>       param</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`,<br>      UNNEST(event_params) AS param</pre><pre>      WHERE event_name = &quot;level_complete_quickplay&quot; <br>      AND (param.key = &quot;value&quot; OR param.key = &quot;board&quot;)</pre><pre>-- ( 1 ) END First piece</pre><pre>)</pre><pre>GROUP BY user_pseudo_id,<br>         Event_time,<br>         event_name</pre><pre>ORDER BY user_pseudo_id,<br>         Event_time</pre><pre>-- ( 2 ) END Second piece</pre><pre>)</pre><pre>GROUP BY user_pseudo_id, <br>         board_type,<br>         score</pre><pre>-- ( 3 ) END Third piece</pre><pre>)</pre><pre>GROUP BY board_type,<br>         score</pre><pre>ORDER BY board_type DESC, <br>         score</pre><pre>-- ( 4 ) END Fourth piece</pre><p><strong>So, let’s take a look at each of the pieces of SQL code step by step.</strong></p><p><strong>(1) First piece of code</strong></p><p>Will give an answer to the question with what result, namely, in how many steps a unique user was able to pass a board of a certain size, using the example of a player … C4CD and his separate party (lines 1 and 2), we see that he was able to pass a board of size S in 18 steps.</p><p>The resulting table is:</p><figure><img alt="how many steps a unique user was able to pass a board of a certain size — bigquery sql" src="https://cdn-images-1.medium.com/max/860/1*Xu1AvVfAQJJ48NRaFXCagQ.jpeg" /><figcaption>First piece of code, QuickPlay End Event</figcaption></figure><p><strong>(2) Secon piece of code</strong></p><p>The second piece of code allows you to group all the games of one player, arrange them by launch time, and most importantly, changes the structure of the table. Now we have the achieved result for each individual game and the type of board placed on one line.</p><p>The resulting table is:</p><figure><img alt="group all the games of one player, arrange them by launch time, and changes the structure of the table — bigquery sql" src="https://cdn-images-1.medium.com/max/600/1*bJgYkpLU7VxBKhPhFQ_s1g.jpeg" /><figcaption>Second piece of code, board type and unique user result</figcaption></figure><p><strong>(3) Third piece of code</strong></p><p>Aggregates the results of a player, how many times and in how many steps he was able to pass a particular board. As an example of a player … C4CD, board S, he walked twice in 18 steps, but in 21 steps he did only 1 time.</p><p>The resulting table is:</p><figure><img alt="Aggregates the results of a player, how many times and in how many steps he was able to pass a particular board — bigquery sql" src="https://cdn-images-1.medium.com/max/435/1*Nr6Nk9NOzKkudN07J0WNXQ.jpeg" /><figcaption>Third piece of code, the sum of the results of a unique player</figcaption></figure><p><strong>(4) Fourth piece of code</strong></p><p>The final table sums up the results. How many times, with what result (in what number of steps) on each type of board S / M / L was in the sum of the launches of the game, and how many, on average, a unique user achieved what results.</p><p>The resulting table is:</p><figure><img alt="final table sums up the results on each type of board S / M / L" src="https://cdn-images-1.medium.com/max/351/1*OEggw1GdNCYuAmUdll_sAQ.png" /><figcaption>Fourth piece of code, summary results for each type of board</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64cca3bda47" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[LTV — lifetime value]]></title>
            <link>https://andy-guliev.medium.com/ltv-lifetime-value-374814a66f65?source=rss-444fdbdeed0f------2</link>
            <guid isPermaLink="false">https://medium.com/p/374814a66f65</guid>
            <category><![CDATA[data-studio-bi]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[bigquery-sql]]></category>
            <category><![CDATA[google-data-studio]]></category>
            <dc:creator><![CDATA[Andy Guliev]]></dc:creator>
            <pubDate>Mon, 22 Mar 2021 07:07:02 GMT</pubDate>
            <atom:updated>2021-03-22T07:07:02.923Z</atom:updated>
            <content:encoded><![CDATA[<h3>LTV — lifetime value</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KCAPWaiesZcpTcNYvXF7Mg.jpeg" /></figure><p>How to understand during what period of time the users of our application will remain active, when interest begins to disappear and it is necessary to start active resuscitation actions?<br>After all, this is already our user, and it is very likely that the user we paid for.</p><p>I propose to take a closer look at those users who have used the application during the last 10 days. Who are they? When they first opened the app and how active they are within the last selected period.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/994/1*7bcilnTP4mWfzsX3oEgLNw.png" /><figcaption>Visualization of data obtained from Bigquery in Google Data Studio</figcaption></figure><p>In the graph above, in the columns we see the number of user sessions, grouped by month and year when the user first opened the application.<br>The line above the columns shows the average number of sessions per user.</p><p>For example, over the past 10 days, the largest number of sessions for users who came a month earlier was 1374 sessions, starting from the second month, user activity begins to actively fall, on the graph we see that the number of sessions of those who first came to us in August is only 445, and then downward. With a green frame, I marked the period of time during which users continue to be active, in our case it is 4 months.</p><blockquote>An example is given on the basis of a public dataset:<br>firebase-public-project.analytics_153293282.events_ *</blockquote><blockquote>Please note that the deadline for this dataset is October 3, 2018</blockquote><p>So, let’s use Bigquery in order to get a table with the data we need, which we will either use as an independent product or build a dashboard similar to the one above on their basis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/340/1*RjDt41-5vD0YqM5zaGNtIQ.png" /></figure><p>Query the onion structure to build the given table:</p><pre>-- ( 3 ) Third piece of code - The sum of sessions and the average <br>--       number of user sessions for the first time who opened the <br>--       application on a certain day</pre><pre>SELECT First_touch_DateTime,<br>       SUM(Sum_) AS Total_Sessions,<br>       AVG(Sum_) AS Avg_Sessions<br>       FROM (</pre><pre>-- ( 2 ) Second piece of code - The sum of sessions of a unique<br>--       user since the date of the first opening</pre><pre>SELECT user_pseudo_id,<br>       First_touch_DateTime,<br>       SUM(COUNT) AS Sum_<br>       FROM (</pre><pre>-- ( 1 ) First piece of code - The number of sessions of a unique <br>--       user on a specific day</pre><pre>SELECT user_pseudo_id,<br>       FORMAT_DATETIME(&#39;%x&#39;,<br>       DATETIME(TIMESTAMP_MICROS<br>       (user_first_touch_timestamp))) AS First_touch_DateTime,<br>       event_date,<br>       event_name,<br>       COUNT(event_name) AS COUNT</pre><pre>FROM `firebase-public-project.analytics_153293282.events_2018*`<br>      WHERE (_TABLE_SUFFIX BETWEEN &#39;0920&#39; AND&#39;1000&#39;)<br>      AND event_name=&#39;session_start&#39;</pre><pre>      GROUP BY user_pseudo_id,<br>      First_touch_DateTime,<br>      event_date,<br>      event_name<br>    )</pre><pre>    GROUP BY user_pseudo_id,<br>    First_touch_DateTime</pre><pre>  )</pre><pre>GROUP BY First_touch_DateTime<br>ORDER BY First_touch_DateTime DESC</pre><p><strong>Let’s analyze each of the request blocks in detail.</strong></p><p><strong>(1) First piece of code</strong></p><p>As a result of executing the first part of the code, we get a table with data for each unique user: the date of the first opening of the application and the amount of sessions within a single day.<br>In total, we have data when the user first opened the application, how often he used it, and the number of sessions by day.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/533/1*cJybGZ_z4hJV-TWpuAdNNw.png" /></figure><p>Code to get this data:</p><pre>-- ( 1 ) First piece of code - The number of sessions of a unique <br>--       user on a specific day</pre><pre>SELECT user_pseudo_id,<br>       FORMAT_DATETIME(&#39;%x&#39;,<br>       DATETIME(TIMESTAMP_MICROS<br>       (user_first_touch_timestamp))) AS First_touch_DateTime,<br>       event_date,<br>       event_name,<br>       COUNT(event_name) AS COUNT</pre><pre>FROM `firebase-public-project.analytics_153293282.events_2018*`<br>      WHERE (_TABLE_SUFFIX BETWEEN &#39;0920&#39; AND&#39;1000&#39;)<br>      AND event_name=&#39;session_start&#39;</pre><pre>GROUP BY user_pseudo_id,<br>      First_touch_DateTime,<br>      event_date,<br>      event_name</pre><p><strong>(2) Second piece of code</strong></p><p>The second part of the code summarizes all the user’s sessions for 10 days (the period we selected).<br>Using the example of user … 5401, line # 4, we see that this user, who first opened our application on August 30, 2018, has opened the application 22 times in the last 10 days.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/394/1*ixuKrCg6eU1JB6y_PylBdQ.jpeg" /></figure><p>Code to get this data:</p><pre>-- ( 2 ) Second piece of code - The sum of sessions of a unique<br>--       user since the date of the first opening</pre><pre>SELECT user_pseudo_id,<br>       First_touch_DateTime,<br>       SUM(COUNT) AS Sum_<br>       FROM (</pre><pre>-- ( 1 ) First piece of code - The number of sessions of a unique <br>--       user on a specific day</pre><pre>SELECT user_pseudo_id,<br>       FORMAT_DATETIME(&#39;%x&#39;,<br>       DATETIME(TIMESTAMP_MICROS<br>       (user_first_touch_timestamp))) AS First_touch_DateTime,<br>       event_date,<br>       event_name,<br>       COUNT(event_name) AS COUNT</pre><pre>FROM `firebase-public-project.analytics_153293282.events_2018*`<br>      WHERE (_TABLE_SUFFIX BETWEEN &#39;0920&#39; AND&#39;1000&#39;)<br>      AND event_name=&#39;session_start&#39;</pre><pre>GROUP BY user_pseudo_id,<br>      First_touch_DateTime,<br>      event_date,<br>      event_name<br>    )</pre><pre>GROUP BY user_pseudo_id,<br>    First_touch_DateTime</pre><p><strong>(3) Third piece of code</strong></p><p>And finally, the third piece of code wraps all previously run queries, the final query groups the activity of users for the last 10 days by the date of the first opening.<br>For example, line # 1 — total users who first opened the application on December 31, 2017 year. Over the past 10 days, these users have made 27 sessions, with an average of 3.8 sessions per user.<br>A very loyal group of users), despite the fact that they almost joined us<br>A year ago, they still actively use the application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/339/1*stABPndV8UEU3Bye8roYiw.jpeg" /></figure><p>The data is grouped by day, which is quite a suitable option if we use it to build a dashboard in Google Data Studio, where we will group the data by month. If you do not plan to build graphs and tabular data, it will be enough for you, then I would recommend avoiding unnecessary detail, and grouping the date of the first opening in a month and a year at once in the table. To do this, you just need to replace the date formatting code in the first part of the code:</p><pre>FORMAT_DATETIME(&#39;%x&#39;,<br>                DATETIME(TIMESTAMP_MICROS(<br>                user_first_touch_timestamp)))<br>                AS First_touch_DateTime</pre><p>Change <strong><em>‘% x’</em></strong> to <strong><em>‘% Y% B’</em></strong> the date formatter now looks like:</p><pre>FORMAT_DATETIME(&#39;%Y %B&#39;,<br>                DATETIME(TIMESTAMP_MICROS(<br>                user_first_touch_timestamp)))<br>                AS First_touch_DateTime</pre><p>As a result, we get the data in the form, the right image:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*LqPZbnJC04aKCzwHDKcQiw.jpeg" /></figure><p>Full code to get this data:</p><pre>-- ( 3 ) Third piece of code - The sum of sessions and the average <br>--       number of user sessions for the first time who opened the <br>--       application on a certain day</pre><pre>SELECT First_touch_DateTime,<br>       SUM(Sum_) AS Total_Sessions,<br>       AVG(Sum_) AS Avg_Sessions<br>       FROM (</pre><pre>-- ( 2 ) Second piece of code - The sum of sessions of a unique<br>--       user since the date of the first opening</pre><pre>SELECT user_pseudo_id,<br>       First_touch_DateTime,<br>       SUM(COUNT) AS Sum_<br>       FROM (</pre><pre>-- ( 1 ) First piece of code - The number of sessions of a unique <br>--       user on a specific day</pre><pre>SELECT user_pseudo_id,<br>       FORMAT_DATETIME(&#39;%x&#39;,<br>       DATETIME(TIMESTAMP_MICROS<br>       (user_first_touch_timestamp))) AS First_touch_DateTime,<br>       event_date,<br>       event_name,<br>       COUNT(event_name) AS COUNT</pre><pre>FROM `firebase-public-project.analytics_153293282.events_2018*`<br>      WHERE (_TABLE_SUFFIX BETWEEN &#39;0920&#39; AND&#39;1000&#39;)<br>      AND event_name=&#39;session_start&#39;</pre><pre>GROUP BY user_pseudo_id,<br>      First_touch_DateTime,<br>      event_date,<br>      event_name<br>    )</pre><pre>GROUP BY user_pseudo_id,<br>    First_touch_DateTime</pre><pre>)</pre><pre>GROUP BY First_touch_DateTime<br>ORDER BY First_touch_DateTime DESC</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=374814a66f65" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BIGQUERY — Flow of events (CJM)]]></title>
            <link>https://andy-guliev.medium.com/bigquery-flow-of-events-cjm-fabedae009ad?source=rss-444fdbdeed0f------2</link>
            <guid isPermaLink="false">https://medium.com/p/fabedae009ad</guid>
            <category><![CDATA[bigquery-sql]]></category>
            <category><![CDATA[google-cloud-sql]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Andy Guliev]]></dc:creator>
            <pubDate>Sun, 14 Mar 2021 16:51:02 GMT</pubDate>
            <atom:updated>2021-03-14T16:51:02.426Z</atom:updated>
            <content:encoded><![CDATA[<h3>BIGQUERY — Flow of events (CJM)</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ESeMmtYfV1pfYljowFac5w.jpeg" /></figure><p>There are a lot of questions related to the chain of events accompanying the user’s path through the site / application.<br>In particular:<br>- what is the user path (CJM) when interacting with the site / application?<br>- how much time passes between the first user event and the last one, or any<br> others interesting events for us?<br>- the first / last event in the user’s path.<br> <br>How to get an answer to them using Bigquery, I will describe below in several parts.</p><p><strong>Part one - CJM user, by the minute.</strong></p><p>As a result of executing the query, we will get the following table.<br>Within the framework of a single user, we see the events accompanying him through the site, the <strong><em>event_name</em></strong> field and the <strong><em>time</em></strong> when the event occurred in the Time column with details to the second.<br>So, the first user event … 5AB1 is screen_view committed at 13: 32.19<br>the last event is also a screen-view, but already committed at 13:33:55.</p><p>In total, when interacting with the site / application, 14 events occurred.<br>15 events if you pay attention already belongs to another user.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/531/1*aSWEPHfmK0izTDjCA4bAOQ.jpeg" /></figure><p>request code:</p><pre>SELECT user_pseudo_id,<br>       event_name,   <br>       FORMAT_TIME(<br>                   &#39;%T&#39;,TIME(TIMESTAMP_MICROS(event_timestamp))<br>                   ) AS Time</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         event_timestamp</pre><pre>ORDER BY user_pseudo_id,<br>         event_timestamp</pre><p>The table obtained as a result of this query, a kind of framework in order to understand the logic of building a query, and based on this, go further in order to get answers to deeper questions.<br>For example,<em> “how much time passes between the two nearest events in CJM”</em>.</p><p><strong>Part two. The time difference between the nearest events.</strong></p><p>In order to be able to calculate the time difference between the current event and the next one in time, we add two columns <strong><em>Next_event</em></strong> &amp; <strong><em>Next_time</em></strong>, and we get the opportunity to calculate the time difference between two consecutive events. Сalculate the difference and place it in the <strong>Diff_tim<em>e</em></strong> column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/831/1*PlhlOUl65QFQFnG5mVaCOA.jpeg" /></figure><p>The query that generates the table has an onion structure:<br>- <strong>The first block</strong> - is the flow of user events, the same basic table that <br> was considered earlier.<br>- <strong>The second block</strong> - adds fields describing the next event in the chain of <br> events.<br>- <strong>The third block</strong> - we calculate the difference in seconds between the two <br> nearest events.<br>- <strong>The fourth block</strong> - we format the time in a readable format.</p><pre>-- ( 4 ) <strong>The fourth block</strong> - Format time</pre><pre>SELECT user_pseudo_id,<br>       event_name,<br>       FORMAT_TIME(&#39;%T&#39;,TIME) AS Time,<br>       Next_event,<br>       FORMAT_TIME(&#39;%T&#39;,Next_time ) AS Next_time,<br>       Diff_Time<br>       FROM (</pre><pre>-- ( 3 ) <strong>The third block</strong> - Difference in time</pre><pre>SELECT *,<br>       TIME_DIFF(Next_time, Time, SECOND) AS Diff_Time<br>       FROM (</pre><pre>-- ( 2 ) <strong>The second block</strong> - Next Events &amp; Time</pre><pre>SELECT *,<br>       LEAD(event_name) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_event,<br>       LEAD(Time) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_time <br>       FROM(</pre><pre>-- ( 1 ) <strong>The first block</strong> - Events flow</pre><pre>SELECT user_pseudo_id,<br>       event_name, <br>       TIME(TIMESTAMP_MICROS(event_timestamp)) AS Time</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time<br>    )</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time<br>    )<br>)</pre><p>So, let’s analyze the result of executing each of the blocks of sql code.</p><p><strong>The first block</strong> - the flow of user events, in fact the same base table with which we began our meeting, the only difference is that the time is not in a human readable format. There is only one reason if we are now let’s format the time, later we won’t be able to calculate the time difference.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/520/1*P4bbBXsIKjUVRJD_va_8Rg.jpeg" /></figure><pre>-- ( 1 ) <strong>The first block</strong> - Events flow</pre><pre>SELECT user_pseudo_id,<br>       event_name, <br>       TIME(TIMESTAMP_MICROS(event_timestamp)) AS Time</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time</pre><p><strong>The second block </strong>- adds columns describing the next event in the <strong><em>Next_event</em></strong> &amp; <strong><em>Next_time</em></strong> chain of events</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/851/1*9fJ6HDoeKM5NSh4fc2Hgfg.jpeg" /></figure><pre>-- ( 2 ) <strong>The second block</strong> - Next Events &amp; Time</pre><pre>SELECT *,<br>       LEAD(event_name) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_event,<br>       LEAD(Time) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_time <br>       FROM(</pre><pre>-- ( 1 ) <strong>The first block</strong> - Events flow</pre><pre>SELECT user_pseudo_id,<br>       event_name, <br>       TIME(TIMESTAMP_MICROS(event_timestamp)) AS Time</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time<br>    )</pre><p><strong>The third block </strong>-<strong> </strong>adds a <strong><em>Diff_Time</em></strong> column with the difference in seconds between the two closest events.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/918/1*00YazNmGLCYC6STp9Von2A.jpeg" /></figure><pre>-- ( 3 ) <strong>The third block</strong> - Difference in time</pre><pre>SELECT *,<br>       TIME_DIFF(Next_time, Time, SECOND) AS Diff_Time<br>       FROM (</pre><pre>-- ( 2 ) <strong>The second block</strong> - Next Events &amp; Time</pre><pre>SELECT *,<br>       LEAD(event_name) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_event,<br>       LEAD(Time) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_time <br>       FROM(</pre><pre>-- ( 1 ) <strong>The first block</strong> - Events flow</pre><pre>SELECT user_pseudo_id,<br>       event_name, <br>       TIME(TIMESTAMP_MICROS(event_timestamp)) AS Time</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time<br>    )</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time<br>    )</pre><p><strong>The fourth block</strong> - formatting the time into a readable format.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/831/1*0_JFExBOGBCqsZLRGwP0sA.jpeg" /></figure><pre>-- ( 4 ) <strong>The fourth block</strong> - Format time</pre><pre>SELECT user_pseudo_id,<br>       event_name,<br>       FORMAT_TIME(&#39;%T&#39;,TIME) AS Time,<br>       Next_event,<br>       FORMAT_TIME(&#39;%T&#39;,Next_time ) AS Next_time,<br>       Diff_Time<br>       FROM (</pre><pre>-- ( 3 ) <strong>The third block</strong> - Difference in time</pre><pre>SELECT *,<br>       TIME_DIFF(Next_time, Time, SECOND) AS Diff_Time<br>       FROM (</pre><pre>-- ( 2 ) <strong>The second block</strong> - Next Events &amp; Time</pre><pre>SELECT *,<br>       LEAD(event_name) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_event,<br>       LEAD(Time) OVER (PARTITION BY user_pseudo_id<br>                              ORDER BY Time) AS Next_time <br>       FROM(</pre><pre>-- ( 1 ) <strong>The first block</strong> - Events flow</pre><pre>SELECT user_pseudo_id,<br>       event_name, <br>       TIME(TIMESTAMP_MICROS(event_timestamp)) AS Time</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time<br>    )</pre><pre>GROUP BY user_pseudo_id,<br>         event_name,<br>         Time</pre><pre>ORDER BY user_pseudo_id,<br>         Time<br>    )<br>)</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fabedae009ad" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BIGQUERY — Count events]]></title>
            <link>https://andy-guliev.medium.com/bigquery-count-events-4052f7e0f0f2?source=rss-444fdbdeed0f------2</link>
            <guid isPermaLink="false">https://medium.com/p/4052f7e0f0f2</guid>
            <category><![CDATA[bigquery-sql]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-big-query]]></category>
            <dc:creator><![CDATA[Andy Guliev]]></dc:creator>
            <pubDate>Sat, 13 Mar 2021 06:27:05 GMT</pubDate>
            <atom:updated>2021-03-13T07:52:33.255Z</atom:updated>
            <content:encoded><![CDATA[<h3>BIGQUERY — Count events</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N117tlDdW5-dG6AkvbqHhg.jpeg" /></figure><p>How to quickly and most importantly simply count the number of events and, if necessary, the conversion between them in Bigquery? <br>And as a result, get such a wonderful table, with the sum of each of the events!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/345/1*gCvIyquyHj_7M0bXx4bh9Q.png" /></figure><p>extremely simple piece of code, onion structure:</p><pre>-- ( 3 ) Third piece of code</pre><pre>SELECT <br>    *,<br>    end_quickplay/start_quickplay AS CR_to_end_quickPlay<br>    FROM (</pre><pre>-- ( 2 ) Second piece of code</pre><pre>SELECT<br>    SUM(start_quickplay) AS start_quickplay,<br>    SUM(end_quickplay) AS end_quickplay<br>    FROM(</pre><pre>-- ( 1 ) First piece of code</pre><pre>SELECT<br>    user_pseudo_id,<br>    event_name,<br>    IF(event_name=&#39;level_start_quickplay&#39;, 1, 0) AS start_quickplay,<br>    IF(event_name=&#39;level_end_quickplay&#39;, 1, 0) AS end_quickplay</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`<br>     WHERE event_name=&#39;level_start_quickplay&#39; OR     <br>           event_name=&#39;level_end_quickplay&#39;</pre><pre>GROUP BY user_pseudo_id,<br>         event_name</pre><pre>  )<br>)</pre><p>( 1 ) <strong>First piece of code</strong></p><p>The first part of the code, here, depending on what event happened, put a “tick” in the appropriate column. For example, if the level_start_quickplay event has occurred, put ‘1’ in the column with the name below is the result obtained as a result of the work of this part of the code.</p><pre>SELECT<br>    user_pseudo_id,<br>    event_name,<br>    IF(event_name=&#39;level_start_quickplay&#39;, 1, 0) AS start_quickplay,<br>    IF(event_name=&#39;level_end_quickplay&#39;, 1, 0) AS end_quickplay</pre><pre>FROM `firebase-public-project.analytics_153293282.events_20181003`<br>     WHERE event_name=&#39;level_start_quickplay&#39; OR     <br>           event_name=&#39;level_end_quickplay&#39;</pre><pre>GROUP BY user_pseudo_id,<br>         event_name</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/569/1*EYJ3vDeTf-iPjiIT9nQeQg.png" /></figure><p>( 2 ) <strong>Second piece of code</strong></p><p>This part of the code calculates the sum of each of the events</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/216/1*o13Xvw5EECVk8jrpSQISmw.png" /></figure><p>( 3 ) <strong>Third piece of code</strong></p><p>And finally, the final part of the code, which wraps all the previous parts, is used to calculate the conversion. In this case, we are talking about the conversion from the beginning of the Quickplay (event — start_quickplay ) to the end (event — end_quickplay).</p><p>In our case, 85% of those who started finished the game.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/345/1*gCvIyquyHj_7M0bXx4bh9Q.png" /></figure><blockquote>An example is given on the basis of a public dataset:<br>firebase-public-project.analytics_153293282.events_*</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4052f7e0f0f2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>