Tuesday, April 28, 2015

Deploying a Mobile Application Isn’t Enough – Mobile Experience Analytics completes the puzzle

Deploying-A-Mobile-App

Deploying a Mobile Application Isn’t Enough – Mobile Experience Analytics completes the puzzle

Mobile Apps have become an integral part of everyday activities and a necessary part of how we all “do business”.  They are all the rage, and are only going to increase in popularity.  Gartner recommends that business leaders prioritize deploying mobile applications among their top initiatives for 2015.  The Bill and Melinda Gates Foundation recently identified mobile banking as one of the four exciting breakthroughs in their annual letter to shareholders.  With so much emphasis on building and deploying new mobile applications, there is potential for missing a key part of successful mobile experience strategies – Evaluating what works, and what doesn’t – through analytics and reporting.  ClickFox has analyzed hundreds of mobile applications as an important part of customers’ journeys, and our key learnings are highlighted below.
We’ve learned that Mobile is an incredible channel for so many different reasons.  When done well, it can be incredibly popular and “sticky” for customers.  For one of our clients, 75% of their customers who use mobile – use mainly mobile – with little reliance on other channels.  On another front, because it’s instant gratification and self-service (interpret, no human), it’s a great indicator of true customer needs.  The sensitivity of having to justify to another human being why you’re calling for the 6th time in 2 days to check on the status of your payment  is gone.  Since the mobile application a) doesn’t judge and b) is RIGHT THERE and begging for your attention, customers just do it.  While not the primary benefit, in this way mobile can also provide an invaluable tool for prioritizing proactive strategies to meet customers’ insatiable appetite for information.
It’s unlikely that you’ll get everything right the first time you deploy your mobile application.  Visibility into usage patterns, most popular sections, journeys to abandonment, and least popular functions can drive critical identification and prioritization of the next steps to take.  For instance, what if you learn through analytics that for every 100 customers who begin utilizing the mobile application, 50 of them don’t return after their first 1 to 2 visits?  Or, what if you learn that the mobile application is incredibly functional for those that use it, but only a very small fraction of your customer base actually does so?  Those 2 very different insights would lead to executing dramatically different strategies for improving your mobile application.  In the first example, identifying the journeys taken that lead to abandonment, segmented by customer profile, would go a long way towards identifying the root cause of the problem.  In the second, it would be paramount to identify the types of customers who are “finding” the app on their own vs. not, and crafting marketing strategies to approach those who need prodding.
Mobile is an important piece of the puzzle, but not THE only piece of the puzzle.  Remember when 15 years ago prognosticators predicted the death of contact centers, as the web would take over everything?  We certainly have seen a substantial increase in the percentage of customer activity that migrates from live employees to the web, but it also has created a new phenomenon – more educated and empowered customers – who ask increasingly complex questions of your live representatives.  Mobile will be important, but, just like the web, it won’t be the end-all be-all.  Understanding not only how your mobile application is being used, but also identifying how it “fits” with all of the other touchpoints customers use during their journey is especially important.   Our research indicates that journeys are over 30% more predictive than individual events or moments of time for key business drivers such as customer satisfaction, complaints analysis and reducing churn.
Creating adequate logging must be one of your “pillars” during the deployment and modification of your mobile application.  Without a “cookie-crumb” trail of customer activity, time stamps, and customer identity when possible, understanding mobile behaviors and linking them to the broader journey will be impossible.  With tight IT resources and short deadlines, it can be tempting to sacrifice ample logging during the development phase, but this deploys an application on a shaky foundation to begin with and leads to long term viability concerns right out of the gate.
In conclusion, mobile applications are of increasing importance everywhere in the enterprise space, not only in serving customers.  It’s essential that leaders do three things as part of these projects to ensure they have a great mobile experience for their customers and employees.
1) Create a mobile application that offers customers and employees the options they’re looking to complete.
2) Create adequate logging and tracking so a record is made of the experience that can be analyzed later.
3) Analyze mobile experiences independently and in combination with other channels so that the full journey is understood and mobile enhancements are more grounded and strategic with understanding of the full customer or employee journey in mind.
Following these strategies can lead to a great mobile experience for your customers and employees.  To learn more about how ClickFox helps our customers with mobile and other channels as a part of the customer journey, visit www.clickfox.com.

Monday, April 20, 2015

Los alemanes se deshacen de su peor trabalenguas - palabra de 63 letras.

Los alemanes se deshacen de su peor trabalenguas

  • 4 junio 2013
vaca
La palabra más larga del alemán describe una ley vacuna.
En español se necesitan 15 palabras -una frase entera- para describir la palabra más larga del idioma alemán, una de 63 letras que acaba de desaparecer:
Rindfleischetikettierungsüberwachungsaufgabenübertragungsgesetz.
Significa "ley para la transferencia de tareas de supervisión en el etiquetado de la carne vacuna", una normativa regional que fue reemplazada por las autoridades.
El trabalenguas -poco frecuente en el uso común- nunca apareció en el diccionario, pero sí fue la palabra más larga en un documento oficial. Ni siquiera su abreviación, que en teoría debe servir para facilitar las cosas, se salva de ser compleja: RkReÜAÜG.
El récord entre las palabras que sí figuran en el diccionario es para Kraftfahrzeughaftpflichtversicherung, más corta que la ley pero seguro un término que genera más dolores de cabeza para las personas comunes y corrientes porque significa "seguro obligatorio de automóviles".
Estas palabras son ejemplos extremos de una característica de este idioma: las largas y a veces enrevesadas palabras compuestas, que en alemán se definen, cómo no, con una palabra compuesta y difícil: Bandwurmwörter (algo así como "palabras de la lombriz solitaria", si se traduce literalmente).
Ahora, según los medios alemanes, se está buscando un nuevo candidato para el primer lugar.

Palabras infinitas

Tal como la normativa sobre la carne vacuna, la mayoría de las palabras más largas del idioma alemán provienen de leyes. En otros casos se trata de términos químicos.
Más allá de cual sea el significado, estas lombrices idiomáticas explican un fenómeno típico de esta lengua: en teoría es posible construir palabras infinitas, casi como si se tratara de armar un Lego.
Simplemente, a una palabra original, digamos una ficha de Lego base, se le van agregando otras fichas de distintos colores hasta que queda una figura nueva.
lego
Crear palabras en alemán es casi como jugar Lego. O eso dicen.
Parece fácil, pero se complica, como en este ejemplo.
Supongamos que el capitán de un barco (Kapitän) muere. Su viuda (Witwe), pasa a ser "Kapitänswitwe".
El capitán, además, trabajaba para una sociedad de barcos a vapor: Dampfschifffahrtsgesellschaft.
La viuda del capitán se convierte, como si nada, en Dampfschifffahrtsgesellschaftskapitänswitwe.
Y como el capitán navegaba por el Danubio y no por otro río, entonces su viuda termina siendo Donaudampfschifffahrtsgesellschaftskapitänswitwe.
La precisión alemana ante todo.

Otros idiomas

El alemán no es el único idioma que se complica de esa manera (o se facilita la vida, dependiendo de cómo se mire).
El finlandés y el húngaro también tienen la costumbre de alargar sus palabras según convenga.
El inglés y el español, por el contrario, tienden a escribir las palabras compuestas por separado.
estación de tren en Gales
¿Quién quiere bajarse en esta estación de tren en Gales?
Por ejemplo, la ley alemana de 63 letras se explica en español con 15 palabras y en inglés, con cinco (law delegating beef label monitoring).
Pero eso no significa que en esos dos idiomas no haya palabras interminables.
La palabra más larga del inglés, según aparece en el diccionario de Oxford, es pneumonoultramicroscopicsilicovolcanoconiosis, una enfermedad que 'apenas' tiene 45 letras.
Y en español, la palabra más larga registrada en el diccionario es electroencefalografista.
Puede ser enredada y poco común, pero al lado de Rindfleischetikettierungsüberwachungsaufgabenübertragungsgesetz -o incluso de pneumonoultramicroscopicsilicovolcanoconiosis- parece definitivamente un juego de niños.
O si no, pregúntenle a la viuda del capitán.

Thursday, April 09, 2015

Comparing Email Marketing Service Providers (ESP)




Media-sweet.com

Comparing Email Marketing Service Providers


This week I came across an article from We Rock Your Web about ESPs (Email Service Providers), and wanted to share their comprehensive (yet scannable) comparison chart of services. I especially like the glossary of email marketing terms they include at the end likeWYSIWYG and Double Opt-In – important to know if you want to understand how to manage email campaigns well. Take a look at their chart and let me know in the comments section which ESP you use and how you like it!
Email Marketing Service Provider Features Compared: Updated September 20, 2012. Click the image below to see full chart on werockyourweb.com.


Useful Comments to Guide Your Choice of ESP:
Really, the determining factor is going to be how comfortable you are with using the programs they have at your disposal for editing and managing your emails to clients.  Managing your email list is important too and you generally want to weed out any non-responsive or unwilling participants in your email list as soon as possible as you will eventually pay for them.
The chart listed here is a good way to start and see what kinds of options you have before you, but honestly speaking you will learn very little about a service or application until you get an opportunity to actually get in and play with it a bit and use it for a few small campaigns.  That way you can see what fits best for your needs and your style.  So get in and try some free trials!
It should provide a steady and easily implemented stream of data about what your customers are doing and how they are reacting to your email marketing campaign.  Constant Contact would tell us exactly what people were clicking on and how many people were actually spending time on our email and subsequently our webpage.
Email Marketing Glossary:
  • Surveys - email marketing surveys help you capture visitor data. Typically, surveys will let you:
    • Create public (anyone can answer) or private (only a specified subset of your subscribers can answer) surveys.
    • Choose different answer formats – text boxes, radio buttons, drop-downs, etc.
    • Redirect visitors to a custom thank you page upon completion of your  survey.
    • Export survey results to a file or spreadsheet for analysis (ie. by sorting columns you can gauge responses based on different variables).
  • WYSIWYG What You See IWhat You Get. This is used to describe a web editor interface that lets you design newsletter templates using buttons similar to those found in a word processor such as Microsoft Word. Examples of buttons include bold, italic, using headings, a button to insert images, and more.
  • Autoresponder – An autoresponder fires off an email to a subscriber automatically. A single autoresponder is most common – this typically just replies to a subscriber when they sign up. A sequential autoresponder is a little more advanced and allows you to schedule a series of predefined emails to be sent out on a specified schedule.
  • A/B Testing (Split Testing) – an Internet marketing technique that lets you test and see which elements of your email newsletter: subject line, text, graphics, design or layout, can be improved to increase your newsletter’s success rate.
  • Trigger-based messaging – lets you send messages based on your subscriber’s actions. They offer a more personalized interaction with your subscribers. Examples of trigger-based messages include a welcome email, a “happy birthday” message, a reminder to renew a subscription, a note that a new version of a product is available, a “we miss you ” note to a subscriber that hasn’t opened a newsletter in a while, etc.
  • List Segmentation – send messages to subsets of your subscriber base to help establish a stronger connection via more relevant newsletters. Customer segmentation marketing is becoming increasingly important as the consumer looks for more one-to-one communication in the Facebook age. You can use split testing mentioned above with list segmentation to test the efficacy of different subject lineslayouts, and content. You have the option of resending messages to subscribers who didn’t open or respond the first time. Finally, you can target subsets of your subscriber base and mail out promotions and offers based on demographics, purchasing history, geographic location, and more.
  • Attachments – most Email Marketing Service Providers (EMSPdo not allow attachments for a simple reason – they increase the probability of a given message being viewed as spam (as a result, the EMSP (and your) delivery rates may be affected. Attachments can also be flagged as computer virus files. You’re better off using inline links in your newsletter email, which you can then link to a hosted file. When the user clicks the link, they are given the option to download the file. Not as streamlined as an email attachment, but much more likely to allow the email to reach its full audience.
  • Spam Score/Checking – finding out whether your subscribers actually got your email is important. If they didn’t, why not? Some EMSP’s offerspam score utilities that help you determine the likelihood of your newsletter ending up in a spam box before you hit send.
  • Newsletter Archiving – the ability to save, or archive, your old newsletters. EMSP’s differ in the way they handle this feature. Some offer you to backup a certain quantity or volume of newsletters on their servers. Others offer export options that convert your newsletters into PDF or HTML files so you can download them and/or store them on your website. We point out that there is an SEO advantage to archiving your own newsletters (DIY).
  • Opt-In or Unconfirmed Opt-In: When an e-mail service provider requires a “opt-in” process, this means that e-mail recipients have agreed to receive e-mail advertising, e-mail marketing or e-mail communication specifically from the company or individual sending e-mails.
  • Double Opt-In or Confirmed Opt-In: When an e-mail service provider requires a “double opt-in” process that means that e-mail recipients have signed up to receive e-mail advertising, marketing or communications specifically from the company or individual sending e-mails. In addition, these individuals have confirmed (usually by clicking a link) that they did indeed, sign up to receive such e-mail communication.
  • No Opt-In Requirements: Very few e-mail service providers these days allow for e-mail addresses to be imported without any opt-in requirements. Opt-in requirements have been established to reduce SPAM mail and e-mail abuse.

Six Things Your AdWords Manager Won’t Tell You



Six Things Your AdWords Manager Won’t Tell You

by Kris Scheben-EdeyMay 3rd, 2011
secrecy

Google AdWords " by far the largest self serve advertising platform in the world " is also one of the best ways to drive qualified visitors on a timeline.  In just a few short minutes, you could have an advertising campaign up and running, driving visitors to your content while quickly eating away at your budget.
Many business owners opt to hire an AdWords manager to help manage their campaign, performance and advertising expenses as a way to save time, and improve the overall effectiveness.  A talented, proactive AdWords manager can be a phenomenal asset to any business and help accomplish your advertising goals within a budget and timeline.

Your AdWords manager can help you yield incredible results that on your own seem extremely difficult to achieve.  Just like a magician, your AdWords manager has some tricks up their sleeve; ones that allow them to perform on a scale which just doesnt seem like a possibility for one person to us. Also like a magician, your Adwords manager won't tell you all the tricks of their trade.
Want to know their secrets?  Would you like to know about those tricks your AdWords manager wont bother telling you?
Here are six of the top features and techniques used by AdWords managers to deliver professional results for your campaigns.

Keyword Suggestion Tool


The AdWords Keywords Suggestion Tool enables AdWords managers to quickly scale up campaigns by finding related keywords for each ad group.  Using the external version of the tool will also provide more data on keywords like search volumes, competition level, estimate average cost-per-click and more.

CPC Bid Automation


Recently AdWords introduced the ability to create automated rules.  These rules run at a certain time and can make real-time changes to the campaign based on preset criteria, like clicks, impressions or spending.
With a mastery of automated rules, your AdWords manager can effectively manage a high number of campaigns that would have traditionally required a team to manage.

Dynamic Keyword Insertions


Have you ever done a Google search and noticed an Adwords ad with keywords exactly matching your search phrase?  In general, it appears more relevant to the searched term - but more importantly, it's bolded and eye-grabbing.
Imagine that you could setup your ad to include the exact keyword your audience used to search - and matching it so when they search, your ad shines with the perfectly relevant solutions.
This is the magic of dynamic keyword insertions. Its a neat trick, and an AdWords managers best friend when writing out ads.

Real-Time Alerts


Now that your AdWords manager can effectively create and scale up campaigns, management becomes the focus.  The alert system gives AdWords managers the ability to effectively keep a tab on their advertising efforts and send a signal if anything seems out of place.

Radius & Custom Shape Geo-Targeting


Its common to target a location by simply choosing an area, city, or zip/postal code segment.  However, with custom shape targeting, your AdWords manager can more accurately target their audience with the geological targeting tools.
Using radius or custom shape targeting tools, you can specify a radius around a point to target - or create a custom shape overlay on a map of your target areas.

Historical Campaign Performance


Did you know that your past AdWords performance can have a real effect on your current campaigns, their quality scores and therefore your first page bid price?
Within your account, historical performance on metrics like clicks, impressions, and clickthrough rates are taken into account.  Having a track record of great converting campaigns will result in a slight quality score bonus for all new keywords in a campaign increasing display rankings, lowering bids and making an AdWords managers job a lot easier.
Kris Scheben-Edey
I'm a young marketing professional passionate about all forms of marketing. Currently,
I have the opportunity to work with Switch Video which specializes in animated explai-
ner videos and corporate video production. Fun fact? I have as many PJ's as pants... and
 I love it!


Read more: http://www.searchenginepeople.com/blog/adwords-manager-secrets.html#ixzz3Wp7JWaWl

Becoming AdWords certified

Image result for google


Becoming AdWords certified

  • The AdWords certification is a professional accreditation that Google offers to individuals who demonstrate proficiency in basic and advanced aspects of AdWords.
  • Why get certified? An AdWords certification allows you to demonstrate that Google recognises you as an online advertising professional.
  • When you sign up for Google Partners, you'll get access to the free AdWords certifications exams and related study material.

Benefits of becoming AdWords certified

Any member of Google Partners can become AdWords certified. Here are the benefits of becoming certified:
  • Demonstrate your expertise: Show current and prospective clients that you're a certified online advertising professional with a personalised, printable certificate and a Google Partners public profile page listing the exams that you've passed and the certifications that you've earned.
  • Help your agency achieve Partner status: To earn the Google Partner badge and get listed on Google Partner Search, agencies need at least one of their affiliated members to be AdWords certified, in addition to other requirements. Badged agencies can promote the areas that they specialise in on their Google Partner Search profile, showing the certifications that their employees have earned and the number of people who have earned each certification. Learn more about the requirements to qualify for Google Partners status.

How to become AdWords certified

First, you'll need to sign up for Google Partners and create an individual profile. This will give you access to the free AdWords certification exams and related study material. Once you get certified, you'll be able to access your personalised certificate and Partners public profile page. If you work at an agency, you'll also want to affiliate your individual profile with your agency's company profile in order to help your agency achieve Partner status.
The AdWords certification includes exams that test your knowledge of AdWords and Google advertising products such as Google Merchant Center. The AdWords advertising exams are designed to test your knowledge of basic and advanced advertising concepts, including the value proposition of online advertising, and AdWords campaign setup, management and optimisation.
Here are the available AdWords certification exams:
ExamDescription
AdWords FundamentalsThe AdWords Fundamentals exam covers basic and intermediate concepts, including the benefits of online advertising and AdWords, and best practices for managing and optimising AdWords campaigns.
Search AdvertisingThe Search Advertising exam covers advanced concepts and best practices for creating, managing, measuring and optimising Search campaigns.
Display AdvertisingThe Display Advertising exam covers advanced concepts and best practices for creating, managing, measuring and optimising Display campaigns.
Video AdvertisingThe Video Advertising exam covers basic and advanced concepts, including best practices for creating, managing, measuring and optimising video advertising campaigns across YouTube and the web.
Shopping AdvertisingThe Shopping Advertising exam covers basic and advanced concepts, including creating a Merchant Center account and product data feed, and creating and managing Shopping campaigns.

Note

The Shopping Advertising exam is available in the following languages: Chinese (Simplified), Czech, Danish, Dutch, English (UK), French, German, Italian, Japanese, Norwegian, Polish, Portuguese (Brazil), Russian, Spanish, Swedish, Turkish.

AdWords certification requirements

To become an AdWords certified professional, you'll need to pass the AdWords Fundamentals exam and one of the additional advertising exams: Search Advertising, Display Advertising, Video Advertising or Shopping Advertising. Learn more about AdWords exams.

Sharing your certified status with others

Once you have been certified, you'll be able to share your certified status with others by showing them your personalised certificate or Google Partners public profile page. Read each section below to learn how to access each one.

Accessing your personalised certificate

Once you have been certified, you'll be able to access a personalised, printable certificate from the "My profile" page in your Partners account. Follow the steps below to print your certificate.

Accessing your Partners public profile

You can demonstrate your expertise with your public profile, a read-only page that lists your AdWords certified status and all of the exams that you've passed. This page is a unique URL that's hosted on a google.com domain, and you can choose who can access it.
To get your public profile's URL and choose who can access it, go to the "Public profile" section of your "My profile" page. Bear in mind that by default, your profile is visible to you only.
Here's what will appear on your profile page:
  • Your name: This name will be the same name that you use for your Google account. If you'd like to change your name, read the section below on updating your name.
  • Agency's name: If your Partners individual profile is affiliated with a company profile, we'll show your agency's name and link to its Partners profile, if the profile is published in Google Partner Search.
  • Photo: Your Google+ profile photo will automatically appear on your Partners profile page (note that your Google+ profile photo is always visible to the public). If your Google+ profile doesn't have a photo, a photo won't appear on your Partners profile page.
  • Google+ profile: A link to your Google+ profile will automatically appear on your Partners profile page. Bear in mind that you can choose who sees sections of your Google+ profile for certain people. Learn how to choose who can see sections of your Google+ profile.
  • Certifications: We'll show your AdWords certified status and a list of the exams that you've passed.

Tip

Not on Google+ yet? If you don't have a Google account connected to your work email address, learn how to create one and access Google+. If you already have a Google account connected to your work email address, find out how to add a photo to your Google+ profile.

How to share your Partners public profile

By default, your profile will only be visible to you, which means that no one will be able to see your profile page even if they have the URL to it. To share your profile with others, you'll need to make it visible to the public. Follow the steps below to edit your profile's visibility settings.

Updating your name on your certificate or public profile page

If the name that appears on your individual certificate or public profile page is incorrect, you'll need to update this information in your Partners account. To do so, you'll need to edit your Google account information.

Guidelines for communicating your certification to others

Your AdWords certifications demonstrate that you're a certified online advertising professional. Here's what you can say to current and prospective clients about this recognition:
  • Your AdWords certification recognises that you're a certified online advertising professional.
  • You received this accreditation after successfully passing the AdWords Fundamentals exam and either the Search Advertising, Display Advertising, Video Advertising or Shopping Advertising exam administered by Google Partners.

Example

Here's an example of what you can say about your certified status:
“Google has recognised me as an AdWords certified professional, meaning that I’ve passed multiple exams that assess my product expertise. I'm qualified to help you grow your business on the web using Google AdWords”.
Here are some additional guidelines about communicating your certification:
  • You may only refer to yourself as certified if you, personally, have passed the certification exams. It's not enough for a colleague to be certified or for your agency to be badged.
  • You can refer to yourself as "certified" as long as your certification remains in effect. After the expiry date, you won't be able to refer to yourself as "certified" until you pass the exams again.
  • You're allowed to mention your certification on your resume, business cards, LinkedIn profile and other social media profiles. Bear in mind that Google Partners logos can only be used in accordance with our usage guidelines.

Common questions about certification

The difference between AdWords certifications and the Partner badge

  • Individuals are certified. Any member of Partners can become AdWords certified. As a certified professional, you can demonstrate your achievement with a personalised certificate issued by Google.
  • Agencies are badged. An agency that meets the requirements for Partner status will earn the Google Partner badge and be allowed to promote itself as a "Google Partner." Note that an agency doesn't become a "Google Partner" simply by joining the programme.

How To Track Your AdWords Competitors Over Time Using Auction Insights

Image result for search engine land

How To Track Your AdWords Competitors Over Time Using Auction Insights

Former Googler Daniel Gilbert shows you how to turn the static AdWords Auction Insights data into a report that tracks your closest competitors over time.

It’s good to know what the competition is doing. While you control your bids, it’s your competitors who determine what you actually need to pay and where your ad will be positioned.
There are third-party tools that provide some competitor information, but their data on average position, impression share, outranking share, etc., will never be as accurate as the actual AdWords data that you can get from the Auction Insights report. The only issue with this report is that it isn’t easy to see changes over time, which is what you want to do if, say, you’re trying to understand changes in cost-per-click (CPC) or average position.
Irritatingly, the Auction Insights report isn’t available in AdWords Scripts (or even in the API) — if you want to do anything with it, you have to download it from AdWords manually. However, that doesn’t mean you have to do everything manually! AtBrainlabs (my employer), we use a simple Google Apps Script that we’ve shared below to turn the data into a readable format and add graphs like the ones featured below.
Google Apps Scripts are quite similar to AdWords Scripts. They allow you to automate things like creating sheets, charts and formatting. Our script below will pick out your top five competitors based on their highest impression share and show you how their presence has varied over time (once you’ve followed the steps below).
Note: Auction Insights are available for Search and Shopping campaigns, although Shopping campaigns get fewer columns. This script should work for either.

How To Use The Script

Go to Google Drive, click the red “NEW” button, and click on “Google Sheets.” Inside the new spreadsheet, click on “Tools” in the top menu and then “Script editor….”
image001
This will open a script editor in a new tab. Click on “Google Sheets Add-on.”
Google Apps Script screen
There will be some sample code — delete all of this, and paste in the script that’s at the bottom of this article. You can change a couple of settings at the top if you want. There are variables called dateFormat and currencySymbol, which are used for formatting.
script code
From there, go to “File” and save the script. It will ask for a project name, so call it something like “Auction Insights.” (It doesn’t matter what you name it.)
image007
In AdWords, get the Auction Insights report: Go to the campaign, ad group or keyword tab and select the campaigns/ad groups/keywords for which you want the report. Click the “Details” button (it’s between “Edit” and “Bid strategy”); then, under Auction Insights, click “Selected.”image009
Make sure you’ve got the date range you want to cover, then download the report, adding a segment for “Month” or “Week” (depending on how long a time range you’re looking at).image011
This will give you a CSV file. Open it in a text editor (Notepad, for example), then copy it into Sheet1 of your Google spreadsheet. (We suggest using a text editor rather than Excel because Excel may reformat the dates in the report, and then when it’s pasted into the Google spreadsheet it may get confused over which digit is the day and which is the month.)
image013
The onEdit function should trigger automatically after you paste the report in — it should create a sheet for each of the report column headings and fill them with data and charts.
image015
If you want to add in CPC data, add another sheet to the spreadsheet (named Sheet2) and copy in any AdWords report that contains the clicks and costs over the date range (segmented by month or week, the same as the Auction Insights report). It doesn’t matter if the pasted report has multiple campaigns/ad groups/keywords: the script will add up everything for each date to calculate the average CPC.
The onEdit function will trigger again, updating the REARRANGE functions so that they take in the CPCs. Having your CPC is useful: you can see how your bid changes affected the auction, or if a new competitor caused clicks to become more expensive.
image017

image019
In the next version, we’re thinking of allowing more flexibility to choose which competitors to include. If you can think of any other improvements, let me know in the comments below!

/**
* Brainlabs Auction Insights Report Tool
*
* This script will take data from an Auctions Insights report and use
* it to create a sheet for each column heading, with the data for the
* your domain and the top 5 competitors over time.
*
* Version: 1.0
* Google Apps Script maintained on brainlabsdigital.com
**/

var dateFormat = 'yyyy-MM-dd';
// The date format to be used in the tables and charts
// Can be replaced with 'dd/MM/yyyy' or 'MM/dd/yyyy' if preferred

var currencySymbol = "£";
// The symbol used for formatting cells as currency
// Can be replaced with "$", "€", etc
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Information about the different columns of the Auctions Insights report

var searchColumns = ["Impr. share", "Avg. position", "Overlap rate", "Position above rate", "Top of page rate", "Outranking share"];
// These are the different columns from the Search Auctions Insight report (in order)

var shoppingColumns = ["Impr. share", "Overlap rate", "Outranking share"];
// The columns for the Shopping campaign Auctions Insight report (in order)

var includeYou = ["Impr. share", "Avg. position", "Top of page rate"];
// These are the columns where there is data for your domain (referred to as "You").

var subtitle = {};
subtitle["Impr. share"] = "How often a participant received an impression, as a proportion of the auctions in which you were also competing.";
subtitle["Avg. position"] = "The average position on the search results page for the participant’s ads when they received an impression.";
subtitle["Overlap rate"] = "How often another participant's ad received an impression when your ad also received an impression.";
subtitle["Position above rate"] = "When you and another participant received an impression in the same auctions, % when participant’s ad was shown in a higher position.";
subtitle["Top of page rate"] = "When a participant’s ads received impressions, how often it appeared at the top of the page above the search results.";
subtitle["Outranking share"] = "How often your ad ranked higher in the auction than another participant's ad, or your ad showed when theirs did not.";
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to create new sheets and charts
function onEdit() {
// We first check what sort of Auctions Insight report there is
// as the Shopping report has a different set of column headers
var title = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue();
if (title.substr(0,7) == "Auction") {
// This means the Auctions Insights report is for Search campaigns
var columnHeaders = searchColumns;
} else if (title.substr(0,8) == "Shopping") {
// The report is for Shopping campaigns
var columnHeaders = shoppingColumns;
} else {
// The title presumably hasn't been copied in yet
// The columnHeaders array is set to an empty array so the for loop below won't try to run
var columnHeaders = [];
}

// Loop through all the columns
for (var g=0; g<columnHeaders.length; g++) {

// We try to go to the sheet for the current column
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(columnHeaders[g]);
if (sheet == null) {
// If the sheet doesn't exist, create it
var newSheet = true;
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnHeaders[g]);
} else {
var newSheet = false;
}

// Because there may not be a Sheet2 yet, or it might not have the CPC information yet, we check
// whether we should include CPCs or not, and only use a REARRANGE function with Sheet2 if it's there.
// REARRANGE has three inputs: the column name, the Auctions Insights report and the CPC performance
// If there is no CPC performance then the third input is set as a single cell
// The REARRANGE function (see below) will check for this
if (SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2") == null ||
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("A1").getValue().length == 0) {
sheet.getRange(1,1).setValue('=REARRANGE("' + columnHeaders[g] + '",Sheet1!A:H,Sheet1!A1)');
} else {
sheet.getRange(1,1).setValue('=REARRANGE("' + columnHeaders[g] + '",Sheet1!A:H,Sheet2!A:Z)');
}

// If we just created this sheet we need to add formatting and charts
if (newSheet) {
var numRows = 0;
var numColumns = 0;

// These are set to 2 and 1 because the data from REARRANGE starts in cell A2
var startRow = 2;
var startColumn = 1;

// Look at cells to the right of the starting position until a blank one is found,
// to find the number of columns that contain data
for (var i=0; i<10; i++) {
if (sheet.getRange(startRow+i,startColumn).isBlank()) {
numRows = i;
break;
}
}

// Find the number of rows that contain data
for (var i=0; i<50; i++) {
if (sheet.getRange(startRow,startColumn+i).isBlank()) {
numColumns = i;
break;
}
}

// Format the first column (the dates) as dates
sheet.getRange(startRow+1, startColumn, numRows-1, 1).setNumberFormat(dateFormat);

// Format the second column (the CPCs) as currency
sheet.getRange(startRow+1, startColumn+1, numRows-1, 1).setNumberFormat(currencySymbol + "0.00");

if (columnHeaders[g] == "Avg. position") {
// If the data is average positions, format it as a number
sheet.getRange(startRow+1, startColumn+2, numRows-1, numColumns-1).setNumberFormat("0.0");
} else {
// Otherwise format it as a percentage
sheet.getRange(startRow+1, startColumn+2, numRows-1, numColumns-1).setNumberFormat("0.00%");
}

// Get the width in pixels for the chart, based on the chart being a few columns wider than the data
var width = 0;
for (var i= startColumn; i< startColumn+numColumns+2; i++) {
width += sheet.getColumnWidth(i);
}

// Creates the chart
var chart = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.addRange(sheet.getRange(startRow, startColumn, numRows, 1))
.addRange(sheet.getRange(startRow, startColumn+1, numRows, 1))
.addRange(sheet.getRange(startRow, startColumn+2, numRows, numColumns-2))

.setOption('series', {
0: {targetAxisIndex: 1},
1: {targetAxisIndex: 0}
})

.setOption('vAxes', {
// Adds titles to each axis.
0: {title: 'Percentage'} ,
1: {title: 'CPC'}
})
.setOption('chartArea', {left:'10%',top:'15%',width:'80%',height:'70%'})

.setPosition(startRow + numRows + 1, startColumn, 0, 0)
.setOption('width', width)
.setOption('height', 500)
.setOption('title', columnHeaders[g] + " - " + subtitle[columnHeaders[g]])
.setOption('legend', {position: 'top'})
.build();

sheet.insertChart(chart);
}

}
} //end function onEdit
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to create new sheets and charts
function REARRANGE(columnHeader,auditInsights,performance) {

// Dates are stored as bigendian date strings, then converted back to dates at the end
var bigendianDate = 'yyyy-MM-dd';
// The timezone is used to convert them back
var timezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

var domains = {};
var dates = [];
var domainNames = [];

// First we record the stats for each domain, by month
// and record each domain's highest impression share
for (var i = 2; i<auditInsights.length; i++) {
// auditInsights is a multi-dimensional array containing the values of the auditInsights cells.
// So auditInsights[i] is a row on the Auction Insights report
// The loop starts at 2 as auditInsights[0] is the title and auditInsights[1] is the headers.

var date = auditInsights[i][0];

if (!date) {
// If the date field is blank, we have reached the end of the data
// so we end the for loop
break;
}

if (typeof date != "string") {
// The date is converted into a string
date = Utilities.formatDate(date, timezone, bigendianDate);
}

if (dates.indexOf(date) < 0) {
// If the current row's date isn't in the dates array, it's added
dates.push(date);
}

var imprShare = auditInsights[i][2]; //the impression share
if (imprShare == "< 10%") {
// If the impression share is "< 10%" (a string) it is changed to 5% (a float)
// so it can be displayed in the graph.
imprShare = 0.05;
}

var domainName = auditInsights[i][1];

if (domainNames.indexOf(domainName) < 0) {
// If the current row's domain name isn't in the domainNames array, it is added,
// and an entry for it is entered into the domains object.
domainNames.push(domainName);
domains[domainName] = [];
domains[domainName]["Max Impr Share"] = imprShare;
}

domains[domainName][date] = [imprShare].concat(auditInsights[i].slice(3));
// This is (in order) the impression share, avg. position, overlap rate, position above rate, top of page rate, outranking share

if (imprShare > domains[domainName]["Max Impr Share"]) {
// If the current imprShare is bigger than the last recorded max impr share,
// the current one is recorded as being the max
domains[domainName]["Max Impr Share"] = imprShare;
}

} // end of for loop

// Next we get the costs and clicks from Sheet2 (if it exists), to get the CPC
// If Sheet2 exists, performance will be a multidimensional array. If it doesn't it will only contain 1 cell.
var hasCPC = (performance.length > 1);
var costTotals = [];
var clicksTotals = [];

if (hasCPC) {
// Dates should be in the first column, but the position of the cost and clicks columns varies depending on AdWords settings
// So we set variables to record the required column numbers
var costIndex = performance[1].indexOf("Cost");
var clicksIndex = performance[1].indexOf("Clicks");

for (var i = 2; i<performance.length; i++) {
var date = performance[i][0];

if (!date) {
// If there's no date we've reached the end of the data
break;
}

if (typeof date != "string") {
// If the date isn't a string, convert it into one
date = Utilities.formatDate(date, timezone, bigendianDate);
}

if (costTotals[date] == undefined) {
costTotals[date] = performance[i][costIndex];
clicksTotals[date] = performance[i][clicksIndex];
} else {
costTotals[date] += performance[i][costIndex];
clicksTotals[date] += performance[i][clicksIndex];
}

} // end of for loop
}

dates.sort();
// Sorts the dates alphabetically - as they're in bigendian format, this means they are sorted oldest to newest

domainNames.sort(compareDomainNames);
// Sorts the domain names by their highest impression share, using the function below

function compareDomainNames(a,b) {
if (domains[a]["Max Impr Share"] != domains[b]["Max Impr Share"]) {
// If the max impression shares are different, the domain with the highest is put first
return domains[b]["Max Impr Share"] - domains[a]["Max Impr Share"];
} else {
// If both domains have the same max impression share, the one with data for the most dates is put first
return Object.keys(domains[b]).length - Object.keys(domains[a]).length;
}
}

domainNames.splice(domainNames.indexOf("You"),1);
// Removes "You" from the array

if (includeYou.indexOf(columnHeader) > -1) {
// If this graph is supposed to include 'You', then it's added to the start of the array
domainNames.unshift("You");
}

var g = auditInsights[1].indexOf(columnHeader)-2;
// The index of the required stat

if (g < 0) {
// Error checking - if the columnHeader wasn't a recognised title, we output an error message
return [[columnHeader + " not recognised."]];
}

// 'output' is a multi-dimensional array that will become cells in the spreadsheet
output = [];

// The first row of the output is the column name
output[0] = [columnHeader];

// The second row of the output is the headings
output[1] = ["Date","Avg. CPC"];
for (var d = 0; d<domainNames.length && d<6; d++) {
output[1].push(domainNames[d]);
}

// We loop though the dates to make their lines of output
// (the date, the CPC, then each domain's metric)
for (var i = 0; i<dates.length; i++) {
output[i+2] = [stringToDate(dates[i])];

// Calculate the average CPC
if (costTotals[dates[i]] == undefined || clicksTotals[dates[i]] == undefined || clicksTotals[dates[i]] == 0) {
output[i+2].push(0);
} else {
output[i+2].push(costTotals[dates[i]]/clicksTotals[dates[i]]);
}

for (var d = 0; d<domainNames.length && d<6; d++) {
if (domains[domainNames[d]][dates[i]] === undefined) {
output[i+2].push(0);
} else {
output[i+2].push(domains[domainNames[d]][dates[i]][g]);
}
}
}

return output;

}// end function REARRANGE
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function is used by REARRANGE to convert date-strings back into dates
function stringToDate(string) {
var dateBits = string.split("-");

var date = new Date();
date.setFullYear(dateBits[0]);
date.setMonth(parseInt(dateBits[1],10)-1);
date.setDate(parseInt(dateBits[2],10));

return date;
}

Some opinions expressed in this article may be those of a guest author and not necessarily Search Engine Land. Staff authors are listed here.

SMX Advanced is the only conference designed exclusively for experienced paid search advertisers and SEOs. You'll participate in experts-only sessions and network with fellow internet marketing thought leaders. Check out the tactic-packed agenda!