At Summit 2014, there was a session called “Adobe ReportBuilder: Attaining data mashing (Excel)ence“. (Here’s the PDF download of the presentation. It will show more detail than this post.) It was one of the more helpful sessions. If you don’t know what ReportBuilder is, it’s an add-in to Excel that allows you to build data blocks within your spreadsheet that pulls data directly from SiteCatalyst. (It used to be called Excel Client.) It’s a lifesaver for recurring scorecards where you constantly have to pull the same metrics with updated time ranges. No more exporting / copying / pasting!
While I find ReportBuilder to be pretty self-explanatory and Adobe has training videos on the basic functionality, at Summit they shared these useful new features and/or best practices:
Use separate Excel tabs for controls versus data
One of the most powerful features of ReportBuilder is that you can have the report blocks set up to dynamically pull certain report parameters from cells in Excel. Adobe’s first tip was have one dedicated Excel tab for all your controls/inputs, and separate data tab(s) that contain the data blocks:
Advanced / shortcut rolling date expressions
If you choose “Rolling Dates” from the date selection dropdown, there is an additional link for “Show Advanced Options”. Those allow you to write expressions to more exactly identify your rolling dates. Customized Expression Elements:
- cd = current day
- cw = current week
- cm = current month
- cq = current quarter
- cy = current year
Expression Examples:
- From cm-13m to cm-1d (returns the last full 13 months of data)
- From cw-13w To cw-1d (returns the last full 13 weeks of data)
- From cd-7d To cd-1d (returns the last 7 days of data)
- *always add “-1d” to eliminate partial, current day data
Editing multiple report blocks at once
While using cell-based inputs can eliminate the need for this feature, if you happen to still do it the old clunky way there is an option that will save you time by allowing you to mass-edit several report blocks at once. On the “Add-Ins” ribbon in Excel, select “Manage”, highlight the report suites you want to mass edit, select “Edit Multiple”, and follow the wizard to update the report parameters. Don’t forget to refresh!
Dependent data blocks
These are useful if you have a report that returns a list of line items that you want to use as a filter in another data block. I didn’t know this was even possible!
Anomaly detection
Using the previous 30 days as a training period, ReportBuilder can estimate the expected, upper, and lower range of data for a given metric. You must be looking at a trended report with daily granularity to be able to use this functionality. Enabling this will help you identify weird outliers in your trended data.
Bonus
Since it took me 2 months to write this post, with the May 22 upgrade it looks like all the events/metrics are now in the same pane and no longer separated by traffic/conversion metrics! No more having to build separate data blocks for visits and revenue, yay!