Pulling and manipulating data into Excel from ComScore Analytics (Dax)

I used Comscore Analytics (DAX) for about 9 months at the BBC – it is an amazing tool but the interface ‘lacks’ anything that could be described as sexiness – I am sure its going to be improved soon but whilst there I built dashboards in Excel that pulled in dynamic data…

This simple tutorial is a how I got started and but key is to leave it as “fixable” as I can and rather than lie on thousands of lines of code, literally a few.

Requirements

1. Access to Dax
2. Excel
3. Dax plugin installed.

This isn’t a tutorial on using Dax – I can’t write one of those in a blog post.

1. Create a starter template – this is very useful as typically I want to do the same sort of thing time and time again, I usually start with the following

– Title of the report

– Name of time range (useful for reporting)

– Time Frame start

– Time Frame End

Refresh all pivot tables button

I would also include a cell which reports the day of weeks of both the start and end, this is more useful when comparing two date ranges.

Frequently I also include a comparison period, often (such as in the case I am currently working on) some of the values are automatically populated. (i.e. the start date – 7 if I am comparing the previous week)….

The starter template can also be expanded to include fields that populate parameters – such as a variable, page name or some other key piece of data – but generally a date was all the entry that was required – other key elements were hard coded in.

Create a new tab which I always call “Source” and colour it red, the reason for it is that other than hiding it, it’s the easiest way to flag it as being  different, I then pull the data I want using DAX, it pulls in all the data from a report builder item, I set the date as being pulled in dynamically – it will look something like =sheet!cell which in my case always looks like “=S!D6” to “=S!D7”. Even if I usually want “yesterday” or something which can be done within the report builder as sometimes I will want to override it, date calculations are quite easy to do in Excel – so its as easy to build this into it, make sure your report builder doesn’t output totals, “uncheck the box already checked” otherwise it will break the next step.

The data that is pulled in using DAX usually looks “messy” so turn it into a pivot table, one thing I didn’t mention

Select your data and click Insert Pivot, change the data range to be far “longer” if there is a chance that your data will be longer than it is – I usually insert an extra 9 to be safe, if your familiar with pivot tables the next section is pretty easy.

Drag the columns and rows how you want the data and build up exactly what you want.

Ok – so at this point you can play with the data in a pivot table, one thing I do like to do however is to use colour (for highlighting data anomilities), chart it and generally manipulate it – unfortunately pivot tables lose all their formatting afterwards – so as often as not I use a hack which just pulls out the data somewhere else, where I can use cell conditional formatting and other tricks – generally it works really well!

So one example is search click through rates, I can list the top search terms and show the ones where the ct is poor – simply by highlighting them in red, allowing editorial to fix them rapidly,

One issue I have found is that if you have multiple Row labels (allowing you to drill into them) on refresh they won’t stay closed, so either avoid them OR use a bit of script to close them.

Scripts

If you have multiple pivot tables in your doc, including a button that refreshes all the tables at once is possibly the easiest thing I have ever written

Sub RefreshAll()
ThisWorkbook.RefreshAll
End Sub

If you are using the referencing cell tip mentioned above, but are finding that the pivot table is open – this is the script which can be included in the above.

ActiveSheet.PivotTables("PivotTable1").PivotFields("PivotField1").ShowDetail = False

Week Date hacking

When the week date comes out – week xx 2012 its not too useful for two reasons  – its not the way people talk about dates and because it is larely useless for date sorting.

Turning this back into a date is pretty easy …

so c8 =

Getting the year

=RIGHT(C8,4)

Getting the Week Number

=MID(C8,6,2)

Converting that back into a date

=DATE(YEAR,1,1)+(D5*7)