Looker: Dynamic Reports

Article Topics:

Overview

Reports

Filters

Selections

Pivoting Dimensions

Removing Fields

Report generation

Sort & Order Data

Export Data

One-time

Como Templates

Business templates

Dimensions

Measures

Examples

 

Overview

The Looker is an important BI tool that allows you to view analytics, generate dynamic reports, use visualization, and segment members. You can find it in the Data & BI tab of the Como Hub.

 mceclip2.png

 

Reports

There are different reports you can use to view your data in various ways:

  • PURCHASES & PAYMENTS
    • Points\Credit- view points, credit and purchases with points\credit measures with breakdown to time, points details, purchase details and member details.
    • Sales—view sales and items measures with breakdown to time, purchase details, items details and member details. (not include cancelled purchases)
  • CAMPAIGNS
    • Benefits- view benefits and purchases with redeem measures with breakdown to time, benefits details, purchase details, items details and member details.
    • Deals- view Deals measures and purchases and items with deals measures with breakdown to time, deals details, purchase details, items details and member details.
    • Punch Cards- view punches measures with breakdown to time, punch details, purchase details and member details.
  • COMMUNICATION
    • App- view app opens measures with breakdown to time and member details.
    • Emails- view emails measures with breakdown to time, emails details and member details.
    • SMS- view SMS (billing) measures with breakdown to time, SMS details and member details. This report shows number of SMS that were sent to the provider even if it didn’t arrive to the members.
  • MEMBERSHIP
    • Friend referral- view friend referral and their purchases measures with breakdown to joining time, purchases details and member details (referring and joined members).
    • Members details- view members count with breakdown to joining date and members details

From the report menu, you can navigate between reports:
mceclip3.png

 

A report consists of the following components:

Component

Description

Dimensions

Dimensions allow you to “slice and dice” your data according to different attributes. For example, add a dimension of Year to display your data by year or even filter specific years.

Measures

Measures are the actual quantitative measurements of your data. Different measures are available based on the report type you choose.

All Fields

Show both dimensions and measures

 

Note that you can search for a specific dimension or measure in the search line.

 mceclip4.png

 

Filters

Add filters to include only certain data in your report. For example, if you filter by the year 2019, only the data for 2019 is included in the report.

You can filter on almost every dimension and measure you see on the left bar.

  • When you hover the field you want to filter, click on the filter option (it will become purple)

 mceclip5.png

  • Another option, you can click on a dimension value, and it will offer you to filter according to it. 

1.jpg

  • The field will be added to the filter bar:

 mceclip6.png

  • You will see the field in the filters bar with options such as is equal, contains, etc.:

 mceclip7.png

  • In the value box you will see drop down of the values in this field:

 mceclip8.png

  • If you want to remove filter, click on the X mark on the right:

 mceclip9.png

  • In date filter you have different options such as: in the past X days/months/complete months (not include current month), is on the day (specific day), is in range, etc.

Note: when you select date range y, it doesn’t include the “until date”, so you need to select until the day after the day you want

mceclip10.png

Selections

  • In order to select a dimension or measure (to present in the report), you need to click on it, and it will become white and will appear in the output table:                                                            (dimensions are in blue and measures are in orange)

 mceclip11.png

 

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. Looker supports up to 49 pivoted values.  

 

  • 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.

 mceclip12.png

 

Removing Fields:

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

 mceclip13.png

 

Report generation:

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:

 mceclip14.png

then running the report by clicking “Run” checkbox in the upper right:

 mceclip15.png

 

Sort & Order Data

Order data:

  • You can reorder columns in the Data section by clicking on a column header and moving the column to its desired position.
  • Columns are organized in the Data section by field type: dimensions, measures, 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 between two measures.mceclip16.png

Sort data:

  • Click on any column header to sort (double click will order it descending)                                           

        mceclip17.png

 

Export Data

  • Once the report is ready, you can export it to excel by clicking the gear button located next to the  run button on the right and select “download”:

mceclip18.png

In the download window,

  • you can select the file format (Excel)
  • in the limit section, please mark “All results” (in the looker UI you will always see only 5000 rows so in order to export all rows (more than 5000) you need to mark this
  • in the file name section, enter the name for the excel file (English only)
  • note that the date format in the excel file will be according to your excel definitions

mceclip19.png

 

One-time

The one-time button allows you to filter the population and perform onetime on it.

In order to enable this button, you must build a report (and run it) with the “membership key” field that is under Members -personal details

mceclip20.png

 

Como Templates

Como has created for you some recommended templates you can open via the “Explore our out of the box reports” under the report menu.

 mceclip21.png

 

Business templates

You can create your own reports and save it as business templates. The Template is a tool that lets us keep reports that we want to generate regularly (monthly sales report for example).

  • Save template- create and filter report and then click on “Save as template”
  • Open saved template- click on “Load template” and select the report you want to open

mceclip22.png

 

Dimensions

You can filter or breakdown your data according to different dimensions.

Members

Category

Dimension

Description

Personal Details

First Name

First name specified by the member (such as on the registration form)

Personal Details

Last Name

Last name specified by the member (such as on the registration form)

Personal Details

Member Phone

Phone number specified by the member (such as on the registration form)

Personal Details

Member Email

Email address specified by the member (such as on the registration form)

Personal Details

Membership Key

Unique internal identifier for the membership

Registration Demographics

Birth Date

Date the member was born

Registration Demographics

Birth Month

Month the member was born

Registration Demographics

Age

Member's age in years, according to their birthday

Registration Demographics

Age Group

Different possible age ranges for members

Registration Demographics

Gender

Male, female, or whatever options provided on the registration form

Registration Demographics

Home Branch

Home branch selected by the member (such as on the registration form)

Segments

Member Tag

All the tags a member can have

Segments

RFM

The RFM tag the member has

Segments

Preferred Location

The preferred location tag the member has

Segments

Days Since Last Visit

The days since last visit tag the member has

Consent & Communication

Member Consented to T&C

Whether or not they gave consent

Consent & Communication

Allow Email

Whether or not they allow receiving marketing emails

Consent & Communication

Allow SMS

Whether or not they allow receiving marketing SMS 

Consent & Communication

App User

Whether or not they installed the app

Membership Type

Loyalty Member

Whether they members or non-Members

Membership Type

Test Member

Whether they test members (tag) or not

Membership Type

Registration Status

registered: member that registered (even if membership is expired or deleted) 

non-registered: app users that didn’t register

Additional Information

Staff Member

Whether they staff members (tag) or not

Additional Information

Special User

Whether they irregular members (tag) or not

Additional Information

Expiration Date

The date the membership is expired

Additional Information

Expiration Month

The month the membership is expired

Default Filter

Member Status

Whether the membership was deleted, or not (“ok”)

Member Join Date

Month

Month-year that they registered

Member Join Date

Quarter

Quarter-year that they registered

Member Join Date

Year

Year that they registered

Member Join Date

Date

Date that they registered (the date format is based on the business territory)

Member Join Date

Time

Date and time that they registered (the date-time format is based on the business territory)

Member Join Date

Week

Week number that they registered (the week starts on Sunday or Monday based on the business territory)

Joining Details

Joining Location Name

The location name where the member joined the club (only if he joined via POS)

Joining Details

Joining Source

Whether the member joined via POS, app, etc.

 

Note: in friend referral report, there are 2 members dimensions groups:

  • “Joined with Referral Code Members”- represent the details of the members who joined the club with referral code
  • “Referring Members” – represent the details of the members who invited others to join the club.

 

Comillia Member Insights

Category

Dimension

Description

 

Avg. Spend Rank

Whether the member is Low/medium/high spender

 

Days Since Last Visit

How many days since the member’s last visit

 

Recommended Item Code

The most recommended item for the member based on his preferences

 

Morning Visits Frequency

Whether the member is Low/medium/high frequency at morning

 

Noon Visits Frequency

Whether the member is Low/medium/high frequency at noon

 

Afternoon Visits Frequency

Whether the member is Low/medium/high frequency at afternoon

 

Evening Visits Frequency

Whether the member is Low/medium/high frequency at evening

 

Night Visits Frequency

Whether the member is Low/medium/high frequency at night

 

Purchases (Ticket)

Category

Dimension

Description

Purchase Date

Day of week

Weekday that the purchase was made

Purchase Date

Month

Month-year that the purchase was made

Purchase Date

Quarter

Quarter-year that the purchase was made

Purchase Date

Year

Year that the purchase was made

Purchase Date

Date

Date that the purchase was made (the date format is based on the business territory)

Purchase Date

Time

Date and time that the purchase was made (the date-time format is based on the business territory)

Purchase Date

Week

Week number that the purchase was made (the week starts on Sunday or Monday based on the business territory)

General Information (Single Purchase)

Location ID

Identifier of the location in which the purchase was made

General Information (Single Purchase)

Location Name

name of the location in which the purchase was made

General Information (Single Purchase)

Purchase Tags

The combinations of tags that the purchase has

General Information (Single Purchase)

Order Type

The purchase type such as delivery, dine-in, etc.

General Information (Single Purchase)

Cashier

The cashier that submitted the purchase in the POS

General Information (Single Purchase)

Sum

The invoice sum of the single purchase

General Information (Single Purchase)

General Discount

The general discount of the single purchase

General Information (Single Purchase)

POS ID

Identifier of the POS "terminal" associated with the purchase (which can sometimes be used to determine if the purchase was made in-store or website like ordering)

General Information (Single Purchase)

POS Transaction ID

The purchase transaction ID that is coming from the POS

General Information (Single Purchase)

Como Purchase ID

Como internal identifier of the specific purchase

 

Items

Category

Dimension

Description

General Information

Item Code

Item code defined in the POS catalog (or in the Hub for in-app purchases)

General Information

Item Name

Item name defined in the POS catalog (or in the Hub for in-app purchases) 

General Information

Item Price

The net item price (gross - discount)

General Information

Item Gross Price

The gross item price

General Information

Item Discount

The discount on the item (shows as negative)

Additional Information

Department Code

Department code (or code for a different hierarchy) defined in the POS

Additional Information

Department Name

Department name (or name of a different hierarchy) defined in the POS

Additional Information

Item Tags

The combinations of tags that the item has

 

Benefits

Category

Dimension

Description

Event Date

Day of week

Weekday that the benefit was received\redeemed

Event Date

Month

Month-year that the benefit was received\redeemed

Event Date

Quarter

Quarter-year that the benefit was received\redeemed

Event Date

Year

Year that the benefit was received\redeemed

Event Date

Date

Date that the benefit was received\redeemed (the date format is based on the business territory)

Event Date

Time

Date and time that the benefit was received\redeemed (the date-time format is based on the business territory)

Event Date

Week

Week number that the benefit was received\redeemed (the week starts on Sunday or Monday based on the business territory)

General Information

Benefit Name

The name of the benefit template

General Information

Benefit Type

Whether the benefit is a gift or punch card

General Information

Benefit Status

Whether the benefit is active or not

Additional Information

Benefit ID

Identifier of the benefit template (instead of specifying the benefit by name)—helpful if you have multiple benefits with the same name

Additional Information

Benefit Key

Identifier representing a specific benefit that the member received (so if you send the Free Coffee Gift to 2 members, each gift will have a unique key)

Additional Information

Redeem Code

The redeem code (relevant for promo codes)

Additional Information

Event Source

Whether the receive\redeem were from POS, automation, client, etc.

 

Points\Credit

Category

Dimension

Description

Event Date

Day of week

Weekday that the points\credit were received\used

Event Date

Month

Month-year that the points\credit were received\used

Event Date

Quarter

Quarter-year that the points\credit were received\used

Event Date

Year

Year that the points\credit were received\used

Event Date

Date

Date that the points\credit were received\used (the date format is based on the business territory)

Event Date

Time

Date and time that the points\credit were received\used (the date-time format is based on the business territory)

Event Date

Week

Week number that the points\credit were received\used (the week starts on Sunday or Monday based on the business territory)

Additional Information

Event Source

Whether the receive\used were from POS, automation, client, etc.

Additional Information

Operation

Whether the operation was pay, cancel pay, add points, etc.

 

Punch Cards

Category

Dimension

Description

Event Date

Day of week

Weekday that the punch card was received\punched\\redeemed

Event Date

Month

Month-year that the punch card was received\punched\\redeemed

Event Date

Quarter

Quarter-year that the punch card was received\punched\\redeemed

Event Date

Year

Year that the punch card was received\punched\\redeemed

Event Date

Date

Date that the punch card was received\punched\\redeemed (the date format is based on the business territory)

Event Date

Time

Date and time that punch card was received\punched\\redeemed (the date-time format is based on the business territory)

Event Date

Week

Week number that punch card was received\punched\\redeemed (the week starts on Sunday or Monday based on the business territory)

General Information

Benefit Name

The name of the punch card template

General Information

Benefit Status

Whether the punch card is active or not

Additional Information

Benefit ID

Identifier of the punch card template (instead of specifying the punch card by name)—helpful if you have multiple punch cards with the same name

Additional Information

Benefit Key

Identifier representing a specific punch card that the member received (so if you send the Coffee punch card to 2 members, each punch card will have a unique key)

 

Deals

Category

Dimension

Description

Event Date

Day of week

Weekday that the deal was used

Event Date

Month

Month-year that the deal was used

Event Date

Quarter

Quarter-year that the deal was used

Event Date

Year

Year that the deal was used

Event Date

Date

Date that the deal was used (the date format is based on the business territory)

Event Date

Time

Date and time that the deal was used (the date-time format is based on the business territory)

Event Date

Week

Week number that the deal was used (the week starts on Sunday or Monday based on the business territory)

General Information

Deal Name

The name of the Deal

General Information

Deal Discount

The discount amount of the deal

General Information

Deal Status

Whether the deal is active or not

Additional Information

Deal Key

Identifier of the deal (instead of specifying the deal by name)—helpful if you have multiple deals with the same name

Additional Information

POS Transaction ID

The transaction ID from the POS that the deal was used in

 

App Open

Category

Dimension

Description

Event Date

Day of week

Weekday that the app was opened

Event Date

Month

Month-year that the app was opened

Event Date

Quarter

Quarter-year that the app was opened

Event Date

Year

Year that the app was opened

Event Date

Date

Date that the app was opened (the date format is based on the business territory)

Event Date

Time

Date and time that the app was opened (the date-time format is based on the business territory)

Event Date

Week

Week number that the app was opened (the week starts on Sunday or Monday based on the business territory)

 

Emails

Category

Dimension

Description

Event Date

Day of week

Weekday that the email was sent

Event Date

Month

Month-year that the email was sent

Event Date

Quarter

Quarter-year that the email was sent

Event Date

Year

Year that the email was sent

Event Date

Date

Date that the email was sent (the date format is based on the business territory)

Event Date

Time

Date and time that the email was sent (the date-time format is based on the business territory)

Event Date

Week

Week number that the email was sent (the week starts on Sunday or Monday based on the business territory)

 

Email Source

Whether the email source is from automation, hub, etc.

 

Template Name

The name of the email theme

 

SMS

Category

Dimension

Description

Event Date

Day of week

Weekday that the SMS was sent

Event Date

Month

Month-year that the SMS was sent

Event Date

Quarter

Quarter-year that the SMS was sent

Event Date

Year

Year that the SMS was sent

Event Date

Date

Date that the SMS was sent (the date format is based on the business territory)

Event Date

Time

Date and time that the SMS was sent (the date-time format is based on the business territory)

Event Date

Week

Week number that the SMS was sent (the week starts on Sunday or Monday based on the business territory)

 

Activity Name

The name of the activity that triggered the SMS (through one time)

 

Rule Name

The name of the rule that triggered the SMS (through automation)

 

SMS Content

The content of the SMS

 

SMS Provider

The provider that the SMS was sent via him

 

SMS Source

Whether the SMS source is from hub, POS, Client, ETC

 

SMS Type

Whether the SMS is operational or marketing

 

Joined with Referral Code

Category

Dimension

Description

Event Date

Day of week

Weekday that the member joined with referral code

Event Date

Month

Month-year that the member joined with referral code

Event Date

Quarter

Quarter-year that the member joined with referral code

Event Date

Year

Year that the member joined with referral code

Event Date

Date

Date that the member joined with referral code (the date format is based on the business territory)

Event Date

Time

Date and time that the member joined with referral code (the date-time format is based on the business territory)

Event Date

Week

Week number that the member joined with referral code (the week starts on Sunday or Monday based on the business territory)

 

Referral Code

The code that was generated when a member invited other to join the club

 

Measures

Measures are the quantitative measurements of your data. Different measures are available based on the report type you choose. You can add those measures to the report and also filter on those measures.

Purchases (Ticket)

Category

Measure

Description

Calculation

General Measures

Sales (Ticket)

Total revenue from purchases (total spend)

 

General Measures

Total General Discount

Total general discount from purchases

 

General Measures

# Visits (count)

Number of purchases (only purchases with 0 or more invoice sum)

 

General Measures

Avg. Spend per Visit (Ticket)

Average amount spent per purchase

Sales (ticket)/# Visits (count)

Members Measures

Sales by Members (Ticket)

Total revenue from purchases made by members (total spend)

 

Members Measures

% Sales by Members (Ticket)

Percentage of revenue (spend) from members

Sales by Members (Ticket)/ Sales (Ticket)

Members Measures

# Visits by Members (Count)

Number of purchases made by members (only purchases with 0 or more invoice sum)

 

Members Measures

% Visits by Members

Percentage of purchases made by members

# Visits by Members (Count)/ # Visits (count)

Members Measures

# Buying Members (Count)

Number of members who made a purchase

 

Members Measures

Avg. Spend per Member (Ticket)

Average amount spent per member

Sales by Members (Ticket)/# Buying Members

Members Measures

Visit Frequency

Average number of purchases per member

# Visits by Members (Count)/# Buying Members (Count)

Sales with Deals Measures

Sales with Deals

Total revenue from purchases that contain deals used (total spend)

 

Sales with Deals Measures

# Visits with Deals (Count)

Number of purchases that contain deals used (only purchases with 0 or more invoice sum)

 

Sales with Deals Measures

Avg. Spend per Visit with Deal

Average amount spent per purchase that contain deals used

Sales with Deals/# Visits with Deals (count)

Sales with Deals Measures

# Members Enjoyed Deals (Count)

Number of members who used a deal

 

 

Items

Category

Measure

Description

Calculation

 

Sales (Item)

Total revenue from items in purchases (total spend)

 

 

Total Item Discount (Item)

Total item discount

 

 

# Units Sold (Count)

Number of items sold

 

 

Avg. Spend per Visit (Item)

Average amount spent (from items) per purchase

Sales (Item)/# Visits (count)

 

Avg. Units Sold per Visit

Average number of items per purchase (i.e. cart)

# Units Sold (count)/# Visits (count)

 

Avg. Price per Unit Sold

Average amount spent on each item purchased

Sales (Item)/# Units Sold (Count)

 

% Sales by Members (Item)

Percentage of revenue (spend) from items in purchases made by members

Sales by members (Item)/ Sales (Item)

 

Sales with Deals (Item)

Total revenue from items in purchases that contain deals used (total spend)

 

 

Note: Ticket vs Item Measures

Sales and spend can be measured in two ways. If the data is filtered by a specific item, Sales (Ticket) include the total amount spent on purchases which included the item, while Sales (Item) include only the amount spent on this specific item. Even without a filter, these measures can be different based on how the POS reports discounts if the POS excludes specific items (like delivery fees) or rounding.

 

Benefits

Category

Measure

Description

 

# Received (Count)

Number of benefits that were received

 

# Redeems (Count)

Number of benefits that were redeemed

 

% Redeemed

Percentage of received benefits that were redeemed (redemption rate)

 

# Members that Redeemed (Count)

Number of distinct members that redeemed a benefit

 

Avg. Days Until Redeem

Average number of days it takes for a member to redeem their benefit

 

Points\Credit

Category

Measure

Description

Calculation

Points

Received Points

Total received points

 

Points

Used Points

Total used points (shows negative so if you filter on this measure make sure to filter on less than -100 instead of more than 100)

 

Points

Point Balance

Total received points minus used points

Received Points-Used Points

Credit

Received Credit

Total received credit

 

Credit

Used Credit

Total used credit (shows negative so if you filter on this measure make sure to filter on less than -100 instead of more than 100)

 

Credit

Credit Balance

Total received credit minus used credit

Received Credit-Used Credit

 

Punch Cards

Category

Measure

Description

Calculation

 

# Punches (Count)

Total Number of used punches in punch cards

 

 

Total Slots in Punch Card

Total slots in punch card (the size of the punch card)

 

 

% Punched

Percentage of punches used out of total slots

# Punches (Count)\ Total Slots in Punch Card

 

Punches Left

Number of unused punches left in punch card

Total Slots in Punch Card- # Punches (Count)

 

# Punch Cards Received (Count)

Number of punch card that were received

 

 

# Punch Cards Redeemed (Count)

Number of punch cards that were redeemed

 

 

# Full Unredeemed Punch Cards (Count)

Number of full (completed) punch card that are still unredeemed

 

 

Avg. Days Between Punches

Average number of days it takes for a member to punch a punch card

 

 

Avg. Days from Receive to Redeem

Average number of days it takes for a member to redeem their punch card since they received it

 

 

Avg. Days from Last Punch to redeem

Average number of days it takes for a member to redeem their punch card since it’s completed

 

 

Deals

Category

Measure

Description

 

# Deals (Count)

Number of times deal was used

 

# Unique Deals (Count)

Number of distinct deals that were used

 

Total Deals Discount

Total discount of deals that were used

 

App Open

Category

Measure

Description

 

# App Opens

Number of times the app was opened

 

# Users that Opened App

Number of distinct members that opened the app

 

Emails

Category

Measure

Description

 

# Emails sent

Number of emails that were sent

 

# Emails Reached Inbox

Number of emails that reached inbox

 

# Emails Didn’t Reached Inbox

Number of emails that didn’t reach inbox

 

# Emails Opened

Number of emails that were opened by members

 

# Emails Clicked

Number of emails that the members clicked the link inside them

 

# Emails Marked as Spam

Number of emails that were marked as spam by the members

 

# Unsubscribed from Email

Number of emails that members unsubscribe from

 

SMS

Category

Measure

Description

 

# Billing SMS

Number of SMS that were sent (for each provider this number is being calculated differently based on its provider logic and requirements)

 

# SMS

Number of SMS that were sent (for all providers each SMS is being count only once)

 

Members

Category

Measure

Description

 

# Members (Count)

Number of distinct members

 

Joined with Referral Code

Category

Measure

Description

 

# Joined Members

Number of distinct members who joined the club with referral code

 

Examples

Sales in 2019 by month

  • Report type: Sales.
  • Filters: Year > 2019.
  • Dimensions: Month.
  • Measures: Sales (Ticket), Avg. Spend per Member (Ticket), Buying Members (Count).
  1.  mceclip23.png

 

Credit in 2019 by Month

  • Report type: Points/Credit.
  • Filters: Year > 2019.
  • Dimension: Month.
  • Measures: Used Credit, Credit Balance.

 mceclip24.png

 

Received Bottle of Cava by member

  • Report Type: Benefit.
  • Filters: Benefit Name > Birthday: Bottle of Cava.
  • Dimension: Benefit Name, Phone.
  • Measures: Received (Count).

 mceclip25.png

 

 

 

 

Updated
Was this article helpful?
0 out of 0 found this helpful