You’re definitely searching for a way to automate your coverage efforts, if you have ever felt the need to decrease the time spent on web analytics, then cutting out a huge section of the manual monkey work. For people who use Google Analytics, there are tools which permit you to question the Analytics APIs and to build automatic reports in Excel using a setup. (Supermetrics Data Grabber is most likely the easiest tool of this type.)
Are you afraid of Office licenses, but love Google Drive sharing options? Then you’ll be glad to know there is a absolutely free add-on for Google Sheets which will do of the work for you, leaving you to the burden of organizing information in private dashboards or whatever you require.
This tool helped me a lot in enhancing and automating my search engine optimization reporting actions.
I will explain here to install it and use its essential functions in order to save a little period that is working.
Installation
- Log into Google Drive together with the account that you use for Google Analytics.
- Head into this page and click on the “Free” button. (You will be redirected to Google Sheets.)
- Enable the usual permissions along with the add-on will be installed.
Done. You are going to have the ability to get your brand-new tool in your “Add-ons” tag in the menu.
Create your first report
Let’s start getting some information. Clicking on “Create new report,” you’ll open the simple interface of this add-on.
The shape is easy and self-explanatory. Simply name your document, select your Analytics profile, also place size and the metrics that you wish to track. Don’t be concerned if it appears restricted; in the next step, you will have the ability to customize all you need (e.g., date ranges and blockers). Within this simple case, just select Sessions in the “Metrics” field.
Click on “Create report” along with the next table will appear:
(In a while, we will find out how to work with and improve it, as it’s the heart of this Google Sheets Analytics add-on.) For the time being, simply click “Run reports” in the “Add-ons” tag: The application will question the Analytics APIs to acquire the information you’ve requested, and also a pop-up will tell you if the request was successful. In a brand new sheet, you will realize the actual report, which in this case indicates the number of sessions enrolled within the default date range (the last 7 days).
Want to make a report when keeping the other one? Insert a new column where required. (The reports will be generated in two separate sheets). This is significantly faster than clicking on “Generate new report” and employing the GUI every time!
Improve your report
Time to return to this “Report Configuration” sheet. As you saw before, there are more areas than those contained in the GUI. Every field, naturally, can be edited or filled .
Some of them might require an explanation of this syntax. Let’s take a look at exactly what I mean.
View (profile) IDs
Here, it is possible to define the Analytics perspective you wish to question. That “IDs” is rather misleading: You can select only one view here. Then you’ll need to add columns, in the event you will need to explore at least two views. To find your perspective ID, simply use the GUI of this add-on; or visit Analytics and click on “Administration,” followed by “View settings.”
Start and end dates
You may either write dates manually (it’s OK if you need to collect information within a specific time range) or utilize relative dates using formulations such as now and yesterday. Let’s say you want to produce a “last-week-vs. -this-week” report. As indicated in this nice Google’s walkthrough, an elegant way to automate calculations is to create 2 columns (i.e., reports) called, as an Example, “this week” and “last week” using all the formulas set such as that:
Syntax —mm/dd/yyyy or relative dates
Last N days
Leave it blank if you use the Start Date and End Date fields.
Metrics and dimensions
Here you are able to tell the add-on that information that you wish to grab out of Google Analytics. You can set 7 dimensions for each account and up to ten metrics out. Should you require help with finding valid mixtures between dimensions and metrics, visit this webpage.
Syntax —ga:sessions, ga:Users... and also ga:nation, ga:bounces...
Form
This field allows you to alter your report’s tables are organized. You can select:
- How to group information (by priority);
- which order (ascending or descending) to utilize.
An example will make this clearer. Suppose you composed ga:quests in the “Metrics” field and ga:pagePath, ga:nation from the “Dimensions” field: that the report will show you, for each page, the list of countries which created at least one session, ordered alphabetically.
Now suppose you need to group this information by state (in other words that you want to know, for each nation, the listing of webpages that created at least one session): you simply should write ga:nation in the “Type” field.
Let’s say you want to improve this table, revealing for each nation, sessions in sequence. Simply add -ga:sessions along with the minus sign is going to do the magic.
Syntax —ga:nation, -ga:sessions...
Filters
Let’s continue with the exact same case and say you wish to restrict information visualization to a particular nation (e.g. Italy). This is exactly what the “Filters” field is used for: fill it using the education ga:nation==Italy and that’s it. As you see, I used exactly the operator == to question the Analytics API using the right syntax. In order to find out that operators you may use to filter dimensions and metrics properly, see this list. Of course you can also combine filters together with all the OR along with AND operators, represented respectively as a comma (,) plus a semi-colon (;-RRB-.
Syntax —ga:nation==Italy; ga:moderate==organic...
Segment
The add-on lets you to apply sections . You can either remember segments defined in Google Analytics or make a new section. I’m not familiar with dynamic sections — not tried them honestly — so I’ll just point out this manual for people who wish to take a deeper look into this topic. To use an existing section you have to understand its ID: the easiest way to find it’s through Google’s APIs Explorer, which will give you every detail concerning the sections set in your Analytics profiles.
Sampling Level
If you have to take care of a large amount of information (more than 500k sessions within the chosen time variety) your Analytics reports will likely be sampled. Employing this field is like correcting the sampling slider in the usual Google Analytics panel: make it blank if the sample dimensions satisfies your requirements, write FASTER (slider to the left) for a faster query using a bigger sample, HIGHER_PRECISION (slider to the right) for a slower query using a larger sample. If you’d like additional information, here is Google’s official document concerning how sampling works.
Start index and maximum results
Since Google reports, “The Analytics Core Reporting API returns a maximum of 10,000 rows per petition.” The default value for the field “Max Results,” however, is 1,000: This means that, if you would like to recover more information (i.e. rows), then you’ll need to define the desired number here. Let’s say then the total results of your question exceed 10,000, and you also want to see also the rows which go beyond this limit: The subject “Start Index” comes in handy as it allows you to choose the row (i.e., index) where to begin displaying the information.
A real-life example
What I wish to share with you is that a use case of this Analytics add-on. Some time ago I chose to improve among my search engine optimization reports developing a dash in Google Sheets. I needed to figure out how to collect the next KPIs:
- Natural sessions by month and by device category;
- natural contributes by month and by device class;
- natural sessions Vs. all sessions through the month.
A brief caution before exploring the dashboard: fixed dates were utilized by me here to save information and so as to spot the trend of my enterprise, but you could find value. It’s up to you.
Let’s take a look at the report configuration sheet.
As follows, the first few queries have been set up.
- Start Date and End Date (01/01/2015 and 12/31/2015): Everything I want here is the entire year. I’ll pick month later as a dimension to have the ability to examine the monthly fad, and I’ll create a brand-new report once the new year starts (simply copy-pasting the old one and altering the dates). This way I will always refer to this reports that are previous to detect year-over-year trends. If the current year is not over yet doesn’t matter: future months will receive zero results and update themselves if it’s time. Be careful: in sampling problems, Selecting a time range may result. To reduce or avoid them you can divide your query from 12 — one a month — then aggregate numbers in another sheet.
- Collars and Dimensions (ga:sessions, ga:goal1Completions, ga:goal2Completions along with ga:month): The specified dimensions tell Google I wish to divide the essential descriptions of my reports by month. In order to utilize the metric goalXXCompletions you obviously have to replace XX with your goal’s ID (you can readily find it from Google Analytics).
- Type (ga:month): This is the way I need information to be shown, “grouped” through the month. As you will see it only helps me in the arrangement of information.
- Filters (ga:moderate==natural;ga:deviceCategory==desktop/mobile/tablet): This way, the inquiries will get only natural benefits in response, divide by apparatus. Why don’t you only building one question using deviceCategory place as a dimension? Because — you’ll see — formulations in the dashboard would be more complicated, because I’d have to remember data from different sheets using some kind of lookup based on two standards (month and apparatus).
As it simply asks Analytics for the entire sessions by 17, the fourth question is quite simple.
Here is a screenshot of this report, which shows the loudness of the traffic that is organic that is desktop by month and the target completions.
Establishing the Search Engine Optimization dash
Now that the information is present, let’s jump into the fun part. It’s simply an issue of functions and calculations. A new sheet opens.
Organic sessions
(Click on the picture for a better view.)
The “Total” row along with the deltas below will be the consequence of rather simple calculations based on the rows above. What is well worth looking at is cell B5. The formulation is spread across the desk in the “Desktop,”“Mobile” and “Tablet” rows.
=VLOOKUP(B$2, OrganicDesktop! $A$16:$D$27, 2, FALSE)
The VLOOKUP scans the specified range of this “OrganicDesktop” sheet trying to find the value of this cell B2 (the range of this month, in this case, 1) and returning the corresponding value in the second column of this table (the number of sessions).
Mention the value to look for not and with a cell directly with the number? It’s a well-known trick that here allows me to drag the formulation applying it to every cell of this row, thanks to this “dynamic” reference to this row 2 (where I wrote the quantities of the months).
The rows “Mobile” and “Tablet” are populated in the exact same manner, replacing the referenced sheet respectively using “OrganicMobile” and “OrganicTablet”.
The pie graph on the right displays the complete natural sessions that are average . To Locate the percentage value of their traffic, for example, I applied this very simple formula:
=SUM($B5:$M5)/ $SUM($B$8:$M$8)
Organic contributes
The second area of the dashboard is a copy of the first one. The sole difference is that the cells of these rows “Desktop,”“Mobile” and “Tablet” amount up two VLOOKUPs, because I have to report here the complete number of prospects (target 1 completions + target 2 completions).
This is the formulation of the cell B33:
=VLOOKUP(B$2, OrganicDesktop! $A$16:$D$27, 3, FALSE)+VLOOKUP(B$2, OrganicDesktop! $A$16:$D$27, 4, FALSE)
Organic sessions vs. All sessions
At length, a very straightforward one. The row “Free visitors” uses values in the row “Total” of this “Organic sessions” table, even whereas the row “All visitors” hunts for values in the “All” sheet through a VLOOKUP formula:
=VLOOKUP(B$2, All! $A$16:$B$27, 2, FALSE)
Conclusion
This dash is a simple example of what you can do with the help of this Google Analytics. When you understand how to recover data properly in the Analytics APIs, you’re free to organize them as you like in a brand new sheet (or in a new file, with all the IMPORTRANGE purpose), in order to set up a totally automatic and self-updating panel in accordance with your needs.
1 last thing: Remember, you may also schedule reports to update them without pushing a button.
Head to Add-ons > Google Analytics in the menu above to get exactly what you need.
Do you utilize a process? I’d really enjoy seeing your thoughts under the comments.
Subscribe to The Moz Top 10, a semimonthly mailer updating you about the top ten hottest pieces of SEO information, hints, and rad links uncovered from the Moz team. Consider it as your exclusive digest of stuff that you don’t have enough time to hunt down but want to read!
source http://wz2s.net/here-is-how-to-automate-google-analytics-reporting-with-google-sheets-digital-marketing-seo-nyc/
No comments:
Post a Comment