Metrics Downstream Questions | Community
Skip to main content
skatofiabah
skatofiabah
New Participant
September 17, 2024
Solved

Metrics Downstream Questions

  • September 17, 2024
  • 1 reply
  • 1233 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
Krishna_MuskuAccepted solution
New Participant
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
    New Participant
    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
    Krishna_Musku
    New Participant
    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...