How to Create a Travel Expense Report in Looker with AI
Creating a travel expense report can feel like piecing together a puzzle with incomplete information. When done right, however, it gives you a clear and powerful view of where your budget is going, helping you cut costs and plan for the future. This article will walk you through the entire process of building a detailed travel expense report in Looker and show you how modern AI tools are making this task faster and more accessible for everyone on your team.
Why Your Business Needs a Travel Expense Dashboard
Before jumping into the setup, it’s worth clarifying what you gain from a dynamic travel expense dashboard versus a static spreadsheet you email around once a month. A real-time dashboard turns raw numbers into actionable insights, helping you to:
Control Your Budget: Instantly see if spending is on track, over, or under budget. You can spot potential issues early instead of waiting for a monthly summary when it’s too late.
Identify Spending Trends: Are flight costs creeping up? Is one department spending significantly more on hotels than others? A dashboard makes these trends obvious at a glance.
Ensure Policy Compliance: Quickly flag expenses that are out of policy, such as first-class flight bookings or spending that exceeds per diem limits.
Streamline Reimbursements: By visualizing the status of expense reports (e.g., submitted, approved, paid), you can identify bottlenecks in the reimbursement process and ensure employees are paid back promptly.
Make Data-Driven Decisions: Use historical spending data to forecast future travel budgets accurately and negotiate better rates with preferred airlines or hotel chains.
Step 1: Get Your Data Ready for Looker
The success of any dashboard hinges on the quality and structure of the underlying data. Looker needs clean, organized data to work its magic. This preparation phase is the most critical and often the most time-consuming part of the process.
Gather Your Expense Data Sources
First, figure out where all your travel expense information lives. For most companies, it's scattered across multiple places:
Expense Management Software: Platforms like Expensify, SAP Concur, or Ramp are common sources for detailed expense records.
Company Credit Card Statements: These provide line-by-line transaction data.
Manual Spreadsheets: Many teams still rely on Excel or Google Sheets for ad-hoc expense tracking.
Invoices and Receipts: Sometimes you'll need to pull data directly from digital or paper receipts.
Your goal is to consolidate all of this data into a single, unified source of truth. The easiest way to do this for Looker is by creating a master spreadsheet or a dedicated database table.
Structure Your Data in a Central Hub
Once you’ve gathered your sources, you need to structure the data with consistent columns. Whether you're using Google Sheets, Excel, or a database, a clean structure is non-negotiable. Here’s a recommended format:
Expense ID: A unique identifier for each transaction.
Transaction Date: The date the expense occurred.
Employee Name: The name of the person who incurred the expense.
Department: The team the employee belongs to (e.g., Sales, Marketing, Engineering).
Expense Category: A standardized category for the expense (e.g., Airfare, Hotel, Meals, Ground Transport, Client Entertainment).
Amount: The monetary value of the expense.
Currency: The currency the expense was paid in (e.g., USD, EUR, GBP).
Location: The city and/or country where the expense took place.
Project/Client: If the travel was for a specific project or client, note that here.
Reimbursement Status: The current state of the reimbursement (e.g., Pending, Approved, Paid).
Description: A brief note about the expense (e.g., "Dinner with Acme Corp team").
Pro Tip: Consistency is everything. Make sure everyone uses the same term for the same thing - for example, "Airfare" instead of "Flights" or "Plane tickets." Inconsistent naming will split your data and make your charts inaccurate.
Connect Your Data to Looker
Looker doesn't import files directly like Excel. It connects to a SQL database where your data is stored. This means your beautifully structured spreadsheet still needs one more stop before it can be visualized.
You’ll need to load your data into a cloud data warehouse like Google BigQuery, Snowflake, Amazon Redshift, or a similar SQL database. This step often requires help from an IT or data engineering team. They will set up the pipeline to move your expense data from its source (like that Google Sheet) into the database that Looker can access.
Step 2: Building Your Travel Expense Report in Looker
With your data prepped and connected, you’re ready to start building inside Looker. This process involves defining your data with LookML and then creating visualizations.
Defining Your Model with LookML
At the heart of Looker is LookML (Looker Modeling Language). It's a layer of code that sits between your database and your dashboards. In simple terms, you use LookML to tell Looker what your data means and how the tables relate to each other.
You’ll define two key things:
Dimensions: These are the fields you group by - the "who," "what," and "where" of your data. Examples include
Employee Name,Expense Category, andLocation.Measures: These are the things you calculate or count, such as the total amount spent, the average cost per trip, or the number of expenses.
Learning an entire modeling language can feel daunting, as it requires a specific skillset that goes beyond typical business analysis. You'll likely be working closely with a data analyst or Looker developer during this phase.
Creating an "Explore" for Your Team
Once your LookML model is defined, you can create an "Explore." An Explore is a user-friendly starting point for querying data. It presents the dimensions and measures you defined in a list, allowing you (and your team) to select the fields you need to answer specific questions without writing any SQL.
For your travel expense report, your Explore would contain all the useful fields like Total Spend, Average Hotel Cost, Employee Department, and so on.
Visualize Your Key Metrics
Now for the fun part: creating the charts and graphs for your dashboard. In the Explore interface, you'll select a combination of dimensions and measures, and then choose a visualization type. Here are some essential charts for any travel expense dashboard:
Total Spend by Category
What it tells you: How your travel budget is allocated across airfare, hotels, meals, etc.How to build it: Use a pie or bar chart. Select Expense Category as your dimension and Sum of Amount as your measure.
Spend Over Time
What it tells you: Your company’s travel spending patterns on a monthly or quarterly basis.How to build it: Use a line chart. Select Transaction Date (grouped by month or week) as your dimension and Sum of Amount as your measure.
Top Spenders by Employee or Department
What it tells you: Which individuals or teams are driving the highest travel costs.How to build it: Use a horizontal bar chart. Select Employee Name or Department as the dimension and Sum of Amount as the measure. Sort in descending order.
Spend by Location
What it tells you: The geographical distribution of your travel expenses.How to build it: Use a map visualization. Select Location (city or country) as the dimension and Sum of Amount as the measure. This can highlight regions where you might want to negotiate corporate rates.
A Faster Approach: Using AI for Your Reporting
Building a dashboard in a traditional BI tool like Looker is powerful, but it's not without its challenges. The process is often slow, requires technical expertise, and isn't very flexible for non-data professionals.
The data prep is manual and requires extreme attention to detail.
Setting up and maintaining a data pipeline and database can be costly and requires dedicated engineering resources.
Learning LookML has a steep learning curve, creating a bottleneck where only a few "data people" can create or edit reports.
If you have a follow-up question, you often have to go back to an analyst to modify the report, which adds delays.
This is where new AI-powered analytics tools are changing the game. Imagine if you could skip the manual modeling and complex interface and simply describe the report you want in plain English.
With modern AI analytics platforms, you directly connect your data source - like a Google Sheet of expenses or your QuickBooks account - and then start asking questions. For example, you could type prompts like:
"Create a pie chart showing total expenses by category for the last quarter."
"What was our recurring monthly travel spend over the past 12 months? Show me as a line chart."
"Which 5 employees had the highest travel and expense costs in Q3?"
"Compare travel spend between the Sales and Marketing departments for this year."
The AI handles the heavy lifting, instantly generating the visualizations you need. This approach flips the model on its head. Instead of conforming to the tool's structured workflow, the tool adapts to your natural way of thinking and asking questions. It makes data analysis conversational and immediate, empowering anyone on your team to find an answer in seconds.
Final Thoughts
Building a travel expense report, whether in Looker or another tool, is essential for maintaining financial health and making smarter decisions. The traditional BI path gives you a robust, customized solution but demands significant upfront investment in data setup, technical modeling, and training.
At Graphed, we’ve built a platform to completely automate this process. We believe getting insights from your data shouldn't require a data engineering degree. By connecting your sources and using natural language, you can build an entire travel expense dashboard in less time than it takes to drink your morning coffee. This allows you to spend your time acting on insights, not chasing them.