Do you get a lot of traffic to your website? Maybe more than 500,000 sessions per month, or even per week? If you answered yes and you’re using the free version of GA, you know it’s a pain to run reports with any advanced segment or secondary dimension applied.
Here’s a way to automate this reporting using the Google Sheets GA add-on. If you haven’t used Google Sheets GA add-on before, please refer to this introduction.
In our scenario I’m assuming we’re having over 200,000 sessions per day, so we need to export the data on a daily basis and combine it for the month. Here’s a link to the Google Sheets template I’m using that you can save in your account by clicking File -> Make a copy.
Here’s how to populate the template, you need to fill-in the yellow fields only:
- In the yellow fields in column A put your dates. In my example these are the individual dates of January.
- The View ID field should be the ID of your GA view. If you don’t know it, go to Google Analytics -> Admin -> View Settings and get your View ID. Don’t forget to add ga: in front of the number.
3. Metrics & Dimensions – you should list your metrics and dimensions in these fields, formatted in such a way that the GA API can understand them. You can refer to the list of popular dimensions and metrics in the right section or click the links below the fields to select the proper dimensions and metrics. You can pull up to 7 dimensions in one report.
4. Segment – I’m interested in traffic from mobile devices, so in the Segment field I’m building my segment. For a quick reference what’s possible with segments, check out the documentation using the link below the field.
5. Filters & Sort – if you need to use a filter you can do so here, and also sort the data.
6. Run the report – select Add-ons -> Google Analytics -> Run reports. In order to run the report you need to login to Google Sheets with the same email, that’s added as user in Google Analytics. You’ll get an upgrade notice the first time you run the report.
Now we have all the data we need. If you’re interested in summary metrics, you can see the results in tab Summary. If you need to get a list of the dimensions, check out the Collect tab. You can export it from there, clean up the headers, do a pivot table in Excel or Google Sheets and get the report you need.
Good luck!
