How To Automatically Export XPO Reports To Looker Studio (fka Google Data Studio)

XPO comes with its built-in reporting and visualization capabilities. You may want to export the reporting data from XPO via email, FTP and also Google's Looker Studio (formerly, Google Data Studio) to slice and dice the data further in different visualization tools. Here's how you can export data into Looker Studio. 

Step 1: Create a Looker Studio account (OPTIONAL)

Sign up and create a Looker Studio (LS) account  , if you do not have one.

Step 2: Schedule a report in XPO

1. After signing up for a LS account, go to XPO dashboard.
2. To create a report to be delivered at your preferred schedule, click
   • If you are using Account Dashboard: click Reports > Scheduled Reports
   • If you are using Partner Dashboard: click Utility > Scheduled Reports

3. Under Report Details > Recipient(s), type in your LS account email which you used to sign up or log in to LS to link your LS account with XPO.

   

Step 3: Create Report and Add Tables in LS

Here, we will create a report and add the list of tables as data sources for the report. These tables are based on what you have selected in XPO (see Step2.2 above).

1. Create a report on LS: click Create >> Report to create a new report.
   
   
   
2. You should see this pop-up Add data to report.
   
   
   
   
   Otherwise, click edit to enter into edit mode. Then, click on Add Data.
   
   
   
   
   
   
   
3. From the Add data to report pop-up, click on Google Big Query.
   
   
   
4. Click Authorize to enable LS to connect to XPO data using Google Big Query.
   
   
   
5. Search for the project knorex-gds.
   
   
   
6. Select the project knorex-gds > available dataset > the table you wish to use. (More about the table) 
   
   
   
   More About The Table
   

   1. The selected project is the same for all users.
   2. Each account is linked to only one dataset.
   3. The tables are based on the fields selected when creating the scheduled report in step 2.
      Naming format of a table: <additional grouping>_<report name>_<scheduling deliver option>. 
      
      A table is created for each field selected under Additional Grouping. Each table contains all the fields selected under Report Metrics, Service Cost and Attribution Window. 
      
      
      For example, category_Test_DAILY table is created because the category field was selected under Additional Grouping. The other fields selected in the report metrics will appear as fields within the same table.
       
7. Click Add to add the table to the report. The report will be used for creating charts and other presentations and visualizations in Step 4 (see below).
   
   
   
8. Click Add to report.
   
   
   
9. To add another table to the final report, repeat step 2 to 5.

Step 4: Configure Data Type

Here, you would need to configure the data types of the fields in each of the tables added in Step 3. 

1. While in Edit mode, click Resource menu > Manage added data sources. 
   
   
   All the data tables that you have added in Step 3 will be listed.
   
   
   
2. For each Click into one of the data tables to configure the data. Under Actions column, click Edit. 
   
   
   A list of all the fields in the table will be shown. 
   
   
   
3. Change the data type of the data fields accordingly. (view XPO report fields and data types)
   What are the fields and their data types?
   

   Field	Data Type	Additional Information
   KNX_Id	String/Text
   KNX_CreateAt	Date
   KNX_Date	Date	Create a new field to change the data type to Date
   KNX_Campaign	String/Text
   KNX_Ad_Group	String/Text
   KNX_Creative	String/Text
   KNX_Channel	String/Text
   Additional Grouping
   Inventory Resource E.g., Ad Space ID	String/Text
   Audience E.g., Age, Gender, Geo	String/Text
   Technology E.g., Browser, Carrier, Device	String/Text
   Content E.g., Extension, Keywords	String/Text
   Call Tracking	String/Text
   Report Metrics
   Generally,	Number
   Contain “Percent”	Number	Create a new calculated field to use the "Percent" data type
   Contain “Partner_Currency”	Currency
   Contains “Duration_seconds”	Duration (sec.)
   Cost	Currency
   Service Cost
   Generally,	Currency

   
   Click the drop down and select the correct data type. 
   
   
   
   
   
4. Select the field name to change the name. 
   
   
   
5. Once completed, click Done. You will be presented with a list of tables. 
   
   
   
6. Once you have finished configuring the fields, click Close.
   

Step 5: View Report (Sample)

A sample report is provided here. You can easily create all sorts of charts and visualizations in LS.