Insights 202: Web Analytics
Web analytics refers to the measurement, collection, analysis, and reporting of data related to website or web application usage.
Prerequisites
You need to make sure you complete this module and its prerequisites:
Scenario
We are going to ingest LUMA data into our test environment. This is a fictitious online store created by Adobe
The fastest way to understand what is happening on the website is to check the Products tab. There are 3 categories of products for different (and all) personas. You can browse them. You authenticate yourself and also can add items to a cart. The data that we are ingesting into the Platform is the test website traffic data that conforms to the Adobe Analytics schema.
We need to run some analytical queries on this dataset.
Count the Number of Events in the AA Dataset
The answer should be 733,265. This is also the web traffic volume.
Count of Visitors and Authenticated Visitors
The answer you should get for both should be 30,000. This means that every cookie is associated with an email which at first instance should come across as strange. But this is demo data and we can assume that someone has done the ID resolution for us for ALL mcids.
Time Range of the Data
The time range should come as 2020-06-30 22:04:47 to 2021-01-29 23:47:04
Most Popular Web Pages
Count the Number of Visits/Sessions
One of the foundational concepts of web analytics is the idea of a session or a visit. When you visit a website, a timer starts ticking and all the pages that you visited, say in the next 30 minutes are part of that session. Sessions are great because they are the atomic unit of a journey. Customers interact with a channel or a medium as part of a session. What they do in the session has some intent or goal - if we can study what happens in these sessions, then we can get a solid understanding of the users.
Let us understand the code first:
to_json(SESS_TIMEOUT(Timestamp, 60 * 30)
: Here, theSESS_TIMEOUT
function is used with theTimestamp
column. This function calculates the session timeout by adding 30 minutes (60 * 30 seconds) to the givenTimestamp
. The result is then converted to a JSON format using theto_json
function.OVER (PARTITION BY mcid_id ORDER BY Timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
: This is a window function that operates on partitions of data defined by themcid_id
column. It orders the rows within each partition based on theTimestamp
column in ascending order. TheROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
clause specifies that the window includes all rows from the beginning of the partition up to the current row.
The result is the following:
Let us now parse the results in the session object:
If you look at the mcid_id column, all of those ids are sorted by the same person. The sessionization always operates on a single mcid_id
timestamp_diff:
The difference in time, in seconds, between the current record and the prior record. It starts with "0" for the first record and increases for the other records within the same session as indicated bydepth.
num:
A unique session number, starting at 1 for each mcid_id.isnew
is just a flag as to whether the record is the start of a new session or not.
I can now extract the session number at a visitor level and also assign it a unique session number across all visitors by doing the following:
Warning: I have removed to_json
in the code here as I need to access the fields within the session object. If I use to_json
, it will create a string and the fields cannot be extracted.
The results are the following:
Let us compute the number of visits overall:
The result should be 104,721.
The average number of pages visited per visit is 733,265/104,721=7. This does agree with what we see when we inspect the results.
Last updated