Adding a new query to an Excel workbook using ShufflePoint's IQY Web Query capability is pretty fast and easy. But faster and easier is always better. After finding that another Excel default wasn't appropriate (and Excel doesn't remember your selections for the next query), I decided to create a short macro which makes all of the settings that we recommend in our how-to on Excel. It eliminates a dozen mouse clicks and the chance to make mistakes. The macro assumes that your key, timeframe, and profile cells have been named. You can do that by right-clicking those cells and selecting...
Read more...
Over the recent months we've been getting a lot of requests from our clients to gather data on what page an event occurred on. It turns out the solution was the just right mix of dimensions and filters. First, a little background on Google Analytics event tracking: Google Analytics gives you the ability to track different actions that visitors take on your website through some customization of the stock Google Analytics tracking code . Some of the most popular event tracking applications include clicks on outbound links, document downloads, video stats like plays, pauses and finishes, flash elements, AJAX elements,...
Read more...
After creating this entry and the images, it was realized that another parameter, "visualization", was needed to make the solution generic. The updated Excel file reflects this but the screenshot does not. Several customers in the past have asked if it is possible to place thematic maps in Excel and PowerPoint. And in the past, my response to these queries has been "probably, but it sounds difficult". Recently a new customer asked this questions, and, having recently blogged about Google's new map gadgets, I decided to have a go at getting maps into Excel. In the recent map gadgets article,...
Read more...
Some time while I wasn't paying attention, Google posted a neat new map gadget to the visualization gallery. It is called "Geomap", and it is the same map gadget used in the Google Analytics web portal. The documentation page is listed at the end of this article. This gadget also shows up in iGoogle with the name: "Value Map". If you type that in the "search for gadgets" box, it should come up as the first entry (don't search for or use "Geomap" as this finds a non-Google gadget). The gadget renders using Flash, which means no iPhone/iPad support. But...
Read more...
We found today that the monthsToDate timeframe is throwing an error and we plan to roll out a hotfix tonight to address this but wanted to first get feedback from users. We believe that the correct behavior will be to make the following translations when the query is run in the first month of a year and has a ga:month dimension present. monthsToDate is replaced by lastCalendarYear lastCalendarYear is replaced by lastCalendarYear2 lastCalendarYear2 is replaced by lastCalendarYear3 The rational is that if you requesting months at the end of a monthly reporting cycle and it is also the end of...
Read more...
Excel reports built with ShufflePoint can get complex when you add lots of queries. Part of the difficulty is that Excel doesn't have good tools to look at all of the queries that you have added to your workbook. To plug this gap, I've created a macro which a new sheet to the current workbook and creates a sort of "dashboard" of all the queries you have added. A new sheet named "QueryDebug" is added to the workbook. I think any ShufflePoint customer using Excel will find this to be useful. A useful extension to this macro would be to...
Read more...
Tag Clouds are a very common paradigm for navigation on blogs and web sites. The typical tag cloud consists of a block of works, one after another, usually ordered alphabetically and with font size being used to represent a variable such as the number of articles so tagged or the number of searches performed on a particular tag. I am going to set out in this article to create a Google Gadget which implements a tag cloud from data obtained from Google Analytics. Using custom variables to tag pages First, consider how you can "tag" pages with Google Analytics. There...
Read more...
The default ShufflePoint IQY files have four parameters: key, profile, timeframe, and query. Customers often ask if they can remove some parameters to make data imports more streamlined. You can certainly edit the IQY and remove timeframe and profile. I always keep such a file handy (see attached). They can be removed because they are optional. If these parameters are not specified in the query or in the IQY parameter, it will use your ShufflePoint defaults. The authentication key is not optional so it must be present either as a parameter or as a static string in the IQY file....
Read more...
Our latest build includes the ability to have two timeframes present in the query. ShufflePoint will interpret the second time period as a previous period against which you wish to compare the first period. A simple example of such a query is: select metrics ga:visits on columns dimensions ga:medium on rows from default where timeframe lastMonth, lastMonth2 In English, is "Get visits by medium for last month and the month before, and get the values and the change in values" Since only Excel imports using IQY files currently support this new feature, the first thing I'll do is get an...
Read more...
Ok, I admit it. I'm a gadget freak and a visualization freak. So of course I am going to enjoy playing with the Google Visualization Gadgets. You need to check periodically to see what cool gadgets Google or 3rd party developers might have added to the gallery. Today I notice a new Treemap gadget by DrasticData. I was present at CHI99 when Wattenberg first showed this visualization which became famous as the "Map of the Market" on SmartMoney. It is great to see a high-quality implementation arrive which can work with the Google Vis API. This visualization works really well...
Read more...
