Google sheets presents an opportunity for Google Analytics users to pull data from their accounts and create highly customizable charts, graphs, and projections in a single page dashboard. What’s more, these reports can be automated using a handy add-on for Google sheets, which will save e-commerce marketers the chore of having to manually retrieve data from Google Analytics on a regular basis.
In this post, I will outline how to use Google sheets to set up an e-commerce-specific analytics dashboard for your business, how to customize your dashboard to include relevant charts and projections, and how to automatically update the data being reported on a daily, weekly, or monthly basis.
Step 1: Download the Google Analytics Add-on
To get started, you’ll need to open Google sheets and navigate to the Add-ons menu, and click Get add-ons:
Please note that to generate reports referencing Google Analytics data in Google sheets, you must ensure you have access to the Analytics property in question via the Google account you’re using on Google sheets.
Step 2: Define Metrics and Dimensions
After downloading the add-on, you will be able to create a report by visiting Add-ons > Google Analytics > Create new report:
Within the ‘create new report’ window, you will be able to specify the account, property, and view you’d like to report on and choose which metrics and dimensions to base the report on.
At this stage, there’s no need to spend a huge chunk of time customizing your metrics and dimensions. Once you’ve clicked ‘Create Report’ a new sheet will be created (Report Configuration) where you’ll be able to set up and fine tune each report you’d like to create in far more detail.
A very basic report (sessions split by channel) purely for the purpose of generating a blank report configuration sheet.To configure an e-commerce specific report we would need to visit the report configuration sheet. I have provided an example of the report configuration sheet below, which has been used to set up a range of e-commerce specific reports (separated by columns):
There are six mandatory fields you will need to complete in the report configuration sheet:
- Report name – the name of each report you wish to generate
- Type – set to (core) by default
- View (profile) – the Google Analytics profile ID
- Start / end date – can be set manually or using date formulas
- Metrics – see reference guide for details
- Dimensions – see reference guide for details
There are more options available to customize your reports by sorting data, using segments and filters, specifying sampling levels, and limiting the number of results returned within each report.
Step 3: Create a Set of E-Commerce Specific Reports
There is a huge range of potential reports you can create using this add-on, but for this post we will be focusing specifically on the creation of an e-commerce dashboard.
If you have implemented Google Analytics e-commerce tracking, an e-commerce report can easily be generated based on your Google Analytics data to display the following information:
- Revenue, order quantity, average order value, and average items per order based on data from the last full month vs. YOY and the YTD vs. YOY:
- Number of orders (split by city) based on data from the last full month and the YTD:
- Top products (ordered by revenue) for the last full month:
- Projected revenue vs. YOY and target:
To retrieve the information to build these reports, you need to edit your report configuration sheet to reflect the settings shown here:
Example report configuration (open image in separate tab to view detail)Setting Automated Date Ranges
In the screen grab shown above, I have displayed the metrics, dimensions, and sorting options needed to build each of the reports that make up the e-commerce dashboard. Because you will be automating the generation of these reports, it is important that you set up the start and end dates of each report to update automatically. We will be utilizing DATE formulas in the report configuration sheet to do this.
In the examples displayed in columns O, P, Q, S, and U, we are looking to create reports that include data for the last complete month vs. the same month a year previous. So in the report configuration sheet we need to ensure that the end date automatically updates to the last day of the last calendar month, and that the start date is set 13 months prior to the end date column.
- In the End date column, we would need to insert the following formula: =if(today()=eomonth(today(),0),today(),eomonth(today(),-1))
- In the Start date column, we can use the EDATE function, to return the date 13 months prior to the end date (O6): EDATE(O6, -13)
To compare this to the previous year (as displayed in columns R & V on the example configuration sheet), you would need to reference the formulas listed above, minus a year:
- For the end date choose a cell referencing the last day of the most recent month (O6, for example) and use the following formula: EDATE(O6, -12)
- For the start date we can again use the EDATE function, to return the date 13 months prior to the end date as listed in the example above.
Once you’ve amended your start and end dates and configured the reports as per the examples shown in the screen grab above, navigate to the Google Analytics add-on and click ‘run reports.’
Once generated, each report will be displayed on a new sheet without any formatting. The next step is to create a ‘dashboard’ tab to reference the data on each report so it’s easily accessible and digestible via a single sheet.
Step 4: Customize Your E-commerce Dashboard
E-commerce KPIs
To create these tables, create a heading describing each of the reports and add relevant column headings as displayed above. Then you need to reference the cells displaying the relevant data in the individual report sheets you have generated. To add the comparisons, add the formula below, format the cells as percentages, and add conditional formatting rules:
- For YOY comparisons: =(CURRENT MONTH-YOY)/YOY
- For YTD comparisons: =(YTD-YOY)/YOY
YTD Revenue and Projections
To create the revenue projection graph vs. target and YOY data, you can utilize the FORECAST function to project data for the year ahead based on previous performance. To create a chart to represent this data, you will need to ensure that your forecast column sits beside your YTD and YOY data in a separate sheet before selecting all three columns and inserting a chart via the insert menu:
Orders Split by City
To display split of orders by city for both the current month and YOY, select the cities and the sessions columns in the individual report sheets and insert a pie chart based on the selection:
Top Performing Products
To display the top performing products as a chart on your dashboard, you’ll need to navigate to the top products individual report sheet and ensure the product names and total product revenue columns are selected before creating a bar chart based on the selection:
Step 5: Automate and Share Your Report
Now that you’ve created your reports, you need to ensure that your reports update automatically. To do this, navigate to the Google Analytics add-on menu, click schedule reports and enable the reports to run at relevant intervals to suit your requirements:
To share the report, click on the sharing settings in the top right of your report and enter the email addresses of anyone you’d like to be able to view the data you’re reporting:
Conclusion
Once you have set up your e-commerce dashboard, you can start to create additional sections to your dashboard to display SEO, PPC, social media, and email marketing metrics from your Google Analytics account. You can also use Google sheets to pull in link metrics via APIs provided by Ahrefs, organic visibility data from Google search console, and email performance data using APIs from providers such as Mailchimp.
It can take some time to get to grips with the Google Analytics add-on, the associated metrics and dimension references, and the formulas required to automate your report settings in Google sheets. Investing time in the creation and customization of your reporting dashboard will certainly be worthwhile in the long-term.
Have you already used the Google Analytics add-on for Google sheets? If so, please comment below to share your thoughts and advice.
Image Credits
Featured Image: WD net / Pexels
Screenshots by Ben Wood. Taken November 2016
