2.2 Clean and prepare your order data for dashboards and reports

πŸ“Š Outcome: Create an order table ready for dashboard & reports

πŸ•‘ Estimated Time: ~30min

 

Introduction

In this section, you will learn how to prepare your raw Shopify order data for a first simple order table that you can use for a first dashboard or reporting use case.

 

Bildschirmfoto_2021-10-08_um_17.03.39.png

 

Step-by-step guide

Step 1: Select relevant columns

  1. Open MODEL and create a new model "Shopify_orders"
  2. Insert an Input node and select the "orders" table from Shopify
  3. Use the Fields node to select the relevant columns that you will need. An example selection is:
        • cancelled_at 
        • created_at
        • currency
        • financial_status
        • fulfillment_status
        • id
        • shipping_address
        • test
        • total_discounts
        • total_price
        • total_tax

Step 2: Rename columns

Rename the columns to match your company's naming convention. Yellow highlighted columns will indicate changes.

        • cancelled_at β†’ cancellation_date
        • created_at β†’ order_date
        • currency β†’ currency
        • financial_status β†’ financial_status
        • fulfillment_status β†’ fulfillment_status
        • id β†’ order_id
        • shipping_address β†’ shipping_address
        • test β†’ test
        • total_discounts β†’ gross_discount_amount
        • total_price β†’ gross_revenue
        • total_tax β†’ total_tax

Step 3: Extract fields from shipping_address

Add a JSON extraction node in order to extract the columns "city" and "country" from the JSON column "shipping_address". JSON or JavaScript Object Notation is a standard text-based format for displaying structured data. JSONs are built on two structures:

  • Arrays are ordered lists of values. You can identify them based on the squared bracket [
  • Objects are a pair of name & value. You can identify them based on the curly brackets { In this example we are extracting the objects "city" and "country" out of the JSON "shipping_address"

Bildschirmfoto_2021-08-30_um_13.51.17.png

Step 4: Filter out test orders

Use a FILTER node in order to select only rows where the column "test" is 'false'.

Step 5: Create an OUTPUT node

Finalize your model by adding an OUTPUT node. Now you can determine a name for your output table. The OUTPUT node has similar functions as the FIELDs node. Additionally, you can determine a custom sequence of the columns.

Step 6: Commit the model

By committing the model you will safe your model and create your output table.

Video Explanation

 

Next Step ➑️:

2.3 Visualize order data in a dashboard

 

 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.