NEXT Analytics provides the ability to iteratively query for sub-periods over a date range, great for reducing sampling errors by minimizing the date range (and hence number of visit sessions to be processed) for each query. But the algorithm always starts from today. What if you want to query a period way in the past? Here’s an advanced technique using NEXT Analytics script that can help.
–
This approach uses two advanced capabilities of NEXT: iterative processing using uiSet commands, and; the ability to import multiple files with a single command. Start by making the query you want, for a single day using NEXT’s Custom Dates settings. Save the query to the workbook and open it in Excel. The _actions worksheet looks something like this.
The first step is to change the query to be for a generic date, and then to add uiSet commands to iterate for each of the dates desired. The uISet command sets values for the various prompt/variables listed on each row, and then runs the script below for each combination of values.
Because we need to keep the results of each query, we need to change the name of the file used to ‘catch’ the query results to include the date variable. Then change the ImportDataFiles command to load all of the files, using a wildcard (‘*’) in the file name in place of the date.
When we Refresh with NEXT, each query runs and writes to a file in the NEXT Analytics data directory.
And the results are loaded and appended together and written to the data worksheet. BEAUTIFUL!
Now, insert 365 rows and copy the uiSet row down until you get the ending date you want. Refresh with NEXT and save your results! Quick and easy – any date range you want.
Programming Tips
1. Use Formulas for AutoFill
The dates used MUST be in the form of TEXT strings, not Excel dates formatted to look ok. Text strings do not auto-fill properly when you copy a uiSet row down 300 rows, so to make your life easier, use an Excel formula that will auto-fill the way we want. One way is to use the formula
=TEXT(DATEVALUE(cell above)+1,”yyyy-mm-dd”)
2. Force the column Type with NEXT
If NEXT auto-detects the Date column as a date, it may insert a text column to the left of it with a row number (a whole bunch of one’s in this case). If you want the Date column to actually be a text string instead of an Excel date, then go to the Columns tab when you make your original query and force the types of the columns. That will insert code into the script command to force the type, and the data will be loaded the way you want.
ImportDataFiles,IterativeQuery*.csv,"nxRowLabel_Date,nxNumeric_Visits",…
Google Analytics now classifies visits from hundreds of social networks for you, and with the API, we have full access to the details. Do you know how much your site traffic depends on these social channels? In this article, we will build a report with NEXT Analytics that shows the top social channels as a percentage of the total daily visits.
Many of our customers manage multiple websites, whether they are different product or service websites, support forums, or mobile-specific variants. With NEXT Analytics, it is easy to combine multiple sites into a single report, but you can go even further; you can compare multiple segments for each of your sites.
I saw a tweet today about an old
