Archive for the ‘Tips and Tricks’ category

Whitelabel Analytics using Google Analytics

July 19th, 2010

Whitelabel products can be seen anytime you walk into a grocery store.

Image Courtesy of Wikipedia

They’re becoming even more popular in online business models:

Tracking

Most whitelabels operate by either providing a back-end API, or in having a DNS CNAME entry pointing to the host.  In the case, for when you’re powering a whitelabel through a CNAME entry, usually the partner wants to have a way to track their stats. An easy way for them to do this is to include their Google Analytics code and then have them setup a Custom Segment to isolate the traffic to the whitelabel site. That setup is easily accomplished through the following instructions:

Creating Custom Reports

If you are only interested in statistics for an exact Web site, following the steps above to drill-down to your site becomes a time consuming process. You can follow the steps below to create a custom report that is already focused on one or more URLs. In Google Analytics, you can create an “advanced segment” to narrow down a report to basically any criteria.

  1. Log into Google Analytics
  2. Click “Advanced Segments” under “My Customizations” on the left menu.
  3. Click “Create New Custom Segment” on the top right of the screen.
  4. In the “type to filter” search box on the left menu type in “hostname” as one word.
  5. Drag the green hostname box over to the center column and on top of the dotted box labeled “dimension or metric”.
  6. Change the condition to “contains” in the drop down box.
  7. In the value box, type in your full URL of your web site minus any slashes, http://, www or anything else. Example: autoparts.yourdomain.com
  8. Down at the bottom of the form you need to give your new segment a name, for example call it “Entomology site traffic”.
  9. Click the button “create segment” to the right of the name box.

You have now created a new segment, which will be available at any point in the future from only your account. This segment can be applied to your reports by following the instructions below.

Viewing Custom Reports (Segments)

  1. Log into Google Analytics
  2. From any report or the Dashboard click down arrow in the drop down window next to “advanced segments” on the top right of the screen.
  3. By default “All Visits” will be checked, which is the default segment that shows all traffic and all sites. Under “Custom Segments” you will see the segments you created.
  4. If you wish to only view your segment, check it in the “Custom Segments” box and un-check “All Visits”. If you leave “All Visits” checked, it will overlay the reports to allow comparisons.
  5. Once you have selected your segment and unchecked “All Visits” you can click “Apply” below the “Custom Segments” menu.

You are now viewing all of the reports that are narrowed to to only this specific segment or Web site. You can verify the segment is selected by looking above the graph on any report and it should say the name of the segment you created.

Howto create “standardized” CSV exports from MySQL

April 26th, 2010

When you start querying/analyzing/and wanting to export data from MySQL (especially anything free-form like SEO/SEM Keyword Data Extracts) you’ll quickly find out you can’t simply concatenate your columns together and join them with a delimiter to be read later.
Sampling some of our keywords illustrates a number of problems:

Quotes: ! 1/4″ inlet muffler
Newlines: xtra vision
headlight h4703xv hours
Control Characters: ^HToyota+starlet+coolant+temp+sensor, 242°/250° duration @ .050″ 601 lift
Commas/Tabs/Semi Colans or other typical delimiters: “+bosch, +part no., +67647″, 2500hd
chevy cat back exhaust
Unicode: ايسوزو 20011
ב.מ.וו 545i, 宝马m3冷却系统

The Original MySQL attempt to export this was:

SELECT
	`medium`,
	`keyword`,
	`landingpage`,
	SUM(`entrances`) as entrances
INTO OUTFILE 'keyword_data.csv'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
	LINES TERMINATED BY '\r\n'
FROM
	`daily_medium_keyword_landingpage_visits`
WHERE
	medium = 'organic'
GROUP BY
	`medium`,
	`keyword`,
	`landingpage`;

This produced a really great file format, any crazy characters were escaped properly and it looked great on paper:

"organic","! 1/4\" inlet muffler","/cache.aspx?q=\"1+4\"+\"inlet+muffler\"&d=76309488474199&mkt=en-US&setlang=en-US&w=6d792683,38b10c05",1
"organic","!((^ toyota avalon mechanical lifter repairs","/auto-parts/toyota/engine-mechanical-valve-lifter",1
"organic","!(gl-508 rm) motorcraft","/auto-part/149373-motorcraft-gl508rm-remanufactured-alternator",1
"organic","!947 dodge power wagon","/auto-part/983439-mallory-ignition-wire-set-m11947",1
"organic","!967 ford mustang parts","/auto-parts/ford-mustang",1
"organic","!971 142 volvo specifications","/auto-parts/volvo-142/engine-electrical-distributor",1
"organic","!972 buick skylark heater core","/cars/1972-buick-skylark",1

I loaded up FasterCSV to start parsing the file and quickly figured out how naive I was about the CSV file formats.  MySQL’s default use of \ as an escape character isn’t “standard.”  It turns out there isn’t an official CSV standard but I did find something close: http://tools.ietf.org/html/rfc4180#section-2

Hmm so effectively none of the control characters should be escaped, but fields should be quoted if they have a delimeter in them, and a quote in the field should be replaced with “” to be a CSV Standard.

The other wrinkle is the standard calls for \r\n to be the line delimeter, but what happens if there are other \r\n in the columns?

First Step – Remove Escape Characters:

	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''

By adding the ESCAPED BY ”, none of the characters will be escaped in the output

Step Two – Replace \r\n with \n:

	REPLACE(`keyword`, "\r\n", "\n")

Step Three – Replace ” with “”

	REPLACE(REPLACE(`keyword`, "\r\n", "\n"), '"', '""')

Step Four – Replace NULL-Byte with a blank

	REPLACE(REPLACE(REPLACE(`keyword`, "\r\n", "\n"), '"', '""'), ""), char(0), "")

Step Five – Replace NULL field with a blank

	IFNULL(REPLACE(REPLACE(REPLACE(`keyword`, "\r\n", "\n"), '"', '""'), ""), char(0), ""), "")

Putting it all together:

SELECT
	IFNULL(REPLACE(REPLACE(REPLACE(`medium`, "\r\n", "\n"), '"', '""'), char(0), ""), "") as medium,
	IFNULL(REPLACE(REPLACE(REPLACE(`keyword`, "\r\n", "\n"), '"', '""'), char(0), ""), "") as keyword,
	IFNULL(REPLACE(REPLACE(REPLACE(`landingpage`, "\r\n", "\n"), '"', '""'), char(0), ""), "") as landingpage,
	SUM(IFNULL(`entrances`, 0)) as entrances
INTO OUTFILE 'keyword_data_new.csv'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
	LINES TERMINATED BY '\r\n'
FROM `daily_medium_keyword_landingpage_visits`
WHERE medium = 'organic'
GROUP BY
	IFNULL(REPLACE(REPLACE(REPLACE(`medium`, "\r\n", "\n"), '"', '""'), char(0), ""), ""),
	IFNULL(REPLACE(REPLACE(REPLACE(`keyword`, "\r\n", "\n"), '"', '""'), char(0), ""), ""),
	IFNULL(REPLACE(REPLACE(REPLACE(`landingpage`, "\r\n", "\n"), '"', '""'), char(0), ""), "");

Put Google AdSense Into the Corner, Let your page render first using jQuery

April 23rd, 2010

First off, if you haven’t installed both Yahoo YSlow! and Google Page Speed then bookmark this page and come back to it later. Chances are you have bigger fish to fry on the rest of your website.

Now if you read my post on AB Testing with Google Website Optimizer, you probably caught on that <script type=”text/javascript”> tags block rendering of content on your pages.

Now that you’re using these tools, your noticing some of these AdSense calls are taking 300ms to render – sometimes multiple SECONDS…wait but when I combine those two facts that means my content below the ads isn’t rendering yet…

WTF Google!

The good news, is, it’s possible to unblock the rendering of your page’s content. The bad news, is, it involves repositioning the ads after the page has been loaded (a little bit of a flicker).

Solution:

The mechanics of the solution involve three pieces:

  1. A placeholder <div> where the ad should be displayed
  2. Shoving the actual ad elements as one of the last items in your HTML document (so everything else gets rendered first)
  3. jQuery code to reposition the ad back into its placeholder slot

Inline Content:

The container for your ads should be pretty straightforward, you know what size ad unit you’re including so block out the section of your page:

   <div class="detail_ad_bottom">
      <div id="fmad_placeholder_Detail_Page_Bottom" style="height: 90px; line-height: 90px;">&nbsp;</div>
   </div>

Bottom Page Content:

Your moving div that actually calls AdSense/Ad Manager code:

<div id="fmads_Detail_Page_Bottom" style="display: none; text-align:center;">
  <script type="text/javascript">
   //<![CDATA[
     GA_googleFillSlot("Detail_Page_Bottom");
   //]]>
   </script>
</div>

jQuery Magic to reposition your ads back to where they should be:

<script type="text/javascript">
//<![CDATA[
  var fmPositionGoogleAdsInit = false;
  function fmPositionGoogleAd(ad_name) {
    var fun = (function () {
      var placeholder = $('#fmad_placeholder_'+ad_name);
      var ad = $('#fmads_'+ad_name);

      if(placeholder && ad) {
        var pos = placeholder.offset();
        ad.css({
          "display": "block",
          "position": "absolute",
          "left": pos.left+"px",
          "top": pos.top+"px",
          "width": placeholder.width()
        });
      }
    });

    if(!fmPositionGoogleAdsInit) {
      $(window).resize(fun);
      $(document).ready(fun);
    }
    fun();                     

    fmPositionGoogleAdsInit = true;
  };

  function fmPositionGoogleAds() {
    // List all of your ad slots
    fmPositionGoogleAd('Detail_Page_Bottom');
  };

  fmPositionGoogleAds();
//]]>
</script>

Gotchas:

Now, if you have any other dynamic content on the pages, you may have to manually reposition the ads again.  This is simply done by calling:

fmPositionGoogleAds();

Hope that helps!

Ruby Script to Extract Google Analytics Keywords, Landing Page, Medium Entrances to CSV/TSV format

April 19th, 2010

I’ve had versions of this script hacked together before.  In fact, if you search for “Google Analytics Data Extractor” the #1 result is a google groups posting I made in a former life for a C# program I developed: http://groups.google.com/group/analytics-help-misc/browse_thread/thread/d2ad6ddf3d73e511

There’s now a couple of ruby gems out there for handling the Google Analytics API

One of them I’ve been doing some light-contributing to, including the pagination support (automatically grab all results in 10,000 result chunks)

I pushed a couple of changes up today at: http://github.com/er1c/gattica that hopefully will get merged into the main trunk soon (http://github.com/activenetwork/gattica)

One of the more useful uses of the library is to extract your Organic Search Results Keywords from Google Analytics. We’re using this at Frugal Mechanic to see how people search for Auto Parts.

require 'rubygems'
require 'gattica'
require 'fastercsv'

ga_profile = "" #Enter your Profile Here
start_date = Date.new(2009, 1, 1)
end_date = Date.new(2009, 12, 31)
file_path = "data" # Directory Needs to exist

puts "Google Username: "
u = gets.chomp
raise "bad username" unless !u.empty?
puts "Google Password: "
system "stty -echo"
pw = gets.chomp
system "stty echo"
raise "bad password" unless !pw.empty?

class ExtractKeywords
  def initialize(email,password)
    @gs = Gattica.new({:email => email, :password => password})
  end

  def get_accounts
    results = []
    @gs.accounts.each{|account|
      profile = {}
      profile[:site_title] = account.title
      profile[:profile_id] = account.profile_id ## this is the id required for requests to the API
      profile[:account_name] = account.account_name
      profile[:account_id] = account.account_id
      profile[:web_property_id] = account.web_property_id
      results << profile     }     return results   end   def connect_profile(profile)     @gs.profile_id = profile[:profile_id]   end   def connect_profile_id(profile_id)     @gs.profile_id = profile_id   end   def get_keywords(start_date = nil, end_date = Date.today)     results = []     csv_data = @gs.get({         :start_date => (start_date || (end_date - 365)).to_s,
        :end_date => end_date.to_s,
        :dimensions => ["medium", "keyword", "landingPagePath"],
        :metrics => "entrances",
        :sort => "-entrances",
        :page => true}).to_csv(:long)

    return FasterCSV.parse(csv_data, :headers => true)
  end

end

gs = ExtractKeywords.new(u, pw)
gs.connect_profile_id(ga_profile)

(start_date .. end_date).each { |date|
  file = "#{file_path}/medium_keyword_landingpage_visits_#{date.strftime('%Y-%m-%d')}.csv"
  next if File::exists?( file )

  FasterCSV.open(file, "w") do |csv|
    csv << ["medium", "keyword", "landingPagePath", "entrances"]
    keywords = gs.get_keywords(date, date)
    keywords.each{|row|
      csv << [row["medium"], row["keyword"], row["landingPagePath"], row["entrances"]]
    }

  end
}

Rails Plugin for Google Website Optimizer for AB Tests – Dynamic Content Experiments and No Flickering!

January 19th, 2010

Note: If you don’t care how this plugin works and just want to see/get the code, it’s available at github: http://github.com/tpunder/gwo

There’s a ton of reasons to reasons to invest in conversion rate increases.  If you’re spending $1 to acquire 10 visits, and you can get 2 instead of 1 to convert its like putting an extra dollar in your pocket.  A couple days ago Rand Fishkin from SEOMoz even said “2010 is the Year of Conversion Rate Optimization

Google Website Optimizer’s Homepage will give you a number of reasons to use their product My biggest reasons is its FREE!

Last March, we met with Eric Vasilik with the Google Website Optimizer Team in the Seattle Google Office about some hang-ups we had trying to A/B Test functionality and integrate Google Website Optimizer:

  1. Speed. The out-of-the-box Google Website Optimizer code does something like this:
    1. Download/Initialize GWO Code
    2. Identify which experiment/treatment group the user should see
    3. Download each of the content sections that should change and
    4. Use javascript to dynamically update each of the content sections of the page (FLICKER!)
  2. Static Content. Since you must store the HTML fragments in GWO for the service to return back the appropriate multivariate combination to show to users, the HTML fragments must be static.
  3. Maintenance. This ties back to our #2 reason, we would have to upload each of the HTML fragments into the GWO tool so they could be tested.  Since we have to code them locally to make sure they function & display correctly anyhow, it effectively means we need to duplicate the code to GWO.

The solution?  Leveraging a few very very clever Javascript/HTML tricks this is what we’re doing for our Auto Parts Price Comparison site.

Background:

A vanilla GWO Implementation operates similar to:

Tricked out solution looks like:

Why is it clever?  Lets walk through each of the scenarios for the GWO Variations.

Scenario 1: No Javascript Support

<!– Section1 – Default content –>
<script>
var GWO_Section1 = utmx(“variation_number”, “Section1″);
if (GWO_Section1 != undefined && GWO_Section1 != 0) document.write(‘<no’ + ‘script>’);
</script>
Default content – shown by default<br>
</noscript>

Browser ignores everything inside <script> tags and ignores the </noscript>

<!– Section1 – Variation 1 –>
<script>
if (GWO_Section1 == 1) document.write(‘</noscript a=”‘);
</script><!–”>
Alternative content 1<br>
<script>document.write(‘<’+'!’+'-’+'-’)</script>–>

<!– Section1 – Variation 2 –>
<script>
if (GWO_Section1 == 2) document.write(‘</noscript a=”‘);
</script><!–”>
Alternative content 2<br>
<script>document.write(‘<’+'!’+'-’+'-’)</script>–>

Scenario 2: Browser Supports Javascript – Variation == Control/Default Treatment

<!– Section1 – Default content –>
<script>
var GWO_Section1 = utmx(“variation_number”, “Section1″);
if (GWO_Section1 != undefined && GWO_Section1 != 0) document.write(‘<no’ + ‘script>’);
</script>
Default content – shown by default<br>
</noscript>

Browser evaluates GWO_Section == 0 so the document.write(<noscript>) is NOT executed resulting HTML code is:

Default content – shown by default<br>
</noscript>

Browser ignores the </noscript>

<!– Section1 – Variation 1 –>
<script>
if (GWO_Section1 == 1) document.write(‘</noscript a=”‘);
</script><!–”>
Alternative content 1<br>
<script>document.write(‘<’+'!’+'-’+'-’)</script>–>

Since GWO_Section == 0, the document.write(‘</noscript is not executed, but however the 2nd <script>document.write IS still executed resulting in

<!–”>
Alternative content 1<br>
<!–</script>–>

The HTML comment starts with the first <!– and keeps going until –> hits (the browser doesn’t even try to interpret the DOM inside the HTML comment – making this the most efficient way to “throw away” code from even being parsed by the HTML engine.  The variation 1 never gets displayed.

Scenario 3: Browser Supports Javascript – Variation == Experiment Treatment

<!– Section1 – Default content –>
<script>
var GWO_Section1 = utmx(“variation_number”, “Section1″);
if (GWO_Section1 != undefined && GWO_Section1 != 0) document.write(‘<no’ + ‘script>’);
</script>
Default content – shown by default<br>
</noscript>

Browser evaluates GWO_Section1 == 1 so it DOES print the <noscript> resulting in:

<noscript>
Default content – show by default<br>
</noscript>

Browser starts at the first <noscript> and ignores everything until the ending </noscript> – thereby ignoring the control treatment’s HTML

<!– Section1 – Variation 1 –>
<script>
if (GWO_Section1 == 1) document.write(‘</noscript a=”‘);
</script><!–”>
Alternative content 1<br>
<script>document.write(‘<’+'!’+'-’+'-’)</script>–>

GWO_Section1 evaluates to == 1 and the document.write prints out resulting in:

</noscript a=”
<!–”>
Alternative content 1<br>
<!–</script>–>

There’s two clever things in this one. The first is the a=” starts to create an HTML attribute that “ingests” the dangling <!–. The second, is the <!–</script>–> is a complete HTML comment, so there isn’t a dangling </script> tag floating in the HTML. Poof the Web Browser is now showing the Treatment HTML.

After we met with Eric he posted a link and an explanation for this trick at http://www.gwotricks.com/2009/05/server-side-dynamic-section-variations.html

We have a Rails Plugin that encapsulates all of this logic pretty well – so you don’t have to worry about all of the javascript hacks:

http://github.com/tpunder/gwo

Happy A/B + Multivariate Testing!