Recreating the Google Analytics User Explorer Report

With the [undocumented] addition of the Client ID dimension to the Google Analytics API, you can now use the Analytics Edge Core Add-in and Google Analytics Pro connector to recreate the User Explorer report in Google Analytics.

Start by downloading the Client ID data for the time period of interest. Note that the next step will iterate and make a query for each client ID value, so decide which Client ID’s are of interest — maybe the sessions that included specific goals or transactions, or those with more than a certain number of pageviews. Write the data to its own worksheet. In this example, I used a macro, but you could use a Quick Query as well.

Add a second target worksheet for the user activity, then create a new macro, starting with a Repeat Macro command. Select your client ID data worksheet as the source, and select the option to clear the target worksheet before the first iteration.

Make a User Activity query, entering [Client ID] for the client Id field. Be sure to include the square brackets and ensure the capitalization matches the source spreadsheet column name.

If you use a lot of custom dimensions, the User Activity report make produce multiple rows per client id, so you might want to filter for the specific dimension index and/or activity type you want in your report.

End your macro with an Append to Worksheet command, so each iteration will append to the bottom of the target worksheet.

Enjoy your data — another Analytics Edge macro can be used to further analyse the detail for insights unavailable through standard reports!