Monthly Archives: April 2010

Google Analytics Custom Segmentation by Role in Drupal

by stuporglue Posted on April 30, 2010

At the company I work for, I was already using Google Analytics to track visitors to the website, but I wanted a little more information. I wanted to see how many visitors were staff, versus other visitors.

Google Analytics tracking drupal users by role, one week view

Put the Pieces on the Table

The first piece we needed was already in place. We are using Drupal for our website, and every staff member needs to log in from time to time for various staff only resources. Regardless of any other permissions they may have, everyone who is staff is made part of the role “staff”.

Google lets you add up to 5 custom variables to your Google Analytics tracking with the _setCustomVar() function. There are 4 parameters: Slot Number, Variable Name, Variable Value, Variable Scope.

The Variable Scope can be set to 1,2 or 3. 1 is for visitor level scope. A variable with scope 1 will be tracked even if the user has logged out of the site. 2 is session level scope. A variable with scope 2 will be tracked as long as their current session lasts — usually till they close their browser. 3 is page-level scope, which I did not use.

I decided to set two variables, a Visitor scope variable, and a Session scope variable. This way I could not only tell when users were logged in, but compare logged in staff visits to logged out staff visits. I needed to only set these variables when the user is logged in however, which takes us to the next step…

Putting the Puzzle Together

Granted, this isn’t really a hard puzzle, each piece is really simple…which is probably why when I searched for how to do it initially I didn’t find a tutorial .

All we do now is add a piece of PHP to our footer to print the JavaScript if the user belongs to the ‘staff’ role. Here’s our whole footer block including our the Google Analytics JavaScript code.

<div>Corporate Office | Michael Moore , President | Fridley, MN 55432</div>
<div>123-456-7890 (voice, TTY) | 123-456-7890 (fax) | <a href='/contact'>Complete contact information</a></div>
<div>Copyright &copy; Stuporglue.org</div>

<script type="text/javascript">
<!--//--><![CDATA[//><!--
 var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
//--><!]]>
</script>
<script type="text/javascript">
<!--//--><![CDATA[//><!--
try{
 var pageTracker = _gat._getTracker("XX-1234567-8");
<?php
global $user;
if($user->uid && in_array('staff',$user->roles)){
 // Identify this as a staff computer, even if they're not logged in so we can see how many staff use the website while not logged in
 $js_string = 'pageTracker._setCustomVar(1,"StaffComputer","True",1);' . "\n"; 

 // Identify this session as a logged in staff member session
 $js_string .= 'pageTracker._setCustomVar(2,"LoggedInStaff","True",2);' . "\n"; 

 print $js_string;
}
?>
pageTracker._trackPageview();
} catch(err) {}
//--><!]]>
</script>

Stepping Back and Looking at the Results

The last thing to do is to set up your segmentation in Google Analytics. In the left column of Analytics you should see Advanced Segments in the My Customizations area. Once you have clicked that, choose Create a new custom segment.  The custom variables you defined in the steps above should be listed under the Visitors area of the Dimensions section. Drag the custom key and/or values desired to your segment creator, then save it.

If you are tracking company computers for logged off users as I am, you will need to wait a few days or weeks for all of the appropriate cookies to be set. The cookies aren’t set unless the user is logged in, and not all of our staff members log in every day.

Enjoy!

Posted in Computers, Programming | Tagged , , , , | 2 Comments

Update Multiple Rows At Once With Different Values in MySQL

by stuporglue Posted on April 22, 2010

I’ve had to figure this out on my own twice now, so I guess it’s time to document it. It is possible, and fairly easy, to update multiple rows of a MySQL table at the same time, with different values for each row. Unfortunately it’s not as easy as inserting, but once you see what’s being done you will probably say “Oh, of course!”.

The key to the multiple row update query is the CASE statement.  MySQL’s CASE reference page doesn’t even have the word “UPDATE” on the page anywhere, but don’t let that fool you, it can be used in UPDATE statements!

The resultant query, to all of you holding your breath, will look something like this:

UPDATE `table` SET

`column1` = CASE
WHEN `id`='1034786' THEN '0'
WHEN `id`='1037099' THEN '0'
WHEN `id`='1034789' THEN '3'
ELSE `column1` END,

`column2` = CASE
WHEN `id`='1034786' THEN NULL
WHEN `id`='1037099' THEN '1034789'
WHEN `id`='1034789' THEN '1034789'
ELSE `column2` END,

`column3` = CASE
WHEN `id`='1034786' THEN 'Text One'
WHEN `id`='1037099' THEN 'Text Two'
WHEN `id`='1034789' THEN 'Text Three'
ELSE `column3` END

WHERE `id`='1034786' OR `id`='1037099' OR `id`='1034789'

As you can see, for each column to be update we create a CASE statement which updates the column based on a unique ID. As I said, it’s not as easy as inserting, but if you have ever seen a case or switch statement while programming it should be straight forward. The ELSE case will let us keep the original value if we don’t explicitly provide one. The WHERE will limit our updates to the rows we are trying to update.

Now, for a little untested PHP code similar to something I’ve been working on:

// Update values we got from somewhere
$update_values = Array(
  '1034786' => Array('column1' => 0, 'column2' => NULL, 'column3'=> 'Text One'),
  '1037099' => Array('column1' => 0, 'column2' => 1034789 , 'column3'=> 'Text Two'),
  '1034789' => Array('column1' => 3, 'column2' => 1034789 , 'column3'=> 'Text Three')
);

// Start of the query
$update_query = "UPDATE `table` SET ";

// Columns we will be updating
$columns = Array('column1' => '`column1` = CASE ', 'column2' => '`column2` = CASE ', 'column3' => '`column3` = CASE ');

// Build up each columns CASE statement
foreach($update_values as $id => $values){
  $columns['column1'] .= "WHEN `id`='" . mysql_real_escape_string($id) . "' THEN '" . mysql_real_escape_string($values['column1']) . "' ";
  $columns['column2'] .= "WHEN `id`='" . mysql_real_escape_string($id) . "' THEN "  . ($values['column2'] === NULL ? "NULL" : "'".mysql_real_escape_string($values['column1'])."'") . " ";
  $columns['column3'] .= "WHEN `id`='" . mysql_real_escape_string($id) . "' THEN '" . mysql_real_escape_string($values['column3']) . "' ";
}

// Add a default case, here we are going to use whatever value was already in the field
foreach($columns as $column_name => $query_part){
  $columns[$column_name] .= " ELSE `$column_name` END ";
}

// Build the WHERE part. Since we keyed our update_values off the database keys, this is pretty easy
$where = " WHERE `id`='" . implode("' OR `id`='", array_keys($update_values)) . "'";

// Join the statements with commas, then run the query
$update_query .= implode(', ',$columns) . $where;
mysql_query($update_query) or die(mysql_error());

That aught to be enough to either get you started, or get you in trouble.

As always, make a backup of your database or use a test database when testing UPDATE queries.

Enjoy!

Posted in Programming | Tagged , , , , | 7 Comments

Have Pidgin Beep at You Through the PC Speaker

by stuporglue Posted on April 14, 2010

This post will show you how to make Pidgin beep at you through your PC speaker when an event occurs. The PC speaker is NOT the speaker which plays music for you. It’s the speaker that makes a beep when your computer turns on or when you do something wrong in the terminal.

There are a couple of different reasons why you might want to have Pidgin beep at you through the PC speaker instead of through your normal audio output device. Both of these scenarios have been applicable to me over the years.

Scenario 1 : You want computer volume down, but want an audible notification of new messages

I have left my headphones hooked up, and walked away only to find out later that a friend had sent me messages wondering where I was. At other times I have turned the computer volume down while listening to music late at night only to be unable to hear Pidgin in the din of day.

Scenario 2: You don’t have speakers hooked up to your computer, but want to hear when a message comes in

This is my current setup, and has been for quite a while. I have a laptop for taking work with me and for watching videos on, but when I’m at my desktop it’s because there’s something so important to do on the computer that I can’t do it while lounging on the couch. Usually that means I won’t have a video or even music going. Basically it’s a work station, and if I need sound I can grab my laptop.

When doing development though, I don’t want to miss a co-worker’s incoming message. And I definitely don’t want to miss a message from my wife!

There is a built in option in Pidgin to use a Console Beep, but if you want more than just a simple beep, you’ll need to follow these instructions. The following instructions apply to Ubuntu Linux 9.10 and may or may not apply to other versions of Ubuntu and/or Linux.

Step 1 : Install the Beep Program

Beep lets you easily play different length and frequency beeps. Go ahead and install it with

$ sudo aptitude install beep

Step 2: Check if You Have Beep Power

Run the command ‘beep’. If you get a beep,  you’re in good shape. If not…

1) Verify that the pcspkr kernel module is enabled

$ lsmod | grep pcspkr

2) If it isn’t, try enabling with modprobe for testing

$ sudo modprobe pcspkr

3) If that works, add pcspkr to /etc/modules

Step 3 : Find a Nice Beep Command

The command I use for my beeps is :

$ beep -l 90 -f  1000 -n -l 200 -f 2000 -n -l 100 -f 1000 -n -l 75 -f 500

-l — The length (in milliseconds)

-f — The frequency

-n — Start a new beep

So my Pidgin alert sound is 4 beeps which go from 1000Mhz to 2000Mhz then down to 500Mhz. It’s different enough that I know it’s an incoming chat message and not some other computer beeping noise.

Step 4: Add Your Command To Pidgin

In Pidgin, open the Preferences page from the Tools menu.

Under the Sounds tab, select Command as your method. Enter your beep command into the command text box. Close the preference pane.

Configure Pidgin to beep through the PC Speaker instead of through normal audio out

You’re all set, Enjoy!

Posted in Computers, Something Interesting | Tagged , | 1 Comment