What data to track?

[shindig]

I'm building a framework so I can update affiliate ads from a backend and track information about users and ads.

Designing the db table for user info, what to store, and so far I'm stuck at:

mac_address, adNumber, date, number_clicks

Where mac_address is the unique phone/computer that sent the click on adNumber, today, 4th time ever, for example.

I was going to add the mac_address for every person that ever opens the site, then track any clicks by that user on particular ads, with the date, and total number of times that device clicked any ads in particular.

I want to collect information i could use to determine any value for the impressions and web traffic. Like say 1 ad in the past 30 days has 10k impressions, 100 clicks, and I want to charge $5 to put your ad there for 30 days. You want to know if it's worth $5. I figure tracking unique devices rather than IP address is best.

You would look at a list of available ad placements, the data tied to it, and each would be up for rent for a month. Rental would be you giving me a link to your logo, the url you want the ad to open when clicked, and a rental fee.

Thoughts?

 

[Jake232]

And how do you think you're going to detect the MAC address? The mac address of the users device is never sent past the router on the users network.

 

[jryan21]

Here you go, bro:

The 7 Layers of the OSI Model - Webopedia.com

 

[Kiopa_Matt]

1.) As mentioned above, MAC address isn't going to work. How do you expect to get that from the visitors? Do same as everyone else -- combo of cookie + IP.

2.) # of views would be helpful, so you can determine CTR.

3.) Make sure there's checks in place so bots can't hammer the hell out of your ads, inflating the numbers.

4.) Make sure there's checks in place for SE bots (Google, Yahoo, Bing) crawlers, etc.

5.) If you want higher-end stats, Google "OLAP Mondrian", and start from there.

 

[hummer]

3.) Make sure there's checks in place so bots can't hammer the hell out of your ads, inflating the numbers.

4.) Make sure there's checks in place for SE bots (Google, Yahoo, Bing) crawlers, etc.

^^^This

I would create another table called 'bots' and create two columns to stores the UserAgent and IP address and have your program/script filter out any bots before updating your clicks table.

I would also store the UserAgent into your clicks table just so you can clean up stats as you discover new bots. If not for customers but your own sake.

Oh and like everyone said, you can't get a MAC address, IP should do.

 

[shindig]

Sorry but I have the mac address from my software they're using(video game), and in the php script called from within the game I grab the ip the request came from. Verified working, I get the mac address off the network card they send the request from within the game.

Partial code:

import System.Net.NetworkInformation;
function GetMacAddress(){
var macAdress = "";
        var nics : NetworkInterface[] = NetworkInterface.GetAllNetworkInterfaces();
       
        var i = 0;
        for (var adapter: NetworkInterface in nics){
            var address: PhysicalAddress = adapter.GetPhysicalAddress();
            if(address.ToString() != ""){
                macAdress = address.ToString();
                [B]mac = address.ToString();[/B]
                   postMac(mac,1, 1);
                //Debug.Log(mac);
                return macAdress;
            }
        }
        return 0;
}
function postMac(mac, adNumber, playClick){ 
    var highscore_url = addScoreUrl + "name=" + mac +"&adNum=" + adNumber +"&playClick="+playClick;
   // Debug.Log(highscore_url);
       var hs_post = WWW(highscore_url);
       yield hs_post; 
       Debug.Log("done posting");
     }

Works on mobile apps.

I made 3 tables, one stores info about the user, based on mac, if the request comes from a new mac address not in the db it makes a new row with it's information, if it has an existing record it updates it, incrementing times played, and a second table updates every time that mac address clicks an ad, what IP it connected with, how many times they click each ad, along with the date of the clicks. The third table stores info about the ad units. I can do queries against the tables to find out how many times the user clicked each ad, how many times they launched my game, what levels on the game they visited, etc info.

To get the IP address in Php is simply

$ip = getenv("REMOTE_ADDR");
$name = mysql_real_escape_string($_GET['name']);
$date = date('Y-m-d H:i:s');
$adClick =($_GET['adNum']);
$playN = ($_GET['playClick']);
$adID = $_GET['adNum'];

$query = "INSERT INTO playerData (mac,date,adClick,playNum) VALUES ('$name', '$date','$adClick','$playN')ON DUPLICATE KEY UPDATE playNum = playNum+1, adClick = adClick + $adClick;"; //This one updates existing rows if there is one.


$sql = "INSERT INTO adStats (mac_id, ip_add, ad_id, date ) VALUES ('$name', '$ip', '$adClick', '$date');";

Those entries are made from a URL called inside the game passing whatever I want to track. It looks like:

addScoreUrl="http://mydomain.com/adverts/userStats.php?"; 

postMac(mac, adNumber, playClick){ 
highscore_url = addScoreUrl + "name=" + mac +"&adNum=" + adNumber +"&playClick="+playClick;

hs_post = WWW(highscore_url);
       yield hs_post; 
       Debug.Log("done posting");
}

Simply triggering postMac with the variables you want to pass in the url updates the 2 database tables. The first one has a condition to update existing records if there is one, for that mac, otherwise insert a new record. The second table is inserted a new record with mac, IP, date, ad number clicked.

I'm using bluehost, those pics are from the myphpadmin from the control panel in BH. Opening the game on my computer or mobile adds records perfectly (after much crying over php syntax).

So I'm tracking mac, ip, number of times playing, ad_id clicked, num times cilcked, dates of clicks (determine activity rates) for anyone that plays the game.