Re-Creating AdWords Campaign Performance Charts in Excel, Part 4: Making the Charts

In part three of this series, you learned how to build our dashboard’s scorecard. In this article, I will show you:How to build the charts.How to copy and reuse your charts to save time when building multiple charts.Building the Charts The AdWords Campaign Performance report only has two charts and they allow you to choose from many different available metrics for comparison.

Our dashboard isn’t really limited by space, so we will be creating a chart for each of our KPI’s.ImpressionsClicksCostConversionsCTRCRCPACPCThe best way to do this is to create and format one chart for one KPI, and then we will copy the chart several times for each of the other KPI’s. This technique will save you lots of time, because you will only have to format your chart once.Follow these steps:1. Ins... > Read more

May 11, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 1

Congrats to Microsoft, Proud New Owner of Skype

Today, Microsoft officially announced its acquisition of Skype, dishing out a hefty $8.5 billion for the popular video conferencing service. The Skype deal will be Microsoft’s most expensive acquisition in its 36-year history. As a company that traditionally avoids pricey purchases, Microsoft must have some big plans for Skype.

A Quick Skype Background CheckSkype was created by Niklas Zennstrom and Janus Friis in 2003. Offering free video conferencing and calls between Skype users via computer and certain cell phones, Skype quickly became popular with international callers. Interestingly, Zennstrom and Friis are also the inventors of the infamous Kazaa music sharing program, a big name in the muddied waters of music piracy (Kazaa also became well known for being riddled with viruses).Skyp... > Read more

May 10, 2011     |   Written by: Megan Marrs   |   Posted In: Social Media   |   Comments: 1

Commonly Used PPC Metrics: What They Will and Won't Tell You

ImpressionsAn impression, in online advertising, is an appearance of an ad on a web page. In search engine marketing, an impression constitutes an appearance of a text ad on a search engine results page. Internet advertising costs are sometimes measured in cost per impression. An impression will tell you how many people have been exposed to your brand or product.

What impressions won’t tell you: If that exposure actually left “an impression.” Search behavior tends to be a fast and reactive experience. Often users only look at one or two results on a page. Even if your ad is at the top of the list, it may be missed.Click-Through Rate (CTR)Your PPC click-through rate is the percentage of people who view your ad (impressions) that actually go on to click the ad. You can view your cl... > Read more

May 10, 2011     |   Written by: Elisa Gabbert   |   Posted In: Analytics   |   Comments: 2

Google's New Toys: Google Releases Site Speed, Business Photos, Search Globe

It seems we’re always talking about Google. Whether we consider it a friend or foe, it’s difficult to deny that much of SEO news revolves around every breath Google takes. And it’s true, you don’t hear folks saying “I don’t know, let's Yahoo it.” In keeping with its nonstop release schedule, Google recently announced several new features.

  Google Analytics Tracks Page Load Times Google is like an inspirational high-school teacher. He sees the potential in you that you have not yet discovered, and he’s always setting the benchmark higher and pushing you further. Google wants you to be the best you can be – for yourself, and for your users. That’s why with Google Analytics you now have the ability to track your page’s loa... > Read more

May 09, 2011     |   Written by: Megan Marrs   |   Posted In: Google   |   Comments: 2

Five Ways to Lower Your AdWords & PPC Costs

This is the last installment in a three-part series on achieving specific goals within your paid search accounts. In the prior installments we’ve covered:Five Ways to Get More Leads from PPCFive Ways to Lower Your Cost Per Action (CPA)And today we’ll cover a third, somewhat less frequent objective: lowering your PPC costs.

It seems like lowering your PPC costs should be a pretty common objective, but in actuality lowering your CPA is usually a much better goal. After all, for most businesses you’ll take as many cost-effective leads and sales as you can get, so the concern is more how much you’re paying for each lead rather than how much you’re paying total. But as most PPC managers know, in the real world there are various factors that sometimes cause businesses to want to quickly... > Read more

May 09, 2011     |   Written by: Tom Demers   |   Posted In: Paid Search Marketing   |   Comments: 0

What the Heck Is a Sales Letter? Old School Direct Marketing Techniques

I was doing a little research on copywriting blogs recently, and noticed that many of the renowned veteran copywriters who got their start in direct (i.e., offline) marketing would often make reference to “sales letters.” I wasn’t born in the ‘90s or anything (I know who Osama bin Laden is), but my response to this was, What the H is a sales letter? A sales letter, of course, is the “letter” you receive in direct marketing mailings, which begins “Dear ________” and informs you of all the reasons you should give the sender your money.

Believe it or not, young ones, direct marketing still exists – and even works. According to a recent Marketing Sherpa survey of B2B marketers, 79% of respondents found direct mailing “somewhat&rd... > Read more

May 06, 2011     |   Written by: Elisa Gabbert   |   Posted In: Online Marketing Blog Roundup   |   Comments: 1

Google Tests New SERP Format: Site Links to News Articles Replace Website Description in Branded News Searches

I saw this change in the Google SERP format today on a search for "CNN." Check out how Google has replaced the 2-line website description with up to three sitelinks with full-line descriptions corresponding to the headlines of trending news stories. Take a look here:See how you can click on "Obama won't release bin Laden photos: Reaction" and it will link directly to the article.

 It seems to be working this way on other major national media outlets, for example:USA TodayFox NewsWashington PostCBS NewsI guess on branded searches for media outlets, Google might be thinking something like...The anchor text on site links are too small for news articles (they're typically 1-3 words like "contact us" or whatever)The user probably doesn't care much for the website description (they obviously... > Read more

May 04, 2011     |   Written by: Larry Kim   |   Posted In: Google   |   Comments: 0

Osama Bin Laden's Death as Told by Social Media and Search Engines

While Twitter, Facebook, and media outlets around the world were ablaze with news of Osama Bin Laden’s death on the night of May 1st, I was fast asleep in bed. It seems I’m always sleeping through important events, having snoozed through the Royal Wedding as well. I don’t wake up at 5 am for anything, and while old ladies in fancy hats are always good for a laugh, I’ve gotten more of my share of darling William and Catherine through the continuous stream of replay coverage.

So naturally it wasn’t until I groggily sat down at the computer with my coffee the next morning that I had any idea of the massive NYC partying I had missed out on. And like many others, I got the startling news via Facebook.Twitter Sets Aflame With an Abbotta-BANG!Osama Bin Laden’s death has once again exem... > Read more

May 04, 2011     |   Written by: Megan Marrs   |   Posted In: Google   |   Comments: 2

Webinar: Winning the Keyword Research Game

A couple of weeks ago Larry Kim gave a webinar on keyword research, revealing his three-step process for discovering keywords. You can watch a recording of the webinar, as well as view the slides, below. You'll learn:How to dominate a search category for your brandWhy keyword niches are more important than single keywordsHow to apply your keyword research in on-page SEOAnd don't forget to sign up for our next webinar, Improving Quality Scores, on Wednesday, May 18.

Webinar: Winning with Keyword ResearchTo try the keyword tools demonstrated in the presentation, take a test drive today! ... > Read more

May 04, 2011     |   Written by: Elisa Gabbert   |   Posted In: Keyword Marketing   |   Comments: 4

Re-Creating AdWords Campaign Performance Charts in Excel, Part 3: Making the Scorecard

In part two of this series, you learned how to transform the data we will be using to create our dashboard. In this article, I will show you:How to build the scorecard.How to use conditional formatting.How to use the camera tool.Presentation (the “P” in “DTP”)Again, we are using the DTP framework created by the smart folks at Juice Analytics.

The presentation layer for our exercise consists of all the pieces of our dashboard:Dynamic charts and scorecardsDynamic controls for date ranges and campaign filteringBuilding dynamic dashboards in Excel can be a challenge, but if you’ve already completed the steps from the first two posts in this series you’ve done the hard part. Now we are ready for the fun stuff.The Dashboard TabFirst, we need to create a new worksheet and rename it ... > Read more

May 03, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 1

Five Ways to Lower Your PPC Campaign's Cost Per Action (CPA)

Last week we gave you five tips on how to get more leads from PPC. But what if your main problem isn’t leads? Everyone wants more leads, of course, but what if your most immediate problem is that your PPC campaign just isn’t profitable?In this post we’ll walk through five more tips, this time for lowering your CPA.

Cost Per Action Lowering Tip 1: Lower Your BidsIf your cost per conversion is out of whack, this basically means that the following equation isn’t adding up properly for you:Cost / Conversions = CPAWe’re thinking about ways to get conversions for cheaper, so it’s pretty obvious that we might want to do something about the conversion end of this equation, but all too often I see people ignore the cost side to their own peril.Many times, there’s a range of conversion ... > Read more

May 02, 2011     |   Written by: Tom Demers   |   Posted In: Paid Search Marketing   |   Comments: 4

The Art of the Terrible Analogy: How SEO Is Like Bacon, Sex & World of Warcraft

Sometimes, the best thing I read over the week has nothing to do with Internet marketing. But I really want to share it with you, so by God, I make it have something to do with Internet marketing. This week, that thing was this list of the worst analogies written by high school students, originally published in the Washington Post.

The source above ("The Lost Eyeball") calls them the "worst/best" because many are actually quite brilliant – if I was a high school teacher and one my students wrote these gems, I’d give them a king-size gold star. To further qualify the list, the source says the paper “held a contest in which high school teachers sent in the ‘worst’ analogies they’d encountered in grading their students’ papers over... > Read more

April 29, 2011     |   Written by: Elisa Gabbert   |   Posted In: Online Marketing Blog Roundup   |   Comments: 5

Best of the Month: WordStream's April Highlights

April is over, you've paid your taxes, and now you're waiting for your tax return. Oh, the miserable wait: What to do in the meantime??? How about catching up on our best blog posts from the month? Is the Internet Bad for the Environment?: In honor of Earth Day we published this infographic detailing what kind of damage we're doing with our Internet addictions, as well as how the Internet is good for the planet.

Crayola Colored Bubbles: Reputation Management for a 1-Star Disaster: Worst product ever? Read some of the hilarious reviews for this mess of a product (literally) and learn what to do if this happens to your company. Understanding the Differences Between Google and Bing Match Types: This guest post by Bethany Bey from Hanapin Marketing explains the differences betwee... > Read more

April 28, 2011     |   Written by: Elisa Gabbert   |   Posted In: WordStream   |   Comments: 0

Five Ways to Get More Leads from PPC

Everyone wants more leads, right? This is probably your number 1 request from clients or upper management if you’re running pay-per-click campaigns (followed closely by “without increasing spend”). But the reality is you’re likely managing to a target cost per acquisition, so grabbing more volume isn’t as easy as jacking up bids and lifting your budgets.

The obvious answer to increasing conversions is focusing on conversion rate optimization, but we’ll walk through five slightly less obvious ideas worth testing that can help you grab more volume without ignoring your margins.Idea 1: Keyword ExpansionThe first idea is to try different keyword verticals and expand your keyword list. How do you do that? In all likelihood you’ve already brainstormed your initial list, used some tr... > Read more

April 28, 2011     |   Written by: Tom Demers   |   Posted In: Conversion Rates   |   Comments: 5

Facebook Family Additions: The New Send Button & Social Deals

Facebook develops new features and alters its layout and settings (and privacy policies) frequently, and the social giant recently introduced two new additions in its continued mission to take over the word. Facebook’s new Send button allows users to share items with select people rather than everyone in their friend network.

Maybe you found a great bar in which to throw your friend’s surprise party – but you certainly don’t want the birthday girl to find out. With the Send button, you can quickly and easily share and send the URL to specific friends, groups, and email addresses. Cynthia Boris at Marketing Pilgrim has a clever idea about how the Send button could be implemented as a marketing strategy: "As a marketer, I’d take this one step further and u... > Read more

April 27, 2011     |   Written by: Megan Marrs   |   Posted In: Social Media   |   Comments: 0

Google's Instant Preview Now Includes Ads!

Yesterday Google announced that their Instant Preview feature will now apply to ads. That means you’ll now be seeing that nifty little magnifying glass appear next to ads as well as organic search results. Clicking the Instant Preview magnifying glass brings up a window that lets users preview the ad landing page.

In the image below, you can see that my Google search for “flowers” brought up a few ads. Clicking the magnifying glass next to my 2nd ad, I got to see a preview page and was able to confirm that the ad would, in fact, lead me to a site with an abundance of beautiful flowers to sell (just seeing them all nearly makes me sneeze). Instant Preview allows users to preview the ads they are about to click on to ensure that the content of the landing page matches what... > Read more

April 27, 2011     |   Written by: Megan Marrs   |   Posted In: Google   |   Comments: 1

Five Reasons Every Startup Needs a Great Writer

When hiring for a tech startup, most companies focus, understandably, on technical talent: developers and engineers. Obviously, you need great engineers if you're going to build software, apps or a website of any kind. But don't underestimate the value that a great writer can bring to your organization.

A lot of startups make any writing tasks that come up the responsibility of whichever staff member is the most competent writer. When you're bootstrapping, multitasking is a necessity. But if you've got some funding, hiring an excellent writer can impact your business in a lot of positive ways. So whether you make a full-time hire or find a reliable contractor, it's worth the investment to seek out a writer who understands your business and industry and cares about the outcomes. Here are fi... > Read more

April 27, 2011     |   Written by: Elisa Gabbert   |   Posted In: Copywriting   |   Comments: 1

Re-Creating AdWords Campaign Performance Charts in Excel, Part 2: The Transformation

In Part 1 of this series, you learned how to prepare the data we will be using to create our dashboard. In this article, I will show you:How to set up an Excel pivot table.How to use calculated metrics in a pivot table.How to use Named Ranges in Excel.How to use the OFFSET formula in Excel.Transformation (The “T” in “DTP”)We are going to use an Excel pivot table for our "transformation" layer in our DTP framework (worksheet).

You should already have your data prepared in the "current" worksheet, so you will need to:1. If you haven’t already, format your data as a table in Excel by highlighting your entire data set in the "current" worksheet and click on "Format as Table" from the "Home" menu.2. Now select any cell within your table and navigate to the "Insert" menu and insert a "... > Read more

April 26, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 2

Announcing the Keyword Research Kit from WordStream

We recently put together a Keyword Research Kit that includes a lot of great resources for anyone doing keyword research and marketing for either SEO or PPC applications.The Keyword Research Kit is a completely free resource available to download. The kit includes:The Keyword Research Cheat Sheet – This PDF offers a bird's-eye view of what keyword research is, what it isn’t, and why it’s important.

This cheat sheet is helpful for quickly orienting those new to the concept of keyword research, from first-time hires to executives.Keyword Monitoring Worksheet – This tool allows you to plug in your keyword data every month and monitor trends in your rankings, traffic from SEO, and ultimately how your organic search engine marketing efforts are helping to drive your business.The Ultimat... > Read more

April 25, 2011     |   Written by: Elisa Gabbert   |   Posted In: Keyword Marketing   |   Comments: 2

New and Noticeably Worse! The Downgrade in Upgrade's Clothing

On Wednesday, Google rolled out a new Analytics upgrade. Here’s Mashable’s report on the upgrade: Google rolled out a new and improved version of Google Analytics to all current Analytics users on Wednesday. The upgraded product comes with a bevy of new features. In addition to faster performance and a streamlined UI, the new Analytics also packs quite a lot of improved and entirely new functionality.

Users can create multiple dashboards, up to 20 per user; and each dashboard can contain up to 12 widgets. Users can also set interaction goals (for example, you might use Google Analytics to track and optimize file downloads or video views), graph and compare certain metrics over time, and toggle between multiple profiles and sites while focusing on one report. Wiep Knol begs to d... > Read more

April 22, 2011     |   Written by: Elisa Gabbert   |   Posted In: Online Marketing Blog Roundup   |   Comments: 2

Is the Internet Bad for the Environment?

In honor of Earth Day, we've done a little digging to find out just how bad (or good) the Internet is for the planet. Click the image to enlarge. ... > Read more

April 21, 2011     |   Written by: Elisa Gabbert   |   Posted In:   |   Comments: 13

Be Your Own Lewis & Clark with Google's Map Maker

In Google’s continuing efforts to get more social, Google’s Map Maker tool made its introduction into the United States yesterday. Map Maker allows users to add and update maps with their own insights. This can mean a number of different things, such as moving place markers to more appropriate spots, pointing out certain parks and playgrounds, building a more detailed map of a college campus, or even marking the bike lanes in your town.

Essentially, it makes Google Maps into a Wiki.Google’s Lat Long blog notes that since the release of Map Maker, the percentage of the world’s population that has detailed online maps of their neighborhoods has jumped from 15% to 30%. Users have mapped road networks, bike lanes, schools, and even entire cities that were not documented ... > Read more

April 20, 2011     |   Written by: Megan Marrs   |   Posted In: Google   |   Comments: 1

SEO for Small Businesses: An Interview with Dan Olson of DIYSEO

Dan Olson is the CEO of DIYSEO, a provider of SEO software for small businesses. Dan leads the strategic direction and manages all day-to-day activities at DIYSEO. He is a former vice president at Performics, the largest search marketing company in the United States that was acquired by DoubleClick, Google and eventually Publicis Groupe.

 How should small business owners think about and evaluate SEO?I think three things are important here:Approach it like marketing. SEO isn’t some tech-voodoo, it’s another way of getting customers to your site.Compare it to other things you do to market your business – sometimes that’s similar stuff like PPC, but it’s also print ads, billboards, etc.Focus on the actual return on investment.I think a lot of small businesses get overwhelmed by ... > Read more

April 20, 2011     |   Written by: Elisa Gabbert   |   Posted In: Interview Series   |   Comments: 2

What You Should Know About Web Hosting & SEO

The process of choosing a proper hosting service for your website is fairly straight-forward. First, you'll need to have an idea of the type of site you are aiming for. While you don't need to have every single page in mind that will be on your website, having good estimates will mean that you'll be able to properly gauge what type of hardware and software a web hosting server will need to be able to host your site.

During this planning phase, you'll also need to think ahead to how your website might grow, and what new hardware and software needs the site may have in the future. With this information in hand, you're prepared to start looking for a hosting service. Software Requirements Some websites require support for various programming languages, like PHP or Ruby on Rails. These differe... > Read more

April 19, 2011     |   Posted In: SEO Marketing   |   Comments: 0

Announcing the New Keyword Research Suite: More Keywords, Exclusive Data, Actionable Results

Today I’m thrilled to announce the general availability of what I believe to be the World’s most comprehensive and accurate suite of keyword research tools for SEO and PPC – the new Keyword Research Suite from WordStream! You can test drive the tools for free, or read on for more information.In this post, I’d like to talk a little about what the Keyword Research Suite does, how it’s different, and why I think it’s a must-have for anyone who's serious about search marketing.

What Is the Keyword Research Suite?For the past several months we've been gathering feedback from our regular free tool users and buckling down to develop what we think of as the ultimate keyword research suite. We wanted to build a tool that would offer…The Biggest Keyword Research Database on the Planet ... > Read more

April 18, 2011     |   Written by: Larry Kim   |   Posted In: WordStream   |   Comments: 5

Re-Creating AdWords Campaign Performance Charts in Excel, Part 1: The Data

Interesting things you will learn in this how-to:How to prepare data for year-over-year analysis in ExcelHow to use the Excel INDEX-MATCH formulaHow to use the Excel ISNA formulaA few weeks ago I showed you how to compare date ranges in AdWords, and then a few days later I came across an article on Search Engine Journal about using the new AdWords dashboard located on the "Home" tab.

 In the article the author mentioned how, in the new version of the Home tab, you couldn’t do date range comparison like in the previous version. See the image below:So, I thought it might be interesting to try and recreate this chart and summary in Excel. The finished dashboard will look something like this:For this exercise I will be using a variation of the Juice Analytics DTP Framework.Data (the... > Read more

April 18, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 4

Friday Roundup: Panda Bears Down Internationally

This week Google’s Panda update went global, shutting out spam sites worldwide. Sistrix posted a nice little chart listing the “losers” of the new updates. Sites like suite101.com and associatedcontent.com suffer just as much now as they did in the US, but there were some surprises;  eHow, which most expected to suffer from the US Panda update, was actually deemed one of the “winners” originally, but with the global changes, eHow is now losing the numbers we expected it to.

    There is some speculation that this might be a result of Google implementing data from users who manually blocked certain sites. Ever since Google released a block link directly in search results, it’s been expected that this data will end up affe... > Read more

April 15, 2011     |   Written by: Megan Marrs   |   Posted In: Online Marketing Blog Roundup   |   Comments: 2

AdWords Position Preference Bidding Is Retired: What You Need to Know About AdWords Average Position

Google recently announced that they are retiring position preference bidding. Some smart PPC managers had been talking about how position bidding wastes money for some time, and Google's own chief economist had taken great pains to lay out the argument against bidding to position in your attempts to improve your actual sales and costs per sale.

 If you're hearing this news now for the first time you might be wondering why people ever used bidding to position as a means of managing bids, what you should know about AdWords average position metric, and of course: whether or not there may be some instances where this data is still useful in your campaign.Why Bid to Position?Obviously with Google sharing some of its own data and Google and top PPC minds decrying the value of the metric... > Read more

April 14, 2011     |   Written by: Tom Demers   |   Posted In: AdWords Tips   |   Comments: 2

Using Educational Linkbait to Get Valuable .edu Links

Kellogg’s Cereal has space-themed nutrition lessons in downloadable PDF files. Orkin Pest Control has a virtual insect designed to offer an alternative to classroom dissection. Ben and Jerry’s has interactive games about the environment. Big companies have, for years, been putting educational materials online and getting benefits in the form of coveted .

edu links and serendipitous traffic. Smaller sites can do the same. They usually don’t, but the benefits can be significant. High-quality sites that would never link to your commercial website’s homepage will link to your educational page. You can also list your educational page in high-value places like Google groups for educators or Teacher’s Sourcebook – as long as you’ve got actual educational m... > Read more

April 13, 2011     |   Posted In: Link Building   |   Comments: 10

PPC Bid Management Strategies: An Interview with Todd Wilkinson of WordWatch

Todd Wilkinson, CEO of WordWatch, began his online career in 1999 with WorldOnline/Tiscali, a pioneering European ISP. Todd later co-founded a digital agency in Amsterdam in 2002, called iizt (pronounce “east”), serving the advertising industry in the Benelux. In 2006, Todd co-founded and served as CEO to Respectance.

com, a social media play described by TechCrunch as the “MySpace for dead people.” Funded by two European VCs, Respectance was located in San Francisco, CA and Amsterdam, NL. Todd exited Respectance in 2010 and launched WordWatch in March 2011, based in Foster City, CA and Amsterdam, NL, with production and algorithm development in Krakow. You believe that bid management is the most time-intensive part of search engine marketing. Why is it so time-consuming and what... > Read more

April 12, 2011     |   Written by: Elisa Gabbert   |   Posted In: Paid Search Marketing   |   Comments: 2
 
Free Keyword Tool

Get thousands of relevent keyword suggestions - more,
faster, free!

Free Keyword Niche Finder

Discover profitable pockets of keywords for your
business.

Free Negative Keyword Suggestion Tool

Identify wasted spend before it happens and increase
your paid search ROI.

Contact Us | Company | Support | Site Map | Trademarks | Privacy Policy © 2007-2014 WordStream, Inc. All rights reserved.