Tom's Analytics

• RKG: Ya Hoo for Yahoo! – Great explanation of Yahoo's Syndicate Partner segmenting (which is huge!) http://cli.gs/rz62Zu #ppc #
• RT @AdamMarshall: What if I go instore, get a demo of an ipad, then use it to buy an ipad. Attribute that, #measure -> LOL, IP filter BAM! #
• Forrester: Setting the record straight on mobile ad spending projections. http://cli.gs/5vY7Zr #
• Godin: Open buying and open selling. http://cli.gs/np6GGB Remind anyone of the WA tools market? #measure #
• Anna O'Brien: Influencers- Another Social Media Buzzword I Hate http://cli.gs/JS1Zg1 – Anna is easily my fav #socialmedia blogger. #
• AdAge: Why Metrics Are Killing Creativity in Advertising http://cli.gs/zVtyvE Point proven with metrics! #measure #

• Big news: Google announced upgrade for Ad Manager: DoubleClick for publishers. http://cli.gs/hmq6u1 #
• Social Times: The 10 #SocialMedia Metrics Your Company Should Monitor http://cli.gs/u3JArj #measure #
• SMBC: Science TV vs. What actual Science TV would be like: http://cli.gs/E9y9zQ #
• Google Solutions for Southeast Asia: My Favourite Google Analytics Advanced Segments http://cli.gs/YNDBzp #measure #

• RT @HilliconValley: D.C. wants Google's fiber network: D.C. hopes to be a test market for Google's broadband http://bit.ly/csTMkn #
• ClickEquations: Why Keywords Are Over-Rated – http://su.pr/43PPrm Extolls importance of search query discovery process. #
• PPC Hero: Living On The Edge: The Google Content Network http://su.pr/7HPkZI With effort comes great reward on the GCN. #adwords #ppc #
• RT @jdersh: Getting a tour of the new #WAA site. It launches tomorrow! #measure – Sweet, hopefully with tighter #GA imp! #
• RT @rommil: Is there a future for certified web analytics professionals? http://tinyurl.com/yevj3bm My point of view. <- Thought-provoking. #
• Is There a Future for Certified WA Pros? http://su.pr/22lkzl A: Yes as long as there is a future for HiPPO's. #

By using functions in Excel and SQL that return the text from specific locations within a string combined with ones that can isolate the location of the “@” character in every email address, you can easily extract domain names from lists of email addresses.

EXCEL:

The base function for this is RIGHT.  RIGHT gets passed two arguments, text, which is the text being parsed, and num_chars, which is the number of characters returned by the function.  RIGHT takes the form in Excel of RIGHT(text,[num_chars]).

The text argument is obvious; it is the text of the email address to be parsed.

The num_chars argument is determined using a combination of two other functions, LEN and FIND.  We use LEN to determine the length of the overall email address and subtract the position of the @ operator, determined using FIND.  The resulting differencewill return the length of the domain portion of the email address.

LEN(text) returns the number of characters in the string.

FIND(find_text,within_text,start_num) returns the postion of the find_text within the within_text.  start_num, which we won’t use here, is a way to start selecting text after a certain number of characters.

To put this all together, let’s put my email address in cell A1, place our derived function into the B1, and derive the result.

=RIGHT(A1,((LEN(A1)-FIND(“@”,A1)))) calculates to:
=RIGHT(A1,(21-4)) to:
=RIGHT(A1,17) to:
=tomsanalytics.com

SQL:

SQL is a little bit trickier, as there isn’t a right-to-left text selection function, instead we are going to use a left-to-right function, SUBSTRING.

SUBSTRING returns a string of text based on definition passed to the function.  SUBSTRING takes three arguments, value_expression, which is the text being parsed, start_expression, which is the starting character of the returned string, and length_expression, which is the number of characters returned, starting with the start_expression.  This function in SQL looks like this: SUBSTRING(value_expression,start_expression,length_expression).

In this case our email address is the value_expression.  The character following the “@” symbol is the start_expression, with the length of the remaining string being the length_expression.

To determine start_expression, we deploy another SQL function, CHARINDEX, which works exactly like FIND in Excel.  Using SQL’s version of LEN and the same math, we can determine length_expression.

Putting it all together, let’s assume a table named email_addresses with a column named email:

SELECT SUBSTRING(email, CHARINDEX(‘@’, email) + 1, LEN(email) – CHARINDEX(‘@’, email) + 1) AS domain_name
FROM email_addresses
WHERE email like ‘%@%’

The WHERE clause is in there to prevent malformed email addresses from crashing the CHARINDEX function.

The math works the same way as the Excel, except that you have to remember that we are working from the left, so the need arises to add one to the character counter in the CHARINDEX function.

Simple and powerful.  I hope this is useful to someone – the inspiration for this post came from this post at Chandoo.org.  Pointy Haird Dilbert is easily my favorite as well as one of the most useful and entertaining Excel blogs out there.

• Thinking "I must have moved a ton of snow this weekend." Then did math: 100lbs/min x 300 min = 15 TONS! #datageek #
• All Facebook: Facebook Releases Beta Version Of Ad Conversion Tracking http://su.pr/2rdD8T #socialmedia #ppc #
• Highlights of what I've been reading during Cabin-Fever-time: http://su.pr/2zrS6S #

Keep Your Log Files, from Ian Lurie.  Good advice, although I haven’t done post-hoc logfile analysis since clickstream analytics tools only did logfile analysis.  My advice: set up a cron job and compress files by month.  Once a year, archive them, just make sure that your backup solution handles the current set.  Someone once told me that logfiles were needed for Sarbanes-Oxley compliance, I did not believe them.

Dave Naylor explains systemic discrepancy between bit.ly vs. Google Analytics referral in Why Google Wants You to Use it’s Url Shortener.

Google Uses Hours of Search History to Serve Ads from WebProNews – this is pretty cool.  Having just taken the AdWords certification exam for the second time, I see this change making a few of the questions potentially confusing.

Via Kottke, a visualization of Twitter’s codebase changes and developers over time:

Twitter Code Swarm from Ben Sandofsky on Vimeo.

From SmartData Collective, Huffington Post: Crawling with data addicts.

From Silicon Alley Insider, Google Is Wrecking DoubleClick, Says Unhappy Client.  I’m not sure if “wrecking” is appropriate, especially if Google starts to allow third-party ad serving through AdWords.  Note to Google: please start doing this, it would be highly disruptive to the entire online advertising market!

From Dave Chaffey, Customising Google Analytics for your business – 6 key types of customisation.  Great summary here of GA functionality.

From Chandoo, an open thread on Excel Keyboard Shortcuts.  I have to admit, the only one I use with any regularity is F4 and CTRL+Arrow Keys.  CTRL+Space and Shift+Space are awesome ones that I always forget about.

From Search Engine Land, 20 Metrics to Effectively Track Social Media Campaigns.

From Logic+Emotion, Six Ways to Find Social Media Talent.  This advice is really applicable to finding any talent.

• Senior Marketers Need Greater Accountability – http://su.pr/1DCFtd – In other news: water wet. In all seriousness, great article. #measure #

• Are browser configs so unique that they make browsers personally identifiable? Help the EFF find out! http://su.pr/9Mv2dH #
• Out of the 16,359 browser configs tested so far by Panopticlick, mine is unique (w/ Chrome). Yikes! http://su.pr/9Mv2dH #
• Google: Top 5 strategies for political campaigns – http://su.pr/1ZiQcO – So happy that Chris mentioned #GWO! #measure #
• @erinrecruiter There is a Web Analytics Washington happy hour at 5 at the Mayflower Hotel. If there are seekers there, I will mention it. in reply to erinrecruiter #
• I want to love #tableau, but it makes me crazy – Can't figure out how to calculate Z-scores acorss range of values – takes 2 secs in #excel. #
• TechWyse likes the GAIQ passing score bump: http://su.pr/1ROuBZ #Adwords cert is 85% and a harder test! #measure #

• Are browser configs so unique that they make browsers personally identifiable? Help the EFF find out! http://su.pr/9Mv2dH #
• Out of the 16,359 browser configs tested so far by Panopticlick, mine is unique (w/ Chrome). Yikes! http://su.pr/9Mv2dH #
• Google: Top 5 strategies for political campaigns – http://su.pr/1ZiQcO – So happy that Chris mentioned #GWO! #measure #
• @erinrecruiter There is a Web Analytics Washington happy hour at 5 at the Mayflower Hotel. If there are seekers there, I will mention it. in reply to erinrecruiter #
• I want to love #tableau, but it makes me crazy – Can't figure out how to calculate Z-scores acorss range of values – takes 2 secs in #excel. #
• TechWyse likes the GAIQ passing score bump: http://su.pr/1ROuBZ #Adwords cert is 85% and a harder test! #measure #

• RT @erictpeterson: Hey #measure people, did you see I'm giving away PDF copies of my books? http://bit.ly/demystified-books (!!!) #
• We live in the future! A piece of art that periodically sells itself on eBay: http://su.pr/9qFC22 #
• @mrwebanalytics Answer: you can set up Google Analytics to "fool" the call into thinking that the embed is on the same domain. in reply to mrwebanalytics #
• One week until #WAW in Washington, DC at the Mayflower Hotel. Hope to see all the Dodge City #measure folks there! #
• @erictpeterson I did assume that our Web Analytics Washington was an offical #WAW – It is, after all, a WA event that occurs on a W, in a W. in reply to erictpeterson #
• Point of clarification – Wednesday's Web Analytics Washington (DC) is not a #WAW event – I incorrectly assumed that it was. #