Google Apps Developer Blog: Analytics reporting with Google Apps Script at the UK Cabinet Office

Analytics reporting with Google Apps Script at the UK Cabinet Office

December 3, 2012

Editor’s Note: Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. -- Arun NagarajanRecently, when we were preparing the launch of GOV.UK, my team was tasked with creating a series of high-level metrics reports which could be quickly compiled and presented to managers without technical or analytical backgrounds. These reports would be sent daily to ministers and senior civil servants of several government departments, with the data customised for each department.We decided to use Adobe InDesign to manage the visual appearance of the reports. InDesign’s data-merge functionality, which can automatically import external data into the layout, made it easy to create custom departmental reports. The challenge was to automate the data collection using the Google Analytics API, then organize the data in an appropriate format for InDesign’s importer.In a previous post on this blog, Nick Mihailovski introduced a tool which allows automation of Google Analytics Reporting using Google Apps Script. This seemed an ideal solution because the team only had basic developer knowledge, much of the data we needed was not accessible from the Google Analytics UI, and some of the data required specific formatting prior to being exported.We started by building the core reports in a Google spreadsheet that pulls in all of the required raw data. Because we wanted to create daily reports, the start and end dates for our queries referenced a cell which defaulted to yesterday’s date [=(TODAY())-1].

These queries were dynamically fed into the Google Analytics API through Apps Script:// All variables read from each of the “query” cells var optArgs = { 'dimensions': dimensions, 'sort': sort 'segment': segment 'filters': filters, 'start-index': '1', 'max-results': '250' }; // Make a request to the API. var results = Analytics.Data.Ga.get( tableId, // Table id (format ga:xxxxxx). startDate, // Start-date (format yyyy-MM-dd). endDate, // End-date (format yyyy-MM-dd). endDate, // Comma seperated list of metrics. optArgs); Next, we created additional worksheets that referenced the raw data so that we could apply the first stage of formatting. This is where storing the data in a spreadsheet really helps, as data formatting is not really possible in the Google Analytics UI.For example, the final report had a 47-character limit for page titles, so we restricted the cells in the spreadsheet to 44 characters and automatically truncated long URLs by appending “...”.

Once the initial formatting was complete, we used formulas to copy the data into a summary sheet specially laid out so it could be exported as a CSV file that merges seamlessly into InDesign.

Below is an example of how a report looks on publication. Nearly everything on the page was extracted from the API tool, including the department name and the day number. Because most of the data was automated, it required minimal effort on our part to assemble these reports each morning.

We discovered that an added bonus of pulling data into a Google spreadsheet was that it also allowed us to publish the data to a Google site. This helped us display data to stakeholders without adding lots of users to our Google Analytics account.

The tools let us present Google Analytics data in deeper, more creative ways. That’s really important as we share information with more and more non-technical people, whether they’re inside GDS or beyond.

Ashraf Chohan

Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. GDS’s role is to deliver digital transformation of government services. Ashraf is a Product Analyst for GOV.UK, a new site for public services and information.

5 comments :

Igor Griffiths said...: Well Hello Ashraf

Thanks for sharing in detail how you accomplished this, the time taken to generate statistical reports is the one reason why I all too often skip this task, I am sure many others feel and do the same.

Will have to explore automating my statistic generation further, thanks

Igor Griffiths; December 5, 2012 at 2:56 AM
Neil Krishnan said...: Amazing stuff. I've gotten the data using GA magic script into docs - any more info on the next step of transferring to InDesign?; December 6, 2012 at 12:23 PM
Ashraf Chohan said...: Hi Neil

The data from Google Analytics was used in two ways: first, using InDesign's 'dataMerge' feature, and second, using manual graphic creation.

For any textual or numeric items, including data specific to each department, we used InDesign's 'dataMerge' feature to link to the CSV containing the relevant data to the InDesign file. Once linked, the columns in the source file were available as variables to be inserted into existing text fields in the design. As a final step, the data was 'merged' in, creating a new sheet for each row of data contained in the CSV. The variables applied to the text fields have standard formatting options, so can be styled in advance with paragraph styles or other design tweaks. This process worked well to create the several custom reports from one source file.

Two graphics could not be automated with 'dataMerge' - these were a stacked column graph of daily visits, and a graphic comparing desktop to mobile usage. The column graph was created and styled in Adobe Illustrator, using the 'Column Graph Tool', with the source data updated each day to create a new graphic. The desktop / mobile comparison was a simpler design, which only required resizing a rectangle object directly in InDesign to reflect the new split.

Hope that helps; December 7, 2012 at 4:16 AM
Chris Ryland said...: Ashraf--

Note that you could link the spreadsheet data directly into InDesign, and format it, with our DocsFlow product.

For full details, visit

http://emsoftware.com/products/docsflow/,

and we'd love to get your feedback on how it does or doesn't work for you.

Thanks.
--Chris Ryland, Em Software; December 12, 2012 at 2:32 PM
Isabel Olmos said...: Great article!
Thank you for sharing it! I have some question about the configuration of the Dashboard in Google Site:
- Is there any way to change the date of analysis in Google site instead of Drive? Or it's neccesary change the time period in Drive?
- On the other hand, there is some way to change the names that Google Analytics offers by the default? I mean, for example, if I want to show a graph of Traffic Sources by medium, that it indicates "Direct Traffic" instead of (none)...it's possible?
I do not know if I'm explaining well... Thank you very much!; December 17, 2012 at 9:37 AM

Apps Developer Blog

Analytics reporting with Google Apps Script at the UK Cabinet Office

5 comments :

Labels

Archive

Feed

Company-wide

Products

Developers