Follow the Metric Recipe below to create the metric Total Sales excluding POS from Shopify.
1. Set up the dataset
Ensure that you have the following views and columns included in your datasets. Read more about how to create a dataset.
a) Include these columns in the Sales Dataset
Data Source
Views
Columns
Shopify
Sales
SalesAgreementId
Id
LineType
TotalAmount_ShopMoney_Amount
b) Include these columns in the Orders Dataset
Data Source
Views
Columns
Shopify
Orders
Id
SourceName
Test
ProcessedAt
c) Include these columns in the Sales Agreement Dataset
Data Source
Views
Columns
Shopify
Sales Agreements
Id
OrderId
HappenedAt
d) Merge Datasets together
First merge the following datasets:
Dataset
Column to join
Operator
Sales Dataset
SalesAgreementId
Left Outer Join
Sales Agreement
Id
Next, merge the Orders dataset into the previous merged dataset:
Dataset
Column to join
Operator
Sales Agreement
OrderId
Left Outer Join
Orders
Id
2. Create the Custom Metric
Create the metric using the merged dataset:
a) Total Sales (excluding POS)
Field
Value
Measure
TotalAmount_ShopMoney_Amount
Date
HappenedAt
Dimension
(optional)
Aggregation
SUM
Filters
SourceName DOES NOT EQUAL ≠ pos
LineType DOES NOT EQUAL ≠ GIFT_CARD
Test EQUALS = 0
Check out the Schema Explorer to learn more about all supported views and columns/properties: https://schema-explorer.databox.com/?integrationkey=Shopify