LTV — lifetime value

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?
After all, this is already our user, and it is very likely that the user we paid for.

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.

Visualization of data obtained from Bigquery in Google Data Studio

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.
The line above the columns shows the average number of sessions per user.

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.

An example is given on the basis of a public dataset:
firebase-public-project.analytics_153293282.events_ *

Please note that the deadline for this dataset is October 3, 2018

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.

Query the onion structure to build the given table:

-- ( 3 ) Third piece of code - The sum of sessions and the average 
--       number of user sessions for the first time who opened the 
--       application on a certain day
SELECT First_touch_DateTime,
       SUM(Sum_) AS Total_Sessions,
       AVG(Sum_) AS Avg_Sessions
       FROM (
-- ( 2 ) Second piece of code - The sum of sessions of a unique
--       user since the date of the first opening
SELECT user_pseudo_id,
       First_touch_DateTime,
       SUM(COUNT) AS Sum_
       FROM (
-- ( 1 ) First piece of code - The number of sessions of a unique 
--       user on a specific day
SELECT user_pseudo_id,
       FORMAT_DATETIME('%x',
       DATETIME(TIMESTAMP_MICROS
       (user_first_touch_timestamp))) AS First_touch_DateTime,
       event_date,
       event_name,
       COUNT(event_name) AS COUNT
FROM `firebase-public-project.analytics_153293282.events_2018*`
      WHERE (_TABLE_SUFFIX BETWEEN '0920' AND'1000')
      AND event_name='session_start'
      GROUP BY user_pseudo_id,
      First_touch_DateTime,
      event_date,
      event_name
    )
    GROUP BY user_pseudo_id,
    First_touch_DateTime
  )
GROUP BY First_touch_DateTime
ORDER BY First_touch_DateTime DESC

Let’s analyze each of the request blocks in detail.

(1) First piece of code

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.
In total, we have data when the user first opened the application, how often he used it, and the number of sessions by day.

Code to get this data:

-- ( 1 ) First piece of code - The number of sessions of a unique 
--       user on a specific day
SELECT user_pseudo_id,
       FORMAT_DATETIME('%x',
       DATETIME(TIMESTAMP_MICROS
       (user_first_touch_timestamp))) AS First_touch_DateTime,
       event_date,
       event_name,
       COUNT(event_name) AS COUNT
FROM `firebase-public-project.analytics_153293282.events_2018*`
      WHERE (_TABLE_SUFFIX BETWEEN '0920' AND'1000')
      AND event_name='session_start'
GROUP BY user_pseudo_id,
      First_touch_DateTime,
      event_date,
      event_name

(2) Second piece of code

The second part of the code summarizes all the user’s sessions for 10 days (the period we selected).
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.

Code to get this data:

-- ( 2 ) Second piece of code - The sum of sessions of a unique
--       user since the date of the first opening
SELECT user_pseudo_id,
       First_touch_DateTime,
       SUM(COUNT) AS Sum_
       FROM (
-- ( 1 ) First piece of code - The number of sessions of a unique 
--       user on a specific day
SELECT user_pseudo_id,
       FORMAT_DATETIME('%x',
       DATETIME(TIMESTAMP_MICROS
       (user_first_touch_timestamp))) AS First_touch_DateTime,
       event_date,
       event_name,
       COUNT(event_name) AS COUNT
FROM `firebase-public-project.analytics_153293282.events_2018*`
      WHERE (_TABLE_SUFFIX BETWEEN '0920' AND'1000')
      AND event_name='session_start'
GROUP BY user_pseudo_id,
      First_touch_DateTime,
      event_date,
      event_name
    )
GROUP BY user_pseudo_id,
    First_touch_DateTime

(3) Third piece of code

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.
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.
A very loyal group of users), despite the fact that they almost joined us
A year ago, they still actively use the application.

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:

FORMAT_DATETIME('%x',
                DATETIME(TIMESTAMP_MICROS(
                user_first_touch_timestamp)))
                AS First_touch_DateTime

Change ‘% x’ to ‘% Y% B’ the date formatter now looks like:

FORMAT_DATETIME('%Y %B',
                DATETIME(TIMESTAMP_MICROS(
                user_first_touch_timestamp)))
                AS First_touch_DateTime

As a result, we get the data in the form, the right image:

Full code to get this data:

-- ( 3 ) Third piece of code - The sum of sessions and the average 
--       number of user sessions for the first time who opened the 
--       application on a certain day
SELECT First_touch_DateTime,
       SUM(Sum_) AS Total_Sessions,
       AVG(Sum_) AS Avg_Sessions
       FROM (
-- ( 2 ) Second piece of code - The sum of sessions of a unique
--       user since the date of the first opening
SELECT user_pseudo_id,
       First_touch_DateTime,
       SUM(COUNT) AS Sum_
       FROM (
-- ( 1 ) First piece of code - The number of sessions of a unique 
--       user on a specific day
SELECT user_pseudo_id,
       FORMAT_DATETIME('%x',
       DATETIME(TIMESTAMP_MICROS
       (user_first_touch_timestamp))) AS First_touch_DateTime,
       event_date,
       event_name,
       COUNT(event_name) AS COUNT
FROM `firebase-public-project.analytics_153293282.events_2018*`
      WHERE (_TABLE_SUFFIX BETWEEN '0920' AND'1000')
      AND event_name='session_start'
GROUP BY user_pseudo_id,
      First_touch_DateTime,
      event_date,
      event_name
    )
GROUP BY user_pseudo_id,
    First_touch_DateTime
)
GROUP BY First_touch_DateTime
ORDER BY First_touch_DateTime DESC