August 2010

Excel Macro to Generate SQL Insert

by Jason Green on August 28, 2010 · 9 comments

I am in the process of creating a database of County information, and I needed to insert several thousand records. I was able to get the data into Excel, but now I needed to write an SQL Insert statement to get the records into my database. SQL wizards, I’m sure you know a more efficient way to write this INSERT. Actually, what is generated is a series of INSERT statements. If you’d like to share your knowledge, please leave a comment, and I’d gladly update the macro for your improved format.

How it works:

Let’s start by looking at my spreadsheet:

  • I’m using Row 1 as my header.
  • The values are: state, county, statefips, countyfips, and fipsclass
  • Beginning on row 2 I list that information.
  • Columns A, B and E are text.
  • Columns C and D are Integers. ***Note this in the macro when modifying.***


Upon running the macro, it will ask for the start row. Enter the number for the row that begins your data.

It then loops through columns A through E and puts those values into variables.

Then, it writes the insert statement, with our variables included, to the file specified by the “MyFile” variable. Currently, it will be written to the active/default Excel directory.

The macro will then loop down all of the rows until it finds a blank.

This macro is fairly short, so I’m copying it below.
I just realized that there are several things I could change to optimize this code and make it more useful. I’ll try to get an update out soon. Until then, what would YOU change in this code?

Sub generateInsert()

Dim state As String
Dim county As String
Dim statefips As Integer
Dim countyfips As Integer
Dim fipsclass As String
Dim myRow As Integer
Dim myCol As Integer

myRow = InputBox(“What row to START on?”)
myCol = 1
MyFile = “insertStmt.txt”

‘set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

Do Until ActiveSheet.Cells(myRow, myCol) = “” ‘Loop until you find a blank.

state = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
county = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
statefips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
countyfips = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
fipsclass = ActiveSheet.Cells(myRow, myCol)

myCol = 1 ‘ Return to column A
myRow = myRow + 1 ‘ Move down 1 row

Print #fnum, “insert into countyTable (state, county, statefips, countyfips, fipsclass)”
Print #fnum, “values (‘” & state & “‘, ‘” & county & “‘,” & statefips & “, ” & countyfips & “, ‘” & fipsclass & “‘);”

Loop

‘ Close the file
Close #fnum

End Sub


I’m looking forward to your comments.

- Jason

Test Your Google Analytics IQ

by Jason Green on August 27, 2010 · 0 comments

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!)

*** Update: Jason Green is now a Google Analytics Qualified Individual. ***

I Love Google But…

by Jason Green on August 27, 2010 · 0 comments

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

by Jason Green on August 27, 2010 · 11 comments

A wonderfully powerful and easy to use way of comparing data across Excel worksheets 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.

If you still have questions, please leave a comment. I’d be happy to provide additional clarification if needed.

Outlook Attachment Reminder Macro

by Jason Green on August 27, 2010 · 24 comments

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.

Cuil Doesn’t Measure Up

by Jason Green on August 27, 2010 · 0 comments

Well, it’s pretty obvious that they hype over the new search engine, Cuil, was far greater than what the site could actually deliver.

I won’t repeat all of the reasons they won’t be the next Google killer. If you’re reading this, you’ve surely read about them all several times already. Instead, I did a semi-scientific test of their search results. Since they claim to be the largest search engine in existence, they should have the highest number of results for any given search term. Right???

I chose several words at random as they popped into my head. I searched for these terms on Google.com, Yahoo.com, MSN.com, and Cuil.com and recorded the number of search results. For Cuil, the findings are probably not what they were hoping for. Without any more chit chat, let’s get to the charts and graphs!

Search Test Report

From the chart above, you can clearly see that Yahoo! dominates as far as search results go. We’re not talking about relevancy, we’re only interested in number of search results returned. Google could be considered second, beating MSN and Cuil most of the time. Surprisingly, MSN had the lowest number of total results throughout the entire test. (Even lower than Cuil) Something odd were the search terms where Cuil beat out all other search engines, even Yahoo!.

  • “asp.net books” – Cuil: 602,186,415 / Yahoo!: 26,200,000

  • “google knol” – Cuil: 38,607,628 / Yahoo!: 15,400,000

  • “make liquid soap” – Cuil: 127,604,750 / Yahoo!: 12,500,000

This may not be the most scientific test, or be the definitive demise of Cuil, but if they’re the largest search engine in the universe, why don’t they give me the most search results??? Please let me know what you think in the comments.

Raw Search Data

microsoft cuil google yahoo
yahoo 299000000 546032790 2930000000 7020000000
money 638000000 1727246159 1310000000 4620000000
google 189000000 516386388 2750000000 4510000000
web development 532000000 13374732 190000000 4060000000
real estate 291000000 640803910 707000000 3160000000
cars 225000000 814511255 855000000 2540000000
microsoft 237000000 520578288 907000000 2070000000
facebook 20800 15832213 406000000 1590000000
american business 646000000 1917726 98500000 1520000000
myspace 3080000 132450851 861000000 1510000000
internet access 764000000 32763765 176000000 1020000000
batman 25700000 40819411 102000000 469000000
ferrari 68200000 67216311 162000000 335000000
porsche 77000000 75242044 141000000 321000000
linkedin 70300000 5704636 147000000 310000000
britney spears 83000000 136397691 105000000 214000000
foreclosure 16700000 71997961 48100000 198000000
lamborghini 13700000 17650898 62700000 193000000
visual studio 123000000 25181007 43500000 165000000
php books 218000000 81590 23200000 150000000
spiderman 22500000 18529699 50500000 149000000
hacking 142000000 148071687 53000000 144000000
superman 21100000 34224020 61800000 104000000
jason green 19300000 49203 3600000 99000000
riaa 3490000 6881174 8210000 30600000
asp.net books 5080000 602186415 7920000 26200000
led flashlight 4180000 1071520 2510000 25200000
bike commuting 977000 61059 671000 16600000
google knol 6350000 38607628 904000 15400000
excel macro 3200000 88872 649000 13400000
make liquid soap 4620000 127604750 751000 12500000
volcano eruption 2560000 136101 2440000 8390000
johannes brahms 5510000 2119690 4110000 7290000
cuil 71500 124737 1540000 5910000
monty python holy grail 1010000 1898341 412000 4780000
dns cache attacks 2360000 703280 220000 4400000
fryderyk chopin 5200000 1101294 1440000 1270000
grocery shrink ray 164000 434466 59000 777000
Total: 4769373300 6386083562 12225736000 36643717000

Google Analytics 101

by Jason Green on August 27, 2010 · 0 comments

I just came across a simple yet useful post on the Google AdWords blog about Google Analytics.

If you’ve installed Google Analytics and you don’t know what to do next, their post should help. Basically, just look at the major metrics and watch them over time.

The main thing to keep in mind is that there is no “good bounce rate” or optimal time on site for everyone. Each site, and even each section within a site will have different values. The key is to look at trends over time.

Also take a look at our Intro to Google Analytics post for a new video.

Microsoft Gatineau – Google Analytics Killer?

by Jason Green on August 27, 2010 · 1 comment

Is Microsoft Gatineau ready to strip Google Analytics of the web analytics throne?

I’ve been using both Microsoft Gatineau and Google Analytics on one of my sites for about 3 months. I think that’s plenty of time for me to give a fair analysis of both packages.

*Note that I am much more experienced with Google Analytics, so that might have some effect on my opinion. However, I’m trying to be as neutral as possible in this review.

1. User Interface / Usability:
Winner: Google Analytics
Why?: Google is king when it comes to user interfaces. (Not starting off so neutral, are we?) When I log into my Google Analytics account, I’m presented with a very useful dashboard. If the information I need isn’t there by default, I can easily add it. On the other hand, Microsoft’s Gatineau gives me a 3-layer calendar that allows me to select a date range by day, week, or month, with a single click. This might be useful if jumping from one date range to another was a frequent activity, but so far, it’s not. This date-range picker takes up 4 inches above the fold on EVERY report! The two inches below that is a description of the report you’re viewing, and below that is the graph for the report you chose. Unfortunately, half of it is below the fold!

Navigation through reports is done through a folder structure.

This isn’t awful, but getting to the next level always requires 2 clicks. One to select the item and display the menu, and another to choose between demographics or diving a level deeper.

2. Useful Information
Winner:Google Analytics
Why?: This would be a tie because they both have all standard web analytics information, except Microsoft makes it so much harder to view the useful information. Sorry to go back to usability, but with the GA dashboard, I can view my most useful information on one screen. With Gatineau, it takes 12 to 20 clicks and 6-10 page loads just to get the same information.

3. Demographics
Winner: Microsoft Gatineau
Why?: Because Google doesn’t have it. However, with this being Gatineau’s claim to fame, I’m unimpressed. Over the past 3 months, I only have demographics information on about 20% of my visitors. Sure this information is useful for some things, but with 80% being unknown, I really don’t know much more than I did before.

Overall Winner: Google Analytics!

Who didn’t see that coming?

I was really hoping to write some good things about Microsoft Gatineau, but until they improve the user interface, I won’t be leaving Google Analytics any time soon. Also note that Gatineau hasn’t been officially released yet. There are some features which haven’t even been activated, such as the emailing or exporting of reports. However, the framework of what’s to come is there, and I have to say, it’s no Google-killer.

I’d love to hear from other Gatineau users. Is there anyone out there that would choose Gatineau over Google Analytics?

Even though GA won this battle, I’d still encourage you try out Microsoft Gatineau and Google Analytics to decide for yourself.

I look forward to your comments.

- Jason Green

How to Monetize Social Media Traffic

by Jason Green on August 27, 2010 · 1 comment

You click “I like it!” in your StumbleUpon toolbar to promote your own work. Is it spam? Maybe, but let’s look past that for now. Let’s instead assume someone stumbled your site on their own.

Buckle up, because you’re about to get 500 free visitors to your site. I’ve even seen some pages get over 1000 visits!

This is really exciting, but the fact is that you’re not going to make a dime off of them.

So how do we make money from social media traffic?

1. Made For AdSense : NO!
Social media visitors are far too savvy for an MFA (Made For AdSense) page. If you create some stupid list and paste AdSense ads all over the place, you can be damn sure no one will be clicking them. You’ll get a thumbs down faster than your page can load.

2. Hock an Affiliate Product : NO!
Stumblers are not looking to spend money. They’re taking a break to browse the Internet. If they see that you’re just trying to sell them something…thumbs down!

3. Sign up for a Paid Membership to your Site : NO!
We’re talking about instant gratification with these visitors. You have 3 seconds to grab their attention. This offer involves giving you my information before I get anything in return. I’m gone.

So what’s an internet marketer to do?

Social media loves free online apps. Develop a killer Web 2.0 site, with an equally killer web application if you want to make any money from your social media efforts.

Did I mention it needed to be free???

You can’t sell directly to these visitors. You need to give something away for free, and get them to come back later to give you their money. They begin using the free version of your web app, but for $X per month, they can have all these premium features. This is where it’s at!

Oh, I forgot to mention that it wouldn’t be easy. If you’re just looking for pizza and beer money, you might be able to get by with a made for AdSense page, but if you want to make some real money, you actually need to have a good idea, and put a decent amount of work into it.

Am I wrong? What works for YOU?

*** Update – 3/5/2008 ***
An example of what I mean by “Web 2.0 App” is Squarespace.com.

I actually just stumbled upon this company.

What you need to mimic to make money with social media:

1. Their homepage has a big “sign up for free” button.

2. You still have the option of browsing their entire site. You’re not locked into the free option if you’re already ready to buy.

3. They offer many service plans at a wide variety of price points. ($7 – $175)

4. Clean and slick style. If your site looks sloppy, I’m gone.

5. Tells me what they do and who would need them. (They’re hoping YOU do.)

6. Incredibly easy signup process. You only need to provide a login, password, and email address.

Note that I’m not affiliated with this site in any way. This is just a perfect example of what I was trying to convey. StumbleUpon and other social media is a great way to expose new users to your offer. You just need to have a kick-ass offer if you want to get any bites.

Google Analytics Data Sharing

by Jason Green on August 27, 2010 · 0 comments

Upon entering my Google Analytics account this evening, I was presented with a new request.

Google Analytics Data Sharing:

“In order to improve your experience with Google products, Google Analytics is updating its data sharing policy. You now have the ability to share your Analytics data with other Google services. This will improve integration, enable additional features in Google’s advertising services (including Google Analytics, AdWords and AdSense) and improve your experience with these products.”

The big question is: What do I get in return?

I’m all about open source and open information, but they’re not really selling me on this one. If you go by the explanation above, it sounds like I get to improve Google. They can do this without me, I’m sure. There’s also a bunch of fluff about improving my experience with their other services. “Additional Features”? How about a link?

My fear for this new “feature” is that it will be accepted by all of the less savvy Google Analytics users, while the businesses that took the time to set up their analytics accounts properly will decline to share. If that’s the case, how valuable will the data be?

The Google Analytics Blog has a good writeup of what this is all about. It would have been nice if there was a link to this post from the opt-in message so I could check it out before sharing my analytics information.

This might actually be useful, but I think it will take a month or so before we have anything useful to compare.

I’m very interested to find out if anyone else is using this feature. If you’ve enabled the benchmarking feature and have gleaned useful information from it, please share it with us.

What is everyone else thinking about sharing your Google Analytics information with others?

← Previous Entries