You’ve been monitoring number of daily website visitors from Google hoping that your latest marketing campaign is driving incremental business.  The next day you get an e-mail that the executives want to weekly and monthly performance of your marketing campaign in a PowerPoint presentation.   So you have a list of results by day, and need to summarize and aggregate the results by week and by month.  You could do this any number of ways, but Excel Pivot tables are probably the fastest and easiest way to do this.

The tip I’m going to run over will take you allow you to quickly aggregate daily results into weekly (or any other day partition you define) and monthly groups.

We’re going to go from this:

Image

to this:

Image

Step 1.  Insert a Pivot Table into your Excel sheet:

Image

Step 2. With the Pivot Table created, check off the date field and any other fields that you want to group by time period (week, month, etc.).  In this case I am selecting “Day” and “Google Visits”:

Image

Step 3.   This is nifty little trick inside Excel that does all the magic.  Right clicking on any date field, you’ll notice a “Group” and an “Ungroup” option in the modal popup.  Select the “Group” option:

Image

Step 4. In order to group by month, select the “Months” option.  You’ll notice other options available as well for Day, Quarters, Hours, etc:

Image

Voila, the final result:  Total visits summarized by month.

Image

Your boss now wants to meet up with you weekly to see if you’re hitting your weekly goals.  A good way to visualize this is to break the data down into a column chart by week.

Step 5. Right click-on the date column again and select the “Group” option, then select “Days” option and change the “Number of Days” option to 7 (or any other time period you so choose):

Image

Step 6.  The Pivot table now displays data broken out by week.

Image

Step 7 and the Goal Line.  From this point it’s a matter of graphing the table and prettying it up.  If there’s a goal that needs to be hit, annotate it and make it very clear what that line is.  Much of what we do relates to some goal line.  Charting isn’t just about showing growth trends; it’s also a good opportunity to clearly state where you’re winning and losing:

Image

Advertisements