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