how can i get the visits from hit data

[EnsarDo]

hi, 

I have one days worth of hit data. i am trying to get the total daily visits from that day, however when compared to the adobe analytics dashboard its is way over. i get about 10mil when i run below query but dashboard shows around 400k.  below is the sql query that i tried

SELECT COUNT ( CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) FROM dba.myTable WHERE exclude_hit = 0 AND hit_source NOT IN (5,7,8,9)

what am i doing wrong?

[Jennifer_Dungan]

Maybe something is missing in the above SQL (or you just didn't show it), but I don't see a date restriction in your SQL?

Or maybe you only have one day of data in this table, that might be what you said, but I am just trying to be sure/clarify that.

On the surface this looks decent... you are looking at the Visitor Id, Visit Number and Start Times for the Visits; and you have the exclusions accounted for... 

Is there anything else that you might have missed? In Workspace are you pulling from a VRS (Virtual Report Suite) which is filtering out extra information not covered by your SQL? Do you have any segments applied in Workspace?

[leocwlau]

Hi @EnsarDo, you need to count distinct instead of simple count. count gives you the total number of rows where one visit could have multiple rows as there are multiple hits.

[MandyGeorge · Accepted solution]

You want to use count distinct. With just a regular count it will count every instance of when that combination appears, including duplicates. If you want just amount of visits using the count distinct will count just the unique instances, and as it goes down to visit level, it will only be once per visit. (Just like if you wanted unique visitors you would use count distinct with post_visid_high, post_visid_low, and visit_start_time_gmt)

SELECT COUNT DISTINCT ( CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) FROM dba.myTable WHERE exclude_hit = 0 AND hit_source NOT IN (5,7,8,9)

[mohammadz]

Use DISTINCT while counting.
You current query will count all the hits of a visit, while what you need is to count that visit and not its hits.
Here is a hypothetical example :

1. Visitor X - Visit 1 - Hit 1 - Start time 12 PM
2. Visitor X - Visit 1 - Hit 2 - Start time 12 PM
3. Visitor X - Visit 1 - Hit 3 - Start time 12 PM

Your query will count all 3 instances, while AA dashboard will show this as 1, thus creating the discrepancy.

Using DISTINCT will solve your problem.