In this detailed discussion, we will explore how to use Adobe Analytics data feeds to calculate metrics accurately. The official documentation can sometimes lack clarity, but I will provide clear guidance, step-by-step instructions, and practical examples to help you effectively compute metrics within Adobe Analytics. By the end of this exploration, you will have a solid understanding of how to leverage these data feeds for precise metric analysis. Let’s dive in!

1.Page Views :
Page views is a metric in Adobe Analytics that measures the number of times a webpage has been viewed or loaded by a unique visitor during a specific time period.
Definition : Count the number of rows where a value is in post_pagename or post_page_url.

SELECT COUNT(CASE WHEN post_pagename != '' OR post_page_url != '' THEN 1 ELSE NULL END) AS "Page Views"
FROM adobe_data_feed

2. Unique Visitors :
A unique visitor is a term used in marketing analytics which refers to a person who has visited the website at least once and is counted only once in the reporting time period.
Definition : Unique count of combination of post_visid_high and post_visid_low.

SELECT COUNT(DISTINCT CONCAT(post_visid_high,post_visid_low) AS "Unique Visitors"
FROM adobe_data_feed

3. New Visitors :
New visitors are those accessing your site for the first time on a specific device. They offer opportunities for engagement and insights into audience diversity.
Definition : Count of users where visit_num = 1.

SELECT COUNT(DISTINCT CASE WHEN visit_num = 1 THEN CONCAT(post_visid_high,post_visid_low) ELSE NULL END) AS "New Visitors"
FROM adobe_data_feed

4. Returning Visitors :
Returning visitors are defined as individuals who have visited your website at least once before and have made a subsequent visit. They signify a segment of your audience that engages with your content on a recurring basis, providing crucial data on your website’s effectiveness.
Definition : Count of users where visit_num > 1.

SELECT COUNT(DISTINCT CASE WHEN visit_num > 1 THEN CONCAT(post_visid_high,post_visid_low) ELSE NULL END) AS "Returning Visitors"
FROM adobe_data_feed

5. Visits :
The Visits metric indicates the total number of sessions across all visitors on your website. It provides a measure of overall traffic and engagement, reflecting the volume of interactions occurring within a specific timeframe.
Definition : Unique count of combination of post_visid_high, post_visid_low, visit_num, and visit_start_time_gmt.

SELECT COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) AS "Total Visits"
FROM adobe_data_feed

6. Pages / Visit :
It represents the average number of pages viewed by a visitor during a single session on your website. This metric provides insights into visitor engagement and the depth of interaction with your content.
Definition : Ratio of Total page views and total visits.

SELECT COUNT(CASE WHEN post_pagename != '' OR post_page_url != '' THEN 1 ELSE NULL END)/COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) AS "Pages/Visit"
FROM adobe_data_feed

7. Bounces :
It refers to the number of visits where the users enter a website but don’t click on any other pages before exiting. It’s a metric indicating the percentage of single-page visits.
Definition : Count of hit where the visit_page_num = 1 for each visit.

WITH cte AS(
SELECT SUM(visit_page_num) OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt) ORDER BY visit_page_num DESC) AS bounce
FROM adobe_data_feed
) 
SELECT COUNT(CASE WHEN bounce = 1 THEN 1 ELSE NULL END) AS "Bounces"
FROM cte

8. Bounce Rate :
Percentage of visitors who land on a webpage and then leave without interacting further with the site or viewing other pages within the same site.
Definition : Ratio of number of bounces and the total visits.

WITH cte AS(
SELECT post_visid_high, post_visid_low, visit_num, visit_start_time_gmt,
SUM(visit_page_num) OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt) ORDER BY visit_page_num DESC) AS bounce
FROM adobe_data_feed
) 
SELECT COUNT(CASE WHEN bounce = 1 THEN 1 ELSE NULL END)/COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) AS "Bounce Rate"
FROM cte

*Note : Bounce is the flag which describes whether a visit was single page visit (bounce = 1).

9. Total Time Spent on Site :
Time on site, also known as session duration, is the total amount of time that someone spends navigating through your website.
Definition : Sum of Difference of post_cust_hit_time_gmt and visit_start_time_gmt of the last hit of a visit for the all the visits.

WITH cte AS(
SELECT visit_start_time_gmt, post_cust_hit_time_gmt,
ROW_NUMBER() OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt) ORDER BY visit_page_num DESC) AS hit_number
FROM adobe_data_feed
)
SELECT SUM(CASE WHEN hit_number = 1 THEN (post_cust_hit_time_gmt - visit_start_time_gmt) ELSE NULL END) AS "Total Time Spent"
FROM cte

*Note : hit_number gives the order of hits for a visit, hit_number = 1 denoting the last hit for that visit.

10. Average Session Duration :
It is a metric used in web analytics to measure the average length of time a user spends on a website during a single session.
Definition : Ratio of total time spent on site and total engaged sessions.

WITH cte AS(
SELECT visit_start_time_gmt, post_cust_hit_time_gmt, post_visid_high, post_visid_low, visit_num,
SUM(visit_page_num) OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt) ORDER BY visit_page_num DESC) AS bounce,
ROW_NUMBER() OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt) ORDER BY visit_page_num DESC) AS hit_number
FROM adobe_data_feed
)
SELECT SUM(CASE WHEN hit_number = 1 THEN (post_cust_hit_time_gmt - visit_start_time_gmt) ELSE NULL END)/(COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) - COUNT(CASE WHEN bounce = 1 THEN 1 ELSE NULL END))  AS "Average Session Duration"
FROM cte

*Note : Formula for Average Session Duration = Total Time Spent / (Total Visits - Bounces), bounce and hit_number are utilized the same way as used above.

I hope that the insights provided in this blog have been useful for understanding how to precisely compute different metrics using Adobe Analytics data feed. Each metric represents a meaningful aspect of user interaction on your digital platform. By mastering these calculations, you gain deeper insights into your audience’s engagement story that drive informed decision-making.