SQL queries for Adobe Analytics Datafeed data

Question

I have extracted the datafeed for a single day and imported it into a single SQL table. I have successfully created SQL queries to calculate Unique Visitors and Visits, as shown below and its exactly matching with workspace data.

Unique Visitors: SELECT COUNT(distinct concat(post_visid_high, post_visid_low)) from where visit_page_num!=0 and hit_source not in (5,7,8,9)

Visits: SELECT COUNT(distinct CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) FROM
where visit_page_num!=0 and hit_source not in (5,7,8,9)

Could someone please share similar SQL queries for calculating the following metrics?

1. Page Views
2. Bounce
3. Bounce Rate
4. New User
5. Repeated User
5. Average time on site
6. Average time per page
7. Entries
8. Exits

MandyGeorge · Accepted Answer

Here are a couple references with some of the metrics you're looking for.

Calculate metrics | Adobe Analytics

Sample SQL queries to query Adobe analytics Data f... - Adobe Experience League Community - 588992

For page views you would add "visit_page_num" to your visit calculation.

For bounces, you want to count the number of visits with a single hit (a single visit_page_num). Or if you want visits that only saw one page (in case you have other calls that fire as well, such as cookie banners), you can count the number of visits that have a single page.

For new visitors, visit_num will be 1, returning visitors will have a visit_num of 2 or more.

Jennifer_Dungan · Answer

While I don't generally work with the SQL directly, I do often work closely with our data team.

One thing that you will definitely need to account for in all your queries is the exclude_hit flag. Raw Data columns are just that, every single raw hit, including everything that has been processed out by Adobe (bot traffic, internal traffic, badly formed data, etc)... if you don't account for this, your results from SQL will be inflated and never match your workspaces.

If you want to follow how Adobe calculates Page Views, basically, any hit that has a pagename or page_url (on the post versions). Even though you will see this data being sent to Adobe during tracking, they are cleared during processing. While there is a page_event field, this mostly lines up with your page views, but it is not 100%. I believe my team uses page_event = 0, and it's close, but sometimes doesn't quite line up... so the easy solution is to just use it, but if things don't line up, this is why.

Bounce is an interesting one, if you are trying to replicate Adobe's definition of a "Bounce" which is a single hit in a visit... or if you are trying to do a more traditional definition of a bounce such as a single page view in a visit. Using the Visitor Id High and Low (to identify users), with the visit_num (the visit counter) and Hit Id High and Low (to identify the Hit), you should be able to find the number of visits, the number of visits that have a single hit, and if you are trying to do Single Page Views, add the above identification to limit yourself to just looking at Page Views. Bounce Rate is an extension of this taking the bounces divided by the visits.

New and Repeated Visitors, if you are looking at "all time" metrics (similar to Adobe's pre-defined segments), as Mandy mentioned, it simply looking at the visit_num. This is an all time value that continues to count for the visitor's lifespan. However, if you are interested in something like a 30 day new and repeat, you will need to get more complex, looking at the users who have a visit in the current month, and haven't had a visit within your specified time frame or are on their very first visit, vs those that have been to your site within the specified time.

Time on Site and Time on Page are quite frustrating, basically you have to find all your page time stamps, for each user, in each visit, and subtract the difference. So time on page A: take timestamp of Page B and subtract the timestamp of Page A... for the site, take the timestamp of Page N and subtract the timestamp of Page A. Note that the last page of the visit won't have a time associated to it, as there is no "next page view"... and while you might have a timestamp of an Exit Click or maybe a Mobile App Lifecycle close, I don't think that Adobe will take an Exit Click into account (because that may have opened in a new window, leaving you on the site)...I don't know if it takes Lifecycle metrics into account, those are a little more reasonable to trust....

Entries and Exits, these should be the first and last hits for a Visit. Which if you did the above, you should already have the visit identified, and this part should be easy.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded