Create Dynamic Charts with Combo Box in Excel

*Disclaimer: For this video I have used data from the Google Merchandise Store Google Analytics which is publicly available.

Combo Boxes in Excel create drop down menu options and dynamically populate tables and charts.

In this video I will show how to create a dynamic mini dashboard in Excel with the Combo Box. The drop down menu will give us the option to select All Devices, Desktop, Mobile and Tablet and the table and charts will dynamically change to reflect the number of sessions and the revenue made for each device or all devices. The date range is January 2017 to June 2017 and the data is broken down by month.

The most important part of creating a combo box is to properly configure the input range and cell link (demonstrated in the video). The other important part if to understand the Index function in Excel. For the combo box the Index function contains an array, which includes the column or certain rows in a column, and a row_num which would be the cell we have selected as a Cell Link. All of these are demonstrated in the video.

Styling and designing is just to give ideas and show you options but you can style it in any other way that suits your reporting.

How to Create a Heatmap in Excel

*Disclaimer: For this excel heatmap video I have used data from the Google Merchandise Store Google Analytics which is publicly available.

In this video I show how you can create a heatmap chart in Excel. The excel heatmap will highlight the most and least popular times of day and days of week when users make purchases on the online store. You can use it in any way to suit your data and your reporting needs. You can use it to look at marketing channel and location or conversion and browser technology, just to name a few ideas.

Continue reading “How to Create a Heatmap in Excel”

Build an H1 (DOM Element) Variable in Google Tag Manager

There are many examples where having this type of data, the text of the H1, available will make auto events and pageview tracking easier and cleaner to report. By implementing an H1 variable I was able to simplify reporting and present a much cleaner report using the H1 test on a page instead of filtering through a large number of URLs.

H1 Variable in Google Tag Manager

Building a variable with the H1 test is actually pretty easy to do. You need to create a DOM Element variable with the following settings:
Variable name: H1
Selection method: CSS Selector
Element selector: H1

H1 Variable in Google Tag Manager

Ideally only one H1 should be present on a page. If you have multiple H1s Google Tag Manager will pick up the H1 header that appears first in the code of the page.

How to Use H1 Variable for Auto Events and Reporting

Here is how I set up an event for a client to report on the most popular service page using the H1 on the page. There are a number of ways to report on the most popular product or solution or blog post but except you are using the enhanced eCommerce that will report using the actual product name, you will use the page URL in your reports and not the name, H1, of the page.

After building the variable, build a new trigger with the following settings:
Trigger name: {Page Name} Pageview
Trigger type: Pageview
Fire on: Page URL contains {URL identifier}

H1 Variable in Google Tag Manager

Then build your auto event tag with the following settings:
Tag name: Most popular product
Tag type: Universal Analytics
Track type: Event
Event category: Most Popular Product
Event action: {{H1}}

H1 Variable in Google Tag Manager

Then select the trigger and you are all set!

You should be able to see your report under Events. Drilling down to the event action will report on the page H1 and the number of pageviews it has received for a selected period of time.

Excel Dashboard – Website Traffic by Channel

Why It Is Important to Report on Website Traffic by Channel?

If you are investing in different marketing channels to drive traffic to your website it is important to understand how each channel is performing against the other channels. Performance can be measured by many different metrics – the number of sessions sent to the website, the number of micro or macro conversions or the number of qualified leads for a certain period of time and certain budget.
This dashboard is a basic excel dashboard that will compare the number of sessions that a website received by each marketing channel every month for an entire calendar year. You can use this template and build a dashboard for each metric that represent your KPIs, overall sessions, goal completions, eCommerce, leads, etc.

Interactive Excel Dashboard

Website Traffic by Channel Excel Dashboard

This dashboard is built with

  • A pivot table
  • A pivot chart
  • Slicers
  • Custom styling and formatting

To start you will need a table with all your raw data.

Website Traffic By Channel Raw Data

The raw data table contains 12 rows for each marketing channel with traffic data for every month of the calendar year. I have used random numbers to build this dashboard as an example.
You can customize it and use colors and variations that suit your reporting.

Excel Bubble Chart to Visualize and Measure Website Conversions

How to Use Excel Bubble Charts

Excel bubble charts are my favorite way to present data in Excel, yet I rarely see them in use in reports or dashboards. A typical scenario to choose a bubble chart is in a set of data with three values for a category. With bubble charts you have the ability to add another dimension of data in the size of the bubble. Another thing to have in mind when choosing a bubble chart is that these graphs use only values, both axes represent a value, unlike other charts which use the category for one of the axes. Therefore in a bubble chart the category can be added as a data label.

Visualize Website Conversions with an Excel Bubble Chart

Excel Bubble Chart

In this video I am using an eCommerce example to show you how you can represent your conversion data in an Excel bubble chart. In this example scenario I have used random numbers for several eCommerce categories on a beauty website that sells makeup and skincare. The table shows the number of sessions, the number of sales and actual revenue for each category of products. *Note, to have this type of data you’d need Google Analytics content grouping set up, it will make it possible to break all these metrics by all of the categories on the website. Here are some instructions on how to set up content groupings.* The bubble chart will present the number of sales on the vertical axis, the number of sessions on the horizontal axis, the size of the bubble is the actual revenue and the color represents the category. I have also used data labels to mark the revenue and the name of the category.

Steps to Create an Excel Bubble Chart

  • Start by selecting the three right columns (since bubble charts do not use categories for the axes, you only need to select the values in your table)
  • Then insert a bubble chart
  • Change the chart name to reflect the data you are reporting
  • To make the graph look cleaner delete the gridlines
  • Then edit the axes to start from 0
  • Then add data labels to include the actual revenue number and the category name
  • To add data label for the revenue data choose data label to be the size of the bubble
  • Then to add the category name select value from cells and select the column with the category data
  • Aligh the data labels above the bubble
  • Click on bubble and select “Vary by color” from the “Fill” options to add a different color for each category
  • Then add axes titles and align horizontally
  • Rename the axes to reflect the name of the metric (sales and sessions)
  • Finally adjust chart position to fit it within your dashboard

How to Analyze the Data

Ideally you’d want all bubbles to be as big as possible and at the most far top right corner of your chart. When looking at the data find opportunities in categories where you receive a lot of traffic, high number of sessions, but not that many conversions. Or there might be a category that is reporting good revenue and low number of sessions. This section of your website might have a big potential to increase traffic and further increase your revenue.

Have fun creating your excel bubble chart. You might find it useful for other sets of data you are reporting on.