Easily Download Facebook Wall Posts and Comments

May 11 2012 by mike in Example, Facebook, Widget | No Comments

One of the things we did in Next Analytics for Excel 4.3 was to include a revised widget (facebook-any-page-posts-and-comments.xlsx) that lets you download the posts and comments from any Facebook fan page. We have had lots of people ask for this, so we threw it into the package along with another 75 or so widgets you can mix and match in your web and social media reports. Next Analytics makes it look easy – just pick the widget, select a fan page, add it to your report, click the Refresh button.

It takes a powerful analytics solution like Next Analytics to make it possible. You can’t find this type of report anywhere else because Facebook doesn’t make it easy. Their programming interface (API) is designed to support applications like their own web interface; they show the most recent posts with up to 3 of the most recent comments. Their API works the same way – you get a series of posts and 3 of the most recent comments. Want more posts? Send another query (click More Stories). Want all the comments for a single post? Send another query (click View all N comments).

So how do you download the whole list of posts and comments for a given time period? Well, you make multiple queries until you have all the posts for that date range, then for each post that had more than 3 comments, you make additional queries to get all the comments. Oh, then you merge all that data into something the lines up the columns so it can be downloaded into a spreadsheet.

This is not a simple download operation, and so you don’t see many products with the capability. Even Facebook doesn’t offer the service. Luckily for you, we figured it all out and automated it using Next Analytics script commands. All you have to do is open the workbook, click the Next Analytics Refresh button, and wait while we interrogate Facebook for all the relevant data.

Any fan page. Post and comments. In Excel. Click.

Avoid Google Analytics Fast Access Mode

May 11 2012 by mike in Example, Excel, Google Analytics, News | No Comments

You can now easily avoid Google Analytics “fast access mode”, or data sampling, by using the new Next Analytics For-Each period queries.

Fast access mode occurs when you make a query that includes too many visit sessions (about 500,000 or more), causing Google to save its server resources and returning sampled data instead of the whole thing. This becomes a big problem with those valuable queries using advanced segments.

Next Analytics now lets you make individual queries for each time period in your query. Using the new date range definition, you can have Next Analytics make separate queries for each month or week for the period of interest. Results are joined together as if they were a single query, so it couldn’t get any easier.

Make Google Analytics Weeks Start on Mondays

May 11 2012 by mike in Example, Google Analytics, News | No Comments

Frustrated by the Google definition of weekly metrics that start on Sunday? Well, Next Analytics for Excel 4.3 can help! With our new ForEach period capability, you can get weekly metrics where you define what day you’d like to start the week on. And it is so easy…

Select your dimensions and metrics as usual , then check the new feature on the Dates tab that lets you repeat the query for each date sub-period. That means if you enter 26 Weeks, it will repeat the query for each week and join the results together for you.

The magic happens when you use the ‘Period starts on day’ value. For day-of-week, Sunday is day 0, and Monday is day 1, so entering 1 will result in weekly periods starting on Mondays! Just click Fetch, and your results appear in Excel. Perfect!

(No Longer) Secret Configuration Settings

April 11 2012 by mike in Example | No Comments

As products evolve, so do the secret little settings that change the way they work, and Next Analytics for Excel is no different. CAUTION: these are advanced user settings and are not tested as part of our release cycle, but since they are in the product, I thought I’d share a few of them just in case you have a strong preference.

All settings are entered into a variables.xml file, which must be placed in the My Documents\NextAnalytics\ folder. The basic structure of the file is:

<?xml version=”1.0″ encoding=”UTF-8″?>
<main>
<option value=’value‘ />
</main>

Where option and value can be:

  • ga_timeout – allows you to increase the timeout (in milliseconds) for big queries
  • want_save_as_widget_checked_on_load – ‘false’ to turn it off; on by default
  • keep_ga_technical_names – ‘true’ to keep the dimension/metric technical names

Two-Dimensional Whats Changed Analysis

March 29 2012 by mike in Dashboard, Example, Excel, Google Analytics | No Comments

As website owners, we try to stay on top of things that change from one period to the next. I am always curious to see traffic increases from a specific source to a specific landing page. You know the situation: someone posted a link to one of your pages and it gained popularity. The standard reports in Google Analytics make that kind of discovery very difficult, but it can be easy with a little Next Analytics wizardry.

The actual workbook with the Next Analytics script is linked below, but here’s some insight on how it works. I started with a simple 2-dimensional query: landingPagePath and source, using the visits metric. Then I made the same query for the previous period and joined the results together (see Merging Queries Into a Single Table).

I then change the numbers to a ratio of the total for each column. This results in something like a percent of visits number for each of the two periods. Taking the difference of those columns gives us a delta, so if a combination of landing page and source went from 0.27% of visits to 0.57% of visits, the delta would be +0.3%. Big increases or decreases in this list are of special interest.

Since we have two dimensions, lets pivot the data, showing landing pages versus source.  That’s a big table, though, and it contains a lot of what is called ‘sparse data’ where big numbers are interspersed with a  lot of empty cells. It would be much better if we could sort the items of most interest to the ‘top’, or upper left corner.

We do that by calculating the range of numbers in each row or column and putting the ones with the widest range in the upper left. I use the range of values because we’re not just interested in the increases – the decreased are equally important (what key referring site has suddenly gone away?).

This report can be embellished with a little Excel conditional formatting and we have quick report that keeps us on top of the trends. Since it uses a Next Analytics floating time period, it can track daily, weekly or monthly differences without having to worry about setting dates – just open the workbook and click Refresh.

The workbook can be downloaded here: two-dimensional-whats-changed.xlsx

Extrapolating on the idea, think what other dimensions or metrics you could use? Monitor goal conversion changes across medium and country? Ad group revenue by product category? Let your imagination go! Simply change the values in the prompt statements for a completely new report.

Merging Queries Into a Single Table

March 28 2012 by mike in Example | 1 Comment

Web analytics regularly need to contrast and compare multiple queries, whether they are different time period, different segments, or different web properties. Next Analytics lets you easily merge the results of multiple queries in a couple of ways.

While one query can be placed side-by-side with another in Excel, you run the risk that the dates or dimensions won’t line up from one query to the next, making the comparison difficult to visual, or just plain wrong if calculated. Using Next Analytics script commands, the queries can be joined in memory and written as a single table into Excel. The magic is as simple as naming one query result in memory using the script command “PageCaption,Query1”, and then using “AppendPage,Query1” to merge it into another query result.

One caveat: AppendPage will overwrite cells with the same row and column labels.

But you can simply change the names of the columns to keep them separate.

Caveat #2: if you have more than one dimension, make sure you include “,True” in the AppendPage command, or the results will be compressed to unique values in the first column.

Simple little steps with very powerful results – that’s what Next Analytics is all about. Enjoy!

Querying More Than 10 Google Analytics Metrics

March 13 2012 by mike in Example, Google Analytics | No Comments

Ever wanted to get a bunch of metrics and goals on one page but the 10-metric limit of the Google Analytics API won’t let you? With Next Analytics, it is surprisingly easy to get what you want, you just need to do a little scripting.

Start by making two or more widgets, each with the dimensions of interest and as many metrics as you can pack into each. In the example below, I have saved the widgets as w1 and w2.

Figure 1: Saving Widget 1 with visits metrics and some goal metrics

Figure 2: Saving Widget 2 with more goal metrics

Now open a new, empty workbook and use the Next Analytics Add Widget dialog to add the two widgets to your workbook. Hint: do NOT login and do NOT select the dimensions and metrics again – this will leave the previous selections in the widgets.

Now for the magic – unhide all the worksheets so you can see the actions and data worksheets. Cut all the script lines from w1_actions and paste them to the bottom of the  w2_actions sheet. Notice that both script segments end with a uiSaveInWorksheet command – this command writes the results of the query and analysis into a worksheet.

Don your propeller cap because you are about to alter the programming of an in-memory analytics engine. Change that first uiSaveInWorksheet command to “PageCaption,widget2” as shown below. Instead of writing the results into a worksheet, we are labelling it as a ‘page’ in memory that we can reference later.

Scrolling down to the second uiSaveInWorksheet command, INSERT a line above it with command “AppendPage,widget2,true”. This merges the previously labelled ‘page’ into the current one.

That’s it. To see the magic happen, select the w1_data worksheet and click the Next Analytics Refresh button. The results of the two queries have been merged together!

Next Analytics really does make it easy. Extend this example and get as many metrics as you want. You might need a wider screen…

SEO Landing Page Keyword Analysis

February 24 2012 by mike in Example, Excel, Google Analytics | No Comments

I got thinking yesterday of the problem with long tail keyword analysis. We have all this data and I know there is gold down there somewhere. Read through the list of search phrases used on your site, and while there are a lot of unique phrases, you start to see some repetition of specific words.

But are people landing on the pages you want them to? Are they searching for what you expected? Are there some new words you haven’t considered for your paid campaigns? Are some of your targeted pages attracting the wrong visitors; those looking for words you didn’t expect?

Using Next Analytics, we can download a list of the search phrases and the page they landed on, as well as the number of visits (entrances) that each combination produced. Using a technique we used for the Tag Cloud dashboard, we break the search phrases down into individual keywords and add up the number of times each word appeared in a search phrase for a particular landing page (see image 1).

Image 1. SEO Landing Page Keyword Analysis workbook (fictitious data shown)

By delivering the results into Excel in this manner, we can quickly answer those questions:

Are people landing on the pages you want them to?

The landing pages are shown in the order of popularity (from left to right), making it a simple matter to determine if there is a page in the list you didn’t expect to see.  A random support article listed higher than the product feature page you slaved over. An outdated blog post with a strong position. Your home page not holding a prominent position.

Are people actually searching for what you expected?

The keywords are also listed in order of popularity (top down), showing you individual words that appear over and over in various long-tail search phrases. This may give you a completely different perspective about what people are searching for – in the fictitious example above, “facebook” shows up in searches a lot more often than “twitter”.

See some new words for a paid campaign?

Most keywords should not be a surprise to you, but every now and then people land on a web page that gains surprising popularity. In the example above, the keyword “cloud” has garnered a good position. Searches that landed on that page also included “excel”, so people were likely looking for tag clouds in Excel, and that happens to be the topic of that article. Maybe a targeted call to action can be added to the page, and/or a new paid campaign created to focuses on tag clouds.

Are you attracting the wrong/disinterested visitors?

It is also possible that what people are searching for is not what the page they landed on was written for. For example, if the tag cloud article also showed a number of searches for “computing”, people may have been looking for information about cloud computing and were disappointed. You can use Excel’s data sort to sort each landing page column to see which keywords brought the most visits, and if some of the words are not consistent with what the page is about, you might want to review the wording on the page for misleading terminology.

This is a great report to discover what your web visitors were looking for and where they landed on your site. Check it on a monthly basis to see if one of your pages has garnered a sudden interest or if a trendy new word pops into the list. Most importantly, act on what you find.  Enjoy!

Download the file SEO Landing Page Keywords Analysis

How-to Accumulate Historical Facebook and Twitter data on your PC

July 12 2011 by mike in Example | No Comments

One of the cool things about Facebook is that there’s lots of publicly available data and you don’t need Admin access to get it.

For example, our Competitive / Comparative Analysis Worksheet shows lots of valuable data:
next-analytics-both-facebook-twitter-in-same-excel-worksheet

With this simple Excel dashboard you can the following information about anybody’s page.

  • The number of Facebook Fans
  • The number of Facebook shares
  • The number of Facebook likes
  • The number of Facebook comments
  • The number of Twitter followers
  • The number of Tweets

You can get that spreadsheet here: Competitive+analysis+worksheet+retail.xls

This is very useful information especially since if it is your competitors you are tracking.

But it’s not enough.  You need more than snapshot in time

You need to know how they are trending, are there any unusual values, does it correlate to any outside events or campaigns, is there a pattern?

I’m sure you’d agree it would be more useful to be able to go back in time and see what the values were last month and what the trends were day by day or even hour by hour or minute by minute (if you were, for example, tracking tweets about a TV broadcast tied to commercials being shown).

In short, do these steps:

  1. Change your Facebook and Twitter queries to create a data file for each day (rather than having a single query that fetches many days)
  2. Change your analytics to load the data files, and not to query Facebook or Twitter.
  3. Use our PowerPack Scheduler to get fresh data each day and
  4. Change the reports to use the new data files.

This blog article is a detailed walk-thru on how to do it.  This lets you gather competitive intelligence historically.

From Facebook, you can also get get:

  • Names of commentors (to a status message)
  • Listing of their comment
  • Number of likes on the comments

Learn to prevent Google Analytics Fast Access Mode–Use Offline data!

July 12 2011 by mike in Example | No Comments

If you have a busy site, you know the pain Fast Access Mode is causing you. Have sympathy for Google though. It is, after all, a free service, and you are making their servers work exponentially (factorially?) harder than most other businesses.

Some of you might be asking — What’s “Fast Access” mode. This mode alters your results if you are running a busy site. If you know anything about statistics, think of it as sampling, though I am not sure what Google’s algorithm is so I would rather not put a non-Google sponsored label on it.

One customer yesterday said they get 10 million hits a day. The size of the download was lengthy, causing timeouts, and they had to deal with sampled data. Yikes — In a way, I wish I had that problem!

Fortunately, I have the solution! The answer is to run a smaller queries, potentially one for each day, and build your analytics based off the results of those.

We have a technical blog article that explains how to do it. In short, there’s four steps:

  1. Change your queries to create a data file for each day (rather than having a single query that fetches many days)
  2. Change your analytics to load the data files, not query Google Analytics. These data files are on your own pc, not on some server somewhere!
  3. Implement a scheduler to fresh data is obtained each day and
  4. change the dashboards and reports to use the new data, and not perform queries to the data servers.

The big benefit is that there is no single large time-consuming error-prone download.

No Fast Access mode, no data sampling.

The dashboards refresh instantly because they’re not waiting for internet data transfers.

In fact, you pack the whole thing up on a memory stick and take it on a plane train or automobile.  Mobile GA, I like it!

Next Page »