Metrics Downstream Questions

[skatofiabah]

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/d...

and here: https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-blogs/sample-sql-queries-to-query-a...

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

Thanks!

[Krishna_Musku · Accepted solution]

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]

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]

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.

[skatofiabah]

Hi @Krishna_Musku,

Wait I'm confused. So you don't use that downstream table. Then what do those queries point to?

[Krishna_Musku]

Those queries are for visit counts, time on page, average page views per visit and average time spent per visit at the page level. They are used by my data team. I won't use them. I sent them for your reference. You can change them based on your needs.

[skatofiabah]

Hi @Krishna_Musku,

It's different than ours. Ours is "adb_daily_web_traffic". Does your data team have that table?

[Krishna_Musku]

@skatofiabah No, I think the tables are specific to your org.