How to Tag A Google Analytics Campaign

Share Button

tagged google analytics campaign

Ready to get started measuring campaigns driving traffic to your site? For each link you put out—whether from email or social media properties—append your link with a query string using Google’s server syntax to identify up to 4 different content parameters of categorization, all of which can be tracked within Google Analytics.  You can find these reports under Acquisition > Campaigns.

What Does Tagging Enable Me To Do?

Think of tagging your campaigns as putting up turnstiles on paths in a park.  But not only do you get a count of how many people used that path, but you also put a stamp on the hand of everyone who comes through, so you can then see what attractions they visited, how long they spent in the park, and even if they bought ice cream.

Google Analytics can already tell you who your referrers are: whether they came from search, a social media platform, another website, or even directly.  But what about that one special announcement that you made?  Campaign tagging allows you to separate out traffic from specific sources, and see specific behavior that you wouldn’t have been able to observe before.

With this knowledge, you can evaluate landing page performance not in general, but from a specific set of ad copy.  You can track one Facebook link post versus just a picture, and even count conversions from various email campaigns.

So How Do I Tag My Google Analytics Campaign?

Google has a built-in URL builder worksheet that you can use to tag your campaigns, but for any large amount of links to be tagged they even recommend using a spreadsheet to create the same syntax. Well, I have built one already, and I’m willing to share! This past winter, I helped set up my first campaign for a not-for-profit’s conference, with over 120,000 site visits over the course of three months.

For our workflow, we used a collaborative Google Spreadsheet so that any member of the marketing team could tag their own URLs, whether it was the Marketing Manager for a big keynote announcement, or the Digital Communications Manager for a quick tweet. The sheet has five fields to enter text, including a URL and 4 content parameters. The last column simply concatenates each cell together–with the required character syntax in between–and we were able to quickly iterate tagging different messages for our campaign.

Click to view this free template that is publicly available, where you can copy the formulas and even the whole sheet if you’d like. Enjoy, and have fun splicing and dicing your data!

Google Analytics Campaign tagging Google Spreadsheet

How to Make a Weekly Heatmap From Time Data

Share Button

Do YOU have timestamps of some series of events?  Are you looking for a better way to view them than A-Z sorting, glazing over a stack of hundreds of numbers all bleary-eyed? Well, why don’t you try a heatmap!

This is beyond a simple graph.  A heatmap allows for three dimensions of data to be viewed, and the coloring of a heatmap will allow for quick identification of trends and clusters in points of time.

Most likely, your data will be retrieved from some type of streaming feed.  In this case, our registration database returned the timestamps that the “Submit” button was clicked for a completed (and verified) registration for a conference.  In this use case, the three dimensions will be:

  • Day of the week
  • Hour of the day
  • Site visits per hour

You can put them on timestamps, days of the week, frequencies of the digits of pi, or any other time-based metric you can think of.

I have broken this down into 5 steps:

  1. Prep data into desired units of granularity
  2. Stage data for counting in an array
  3. Create the display fields with bins
  4. Add counting “=frequency” formulas
  5. Add conditional formatting to display heatmap zones

1) Prep data into desired units of granularity.

We’ll need to break up the time and date in order to sort them into their desired bins of “day of the week” and “hour of the day.”  Like a Riemann Sum (remember those?), we will break up an otherwise continuous stream into chunks that we can identify.  These formulas are based on 24 periods in a day, but you could modify these into any size that you desire.

Days of the week

For days of the week

Hours of the day formula

For hours of the day:

2) Stage data for counting in an array

Don’t worry, we’re not going to count these ourselves.  We just need to arrange the data into organized columns that we can point a formula to count for us!

Arrange data into columns by days of the week:

Column into array

3) Create the display fields with bins

Now, create a graphical representation of a week, plotting ‘days of the week’ and ‘hours of the day.’  I just made the table a couple columns over from the data, but you could plan wherever you’d like to put this and link it as desired.

We have already sorted our data by one dimension (or axis) already, days of the week.  Now, the counting and sorting criteria (or “bins”) that we will select will be the vertical axis, ‘hour of the day.’

Days and hours array

4) Add counting “=frequency” formulas

Now comes the fun part, adding the formulas to do the work.  The goal here is to look back at the data array of the timestamps, count how many of each hour there are, and then report that number back for each unit that we have in our table.  This can be a little tricky, and I got a lot of help for this method from Eugene O’Loughlin’s video on YouTube.

The syntax for this formula is as follows:

=frequency(array, bins)

  • array: the column of data we will reference, in this case ‘day of the week’
  • bins: the counting and sorting criteria, in the case ‘hour of the day’

We will A. add this formula in the top cell of each day of the week, and then B. apply this formula to the rest of the column.

A. Add “=frequency” formula to first cell (click the image for a larger view)

Click for a larger view





B. Apply to the whole column: select whole row, click into formula bar, then type Cmd + Enter (or Ctl + Shift + Enter for PC).  Watch here if you would like the visual.

Then rinse and repeat for each day of the week.

5) Add conditional formatting to display heatmap zones

You will probably have something like this so far.  I included Sums rows on each side to ensure validity of data adding correctly.

You will probably have something like this so far.

So now you have a lovely table of numbers, but wouldn’t it be nice if all that black and white actually meant something?  Conditional formatting allows for you to quickly identify outliers to your data.

Before I got started, I included rows of Sums on each side for quick data validity to ensure adding was done correctly.

Select the data array, then select a Conditional Formatting ‘Color Scale’ option you like.  With my 2011 version of Excel (Version 14.3.8, from Office 365), there were several templates available.  No need to reinvent the wheel here, we just need some color to highlight our data.

Heatmap - conditional formatting






Conclusions and Applications

Heatmap - final


So there you have it!  From this heatmap, we now have solid data to suggest when the best times to convert are (in our case, obtaining registrations for our conferences and events).  I applied this same technique to our pageviews, and the results ended up being vastly different from when people actually decided to finalize their registrations.

Like any tool, you must understand what limitations the data have.  Ask your own application question before building this… or if you are a —P type personality, just make it and see what conclusions you might come up with.  After all, you’re the creative analysis guru!

Time Management is Like a Coding Project

Share Button


I was reading another time-management article recently, and sure enough they usually say the same types of things… but this time around I had a new realization: when working on any project with coding, the process shifts back and forth between mapping out the form and then going back in and creating the content. This seems to be one school of thought for coding processes, the other beginning with the eventual form in mind and linearly plowing through the project.  Both are pretty similar in principle, just differing in method. For managing time, the typically-recommended process has to do with breaking down projects into individual tasks, and laying out a time-bound schedule of when they should be completed. This is project management on a small scale, and this process is even more critical the larger the scope and more people involved in a project.

In particular, I was intrigued with these common themes:

  1. Direction: you need to know where the ship is pointed before you set the engine to full-steam ahead. Is this currently a priority? Is this the most urgent task at hand, or is there something needing to have time invested now?
  2. Structure: once you know the direction (i.e. specifications/requirements or goals), then the method must be laid out for how it will be accomplished. Will we take a great-circle arc?  Or would we value a scenic route along the coast?
  3. Details: once we know the general outline, then this is the stuff that actually makes it.  What route exactly will we take to get there? Between what rocks and cliffs will we traverse? What will the specific wording be? Which of the “if?then:other” statements will be “>=” or “>“? These must be decided in the moment.

My favorite approach to learning has been to dissect a concept, and learn what the pieces are for and how they function. This way, when put back together again, it is much easier to identify each piece in how they all fit, and appreciate how they work together. Time management for me still largely remains “do this, try that,” but the more musing done on a topic, the more able we will be in understanding how to best manage it.

Communication Cures Confusion (Celebrate Collaboration!)

Share Button

We’re all different.  We all have different perspectives.  How do you coordinate this variety to actually do something useful?

I was talking with a friend who is reading a book about methods of gaining and acquiring power. One of them includes being aloof, and withholding information. This leaves the other party in the dark to a degree, and creates a reliance on you–the beholder of knowledge–to bequeath to them what they desire when you see the time is fit. Ok, not the most ethical, or maybe not even the most mature, but I can see it.

In practice however, at my current internship and in my numerous group projects in business school, I’ve been frustrated with not being in the communication loop, but not for the reason of this method of acquiring power. I have also found myself on the side of unintentionally siloing information from group members, and feeling anxiety of them inevitably lashing out in distress… or just falling off the boat completely.

My experience both at my job and in group projects has been misdirected efforts, and frustration from incompatibility with pieces constructed in isolation. I’m sure this isn’t much of an off-beat observation. So while we don’t always have to disclose every detail, I am resolving to communicate intent and direction at every opportunity to ensure we’re going the same direction.

A quick caveat: I’m a middle child, I’m all about trying to blaze a new path and be original, be it in rhythms for a music setting or finding a new PowerPoint template that everyone hasn’t already used. Moreover, I firmly believe in diversity, where different elements coming together creates an amalgamation that is greater than the sum of the parts. So my approach to being different isn’t just to successfully find my ecological niche for survival, but because I’ve experienced that this posture of contributing something original is better for all.  This is the ideal I’m holding onto, at least.

From Juggling to Handling

Share Button

I’m currently in a class sponsored by Google, where my group is managing $10,000 (real USD) in an AdWords budget and working with a real-world non-profit organization with their awareness campaigns. I also just started a job at our university’s convocation center, helping with events and developing social media campaigns. And I’m taking the standard 15 credit hours of class and am involved in two student orgs.

I was about to lament on Facebook about how it’s so difficult to juggle all of these simultaneous responsibilities, and how it will be so cool to live and breath a single thing in a job some day. However, in a way, I think that’s the direction my classes and activities are moving… and it’s exhilarating. And I should be so blessed to be able to say that.

Honestly, I wish I could say something inspirational for anyone, like “just work where your heart leads you” or something, but I really think all of this has lined up once I told God “You created me and designed me, why don’t You show me where You’d like to see me get to work?” So while not entirely secular, I will say that I believe we’re all created to fill certain niches, and while I can’t say I know mine 100% yet, it’s gotten a lot easier since I’ve trusted that role to my Father.