Google analytics can be a bit overwhelming and some portions are downright alien to many users.

A client recently ask how they could use excel to anaylze some Google analytics data.

The Problem:

Download monthly Google analytics data, import the data into Excel and display a user friendly interface for a customer in less then 8 hours (due to budget and time restraints).

The solution:

We created an excel spreadsheet that the customer could use to import google analytics data in csv format.

The spreadsheet contained the following:

• 1 worksheet for each month (Jan - Feb)
• A worksheet to display graphs
• A worksheet for the top 20 traffic sources as identified by analytics data to anaylze.

Note: Each of these could have been placed in their own spreadsheets to segregate the data further.  We opted instead to place them in the same spreadsheet and hide the worksheets the customer did not need to see.  In this case the 20 traffic sources were hidden.

Each month, the customer will download a csv file from their analytics account and import the data into the spreadsheet (this could easily be automated but again the project was time restrained).

Graphs were created which are automatically updated to reflect the new monthly data and the customer will have a visual representation of their traffic sources for tracking purposes.

The monthly worksheets are raw csv data imported from the file that Google analytics generates.  Nothing special here.

The Graphs are also fairly straight forward - Displaying the site counts for the selected 20 referring sites, graphs for the bounce rate etc.  Here is an example of one of the graphs:

The worksheets for the site counts however, involved the use of vlookup Excel function (we could have used a few other funtions, but vlookup was quick and easy).

The design of the site count sheets required the use of 1 label to identify the site to be counted.  Each row was labeled Jan-Feb in col A.

There are 10 columns of data in the csv file which can be analyzed as needed:

• Source/Medium
• Visits
• Pages/Visit
• Avg. Time on Site
• % New Visits
• Bounce Rate
• Visits
• Goal1 Conversion Rate
• Goal Conversion Rate
• Per Visit Goal Value

Each cel in the site count reflects the value from one of these data elements using the following function:

=VLOOKUP(string-to-find!$col$row,where-to-find-it!$col:$col,Column-to-return,FALSE)

string-to-find!$col$row - Used to identify the name of the referring site in the current sheet.  Example:  If the referring site to count is Google and the name as it appears in the raw data is located in Col E, Row 1, then this entry would have the following value: Google!$E$1  which says look inthe sheet called Google at col E row ! for the string to look up. (Note: The “!” is required since we are using this formula to reference other worksheets in the same spreadsheet)

where-to-find-it!$col$row - This is used to identify which monthly sheet we want to find the string identifying the referring site.  Example: If we have 12 sheets, labeled Jan - Dec for the raw data, this entry would have the following value for January: Jan!$A:$Z which says: Look in the sheet called ‘Jan’ in the range of cells beginning at A and ending at Z.

Column-to-return - This is the column number whose data is to be retrieved if the string is found.  In the example, we are bringing back the First Column or the searched column.  If we wanted to bring back the second columns data, we would change this value to 2.

FALSE - This final element is telling the function whether to use a case sensitive search or not.  In our example we are not using a case sensitive search.

Here’s an example with some test data for Jan - May:

Overall, an easy spreadsheet for visualizing referring sites so the customer can compare their targeting site traffic.

How to use Autofit to adjust Column widths…

There are several ways to use ‘Autofit’:

I am going to assume VB Script or programming will not be used, so the following methods do not utilize programming techniques.

To Autofit 1 column first method:

Once you have written the text in the cell of a column that you want to Autofit:

1. Move your mouse to the top of the spreadsheet where the column headings are (A through ZZ etc.).
2. Hover your mouse over the right side border line of the column you want to Autofit. Your cursor should change to a solid bar with a right and left arrow.
3. Double click the borderline while your cursor is displayed as the solid bar with the arrows.

This will contract/expand the column to the min/max size of the text that has been entered.

Note: Method 1 above WILL NOT work if you have report headings in columns. The reason is: Report Headings tend to be wider then the data entered into cell elements or may have more then 1 cell merged together to make a single cell.  Using method 1 will contract/expand the column to fit the Heading, not the data.

Therefore, if you have report headings, use method 2…

To Autofit 1 column second method:

Once you have written the text in the cell of a column that you want to Autofit:

1. Select the cell (notice cell, not column) that contains the data you want to Autofit (note: if you have just entered data into cell, you have to press the enter key or move off the cell for the data to be accepted into the cell, then reselect the cell)
2. Move your mouse to top of the spreadsheet where the menu options are.
3. From the menu, Select->Format->Column->Autofit Selection

This will contract/expand the column to the min/max size of the text that has been entered.

These methods will work to Autofit both columns and rows.  To Autofit a row, repeat the above steps and substitute ‘row’ for ‘column’.

There are additional ways to accomplish this, however they involve programming using macros and VB script and were not part of this question.

Hope that helps.

If you have comments or questions regarding applications, contact us and we will do our best to answer them promptly

One issue that I see posted is how to send messages to a group of people at once.  I’ve seen this mostly in some of the games that are being offered by Facebook third party vendors.

So, here is how you can organize your friends into lists and then send emails/messages to groups of friends:

1. Go to your friends list at the top menu of FB. On the left side you should see “Friends List” and a button “Make a New List”.
2. Click the button and enter a name for the list
3. Hit Enter to create the list
4. Next you will get a new screen “Add to list”.  Next to the entry field for adding a friend is a link “Select Multiple Friends” click the link and up comes your friends list.
5. Select the friends you want to add to the list by clicking each one, once (they will become highlighted/selected).
6. When you are done selecting your friends for the list, scroll to the bottom and click the “Save List” button.

There, you’ve made a friends list on Facebook and can send group messages to hearts content.

This time we’ll add some MS Publisher keyboard short cuts.  Remember when executing short cuts, to press and hold CTRL, ALT and SHIFT keys before pressing a printable character key.

• Ctrl B -  Bold text
• Ctrl U - Underline
• Ctrl I - Italic
• Ctrl = - Superscript
• Ctrl Shift = - Subscript
• Ctrl Shift K - Small caps
• Ctrl Spacebar - Will remove all style formats from the highlighted text
• Ctrl Shift f - Accesses the font menu. Use the arrow keys to select a font, and press the enter key to change the text to the selected font
• Ctrl Shift P - Accesses the font size menu.  Use the arrow keys to select a size, and press the enter key to enable the change
• Ctrl Shift > - Will increase the font size by a half point
• Ctrl Shift < - Will decrease the font size by a half point

If you have any additions to these or other application keyboard shortcuts, feel free to post them.  As always, our blog is nofollow free and we welcome comments, questions and suggestions.

Giving your visitors a convenient method of adding your site and being able to quickly return to it, will provide several benefits:

• Recognition - Your visitors will recognize and associate you as a technology aware agent.
• Tracking - Most website statistic counters can track how many times your site has been bookmarked
• Ease of navigation - Giving your visitors a bookmark will allow them to easily navigate to your site. This can also be tracked by many statistic counters.So, how do you do it?There are several ways a visitor can bookmark a website as I’m sure you are aware. The most often used method in IE or FireFox is to “Add to Favorites/Bookmark”. Did you also know that holding down the Ctrl key and pressing ‘D’ would also do this?But that really doesn’t help you to provide a method to your visitors. So, here is some code you can add to any webpage on your website:

  <a href=”javascript:window.external.AddFavorite(’http://www.YOURWEBSITEURL.com’,'Favorites NAME OF YOUR WEBSITE 1′)”>Bookmark </a><strong>NAME OF YOUR WEBSITE 2

  Replace the YOURWEBSITEURL with the url for your website. Also replace the NAME OF YOUR WEBSITE 1 with text you want to be displayed in their favorites list and replace NAME OF YOUR WEBSITE 2 with what you want displayed on your website.

  That’s it.

Cochran Consulting - Tech tips

This is most often and easiest to accomplish using the H1, H2 etc. tags.

What are they?

‘H’ tags are used to promote and organize content. They are very helpful in content as they provide the user with text that stands out as title information for a webpage.

Think of ‘H’ tags as a way to outline webpage content.

When to use:

• H1 tags are used as a Heading. Each webpage should have only 1 heading.
• H2 tags are used as sub headings. A webpage may have more then 1 sub heading
• H3 etc. tags are used as identifying headings. A webpage may have more then 1 identifying heading.

Interesting to note is that while you should leave H1, H2 and H3 tags formatted as they have been designed, H4, H5 and H6 can be customized to provide different font style and sizes. This will add to the appearance of text custom formatted with these ‘H’ Tags.

Providing properly formatted content by employing H tags will not only benefit your users but your website as well.

It will give it a clean, professional look and feel and your visitors will thank you.

When people think of VoIP, they usually think of services like Vonage or their cable company.  In fact, these companies usually advertise their services as “Broadband Phone Service”.

While these companies do offer VoIP services, we will be discussing services much simpler than full-fledged telephone service.

VoIP cab easily accomplished without subscribing to a service and without a specialized telephone or other equipment. Basic VoIP can be accomplished by an internet connected PC which contains a soundcard that supports a headset or microphone and speakers.

There are several services available for consumers to utilize VoIP. Some are free or incur very small costs. One such service, Skype is available as a free download, and is one of the more popular applications in use.

Skype allows members to make free PC to PC calls regardless of distance and, for an extra fee, they can send/receive calls from standard telephones.

VoIP offers many benefits over traditional telephone services. These benefits, has this service positioned to become the phone system of the near future.

One of the main benefits of VoIP is its flexibility. You can take your phone, and your same phone number, with you anywhere in the world where a broadband internet connection is available.

Another benefit of VoIP is the price. In addition to offering local and long distance for lower rates, VoIP also bundles extra calling features that many have come to rely on ( caller ID, call waiting, three-way calling, etc).

In many cases, VoIP allows more advanced features not currently available with your typical land line. These services include: the ability to check voice mail via the web, voice messages sent as an attachment in an e-mail.

The some web interfaces also allow for detailed calling logs to be reviewed, customized messages applied to certain callers, as well as special call forwarding settings.

VoIP is not new, but as phone service prices rise and technology in general advances, the popularity of VoIP continues to gain momentum.

For additional information or to try Skype yourself, visit their website.

If you’d like to see Skype in use, several of our clients utilize this service in communication with our company:

Visit any of the following to see Skype in action:

Panama City Real Estate - Skype Id: panama.city.real.estate

Futureware Software Consulting - Skype Id: futurewarescg

Cochran Consulting - Skype Id: cochran.consulting

Did you know that you can save those documents as a MS-Word template?

That way when you want to use one of your documents as a starting point you can select it from the ‘New from template’ MS-Word menu!

How?   Simple…

1. Open your document in word.
2. Select ‘File’ ‘Save as’.  You will get the ‘Save As’ window.
3. At the bottom of the window where you would type the name of the file is a menu that says ‘Save as type’. Open the drop down menu and select ‘Document Template’.
4. Name the file.

Now when you want to start a file using your template, you can select it directly from within the MS-Word template dialog box on the ‘General’ tab.

Cochran Consulting - Tech tips

Ctrl B, Ctrl I, or Ctrl U apply bold, italics, or underline to highlighted text, respectively.

Ctrl P prints whatever is in an active window.

Ctrl Backspace erases an entire word at a time, instead of a letter. Ctrl up or down arrows let you scroll an entire paragraph at a time, instead of one line, and Ctrl Shift up or down arrow will select an entire paragraph.

Ctrl Enter inserts a page break in Word.

Alt Ctrl C inserts the copyright symbol (Alt Ctrl R inserts the registered trademark symbol, and Alt Ctrl T makes the trademark symbol).

In Outlook, you can jump to the section you want: Ctrl 1 switches to the Mail window, Ctrl 2 switches to the Calender, Ctrl 3 to Contacts, Ctrl 4 to Tasks, and Ctrl 5 to Notes.

Ctrl Shift M starts a new message in Outlook. (Use Ctrl Shift C for a new contact.)

In Outlook e-mail, hit Ctrl N to compose a new message, Ctrl R to reply to a message.

The only Excel shortcut I’ve ever known, Ctrl , enters the date. (If you live in Excel, you should have the Excel Keyboard Shortcuts page in your Favorites (http://office.microsoft.com/en-us/excel/HP052037811033.aspx)!

Cochran Consulting - Tech tips

It is quite a daunting task to keep track of all these id’s and passwords.

However, a company called FutureWare has an excellent product called KeyRing.

What key ring does, is allow you to store unlimited user id’s and passwords for any and every application. It can even launch the application or website.

It’s very easy to use, easy to learn and best of all it’s reasonably priced.

You can find it here:
http://www.futurewaredc.com/keyring/

You can even try it for free, with no oblication!

Cochran Consulting - Tech tips