Test Your Google Analytics IQ

Google is now offering a Google Analytics course for web analytics! Plus, if you want to get your Google Analytics Individual Qualification you can pay $50 to take a test. If you score at least 75% on the test, you become qualified. They say that this is proof of your Google Analytics competency, but there's no information as to whether or not they will have a logo or graphic to display. I assume they do, or will soon.

Even if you're not interested in the certification, there's a ton of great Google Analytics information for beginners to advanced users.

So there you have it. Head on over to Conversion University to learn more about GA and take the course to prove your knowledge to the world.

(Check back for our Google Analytics Individual Qualifications soon!)

eLearningForEmployees.com is Now Live!

My client, Applied Behavioral Insights, has just launched their new eLearning website. By partnering with several e-Learning providers, they now offer over 4500 courses.

Course topics range from Cisco networking, to Microsoft .NET technology, legal & compliance issues, and CompTIA. They also cover workplace health & safety, so you can be sure to find a course that can help you move forward in your career.

They're also offering a free course right now. If you're at all interested in eLearning courses, why not give eLearningForEmployees.com a try?

If you do, please let me know what you think.

I Love Google but...

I love Google! They provide incredible services to the online community for free! Plus, they're giving away Gmail stickers now too!

However, I agree with Brian Clark at CopyBlogger in that we shouldn't use so much energy to game the system. We need to concentrate on excellent content that is useful to many people. Writing a bunch of blog posts only to fill your site with keywords might get you some random search traffic, but it's not even close to having loyal subscribers.

Simple comparison:
1. You get 1000 visitors in a month thanks to the 200 posts you wrote that were strategically written to include buzzwords from the long tail and the short head.
- Super~ These people will give you about 10 seconds of their time before they see what you've done. Real people can easily recognize a post that's stuffed with keywords or that was purely written to attract the search engines. You got a visitor, but they'll never be back.

2. You have 100 subscribers that read every one of your posts. They visit your site and occasionally leave comments. Maybe a few of them have blogs of their own and like to comment about your posts from there. Yes, that's an inbound link that will help your search rank. You didn't get it by spamming the Internet with garbage. You got it by writing content that someone found useful. They found it so useful that they took time out of their day to write about it and give you a link. Now the readers of that blog, who trust what the writer has to say, click over to your blog. Snap! More subscribers. And the cycle continues.

A little more math to back up #2: If you write 10 high-quality posts per month and all 100 subscribers visit, that's 1000 visits right there. Add in the link juice and the fact that it will naturally contain good keywords, and you can expect a ton more traffic than #1.

Moral of the Story:
Everyone please stop writing posts solely for the search engines! Yes, I'm guilty too, but I'm starting to come around. When writing a post pretend that you're writing a letter to a friend. If you wouldn't send this to a friend, then why would anyone else care to read it? If you don't pass that qualifier, don't write the post.

Excel VLookup Function

A wonderfully powerful and easy to use way of comparing data across Excel workbooks is the VLookup function. I've been helping a lot of people with this function lately, so I thought I'd get a quick tutorial out there.

Let's start with the syntax of the VLookup function:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Here's an example of how you would use VLookup to find rows on the "OtherSheet" worksheet that match something in the first row of your main worksheet:

=VLOOKUP(A1, OtherSheet!A$2:B$100, 2, FALSE)

Super. So what does this all mean?

lookup_value: The cell you want to match to the other sheet.
table_array: The range where your lookup_value exists in the first column.
col_index_num: The column of table_array that you want to show if you have a match. (First column is 1.)
range_lookup: Optional. TRUE will find matches “similar” to your lookup_value, and FALSE will only find an exact match.

As an example, you have a list of states with their populations on Sheet1. On Sheet2, you have a list of states with the number of lakes they have. Sheet1 lists all states but some are missing from Sheet2.

Option 1: Manually match the states together and copy the values over. 1 by 1.

Option 2: Use a VLookup function. In column C of Sheet1, you would use the vlookup function to see if the value in column A of that row (lookup_value) is found anywhere in column A of Sheet2 (table_array). Also included in table_array is the column that lists the number of lakes. (If there were 10 states listed, the range might be A1:B10.)

Warning!: When typing the range in your VLookup function, remember to put the dollar sign in front of the row numbers. Otherwise, as you copy the function down Sheet 1, the range will move down with you. So, when you get to row 10, rather than having the range of A1:B10, the range will be A10:B19. Use the dollar sign ($) and Excel knows you want the numbers to remain static.

Advanced VLookup Hack:
Whenever your VLookup doesn't find a match, it insists on putting #N/A in the cell. Needless to say, if you're making a report for someone, you don't want all of these ugly values throughout your sheet. You could sort your data and delete all of the #N/A values manually, but you'd also be deleting the VLookup formulas. Therefore, if your data changes you might miss values that you otherwise would have matched.

To get rid of the #N/A values automatically, use this formula:

=IF(ISERROR(VLOOKUP(…)), “Val if not found”, VLOOKUP(…))
For Example:
=IF(ISERROR(VLOOKUP(C13,'State-List'!C$2:E$100, 3, FALSE)),"",VLOOKUP(C13,'State-List'!C$2:E$100, 3, FALSE))

In the above example, we check to see if the VLOOKUP results in an error. (ie, value not found)
So if the “IF” does result in an error, we put the “Val if not found” value in the cell.
(Generally, you’d simply use back-to-back quotes to enter a null value into the cell.)
Otherwise, we run the VLOOKUP as originally intended.
(Replace the dots with the actual VLOOKUP parameters)

You don't even need to understand this formula to use it. Simply type it exactly as shown above, except, insert your own VLookup function in both places where it says VLOOKUP(...) and remember that whatever you type within those quotation marks will show up if there is no match for your VLookup. I generally recommend back to back quotes for a null value, or a zero if you're working with numbers.

Well I hope that helps clarify what Excel's VLookup function is all about. If you found this post helpful, please consider subscribing to my RSS feed. That way you won't have to remember to come back when I post another great Excel tip.

Plantronics TeleWho Contest

A while ago, a blog I frequent, WebWorkerDaily, wrote about a contest that Plantronics is having. Their goal is to come up with a replacement for the word "telecommuter". They are giving away what WWD called the web worker's dream phone, the Plantronics Calisto Pro, to 10 finalists. The grand prize winner gets $1700 in great tech gear. Check out the contest site for details.

So, I decided to enter the contest with the term "NetWorker". Unfortunately, I don't remember exactly what I wrote to enter the contest, but when I was done, I was pretty happy with my submission. I clicked Submit and basically forgot about the contest. Until yesterday...when I got an email from Plantronics telling me that I was selected as one of the top 10 finalists! Out of 500 entries, they selected my term! I don't believe for one second that I was the only person to submit "NetWorker". They must have picked mine either because I submitted it first, or my commentary was so compelling. :-)

I NEVER win contests, so I was absolutely amazed to get that email. By being a finalist, I already won the Calisto Pro. This amazing phone can connect to your land line, cell phone, and even VOIP services. I'm very happy to be a finalist, but I would love to be the grand prize winner. The grand prize consists of an iPod Touch, Altec Lansing home theater system, moondance glow alarm clock, expressionist bass computer speakers, and the ultra-portable "Orbit" speaker. The total value of this package is over $1700. If you have a spare moment between now and November 7th, I'd really appreciate if you could go to the Plantronics contest site and vote for the term "NetWorker". If you don't think it's the best replacement for the word "teleworker" that's fine. I won't be mad if you vote for something else. I'm just hoping to drive a little more real traffic to the site to make it more difficult for anyone to game the system.

And, to help Plantronics fulfill their real reason for this contest, you should check out the Calisto Pro. It really is a fantastic phone.

Foxmarks with Password Sync

I recently upgraded to the new version of Foxmarks with "Password Sync", and it's so great, I find it worthy to tell you about it. Note, this is for the Firefox browser only. With the new Password Sync option in Foxmarks, you can save your passwords to their network and use them on any computer where you've installed Foxmarks.

There are many other password managers out there, but most are simply an encrypted list of your passwords. If you want to log into a site, you have to look up your saved password and enter it manually. Not only does Foxmarks sync your bookmarks across any number of computers, the new functionality of Password Sync automatically inserts your saved passwords when you visit an applicable site.

There's not much else to say, other than...Install Foxmarks with Password Sync Now!

Outlook Attachment Reminder Macro

How many times have you written an email about "the attached file" only to forget to attach the file before hitting send? If you follow the instructions in this post, that'll never happen again!

We're going to add a VBA macro to Microsoft Outlook that searches your email for the text "attach" when you click "send". If it finds that string of text (ie. attached, attachment...) it will check to see if you actually attached a file. If there's no file attached, you'll be prompted with a message box. Then, you can either continue without attaching anything, or you can stop the email from sending so you can include your file. Yes, it's that simple.

*Note: This will not work in Outlook Express as it doesn't support macros.

How to set it up:

1. Open Outlook.
2. Press Alt+F11 : This will open the Visual Basic editor.
3. Expand the project until you find "ThisOutlookSession" and select it.
4. Copy the code below into the Visual Basic code window.
5. Save.

Now to test:
6. Close & Reopen Outlook for good measure.
7. Write an email containing the word attach.
8. Click send. *This is when you should get the pop-up.

*Note: If you have an image in your email signature, that would count as an attachment. Just change "intStandardAttachCount" from 0 to 1.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
' Pops up a reminder if the word "attach" is found but there is no attachment on your email.
Dim m As Variant
Dim strBody As String
Dim intIn As Long
Dim intAttachCount As Integer, intStandardAttachCount As Integer

On Error GoTo handleError

'Edit the following line if you have a signature on your email that includes images or other files. Make intStandardAttachCount equal the number of files in your signature.
intStandardAttachCount = 0

strBody = LCase(Item.Body)

intIn = InStr(1, strBody, "original message")

If intIn = 0 Then intIn = Len(strBody)

intIn = InStr(1, Left(strBody, intIn), "attach")

intAttachCount = Item.Attachments.Count

If intIn > 0 And intAttachCount <= intStandardAttachCount Then

m = MsgBox("It appears that you mean to send an attachment," & vbCrLf & "but there is no attachment to this message." & vbCrLf & vbCrLf & "Do you still want to send?", vbQuestion + vbYesNo + vbMsgBoxSetForeground)

If m = vbNo Then Cancel = True

End If

handleError:

If Err.Number <> 0 Then
MsgBox "Outlook Attachment Reminder Error: " & Err.Description, vbExclamation, "Outlook Attachment Reminder Error"
End If

End Sub

Also, LifeHacker has a script for GreaseMonkey that does the same thing for GMail.


Credit for this Outlook macro goes to Mark Bird. I've seen this macro a few times on the internets, but I believe he's the original creator. You may also find some answers to any questions on his site. Thanks Mark!

There you go! Now anytime you mention an attachment in your email, you'll be sure to have it attached.