how can i get the visits from hit data | Community
Skip to main content
E
EnsarDo
Level 2
April 4, 2024
Solved

how can i get the visits from hit data

  • April 4, 2024
  • 4 replies
  • 2171 views

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?

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 MandyGeorge

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)

 

4 replies

Jennifer_Dungan
Community Advisor and Adobe Champion
Jennifer_DunganCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
April 4, 2024

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
Community Advisor and Adobe Champion
leocwlauCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
April 5, 2024

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
    Community Advisor and Adobe Champion
    MandyGeorgeCommunity Advisor and Adobe ChampionAccepted solution
    Community Advisor and Adobe Champion
    April 5, 2024

    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)

     

    M
    Adobe Employee
    mohammadzAdobe Employee
    Adobe Employee
    April 5, 2024

     

    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.

    Terms & ConditionsAccessibility statement

    Loading footer...