2.2 Clean and prepare your order data for dashboards and reports

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.

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"

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