In this age of couponing and PPC, it’s easy to waste profit and lose customers by selling the wrong thing to the wrong people with the wrong messaging.

To be successful in couponing, you need our analytics to segment data and to learn which segments to focus on.  What choices do you have for segmenting? Find the intersection of these things:

• Type of offer: Either 2-for-1 or free-with-purchase get people to buy more.  Introductory pricing attracts new customers.
• Type of product being promoted:  High or low margin? High or low volume?
• Location:  Discover which promotions are getting used in which locations
• Buying Group:  Which group they’re coming from hints at behaviours.Watch for patterns, and see which Buying group is getting the most volume, conversions, revenue. This tells you where to invest your promotional effort/budget

For campaign goals, you want to:

1. Focus on the products/services that have good margins.
2. Decide if your goal is to get first-time purchasers to keep coming back or to sell more of high-volume and/or high-profit thing.
3. Avoid hurting your brand with the wrong messaging.

You want reports that tell you:

1. Which offers are getting the most uptake?
2. Which buying groups are getting the most uptake?
3. Which locations are getting the most uptake?
4. What combinations of those are occuring most frequently?

This lets you conduct compaigns suited to who is using the coupons and learn which campaigns aren’t working. You can also check your retention strategy, that it’s working.

Next Analytics has reports and adhoc query capabilities that tell you:

• Which offers are get the most business
• Which ones are retaining *new *customers
• Which message is working (in which location)
• Which Buying Group is getting the most results.

Every day, you want to analyze this and adjust your campaigns and landing pages.   If you do this, you will be successful because you’ll do less work with more effective results.  The secret is to focus on a small number of campaigns that have a proven track record for new business and profitabilty.

Here’s a few ideas for Next Analytics reports and ad hoc analytics that will help you.

Offers that work best. Put Google Analytics tracking codes on your landing page URLs and it’s easy to see which campaigns, source, and medium.  This can tell you which offers are working best. You can also create reports which show you landing page popularity.

Landing Page by Location.  We have a useful report for analyzing important landing pages.

You can also use Next Analytics to build an adhoc query. I suggest this because I think every business will want something different. My adhoc query would do the following steps:

1. Filter out the home page and other non-campaign pages that are popular
2. Pick dimensions of city and landing page and date.  This is where people might pick other things to track such as depth of location, or more filtering on pages, or even incorporate campaign or medium or source into it.  Using http://cloud.nextanalytics.com you can experiment with combinations that work for you.
3. Automatically pivot, and sort by the most recent time period. This shows you what’s hot, but gives you an idea of trends too.
4. Keep only the top ten, but also show the average of the rest.

This report is easy to build and put into Excel – takes less than a minute.

Campaigns Getting the Most Volume and Conversions (Comparing Campaign Funnels)

Find out which campaigns are working best by using this excel report.

Engagement reports . They’ll give you an indication of whether or not your campaigns are contributing to retained business. Get them here. There’s also another good report, here:

In this age of couponing and PPC, it’s easy to waste profit and lose customers by selling the wrong thing to the wrong people with the wrong messaging.

To be successful in couponing, you need our analytics to segment data and to learn which segments to focus on.  What choices do you have for segmenting? Find the intersection of these things:

• Type of offer: Either 2-for-1 or free-with-purchase get people to buy more.  Introductory pricing attracts new customers.
• Type of product being promoted:  High or low margin? High or low volume?
• Location:  Discover which promotions are getting used in which locations
• Buying Group:  Which group they’re coming from hints at behaviours.Watch for patterns, and see which Buying group is getting the most volume, conversions, revenue. This tells you where to invest your promotional effort/budget

For campaign goals, you want to:

1. Focus on the products/services that have good margins.
2. Decide if your goal is to get first-time purchasers to keep coming back or to sell more of high-volume and/or high-profit thing.
3. Avoid hurting your brand with the wrong messaging.

You want reports that tell you:

1. Which offers are getting the most uptake?
2. Which buying groups are getting the most uptake?
3. Which locations are getting the most uptake?
4. What combinations of those are occuring most frequently?

This lets you conduct compaigns suited to who is using the coupons and learn which campaigns aren’t working. You can also check your retention strategy, that it’s working.

Next Analytics has reports and adhoc query capabilities that tell you:

• Which offers are get the most business
• Which ones are retaining *new *customers
• Which message is working (in which location)
• Which Buying Group is getting the most results.

Every day, you want to analyze this and adjust your campaigns and landing pages.   If you do this, you will be successful because you’ll do less work with more effective results.  The secret is to focus on a small number of campaigns that have a proven track record for new business and profitabilty.

Here’s a few ideas for Next Analytics reports and ad hoc analytics that will help you.

Offers that work best. Put Google Analytics tracking codes on your landing page URLs and it’s easy to see which campaigns, source, and medium.  This can tell you which offers are working best. You can also create reports which show you landing page popularity.

Landing Page by Location.  We have a useful report for analyzing important landing pages.

You can also use Next Analytics to build an adhoc query. I suggest this because I think every business will want something different. My adhoc query would do the following steps:

1. Filter out the home page and other non-campaign pages that are popular
2. Pick dimensions of city and landing page and date.  This is where people might pick other things to track such as depth of location, or more filtering on pages, or even incorporate campaign or medium or source into it.  Using http://cloud.nextanalytics.com you can experiment with combinations that work for you.
3. Automatically pivot, and sort by the most recent time period. This shows you what’s hot, but gives you an idea of trends too.
4. Keep only the top ten, but also show the average of the rest.

This report is easy to build and put into Excel – takes less than a minute.

Campaigns Getting the Most Volume and Conversions (Comparing Campaign Funnels)

Find out which campaigns are working best by using this excel report.

Engagement reports . They’ll give you an indication of whether or not your campaigns are contributing to retained business. Get them here. There’s also another good report, here:

The trend of page depth is one of many indicators of how well you’re doing as a web site designer and content author.

Page depth is an indication that people are interested in the content on your site. The more pages they view, the more they like what they see.  Or perhaps they can’t find what they’re looking for — it really does depend on your site’s intent. Either way, you want to know the trend.

This article shows you an easy way to see the trend of your page depth.  First, you need to log into your free trial account at cloud.nextanalytics.com.  This report can also be built using our PowerPack for Windows Excelusers.

Then you choose Build, and Google Analytics. You can now lay out any dimension or metric, but we’re going to choose to view page Depth.

Next, we want to create a Google Analytics Custom Segment.  For this segment, we want to include data that has only 6 or more pages viewed in a single visit. Since page depth is a text field, we need to use a regex statement to accomplish that. Here’s how we do it:

Our report is by default already trended, showing the last 7 days of data (can be changed by clicking “date”).

We want to sort our by the most recent day, to see which page depth had the highest frequency in the most recent time period. When we save this report in Excel, we want this report to automatically re-calculate for the most recent 7 days — which it does do.

We click the View Data, and we see the results. This data has been segmented by the Google data servers to only contain visits that had more than six views.

This data was first trended, and then sorted according to the most recent date.  We weren’t able to use the Google Sort because the data had to be pivoted first (to have the dates on the column axis).

This gives us an indication if our recent work has been improving the number of page views among those who show they’re very interested in our work.

Notice that, of the people who visited more than 6 times, the most popular frequency of page depths is higher than 10, followed closely by 8 then 6.

If you’re interested in this topic, then you might also be interested in some of our published dashboards relating to Engagement.  You can see the full list on our product page, but of particular note are the ones relating to engagement. Here’s two examples:

EngagementAnalysis.xls
EngagementAnalysis.xlsx

In modern SEO, you ride the long tail.  You create lots of domain specific content so the search engines find you.  You create variations of some base concepts, using lots of different keyword combinations. Believe it or not, after a year of doing this, it’s not hard to be at a thousand pages. And you will do it, because see your traffic rising.

So, what do you do when you have a thousand pages? How do you track and measure that?  If you run a report for all your unique urls, for each day the past two years, you’re probably looking at half a million records. Yikes!  And, since it’s long tail, it’s all important.  Nobody cares about your top ten or twenty pages. Those are the branded searches, by then your prospects are well into your funnel, out of the hands of the SEO analyst.

So,  here we are…..  half a million rows….  You have 365 days of data for about a thousand page urls. Like children, they’re the result of hard work and a big investment, you love them all equally, they’re all important.

What are some of the pitfalls? First and foremost, Google Analytics will send that much data only so fast.  It could be a two hour wait for Google Analytics to send that much data. Secondly, Excel starts to get sluggish as the number of rows rises, in Excel 2003 it will outright fail, and most importantly: You can’t use PivotTables because Excel requires you to load the data into a worksheet before you can pivot.  Finally, a serious one: If you ask for too many rows at once, Google will provide you with Sampled Data.

I’m going to tell you how Next Analytics solves these issues.

You are going to see how this Excel Addin was bulit for production demands.  We’ve successfully demonstrated that our Addin is capable of loading 500 million records, on a store-bought laptop, in 35 minutes (after it was downloaded and cached from Google Analytics – see below for how to do this).

In-Memory Analytics

One of the big advantages is that we don’t read large amounts of data straight into the Excel worksheet.  Instead, we have an in-memory analytics engine that lets you do things like:

- filtering (while loading into wks, e.g. filter to include only page-urls that contain the word video)

- fixing (essentially an automated search/replace, e.g. combining urls that contain the word newspaper into a single row)

- pivoting (putting dates onto a column axes)

- calculations (convert raw metrics into comparisons and various normalizations)

Automatic Pivoting of your Data

Even if you are downloading 500k individual records, once you pivot, it might only be 5,000 rows by 12 columns.  Why the shrinkage?

Because we’ve converted the 365 different days of the year into their month equivalents and aggregated the values, before the data was written to a worksheet.   By aggregation, I mean calculating sum, min, max, average, median and others?

Why can’t you just use Google’s month dimension? Because it calculates this up on the server, and its calculations ignores your individual settings down on your client such as Data Fixes, Filtering, Pivoting, and Calculations.  For many business contexts, aggregation has to be done locally, in conjunction with those things.

Auto-pivoting is one technique for letting you load data that would normally exceed Excel’s limits.

Batch Scheduling

GA sends data in 10k chunks. A million records can take an hour or two (several gigabytes).  This can’t be avoided.

So we offer a scheduler.  You create a dashboard that you know is going to take a long time, and that program will open and refresh the data in that dashboard for you, in the middle of the night, every day.  When you show up in the morning, the numbers are all done.  Even if you wanted “the data” there it is, in the CSV file.

Cached Data

You can avoid Google Analytics data sampling by creating smaller queries, caching them to your local PC, and concatenating the results before doing the math for your dashboard.  For older results, you don’t need to re-download, only the incrementally new data.

When you examine our scripts, you wil see that the GetGoogleAnalytics data command saves to a CSV file.   You may also notice that the verb that follows it is “ImportDataFiles”.  Notice the plural.  This will automatically load multiple CSV files, made from separate Google Analytics queries. Cool eh?

Conclusion

Not all Excel Addins are created equally. Too many thing that just connecting to the data and loading the data into a worksheet is enough.

Unlike those other Addins, we add a whole layer of business intelligence to the data.  This makes your dashboards more powerful, with metrics and KPIs that are more useful.

The latest thinking in SEO is to invest in content.  It’s far more achievable to be a leader in a narrow well defined domain of expertise than to compete for broad topics. As such, successful companies are investing in content on their site. This blog article focuses on how to monitor and audit what you’ve already written.

Also, my advice is to not focus on your MOST POPULAR Pages. That’s boring and rarely does anything unusual happen. Deliver those kind of reports, and those are the ones that people never read.

Instead, you should focus on the LONG TAIL. That’s the pages which aren’t your top pages, but have a steady traffic. If you have many pages like that, then you’ve been doing a good job of investing in content. That’s the theory being put in practice. Build plenty of good quality content, and people will find you.

So how do you measure that? Everyone wants to measure the relative popularity of your pages. Long tail analytics means knowing more about the pages which are being landed less frequently than your Top 10 pages.

Here’s how. First, your page URLs identify your content. If you don’t use mnemonic names in your urls, you probably should use Page Titles.  Side point: Some sites might need to strip their tags from what Google Analytics reports so that every single visit is a unique URL. This is easily done using Google Analytic’s filters.

You want to see your URLS ranked by pageviews, unique pageviews, visitors, and unique visitors, and unique visits.

The following image shows you how to ask for that in our tool.

After that, you want to be able to ask for it by dates. The following dialog is how to ask for it either by a rolling time period or by specific range of dates.

And, finally, it you want to put the most recent URLs up at the top of the list so you can compare popularity.

So now you have a ranked list of your URLs according to your choice of measures.

This is your long tail content.

• See  pages are trending up or down in popularity.
• Which pages no longer get visits.
• Which ones got visits in this period but not in last period, and vice-versa.

All this is available in just a few clicks.   Now you know how your long tail content is doing. You’ll see the trends and patterns. It will help you plan what to write about.

While you are executing on your long tail strategy, it would be good to know which authors, categories, and publish-dates were and still are being the most successful for you.

How can you take the data being supplied by Google Analytics and add more dimensions and hierarchy?  This article provides a practical step by step explanation how to do that.

Many web sites have more than one content contributor so it’s useful to be able to evaluate the popularity of that author. Or, content can be categorized and subcategorized and it’s good to see which categories are popular. Finally, it is a good to get a sense of the relationship of popularity with the age of a content item.

GA logs metrics against pages but it does not store author’s name, creation date, category, and sub-category. You should be able to sort, filter, and summarize on those things. Fortunately, with Next Analytics, it’s easy to merge that information into the GA supplied data and offer you that analyses.

Lets walk through a simple example. Google can provide a list of pages visited by day.

We want to add Author, Category, Sub-Category to that data (and Google doesn’t have that information).

So, we issue a command to append one set of data to the other. Things with matching article-titles can be blended. The combined set looks like:

Using Next Analytics, it’s easy to see that data trended and, once trended, is easy to create a chart, dashboard, or scorecard.

With trends, it’s easy to see which items are rising or declining in popularity.  We can also sort by author, date-published, category, and subcategory.

What you’ve just witnessed is how to create custom dimensions and append them to dimensions supplied by Google Analytics.

This allows you to establish a whole new hierarchy of dimensions that are added to what’s coming from the servers.  You could summarize and sort on any of the new dimensions. For example, ask for a sub-total by Topic, Sub-topic, or Author.

For those of you who are familar with our scripting language, this is all it took:

Scripter’s notes:

1) the first row reads in a custom categories.csv file; you can supply your own.

2) the second row simulates a query that puts the data into file named smalldata.csv.  This could have been done offline in batch.

All of our scripts that issue queries save the data to a tempoary CSV file.  This includes:  Google Analytics, Facebook, Facebook Insights, Twitter, LinkedIn, Constant Contact, SQL, and even SAP BW.

3) The third line, AppendPage, blends the categories to the incoming transacitons.  It is looking for a match in the first data column in each incoming file.

4) The work in progress is saved to “appended.csv” and a crosstab is made to make the trend of dates visual and easily charted.

5) The data is sorted.  It could have been sorted by any column.

6) It finishes by saving the finished results in a worksheet.