Seer Blog How to Avoid Google Analytics Data Sampling Using Excel
Analytics

How to Avoid Google Analytics Data Sampling Using Excel

The Yellow Box of Death.

Google Analytics Screenshot - This report is based on X visits (X% of visits). Learn more

Ok. Death is a little extreme. Really, this is just a Google Analytics alert that this report is based on sampled data.

But anytime I decide I’m going to do some deep analysis in Google Analytics (add a second dimension, add an advanced segment, etc.) and this little box appears, I get a little sad.

Why did you sample my data? Sad Cat
I don’t want a sample. I want my data. So how do we get around this? Well, here’s a method that can take a little bit of time (a little less with the API) but give you all your data with Excel.

GET ALL THE DATA!!!

Step 1: Get as much data as you can.

First, tell Google you want higher precision. Click the symbol above the yellow box and drag that little circle all the way to the right. GA defaults in the middle, so make sure you always update this. (If you’re using the API, set the new samplingLevel parameter to HIGHER_PRECISION.)

Select Higher Precision on Number of visits to calculate this report.
Sometimes, if you’re really lucky, just moving that slider will take you out of sampled data. Woohoo! You can stop right here! Still have a yellow box? I’m sorry. Let’s move on.

Step 2: Export smaller data sets to Excel.

The two instances in which you hit report sampling according to Google are described here:

  • 1,000,000 maximum unique dimension combinations for any type of query.
  • 500,000 maximum sessions for special queries where the data is not already stored.

It’s the first type that we’re able to avoid with this method. Note in their example, a 60 day report you could get 16,000 URLs (1,000,000/60), but a 30 day report you could get 30,000 unique URLs (1,000,000/30). So, shorten your time frame to 14 days, 10 days, 5 days, 2 days, and you’ll increase the number of unique URLs (or any dimension) you can get in your report without hitting report sampling.

So, let’s select shorter date ranges. Say, Aug 1-10, Aug 11-20, and Aug 21-31.

Select small date range in Google Analytics

It doesn’t matter if you pull 1, 2, 5, 7 days at a time, just make sure you aren’t overlapping and you cover the full time range you want to report on. I prefer to export as CSV, but you can export as CSV, TSV, TSV for Excel, or Excel (XLSX).

If you’re hitting sampled data looking at a single day, you may be stuck with report sampling, at least for that view (profile).

Note: You can use export almost any metric you like, as long as it’s a number, not a calculated percentage. For example, pull Bounces not Bounce Rate. More on this later.

Step 3: Create a data table & add data points.

Take all of your exports and add them to a clean Excel sheet in column B, creating a combined data table. Add to column A the month, week, or whatever you want the combined time frame to be in your ultimate report.

Put all your exports in a single data table.

Select the data and click “Format as Table.” And keep “My table has headers” checked.

Format as a Table

Step 4: Create a pivot table.

Now that you have all your data in table, time to merge it. Select any cell in your pivot table, then click Insert > PivotTable from the menu. Select where you want the pivot table, I usually just use a New Worksheet, and click OK.

Create PivotTable

Now, it’s super easy to manipulate the data, by dragging fields into Row Labels & Values.

Pivot Table sample

Step 5: Dance!

Oh yeah, and get to work analyzing.

Now you can quickly look at your unsampled data in a variety of ways just by rearranging the fields. Here’s the same data made into a Pivot Chart:

PivotChart

And as a Pivot Table with visits listed as a percent of all.

Pivot Table Visits as Percent of All

There’s plenty more you can do with Pivot Tables once you have the data pulled. I’d suggest the Mr Excel Pivot Tables playlist to learn more.

Bonus Tip:

Metrics like Visits and Pageviews are easy, you can just use sum of values. But if you want to get a Bounce Rate or Exit Rate, you can’t sum or average. Here’s why:

Month 1: 30 bounces / 100 visits = 30% bounce rate
Month 2: 40 bounces / 200 visits = 20% bounce rate
Incorrectly calculated average of Month 1 & Month 2 bounce rate: 25% bounce rate
Correctly calculated bounce rate: 70 bounces / 300 visits = 23% bounce rate

One great way to deal with this is the calculated field option. Within PivotTable Tools select Options > Fields, Items, & Sets > Insert Calculated Field.
In the dialogue, name the field, and create your formula.

Insert Calculated Field

Now you can view your calculated field in the Pivot Table:

Pivot Table with Calculated Field Bounce Rate

Happy Analyzing!

Seer-Analytics-CTA-2015