Add A Customer Report With Raw Queries

Follow the steps below to add a customer report with raw queries. 

Step 1: Locate and click on the “Reports” tab on the left-hand side.

Step 2: Click “Add Report With Raw Query”.

Step 3: Input the report name and click “Done”.

Step 4: To add a column to the report, click “Add Field”.

Step 5: Input the field label and name, then select the data type from the drop-down menu. Then click “Done”.

In this example, we are creating a customer report. The fields available for customer reports are: 

first_name, last_name, email, phone, street, city, territory, state, postal_code, country,

active,lead_source, lead_sub_source, campaign_number, do_not_text, do_not_call,

preferred_contact_method, referred_by, sales_notes, created_at and updated_at. 

The data can be accessed and added to the report using contact.fieldname, as in the example below.

Step 6: The resulting report output will resemble the example in the image below.

Step 7: In the “FROM” field, input the entity. In this case, the “From” entity should be “contacts”.

In the “JOIN” field, join the current entity with another entity. For example, the “contacts” entity has the “owner” column, which is related to the “users” table. In this case, we can use the JOIN function between users.id and contacts.owner_id.  

The “JOIN ORG” field is used to indicate the commonalities between the current entity and the organization. As an example for the “contacts” entity, you can add JOIN ORG = users.organization_id.

See an example of the aforementioned field inputs in the image below.

Step 8: In the “WHERE” field, you can add specific conditions, such as fetching records for the state of California as seen in the example below.

Step 9: To add a grouping column, go to the “GROUP BY” section and select a column from the dropdown menu. Then click “Add”.

Step 10: Use the “Order By” section to sort data from the selected field in ascending or descending order. Simply select the required options from the corresponding dropdown menus.

Step 11: To add a chart to the report, toggle “Add Chart” on.

Step 12: Select the chart type from the dropdown menu. 

Step 13: Select the Y-axis and X-axis values from the corresponding drop-down menus. Then click “Add”.

Step 14: Click “Save & Run”.

Step 15: This will generate the report with the chart. Click on the “Edit” button to make changes. You can also download the report or broadcast it from this screen.