List:General Discussion« Previous MessageNext Message »
From:DL Neil Date:November 19 2002 10:15pm
Subject:Re: Thx to Martin Waite, and 1 more question
View as plain text  
Hi Warren,

> I wrote in a little while ago about getting unique visits to a website
> listed by day.  Martin Waite came up with a very nice formulation, which
> does wonders!
> However, I have done something that is now yielding inconsistent results.
> The screen shot is basically like this:
> Day by Day Summary for Unique Visits since civic elections (Nov 16)
> Date Count Summary
> 17.11-2002 8
> 18.11-2002 62
> 19.11-2002 14
> --------------------------------------------------------------------------
> Current TOTAL for Visits since civic elections (Nov 16): 79
> --------------------------------------------------------------------------
> As you can see the day-by-day summaries are 8 + 62 + 14 = 84, yet the
> reported is 79.
> My query must be somehow flawed but I'm not sure how - in both cases I
> DISTINCT the ip. . .  Here is exactly what I did:
> Thanks so much for your assistance - I learn a great deal from even your
> most off-hand comments!

Let's say that the same person/machine visited your site on the 17th and
again on the 18th, then on each of those days they would be counted as 'one'
towards the totals of 8 and 62 (resp). However if you ask for unique
visitors during the period 17th to 19th (incl), then those two visits will
only be counted once, because of the DISTINCT clause.

This not only answers your question, but I'm afraid also rather exposes the
short-coming of the initial proposition. An IP address uniquely identifies a
machine - but only at one moment in time - there is no one-to-one
relationship between an IP address and a person/constituent!

1 There are many people who use systems behind a NAT box (Network Address
Translation). A NAT box presents only one/a few IP addresses to the
Internet/externally, but runs any number of IP addresses internally,
translating between the two, and thus conserving (expensive/scarce) IP
addresses and providing a rudimentary form of (external) security. As you
can imagine, this is particularly popular with ISPs and used by many
corporates. In this instance many people/computers will appear (to anyone
viewing from the Internet side) to share a single IP address (and

2 ISPs with banks of modems/dial-up users routinely use DHCP to share a
range of IP addresses they have been allocated, amongst a much larger number
of users. In other words a user could dial in now and be given an IP
address, visit your site, have his 'details' collected by your log, and then
hang up - and them immediately another user could dial in, be allocated
exactly the same IP address by the ISP, visit your site, etc, but not be
distinguished as a unique visitor!

Sorry, thought you should know...
Hope the mysterious problem is solved though!

Thx to Martin Waite, and 1 more questionWarren B.19 Nov
  • Re: Thx to Martin Waite, and 1 more questionDL Neil19 Nov