Web Analytics Project

In my last blog I wrote about how I finished my Web Analytics project for BYU Independent Study. I have decided to place the User Guide on here:

Google Analytics Analysis User Guide

The conditions are based on the average weekly data. So, you can easily change the conditions to suit monitoring needs.

Getting Data and Importing Data
1. To add more data to the Excel Spreadsheet you must click weekly data.

2. Next select the date range

3. Finally Export the data in CSV format.

4. At this point I recommend that you download your CSV files to a specific folder. That way you can reference the hard copy data later.

5. Next, open the Excel Analysis Spreadsheet. Open ONLY the file specific to the data you want to upload. Then, click on the Data Menu button.

6. Next, a small window will appear and you may now select to import data. The reason I have this menu, is perhaps at a later date, someone may modify this menu to graph the data.

7. Once clicked you need to find the CSV file you want to upload. Click on that file and it will upload all the data.

8. After the data is uploaded the program will automatically get the average of the data and use it as a base for the “Medium” level data. The “Low” is one standard deviation from the average and the “High” is one standard deviation above the average. The rest of the data is highlighted in a conditional formatting according to the “Low, Medium, and High” conditions.

Adding Metrics
If you want to add or delete metrics you need to be aware of a few things.
1. There can be no blank lines between classes or metrics. See below:

2. Your course names and metrics must match the names and metrics of Google Analytics’ CSV file. See below:

Project Outcomes

It's nearing the end of the semester and I was finishing up some of my projects. One project was from my Web Analytics class. Dr. Liddle gave us quite a bit of freedom in choosing a project suitable for the class. I decided to talk to the independent study and figure out a project that could benefit them.

Turns out, BYU Independent Study uses Google Analytics. While it is set up rather nice, they don't have anyone hired to help them interpret the data they have. Because of that, they don't know who to market to, how to increase traffic, or what trends are occuring. With this information, I decided to think of a project that I could do to help them interpret all the data they currently have.

After a few days of planning, I finally thought of something they could use. I decided to create a spreadsheet that would take the CSV formatted data from Google and compare weekly data with the overall average of the data. For example, if the metric they want to view is of Unique Visitors they can see a weekly basis of how many unique visitors they are indeed getting for each week.

Once I had this idea, the coding frenzy began. I spent about a good three days of coding and testing until I finally got it to work. Since the data from Google can be a CSV file I decided to use Excel and VBA to create a spreadsheet that would automatically upload the data to the sheet. I used conditional formatting to display the weekly trends. In the end, I had a pretty good program. The only other interesting thing was this program could potentially be used for other sites other than BYU Independent Study. One improvement that I could implement (if I had more time) would be to autogenerate graphs. For now, I am proud of the project I have made and I think it will be very useful to them.

http://ce.byu.edu/is/site/courses/index.cfm