Layout & Formulas – Pulse Google Analytics
Get the Template file here.
Start off with just one tab and label it Home.
Then from the top navigation go to Add-Ons -> Google Analytics -> Create New Report.
This will open up the Report Sidebar. I’ll explain each numbered item below the image.
1. Put in your report name. If you are tracking only one site, make this the time frame of the report. For example, Last 90 Days.
2. Choose the Account for this report.
3 & 4: These are advanced features that most people won’t need. Just leave them to the default.
5. Choose your Metrics. You can search from a dropdown list, or search by starting typing any metric name and see what is available, or click “Metrics Reference” to see a list of all available metrics. A good Metric to get started with is ga:users.
6. Choose your Dimensions. This is optional. You have the same search options as for Metrics. A good first Dimension to try out is ga:source.
7. Click Create Report and you are done. This will create a Report Configuration tab if you don’t already have one, create a new report column on that tab, and create a new tab that shows the report.
Create Additional Reports
You can create additional reports by using the sidebar tool again or by simply copying and pasting from one column to another in the Report Configuration tab.
If you copy and paste, make sure to change the Report Name. This is how Sheets knows to create a new report.
Also change the date settings. You should use either Start Date & End Date, or use Last N Days.
Schedule reports from the top navigation.
This opens up the Reports Scheduler. I recommend not doing more than Every Day.
Next we need to decide what data to pull to the Home tab.
In the template I show data for Users, New Users, and Session Duration based on Recency. Those are all directly pulling from the individual report pages.
For Users By Page Visit, it’s time to get creative.
Calling a single data point
Here is the formula for the Last 30 Days:
=IFERROR(vlookup($A17,’RBU Last 30′!$D$16:$E$1000,2,FALSE))
Let’s break it down.
IFERROR makes the formula return a blank space if there is no data available.
$A17 sets the search key to whatever is in the far left column. In this case, it is a page. I want to see how many people are going to that page over time.
‘RBU Last 30’!$D$16:$E$1000 tells Sheets to look in that area for the search key, and the 2 tells Sheets to return the 2nd value after it finds that search key.
If you want to follow my example exactly, make sure that ga:pagePath is in Column D and ga:users is in Column E.
Calling multiple data points
The above approach works great if you want to find a single data point. But if you want to sum data points, we need a new formula.
=SUMIF(‘RBU Last 90′!$B:$B,$A23,’RBU Last 90’!$E:$E)
SUMIF tells Sheets to add together any values it finds.
‘RBU Last 90’!$B:$B tells Sheets to look in this range.
$A23 tells sheets to look for this value.
‘RBU Last 90’!$E:$E tells sheets to return this value for any row in E where the value in A23 is present.
Now we can ask questions like “Show me all users with the Source is Facebook”.