Explanation
You can edit NEXT Analytics script directly. Many people find this faster, easier and even a bit fun.
To get started, we will explain the following popular requirements:
- dimensions
- metrics
- change the numeric filters
- change the text filters
- change the numeric sort
The advantage of doing this with NEXT Scripting is that it gets re-applied after every data refresh, before the worksheet is updated. It can be automated, using Windows Task Scheduler, meaning no human needs to do anything with the Excel workbook after a data refresh. (Automation is a PRO feature).
This is a simple example of fetching dimensions and metrics. NEXT Analytics has a built-in engine that can do much more, but we don’t want to present too much information in one blog article because it would be over-whelming.
Follow the steps in this blog article — it’s easy, fun, and risk-free!
Pre-requisite Steps
This blog articles assumes you have installed the software and then:
- Click the Google Analytics (GA) tab
- Logged into to GA
- chosen a default View and Segment
- Create a new Excel Spreadsheet file
- Rename the first empty worksheet to be custom_actions
Visit this link, and get familiar with the dimensions and metrics that are available.
Seeing your changes
Make your changes by having the spreadsheet open in Excel.
Windows Users
In Windows Excel Addin, after you make a change, you can click “Refresh Spreadsheet” to see the effect. Please note that the date range for the query is controlled by the value you chose on the Excel Ribbon Bar when you click Date Range.
Mac Users
If you are on the Mac, then make the change in Excel, Save and Close the Workbook, then click Get Data in the Downloader Application. The date range for this query is control by the value you chose in the Downloader Application under “Date Range”.
Start with this Template
Start by copy/pasting the following script into your worksheet that you named custom_actions.
| Prompt,default,[GA_PROFILE_ID], |
| Prompt,default,[GA_SEGMENT], |
| prompt,default,[PERIOD],Past 1 Months |
| Prompt,add,[MAX_RESULTS],&max-results=1000000 |
| Prompt,add,[GA_DIMENSIONS],”&dimensions=ga:pagePath,ga:pageTitle” |
| Prompt,add,[GA_METRICS],”&metrics=ga:entrances,ga:exits” |
| Prompt,add,[GA_SORT], |
| Prompt,add,[GA_FILTER], |
| Prompt,add,[GA_WANT_VIEW_ID],False |
| Prompt,add,[GA_WANT_VIEW_NAME],False |
| Prompt,add,[GA_WANT_SEGMENT_ID],False |
| Prompt,add,[GA_WANT_SEGMENT_NAME],False |
| Prompt,add,[GA_WANT_SEGMENT_TEXT],False |
| Prompt,add,[GA_IGNORE_SECURITY_ERROR_403],False |
| Prompt,add,[GA_ALLVIEWS_FILTER], |
| GetGoogleAnalyticsData,,,[GA_PROFILE_ID],[PERIOD],GoogleData.csv,”[MAX_RESULTS][GA_DIMENSIONS][GA_METRICS][GA_SORT][GA_FILTER]”,,,,[GA_WANT_VIEW_ID],[GA_WANT_VIEW_NAME],[GA_WANT_SEGMENT_ID],[GA_WANT_SEGMENT_NAME],[GA_WANT_SEGMENT_TEXT],[GA_IGNORE_SECURITY_ERROR_403],”[GA_ALLVIEWS_FILTER]”,”[GA_SEGMENT]” |
| ImportDataFiles,GoogleData.csv,,0,1,utf-8,,yyyy-MM-dd,en-us,,”” |
| RemoveNumeric,Entrances,LessThan,2 |
| ;;;;KeepText,Page Title,Contains,utm_campaign=campaign_1 |
| Sort,Last,Descending |
| prompt,add,[DATA_SHEET_NAME],Sheet1 |
| prompt,add,[TOP_ROW],1 |
| prompt,add,[TOP_LEFT],1 |
| prompt,add,[BOTTOM_ROW],-1 |
| prompt,add,[BOTTOM_RIGHT],-1 |
| SaveInWorksheet2,current,[DATA_SHEET_NAME],Overwrite,visible,[TOP_ROW],[TOP_LEFT],unformatted,[BOTTOM_ROW],[BOTTOM_RIGHT],,WIDGET_NAME |
Change the Dimensions
How to Change the Google Analytics Dimensions
Find the row:
Prompt,add,[GA_DIMENSIONS],"&dimensions=ga:pagePath,ga:pageTitle"
Remove ga:pagePath,ga:pageTitle
Copy/paste up to seven of dimensions from Google’s List of Dimensions and Metrics
Hint, keep the double-quotes!
Change the Metrics
How to Change the Google Analytics Metrics
Find the row:
Prompt,add,[GA_METRICS],"&metrics=ga:entrances,ga:exits"
Remove ga:entrances,ga:exits
Copy/paste up to ten metrics from Google’s List of Dimensions and Metrics
Hint, keep the double-quotes!
Keep / Remove Rows by Numeric Values
How to remove rows based on their metric values
Find the row:
RemoveNumeric,Entrances,LessThan,2
Remove the word Entrances, and replace it with the name of the Metric you have chosen
Remove the 2 and replace it with a value that you only want to see values
Remove the word “LessThan” and change it for one of these choices:
LessThan LessThanOrEqualTo NotEqual Equal GreaterThanOrEqualTo GreaterThan
Keep / Remove Rows by Text Values
How to keep or remove rows based on values in the text
Find the row:
;;;;KeepText,Page Title,Contains,utm_campaign=campaign_1
Remove the ;;;;
Ignore KeepText
Change Page Title to be name of the dimension you added
Change Contains to one of the following
Change utm_campaign=campaign_1 to a text string that occurs in your data
StartsWith or NotStartsWith EndsWith or NotEndsWith Contains or NotContains ExactMatch or NotExactMatch
Sort by metric value
Find the row:
Sort,Last,Descending
Change the word Last to one of:
First Last
the name of a numeric column (aka metric name)
Change the word Descending to one of:
Ascending Descending
Sort by dimension value
Find the row:
Sort,Last,Descending
Change sort to SortTextColumn
Change last to the name of your dimension
Change descending to either
Ascending Descending
Add your choice of:
Alphabetic Numeric
For Example:
SortText,Page Title,Ascending,Alphabetic
Override the Date Range
Make your changes by having the spreadsheet open in Excel.
Find the row:
prompt,default,[PERIOD],Past 1 Months
Windows Users
This value is overriden by the value you chose on the Excel Ribbon Bar when you click Date Range.
To prevent the value in the script from being overridden, change default to add.
Mac Users
If you are on the Mac, date range for this query is control by the value you chose in the Downloader Application under “Date Range”.
To prevent the value in the script from being overridden, change default to add.