How to Create a Travel Expense Report in Looker

Building a travel expense report in Looker transforms a painful, manual task into an insightful, automated process. Instead of drowning in spreadsheets and chasing down receipts, you can create a dynamic dashboard that gives you a crystal-clear view of where your company’s money is going. This guide will walk you through setting up your data, building the foundational LookML model, and creating an interactive report to analyze travel spending effectively.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Build Your Travel Expense Report in Looker?

If you're still relying on spreadsheets for expense reporting, you're familiar with the headaches: manual data entry, disconnected information, version control issues, and zero real-time visibility. Reports are often outdated the moment they’re finished. This manual process is not only slow but also prone to errors that can impact your budget analysis.

Looker, on the other hand, connects directly to your company's data warehouse, acting as a single source of truth for all your expense data. Here’s why it’s a better approach:

• Automation: Once connected, Looker dashboards update automatically as new expense data flows in. No more manually downloading CSVs every Monday morning.
• Centralization: All of your travel and expense data lives in one place, providing a unified view for everyone from department heads to the finance team.
• Interactivity: Users can filter, sort, and drill down into the data to answer their own questions. Wondering why the marketing department's travel costs spiked last month? Just click on the chart to see the individual expenses.
• Governance: Looker's data modeling layer (LookML) allows you to define business logic and metrics once, ensuring everyone in the organization uses the same calculations for key metrics like "Total Spend" or "Average Cost Per Trip."

By moving your expense reporting to Looker, you shift from reactive data entry to proactive financial management, allowing you to spot trends, control costs, and make smarter budget decisions.

Step 1: Get Your Data Ready

Before you can build anything in Looker, you need a solid foundation of clean, organized data. Your travel expense information might be stored in an expense management platform like Expensify or SAP Concur, a dedicated accounting system, or even a structured database table that gets populated from various sources. To get started, make sure this data is available in a SQL database that Looker can connect to, such as Google BigQuery, Snowflake, or Amazon Redshift.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Essential Data Fields for Your Report

Your expenses table should ideally contain the following columns. The more detailed your data, the more powerful your report will be.

• Expense ID: A unique identifier for each expense line item.
• Employee ID/Name: Who submitted the expense.
• Department: The employee's department (e.g., Sales, Marketing, Engineering).
• Report ID: An identifier that groups individual expenses into a single trip or submission.
• Submission Date: The date the expense report was submitted.
• Expense Date: The actual date the expense occurred.
• Merchant: The name of the vendor (e.g., Delta Airlines, Marriott, Uber).
• Category: A standardized category for the expense (e.g., Airfare, Accommodation, Meals, Ground Transport, Other).
• Description: A brief note about the purpose of the expense.
• Amount: The total cost of the expense.
• Currency: The currency of the expense.

Pro Tip: It’s crucial to standardize your categories. Work with your finance team to create a definitive list. This prevents ambiguity and ensures that expenses for "Taxi," "Uber," and "Lyft" are all consistently labeled as "Ground Transport" for accurate reporting.

Step 2: Define Your Data with LookML

Once your data is in an accessible database, the next step is to teach Looker how to understand it. This is done using LookML, Looker’s modeling language. LookML is where you define your dimensions (the "what" or "who" data, like Employee Name or Category) and measures (the calculated data, like Total Spending or Average Cost).

Don’t worry if you’ve never written LookML before. It’s designed to be readable and intuitive. Here’s how you'd set up a basic model for your travel expenses.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Creating an 'Expenses' View

In Looker, a "view" represents a single table in your database. You’ll create a view file for your expenses table to define all its fields.

Here’s an example expenses.view.lkml file:

view: expenses {
  sql_table_name: `your_schema.travel_expenses` ,,

  dimension: expense_id {
    primary_key: yes
    type: string
    sql: ${TABLE}.expense_id ,,
  }

  dimension: employee_name {
    type: string
    sql: ${TABLE}.employee_name ,,
  }

  dimension: department {
    type: string
    sql: ${TABLE}.department ,,
  }

  dimension: category {
    type: string
    sql: ${TABLE}.category ,,
  }

  dimension_group: expense_date {
    type: time
    timeframes: [raw, date, week, month, quarter, year]
    sql: ${TABLE}.expense_date ,,
  }

  dimension: amount {
    type: number
    value_format_name: usd_2
    sql: ${TABLE}.amount ,,
  }

  measure: total_spend {
    type: sum
    sql: ${amount} ,,
    value_format_name: usd_2
  }

  measure: expense_count {
    type: count
    detail: [expense_id, employee_name, expense_date]
  }

  measure: average_spend_per_employee {
    type: average
    sql: ${amount} ,,
    value_format_name: usd_2
  }
}

In this simple example, we’ve defined our columns as dimensions and created a few key measures to aggregate the data. The dimension_group for expense_date is especially useful, as Looker automatically creates different timeframes (date, week, month, etc.) you can use for filtering and visualization.

Defining the Model and Explore

Next, you create a "model" file to connect your views and define "Explores." An Explore is the starting point for users to query data in the Looker UI.

Your travel_reporting.model.lkml file would look something like this:

connection: "your_database_connection"

include: "/views/*.view.lkml"

explore: expenses {}

With this simple setup, users can now go to the "Explore" section in Looker, select "Expenses," and start pulling data by combining dimensions and measures.

Step 3: Build Your Interactive Dashboard

This is where your expense report comes to life. A Looker dashboard is a collection of "tiles," which can be charts, tables, text, or single-value visualizations. The goal is to present the most important information in an easy-to-digest format.

Start by creating a new dashboard and adding tiles that answer key business questions.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Essential Components for Your Expense Dashboard

A good travel expense dashboard provides a high-level overview while allowing for detailed exploration. Here are some critical tiles to include:

• Total Travel Spend (KPI Tile): A single value tile showing the total amount spent for a given period (e.g., this quarter or year-to-date). This is your main headline number.
• Average Expense Report Value (KPI Tile): Another single value tile that displays the average amount per submitted report. This helps you track an important part of cost compliance.
• Spend Over Time (Line Chart): Plot Total Spend by Expense Date Month to see trends, seasonality, and the impact of business events like conferences or team offsites.
• Spend by Department (Bar or Pie Chart): Visualize Total Spend by the Department dimension. This quickly shows which teams are driving the most travel costs. A bar chart is often clearer than a pie chart if you have many departments.
• Spend by Expense Category (Pie Chart): Group Total Spend by Category. This helps you see if costs are concentrated in airfare, accommodation, or other areas, highlighting potential areas for savings.
• Top 10 Spenders (Table): A table showing Employee Name and Total Spend, filtered to the top 10. This is useful for finance and department leads to monitor individual spending habits.

Add Filters for Deeper Analysis

Dashboards become truly powerful when you add filters. Filters allow viewers to slice and dice the data without needing to create a new report. For a travel expense report, the most useful filters are:

• Date Range: Let users look at specific time periods, such as "last month," "this quarter," or a custom date range.
• Department: Allow department heads to view spending for their specific team.
• Employee Name: Enable managers to drill down into the expenses of a specific team member.
• Expense Category: Isolate spending on a particular category, like flights, across the entire company.

When you link these filters to all the tiles on your dashboard, a user can, for example, select the "Sales" department and a date range of "last quarter" to see every chart update in real time to reflect that specific view.

Tips for an Actionable Report

A beautiful dashboard is nice, but an actionable one is better. Here are a few ways to take your Looker report to the next level:

• Set Up Scheduled Deliveries: Automatically send a PDF of the finance team's dashboard to key stakeholders every Monday morning. Everyone stays informed without having to log into Looker.
• Use Alerts: Create alerts to get notified when certain thresholds are crossed. For example, Looker can send an email to a finance manager if an individual employee’s spending exceeds $10,000 in a single month.
• Implement Drill Downs: Enable drilling on your charts. When a user clicks a slice of a pie chart (e.g., "Airfare"), they can be taken to a detailed report showing every flight expense. This fosters self-service and reduces follow-up questions.
• Add Context: Use text tiles on your dashboard to explain what each chart shows or to define key metrics. This makes the report more accessible to users who aren't in the data every day.

Final Thoughts

Converting your travel expense reporting from manual spreadsheets to an automated Looker dashboard is a big step towards a more data-driven culture. It frees up your team from monotonous data wrangling and empowers them with real-time, interactive insights to manage costs, enforce policies, and forecast budgets more accurately.

While powerful, tools like Looker involve a learning curve, especially with the complexities of LookML and initial setup. For teams that want the insights without the steep climb, we’ve made analysis much simpler. With Graphed, we connect directly to your data sources, and you can build comprehensive, real-time dashboards just by asking questions in plain English - no coding required. The entire process becomes a 30-second conversation, giving you back the time to focus on what the data actually means for your business.