How to Create Web Analytics Dashboards in Excel?

Web analytics refers to gathering, examining, and reporting web data. It gives you information on how well your website performs.

For a better understanding of the value of web analytics, let’s look at a case study.

Increased internet traffic and conversions were priorities for the marketing department of a certain company. Despite their best efforts, they were unable to succeed.

They discovered the website needed to be user-experience optimized after doing some research.

They had to measure and analyze the website’s performance to understand user behavior and make the required adjustments.

They, opted to use web analytics to study the performance of their website. Thus, they developed a web analytics dashboard to track the top website metrics. This enabled them to tweak their website for improved user experience and increased conversions.

Table of Content:

1. What is Web Analytics?
2. Top Website Metrics to Include in an Analytics Report
3. Why do You Need a Web Analytics Dashboard?
4. Different Examples of Web Analytics
5. How to Create a Web Analytics Dashboard in Excel?
6. Tips for Creating an Effective Web Analytics Dashboard
7. Wrap Up

Keep reading to learn about web analytics and how to create a web analytics dashboard in Excel.

What is Web Analytics?

Web analytics is the process of measuring and evaluating website performance-related data. With web analytics, you can evaluate and enhance the user experience and optimize website performance.

Utilizing web analytics tools can assist you in monitoring, evaluating, and scrutinizing the effectiveness of your website. You can recognize user trends, segment your audience and track the performance of your website.

Top Website Metrics to Include in an Analytics Report

When it comes to web analytics, there are several metrics you can track. You can use these metrics to measure website performance and user behavior.

The top website metrics to include in an analytics report include:

• Number of visits
• Page views
• Time spent on site
• Bounce rate
• Conversion rate
• Referral sources
• Popular pages
• Popular products
• Average order value

Why do You Need a Web Analytics Dashboard?

A web analytics dashboard visualizes website performance indicators. This enables you to find chances for improvement and growth.

You can monitor website performance and identify trends with the help of a web analytics dashboard. This aids in optimizing it for increased conversions and improved user experience.

Different Examples of Web Analytics

Example 1

Let’s say you have website traffic data and wish to determine the overall traffic. You also want to determine which social media sites generate the highest traffic. Thus, you can focus on the sites that are not doing well.

The data is displayed below.

From	Sources	Web Pages	Traffic
Facebook	Product Page	Checkout Page	10831
Facebook	Product Detail Page	Contact us	22121
Facebook	Product Detail Page	Checkout Page	26751
Facebook	Home Page	Product Page	28240
Facebook	Home Page	Contact us	24506
Direct	Home Page	Product Detail Page	28166
Direct	Product Page	Product Detail Page	28074
Direct	Contact us		10779
Direct	Product Page	Checkout Page	11449
Direct	Product Detail Page	Contact us	19316
Instagram	Product Detail Page	Checkout Page	27440
Instagram	Home Page	Product Page	16351
Instagram	Home Page	Contact us	28424
Instagram	Home Page	Product Detail Page	24986
Instagram	Product Page	Product Detail Page	10182
Twitter	Contact us		18607
Twitter	Product Page	Checkout Page	18375
Twitter	Product Detail Page	Contact us	10815
Twitter	Product Detail Page	Checkout Page	22742
Twitter	Home Page	Product Page	28432
Google Ads	Home Page	Contact us	17722
Google Ads	Home Page	Product Detail Page	11160
Google Ads	Product Page	Product Detail Page	11722
Google Ads	Contact us		19854
Google Ads	Product Page	Checkout Page	21936
SERP	Product Detail Page	Contact us	23250
SERP	Product Detail Page	Checkout Page	26512
SERP	Home Page	Product Page	10236
SERP	Home Page	Contact us	22565
SERP	Home Page	Product Detail Page	17076
SERP	Product Page	Product Detail Page	24402
SERP	Contact us		27724

Here is the final visualization.

Example 2

Here, we will use Geo, Sources, and Web Pages data.

Geo	Sources	Web Pages	Traffic
Boston	Product Page	Checkout Page	10831
Boston	Product Detail Page	Contact us	22121
Boston	Product Detail Page	Checkout Page	26751
Boston	Home Page	Product Page	28240
Boston	Home Page	Contact us	24506
Boston	Home Page	Product Detail Page	28166
Boston	Product Page	Product Detail Page	28074
Boston	Contact us		10779
Austin	Product Page	Checkout Page	11449
Austin	Product Detail Page	Contact us	19316
Austin	Product Detail Page	Checkout Page	27440
Austin	Home Page	Product Page	16351
Austin	Home Page	Contact us	28424
Austin	Home Page	Product Detail Page	24986
Austin	Product Page	Product Detail Page	10182
Austin	Contact us		18607
Phoenix	Product Page	Checkout Page	18375
Phoenix	Product Detail Page	Contact us	10815
Phoenix	Product Detail Page	Checkout Page	22742
Phoenix	Home Page	Product Page	28432
Phoenix	Home Page	Contact us	17722
Phoenix	Home Page	Product Detail Page	11160
Phoenix	Product Page	Product Detail Page	11722
Phoenix	Contact us		19854
Dallas	Product Page	Checkout Page	21936
Dallas	Product Detail Page	Contact us	23250
Dallas	Product Detail Page	Checkout Page	26512
Dallas	Home Page	Product Page	10236
Dallas	Home Page	Contact us	22565
Dallas	Home Page	Product Detail Page	17076
Dallas	Product Page	Product Detail Page	24402
Dallas	Contact us		27724

Here is the final visualization.

Example 3

This is data of traffic from an e-commerce store.

Starting Point	Platforms	View product & details	Read Reviews	Add to Cart	Cart Status	Ending Point	Traffic
External Ad	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	29246
External Ad	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	6133
External Ad	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	12954
External Ad	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	22337
External Ad	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	14685
External Ad	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	10210
External Link	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	29605
External Link	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	27600
External Link	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	5637
External Link	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	23336
External Link	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	28281
External Link	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	17939
Direct Search	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	5490
Direct Search	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	19138
Direct Search	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	27311
Direct Search	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	19560
Direct Search	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	24788
Direct Search	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	10914
Organic Search	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	29043
Organic Search	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	12028
Organic Search	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	24685
Organic Search	Mobile App	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	8508
Organic Search	Mobile App	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	12448
Organic Search	Mobile App	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	5034
External Ad	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	23552
External Ad	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	19632
External Ad	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	18914
External Ad	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	19234
External Ad	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	26871
External Ad	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	29736
External Link	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	14141
External Link	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	10680
External Link	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	23756
External Link	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	18491
External Link	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	15740
External Link	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	5976
Direct Search	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	19080
Direct Search	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	22204
Direct Search	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	22736
Direct Search	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	25578
Direct Search	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	11141
Direct Search	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	6881
Organic Search	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	18022
Organic Search	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	21183
Organic Search	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	15307
Organic Search	Official Website	View product & details	Read Reviews	Add to Cart	Clear Cart	Exit	11557
Organic Search	Official Website	View product & details	Read Reviews	Add to Cart	Checkout	Purchase	26480
Organic Search	Official Website	View product & details	Read Reviews	Add to Cart	Click on another product	Purchase	29140

This is the final visualization.

How to Create a Web Analytics Dashboard in Excel?

Creating a web analytics dashboard in Excel is vital for an in-depth evaluation of your website’s performance. You can learn crucial information about the performance of your website and pinpoint areas to improve.

You can create appealing analytics visuals with ChartExpo. Visuals that make it easy for website performance evaluation.

How to Install ChartExpo in Excel?

1. Open your Excel application.
2. Open the worksheet and click on the “Insert” menu.
3. You’ll see the “My Apps”.
4. In office Add-ins window, click on “Store” and search for ChartExpo on my Apps Store.
5. Click on “Add” button to install ChartExpo in your Excel.

ChartExpo charts and graphs are available both in Google Sheets and Microsoft Excel. Please use the following CTA’s to install the tool of your choice and create beautiful visualizations in a few clicks in your favorite tool.

Example

Let’s use example 3 to demonstrate how to create a web analytics dashboard on data of traffic from an e-commerce store in Excel.

• To get started with ChartExpo, install ChartExpo in Excel.
• Now Click on My Apps from the INSERT menu.

• Choose ChartExpo from My Apps, then click Insert.

• Once ChartExpo is loaded. Click on “Sankey Chart” from the list of charts.

• Click “Create Chart From Selection” button after selecting the data from the sheet, as shown.

• The Sankey Chart will look like as follows.

• If you want to have the title of chart, click on Edit Chart, as shown in the above image.
• To change the title of the chart, click on the pencil icon that is available very next to Chart Header.
• It will open the properties dialog. Under the Text section, you can add a heading in Line 1 and enable the Show option. Give the appropriate title of your chart and click on Apply button.

• To change the node color, click on the pencil icon available with the node. Expand the Node properties and change color from the Node Color property. Click on the Apply button.
• For saving changes click on Save Changes. This will persist the changes.

• The final chart will look as below.

Insights

• On the first level, you can observe the sources of traffic. Clearly, the majority of the traffic, which is 233.5K and makes up 26.45%, came from external ads.
• The website generated 456K, 52% of the total traffic. This makes it the largest traffic compared to the mobile app’s 427K.
• All the traffic resulted in product page views and reviews. Almost all of them added products to their carts.
• 7K users, 38%, cleared their cart and exited. The remaining 34% proceeded to the checkout and completed their purchase, while 30% clicked on another product. Thus, the final user purchase rate is 64%.

Tips for Creating an Effective Web Analytics Dashboard

Here are some tips for making a web analytics dashboard that works well:

• Keep it simple, easy to use, and understandable.
• Concentrate on the most crucial metrics.
• To visualize the data, use graphics like graphs, charts, and tables.
• Include filters to compare various time frames and segmentations.
• Monitor website performance over some time to spot trends and user patterns.

FAQs:

What is a web analytics dashboard?

A web analytics dashboard is a visual tool for data analysis on a website. It offers the user a thorough assessment of the website’s performance. Here, you can monitor key metrics such as website visitors, page views, bounce rate, and conversion rate.

What should be in a web analytics dashboard?

A web analytics dashboard should include several types of data, such as;

• Overall website performance metrics
• Real-time data such as website visitors, leads, and sales activity.
• Social media analytics such as followers and engagement.
• Search engine optimization (SEO) analytics.

What metrics should I track in a web analytics dashboard?

The top website metrics to track in an analytics dashboard include;

• Number of visits.
• Page views.
• Time spent on site.
• Bounce rate.
• Conversion rate.
• Referral sources.
• Popular pages.
• Popular products.
• Average order value.

What is the purpose of a web analytics dashboard?

A web analytics dashboard enables website owners to get insightful knowledge about the performance of their websites. You can use this information to spot patterns, areas for improvement and make decisions on website optimization.

Wrap Up

Everyone worries about the health of their business in today’s fast-paced economy. However, a basic web analytics dashboard can provide critical insights into your business. Thus, guide you toward the changes you need to succeed.

A web analytics dashboard can help you improve your business’s performance and boost revenue.

When designing and creating a web analytics dashboard, you can use Excel and make it interactive. This will bring in more holistic views of the performance of your website.

Using ChartExpo, you can create a web analytics dashboard in Excel in minutes. You can choose from various visualizations, such as charts and graphs.

These visualizations can help you make sense of your data and spot trends and patterns. Then you can make changes to your website and improve its performance.