Learn about the New Google+ Interactive

November 21, 2013 Leave a comment

Learn about the New Google+ Interactive posts. They allow you to enhance your existing sharing by embedding a clickable Call to Action in your post. http://ow.ly/qWpUz

Categories: Uncategorized

Make the post to your Google Plus a bitl

November 18, 2013 Leave a comment

Make the post to your Google Plus a bitly and then link to it from your personal Facebook page. Click Promote and pay $7 to see the post. http://ow.ly/qWepd via @AimClear

Categories: Uncategorized

How to create a random A/B/n split in Excel

October 9, 2013 Leave a comment

I took over a test earlier this summer and one of the tasks I had to do was to validate the A/B/C split. I had to evaluate if my counterpart that proposed the original split had developed a random split with little to no bias between the data sets. In this post I’ll provide you with some quick and easy formulas you can use to automate the a/b/n split process as well as how to create a table that will help you know if there is bias in the split.


The RandBetween function in excel allows you to generate a random number between two variables. If you are doing a simple A/B split, you can set the fuction to =randbetween(0,1) and Excel will automatically divide half of of the rows of data to 0 and the other half to 1. If you need a three way split you can use the function =randbetween(0,2) down all rows of data you want split and you’ll see a 33% between 0, 1, and 2.

A few things to note with this function:

  1. The random split will automatically reset everytime you press enter within the workbook or make any updates. If you find a split you like, copy and paste special the variables into a new column so you don’t loose your split.
  2. The random element of the split does not guarantee that your split is unbiased. You still need to validate the split by creating a report that compares A/B/n to make sure that the variance is within your tolerance range. I’ll share a quick and simple example of how I did this so that I could hit enter until I found a split that appeared to be non-biased so I could do further statistical analysis.
  3. The randbetween function doesn’t allow you to develop a stratified split. If you need to develop a stratified split then you’ll have to embed the randbetween within an IF function and you’ll want to validate that both the split is random and valid and that the stratified variables are also evenly split between the test and control.
    What do I mean by stratified split? Check out this great article about Stratified Random Sampling by BetterEvaluation.org
    1. Evaluating the A/B/n Split for bias

      Everytime that you create a split you want to evaluate the split to see if there is any bias in the data sets. I discovered that I could use SUMIFS and COUNTIFs functions in XLS to create a table that would let me look at the output of the split to determine if the random sample appeared to be unbiased so I could then do my statistical analysis on the data set.

      Every time you enter a value into any cell on the tab that is calculating the SumIFs/CountIFs excel will randomly reassign 0,1 to the rows, giving a new potential split. Continue entering a value into a cell until the split is within your tolerance range. I try to keep my primary KPI variables of the test group within 0-5% variance to the control. A quick and easy way to visually tell if you’re split is less than 5% is to set conditional formatting for the cell background to “Green”,”Yellow”, “Red” based on the variance of test to control. Here is an example of what the A/B split table might look like and the formulas you would use to create it.

      Example of the table with the CountIF/SumIF formulas shown and conditional formatting set:

      Make sure you know what your test KPIs are because it can be difficult to find a random split where all metrics are within 0-5% variance between the groups. In this example I wanted to run a test to improve my Revenue and TXN volume, so the metrics I care the most about are Revenue, Transaction, and Return on Ad Spend. My goal is to find a split where these metrics are as close as possible, and then control paste values the split before I accidentally reset it by hitting the wrong button/cell. The screenshots below are a few examples of me hitting enter multiple times to go from an okay split, to a bad split, to a better split, to the best split.

      Split 1: Revenue variance is to large. I might copy and paste values for this split into a separate column to the far right just in case after 20-30 splits I can’t get a better combination, but since Revenue is one of my evaluation KPIs for the success of my test I should keep on looking.

      Split 3:Bad. Notice the large variances between Test & Control metrics

      Split 8: Still too much variance in Transactions

      Split 16: Best Split! Copy and Paste Values into a new column NOW. Notice in this split the variance in all of the core KPIs I’m going to use for evaluating my test performance appear to be within 5% of each other. Use this split for the statistical analysis.

      I’ve mentioned multiple times to do a separate statistical analysis post split. I’m not a stats guru so I work with a much smarter individual and team than myself, but they run multiple statistical significance calculations to determine if the split is valid. There are times when that amazing split that we saw at the end when analyzed to the mean and STD DEV are actually not valid the means are too far apart and so we have to resplit again until the means are very close together.

      I know that this won’t answer the question, “How do I determine if my split is valid?” or “How do I do the statistical calculations?” That’s not my area of expertise so I’ll try to find some links to post to help us all learn!

Categories: Analytics, Best Practices, Excel Tags: ,

Using SumIfs and CountIfs in Excel

September 5, 2013 1 comment

In the last two quarters I’ve been increasing the amount of functions I use on an on-going basis. The functions that I’ve fallen in love with recently that I’ve been using as much as if not more than Pivot Tables with Vlookups are “SumIF” and “CountIF” and their respectable “s” versions. Why do I love them? Because they can be just as effective for creating a high level report that would have previously required me to use a pivot table followed by a vlookup.

What is SumIf and CountIf

First off what are they? They are exactly what the sound like… It’s a either a count or sum of an array based on the array meeting some set of criteria. For example, I might want to know how many keywords I have in my Google Adwords account? (CountIF) or How many clicks all of my Mobile Targeted campaigns have received (SumIF.)

What is SumIFs and CountIFs

The plural form means that the sum or count only happens within the rows of the array when it meets multiple set of criteria. For example as a marketing manager in paid search I might want to know the count keywords on Google that are active (CountIFs – Counting Keywords based on two variables: SearchEngine=Google & Status=Active).
I might also want to know the total cost of all keywords on Google with Mobile Targeting (SumIFs)

How to write the SumIF OR CountIF Function

SumIFs and CountIFs are very easy formulas to write, if you have issues remembering the order of the criteria vs sum array look at the hint in the formula bar. Below is a sample set of data along with a CountIF function in action.
=CountIF(Range, Criteria) =CountIF(What do you want counted, What is the variable that should be counted)

=SumIF(Range, Critiera, [Sum Range]) =SumIf(Where to look for the variable, What is the variable, [Set of data if criteria are met that you want Summed]

Example of writing a CountIf Function in XLS

Taking it one step further as I mentioned above you when explaining the SumIFs and CountIFs variables you can create a table that looks up a preset data format and have it return variables based on multiple sets of criteria. For me this is very helpful with weekly reporting because I get my performance data in the same format each week. I Can essentially create a preformatted table where my data will be delivered and have the COUNTIFS/SUMIFS provide my high level summary of information on a “Performance Dashboard” that then would get distributed to my manager or leadership team.

Here is an example of using CountIFs and SUMIFs in an excel workbook:

I hope that you find these functions as useful as I find them and that you can save a little bit of time in your work day or work week by adding these simple formulas into your arsenal of XLS awesomeness!

Categories: Excel

Mentors and Mentoring

Mentors are essential in guiding and supporting us into becoming the best people we can be—professionally and personally. It’s a process of discovery and takes effort from both mentor and mentee. Here are some tips on cultivating effective mentoring relationships, and on being an effective mentor yourself.

The Mentoring Relationship
• Mentoring relationships develop over time—they’re an investment!
• A mentor is different than an advisor or a personal hero. A mentor is genuinely interested and invested in you.
• Being invested in you, and your long-term professional development and success, means the mentor can guide you toward the path that makes sense for you (not create a “mini-me”).
• Different mentors may play different roles in your life. They may act as one or all of the following: a guide, counselor, advisor, consultant, tutor, teacher, role model. Gathering together a team of mentors is an effective strategy to getting a variety of your needs met.
• Everyone is different. The more you know about your goals, needs and passions, and the more openly you can share these with your mentor, the better they can support you.
• How do you know if you have found a great mentor? A good place to begin is to ask yourself: Are they open, approachable and caring? Do they engage with you in ongoing conversation? Demystify the job/company for you? Provide constructive and supportive feedback? Provide encouragement? Foster networking and seeking out multiple mentors? Look out for your interests? Treat you with respect?
• Just getting started? Or haven’t found the right fit yet? (See point number one above!) Don’t give up, it’s worth it. Have “informational interviews” to find out more about the mentor’s work and their approach to work and leadership. These conversations will create a spark (or not) and you’ll know which relationships to invest more time in.
• Mentors can come from all directions. Peers can be great mentors for each other as well as family and people in industry circles.
• You, too, can be a mentor to others. Being able to give in both directions is the best way to learn and grow.

Categories: Career

How to respond when you’ve made someone upset or angry

I’m getting back into my leadership and mentoring mindset now that I’m going to be leading a team again. It’s an exciting thing to move from being an individual contributor to a manager– and one that I’ve missed since the dissolving of the iSEM team back in 2009. I’ve gotten back into reading management and leadership blogs and came across the article What to Do When You’ve Made Someone Angry on the Harvard Business Review.  It’s a great reminder for how to handle and deal with difficult situations where someone has gotten upset or angry and pertains to both your professional and personal life.

The article contains two examples that make it easy to relate and understand who to apply the easy principals to defuse the situation: an individual is 30 minutes late for a dinner date and a angry spiral of emails that stem from a work meeting gone bad. Both of these are use cases that apply to real life and that almost everyone has experienced in their personal and professional life.
A few highlights and quick reminders: a lot of strife comes from misunderstandings of intention vs. consequences.  While we mind not intend to upset someone our actions can, and instead of focusing on the intention we should focus on the consequences of our actions.

As it turns out, it’s not the thought that counts or even the action that counts. That’s because the other person doesn’t experience your thought or your action. They experience the consequences of your action.

So what do you do when you’ve angered someone?
Simple. So simple, that we over look it most of the time because we’re trying to get the other party to agree to our intentions, instead of acknowledging the consequences of our actions.

When you’ve done something that upsets someone — no matter who’s right — always start the conversation by acknowledging how your actions impacted the other person.

You can put it into three simple steps to create your response to the situation. Don’t attempt to justify. Apologize, accept responsibility, and attempt to change.

1. Regret
2. Responsibility
3. Resolution

(1) I’m sorry because (2) [action you are responsible for & consequence of action]. (3) How can I make it better?

Next time you see yourself going in the downward spiral of anger. Try to respond with the simple 1.2.3. answer and see if your need to justify your actions dissipates.

Robots.txt for Subdomains

April 18, 2013 2 comments

Do I need a unique robots.txt for each of my Subdomains?

The quick and dirty answer is yes. Spiders treat subdomains as separate website, and similar to how you create a unique robots.txt for each domain one should also be created for the subdomain.

When a spider finds a URL, it takes the whole domain name (everything between http:// and the next ‘/’), then sticks a ‘/robots.txt’ on the end of it and looks for that file. If that file exists, then the spider should read it to see where it is allowed to crawl.

In the case of multiple websites and sites with subdomains, the spider should try to access each of the sites, example: domain.com/robots.txt and subdomain.domain.com/robots.txt. The rules in each robots.txt file are treated as separate and unique so disallowing robots from domain.com/ should result in domain.com/ being removed from search results while subdomain.domain.com/ would remain unaffected and could still appear in the index. In some cases you can disallow an entire subdomain via the main websites robots.txt file, but if you notice pages appearing into the index it’s time to go back to best practices and place unique robots.txt files at the subdomain level.

Here is an excerpt from Bing Webmaster Tools speaking exactly to the example above:

Note that the host here is the full subdomain (us.contoso.com), not contoso.com nor http://www.contoso.com. This means that if you have multiple subdomains, BingBot must be able to fetch robots.txt at the root of each one of them, even if all these robots.txt files are the same. In particular, if a robots.txt file is missing from a subdomain, BingBot will not try to fall back to any other file in your domain, meaning it will consider itself allowed anywhere on the subdomain. BingBot does not “assume” directives from other hosts which have a robots.txt in place, associated with a domain.

Best Practice for Robots.txt

Placing a robots.txt on every domain and subdomain, every time.

Free Robots.txt Tools

Resources for more information about Robots.txt

Examples of valid robots.txt URLs from Google WMT

Information for this table is taken from Google Webmaster Tools Guide for Controlling Crawl Index

Robots.txt URL Valid for Not valid for Comments
http://example.com/robots.txt http://example.com/ http://other.example.com/ This is the general case. It is not valid for other subdomains, protocols or port numbers. It is valid for all files in all subdirectories on the same host, protocol and port number.
  http://example.com/folder/file https://example.com/  
http://www.example.com/robots.txt http://www.example.com/ http://example.com/ A robots.txt on a subdomain is only valid for that subdomain.
http://example.com/folder/robots.txt not a valid robots.txt file!   Crawlers will not check for robots.txt files in subdirectories.
http://www.müller.eu/robots.txt http://www.müller.eu/ http://www.muller.eu/ IDNs are equivalent to their punycode versions. See also RFC 3492.
ftp://example.com/robots.txt ftp://example.com/ http://example.com/ Google-specific: We use the robots.txt for FTP resources. http://example.com/ (even if hosted on A robots.txt with IP-address as host name will only be valid for crawling of that IP-address as host name. It will not automatically be valid for all websites hosted on that IP-address (though it is possible that the robots.txt file is shared, in which case it would also be available under the shared host name).
http://example.com:80/robots.txt http://example.com:80/ http://example.com:81/ Standard port numbers (80 for http, 443 for https, 21 for ftp) are equivalent to their default host names. See also [portnumbers].
http://example.com:8181/robots.txt http://example.com:8181/ http://example.com/ Robots.txt files on non-standard port numbers are only valid for content made available through those port numbers.

Get every new post delivered to your Inbox.