Austblue

Not as yet and he's responded in that thread answering other questions so I'll PM him today.

RedRover

Hi s2o0o, How are you? What have you been up to lately? Any motoring adventures to tell us about?

Austblue

RR,

From cthree

I assume that you're satisfied with just the results? Let me know so I can respond and he can sort it out.

RedRover

Of course I would be happy with just results

It would save me all the work.

However, having done it before I know it's not as straightforward as C3 might be thinking at the moment. Is he planning to construct some SQL queries? If so, would he be happy to show us the code beforehand so that we can see if it is complex enough to produce an accurate result?

The basic problem is inconsistencies in the data and some local Australian knowledge is required to sort it out.

For example, many of our Australian members think that their State comprises only the metropolitan area and so they put "Sydney" or "Melbourne" where they should have put NSW or VIC. Some have difficulty spelling "Australia" and so they put "Oz" instead etc.

For those who just put a regional city name (like Wollongong, Newcastle or Bendigo) some local knowledge is needed to derive which state it is in. Previoously, I did all this manually. I don't think an SQL query could resolve those data problems and I do like results that are accurate.

If I give a rock-solid guarantee not to disclose the data nor to use it for any other purpose, would he be willing to grant me access to the membership list again? Perhaps if C3 were to have a look at the old threads where I did the analysis before he'd understand what we're looking for and what we're proposing to do?

Austblue

Mate, I think that it might be best for you to shoot him a PM. I'll also PM him to check this thread but I don't think we're going to solve much playing chinese whispers all afternoon when I've got work work to get done.

DavidM

Why not just ask cthree to run the query (ie. to list all the Australian members) and provide us with all the data? I'm sure RR would be happy to sort the data as long as it retains all the user information.

RedRover

Part of the problem is to first identify the Australian members. When you look at all the membership list you'll see that there are many different things entered in the "Country" field by the members when they joined.

Some have put "Au". Some have put "Aus". Some have put "61" and some have put "Oz". Some have left Country blank but we know they're Australian because they put "Sydney" as State/Province.

That is, we have incomplete and inaccurate data in the database that no SQL query could decipher. It needs human intelligence and local knowledge to sort it out.

cthree

From a technical standpoint it's easy. I can do a search of everyone's IP addresses and generate a list of every account that has posted a message from an Australian IP address. There is a database which pinpoints every IP address block which we subscribe to and this is far more accurate than relying on what the user enters. This will include those who have posted while on vacation or visiting but we can screen those out by counting the number of posts made in Australia compared to their total number of posts. We know which IP people registered from so we can also include those who have never posted. If they don't have an account then there is nothing but an IP address to identify them so that's worthless.

The bigger issue is whether I can do this and still respect people's expectation of privacy. The fact it can be done is without question.

Australia has accounted for 38870 of the 3675971 forum page views we've had this month so far. You are the fourth largest country by traffic following USA (2.9M pages), UK(291K pages) and Canada (229K pages). We normally serve about 8-9M pages per month (300K-400K per day). Those are real pages, not hits on images, avatars, style sheets, javascript files or redirect pages. Those are forums, gallery, PM and other full pages of content (if there is an add on it it counts, otherwise probably not).

We serve those pages to about 200K-300K unique IP addresses each month. I guess that equates to about 75K real people when you take dynamic assigned IPs and dialups out of the picture but there is no way to know for sure.

Just FYI in case you were interested.

Austblue

Thanks for your attention cthree

RedRover

Hi cthree, thanks for your interest in this issue.

We just want to extract a statistical picture of our Australian users and their usage of the forums. We don't want any personal information about any particular user.

If you can do it for us we would be impressed and grateful. I understand that finding the time may be difficult for you. If you can't do it yourself is there any way that you could grant either Austblue or myself sufficient access (on a temporary basis) to extract what we need?

Perhaps some of the other Country or USA State groups might be interested in much the same statistical info and if so, someone might be able to develop a generic process that would provide comparable info for all groups?