Table of Contents

• Lab Overview
• Lesson 1 - Set-up
• Lesson 2 - Setting up your first data extract
• Lesson 3 - Organize your Data
• Lesson 4 - Setting different dates
• Lesson 5 - Linking to cells for filtering and scalability
• Lesson 6 - Scheduling Workbooks
• Lesson 7 - Bring in external data
• Lesson 8 - Anomaly Detection and Realtime Reporting
• Lesson 9 - Make your excel workbook user friendly with Macros
• Lesson 10 - Make your excel workbook look on point
• Next Steps
• Additional Resources

Lab Overview

Excel continues to be one of the most popular tools used in business today. Excel’s continued popularity can be attributed to its ease of use for beginners, through to its powerful functions for advanced users. In this lab, we will show you how to use the Adobe Analytics Report Builder plugin to extract your Adobe Analytics data into excel. We will show you some tips & tricks that will enable you to easily create functional reports that suit the reporting requirements of your organization.

Key Takeaways

You will learn how:

1. Structure and set up your data extracts for maximum scalability and easy presentation
2. Understand how to use report suites, segments, date ranges and filters to extract your data
3. Utilize the advanced functions such as anomaly detection and real-time reporting
4. Learn some tips & tricks to take your excel sheets and workbooks to the next level

Prerequisites

You will need:

1. Adobe Analytics
2. Excel
3. Report Builder Excel Plug-in
4. Excel Developer Add In

Lesson 1 - Set-up

Objective

• Log into the lab machine

• Open excel and log into Report Builder

Lesson Context

To set up your machine ready for the report builder lab. To speed things up, we've already set up an excel workbook for all lab participants, LAB-L754-Excel-lent_Plug-in.xlsm. We will be using this workbook for all of our exercises today.

Exercise 1.1 – Lab Setup

1. Login to your lab machine using the credentials on the sticker on your laptop

2. Open the lab Excel workbook, LAB-L754-Excel-lent_Plug-in.xlsm

3. Log into Report Builder with the following credentials

   • Username: L754+00@adobeeventlab.com
   • Password: Adobe2019!

   *Replace ‘00’ with the user id listed on the sticker attached to your machine

Lesson 2 - Setting up your first data extract

Objective

1. Basic run through of the report builder extract structure

2. Build a data extract from different report suites, segments and metrics

3. Understand how apply date ranges to your extract

Lesson Context

Firstly, we will set up a data extract that will form the basis of our lab today. We will set up a basic visits extract, using a production report suite and a segment.

Exercise 2.1 – Set up your report builder extract

1. Navigate to Add-ins Menu and click on the Create menu.

   

2. Select the following options:

   • Report Suite = JJ Esquire Training 22

   • Segment = US Buyers

   • Site Metrics > Site Metrics

   • Preset Dates > Commonly Used Dates “Last 90 days”

   • Apply Granularity = Day

   

3. Click the Next button.

4. Select visits from Metrics by typing “visits” in the metrics bar.

5. Double click Visits in the list to add it to the metrics area. You will now see it in the Preview pane.

   

6. Click Finish

   Tip – if you have many variables, it’s usually easier just to type what you are looking for, rather than scrolling through a long list.

7. Report builder will then prompt you to select the position for your table. You can either type directly in or select a cell, select cell A1.

   

8. Then click in the small report icon to the right of the input box

9. Report builder will then ask you to pick a date. Check the box :Set to current date on future refresh actions: and click OK.

   

   Tip - If you use dual screens, report builder has the annoying quirk of displaying pop-up windows on the alternate monitor. If you find yourself in a position where report builder appears to be non-responsive, check your other monitor for a pop-up window

   You should then see your data extract as follows:

   

Lesson 3 - Organize your Data

Objective

• Set up your Excel workbook for the Report Builder data extracts
• Set up your worksheet for the structured data tables and dashboard information
• Build your presentation layer based on data from the structured data table

Lesson Context

Data preparation is key for a successful Report Builder project. A well thought through data setup will enable you to quickly scale and duplicate data for your report builder reports, with minimum changes or rework. (We have pre-populated some of the excel worksheets to save time and focus on the Report Builder features, rather than the excel formulas).

Exercise 3.1 – Setup of Data extract worksheet

Firstly, we will rename the worksheet that contains the data extract. Use a meaningful name. Follow a convention such as starting ending with extract.

1. Right click on Sheet1 and rename the sheet dataExtract

   

Exercise 3.2 – Setup of Data table worksheet

Now we will set up a new sheet to manipulate the data into the right format for our dashboard.

1. This sheet has already been set up and is called dataTable. This sheet simply links to the data extract table and should look like this:

   

Exercise 3.3 – Setup of information worksheet

This sheet should contain all relevant information about your workbook and space for any user input that may be required. We will come back to this again and again throughout the lab. This will make it much easier to link to dates for your reports.

1. This sheet has already been set up and is called information

2. The sheet contains the follow information and the dates are linked from today’s date.

   

3. For your reference, the formulas and named ranges are:

Cell	Formula	Name
B5	=today()	todayDate
B7	=today()	reportDate
B11	=reportWeekEnd-6	reportWeekStart
B9	=INT((B7-1)/7)*7+0	reportWeekEnd

Exercise 3.4 – Build the presentation layer

Finally, we need a worksheet for the presentation layer. This worksheet will be viewed and interacted with by your end users.

1. This sheet has already been set up and is called summary. It contains a line chart of range: dataTable!A1:B91 and should look like this:

   

Lesson 4 - Setting different dates

Objective

• Understand the different date options available in Report Builder
• Changing time periods to match your organization’s Financial Year
• Run reports for different time periods such as fixed vs relative date ranges

Lesson Context

Dates are often the most important way to view data in most organizations. Report Builder gives you the flexibility to set up different date ranges across, hours, days week, months and years. You can use report builder to ensure each end user can view their data using their preferred date ranges.

There are four types of date ranges you can select

• Preset
• Fixed
• Rolling
• Date from cell

Exercise 4.1 – Setup a fixed date report

Report builder gives you options for fixed or relative dates. In this exercise we will change the dates to match an uncommon financial year, in this case November through to October.

1. Return to the dataExtract sheet. Right click on your extract and select Edit Request > All Parameters

   

2. Change your date range to 11/1/2018 to 10/31/2019 and click OK.

   

3. Click Next and Finish

4. Go to your dataTable sheet and copy down the rows for the full year

5. If you review the graph you still see that it shows a zero line. This is a quirk of Excel for line charts.

   

6. Change the formula to show #N/A so Excel wont graph it, by copying and pasting the formula below (You can find this formula in cell C1) and fill it down.

   • =IF(dataExtract!B2=0,NA(),dataExtract!B2)

   This will change the graph to only show the values you want.

   

   However, this is frustrating as the chart shows today’s date, which contains incomplete data. So, we can further adjust the formula not to show today’s date.

7. Adjust the formula to check if the date from the extract is larger or equal to today’s date, by copying and pasting the formula below (You can find this formula in cell C2)

   • =IF(dataExtract!B2=0,NA(),IF(A2>=todayDate,NA(),dataExtract!B2))

   You now have a graph that will automatically update correctly when you refresh report builder for any data range.

   

Exercise 4.2 – Using rolling dates

Rolling dates can be useful for reporting different time periods. E.g. we will now show the orders over the last 4 weeks and compare them to orders 4 weeks prior using a different report suite

In order to do this, we will set up a full 8 week rolling date period, based on today’s date, using the advanced options.

Tip: An easy way to start a new extract is to Copy/Paste an existing extract. This is especially helpful if you are using the same report suite and segment.

1. Right click on your first extract to copy and then right click again to paste the first extract into cell E1

2. Then right click and click Edit > All Parameters

3. Select Rolling Dates and then click “Show Advanced Options”

4. Now we will change the Report Suite to “JJ Esquire Training 21”

5. Tick Customize expressions

6. Enter the following:

   • From: cw-8w
   • To: cw-1d

7. Change the granularity to Week

   

   • cw – means current week
   • -8w – means go back 8 weeks
   • -1d – means go back 1 day (useful to not include today’s date)

8. Click Next and change Visits to “Orders”

9. Click Finish

   Your data should look like this

   

   Tip: A link to more information on date parameters can be found in the appendix

Exercise 4.3 – Creating a graph using relative values

Now, imagine you are required to report this week’s sales, next to the sales from 4 weeks ago.

1. Switch back to the dataTable sheet, and review the formulas and data starting in cell E1.

   

   For your reference, the formulas are here:

   Cell	Formula
   F5	=reportWeekStart
   G7	=VLOOKUP(F5,dataExtract!E:F,2,FALSE)
   H5	=VLOOKUP(reportWeekStart-(8-E5)*7,dataExtract!$E:$F,2,FALSE)

   Tip – In report Builder the date indicates the starting date. E.g. for a weekly report, the date is the first day of the reporting week

2. Return to the summary worksheet to view the graph of the last 4 weeks sales, compared to sale 4 weeks prior.

   

Lesson 5 - Linking to cells for filtering and scalability

Objective

1. Use the “offline” mode

2. Link to a specific cell

3. Filter by custom dropdown list

4. Adding this chart to the summary sheet

Lesson Context

This lesson will show you how to adjust the date and other variables using linked cells. You can then change these link cells with manual input, formulas or drop-down lists. This will enable your report to be interactive and allow for quick replication for different audiences.

Exercise 5.1 – Use offline mode

Now that you have more than one report builder request, it can be handy to work in “offline” mode. This will stop Report Builder constantly refreshing the requests every time you change them. This is like entering Excel’s manual mode.

Exercise 5.2 – Filtering on variables by linking to a specific cell

Now we are going to create the option of filtering on products.

1. On the dataExtract sheet, cut and paste the 2nd extract into Cell H1

2. Right Click Edit > All Parameters

3. Select Product > Product and click Next.

   

4. Change metrics to Orders, then click on Top 1-10, click Filter

5. Click Specific and select the Cell with the Product name ‘information’!$B$13

   

   Tip - Named cells and ranges don’t work in this drop down as Report Builder names the cell itself.

6. Click OK and you should see this

   

7. Then click Finish

8. Since we are in offline mode, the extract will not have populated. There are 2 easy to update this extract. Firstly, right click on the extract and click Refresh Request

   

   OR

   Use the request manager and click on your request and click Refresh. This will refresh only the extract selected

   

   

   You will now have the sales of only the one product, linked from cell B13

   

Exercise 5.3 – Adding to your Dashboard

1. Select the dataTable sheet

2. Confirm that the data is linking correctly starting in cell H1.

3. Select the data range L1:M9 and click “Insert Column Chart”

4. Move the chart to the summary sheet under the “Total Weekly orders compared to 4 weeks prior” chart

5. Right click on the X Axis and click Format Axis**. Change** it to “Text axis”.

   

6. Click on the Chart title and then into the formula bar, type on link to cell =information!B15

   You will now have a graph of only the “Basic High Waist Jean” product orders. The title is automatically driven by a link back to the information sheet.

   

Lesson 6 - Scheduling Workbooks

Objective

• Schedule your workbook for weekly email sends

Lesson Context

A useful way of communicating your data is to schedule your report builder workbooks to be sent directly to your audience. You can easily set up email or ftp transfer of your data following a schedule.

Exercise 6.1 – Schedule a workbook for email

1. Click Schedule on the Report Builder Toolbar.

   

2. Click New

3. Click Schedule for Later

4. Enter email addresses (separated by a “,”)

5. In the subject box, replace the text with “greenfieldswidgets_operational_report“

   

6. Click Customize and check the “Insert date stamp in subject” box and click OK

   

7. Now click on “Advanced Delivery Options” and change the options to send the report weekly on Monday at 8am

   

8. Once you have made your necessary changes, click **OK. **

   Your workbook will now send to your recipient every week.

   Tip - It’s really important to think about your end users so you can build your workbook in the most flexible way possible. E.g. using this, workbook you can easily change the product in the information sheet, save the workbook with a different name and reschedule to a different audience.

Lesson 7 - Bring in external data

Objective

• Import your own data
• Bring the Data into the dataTable sheet
• Bring it onto the summary sheet

Lesson Context

One of the main reasons to use report builder, instead of the Adobe Analytics workspace Interface is the ability to easily bring in external data. In this example we will incorporate external data from Facebook into our Dashboard.

Tip – If you are scheduling these workbooks you will need to ensure you update this data regularly before the report is scheduled to go out

Exercise 7.1 – Import external data sources

1. To save time, we have already imported some data into the workbook in the sheet dataExternalFacebook:

2. Now we will bring the data into the correct Format. In the dataTable sheet, copy the formulas from cells P2:S2 down to row 366.
   You should have a similar table to the one below

   

   For your reference the formulas are below

   Cell	Formula
   P2	=+dataExtract!A2
   Q2	=VLOOKUP(P2,dataExternalFacebook!A:M,13,FALSE)
   R2	=+T2-R2
   S2	=VLOOKUP(P2,dataExternalFacebook!A:M,10,FALSE)

3. Now we need to bring the data onto the summary sheet. Return to the summary sheet and you will see a stacked area chart set up for Facebook Reach

   

   Tip - By running all the formulas off the same date ranges, you will ensure your different data sets does not get out of sync.

Lesson 8 - Anomaly Detection and Realtime Reporting

Objective

• Run an extract that includes anomaly detections and real-time data
• Set-up a trended range graph in your summary sheet highlighting anomalies
• Set up a real-time reporting

Lesson Context

Report builder allows you to access different data types including some of the more interesting features of Report Builder, including Adobe Sensei that powers anomaly detection, and real-time reporting

Exercise 8.1 Anomaly Detection Extract

1. Copy and paste the first data extract (cell A1) into cell N1

2. Right Click and click Edit Request > Edit All Parameters

3. Click Next and then Anomaly Detection > Insert > Lower Bound, Expected and Upper Bound.

4. Click Finish

   

   Tip - Anomaly detection is available only when you select the Day granularity.

   You will now have the anomaly detection data into your extract

   

Exercise 8.2 Set up Range Graph

1. In order the set up a graph with a range, we need to do some data manipulation

2. On the dataTable sheet, add one more column called Expected Range in cell Z1.

3. Calculated the difference between the lower range and the higher range but typing =Y2-W2

4. Copy the formulas down to Row 366

5. Switch to the summary sheet, and jump down to row 55 to see we now have a graph of this data

6. This had been created using a combination of a Line and Stacked Area Graph

   

Exercise 8.3 Realtime Reporting

To enable this, you must first setup your real-time reporting from within the Analytics interface. It will then be available as a Report Builder option.

1. Create a new extract, located in cell U1 on the dataExtract sheet

2. Select Real-Time Reports > Sales > Product Reports > Product Department

3. Select *Last Rolling 12 Hour(s) by 60 Minutes *

   

4. Then click Next

5. Swap the Date Range and Product Department around to match the format of the other extracts

   

6. Then click finish - you will have a data extract that looks like this

   

Exercise 8.4 Set up the Realtime Reporting Charts

1. On the dataTable sheet, you will notice we are pulling through the data in AC1:AH14

   Switching to the summary sheet you will see we now have a chart of this data, as well as the orders over the last 12 hours by product department

   

Lesson 9 - Make your excel workbook user friendly with Macros

Objectives

• Create filter dropdowns
• Create a Report Builder refresh button
• Create print to pdf button

Lesson Context

It’s important to build your workbook with your end user in mind. Questions to ask are:

• How often will they need updated data?
• Do they need to print this out?
• Do they want to adjust settings?
• Who will they share it with?

You can make your workbook friendlier by utilizing Macros to refreshing the data, allowing filtering of the data, make it easy to print (in the correct format for your region!) and ensuring it looks awesome. We have prepopulated the workbook with Macros for you to use.

Exercise 9.1 – Add a filter button for Products

We will now add a drop down to the graph for selecting products.

1. Go to your information sheet and notice the list of Products in cells C17 to C20 and the Product Selection cell B24

   

2. Return to the summary sheet and add a list button by
   Developer Tools > Insert > Combo Box

   

3. Right click format control and link to the Products and Product Selection Values

   

   You will now have a drop-down list your users can select which product to run

   

   Tip - Another way to do this is to add all products to the initial report builder query, and then use formulas to only show the selected product in the graph. This way users would not have to refresh the query.

Exercise 9.2 – Create a button to refresh report Builder

Creating a refresh button can make it easier for your end users to re-run and update the data in the report

1. Go to the summary sheet and add a button via Insert > Form Control > Button

   

2. Draw the button under the drop down list

3. Pick the RBRefresh Macro and Click OK

4. Rename the button to Refresh Data by right clicking and selecting Edit Text.

   You now have a button that your users can easily click to refresh the whole worksheet

Exercise 9.3 – Ensure it will print

1. Go to Excel Menu Page Layout > Size and check the paper size, usually Letter or A4.

2. Go to Excel Menu View > Page Break Preview and ensure your report fits neatly onto 2 pages by dragging the outside page lines to capture the graphs only (and not the macro buttons).

   

Exercise 9.4 – Add a print to PDF button

1. Go to the summary sheet and add a button via Insert > Form Control > Button

2. Draw the button under your Refresh Data button

3. Click Assign Macro and pick the PDFActiveSheet Macro and Click OK

4. Rename the button to Print to PDF by right clicking and selecting Edit Text.

Exercise 9.5 – Add information on how to use the report

1. Finally add some instructions to help your end user with how to run the report as below

   

Lesson 10 - Make your excel workbook look on point

Objectives

• Use a logo
• Color palette
• Ensure it can print
• Remove unnecessary distractions
• Lock it down

Reports that look good are usually taken more seriously than ones that don’t. Here are some tips to brand your presentation sheet for maximum impact. Some simple changes to excel can make your report look much better than the default style.

Exercise 10.1 – Use your logo

1. Add your logo. It’s a simple thing to do that makes a big difference. Go to your summary sheet and observe the logo. (We’ve already added it for you to save time!)

Exercise 10.2 – Fix the color palette

1. Ideally you would have a color palette set up for your organization.

2. In this case we will pick the Green color palette to match the logo.

3. Click on the Excel Menu Page Layout Menu > Click Color > Green

   

Exercise 10.3 – Remove unnecessary distractions

1. Return to the View menu and click Normal

2. Uncheck all the boxes for:

   1. Gridlines
   2. Formula Bar
   3. Headings

   

Exercise 10.4 – Lock It Down

1. To stop people changing the report builder settings, you can lock it by clicking on the Report Builder Menu “Unlocked”

   

You will see the Report is now locked

TIP - You may also want to lock down and/or password protect the workbook in Excel. This can be useful to stop people fiddling with it!

Exercise 10.5 – Pdf your finished report

1. Now click on the PDF Report button and save your report

2. Your final report should look like this:

   

Next Steps

There are many great uses for Report Builder. These three articles have excellent information on how to take Report Builder to the next level.

1. Use Report Builder to learn The API
   https://helpx.adobe.com/analytics/kt/using/learn-analytics-api-report-builder-feature-video-use.html

2. Power BI Integration
   https://marketing.adobe.com/resources/help/en_US/arb/power_bi.html

3. Facebook Power Query into Excel
   https://support.office.com/en-us/article/connect-to-facebook-power-query-5c3f381b-7268-4fb6-a586-dfb94a03a0aa

Additional Resources

There are many Adobe resources available.

• https://www.adobe.com/au/data-analytics-cloud/analytics/report-builder.html

• http://blogs.adobe.com/digitalmarketing/analytics/report-builder-power-user-series-create-auto-refreshing-workbooks/

• http://blogs.adobe.com/digitalmarketing/analytics/two-more-ways-to-extract-value-from-report-builder/

• https://marketing.adobe.com/resources/help/en_US/arb/examples_of_date_ranges_using_customized_expressions.html

• https://forums.adobe.com/thread/2325341