How to Compare Two Lists in Excel

Knowing how to compare data from two different columns in Excel using the VLOOKUP feature is something that can be super useful for internet marketers or anyone who deals with data regularly. The other day I did some competitive research to gather backlink data from various websites, many of which had backlinks from sites I’ve contacted in the past. I wanted to make sure not to contact these sites again, so I needed to weed out the URL’s that matched in both lists.

I’m on a Macbook and using VBA code isn’t available in Excel for Mac, so I had to find a formula, which I did after a long search. Start by putting the longest list in Column A and the shorter list in Column B. In Column C enter =VLOOKUP(A1,B:B,1,FALSE) and copy the formula down to the last cell where data ends in Column A.

A1 = The cell being compared
B:B = The set of data Column A cells are being compared to
1 = Tells the formula to return a result from of data matches
FALSE = Tells the formula to return an exact match

Tip: If you’re using this to compare deep URLs but need to compare to domains, use Ontolo’s URL & Hostname Counter, set to Count Hostnames and click the button. Copy to Clipboard and paste into Excel.

My Review of Affcon 2010

I attended Affcon 2010 several weeks ago and I’m finally getting around to putting my thoughts about it on my site. This is the first time I’ve ever been to an affiliate specific conference, but I have been to a couple of internet marketing conferences before such as SES and Pubcon.

The conference took place over three days, the first two being free for all affiliates and the third day being a paid workshop day. Affiliates are sponsored by the various affiliate networks at the show in order to attend but there was a page for people to pay for passes too. Everyone I talked to there got in free, so I’m not sure who would have had to pay to get in, but as long as I got in at no cost, that’s all that matters πŸ™‚

I didn’t actually network with many people because I was still trying to work during the short breaks between sessions when I could so that I wouldn’t fall too far behind at my job, but it seemed to me like most of the attendees were actually small business owners or people who were just getting into affiliate marketing. The crowd seemed to be fairly basic, except for one guy who kept raising his hand to tell the crowd about Gary Vanerchuk and his huge success online. It was probably Gary himself, who knows.

The Sessions and the Presenters

The very first session of the conference started with a keynote from Joel Comm. I’m not normally a fan of keynotes and I had originally planned on skipping it, but I’m glad I didn’t because it was really good. Joel Comm is a great speaker and kept things interesting the entire time.

I was actually really excited for the keynote on the second day too, because I knew it was going to be Shoemoney talking. To my disappointment, it was Shoemoney asking questions of Aaron Baker of Atrinsic Interactive. It wasn’t bad, but I would have preferred it be the other way around. They covered a lot and answered a lot of interesting questions related to affiliate marketing. Aaron Baker kept getting really quiet at the end of each sentence so that kinda sucked. He starts loud and gets quieter and quieter as he speaks.

I saw Shoemoney out-and-about with other conference-goers after his keynote too, so that was really cool of him. That’s one affiliate marketer that you could learn a ton from, I’m sure.

Tim Ash also had a really good session about conversion optimization (his specialty) on the second day. He’s just as entertaining as Joel Comm was, so that session was a lot of fun. He also mentioned a whole bunch of really cool tools to use so I think everyone got a lot out of that one. One guy even got $20 just for answering one of Tim’s questions.

Jordan Kasteler of Search and Social (now Blueglass Media) had one of the best and most interesting presentations I’ve seen at any conference. He went through lots of ideas and tactics and wasn’t afraid to talk about black/grayhat stuff. That’s rare. Most people are afraid to let anyone know that they’ve even dabbled in the grey hat side of marketing.

I Would Have Liked to See…

While there was some talk about the Colorado affiliate tax (in Shoemoney’s keynote and various other sessions), nobody really seems to completely understand it yet. This is definitely something that affiliates need to gain knowledge on still, myself included. There actually was a session about this but from what I heard, even the CO. State Representative on the panel didn’t really get it. Sad πŸ™

One session titled “Building The Best Model For Your Affiliate Business” was the best session I attended and was probably the first one that focused almost entirely on affiliate marketing. The discussed in-depth the various business models available to marketers such as membership sites, email marketing, review sites, etc. I really learned a lot from this one.

Too be completely honest, a lot of the sessions were pretty boring. Most of them were presentations that were about SEO and didn’t really relate to affiliate marketing at all. I understand that affiliates need to do SEO too, but I was really hoping for this conference to hit on advanced topics in the affiliate marketing industry. Maybe I just made bad choices in the sessions I attended.

There is one session that I really wish someone would have covered. I would’ve loved to see a session about managing taxes, payroll, income, etc. as an affiliate marketer. I’ve never seen one presentation on something like this, perhaps due to legalities of the topic, but I think even general info would be helpful to a lot of marketers.

One last gripe. There were no freakin’ plugins in the session rooms!

Anyway, I had a lot of fun at Affcon 2010 this year and hope to go again next year. I think I’ll choose sessions that are the opposite of my first choices. Just like George Costanza did to get his job at the Yankees.

Fortitude Online Mag Getting a Reboot

Turns out that new memberships at Fo.rtitu.de have come to a halt, which you can see from that link that they haven’t had a new signup since May 21st. This is requiring that they take a look at their processes and figure out what needs to be done to keep the online magazine going, meaning some changes are going to take place as of today.

The previous algorithm they had in place didn’t allow many articles to reach the front page, which made a lot of people hesitant on submitting their articles. If you put a lot of work into an article and it never hits the front page, you don’t get paid…much. To remedy this, they are now increasing front page chances to 50% of articles submitted. That gives you much better chances to get visibility and cash.

I think this is a great idea and is sure to get me posting more as well.

I originally signed up for a pro account at Fortitude to get paid for writing, and the strict review process sounded like something that could help me strive to write higher quality content. This is still true for me, but now I see it more as a way to get more visibility to the sites that I link to in my articles. Although the nofollowed links provide little or zero SEO value, they can still drive traffic to the sites of clients or even a few of my own.

This means that from now on, I’m going to look at Fortitude not as a place to make money, but a place to drive traffic to my sites. Because of the registration fee (which I’ve already earned back by the way), only quality articles make it to the front page of the site, so your article isn’t going to get shuffled in with a bunch of crap.

I only have one complaint about Fortitude right now, and that’s the character limit. For some people 3,500 characters isn’t a lot, but this depends entirely on what you’re writing about. For example, they state that they take poems, but how many people write 3500+ word poems? Do you know how many characters this post has right now? 1,672. I’m not even halfway to the requirement.

A quality article should not be judged by the amount of characters it contains. I typically write articles that come out to be around 2,500 to 3,000 characters. It will have everything I want to say and I will not fill it with fluff to meet Fortitudes requirements so most of the time I submit my articles elsewhere or on my own sites. Which is still fine with me, but bad for Fortitude.

I really like the business model that Fortitude has put in place and hope they are able to keep going. There is a lot of really good stuff on there right now from all sorts of different niches, all very linkable. Maybe they should invest in an SEO company who can help them create a marketing strategy for the user generated content everyone is creating for them πŸ˜‰

If you want to sign up to Fortitude, please do with my affiliate link. I’d appreciate it very much!

How Does Indexing and Ranking Work at Google?

The way Google previously worked was by crawling the web for a period of time and then pushing those updates out every so often. For example, they would crawl for 30 days, take down an old data center and then push out a new datacenter in it’s place. This is what caused the “Google Dance” that would cause people to become frantic in the forums because their rankings would be jumping around all over the place.

Here’s a great video explaining that and a lot of what the rest of my post is actually about. There is a ton of great info from Matt Cutts here, so watch it if you have time:

There are a few key points that I’ve gathered from the video above.

First is that Google’s “Caffeine” update was created to index the web much more rapidly. Instead of waiting 30 days to push out an update, Caffeine will find a page/document and put it immediately through indexing. This means that now lots of documents can be crawled and pushed out onto the web within a matter of minutes.

Matt Cutts also mentioned a few ranking factors that Google specifically looks for. They are already commonly known, but worth mentioning again.

  • Languages of content
  • Languages of linking content
  • Incoming links
  • Comments
  • Anchor Text
  • Page Rank

So basically what Google does is it after a page enters the indexing phase, it figures out what’s attached to that document – what links are going to it, the anchor text of those links and page rank coming in. It will find all of the signals from various sources and attach it to the page, which will then determine where to rank it.

By the way, I’d like to mention that when Matt Cutts mentions Page Rank, he’s not talking about toolbar PR, but instead the internal Page Rank known only by Google.

The factors mentioned above are only six of the 200+ ranking factors that Google uses. I would suggest you check out SEOMoz’s Search Engine Ranking Factors 2009 for much more thorough list of factors and their importance as voted on by top SEOs in the industry.

Also, note that with the Google “Mayday” update that just recently went into effect, keep in mind that it’s not just about ranking factors anymore. Pages that are low-quality, have small amounts of content or are seen as “thin” by Google are being weeded out of the index. I’ve already seen several instances of this happening with lots of sites, mainly in e-commerce or affiliate marketing. It’s common for e-commerce and affiliate sites to be light on content or containing only short reviews or common product descriptions, so consider adding some valuable content now before you take a hit.

After you’ve added value to your sites, then you can start to think about ranking factors.

The 30 Day Challenge: The Good, The Bad and The Ugly – Part Three

The Ugly

This is the third and final part of my series on The Thirty Day Challenge: The Good, The Bad and The Ugly.

I’ll admit that I’ve done the Thirty Day Challenge for three years straight now, despite what the Wickedfire forum thinks about it πŸ˜‰ I also admit that there are some things worth learning throughout the 30DC. Each year I learn something new, even if it is only one or two things out of the 50+ videos you have to watch. For me, the 30DC is a good refresher course of the basics.

It can be a good place to start for anyone who has never done internet marketing before. I’ve actually recommended it to a few good friends of mine who have been interested in getting into internet marketing, BUT with a few warnings.

First….

DON’T BELIEVE EVERY WORD THEY SAY

The whole 30 Day Challenge crew is great at marketing themselves as “gurus”, but they’re not. Don’t get me wrong, they are good marketers and they do know their stuff. The problem is that so many beginners jump into marketing without anyone else to turn to, join the 30DC and follow it word for word like it’s the “Good Book.”

I like the Thirty Day Challenge, but I don’t like everything they do.

I’ve found year after year, that they push an idea on participants as if it’s proof positive and then the next year have to explain that something went wrong with the tactic they showed you which was completely “unforeseen.”

DUDE! How did you not think spamming the hell out of Tumblr and HubPages and Squidoo wasn’t going to get your mini-site taken down? There are wrong and right ways to do things in internet marketing and the Thirty Day Challenge rushes through it too fast to get everything right.

One thing that I find funny is how the whole crew will act like they do the 30 Day Challenge for free each year out of the kindness of their hearts. While this may be true to some extent, there are other reasons for it. If you don’t see those ulterior motives, you haven’t been in internet marketing long enough.

Once you sign up, you are on their list. You will get lots of emails from Ed pushing his products or other internet marketing “teachers” products and services in your inbox regularly. He DOES explain that the links are affiliate links, so I guess that makes it a little better. At least it’s a good marketing tactic that builds trust within his list. I’m not saying what he does is a bad thing either. They all deserve to be compensated for their work, but they should be straight forward with the challengers. Actually Ed mentioned something about this in an email he sent out a couple of days ago, so maybe he’s trying to be a little more transparent for the upcoming challenge.

If you follow through with the challenge, you’ll also notice that they like to push costly services almost as if you’ll fail miserably in marketing if you don’t buy them. Take for example Traffic Bug. A service that basically spams social networking, bookmarking and directory sites and places your sites or minisites, articles, etc. into completely irrelevant directories. I have a feeling Traffic Bug is behind a lot of the “technology” spam posts hitting Sphinn’s recently submitted section.

The other big product they push within the challenge is Market Samurai. To be fair, this really is an awesome piece of software. It does a whole lot stuff and makes research and writing content easier. The 30 Day Challenge crew leads you to believe that this thing will be the deciding factor in making you a millionaire. I’ve never heard so much praise for some keyword research and link analysis software. It does cool things and is worth the one-time price, but don’t you think you’re laying it on a little thick guys?

When the Thirty Day Challenge is over, you finally get to see why they did it all… Thirty Day Challenge Plus. You can sign up for about $30 or so per month and can cancel anytime. Now it starts to make sense doesn’t it? I’m completely fine with this. This is how this type of marketing works. They have to make money and put food on their tables too right? But, what I’m not OK with, is that they continuously deny there is another reason for having a free month of affiliate marketing training, other than that they are just some nice guys.

What really set me in motion to write this post was a video they put together at the very end of last years challenge, telling people that they now have the knowledge to be SEO consultants.

I don’t like to see novice marketers taught how to spam or “half-ass” their way into a career. I didn’t like how they put out a video telling all of those people that they now have enough knowledge to go out into the world and become SEO consultants after 31 days of training. This is why people think SEO’s and Internet marketers are snake oils salesmen. I’ve been doing internet marketing professionally, not just in my free time, for about 5 years and I still don’t call myself a “guru.”

If you’re Ed Dale, “Guru” Bob, Caro, Dan Raine or any of the other members of Thirty Day Challenge, I hope you don’t take offense to this post although I completely understand if you do. I don’t mean to be so hard on you all. I’ll even continue to recommend the challenge to other people, but it’ll come with my own personal warnings.

See you tomorrow when the next 30DC, I mean “The Challenge”, kicks off!!!