VBA Web Analytics Dashboard

Executive Summary

Recently, I worked on an internet marketing consulting project for a veterinary hospital. The animal clinic’s goal for its website was to generate leads via a phone call. Consulting efforts included setting up accounts in Google Voice, Google Analytics, and Google AdWords. In order to quickly allow the veterinary hospital see the business contribution of the website, I created a VBA web analytics dashboard.

The dashboard aims to show at a glance three key metrics to making internet marketing decisions: traffic, conversion rate, and average order value. To do this the VBA dashboard requires that the user first completes the following information: Google account email, Google login password, and a date range. After this the user clicks the “View Dashboard” form button to initialize data collection and dashboard creation.

· To pull total traffic data, the VBA dashboard navigates to Google Analytics, logs-in, sets the appropriate date range, then pulls visits data and copies it to a new worksheet in the Excel file. This information then displays in the dashboard.

· To pull traffic data and costs relating to paid search, the VBA dashboard navigates to Google AdWords, logs-in, sets the appropriate date range, then pulls click data and cost data, which are copied to the Excel file. This information then displays in the dashboard.

· To pull conversion data, the VBA dashboard navigates to Google Voice, logs-in, prints to a new worksheet all the phone calls and corresponding dates that fall within the appropriate date range. The amount of phone calls, or conversions, then displays in the dashboard.

· Conversion rate is calculated using the conversion and traffic data.

· In order to determine average order value, the VBA dashboard allows a user to manually enter data from the hospital’s records regarding services purchased by individuals who called the Google Voice # listed on the website; once this data is entered, total website revenue and average order value are automatically calculated and appear in the dashboard.

Once a dashboard has been generated, a "Save Report" form button is present. When clicked, this button saves a copy of the dashboard report with the appropriate date range included in the file name.









Project write-up: VBA Web Analytics Dashboard (pdf)

VBA Web Analytics Dashboard (xlsm)