Google Analytics Data Retriever (API) - SEM, SEO

Google Analytics should be an integral part of any company's marketing data analysis strategy that plays in SEM, SEO field. Not too long ago Google released API for their Analytics and one can use this data or reports to compliment their in-house data analysis.

Here I have provided a small utility program that can be used to retrieve most popular six (6) reports and have used similar utilities that I have built to dump the data into MySQL data warehouse for much bigger data sets.

The application has two Perl modules (GAProfile.pm and GAReport.pm) and a main program (ga_report_retriever.pl). GAProfile.pm module is used to authenticate your account and get the auth_code that will be used in the subsequent calls to GA to get reports. GAReport.pm module is used to pull the report and dump important dimension and metrics to standard output.  Put both these modules in the same directory (or copy them to directory in the path of PERL5LIB) as the main program and run it. To get different report, make a change in "ga_report_retriever.pl" passing the name of the report.

Before you start using the GA API, you may want to go through the documentation.

At command prompt> ./ga_report_retriever.pl

---

MySQL Toad result set filter and compare features

When you work daily in a data warehouse environment, you will inevitably compare or filter or summarize data for further analysis or debugging.  The result set shown in MySQL Toad 4.5 has couple of very nice features that will help you compare or filter the results quickly - right in the "Results" pane.  In the picture below I was calculating Herfindahl Index for a group of AdWords accounts.  The query resulted in date, some daily metric  and its index; and if I am further interested in filtering for specific range of index, for example, I can point the mouse at  Herfindahl_Index column and click on "filter" icon. See below.

Also, by comparing the data between two result sets, the data comparison feature avoids bringing data to Excel or running sub-queries.  For example, I modified the original query and ran it again and wanted to quickly see for any difference in numbers between two results sets at date level, which I could by just doing the data comparison between result 7 & 8 below.  I know I have couple of ways of comparing the data as noted above, but being able to compare in Result set pane was the quickest - didn't need to modify the query nor move the data.

Finally, you can use Pivot & Chart by dragging and dropping the columns to the grid area.  For small data sets this is handy.

Hope that helps,
Shiva

Sharing files between Linux and Windows

If you are running a guest and host operating systems, many a times you would like to share some drives or directories between them.  It may be to share some documents or data or text files, etc.  And to share a directory (example, C:/Share/Edu) between Windows XP host and Ubuntu guest OS you can do the following.

1. First allow for sharing on Windows by right click on the directory of interest (say, C:/Share/Edu) and select "Sharing And Security..." and it will bring up "Edu Properties" window.  Select Sharing tab and set "Allow this number of users" to 2 or the number you like.  Since the sharing is between guest and host OSes, setting it to 2 will suffice.

  In Windows Explorer > Go to C:/Share/Edu > Right click and select "Sharing And Security" > (In new window) > Select "Sharing" tab > Set "Allow this number of users" radio button value to "2"

2. Assuming you have already installed Ubuntu (version 9 or above), go to System > Administration > Synaptic Package Manager as shown below which will bring up new window and look for smbfs (Samba File System) and choose it for download.  Ubuntu will automatically select other packages that are needed for this package.
Note: You need to have sudo authority (or root privileges) to download and install a package.

3. Once done bring up a terminal on Ubuntu and run

hadoop@ubuntu> sudo -s  (if you are not root already)
root@ubuntu> mkdir /mnt/win_edu
root@ubuntu> mount -t smbfs -o username=YOUR_WINDOWS_USER_NAME  //WINDOWS_HOSTNAME/Edu  /mnt/win_edu
Password: ENTER_PASSWORD
# Replace YOUR_WINDOWS_USER_NAME, WINDOWS_HOSTNAME and ENTER_PASSWORD appropriately.
# This will mount windows shared "Edu" directory on to /mnt/win_edu mount point.
#  NOTE: Instead of "smbfs" option above you can also use "cifs - Common Internet File System" supported by Samba, Windows 2000, XP, etc.

4. Change directory to /mnt/win_edu from Ubuntu terminal and you should see all windows files in Edu directory.  You can also check all mounted directories by running

root@ubuntu> mount

5. After you are done to unmount the file system simply run

root@ubuntu> umount -v -d /mnt/win_edu

Or if you just want to copy files between two systems use WinSCP freeware.  Get the guest IP address by running ifconfig and look for "inet addr", then connect to it from WinSCP.

hadoop@ubuntu> ifconfig
eth1      Link encap:Ethernet  HWaddr 00:0c:29:f7:a0:41  
          inet addr:10.15.14.107  Bcast:10.15.15.255  Mask:255.255.254.0

Cheers,
Shiva

Beauty of 99 Bottles of Beer in Perl

Perl is well known for allowing a software developer to express the solution in more than one way. And it can be harnessed to be as creative or cryptic as one likes to be. Here is one example I saw sometime back on http://99-bottles-of-beer.net/language-perl-737.html

Run the program as:

#!/usr/local/bin/perl


    ''=~(        '(?{'        .('`'        |'%')        .('['        ^'-')
    .('`'        |'!')        .('`'        |',')        .'"'.        '\\$'
    .'=='        .('['        ^'+')        .('`'        |'/')        .('['
    ^'+')        .'||'        .(';'        &'=')        .(';'        &'=')
    .';-'        .'-'.        '\\$'        .'=;'        .('['        ^'(')
    .('['        ^'.')        .('`'        |'"')        .('!'        ^'+')
   .'_\\{'      .'(\\$'      .';=('.      '\\$=|'      ."\|".(      '`'^'.'
  ).(('`')|    '/').').'    .'\\"'.+(    '{'^'[').    ('`'|'"')    .('`'|'/'
 ).('['^'/')  .('['^'/').  ('`'|',').(  '`'|('%')).  '\\".\\"'.(  '['^('(')).
 '\\"'.('['^  '#').'!!--'  .'\\$=.\\"'  .('{'^'[').  ('`'|'/').(  '`'|"\&").(
 '{'^"\[").(  '`'|"\"").(  '`'|"\%").(  '`'|"\%").(  '['^(')')).  '\\").\\"'.
 ('{'^'[').(  '`'|"\/").(  '`'|"\.").(  '{'^"\[").(  '['^"\/").(  '`'|"\(").(
 '`'|"\%").(  '{'^"\[").(  '['^"\,").(  '`'|"\!").(  '`'|"\,").(  '`'|(',')).
 '\\"\\}'.+(  '['^"\+").(  '['^"\)").(  '`'|"\)").(  '`'|"\.").(  '['^('/')).
 '+_,\\",'.(  '{'^('[')).  ('\\$;!').(  '!'^"\+").(  '{'^"\/").(  '`'|"\!").(
 '`'|"\+").(  '`'|"\%").(  '{'^"\[").(  '`'|"\/").(  '`'|"\.").(  '`'|"\%").(
 '{'^"\[").(  '`'|"\$").(  '`'|"\/").(  '['^"\,").(  '`'|('.')).  ','.(('{')^
 '[').("\["^  '+').("\`"|  '!').("\["^  '(').("\["^  '(').("\{"^  '[').("\`"|
 ')').("\["^  '/').("\{"^  '[').("\`"|  '!').("\["^  ')').("\`"|  '/').("\["^
 '.').("\`"|  '.').("\`"|  '$')."\,".(  '!'^('+')).  '\\",_,\\"'  .'!'.("\!"^
 '+').("\!"^  '+').'\\"'.  ('['^',').(  '`'|"\(").(  '`'|"\)").(  '`'|"\,").(
 '`'|('%')).  '++\\$="})'  );$:=('.')^  '~';$~='@'|  '(';$^=')'^  '[';$/='`';

Character set, Collation

Here is an interesting challenge that I ran into in the Data Warehouse environment. While debugging a particular business user query, I joined between a string built in a sub-query to a column in a particular fact table with some 20million rows. BTW, I was using MySQL 5.0 and Toad 4.5 to run this query and instead of getting any result I got the following error!

MySQL Database Error: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='    22    0

The query was simple and similar to

SELECT s.str, s.d1, s.d2
FROM (
    SELECT distinct 'string' str, dimension1 d1, dimension 2 d2
    FROM table_1
    WHERE ...
) s
LEFT OUTER JOIN table_2 t2
ON ...
WHERE s.str = t2.str
AND ...

Regular Expressions

MySQL regular expressions (RE) are a powerful tools that can be very useful in SQL string searches. They enable software engineer to build statements that are very concise and to handle complex string operations that otherwise wouldn't be possible.

If you are new to regular expressions or would like to know more about them, http://www.regular-expressions.info/ is a good site to visit. You can also get a RE tutorial at net|tuts+. Following is quick list of meta characters that can get you started in using them.

. => A dot matches single character

* => An asterisk matches zero or more of previous matched tokens

? => A question mark matches zero or one time the previous matched token

$ => A dollar at the end anchors the search to the end of string

^ => A caret symbol anchors the search to the beginning of the string

| => A pipe matches either of the two. Example: abc|xyz => either 'abc' or 'xyz'
{m,n} => A quantifier matching between 'm' and 'n' times. m & n are integers.

Different computer languages have some variations when it comes to more advanced searches and how they handle given character sets. MySQL uses REGEXP string function to implement and matches the string in case "in-sensitive" mode and to match otherwise see this blog.

MySQL User Defined Variable

This is a brief introduction to user defined variables in MySQL as many times I have gotten queries regarding how to use them in dynamic queries.

In MySQL you can easily create user defined variable and use it through out that particular session or client connection. A variable is a alphanumeric characters following a "@". In the versions 5 and above the name is case-insensitive and hence @VAR, @vaR all mean the same. For example:

set @var = 1;

The above statement creates a variable called "var" and sets it to 1. Also note you don't need to declare it before using it.

The statement,

set @var1 = 1, @var2 = 2, @var3 = 3;

sets all three variables in single statement. You can then select the variables from

select @var1, @var2, @var3;

Now, let us say that you would like to select particular row(s) from a given table. To do so you need to first build the sql string, prepare it and then execute it. This allows you to pass the variables one or more times to the same statement.

For this let us assume we have a table (table_t) that has two columns - id and name and has 3 rows (1, 'one'), (2, 'two') and (3, 'three'). To select row with id = 1

set @var = 1;

set @st = concat('select id, name from table_t where id = ', @var);

prepare stmt from @st;

execute stmt ;

And to select with string variable like names you need to escape the single quote as below.

set @var = 'one'

set @st = concat('select id, name from table_t where name = ', '''', @var, '''');

prepare stmt from @st;

execute stmt ;

This is a trivial example but you get the idea of how to use user defined variables. The same technique can be used to build more complex stored procedures and statements that are executed often with different variables.

Cheers,

Shiva

---

---

Case Sensitive SQL Search

MySQL on default installation uses the case insensitive configuration meaning the data you stored and searched will not be case sensitive.

Let us first check our current server and database's appropariate variables.

SHOW VARIABLES
WHERE variable_name = 'character_set_server'

> latin1

SHOW VARIABLES
WHERE variable_name = 'collation_database'

> latin1_swedish_ci

Since we are using latin based language settings, the cases are different unlike some languages that don't have cases. And with the database collation set to latin_swedish_ci the string search will be case insensitive (ci).

For this illustration, I have created a table "country" with single column "country_name" and inserted 3 rows.

CREATE TABLE country (
     country_name varchar(32) NOT NULL
);
INSERT INTO country
VALUES ('US'), ('UK'), ('ukraine')
; 
COMMIT;

Google Docs Spreadsheet - Better access control needed

I use Google docs to maintain many documentations for a non-profit organization and this has been a very useful, money & time saving application. In my view, following three features will immediately enhance what is already a excellent collaborative product.  These are low-hanging fruits.

• User access control at worksheet level to view
• Copying more than 1000 (one thousand cells) at a time
• Multi-column sorting

In our non-profit organization we have multiple chapters controlling their own documents/ spreadsheets and some times there are situations where I would like to have a single spreadsheet but each work sheet is visible only to a particular chapter or member. For example, a common spreadsheet with 50 worksheets (one for each US state) should appropriately be visible to these chapters.  A California chapter can view California worksheet only and not others.

Currently, I can control the editing access through "Protect Sheet" but not the viewing. See below.

Best of Google homepage logos over the years

Google modifies their home page by including birthdays of some major personalities around the world or major events. Here are some of the best, unique and presented in more than one country. The list below is not any particular order.

2009.05.20 - Charles Darwin Birthday

2009.11.13 - Water on Moon found

2009.10.02 - Mahatma Gandhi

Introduction and using MySQL Explain

Like in any database query optimization is critical for MySQL data warehouse environment and having a better understanding of the "Explain plan" helps the database application developer avoiding issues with query  performance. Also, DBAs will like your queries and they would be more than happy to help you optimize them.

MySQL's "Explain" statement provides details on query parsing and execution steps and outputs 10 fields -
id, select_type, table, type, possible_keys, key, key_len, ref, rows and Extra columns.

You run the statement by issuing

explain <sql query> #-- Insert your query between < and >. 

Shown below is the Toad's output of explain plan of a self-joined table's query with where clause. The table has around 20million rows. The query took less than 0.6seconds to index through ~70K rows and with "const" and a func (date_add function) to compare between one days data to its previous day data.

SELECT a.ad_date, a.unit_id, a.max_cpc, 
               SUM(coalesce(a.max_cpc, a.max_cpc) - coalesce(b.max_cpc, a.max_cpc)) diff_cpc
       FROM  sem_kw_summary a
       LEFT JOIN  sem_kw_summary b
           ON  date_add(a.ad_date, INTERVAL -1 day)  = b.ad_date
           AND a.unit_id = b.unit_id
       WHERE date_add(CURRENT_DATE, INTERVAL -1 day) = a.ad_date
         AND a.engine = 'google'
       GROUP BY a.ad_date,  a.unit_id