Tracking websites with 2 different fields

[phoenixaz]

I have a link that I provide in an email, once click on it to goes to a landing page and then redirects to another site. The landing page is logged in a field called "site" and it also logs the IP in field name "IP". Once redirects it continues to log a different site name in the "site" field, as well as the "IP field.

That being said, how can I write a sql statement that pulls up all of the "IP" fields and "site" fields with the IP that first hits the original landing page?

I suppose you would have to loop through the data base to pick up the name of the landing page that is logged under "site" and then bring up the associated "IP" field regardless of the name of the "site" field.

I am stumped with this one and any help would be appreciated.

Thanks!

[bigmike1212]

Your question makes no sense.

[phoenixaz]

Let me see if I can better explain. I suppose it's like a 2 level search.

sql = "select fieldIP from table where field1 = 'abc'"

That's only going to occur when the user hits the index page. But the user will then continue to click thru other pages.

I want to select * where that IP occurred in the above statement. (field1 will no longer = 'abc'

I hope that makes more sense.

Thanks for your response.

[gk53]

do you record a time when page was requested?
If no how you would know which page was requested first.....

[phoenixaz]

Yes, I have a date/time which matches the page that was requested first.

[gk53]

So, you have table like

site IPField stamp
'abc' 1.2.3.45 '2013-01-28 05:55.654'
'dfg' 1.2.3.45 '2013-01-28 05:56.654'
'abc' 1.2.3.48 '2013-01-28 06:55.654'
'dfg' 1.2.3.48 '2013-01-28 06:55.654'
'dfg' 1.2.4.45 '2013-01-28 07:55.654'
'abc' 1.2.4.45 '2013-01-28 07:56.654'


right?

Now, what result you expect to see?

[phoenixaz]

I want to see:

'abc' 1.2.3.45 '2013-01-28 05:55.654' (this one)
'dfg' 1.2.3.45 '2013-01-28 05:56.654' (this one because it originated from 'abc')

'abc' 1.2.3.48 '2013-01-28 06:55.654' (this one)
'dfg' 1.2.3.48 '2013-01-28 06:55.654' (this one because it originated from 'abc')

'dfg' 1.2.4.45 '2013-01-28 07:55.654' (NOT this one because it didn't originate from 'abc'

'abc' 1.2.4.45 '2013-01-28 07:56.654' (this one)

Sorry for the delay in response. For some reason, I am not getting the email when you replied.

Thanks for you help.

[bigmike1212]

I think it would help you a lot if you would take the time to think through exactly what you want and exactly what the problem is. It certainly still isn't clear to me.

Is this what you want? You have two sets. 1) the set of IP's that visited your landing page 2) the set of IP's that visited other pages. Do you want the intersection of those sets? i.e the list of other pages visited by IP's that also visited your landing page?

select page
from table
where ip in
(
select ip
from table
where page = landing page
)
and page not landing page


What are you tryng to do? Are you trying to construct the most common chains of progression through a site- e.g. people go here first and then here and then here?

For something like that a set up like Google Analytics might actually be what you want.

[gk53]

bigmike is right if it is for analytic just use Google Analytics it is free
but you can try

select t.site, t.IPField, t.stamp 
	from #T as t
	join #T as tt
	on t.IPField=tt.IPField
	and t.site<>tt.site
where t.site='abc'
union
select tt.site, tt.IPField, tt.stamp 
	from #T as t
	join #T as tt
	on t.IPField=tt.IPField
	and t.site<>tt.site
where t.site='abc'
order by t.stamp, t.IPField

[phoenixaz]

select t.site, t.IPField, t.stamp
from #T as t
join #T as tt
on t.IPField=tt.IPField
and t.site<>tt.site
where t.site='abc'
union
select tt.site, tt.IPField, tt.stamp
from #T as t
join #T as tt
on t.IPField=tt.IPField
and t.site<>tt.site
where t.site='abc'
order by t.stamp, t.IPField

I think that seems to work and will know for sure if I can limit the search of the t.stamp field. I tried this and it didn't work.

where t.site ='abc' and t.stamp > '1/1/2013' order by .....

where t.site='abc' and t.stamp > '1/1/2013' union select tt.site ....

Any thoughts? Thanks!

[phoenixaz]

select page
from table
where ip in
(
select ip
from table
where page = landing page
)
and page not landing page

That gives me the results that I was looking for, thanks. I was able to limit the search by adding a date field. I don't know anything about Google Analytics, I will look into that, but your code works.

Thanks for helping.