How to calculate unique visitors( that are reported in Omniture) from clickstream data

[abh_sri]

Hi ,

My organization uses Adobe marketing cloud as a web analytics tool. We currently get data after logging into the website and collecting figures manually. But I understand that we can achieve the same using clickstream data that is provided by adobe to my organization. 

Could someone please help me out how to do this. I did a lot of research and came up with the below query that I ran on the clickstream data but the figures did not match. Infact the result that I got was way higher than what we get from Omniture.

The query is designed to run on teradata.

Select post_visit_hi_id,post_visit_low_id from clickstream

where geo_cntry_cd='xyz' and gmt_hit_dttm>='2015-01-01 00:00:00' and gmt_hit_dttm<='2015-01-31 23:59:59'

group by post_visit_hi_id,post_visit_low_id

The intention was to get unique visitors for a month. The result varied significantly. Help will be greatly appreciated. 

Aim is to be able to generate unique visitors using clickstream data so that more cuts on the data can be generated.

[brian_au · Accepted solution]

Hi,

Check out the below help microsite article on calculating metrics via the clickstream:

https://marketing.adobe.com/resources/help/en_US/sc/clickstream/datafeeds_calculate.html

Visitors

1. Exclude all rows where exclude_hit > 0.
2. Exclude all rows with hit_source = 5,7,8,9. 5, 8, and 9 are summary rows uploaded using data sources. 7 represents transaction ID data source uploads that should not be included in visit and visitor counts. See Hit Source Lookup
3. Combine post_visid_high with post_visid_low. Count unique number of combinations.

Best,

Brian