Excel 102 (Excel for Noobs in GIFs!)

This is the next step in the Excel for Noobs series. If you missed the first one, check out how to make format your data, organize it in a table and create a simple bar graph. This tutorial will go over conditional formatting!

beforeafter

Let’s start with this first step, making it pretty. Just as I said in the last post, making your data pretty is essential for readability and clear communication to everyone who isn’t an excel nerd.

In my last post I went over a few simple ways to make your data prettier. Here is the list of items, but feel feee to referenced my last post if you don’t have these committed to memory yet. Hide gridlines, delete or hide unnecessary data, give the document a title and add a 10px cell padding in A1.

Everyone Loves Colors

wind

Conditional formatting is a quick and easy way to visualize the data you want to show using color coding. There are many different ways to use conditional formatting. So many, that it could merit its own blog post. But today, we’ll focus on simple cell coloring. We’re going to take the ranking data from your AuthorityLabs export and color code it with green and red gradients to show improvements or losses in a month of rankings.

To do this you will need to set up your export as I’ve set mine up. You can download what I’ve done so far in my document.

basicformatting

formatting: hidden gridelines, basic table, A1 cell padding

So, let’s start painting.

You’ll want to start with F4, this cell is what we will be comparing the rankings to. The whole idea is that we want to see if the ranking for that keyword has gone up, down, or stayed the same in the past month.

Start by selecting F5.

Under the Home tab select the Conditional Formatting> Highlighted Cell Rules > Greater Than…

 Next…

Select the cell E5 and get rid of the dollar signs so that it is a relative selection. This way we can drag down the formatting to the other cells.

Then change the formatting.

Select Format and under the Font tab select red.

Next, you will add two more Conditional Formatting rules for this row of data.

One will be Less Than which is the color green, and Equal To… which is the automatic black text formatting. The final product will look like this.

Here’s the easy part!

With the cell F4 selected, click the Formatting Painter which is under the Home tab. Then drag the Format Painter down your column of cells. It should look like this:

The final touch is to add a pretty gradient behind the formatted cells.

This is makes the data really pop. To do this, select that table column (4/1/2014) and open the Conditional Formatting > Manage Rules. In this dialog box you can select a rule by clicking on it.

Then, click on the Format button to get the formatting options.

Under the Fill tab select Fill Effects. From here I selected a darker and lighter shade of red and green to create gradient backgrounds.

step7

Now time for a victory dance!!

You have successfully used Conditional Formatting to show the behavior of keywords over the past month. Stayed tuned for more Excel geeking out in the next post!

mission accomplished

Want more Excel help? Join our first AuthorityLabs 101 Wednesday hangout on April 30th! You will be able to ask me questions about Excel tabling and graphs.

 

Unleash The Power Of Gmail On Your Business In 10 Easy Steps

Power

Let’s face it, each time you hear “You need a blog,” “All your competitors are on social media,” or “Google’s latest algorithm…,” your eyes glaze over and you wonder, under your breath, “When will I have the time to worry about any of this stuff?”

We feel your pain. As a small business owner with too much to do, squeezing more tasks onto a to-do list that already looks like Lindsay Lohan’s rap sheet is not going to happen.

But, what if we gave you a simple way to more effectively use a tool you already have at your disposal? What if we told you the tactic would take less than 10 minutes a week, but has been shown to be one of the most effective content marketing ideas and applications? Interested?

We thought so.

Using Email the Way It Was Meant To Be Used

The tool is email. (Stop rolling your eyes!) But the tactic we’re referring to is using it as a relationship-building growth engine for your company.

Everyone uses email, but not everyone uses it to its full potential. Most use it out of necessity: to correspond with co-workers, employees or vendors; to touch base with friends or family members; or to contact a prospect for potential work.

What few of us do, however, is use email to grow the business with our existing client base, by using the medium as a touch point to enhance rapport and build top-of-mind awareness.

Gmail’s “Canned Response” function allows you to create and save common messages that can be shared later to different parties, averting the need for separate emails for the same or similar audiences.

For example, say you always send a personalized note to your main vendors at the first of each month, but you’d rather not have to blind copy everyone. Using canned responses, you can save messages for each group and then send them at your leisure, only needing to change the name and the email address each time.

There are numerous benefits to using Canned Responses

  1. Saves time from having to send separate emails.
  2. More personal than blind copying everyone on an email.
  3. Makes it possible to stay connected with important parties without a huge investment of time.
  4. Is one of the most effective but underutilized methods of marketing for businesses.
  5. You can save messages for any and all audiences.

The Nuts And Bolts Of Canned Responses

All you need to get started is a Gmail account. Follow these simple steps:

1. Log in to your Gmail account and click on the sprocket on the right side of the screen. (This is the Default Inbox view.)

 Gmail Canned Response

 2. You’ll then get a drop down menu. Click the “Settings” button.

Gmail Canned Response

3. You’ll then be taken to the “Setting” page, where you’ll find and click on “Labs” at the top of the page.

  Gmail Canned Response

 4. Click to “Enable” Canned Responses.

 Gmail Canned Response

 5. Save your changes at the bottom of the page, and you’re almost done.

Gmail Canned Response

6. Hit “Compose” and you’re cooking with grease.

 Gmail Canned Response

7. Type up your message, leaving the “To” “Subject line” and “name” blank in the email. Again, the purpose of using a canned response as opposed to a “Blind Copy” email is to create personalized messages for people without having to create each one separately. So once the message is created, all you have to do before sending it is adding name, “To” and a subject.

I created the message below for retailers I worked with, but you can create one for each group of vendors you work with frequently and want to build a rapport with. Such emails take 5 to 10 minutes to craft but are worth their weight in gold.

 Once the message is complete, click on the upside-down triangle at the bottom of the message.

  Gmail Canned Response

 8. Click Canned Response on the bottom right, then you’ll see a new dropdown menu open above it. Click “New Canned Response.”

  Gmail Canned Response

9. You’ll get a prompt to name the message. Click “OK,” and you’ve just created your first Canned Response.

 Gmail Canned Response

10. The next time you compose a message and click on the upside down triangle, you’ll see your message along with options to insert, delete or save it. To create a message using a saved response, simply click “Insert.”

Gmail Canned Response

Using Email Smartly to Grow Worthwhile Connections

Now that I’ve bored you to tears with the setup—which, honestly, takes less that 45 seconds—l want to share how you can use such messages to set your business apart.

Everyone talks about texting, social media and whatever app they use as a business hack. But you know what? Your customers and clients crave a human touch more than anything. They don’t have time to read lengthy emails or to field 20-minute phone calls.

However, they do have time to read three lines of text. Also, they want to do business with people who take the time to form a connection with them, who reach out to them at times other than when there’s a sale to be made. What’s more, you owe it to yourself and your business to make the time to craft such messages, even if it’s only one a month.

Greasing the Skids

When I worked as a B2B magazine editor, 60 percent of my job was “sales”, which included staying in touch with clients, greasing the skids for my sales team. So each day, without fail, I’d send a minimum of two personal notes to vendors, clients and sundry people in the industry. Many of these folks were never going to do business with my company, but that didn’t matter. My goal was to ensure that no matter what company my salespeople called on, the person on the other end of the phone already knew they had an ally looking out for them and their business. It worked.

I once had the executive vice president for a client representing $2 million in business say to me, “I want you to know that the only reason I do business with your company is you. You go out of your way to help the people of this industry, and we want to support you.”

And it’s not just me that’s had success using this technique. I’ve interviewed some of the most successful sales people on the planet. Their secret? They never have to “sell” their existing client base. They spend the bulk of the year relationship building, so when it comes down to do business any impediments have long since dissolved.

Give Canned Responses a try in this way. I’m convinced they can work for you as well.

6 Ways You Should Be Using Ranking Data in AuthorityLabs

So you’re tracking keywords using AuthorityLabs and loving the reporting capabilities that we give you, but just aren’t quite sure of all the different ways you can leverage the interface. We’ve thrown together a quick list of ideas to help improve your rank tracking efforts. Take advantage of these tips and you will end up with better, more actionable data while also saving yourself countless hours of time.

Break Up Keyword Lists for Easier, More Focused Tracking

In AuthorityLabs, you can add the same domain or URL to track more than once. We actually recommend that you do because it allows breaking down of keyword lists into smaller, more manageable sets.

Let’s say you have a team of people working on the same site. Each team member has a topic they are focused on and those topics each have a set of keywords that are being targeted. You don’t want to mix all of those keywords into the same list because it potentially hides issues with some of the topics and you’ll waste time digging through long keyword lists trying to figure out what’s going on. Even worse, long keyword lists become unmanageable and are more likely to be ignored.

Split keyword lists

Another good reason for breaking down your keywords would be if your focus changes from week to week or month to month. Did you focus time on one section of your site in January? Add a new copy of your domain or URL with the different set of keywords you focused on and now you can see how January’s efforts progress. Do this for every week, month, or custom time period that you’re breaking efforts into. Tag the domains in our interface with the name of that time period and now you can quickly gather insights on how your work is paying off or where improvements can be made.

Monitor Different Cities or Zip Codes

As SERPs become more personalized based on location, it’s becoming increasingly important track results at the city or zip code level. When adding a domain, you have the option to search and choose a city or zip code that will be set when pulling data.

Make sure to track the most important locations, at a minimum. Add the domain once for each location and then group the domains. If you want to share the same keyword set for all locations, sync the domains and then add your keyword list to one of them. The system will automatically populate each domain in the group with the same set of keywords.

zip-code-tracking

We will automatically tag each domain or URL with the tracked city or zip code. This allows filtering by location and faster scanning of the dashboard to find the location you’re looking for.

Compare Rankings With Competitors

Competitive intelligence is an important aspect of any SEO campaign. If you’re not keeping an eye on at least the major players in your market, you’re missing out on some great insights. Maybe you’re dealing with something that’s seasonal and want to see when competing sites start their push, usually months ahead of time.

Competitors

You can easily track competitor rankings in AuthorityLabs by simply adding their domain just like you would add your own domain or URL. You can even get side by side comparisons for up to 5 domains by setting up a group and syncing the domains. Once the syncing is complete, just click on the name of the group to bring up a comparison page.

One thing to keep in mind is that we only count a keyword once per location. This means that if you want to track the same keyword for 10 different domains within the same city, we only count that as a single keyword. This is different from most other rank tracking tools and is a huge benefit for users of AuthorityLabs; especially for those who are tracking competitors or multiple sites on the same topic.

Share Reports Without A Login

Have you ever wanted to quickly share the current rankings of a site with someone? Maybe you have a client that you’d like to give access to ranking data but don’t feel like setting up a login for them. We have the perfect solution. Our “Public URL” feature is a secret, extremely secure way to share a report without needing a login. The combination of your account URL and a couple of hashed IDs is actually more secure and more difficult to guess than a standard username and password authentication. These URLs are not indexed or crawlable and can be reset at any time if you only want to share a report temporarily.

To find out the public URL for one of your domains, click on the “Options” button in the left sidebar while on a domain page. You will see a box at the top with the Public URL in it along with a refresh button to reset the URL. Click here see an example public URL.

Prioritize SEO Efforts

We recently added AdWords volume data to the interface. This allows easier prioritization of SEO efforts. If you see high traffic volume keywords that aren’t ranking or could be improved, those should probably take priority over lower traffic terms.

volume-data

With “not provided” being the norm these days, it can be difficult (or impossible) to figure out which terms are actually driving revenue. Use volume and ranking data along with comparisons of the ranked URL and entry page data from your favorite traffic analytics tool to gather insights on which pages are performing well and which keywords are likely traffic drivers for those pages. From there, determine the entry pages that result in the most goals, conversions, or revenue and focus efforts on the keywords the best performers should be ranking for.

Use Wildcards In Tracked URLs

Wildcards are a feature that’s been in our interface for a while now and some people have been making great use of them. They allow you to track a URL or group of URLs that have a common pattern within them without having to track a more broad URL than is necessary. For example, let’s say you have the following URLs that you want to track for a section of a site that is dedicated to finding plumbers in various cities:

domain.com/plumbers-in-phoenix.html
domain.com/plumbers-in-miami.html
domain.com/plumbers-in-new-york.html

You’ll want to separate out plumbing related keywords on the site and only track the URLs in your plumbing section. Rather than adding each and every page of that section as a separate URL, you can use a wildcard to cover all versions of the URL. In order to use a wildcard, you simply replace the section of the URL that varies with ((!wild!)). The above URLs would be all be tracked by adding the following as your domain in the interface:

domain.com/plumbers-in-((!wild!)).html

You can use the wildcard in place of subdomains, subfolders, and filenames.

If you’re already using AuthorityLabs, we hope that these tips will help you to organize your ranking data and save time using our interface. If you don’t have an account yet, get signed up for a free trial and start gaining valuable insights while saving time monitoring your site’s rankings.

Quickly and Efficiently Use AuthorityLabs [VIDEO]

In this tutorial I will walk through three simple tips that may cut your tool using time in half!

I will go over how to import multiple domain, import large amounts of keywords, and quickly download a screenshot of your data.

You can find all these tricks in the tutorial below. But if skimming is your thing, I will be linking to different parts in the video so you can skip to the part that is most important to you. Enjoy!

Import Multiple Domains

Once in the main dashboard, there is a option in the left hand toolbar called Add Domains. I used a list of swing dancing domains as competitors for my site. You can copy this list in from any where, really. I used Excel because it then imports them on their own lines.

Pro Tip: In Excel, Ctrl+F to pull up the find and replace dialog box. Search for http:// and https:// and set the replace to blank. This will clean up your domain list.

There are domain settings on this page that allow you to select which search engines you would like to display. I displayed all of them. (But I’ve only selected Google in the past.)

Next, there is an option add this group of domains to a domain group. I created the SWING group before starting the tutorial. This is optional, I just like to use them to organize my domains. I got my OCD from my mom. #nohating.

Import a Large Amount of Keywords

Skip to this section of the tutorial.

Starting in the main dashboard. Navigate to a domain that you want to add multiple keywords to.

Once in the domain, look to the left hand toolbar and select the Add Keywords > Bulk Upload. There is an option under File Format Example to Download this example, click on it and open the example .csv in Excel.

Once in the sample.csv, there are two columns, keyword and tags. To just import the keywords delete the example tags from the tags column. Copy in the list of keywords and paste them into the keyword column. Save the file as a .csv, not a .xlsx or .xls. You want the bare bones of the document. Just say yes to the file formatting warnings.

Return to the browser and select the Choose File box and select your .csv. The import will begin automatically.

 Quickly Download a Rank Snapshot

Skip to this section of the tutorial.

Start under a domain that you’re interested in getting an overview of.  Change the date to one you would like to get a quick look at. I did the beginning of the month so that I could compare it with the beginning of that month from the year before.

We’re going to focus on the buttons in the top right corner. There is an option to Download this page as a PDF, or Download this page as a CSV. Select the .csv option and open that export in Excel. From there you can visualize the data and compare it quickly and easily.

Need some tips on visualizing your data? Learn how to make an Interactive Dashboard from your AuthorityLabs export. If you’re not that cozy with Excel yet, brush up on some tips with our Excel for Noobs tutorial.

Google Webmaster Tools Adds Exact Click Traffic Data

Have you logged into your Google Webmaster Tools account recently?! If not, you should because you now have new data available. Google has added exact click numbers into Webmaster Tools. This data is great for figuring out actual click volumes and clickthrough rates from the SERPs. Google Webmaster Tools has its limitations, but could be used as part of an overall analytics strategy. Of course, no data source is perfect but having as much as data as possible always helps. This is as close as we’re currently going to get to seeing solid traffic data for specific keywords.

Take a look at the screenshot example below. As you can see in the Clicks column this previously would only show estimations and ugly data like “<10″ but now you can actually see the exact number of clicks received for each keyword.

GWT Adds Exact CTR Screenshot

Here are a few quick ideas for integrating the data into your AuthorityLabs account:

  • Segment out top traffic keywords and track them separately so they are easier to quickly view and manage
  • Find low traffic, high clickthrough rate keywords and track those to make sure rankings are improved. Some may be long-tail keywords and those may be worth breaking out into additional segments for tracking.
  • Look for high impression, low CTR keywords and track where they rank. These may be skewed due to things like image search being counted as ranking positions by Google. It’s important to track these and make sure they rank in the organic SERPs. If they do not, this is a good opportunity to improve rankings.

Excel 101 (Excel for Noobs)

I have done several Excel tutorials in the past and I know that they have been helpful to those with a background in Excel. But this post is for the marketers who have only opened Excel when a .csv has no where else to go… and they still look like this:

Making It Pretty

First we’re going to take a old busted .csv and make it the new hotness. Who really wants to look at ugly data all day? Here are a steps I take when I first open a .csv.

  1. Gridlines
  2. 10px cell padding in A1
  3. Deleting/hiding unnecessary data
  4. Giving the document a title

Pro Tip: Save as an Excel Workbook first. If you save the document as a .csv all the formatting will be lost.

1. Gridlines are for noobs. Trust me, your data will look 10 times more clean and professional with the Gridlines turned off. Under the View tab deselect the Gridlines box. Here’s a before and after beauty shot of Gridlines.

gridelines-beforeafter

2. To add some cell padding, navigate to the far left column and right-click, select insert 1 cell to the left. Then change that cell to be 0.1 (12px) wide. This give that little bit of padding before your title and charts.

insert-padding

3. I don’t need all the details of my export, I really just like to delete all of that. To do this you click-drag your mouse across the cells to select them. Once selected you can either hit the delete key, or right-click and select Delete.

4. This is the easy part. Simply type in your title “Amazon Keyword Rank Report” and on the Home tab you will have text editing options. I would make the size of the title at least 14pt.

formattingtitle

Adding a Table

From here I selected only the most important data that I wanted to focus on. Since this a comparison report, I selected the column of data for the first of November and December. I also am only looking at the data from Google. So now my raw data looks like this.

raw table

Tables are great for being able to filter your data and organize it better. Not to mention how tables are just prettier than raw data. To add one you can navigate through the ribbon by selecting the Insert tab > Table > Create Table. Or just hit Control + T.

You’ll see that another tab pops up when you create your table, this is the Design tab. From here you can customize your table.

Adding a Chart

Adding a chart is something that intimidates most Excel noobs. But it’s very simple. I’m creating this chart to show the Relative Volume data. To do this I select the data I want to show.

Select the Keyword and Relative Volume columns by holding down the Ctrl key and dragging the selection. From there navigate to the Insert tab and select the chart options. I chose a 2-D column chart. From there you can edit the colors of your chart in the Design tab.


You did it! You now know how to format your data, create tables and a simple chart. Stay tuned for more tips in the Excel 102!