How To Automatically Export XPO Reports to Google Data Studio (GDS)?

Article author
Knorex Support
  • Updated

Screenshot_2022-06-15_at_1.34.19_PM.png

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 Data Studio to slice and dice the data further in different visualization tools. Here's how you can export data into Google Data Studio (GDS). 

 

Step 1: Create a GDS account (OPTIONAL)

Sign up and create a Google Data Studio (GDS) account  , if you do not have one.

 

Step 2: Schedule a report in XPO

  1. After signing up for a GDS 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 recipient, type in your GDS account email which you used to sign up or log in to GDS to link your GDS account with XPO.

 

Step 3: Link GDS Account and XPO Account

Email cst@knorex.com with the subject "Enable Data Studio" with the following details:
(a) your GDS account email
(b) the URL to your XPO account (how to get XPO Account Link)

Once you have emailed, our client success team will enable the linking between your GDS account and XPO account.

 

Step 4: Create Report and Add Tables in GDS

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 GDS: click Create >> Report to create a new report.
    blobid2.png

  2. You should see this pop-up Add data to report.
    blobid3.png



    Otherwise, click edit to enter into edit mode. Then, click on Add Data.

    Picture4.png


    Picture3.png

  3. From the Add data to report pop-up, click on Google Big Query.
    Picture5.png

  4. Click Authorize to enable GDS to connect to XPO data using Google Big Query.
    Picture30.png

  5. Search for the project knorex-gds.
    Picture37.png

  6. Select the project knorex-gds > available dataset > the table you wish to use. (More about the table
    Picture38.png

    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
      Picture63.png

      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 5 (see below).
    Picture11.png

  8. Click Add to report.
    Picture12.png

  9. To add another table to the final report, repeat step 2 to 9

 

Step 5: Configure Data Type

Here, you would need to configure the data types of the fields in each of the tables added in step 4

  1. While in Edit mode, click Resource menu > Manage added data sources
    Picture13.png

    All the data tables that you have added in Step 4 will be listed.
    Picture50.png

  2. For each Click into one of the data tables to configure the data. Under Actions column, click Edit
    Picture15.png

    A list of all the fields in the table will be shown. 
    Picture51.png

  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. 
    Picture51.png
    Picture52.png


  4. Select the field name to change the name. 
    Picture55.png

  5. Once completed, click Done. You will be presented with a list of tables. 
    Picture21.png

  6. Once you have finished configuring the fields, click Close.
    Picture22.png

Step 6: View Report (Sample)

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

Picture62.png

Was this article helpful?

1 out of 1 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.

Didn't find what you were looking for?

Submit a request