Visualise Google Analytics Revenue by Month and Day with Excel

. Posted in: Data Analysis
Tags: Data Visualisation, Excel, Google Analytics

I am constantly surprised annoyed about how short sighted many sales and marketing people are thinking about ecommerce and online performance. In many organisations, it’s not that big a surprise. People in sales and marketing are very often measured by short term goals. So they are naturally using their weekly or monthly sales reports.

But once in a while, it can be a real eye-opener to take a step back and look at the bigger picture. One way to do that is simply to take a look at your daily revenue over a whole year. It sounds almost stupid, but you can actually learn a lot about:

  • Seasonality. When – over a year – does your webshop perform best?
  • Campaign evaluation. Did your campaigns work – and for how long?
  • Campaign planning. When and what campaigns should you plan next?
  • Missed opportunities. Are there seasons or times that you should capitalise on?

Want to entirely skip the guide? Get the final Excel report file here and the Google Analytics custom report template here.

But presenting or analysing line charts and number tables in Google Analytics is often time consuming. And it can also be difficult to read and find patterns in an easy and fast way.

So in this post, I’ll show you how to easily pull data from Google Analytics into Excel and visualise it in a way so that you can identify those seasons and those big and small days and weeks. The final report will look something like this:

Visualise ecommerce revenue over time with an Excel heatmap

Steps

  1. Prepare a Google Analytics custom report for export
  2. Process the data
  3. Visualise the data

Prepare a Google Analytics custom report for export

First off, we need to set up a Custom Report so we can easily export the data we need to Excel. (If you’re pressed for time and just want the revenue report, click here to install it now).

  1. After you log into Google Analytics, go to the Customization tab and click + New Custom Report
  2. Name the report; e.g. ‘Revenue by day’
  3. Switch the report type from ‘Explorer’ to ‘Flat Table’
  4. Under Dimensions, first select ‘Month of the year’ and then ‘Day of the month’
  5. Under Metrics, select Revenue
  6. In the Dimension Drilldown option, select ‘Date’
  7. Click Save

In this case, we would like to like to visualise revenue for a whole year.

So set the date interval to the past 12 whole months (e.g. January 1st – December 31st). Let the report refresh, and make sure to set the ‘Show rows’ option to 500 to display all dates.

Then open the Export dropdown and select the Excel (xlsx) option – this will begin the export of an Excel file.

Process the data

Once the file is downloaded and opened up in Excel, we need to process the data. First off, we need to add columns for each row, that indicate the month and day of the month, since we need to look up those values when presenting our revenue numbers in a calendar format. Although, the exported file already already has that information, we still need to modify it since Analytics exports those values in text format and we need the information in a number format.

The downloaded file will have two work sheets; a Summary and a Dataset1.

The Dataset1 sheet has three columns: Month of the year, Day of the month and Revenue (i.e. the dimensions and metrics that we selected for the custom report). So go ahead and put in a title in cell D1 (call it Month) and another title in E1 (call it Day).

In cell D2 we want to display the number of the month for that row (i.e. 1 = January, 2 = February etc.). So put in this formula, which basically just converts text representation of the month to a number representation):

=VALUE(TRIM(CLEAN(A2)))

And in cell D2 we want to display the number of the day, using this formula:

=VALUE(TRIM(CLEAN(B2)))

Now, select cells C2 and D2 and drag them down to fill out the entire columns D and E (well at least until row 366).

Visualise the data

Create a new sheet and call it Visualisation. This is where the calendar should be built – which is done manually.

Start by entering ‘1’ in cell A2, and ‘2’ in cell A3 – then select both cells and drag down your selection to cell A32. The value of that last cell (A32) should now be ’31’.

Then enter ‘1’ in cell B1 and ‘2’ in cell C1. Select both cells and drag the selection right until column M, which should now have the value 12.

See what we did there? We just created a calendar.

Now select cell B2, and put in this formula:

=SUMIFS(Dataset2!$C:$C,Dataset2!$D:$D,B$1,Dataset2!$E:$E,$A2)

Select cell B2 and drag it down to cell B32 – then keep the selection (B2:B32) and drag it right until column M. The formula has now been pasted into all cells in the calendar.

The calendar just needs a bit of cleaning up – e.g. the formula is present in cells C30:C32, which represent february dates that don’t exist. So you should now manually delete the formula from cells that represent non-existing dates altogether.

When done, simply select all cells with the formula (which would be B2:M32). Then make sure you are viewing the Home ribbon in Excel, click the Conditional Formatting button, point at the Color Scales menu item and select your preferred color scale (I like the first one best where low numbers are a dark red, and high numbers are a dark green).

Now you’re done, and you can apply any other formatting that you’d like.

Advanced Next Steps

  • Automate data updates with an Excel plugin (check out Analytics Edge)
  • Set up similar (and filtered?) reports using sessions, conversion rate or other metrics
  • Set up reports using day and/or hour as a dimension instead of month and day