4 Ways to Up Your Testimonials Game

Are you actually using the testimonials and reviews that you already have for your business?

For most of us in business, it is hard to get testimonials from our current and former clients. Some businesses just do not think about getting them and others just do not want to bother clients with it. So once we get them sometimes we don’t put much into the thought process of what to do with them. However, doing thtestimonialsis just means that we have totally wasted a lot of time and energy getting valuable testimonials and reviews.

Website Testimonials

Most of  us have visited a website where the testimonials are tucked away in some far corner of the website to rarely actually be found. We have also seen the reviews left on social media sites and review sites just sitting there dormant. The businesses have often not taken the time to reply to them or get additional feedback regarding the review. This is a mistake, especially if the review is high quality and beneficial to your brand.  Reviews and testimonials are meant to help your brand. People trust what other people say, so great reviews typically influence others to buy from your brand, which helps grow your business.

We know all too well that many reviews are just blah, nothing more than a quick, generic, one-liner; nothing more than to say the person did a review. These often don’t need a response, but if there is a negative review be sure to respond and get feedback.

So the question is, why aren’t we actually utilizing the testimonials that we have currently?

If you want to maximize the benefit of your testimonials and reviews, you are going to have to up your game, to take them to the next level. Let’s take a look at some ways we can do this:

1. Don’t hide them! Feature them on your site in a prominent location.

When people visit your site, they are beginning to check out your product/service, or they may be returning to get more information about your product/service that they are already somewhat familiar with. So of course they will have to overcome many objections while they do the typical debate on whether to invest or not. People have to decide if they really need your product/services or not, if they like the price of your product, and most importantly, if they TRUST you. So in order to combat these objections, you have to be prepared to prove that your brand is worth their trust. This person may have never heard of your company before or may know very little about your brand.

Client testimonialsSo, what better way to earn this trust than to show them what your current and past clients have to say about you and your brand with reviews and testimonials. Don’t tuck them away on a single page of your website to only be stumbled upon by a few people.

After all, this is free trustworthy advertisement for your brand from the people who use or have used it. You should feature at least one on your homepage, pricing pages, product pages, etc. with each testimonial linking to your actual full testimonials page; this allows for maximum exposure. You could also have rotating testimonials so that people see multiple testimonials without having to leave the page that they are on.

Regardless of how you decide to feature them, the most important thing is that the great things people say about your brand are easily visible to anyone who visit your site.

2. Check this out! Place them around clear calls-to-action. (See what I did there)

All businesses should have clear calls-t0-action on their pages. It may be “DOWNLOAD”, “BUY”, or “CALL”; essentially you are directing people to act.  This of course is very important to your brand because it is a conversion. When people are staring as this large button telling them what to do they are often still in the decision-making phase, trying to decide if they should trust your brand. You want them to see your testimonials so that they will trust you and conversion is possible. But do you want to distract them by asking them to leave the page in order to read testimonials? Of course not!

So why not feature a quick testimonial or review near the call-to-action? This feature could also be just a small portion of a larger testimonial from your testimonials page. By going ahead and placing a review near your call-to-action, you are making a bold statement to the potential client. You are putting it all out there by saying to them, “See, you can trust me….others did and look how pleased they are!”

3. LIKE this. Share your social media reviews and thank the client.

I have written before about ignoring clients on social media, especially negative comments, but many businesses also ignore positive comments. Wait, what?! People actually ignore POSITIVE comments?! Yes they sure do.

Do NOT ignore them. If you fail to acknowledge someone who has left a positive comment about your brand, you have just missed a major opportunity from not only a social media perspective but also from a review-gathering perspective as well. If someone takes the time to actually leave you a positive comment about your brand, be sure to thank them! For example, on Facebook, you can actually reply to their comment and say “Thank You” and use their name to tag them. This makes the reply more personable and shows the consumer that you care, making them feel special. You can also in turn share their comment post so that others can see just how great your product is. Again, this is helping to build trust and brand awareness. This works particularly well if the person left a long comment or even a photo. If you’re on Twitter, no wait, WHEN you are on Twitter, simply retweet the positive tweet or use the quote tweet feature and add a quick thanks at the beginning.

So, you may be thinking WHY should I partake in all of this? You are showing your positive comments to potential customers to build trust, but you are also showing your current and future clients that you care. You are also telling your current and future clients that you appreciate their business. You are engaging the client in a conversation, which is Social Media 101. Doing this is going to build brand awareness and help your social media presence. By social sharing, you’re providing fan exposure, and giving them a few minutes of glory, if you will.

4. Take one! Shoot video testimonials.

VideoEveryone knows that there is just something about seeing someone’s face that is super convincing. Seeing their face allows you to see just how happy they really are with a product or with their brand experience overall. Hearing the enthusiasm in their voice and just how awesome their experience was is just something that you do not get from written word. You most definitely can, however, get all of this from a video testimonial!

I know what you are saying…video will take time and energy to do. You are absolutely correct. But it will be worth it after converting several potential clients into actual clients because they watched some of your video testimonials. This will take more effort than any of the other tactics mentioned above, but people love video so use this to your advantage.

Another great aspect of video testimonials is that you can utilize them in other areas of the web besides just your website. You can upload them to YouTube, they can be shared on your social media platforms and you can also include them in emails. To add to this tactic, you can have the clients that are featured in the videos share them with their audience as well. This allows you even more reach!

In conclusion, in order to maximize your overall marketing strategy, you have to up your game with the individual parts of your strategy. This includes your reviews and testimonials. Do not just rely on the testimonials to reach out and convince potential clients on their own. Don’t be afraid to get out there and think outside the box. See how much more convincing your testimonials can be by upping your game, taking your reviews to the next level.

Image Credit: Shutterstock faysal and Yuriy Vlasenko.

Social Media Experts: Real vs. Fakers

social mediaSocial Media is one of the hottest areas of digital marketing today. Everyone is on social media. Companies small and large are jumping into the pool of social media. That also means there are companies out there that have no idea what they are doing or where to turn when it comes to their social media. They are trying to incorporate it into their marketing efforts but with truly just don’t know where to begin.

So of course, when they start searching for help with this area, they find so-called social media “experts”. Not everyone claiming to be an “expert” in social media or any other area of digital marketing for that matter truly is an “expert” at anything other than BS. People are just dying to become Internet famous no matter what the cost!

So let’s take a look at some key points of a true expert vs a fake:

First of all, an expert should be active on social media and not just on one social media platform. They should have update their social media profiles often and also blog on their own blog or have some sort of blog platform perhaps through their work—somewhere to share their voice and skills.

They should also know and keep current on the latest news and trends with social media and digital marketing in general. In this industry, things change daily and an “expert” should know this. You have to read and research daily to stay up-to-date on everything in this industry.

An expert should know more than just Twitter and Facebook. Those are pretty obvious these days, but now we have so many other social media platforms depending based on niches, Pinterest, Google +, YouTube, Snapchat, Vine, Instagram are all dominating the social media atmosphere at the moment. An expert should be aware of these and others. They should know where the people in their niche can be found. They should be active on these networks, not just have an account. It is important to note that Social Media covers a very large area so no one can be an “expert” at every aspect of it.

Buying Likes and Follows

If the “expert” has tons of connections, but low quality connections then chances are they are a fake! They tend to buy followers because to them quantity is more important that quality. This is NOT a numbers game people! Buying “likes” on your Facebook page or paying for followers on Twitter doesn’t make you an “expert” on anything but being a fool. You want to naturally earn your likes, following, and such. I mean who in their right mind wants to pay $3 or more per follower on Twitter!

A social media faker will often only utilize their social media to promote their own agenda or company’s agenda, but provide little engagement with their followers. An true expert will actually respond to comments, tweets, etc and also share others’ content as well as their own. If all you see on their social activity is self promotion of their latest and greatest program or service please beware of this faker.

Fakers often have zero business sense. They could not run a legitimate business if they tried. They are just throwing crap against the wall to see what sticks. They don’t know how to turn followers and fans into customers. If they do, it’s usually broken promises and flat out lies. These people only see dollar signs and will never do any actual real work for anyone.

If someone you follow is  overly eager to get your business, make sure you watch them closely. If they sound like a used car salesman swearing that this is the only way or that is the only way—WATCH OUT! We’ve all met those types. There isn’t just one magical way that works in digital marketing. It takes several different moving parts to make things successful in marketing. It is also different for every business. For example, Facebook ads make work great for one business whereas they may be a total fail for another. Social Media should be used to complement all other forms of marketing. It requires careful planning and strategy to make social media work in your overall marketing plan.

These social media “experts” unfortunately aren’t always genuine. Many of the fakers have given social media a bad name and made it harder for truly knowledgeable people to prosper much like most other areas of digital marketing. Then there are the social media certifications as well but that would be another blog post in itself so we will have to examine that idea later. What do you think about the influx of self-proclaimed social media “experts”?

Image Credit: Shutterstock Bloomua

Things NOT to Do on Social Media in 2014

Social Media Fail We often read blogs telling us tips, tricks, and other things that we MUST do on social media. So let’s start 2014 off right by having some things NOT to do on social media.  These tips should help you to have successful social media etiquette this year. So let’s take a look at some big no-no’s for social media:

Sharing is Caring! But wait!

Your posts should be spread out over time. Ideally, you want them to be shared at peak times. This can be achieved by using social media management tools like Buffer, HootSuite, or SproutSocial. These programs allow you load your posts ahead of time allowing them to be shared at the optimal time to be seen by you followers. Too many posts at once can easily get you un-followed. People don’t want to see their feed full of nothing but your face and posts. So, if you have a friend that is puking on their social media, please have an intervention and let them know they need to slow their roll!

Get your butt on Google +!

Yea, yea, yea, we’ve all heard the hype on Google + being a joke. Well it’s NOT! If you’re not active on there you better get on there, like NOW! Since this social network is owned by big daddy Google, it is probably the most powerful social network you can use. This social network has the ability to create communities based on specific interests. Google + also has Hangouts. a unique feature, where you can host demonstrations, meetings, and discussions.   Studies have shown a direct correlation with +1′s giving you a bump in Google rankings.

Tweet. Tweet. Repeat.

This may seem like a no-brainer, but you would be surprised. Nobody cares if you just HAVE a Twitter account. What people want to see is that you not only HAVE one but that you’re actually using it. Interact with your followers. This shows that you’re human and not a bot of preloaded article posts. Yes, we have all seen those accounts before! Don’t be afraid to jump into relevant conversations. Follow others, especially your clients, potential clients, and people you do business with . Nobody wants to see a Twitter account that only follows 1 person. What does that say? Oh that you’re so freaking important that you don’t have to follow anyone else? Please. I think not.

Build a Foundation on Facebook.

WHAT?!? You don’t have a Facebook page?! OMG that in itself is a whole other blog post, but let’s just say you have a social media emergency if you don’t have a Facebook page. Everybody and their mama and even grandma is on Facebook nowadays. Regardless of whether you’re B2B or B2C you need to have a Facebook page for your business. And, more importantly, you need it to engage with your Facebook followers. Like and respond to their comments.

Content is Key

Content is content. Even if it’s old content. If it is good content then don’t be afraid to keep re-sharing this content to people. Remember you have new followers or followers that might have missed it the first time around. This is where those social media management tools I mentioned earlier come into play. Schedule the content to re-post from time to time.

Negative Comments

Okay I get it, negative comments scare you. It’s okay. Now get over it and face the music. Whatever you do, don’t delete the comments and don’t ignore them. This just infuriates people and makes them really come back with a vengeance with even more comments. Negative comments are a learning experience. They can teach you what you did wrong and you learn by fixing it or at least trying to. Once you correct the situation people will usually acknowledge that the situation was resolved by saying thank you and/or deleting the comments. With that said, you cannot please everyone so you are likely to have negative comments sometimes.

Beware of the Auto-Bots!

Okay, the only bot I care to see or hear is Roger from Moz! Nobody wants to receive auto-responders on Twitter saying “thanks for the follow”. Ugh. It’s so impersonal and frankly, it’s annoying. I get that you mean well and want to show people you appreciate them following you, but come on they know you want more followers, who doesn’t?

As far as retweets go, I think that fact that you retweeted someone’s content is thanks enough, but some people do actually thank you for retweeting and I feel that this can be acceptable if it is done personally without using auto-responders. The direct messages from bots on Twitter wanting you to visit their page just because you followed them are really irritation to Twitter users. Then there are the LinkedIn surveys wanting you to rate their business. God please just stop. If you want people to rate your business get them on Yelp, FourSquare, Google Reviews, and a testimonials page on your site itself.

Faves are all the Rave

Don’t forget to Favorite people’s tweets if you like something they are sharing or even if it’s something you’re mentioned in. This shows that you are acknowledgement and that you care. Also retweet your own mentions as well. Make sure you return the favor to others by sharing their content.

Get to Know People

When you connect with people on social media let the relationship marinate and build a strong foundation. Don’t solicit people as soon as you connect. We have all seen the guy constantly soliciting people on twitter without any relationship built. AWKWARD!


Have fun on social media. Don’t be afraid to laugh and make others laugh. It shows you are human. After all if you were face to face you would laugh and smile with people!

Don’t Be So Full of Yourself!

This has to be the most important rule of social media. Everything isn’t all about you! Share other people’s content not just your own! We have all seen that arrogant guy on social media who only promotes himself. Sad I know. So, get out there and share relevant articles that you read and think will be helpful to others. After all, you want them to return the favor right?


Image Credit:  Shutterstock Quka

9 Ways to Discover Ecommerce Ideas on Your Own

ecommerceEcommerce is on the rise. According to granify.com, more than $1.23 trillion in B2C ecommerce sales are expected this year. By the time 2013 closes, we’d have 3.5 billion Internet users.  Ecommerce is a good answer to entrepreneurship. Yet, you’d have to begin somewhere. You’d need an idea (while it doesn’t have to be brand new). How do you go about looking for the right idea for your new ecommerce store? How do you dig out ideas? What products or services would you sell? Is there a structured way to brainstorm?

Some of these approaches should give you a direction to follow or at least some food for thought:

Going Back to Tradition

Ecommerce means that the medium of transaction and the operational dynamics of the business changes while most other things remain the same. Instead of a brick-and-mortar office/warehouse/store, you’ll now get a digital equivalent of a store. Instead of having customers visit you in person, they’d come visiting the store. What used to be a cash-and-carry business now becomes a pay-to-delivery process. Payments end up as online transactions and feedback comes through as testimonials.

Sometimes, you don’t need a new idea at all. Pick up a traditional business and give it the ecommerce treatment.

Look around you: what are those classes of products or services that you’d still have to drive downtown to pick up? Your million-dollar idea might just be there. Go look for that spark of inspiration — that apple on your head.

Travel to Import Ideas to Your Market

Travelling, they say, teaches you a lot. It also has a lot to give you in terms of inspiration and adding to your idea bank. There could be tons of businesses operating in another country – they operate in a specific way, have their own style, and market in a particular fashion. Is there a way you could import that idea to deliver your own, customized version for customers in your country? Even if you can’t travel, there are sources such as Spring Wise that will do the job of spotting new entrepreneurial ideas, trends, and stories from all over the world.

Spot your idea. Import it.

Break Into Markets That Don’t Exist Yet

Some products are popular with respect to ecommerce: travel, tickets, events, electronics, books, apparel, and many more. However, there are many product classes and services that are not available online. Depending on where you live, find out some of these product classes that don’t have dedicated ecommerce outlets yet. For instance, we are yet to see an ecommerce store that sells only bed linen exclusively. Apply this approach to a range of products and you’ll have ideas popping up like popcorn. How about socks? What about selling distribution supplies or printing labels exclusively? Maybe you could specialize in printing products or supplies.

Go Specific: Dig Into Unexplored Niches

Niches are powerful. They give you exclusivity and enable you to quickly make a name for yourself within that niche. By picking up a focused niche, you don’t compete with the big boys such as eBay and Amazon. You are on your own and you’ll be known for that. What niche, exactly? Here are some ideas to kick-start your train of thought:

  • Apple products for self-employed professionals. Alternatively, sell Mac products only for corporates. More ideas? Sell stickers and designer covers only for Mac users.
  • Office supplies for work at home moms (and dads).
  • Web design services only for lawyers (or doctors, writers, dentists, video specialists, photographers, etc.).
  • Leather accessories for teens.
  • Travel for differently-abled people.
  • Apparel only for seniors.

That’s just the beginning. Grab a cup of coffee and brainstorm ideas as fast as you can breathe. You can pick and choose “the idea” later. You’ll know when the bulb goes on.

Ecommerce for Non-ecommerce Target Segment

Chances are that you live in a country where people might not be comfortable buying online yet. Or it could be a trend that’s still catching up only to raise its head slowly. Crash into markets like those and look to serve this entire group of customers who don’t wish to transact online.

Maybe you are looking at a target market that doesn’t qualify for credit cards yet (students, perhaps). Alternatively, set up an ecommerce store where they would place orders but you’d complete transactions traditionally by using modes such as “offline payments”, “Bank Transfers”, and “Cash on Delivery”.  Your goal – for this line of thought is simple – how do you bring ecommerce and wedge into a process that traditionally never had one? What customers don’t fall under the rush of ecommerce yet? Go serve them.

Reverse Ecommerce

We first heard about the term “reverse ecommerce” from Marc Ostrofsky who wrote the book Get Rich Click. There are likely to be segments of customers who’d never venture online to buy products or services. There might be customers who don’t even know (or don’t want to know) about such an option.

Think of it another way: there could be customers who still head to local malls and regular stores to buy products that are still expensive (owing to higher markups thanks to rent, utilities, and staffing that local businesses still have to pay for). Drive bargains to these customers. You can buy products on behalf of your customers on any of your online sources (and even bargain for deep discounts when buying in bulk) to make a profit off ecommerce without even putting up an ecommerce site).

Ecommerce arbitrage

Pick up products off an ecommerce store and sell it on another ecommerce store, which allows user listings. For instance, pick up products from Liquidation.com and then sell them on eBay.com.

Find closeouts, deals, and bulk discounts on other ecommerce stores to sell them again on your own ecommerce store for the usual price. It’s called ecommerce arbitrage and it’s a great way to be in business without actually scouting for products to sell and making deals with wholesalers and distributors (Note: this is not the same as drop shipping).

Learn From Others

There are plenty of ideas already in execution. You can learn tons from those ecommerce stores that have been put up and are already in operation. You could also learn from published ecommerce success stories. Gain inspiration from other entrepreneurs, throw ideas into the midst of communities and gain feedback from others. Contrary to what most people think, it’s ok to share ideas because most people won’t act on those ideas anyway. Even if people who can act and take advantage of your idea do spot it, they are likely to have too much on their plate.

Forget the Idea- Just Adopt a New Way of Doing Business

As we mentioned before, you don’t need any new ideas at all. You’ll just find new ways to do the old thing. Zappos isn’t the first ecommerce store that sold footwear. It’s, however, the first e-store that gave away 365 days of money back guarantee, no questions asked.  Try to find ways to do business that others did not attempt yet: provide free shipping and returns. Give unconditional money-back guarantees. Introduce sophisticated software on your apparel store that takes inputs from customers to determine the exact size.  Give freebies. Call up customers to invite them for lunch. Send them free coupons for coffee saying, “Enjoy the product. Coffee is on us”.

You get the drift, don’t you?

Which of these ideas will make you stare at the ceiling tonight? What’s on your mind?  If you are already on the road, go ahead and show off your ecommerce store. We’d love to visit.

Image Credit: Shutterstock mtkang

What’s in Your Wallet: How 11 Marketers Budget for SEO Tools

SEO tools are the key to success in the world of online marketing. However, since money doesn’t grow on trees, budgeting for SEO tools should be an integral part of any company’s marketing strategy. To get an idea of how the pros are doing it, I asked some internet marketers how they budget for SEO tools.

Tony AdamTony Adam – Founder, CEO at Eventup

“It really depends on the client or project. I tend to look at how simple the project is and if I’m just using the basics and free tools (e.g. Google Keyword Planner) or if it’s a larger project where I need enterprise data. Typically, I start with Authority Labs and Moz, because it sets my baseline, and I go from there. I would say I spend no more than $250 monthly on tools for a project, on average.”


Alan BleiweissAlan Bleiweiss – Forensic SEO Consultant at Alan Bleiweiss Consulting

“I don’t budget for SEO tools per se. I have several I already pay for to help me in my audit work, and from time to time I reevaluate if I think another tool will help me be more efficient, or provide more insights for clients in various situations.

From there, I consider whether the cost of the tool is worth the knowledge and increased efficiency or is worth the bother. That’s my entire ‘budgeting’ process for tools.”


Michael BonfilsMichael Bonfils – President at SEM International

“We assess every tool to make sure it’s a fit for the client. Enterprise clients usually see the value in broad, high-level reporting tools. Smaller clients tend to see the value in more strategic tools, which will give them an edge and insight into a specific niche. Many of our clients are agencies; in those cases we utilize their tools.”


jon_cooperJon Cooper – Founder of Hyperlynx Media

“Budgeting for about 80% of the SEO tools out there can be done simply by answering one question – do we generate more profits with it? Yes, there will be some management tools that you may need purely for scalability reasons, which are difficult to measure the ROI of in the short-term. However, for the vast majority of them, it hasn’t been difficult for me to nail each tool’s value down to a quantifiable number that makes the decision process easy.”


Annie CushingAnnie Cushing – Independent Consultant

“I budget for SEO tools according to the urgency of the data needs they meet. Tasks I do often, like data gathering for site audits, get star treatment as they are the most essential to my success as a forensic marketer.”


victoria_edwardsVictoria Edwards – Digital Strategist at Florida Blue

“While I am on the content side of my industry, I heavily focus on SEO. I’m constantly trying to promote the benefit of SEO and show that we need to invest in the proper tools to help us determine what we need to improve on. I am fooling around with Screaming Frog and may purchase a license, but as of now I rely heavily on Google Analytics and Web Master Tools. 2014 is being budgeted for now, and I’m trying to see about getting another tool, but basically just a lot of praising SEO for now and how we need to focus heavily on it, no matter what department you’re in!”


melissa_fachMelissa Fach – Moz Associate and Independent Consultant

“I don’t think you can make money in this business and/or be effective without tools, so they are part of monthly costs; I treat them like the electric bill. With each monthly client there is a cost that technically goes toward tools. The more clients you have the more tools cost. The word ‘how’ isn’t in it for me; ‘must’ is a better fit.”


jon_henshaw_smallJon Henshaw – Co-Founder of Raven Internet Marketing Tools

“When your budget is tight, SEO tools can seem very expensive. However, if you pick the right tool, it should easily pay for itself. For example, what’s cheaper? Paying someone hundreds, or in some cases thousands, of dollars to aggregate data and create monthly reports or paying $99 for an SEO tool that can create all of the reports for you?

The same is true with SEO tools that provide unique data. If a $49 per month tool can provide insightful data that can then be applied to profitable marketing tactics, then you should be paying for that tool. Ultimately, regardless of which SEO tools one uses, the cost of those tools should be factored into the service fees for clients or the budget for an in-house marketing department.”


jordan_kasteler_newJordan Kasteler – Digital Marketing Strategist at Vizion Interactive

“At Vizion Interactive we work the cost of the tools into our hourly rate for all clients. We look at the cost of tools and compare that to time savings. If it can save us more time than our hourly rate is doing it manually then we’re likely to sign up for that tool.”


melanie_mitchellMelanie Mitchell – Senior Digital Marketing Strategist 

“We have a yearly SEO tools budget where we evaluate several areas:

  • Cost
  • Benefit/Differentiator – either proprietary or can build custom data and reports to differentiate us
  • Overlap of reports across tools – in order to make decisions on if we still need to invest or can cut
  • Coverage – can it cover multiple media outlets (display, SEM, social, etc.) for large programs to look at consumer journey

We don’t silo SEO, as we feel it is important to understand what the consumer is doing overall and where to better understand the areas to place investments to test, optimize and scale. SEO tools are roughly 15%-20% of our overall media tool investment. We then submit the complete list of all media tools with monthly/yearly costs, ranked in priority order, to finance for approval. Some tools are an investment to use in pitches, some are used as a value-add for clients, while others may be an additional charge to the client.”


andrew_norcrossAndrew Norcross  Lead Developer at Reactiv Studios

“Same as any other budget item: if the tool matters, I find the money. Beyond that, I look for tools that have some stability, backed by solid support, and give me the ability to get data out if need be.”


How do you budget for SEO tools?


How to Create an Interactive Dashboard

Interactive dashboards are basically the sliced bread of data visualization. They are dynamic, easy to use, and compatible with Mac and PC. This tutorial is best applied to clients who are tracking a domain’s rankings in multiple locations. But, these principles can be used to make many interactive dashboards. This tutorial was heavily inspired by Annie Cushing, author of Annielytics, and her interactive chart blog post. Alright, let’s get started!

If you would rather watch the tutorial, here it is:

For you readers out there, carry on. There will be links to the specific part of the video tutorial for each of these steps.

Collecting the Data

I first started this dashboard by exporting the ranking data from my grouped site Olive Garden. Once you’ve collected the ranking data from each of your cities, compile that data into one document.

step1_compile exported raw data

Next, grab the Keywords, Google rank, and Search Volume for each of the cities and add that data to one table in your Raw Data tab. Pro Tip: The Raw Data tab is sacrosanct, it should be kept free of formulas and charts. Save that for the Calculated Data tab.

step3_raw data tab

Calculate the Data

The first step in calculating the data is creating a list of cities. You can type this out manually, use a VLOOKUP, or just copy and paste.

step4_list of keywords

The second step is a little bit more labor intensive. First make sure that you have the Developer tab open. You have to select this tab to viewable through File > Options > Customize Ribbon. Then you will need to create a Combo Box using the Form Controls.

step5_make combo box_step2

Once you have drawn out the Combo Box, right click to select Format Control.

step6_select formatting combo box_step2

Now here is the fun part. Under the Control tab, select the Input range to be the list of cities you made earlier. (That will be the list that your Combo Box shows.) Next, select your output, or Cell link. Lastly, change the Drop down lines to 4 (because we only have four cities).

step7_inputs combo box_step2

The third step is to put in an INDEX formula. This formula will allow you to look up the Cell Link we set up earlier, and output from our Cities list. This means that when you select Chicago, IL on the Dashboard tab, it will output the text, “Chicago, IL” in that cell.

step8_index formula_step3

Collect Location Specific Data

Bring over the data from the Raw Data tab with a SUMIFS formula. Never worked with SUMIFS? No problem, just follow this handy-dandy image. I would recommend referencing the video tutorial for this bit.

SUMIFS have two criteria that need to be met to output the data you’re looking for. The first criteria in our SUMIFS will be finding the Rank number for the Keyword “italian restaurant”.

step9_sumifs rank_step4

The second criteria will look to make sure that Keyword matches the location in cell F4 (where our INDEX formula is).

step10_sumifs rank_step4

The second SUMIFS formula is exactly the same as the first except for in the “sum_range” you select “Table1[Volume]” instead of Rank.

Concatenation Formula

In this step you will perform a simple concatenation formula. It will combine the text string “Keyword Ranks & Search Volume for ” and the city that the user will select from the Combo Box (which is in cell F4).

step11_sumifs rank_step5

Create the Chart

Create the chart by navigating to the Insert tab and selecting Recommended Charts > All Charts > Combo Charts. Make the Rank data a Line with Markers chart, and the Volume a Clustered Column chart. Also, move the Rank to a Secondary Axis. Move the chart to the Dashboard tab.

step12_creating the chart_step6

Format the Chart

Now that the Rank data is on a secondary axis, reverse the order of the values. This will allow you to view the Rank data in the order that one is positive, and 100 is negative.

step13_format axis_step6

I like to remove the line and just leave the markers. You can do this by right-clicking on the line and clicking “Format Data Series”. Also, add in the Primary and Secondary Vertical Axis Titles. That way we know which axis is Rank data and which is Volume data.

step14_format lines_marker_step6

Add the Chart Title

This is where the chart title we made in the Calculated Data tab will come into play. We want our chart title to automatically update when a user selects a different city.

step15_format lines_marker_step6


Congratulations on making your first Interactive Dashboard!