This tutorial introduces you to data exploration with Seven Senders Portal Analytics. After the tutorial you will be able to understand how to pull data into Analytics, modify a report to see more details and how to deep dive into the data. 

• Where To Start?
• The Basics
• Explores are the Starting Point for Exploration
• Adding More Dimensions for More Detail
• Sorting Data
• Pivoting Dimensions
• Reordering Columns
• Removing Fields
• Displaying Totals
• Drilling Down Into the Data
  • Drilling Deeper …
  • Drilling in the Dashboards
• Copy Value
• Conclusion

Where To Start?

The explore section is the starting point to deep dive into your order and shipment data. 

Click on Explore Analytics in the right side bar menu of Analytics. The arrow allows to hide the menu to see the Explore in full screen. 

The Basics

To get started exploring, follow these steps:

1. Click one or more grey fields (called dimensions) to group your data.
2. Click one or more orange fields (called measures) to add information about those groups, such as totals and counts. In this example, we selected one measures.
3. Click Filter, if desired, to add a filter to your report based on that field.
4. Set the condition for any filters you’ve added. You can also click the Custom Filter checkbox in the upper right of the filters section for more flexible options.
5. If desired, choose a visualization type, such as a column chart in this case. You can click on the gear menu in the upper right of the visualization section to customize your chart.
6. Click Run.

In the Explore view you have dimensions and measurements in the categories: 

These options are just the basics. The Explore section has additional features that you can learn about in this tutorial.

Adding More Dimensions for More Detail

Let’s see how many shipments have a first delivery attempt(FDA). Adding the dimension for Has FDA Tag (Yes/No)(FDA=First delivery attempt).

Sorting Data

Let’s see which date has the most orders from returning customers (in other words, customers not making first-purchases). Clicking on the ORDERS Count column header under No will sort from highest to lowest. If you want to, you can sort by multiple columns by holding down shift, then clicking on the column headers in the order you would like them sorted.

Note that if you reach a row limit you will not be able to sort row totals or table calculations.

Pivoting Dimensions

Multiple dimensions are often easier to look at when you pivot one of the dimensions horizontally. Each value in the dimension will become a column in your Look. This makes the information easier to consume visually, and reduces the need to scroll down to find data.

To pivot a dimension, click PIVOT for that dimension. Before running the query, be sure that you also have included at least one unpivoted dimension and at least one measure. You can pivot additional dimensions as desired, but must always include at least one unpivoted dimension.

If there is no row of data whose value would appear in a column, that is indicated with the null symbol, a zero with a slash across. For example, on April 16th, we shipped one accessory but no active wear or blazers.

You can also sort pivoted dimensions by clicking the title of the dimension. To sort by multiple pivoted dimensions hold down shift, then click on the dimension titles in the order you would like them sorted. When sorting a pivoted measure, any rows with values in that column are sorted first followed by rows without data in that column (indicated by the null symbol.)

Reordering Columns

You can reorder columns in the Data section by clicking on a column header and moving the column to its desired position. The Explore’s visualization will reflect the new column order after you click Run.

Columns are organized in the Data section by field type: dimensions, dimension table calculations, measures, measure table calculations, and row totals. Columns can be reordered within each field type but cannot be moved out of their field type section. For example, dimension table calculations can be rearranged among themselves, but you cannot place a dimension table calculation in between two measures.

Columns under a pivoted dimension can be reordered, but the order of pivoted dimensions can only be changed by changing the sort order, not by manual reordering.

If your Looker admin has enabled the Table-Next Labs feature, there are additional optionsfor reordering columns in the table-next visualization.

Removing Fields

You can remove a field from your Explore by clicking the selected field in the field picker or by choosing Remove from the column’s gear menu:

You can also remove all fields in an Explore using the keyboard shortcuts Command-K (Mac) or Ctrl+K (Windows).

Once you have removed fields, click Run to get the new query results.

Displaying Totals

Sometimes a summary of your data is useful. You can add column totals to your report by clicking the Totals checkbox in the upper right, then running the report. You can also add row totals to your report, but only if you’ve added a pivot to your report:

Please note that if you’ve added row totals, and your query exceeds any row limit that you’ve set, you will not be able to sort the row totals column (though you can sort dimension and measure columns as normal). This is because you might be missing rows in your data that should be included in your totals. If you run into this issue, you can try increasing your row limit (up to 5,000 rows).

There are some cases when totals won’t be available:

1. Totals are only available for measures (and table calculations based on measures), not dimensions.
2. Certain types of columns won’t total, due to database limitations, or because the value would not make sense as a total. For example, you can’t add together a list of words.

Additionally, there are some things to keep in mind about how totals work in certain situations:

1. Columns that count unique items might not add up as you expect, since the same item might show up in several categories, but only be counted as one unique item in the totals.
2. If you’ve filtered your report by a measure, totals may appear to be too high. However, in actuality, what you’re seeing is a total for your data before the measure filter is applied. In other words, the measure filter may be hiding some data from your report, even though that data is included in the total.
3. Similarly, if you’ve placed row or column limits on your report, and your report exceeds that limit, totals may also appear to be too high. However, what you’re seeing is a total for your data before the limits are applied. In other words, the limits may be hiding some data from your report, even though that data is included in the total.

In situations 2 and 3 above, it is possible to calculate totals only for the data you can see. To do so, you’ll need to use a table calculation, explained later in this tutorial. For a column total use sum(${view_name.field_name}). For a row total use sum(pivot_row(${view_name.field_name})).

Drilling Down Into the Data

In Analytics, every query result is the starting point for another query. Clicking on any data point will drill down, creating another query refined by the data point you clicked. In the example below, we see that April 14, 2020 has had 158 orders. Clicking on the count of 158 takes us to details about those specific records.

Drilling Deeper …

In the drill overlay, we can see all of the orders placed on June 16, 2018. From here we can:

• Click the Explore from Here button to open an Explore that uses the fields in the drill overlay as a starting point.
• Click the Download Results button to download the data, using the same options as shown here.
• Click on the drillable Order ID field for an individual customer order to see all of his orders.

Copying Values

Analytics makes it easy to copy all the data from a table column. To do so, hover over a column label, click the gear icon, and then choose Copy Values:

This data can then be pasted into a document or a tool like Excel.

Getting to know the different sections

In the following we will deep dive into the different sections of the explore. 

Order Section

Let's first have a look into the general 'Order' section:

• Count [#] & Share [%] includes the measures that have formerly been listed below 'Order #'.

• Event/ Status includes the actual and historic status and event datapoints. Counts and shares like '# delayed' and '% delayed' can be found in Count [#]/Count by Tag [#] & Share [%]/Share by Tag [%]

• Notable fields in the order section of the explore:

  • Boarding Complete
    Indicates whether all shipments of an order have been shipped. Thereby only outbound shipments are taken into consideration.

  • FDA Tag
    Indicates whether all outbound shipments of an order have already received a first delivery attempt (FDA).

  • Created (Shop) Date Transferred (7S) Date
    The Created (Shop) Date  indicates when an order was created in the system of the shop while the Transferred (7S) Date indicates the time the order was transferred to us or in other words when the order was created in our database.

  • Status History ⚡
    In contrast to other fields in the explore event and status history fields are returning multiple rows for a single order since an order goes through multiple status in its lifetime.

Shipment Section

We now merged all dimensions and measures in one - only lead time calculation is still a separate one which will be further discussed later in the article. Let's first have a look into the general shipment section:

• Count [#]/Count by Tag [#] & Share [%]/Share by Tag [%] includes the measures
•  includes the actual and historic status and event datapoints. Clustering can be done in 'Shipment Lead Times 1/2'. Counts and shares like '# damaged' and '% damaged' can also be found in Count [#]/Count by Tag [#] & Share [%]/Share by Tag [%]

The fields listed below require some additional attention:

• Event History ⚡/ Status History ⚡
  In contrast to other fields in the explore event and status history fields are returning multiple rows for a single shipment since a shipment goes through multiple events or status in its lifetime.

• >Holiday Impact
  Especially for lead time calculation it can be helpful to filter out shipments where the lead time might be affected by public holidays. For this purpose we integrated a yes/no field and the names of holidays that impact the individual shipment. The rules for the holiday tag are as follows:

• There is a public nation-wide holiday in the warehouse country between
  planned pickup and first hub scan (FHS), OR
• There is a public nation-wide holiday in the consumer country between
  first hub scan (FHS) and first delivery attempt (FDA), OR
• There is a public nation-wide holiday in the carrier country between
  first hub scan (FHS) and first delivery attempt (FDA)

• Tracking Code
  Sometimes you need to track a certain parcel in different ways. By clicking on the tracking code of a shipment we already referred you to the tracking on our tracking page.

Shipment / Order Lead Times Section -Dynamic Lead Time Calculations

The analytics explore enables a dynamic and customized way of calculating lead times and SLA fulfillment. You can select between various timestamps and conditions to calculate the lead time that best reflects the individual business case (see below for a business example). 

These selection fields are marked as  FILTER-ONLY FIELDS and are always located at the top of the lead time section in the field picker. They will therefore only show up in the filter section of the explore once selected and not in the data section or visualization.

Lead Time Condition

The first field you need to consider is the Condition. The condition is defining the days included in the lead time calculation. Currently the following options are available:

• Mon to Fri

• Mon to Sat

• Mon to Sun

• Tue to Sat

By selecting Mon to Fri for example Saturdays and Sundays would be deducted from the calculation.
Due to the dynamic architecture the lead time calculation is build on we can easily add more options.

Service Level Agreement (SLA) Days Selection

The field SLA Days [Date Changes] enables you to make dynamic and individual SLA calculations. It is directly connected and only needs to be used with the SLA Fulfillment [%] measure (see below).

With this you are able to define different SLAs for different lead times individually. A common business case for this are different SLAs for Pickup to FDA and FHS to FDA. The field is a number only field. If a string is used no SLA fulfillment will be calculated. The same is true if no days are specified. 

If you for example select 4 as SLA agreement and then calculate the SLA Fulfillment [%] for the lead time FHS to FDA you will get the percentage of shipments that were delivered within 4 days from FHS to FDA taken into consideration the condition specified.

Timestamp Selection

The fields  and  Timestamp - To are setting the start and end point for lead time calculation. 

If you need to calculate the time from pickup to first delivery attempt you would set the Timestamp - From filter to Pickup and the Timestamp - To field to First Delivery Attempt [FDA] 

Lead Time Measures & Dimensions

Within each lead time section in the field picker you will find the different lead time measures you need to select for measuring lead times. The values are of course based on the condition and timestamps selected (see above).

• Date Changes [AVG]
  Date changes are not taking into account the different times of day. It is the most common KPI for lead time calculations. In a descriptive way this lead time equals the number of ‘midnights’ between start and end time.
  Example: A shipment has a FHS on day A 13:00 and a FDA on day C 18:45. The number of date changes for the FHS to FDA lead time is 2.

• Hours [AVG]
  This measures gives you the average number of hours. They are not taking into account the minutes. That means the hours are not rounded in any way.
  Example: A shipment has a FHS on day A 13:00 and a FDA on day C 18:45. The number of hours for the FHS to FDA lead time is 53.

• Days [AVG]
  The day measures is based on the hour measure divided by 24. For a shipment with a hour measure of 24 the day measure would be 1.
  Example: A shipment has a FHS on day A 13:00 and a FDA on day C 18:45. The number of days for the FHS to FDA lead time is 2.2.

• Cluster - Date Changes [#]/Cluster - Date Changes [%]
  Depending on the condition and timestamps selected these measures give you the number or share of shipments where the selected lead time in date changes is either ≤1 or ≤2 or ≤3.
  Example: You can calculate the I+2 ratio (percentage of shipments with an FDA within 2 days from FHS) by simply selecting the lead time FHS to FDA and the measure “Share +2 Shipments [%]”.

• SLA Fulfillment [%]
  Shipments and orders can have a specified SLA. However, those SLAs are of course also dependent on the lead time you measure. This measure takes the selected lead time [date changes] and calculates for each shipment if the specified SLA days (SLA Days [Date Changes]) was fulfilled. 

• Clustered Date Changes
  This dimension enables you to cluster shipments/orders into different lead time clusters. This helps you on questions like “Which % of shipments was delivered within 2 days”. Simply select cluster dimension and the shipment percentage measure to get the results on this question.

• Promised Delivery Fulfillment [%] - Order Lead Times only
  For orders a promised delivery date can be specified. This measure calculates the average percentage of orders where the promised delivery date was kept. The calculation is based on FDA dates of the connected shipments and only takes into account outbound shipments as well as orders where all shipments have an FDA.

Rules for Lead Time Calculation

The following procedure / rules apply in the lead time calculations:

1. A lead time can not be negative

2. The lead time is 0 when Timestamp - From is later than (≥) Timestamp - To

3. The lead time is NULL (i.e. empty; ≠ 0) when either of the timestamps is NULL

4. The timestamps are not rounded (date changes do not take into account the hour of day and hours do not take into account the minutes)

5. If Days to Exclude ∈ [Timestamp - From,Timestamp - To] then we deduct the number of occurrences of the days to exclude from the original lead time

Conclusion

Now that you know how powerful the Analytics Explore page is for building queries, displaying results, and discovering insights through iterative searches, you might want to limit your results to just the data you’re interested in.

Filtering and Limiting Data in Analytics