The term “data-driven” is widely spread around the web, but how many people are really data-driven when it comes to their marketing approach?
There are a bunch of challenges on this approach but thank God we have tools like Google Analytics and Google sheets.
What’s the importance of big data analytics?
“Big data is a way to preserve context that is missing in the refined structured data stores — this means a balance between intentionally “dirty” data and data cleaned from unnecessary digital exhaust, sampling or no sampling. A capability to combine multiple data sources creates new expectations for consistent quality; for example, to accurately account for differences in granularity, the velocity of changes, lifespan, perishability and dependencies of participating datasets.
Convergence of social, mobile, cloud and big data technologies presents new requirements — getting the right information to the consumer quickly, ensuring reliability of external data you don’t have control over, validating the relationships among data elements, looking for data synergies and gaps, creating provenance of the data you provide to others, spotting skewed and biased data.” –
Svetlana Sicular.
This article is my latest addition to our step by step guides (make sure to check our
Guest Blogging guide). This time, you’ll learn how to integrate Google Analytics with Google Sheets in order to gather all the segments of big data for your website, mobile app or
eCommerce. It’s a pretty easy process that everyone should be in the position to follow.
Are you ready to unlock big data?
- Step 1: Define your Transactional Data Model
- Step 2: Map your model to GA Options
- Step 3: Implement / Code your data model
- Step 4: Test that data are captured properly
- Step 5: Create Google Analytics Dashboard
- Step 6: Beyond Dashboarding
- Step 7: Integrating GA data via Google Apps
- Step 8: Creating Dynamic Google Spreadsheets
- Step 9: Scheduling
- Step 10: Pivots and visualizations
Step 1: Define your Transactional Data Model
To begin with, you need to spend some time in analysing (aka technical design) a data model that encapsulates your users’ behaviour while performing various events or transactions. Commonly, it should include all the possible information you would need to recall at the final stage taking under consideration the potential actions a user performs in your web or mobile app. Let’s assume that a user performs a registration, login and a transaction. In order to segment your dataset properly, you need to separate your transactions into three main categories:
- The Main info.
- Session related info (that will be the same across session).
- Transaction related info (that will characterize each transaction).
All the information above is not single-dimensional, but it requires additional analysis. For instance, you may offer three registration options (email, via facebook and via google+). The same goes for the login. Regarding the transactions, most likely you will have multiple transactions, such as submitting an order, sharing to social media and sending an invitation.
Note! It’s clear that each combination has different info to capture or null when no info is mapped.
Step 2: Map your model to Google Analytics Options
Using Google Analytics for mobile or web apps, opens up a full set of features related to capturing and presenting your data. The main choices that are used here are:
- Event Category
- Event Action and
- Custom Dimensions
Custom dimensions are indexed from 1 to 20 – as a maximum of 20 dimensions can be created, and they require a small amount of technical work (see step 3). You may need some help from a technical marketer or developer. But, trust me, it totally worths the time for the higher quality insights, actions and business impact insights you’ll be able to deliver.
If you are a content site, this means you have the ability to slice and dice your data by author names, content type, subscribers and free-loaders, commentators and non-commentators, and so much more to bring a new layer of insights.
If you are an eCommerce site, among many other things you can track customers who purchase more than once, all kinds of product detail related to cart and checkout pages, your internal cross-selling and up-selling campaigns, multiple orders by the same customer, and like a million other things.
After reading the info provided above, your data model should be mapped as below.
Step 3: Implement / Code your data model
Now that the model is clear, you need to configure it, code and start testing.
Creating your custom dimensions (Configuring)
Before you can send any custom dimension to Google Analytics, they must first be defined in a Google Analytics property. Each Google Analytics property has 20 available indices for custom dimensions, and another 20 indices available for custom metrics.
When you define a custom dimension or metric, you point out its name and other configuration values at a particular index. Custom Dimensions have the following configuration values:
- Name – the name of the custom dimension as it will appear in your reports.
- Scope – specifies to which data the custom dimension will be applied. Hit is the one to use here.
- Active – whether the custom dimension or metric value will be processed. Inactive custom dimensions may still appear in reporting, but their values will not be processed.
Coding Snippets
Custom dimension and metric values are transmitted to Google Analytics at collection time as a pair of index and value parameters. The index parameter corresponds to the index of the custom dimension defined during
Configuration phase (see above)
In contrast to other types of data, custom dimensions and metrics are sent to Google Analytics as parameters attached to other hits, like pageviews, events, or eCommerce transactions. As such, custom dimension or metric values need to be set well before a tracking call is made to make sure for that value to be sent to Google Analytics.
For example, to set a custom dimension value, your code might look like this:
ga(‘create’, ‘UA-XXXX-Y’, ‘auto’);
// Set value for custom dimension at index 1.
ga(‘set’, ‘dimension1’, ‘Level 1’);
// Send the custom dimension value with a pageview hit.
ga(‘send’, ‘pageview’);
This is another example from an Android Mobile app submitting data using the data model defined above.
private static void sendEvent(@NonNull Context ctx,
@NonNull @ContentEventCategory String category,
@NonNull String action,
@NonNull String UserID,
@NonNull String AppID,
@NonNull String item,
@NonNull String Quantity,
@NonNull date Date,
@NonNull int Discount) {
if (tracker == null) {
initTracker(ctx);
}
setSessionSpecific(ctx);
HitBuilders.EventBuilder hitBuilderEvent = new HitBuilders.EventBuilder();
hitBuilderEvent.setCategory(category);
hitBuilderEvent.setAction(action);
hitBuilderEvent.setCustomDimension(UserID, UserID);
hitBuilderEvent.setCustomDimension(AppID, AppID);
hitBuilderEvent.setCustomDimension(item, item);
hitBuilderEvent.setCustomDimension(Quantity, Quantity);
hitBuilderEvent.setCustomDimension(Date, Date);
hitBuilderEvent.setCustomDimension(Discount, Discount);
hitBuilderEvent.setCustomDimension(SESSION_SPEC_APP_TOKEN, ApplicationConfiguration.get(ctx).getAppToken());
sendEvent(ctx,
UserID,
AppID,
item,
Quantity,
Date,
Discount);
} catch (Exception e) {
DebugUtils.throwIllegalStateInCaseOfDebug(e);
}
}
Step 4: Test that data are captured properly.
Sometimes, you need to explore your data. Using the typical Google Analytics screens will be tough, therefore Google offers a set of tools like
query explorer to assist you. Be aware data may come few minutes and sometimes hours after their submission.
Tips: When using dimensions in a data request, be aware of the following constraints:
- You can supply a maximum of 7 dimensions in any query.
- You can not send a query composed only of dimensions: you must combine any requested dimensions with at least one metric.
- Only certain dimensions can be queried in the same query.
Step 5: Create Google Analytics Dashboard
Every view in Google Analytics comes with a default “My Dashboard.” While the default Dashboard may suffice, the real usefulness of Dashboards lies in your ability to create and customize them the way you want.
Dashboards contain one or more widgets (up to 12 per Dashboard) that give you an overview of the dimensions and metrics you care about most. The steps outlined in this article will teach you how to create and customize a new Dashboard. It’s not that friendly and it has a lot of limitations especially when you want to use Custom Dimensions.
Step 6: Beyond Dashboarding
In order to see your data in full extension, you need to go into the menu item:
Behavior > events > Overview
to get an overview of your events where event category and event action will dominate but giving you the ability to drill down. While drilling down you will be able to select your dimensions. Here they are hidden behind the term Secondary dimension and you will find all of them alphabetically.
Still, not your most user-friendly option. Especially, when you are addressing a marketer and not an engineer.
Step 7: Integrating Google Analytics data via Google Apps
The easiest step ever. You have nothing to do here as Google provides a full integration to Google Analytics via Google Apps (and specifically Google Spreadsheets). Just follow bullets 1 to 3 in the step below.
Step 8: Creating Dynamic Google Spreadsheets
The Google Analytics spreadsheet add-on brings you the power of the Google Analytics API combined with the power of data manipulation in Google Spreadsheets.
- Create a new spreadsheet
- From the menu bar choose: Add-ons > Get Add-ons…
- Find the Google Analytics Add-on from the add-ons gallery and select it.
- From the add-on description page, click the “+” in the top right corner to add this add-on to your spreadsheet.
- A dialog should pop up requesting your permission for the add-on to access your Google Analytics data. Click “Accept”.
- The add-on is now installed. A “Google Analytics” submenu should now appear in the Add-ons menu.
- Go to add-ons > create a new report
- Set a title.
- Select the account, property and view of your google analytics account.
- Select as metrics the two below (you can always change those later)
- Total events
- Unique events
- Select as dimensions the below:
- Event Category.
- Event Action.
- Custom Dimension XX (This is a placeholder and will have to change before executing the report).
- Press the “Create Report Button”
The result will be something like that.
You have to replace the DimensionXX with few dimensions separated by spaces. Keep this as a reference (it will never run and it can be deleted anytime later) and copy a new column in order to start customizing.
Then you can just add a few elements to customize your report. Such as:
- Start Date. Select using the date picker.
- End Date. Select using the date picker and let the system to define the date format.
- Metrics. Leave the default values you selected before. You can not send a query composed only of dimensions: you must combine any requested dimensions with at least one metric.
- Dimensions. Replace XX with few dimensions using the number together with the word “dimension” eg. ga:dimension1. Remember to put spaces in between and NO space at the end. You can supply a maximum of 7 dimensions in any query.
- Filters. It’s an optional step but you can further personalize by adding a filter you wish. Filters are separated by semicolon (;) and == means equality.
- Sampling Level. Keep the word “HIGHER_PRECISION” as by default it will perform SAMPLING.
- Max Results. Set to 10000 otherwise it will stop at first 1000.
When executed (by selecting Add-ons > Google Analytics > Run Reports), the system will create a new tab (spreadsheet) for each column (in other words for each report), assuming that it will be succeeded.
Remember: The First Report (called in our example “Sample report”) will never finish successfully due to the erroneous name of dimension named “ga:dimensionXX”
Step 9: Scheduling
You can enable your reports to run automatically by selecting:
“Add-ons” > “Google Analytics” > “Schedule Reports” from the menu bar. This opens a report scheduling dialog where you can turn scheduling on and off, and set how frequently your report will run
google analytics to google sheets.
To turn scheduling on, check the box labelled “Enable reports to run automatically.” Once scheduling is enabled you can use the select dropdown to control the time and frequency. Reports can be scheduled to run every hour, day, week, or month.
To turn scheduling off, uncheck the box labelled “Enable reports to run automatically.”
Step 10: Pivots and visualizations
The report will look like that:
It includes few characteristics that will help you create your data mining platform on top. Those are:
- Columns have always the same name.
- Dynamic Data always start on row 15.
- All metrics appear.
By utilizing few typical tools of a spreadsheet such as Diagrams and Pivot you are able to deliver dashboards and tables for audiences of different level eg. From CEO to data scientists.
Examples below:
Conclusion
You could create a simple mapping table in a specific spreadsheet with all custom dimensions and their business names eg. Dimension1 -> User, Dimension2 → App etc and using a simple VLOOKUP function you may replace the technical names (of dimensions) with their business names. How strong is that?
Good luck!
View Comments
I can't help but sharing that great article! Very good job! :)
Hey Nick,
Thanks for your kind words! Are you into more technical content?
Yeah Apostle, definitely! I totally agree that you got to track down almost everything. Even small behavioral habits could lead to big decisions on your next growth strategy plan, don't you think?
Thanks for sharing great information on google analytics which is part of Data Analytics