Metrics Downstream Questions | Community
Skip to main content
skatofiabah
skatofiabah
Level 5
September 17, 2024
Solved

Metrics Downstream Questions

  • September 17, 2024
  • 1 reply
  • 1237 views

Hi Everyone,

 

I wanted to see if anybody has any SQL code handy for Adobe Downstream data for the following metrics? I am only able to find the basic ones here: https://experienceleague.adobe.com/en/docs/analytics/export/analytics-data-feed/data-feed-contents/datafeeds-calculate

and here: https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-blogs/sample-sql-queries-to-query-adobe-analytics-data-feeds/ba-p/588992

 

  • Visits
  • Visits by Referrer and Marketing Channel
  • # of repeat visits (and/or repeat visitors)
  • Avg. Time On Page
  • Avg. Pages Per Visit

 

Thanks!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Krishna_Musku

select "year_of_calendar",
"month_of_year",
"month_short_name",
"page_name",
"Unique_Visit_Count",
"PAGEVIEWCOUNT",
"TIMEONPAGE",
page_view_count/unique_visit_count as average_page_views_per_visit,
(timeonpage - coalesce(TIMEONPAGEBOUNCES))/(Unique_Visit_Count - coalesce(BOUNCES)) as average_time_spent_per_visit
from pre_data

 

@skatofiabah Try the above, This calculates visit counts, time on page, average page views per visit and average time spent per visit at the page level.

1 reply

Krishna_Musku
Community Advisor
Krishna_MuskuCommunity AdvisorAccepted solution
Community Advisor
September 17, 2024

select "year_of_calendar",
"month_of_year",
"month_short_name",
"page_name",
"Unique_Visit_Count",
"PAGEVIEWCOUNT",
"TIMEONPAGE",
page_view_count/unique_visit_count as average_page_views_per_visit,
(timeonpage - coalesce(TIMEONPAGEBOUNCES))/(Unique_Visit_Count - coalesce(BOUNCES)) as average_time_spent_per_visit
from pre_data

 

@skatofiabah Try the above, This calculates visit counts, time on page, average page views per visit and average time spent per visit at the page level.

    skatofiabah
    skatofiabahAuthor
    Level 5
    September 17, 2024

    Hi @krishna_musku,

     

    I need this for the "adb_daily_web_traffic" table. This doesn't appear to point to that. Also, can you break up the code for each inidividual metic separately?

    Krishna_Musku
    Community Advisor
    Krishna_MuskuCommunity Advisor
    Community Advisor
    September 17, 2024

    I didn't use the data feeds much, I found this in my resources for some queries. I don't have the breakdown for individual metrics.

    Terms & ConditionsAccessibility statement

    Loading footer...