exploring how to make seo campaigns more efficient and insightful through google docs and excel


Photo

Aug 26, 2012
@ 12:44 pm
Permalink
454 notes

cjwho:

Creative Bachelor Pad Mezzanine

cjwho:

Creative Bachelor Pad Mezzanine

(Source: cjwho, via ethanlyon)


Text

Sep 13, 2011
@ 9:40 pm
Permalink

Excel: How To Reverse Date Ranges

The problem:

If you have a data set with a date range that begins with today and want to compare it to a data set that begins with the earliest day, you need to normalize one of the date ranges if you want to compare the two data sets. Make sense? If not, take a look at the examples below to see how you can use this formula. 

Instance where you might need this:

If you have two timelines that you’d like to compare, but need them both to begin with the earliest or oldest date. Some APIs might give you different dates that you’ll need to normalize in order to compare them to other API data sets.

Example:

The formulas:

Now you can compare them!

The formulas:

Voila

We now have two data sets with inverse data ranges that you can compare. You can now make a fancy chart in Google Docs:

Alternative

You can use Vlookup on the date to get the conversions, regardless of how the date range is presented. This is just a fun exercise on how to normalized inverse date ranges :)


Text

Sep 11, 2011
@ 10:51 am
Permalink
1 note

Google Docs: Combining and Splitting into Multiple Columns

The problem:

If you have two different date formats that you need to normalize in order to compare two data sets, you can use the below script in Google Docs. 

Instance where you might need this:

An instance where you might use this script would be if you bring GA data into Google Docs and other date formats (say from a ranking API) and need to create charts that compare them side by side. If the formats are different, you cannot use vlookup to compare them and get the most accurate timeline.

Example:

This script in Google Docs will combine a year and month with a dash and split the year and month from conversions.

If your other data set has the same yyyy-mm formatting, you can use vlookup to compare the data.

The script:

Here is how to add scripts in Google Docs. 

Copy and paste the code into Google Docs script editor

/***************************************************************************

Script Experiment

***************************************************************************

combines the year and month in yyyy-mm format and splits yyyy-mm from conversions*/ 

function dateConv(cellYear, cellMonth, cellConv){

  /*if the cell is not equal to nothing, execute this function*/

  if (cellYear != “”) {

    /*combines the year and month with a dash between them*/

  var combineCells = cellYear + “-” + cellMonth;

    /*adds a pipe that will be used to split the year and month from conversions*/

  var pipeConv = “|” + cellConv;

    /*combines the year, dash, month, pipe and conversions*/

  var combineAll = combineCells + pipeConv

   /*splits the year and month into col1 and conversions into col2*/

  var mySplit = combineAll.split(“|”)

  return mySplit 

    }

    }