Sunday, July 1, 2007

Internship

My blog has turned into a journal of technical accomplishments. Some of my posts probably don't apply to many people. But I have written them because they were things that I wished people had blogged about so that I wouldn't have to spend so much time figuring it out. So, ultimately, I hope they can help someone wanting to do similar things.

As the title of this post indicates, I have started my internship with a Big 4 Accounting Firm. In fact, I have survived 2 weeks of training and firm orientation. This is my first experience with a truly global company/firm. The resources that this firm has is incredible. There were over 120 interns in our intern training group representing the pacific southwest area. We stayed in a very nice hotel in downtown Los Angeles and had catered breakfast and lunch everyday. We even had some nice dinners and room service. They gave each intern a brand new laptop and corporate charge card. They also spent 2 weeks training us on their audit methodology and the importance of the work that they and the other firms perform. We heard from many partners, managers, and staff throughout the 2 weeks. What I'm trying to say is that they spend a ton of resources (money) on interns. It was quite impressive.

Money isn't everything of course. I'm more excited to see how the 'work' will be. I'm anxious to see what I'll be doing and how I can contribute to the team and to the firm. My goal of doing this internship is to see if this is where I would like to launch my career.

Tomorrow I begin the first day of work and I'm excited to see how it will go. There are many things that I like about working for this firm and there are things that I'm skeptical of. But as with anything, it takes careful thought, experience, and advise if I decide (and if they give me a full time offer) to work for them after graduation. Ultimately, I'm excited to learn more and to get some more experience.

Friday, April 27, 2007

How to Create Dynamic Annotations with Dundas Charts

Let me just tell you that I love my job. I work at The Generations Network in the Business Intelligence Team. We manage the data warehouse for the company and I develop reports in ProClarity Analytics and in SQL Server Reporting Services (SSRS). We recently purchased Dundas Charts which adds some nice functionality to SSRS charting. One of the features is the ability to add annotations. There are some days when we experience a huge spike in data because of processing issues. Before, people would ask us "what happened on the 1st? Did we have a great day or what???" Now we can add annotations that explain dips or spikes.

Creating the annotations manually is pretty simple. But I wanted to create them dynamically. We have or will have hundreds of reports and charts that look at this data. Manually creating annotations for each report or chart would be ridiculous. By creating them dynamically, every chart we create can include these annotations. So how did I do it? Well it took a lot of help from Dundas Support who was excellent. I don't know VB.net very well but I knew how it could work. They really stepped up and made it easy. I would highly recommend Dundas Charts! Anyways, here is the solution:

1. I first created a normal SQL table with 2 fields... Annotation_Name and Annotation_Text. In order for this to work you need to name the annotation like a date (01/01/2007). The code will then anchor the annotation to the data point based on the name of the annotation. In the text column, you can add the text of the annotation.

2. After the table is created I needed a way to query this table, loop through the results, and create a new annotation for each record in the results set. This is where I had to do a lot of research, get a lot of help from Dundas Support, and some help from one of my professors. It is really simple if you know VB.net. Here is how you do it:

- You will need System.Data.dll. Find the assembly in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

- Copy the assembly System.Data.dll to the following folder: C:\program files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. When the report is deployed, you will need to copy this to the ReportServer\bin folder on the server. By default, this is located at C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\bin (where "x" is some number.)

- In the Chart's code editor > External Assemblies tab, add a new external assembly reference to System.data.dll

- In the source code paste in the code below. Modify the connection string and query string.

' Define Variables
Dim connectionString As String = "Data Source=localhost;Integrated Security=SSPI; Initial Catalog=DB"
Dim queryString As String = "Select Annotation_Name, Annotation_Text From Rpt_Annotations"
Dim connection As New System.Data.SqlClient.SqlConnection(connectionString)
' Open connection
connection.Open()
Dim command As New System.Data.SqlClient.SqlCommand(queryString, connection)
Dim reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader()
Dim newAnnotation As CalloutAnnotation

' Add annotations from the query results
While(reader.Read())newAnnotation = New CalloutAnnotation

' Assign the annotation name and text
newAnnotation.Name = reader.GetString(0)
newAnnotation.Text = reader.GetString(1)

newAnnotation.SmartLabels.Enabled = True
chartObj.Annotations.Add(newAnnotation)

End While


' Match up each annotation to its data point

For Each annotation As Annotation In chartObj.Annotations

Dim point As DataPoint = ChartObj.Series(0).Points.FindValue((DateTime.Parse(annotation.Name)).ToOADate(), "X")

If Not point Is Nothing Then
annotation.AnchorDataPoint = point

End If
Next



- This can be tricky since it is all in the code editor. If you have problems, go to the Chart Designer > Advanced tab and set the Chart's SuppressExceptions property to false to see what errors might be occurring.


And that's it. It works wonderfully and now I can insert a new record in the annotations table and have it show up on many reports and charts! Beautiful.

Thursday, April 5, 2007

More Sample Headers for ChoiceSkills.com

Here are some more sample headers with some different color schemes. Tell me which one you like. Leave some comments.




Monday, April 2, 2007

ChoiceSkills.com Header Mockup

As part of my web analytics course we are re-designing the website www.ChoiceSkills.com and making improvements aimed at increasing traffic, increasing conversion, and increasing revenue.

Here is a mockup header that could be implemented on the new site:

Friday, March 30, 2007

How to create a Report Execution Snapshot with SSIS

This last week I have been searching the internet for a solution on how to create a Reporting Services Report Execution Snapshot with SQL Server Integration Services. SQL Server Reporting Services mainly runs on XML and SOAP methods. So calling a RS function or method is relatively simple. SSIS even has a web service task that theoretically would work perfect for this situation. I tried referencing RS .wsdl file and SSIS wouldn't accept the format. After some more research I realized the web service task is pretty much useless when connecting to Reporting Services.

Then I looked at the scripting task in SSIS and that seemed too complicated. To get it to work I would have to create a RS proxy server and write a good amount of code. It seemed too difficult for what seemed to be an easy task.

I then found the rs utility. You can write reporting services script files (.rss) using VB.net to call Reporting Services web service methods and run these files from the command line. By putting the command line command in a batch file I am able to create an Execute Process task in SSIS and call the batch file. It was that easy.

So here is my solution:

reportsnapshot.rss
Public Sub Main()
'Run this in the command line: rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

Dim reportPath as string = reportPathParameter
Dim schedule AS NoSchedule
Dim EnableManualSnapshotCreation as Boolean = true
Dim KeepExecutionSnapshots as Boolean = false

'Sets the credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Sets the Report History options
rs.SetReportHistoryOptions(reportPath, EnableManualSnapshotCreation, KeepExecutionSnapshots, schedule)

'Updates the Report Snapshot
rs.UpdateReportExecutionSnapshot(reportPath)


End Sub


reportsnapshot.bat
rem change directory
cd to where ever you have the .rss file saved

rem This batch file calls Reporting Services Script 'reportsnapshot.rss' and passes the report path which is the %1 variable
rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

rem This will indicate whether there is an error. Error = 1, Non Error =0. This is used in the Process Task to indicate whether the task fails or succeeds.
EXIT [ERRORLEVEL]


Execute Process Task
Executable: Path to where you have the .bat file saved
Arguments: The path to the report (ex. "/folder/report") Note: You need the first "/". This will be passed into the batch file as the %1 parameter.
FailTaskIfReturnCodeIsNotSuccessValue: True
SuccessValue: 0 (The EXIT [ERRORLEVEL] will return a 0 or 1. 1 is fail. This will make it so the task will fail if the batch file encounters an error.)


If you are in need of clarification or if you have questions about this you can e-mail me!

Disapointing March Madness

So I'm a little disappointed with this year's NCAA tournament. There just weren't enough upsets and I'm sick of seeing Florida win everything. I hope UCLA creams Florida. Besides, Ohio State should have lost to Xavier in the 2nd round. Had only Xavier made that last free throw, Ohio State would not be in the final four. Needless to say, my bracket is full of red font and slashes. Maybe next year!

Thursday, March 22, 2007

Internet Marketing

So I launched a free marketing campaign yesterday for OneStopGPS.com. I tested this tactic out last week and with in seven days I increased my visits by 18 with just one ad. I even got a sale (my second) and made $18.80. All very exciting. In fact this campaign is much better then the pay-per-click campaign that I ran a month ago. First of all it is very targeted, and it is free.

So what will be the impact? I launched 18 adds in about 45 minutes yesterday. So far today I have had 27 visits and 106 pageviews. My highest days are 32 visits and 158 page views. I think I'm on track to break that record today. I have set up a system/process to generate these ads very quickly and I plan on expanding the ads into other products. Hopefully, the net impact will be more sales.

In one week, with one ad, I generated 18 visits and made $18.80 (1 sale only). If I can maintain those same numbers for each ad I should be able to generate about 324 visits and $338.00 in net sales per week. All without spending a dime on advertising!

I am also rapidly adding new features and functionality to the site. All in hopes of increasing conversion, customer satisfaction, and search engine ranking. In the mean time though, this marketing campaign could become very successful.

Thursday, March 15, 2007

ChoiceSkills.com

So in my web analytics class we are beginning a new and exciting project. We will be analyzing and making changes to www.choiceskills.com. This is a small commerce site that sells 3-5 products and generates about $12,000 a year in revenue. Our task is to anlalyze the site and make recommendations on how to improve the site's performance.

Just by initially evaluating the site I have a few ideas on how we can improve the site:

Search Engine Optimization:


  • Put a meta keywords tag on the top of each page. Make sure these keywords match content of the page. Right now there are no keywords.

  • Have a unique title for each page. Right now, the title for every page is just "Home Page".



Site Design

  • Redesign the left navigation panel. Make it more modern and user friendly.

  • Create a new logo.

  • Redesign the format so that it is not so plain. Add some structure such as lines or boxes. Maybe consider a consistent font.



As we dig into the stats of the site I'm sure we'll have some more suggestions.

Tuesday, February 27, 2007

Web Analytics Class Blog - 02/27/07

How did the Omniture Web Analytics Competition Go?
  • Great
  • Asked good questions
  • Not a lot of time to present (only 6 minutes)
  • Should a written deliverable be turned in along with your presentation?

  • We are not meeting for class on Thursday... go to the Web Analytics Competition on Friday.

    Any Recent Readings:

    Mod_Rewrite - Nate Skousen's Blog
    Page View Metrics - Ben Robinson

    Wrap up on WebLogs and AW Stats:

    - A job has to be setup to calculate all the stats based on the weblogs.
    - That means that AW Stats will only be updated when you tell it to using something like a CRON job.
    - Logs are temporary... so to get historical data, you need to capture AW Stats data periodically.
    - Why should you use AW Stats vs. Google Analytics? It might be good to have both. AW Stats uses Web Logs and JavaScript.
    - AW Stats has various plug-ins that you can use. For example GEO Locations.

    So to get AW Stats up and running you need:
  • A CRON job or equivalent
  • Code beacons in your pages
  • Configuration


  • - Some AW Stat benefits might include having data on files, and having data on your own server.
    Now on to Google Analytics:
    - You can export data to Excel!
    - You have to set up a web beacon in every page that you want tracked. Google Analytics will provide this code.
    - You need to have an account code... again Google will provide you this when you sign up a new site to Google Analytics. It is a tracking code for a specific site.
    - You can set up goals with Google Analytics - you need to know which variables to set in the beacon code or you can set them up in the Google Analytics interface.
    - You can also set up funnel reports and goals.
    - We then implemented Google Analytics on ebiz.byu.edu

    The battle with SEO

    So the battle continues with Search Engine Optimization (SEO). Why? Because it is a constant, bloody, nasty, frustrating, ambiguous battle. I have learned a ton in the past 2 months about how to rank high in search engines but actually seeing the results is a different story.

    There are many tutorials online about SEO but uderstanding SEO involves more than just tutorials. It requires staying current with Search Engine algorithms and best practices. I have learned what to avoid and what to implement.

    Recently, I learned about mod_rewrite which rewrites your URL's in a search engine friendly format. I had no idea that search engine bots ignore everything past the '?' indicating url variables. To get around this, mod_rewrite was invented. Basically if I had a url like "http://www.mysite.com/index.php?cat=productcat&title=prodcuttitle&id=123" mod_rewrite would rewrite the url to "http://www.mysite.com/productcat/producttitle/123". Big deal right? Actually, it is a big deal. This not only improves security but it also makes search engines happy. I'm not entirely sure why, but it does. Basically, the search engine bots are able to navigate your entire site and index it all. That is very desireable if you want to show up high (top 5) on search engines.

    How does it work? Well, you need Apache server. It is an Apache module and you have to enable it. Most hosting services will have this available. Second, you need to write your rules using REGEX expressions (Regular Expressions). That can be a little challenging but there are some trusty tutorials out there. This is a must have for SEO today. I will be implementing this shortly on OneStopGPS.com.

    For a more in-depth look at Mod_Rewrite, check out this great article/tutorial. It will do wonders for you... possibly change your life!

    Friday, February 9, 2007

    Real-Life Web Analytics

    I have officially made my first sale on OneStopGPS.com. Thank you for whoever purchased The Complete Idiot's Guide to Geocaching. I made a huge $0.39 in referral fees. I am seriously grateful for your purchase. It has given me a sense of satisfaction, even though it is only $0.39. It is a small start that at least paid for that person's click.

    As I have been battling SEO and my recent adWords campaign, I have been learning a lot about the vastness of the internet. Getting people to a website is a difficult thing that involves a lot of strategy, and in some cases money. It gets even more difficult after the person "lands" on your page and then converting that visit into a sale. I have had 39 unique visitors to OneStopGPS.com so far this month. That means my current conversion rate is about 2.5%. If I can maintain that conversion rate and have about 120 unique visits per month (current trend), I could potentially have 3 sales per month. That is probably not enough to make this worth while. I have learned that this is a hard game to play.

    However, this is where web analytics becomes extremely valuable. Not only does web analytics tell me how many visits I get per day, month, or year but it can help me increase conversion or increase visits. If I want to get more sales in a month I have to do one of those two things... or both! If I can increase visits and increase conversion then I will vastly increase my earnings. Web analytics can help me do just that. By knowing where my customers are coming from, what they are looking for, and how long they are staying, I can focus my marketing efforts to match those needs.

    Web Analytics can evaluate the entire e-commerce process from driving traffic, driving sales, and driving customer satisfaction. The trick is having the time, the patience, and the experience. Here is to a long process!

    Note: I actually made $3.33 not just $0.39. It turns out the same customer also ordered an handheld gps unit in addition to the book. I have temporarily paused my adwords campaign in order to improve the website.

    Wednesday, February 7, 2007

    Why Sports?

    So I really enjoy watching, playing, and talking about sports. There is something about competition that motivates me. I love watching a good game of college football, professional football, baseball, basketball, and even golf. However, in a way it is kind of ridiculous how much money is spent on sporting events and how much time people devote to watching and playing sports. Aren't sports really kind of pointless? They all usually involve hitting or throwing some sort of ball. Sure there are different strategies for each sport but ultimately the point of most sports are to hit, throw, or kick a ball in order to get more points than your opponent. What is so fascinating about that? So the question is why? Why do we enjoy it so much? Why is so much money spent on sports? How can a person devote their whole career/life to playing, analyzing, coaching, or talking about sports? If you step back and think about it, it is kind of wierd that we are so involved with sports. A close friend of mine forwarded me this article and I know the author's family pretty well and it is very touching how she relates life to sports and why it is important that we have sports. I'm glad there are things to relieve the stresses/pressures of life, and I think sports are one of those things. Thanks Natalie for sharing!

    Wednesday, January 31, 2007

    Creating a site map

    So I am really trying to drive traffic to my Amazon.com's associate website www.onestopgps.com. It really is a challenging thing to do and it seems like it will be an uphill battle. In an effort to maximize my chances of showing up in the search results I used Google's site map generator to create a site map for my website. Having a sitemap seems to be like a key ingredient for SEO. In the process however, I learned how to SSH into my webserver, how to execute a python script, and how to upload my site map to google. All good things to know. Periodically, I will regenerate my site map and submit it to google. Google seems to like that! Overall, I feel good, I feel like I accomplished something and learned something new. Now I just need people to visit my site and buy something!!!

    Tuesday, January 23, 2007

    E-Business Wiki

    I have been contributing to the BYU e-Business Wiki for the past couple of weeks. I have mainly been adding content to the e-Business Marketing section of the wiki.

    Here are the pages I contributed to:

    - The main E-Business Marketing page. I added the overiew section, and broke the page into internet marketing and e-Business marketing. Yes, I think there is a difference.

    - Under internet marketing technologies I worked on:
    - Web Transaction Logs
    - Databases and DataMining

    - Under marketing and branding strategies I added content to:
    - Affiliate Marketing
    - Viral Marketing

    Writting in Wiki was more challenging than I thought it would be. I found it diffcult and time consuming to find the syntax to use in order to format my pages correctly. I wish wikis would allow HTML. Maybe they do, I don't know. It was also hard to come up with content without copying other sources. For the most part I put things in my own words and sited sources where I borrowed content. I plan on adding more as the semester goes and as I learn more. I was mainly broad in my analysis and topics. In the future I hope to be more specific in some areas.

    Friday, January 19, 2007

    Search Engine Optimization/Internet Marketing

    A year and half ago I took an e-Business lecture series course at BYU. We had several e-Business professionals come and speak to us. One particular lecture that I remember was given by Jeff Barr from Amazon.com. He spoke about affiliate programs and highlighted Amazon.com's web services. He said that hundreds of thousands of people have websites that uses their affiliate programs, but that only a small margin actually make money doing so. The key, he said, is driving traffic to your site! That is the ultimate challenge. In an internet-world full of billions of websites, how do I get people to my site?

    I have been thinking and reading a lot about this. I have been looking and reading online about how to do search engine optimization and how to do internet marketing. One great resource I found was Paul Allen's blog. He has started several successful online ventures and I have been anxiously reading his tips. He also spoke at one of these e-Business lectures. Suffice it say, I need some help getting people to my website.

    Here are some of the questions I have:

    - How do I do search engine optimization for a dynamic site such as a PHP site?
    - What are the implications of changing content on my site? For example, does the optimization process start over and do I lose any ranking that I previously had?
    - How do I know what keywords to use and do they really work?
    - How do I create a site-map to submit to google if my site is dynamic?

    I know that Search Engine Optimization is only one way to drive traffic to a site. There are many other ways to drive traffic but SEO is definitely a must. Any ideas?

    I did find this that shows you what a spider sees when crawling a site... Give it a try!:






    Search Engine Spider Simulator


    Enter URL to Spider






    Thursday, January 11, 2007

    Web Analytics

    So this semester (Winter 2007) I'm registered in a Web Analytics course. This is my most anticipated class this semester and I'm very excited about it. I think there may be a vast difference between what I expect from this class and what will actually be taught and covered. I don't think this is the professor's problem but my own. I have a weird desire to learn and become an expert in web design and performance. So the scope of my goals are far beyond this class. However, I hope to learn the following from this web analytics course:
    • How to increase conversion
    • How to analyze website performance
    • How to improve website design
    • How to do search engine optimization
    • Best practices in web marketing
    I really want to apply what I learn to my online business, which is OneStopGPS.com. This is a new website where I'm trying to learn how to use Amazon.com's web services in order to create a store front for associate activities. I have not sold a thing yet, but that is fine. I'm using this as a learning vehicle. I also created a family website (SkousenConnection.com) in php that I did mainly to learn.

    My experience with web analytics is not very expansive. I do use GoogleAnalytics to analyze traffic to the above mentioned sites and that is about it. In addition, I work at MyFamily.com, Inc. who owns the acclaimed Ancestry.com & Genealogy.com. Because of this experience, I'm familiar with some KPI's and metrics.

    Web Analytics, however, is becoming a hot topic and a topic that is increasingly important to the web economy and e-commerce. Many companies are looking for web analytics and many companies are providing web analytic solutions. For example, it is rumored that Microsoft is in alpha stages for introducing a web analytics product.

    Here are some links that talk about web analytics:

    Ultimately, I'm very excited about this class and I look forward to a great semester!