Custom Reports

To create a custom report or edit an existing report follow the steps below:

  1. From the dashboard click Reports.

  2. Click on an existing report, or to add a new custom report, click the + symbol.

  3. If you are editing an existing report, click the report and then click the edit report parameters (pencil) symbol in the generate (report name) report box in the upper right hand corner.

    • Name: This is the name of your custom report. This is a required field.
    • Description: A description for your custom report.
    • Icon: The icon to appear in the Reports section for your custom report.
  4. Data Gathering

    • Query Manually: Check this box if you want to manually input a query in the Data Preparation section below.
    • Model: The model database from which to pull the data.
    • Attributes: Use this feature to limit the data pulled to particular fields from the model database. By leaving this blank you impose no limits on the data being pulled. Select from available fields based on the model chosen. The available fields are visible under the first dropdown menu under Build in the Primary Query section. Additional fields based on the model chosen are shown below:

      • Order: items, returns, coupons, fulfillments, payments
      • Orderitem: variant, product, order, returns
      • Product: review, order, variant, collection
      • Variant: registry_item, product, order
      • Category: N/A

      The Attributes can be entered as a chain, e.g. items.variant or product.brand, in order to add additional granularity to the model. Use a * in addition to your chosen attributes if you want to include the rest of the data along with your attributes.

    • Primary Query

      • Query: Queries can be entered manually here. Queries built in Build section also appear here automatically.
      • Build: Use this feature to limit the data pulled to data which satisfies rules you specify, shown below:

        • Is equal to / Is not equal to: Limits the data to lines in which a field’s value is equal to / not equal to a date string, e.g. date_created is not equal to 1/1/2019.
        • Is in / Is not in: Limits the data to lines in which a field’s value is a member of a group of a strings (names, numbers, etc.) e.g. date_created is in 1/1/2019, 2/1/2019, 3/1/2019
        • Exists: Limits the data to lines in which a field’s value exists, i.e. is not empty. Use true and false.
        • Is less than / Is greater than: Limits the data to lines in which a field’s value is less than / greater than a number.
        • Is less than or equal to / Is greater than or equal to: Limits the data to lines in which a field’s value is less than or equal to / greater than or equal to a number.
        • Is in regular expression: Limits the data to lines in which a field’s value is in a regular expression you specify.
      • And/Or: You can add additional constraints using the and and or buttons. The and button will limit the data to lines in which a field’s value matches both constraints, whereas the or button will limit the data to lines in which a field’s value matches either constraint.
      • Test: Based on your queries in the Query and Build section, the ensuing data pulled will show up here. You can filter the resultant data by date using the Date Range button as well as filter the data by various criteria using the Filter button, but the results are just for viewing and do not carry over into the report output below.

        • Filter: You can use this feature to filter the data by criteria such Webstore Orders only. You can also create your own criteria clicking the + Create New Filter button, which brings you to a Query Builder which operates just like the aforementioned one.
        • Export: Click this button to export the data to a CSV file.
        • Search: You can search the data by ID, name, and date modified.
      • Group Aggregates: Aggregates allow you to sum, average, count, or find the maximum/minimum of model fields. To add a new aggregate, click the + Add Aggregate button.

        • Example: Say you wanted to add a column to the output that shows the average order value, or AOV. Under model name, choose order. Use function average, and enter total as your field. Enter AOV as the alias, and click Save.
        • To add a column for this newly created aggregate, go to Data Columns below and click the + symbol. Enter {{ aggregates['AOV'] }} in the black box, and title it AOV above in the title section. A new column titled AOV will now appear in your report containing the average order value.
  5. Data Preparation

    Here, queries can be entered manually, and the data can be prepared to create unique data columns representing facets of the data. You can also view the data in raw form by inputting the command {{ dump(data) }} and selecting Run Test from the Test Output section below. This is a good way to see all available data columns/fields based on your build queries and attributes.

  6. Data Columns

    Click on the + symbol to add columns. Type in the field name, such as order_id, in the entry {{ row['order_id'] }}. Available fields are based on the model and can be found under the first dropdown menu under Build in the Primary Query section. Give the column a title by typing in the white box that says New Column, which is the default name. You can also choose the order of columns by clicking the < and > buttons in the bottom left corner of the box, or delete columns by clicking the x in the bottom right corner.

    Also, you can add columns with your add-on attribute names such as product.brand or items.variant. However, they must be entered in a specific manner. If your model is orderitem and your attribute is product.name (a query to yield the official name of the product), for instance, you must enter the column as {{ row['product'].name }}.

    Or, if your model is order and you’ve added the attribute items (in addition to * to include the rest of the fields), you can add a column that shows the first item in each order by inputting {{ row['items'][0].name }}. The 0 means the first item in each order, and the name is the name of the item. You can’t simply enter {{ row['items'].name }} because there may be multiple items for each order and you must choose which one, i.e. the first, appears in the output row.

    An example of further granularity would be if your model is order and your attribute is items.variant. A variant is a product with specific attributes, e.g. a red sweater, or a 30 count box of bandages. To yield a column for the variant name you would enter a column as {{ row['items'][0].variant.name }}. If you wanted to add a column for the first image url, enter {{ row['items'][0].variant.images[0].url }}. You can easier see how to enter these strings if you first run a data dump.

    You can create new fields by summing or multiplying two separate fields. For instance, if your model is orderitem and you want to create a column for total revenue, simply enter {{ row['price']*row['quantity'] }} to yield the orderitem total.

  7. Test Output

    This section gives you a preview of what your report will look like. Once you have your data columns ready, click Run Test to generate a sample output. Click and drag the gray expand icon in the bottom right corner of the window to expand the output for more visibility. Note that if you have entered a data dump by inputting {{ dump(data) }} in the Data Preparation section the output will be overridden by the data dump.

  8. Once you are done making changes, click Save and the report will be ready to run.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

There are 0 Comments

Please sign in to leave a comment.