Analytics & Insights

User Session Analysis in Tellius and Data Preparation using SQL – Part 1: Data Transformation

hero-shape-left hero-shape-right

In many analytics use cases, event data often plays a key role. This data may be a user’s interaction with an app on mobile or their interaction with a web application. This data is very valuable to understand the series of events a user has traversed in a given period of time.

One of the analyses we can do on this data is user session analysis. A session is a period of time the user comes to an application, spends some time in an application and moves away. This session analysis often helps to understand the various activities users will be doing in a  short period of time.

In this series of blogs, we will discuss how Tellius helps users to perform session analysis on their event data. This is first part in which we will discuss about the use case and how to start with SQL in Tellius Platform

Sample Data

The above data shows the sample data of activities. Here event_at signifies the time at which an event has occurred. Other fields signify the user and activity information.

As we can see from the data, there is no obvious way to say which session any given event belongs to. Before we can perform the session analysis, we need to determine which session a given event belongs to. 

This we can achieve using a little bit of ETL on data. 

ETL in Tellius

ETL is part of any data analysis pipeline. For the above data, we cannot ask the data team to update their schema to include session indicator as it’s specific to our analysis. Unlike many BI tools out there, Tellius has a full fledged ETL built in to the tool, which allows the user to perform use case specific ETL on the data without touching any of the source data.

Tellius supports point and click ETL for simple transformations

Tellius also supports advanced transformations using SQL and Python

In this post, we will be discussing how to use SQL of Tellius to figure out the sessions in our event data.

Figuring out when Session Starts

First we need to figure out when  a session starts. We can figure this out using the LAG function of SQL to calculate the same.

select * , LAG(event_at,1) OVER

(PARTITION BY user_id order by event_at) AS

last_event

from data

 

The above code figures out the last event time and adds a new column to the dataset. This column will be only added to Tellius data, and not to the original table. By not touching the original table, Tellius users are free to any changes to the data without worrying about the integrity of the source data and effecting the downstream systems. 

Now the data will look as below

 

Now we have figured out when the session starts and added it as a new column. As the next step we need to add additional code to figure out the unique sessions. We will discuss it in the next post.

share

Leave reply

Read Similar Posts

  • BI & Data Science: Two Sides of the Same Coin
    Analytics & Insights

    BI & Data Science: Two Sides of the Same Coin

    Tellius offers a robust machine learning layer where users can train, assess, and apply predictive models. Read the advanced approach to customer segmentation based on an unsupervised machine learning clustering model in Tellius.

    Tellius
  • Tellius Recognized in 2022 Gartner® Market Guide for Multipersona Data Science and Machine Learning Platforms
    Analytics & Insights

    Tellius Recognized in 2022 Gartner® Market Guide for Multipersona Data Science and Machine Learning Platforms

    We’re excited to share that Tellius has been recognized as a Representative Vendor in the 2022 Gartner Market Guide for Multipersona Data Science and Machine Learning Platforms (DSML) – just a month after being recognized as a Visionary in the Gartner Magic QuadrantTM for Analytics & Business Intelligence Platforms!

    Tellius
  • Gartner Magic Quadrant Report 2022: Tellius named in Top 20 Visionary Analytics Software Vendors
    Analytics & Insights

    Gartner Magic Quadrant Report 2022: Tellius named in Top 20 Visionary Analytics Software Vendors

    Tellius was recently named a Visionary in the Gartner® Magic Quadrant™ for Analytics & BI Platforms. We believe we were named a Visionary because of our vision of building a unified platform that enables anyone, regardless of their analytical skills, to get insights into “what” is happening, “why” metrics changed, and “how” to improve business outcomes at cloud scale.

    Tellius