Hi Matt,
I work with Craig. We loaded two sets of data and have found mixed results when following the Adobe documentation and queries in the above links. When looking for page views we followed the ClickStream documentation and found that the below query achieves the numbers found on the key metrics report within Adobe Analytics.
select count(*)
from dataTable
where POSTPAGEEVENT = 0
and EXCLUDEHIT = 0
and HITSOURCE = 1
Can you speak to the difference from the below query which produces a different result from the key metrics report mentioned above.
select count(*)
from dataTable
where ((pagename != '') or (pageurl != ''))
and EXCLUDEHIT = 0
and HITSOURCE = 1
When calculating visits, the documentation wants to group by POSTVISIDHIGH, POSTVISIDLOW, VISITNUM and VisitStartTimeGMT. This grouping gives me a different result from the key metrics report. When I use the below query, the result match.
select count(*) from (
select count(*) c
from [FINS_WM_STG].[dbo].[STG_FINS_FACTWMSESSION] stg
where EXCLUDEHIT = 0
and HITSOURCE = 1
Group by POSTVISIDHIGH, POSTVISIDLOW, VISITNUM
) a
Again, I ran the above queries on two sets of data and the results were the same. Can you speak to these differences?
