Better visualizations enable better—and faster—decision-making. After spending hundreds of hours in client data, we’ve developed some charts to help us quickly understand what’s happening, and what adjustments we should make. These are a few of them.
Find Data Visualizations Faster:
Traffic
- In-Market vs. Out-of-Market Traffic
- Paid vs. Non-Paid Traffic
- Traffic (Or Guests or Revenue) By Location Band
Revenue
- Multi-Year Traffic or Revenue
- Share of Revenue By Source
- Revenue Or Guest Growth By Channel
- Guest Count or Revenue By Zip Code
- Revenue vs. Projections
Growth
Bookings
- Simple Daily Charts
- Unsold Capacity Value
- How Are Future Bookings Trending?
- Direct vs. Indirect Cost of Acquisition
Conversion Rate
Reviews
In-Market vs. Out-of-Market Traffic | 100% Stacked Area Chart
What percent of website visitors are visiting while they’re inside our market vs. outside our market? Or what percent of revenue comes from people in those different areas? Understanding what percent of our visitors are shopping before or during their trip—and how that changes over time—can help us tailor our messaging on the website and in ads. It can also be a potential indicator of gain or loss of local search visibility.
How To Create It
Determine which regions are in-market for you. In Google Data Studio, in your Google Analytics data source, create a custom field that assigns a value of In Market or Out of Market based on the region the traffic came from. Use that field as the breakdown dimension in your 100% stacked area chart.
Bonus: Conversion Rate, $/user, $/transaction, and More
Once you’ve defined your market location dimension, you can use it for additional reports.
Multi-Year Traffic or Revenue | Line Charts With Breakdown Dimensions
Typical charts provide year-over-year comparisons, but what if last year isn’t a good baseline? For many of our traffic and revenue charts, we’ve switched to 4-year versions. It’s also possible to remove years that aren’t relevant, if you want to get rid of the noise.
How To Create It
When you create your line chart, choose a 4-year time span. Use month as the date dimension and year as the breakdown dimension. Turn off any date comparisons, since you’re creating your own comparison.
Unsold Capacity Value | Heatmap
We previously showed how to create a heatmap showing capacity utilization. While this is helpful, it can create some false positives or false negatives, if capacity was low for a given time slot. For example, if we opened just 10 seats in all of August for the Sunday 9 am time slots and filled 6 of them, it looks like we have a big problem on Sunday mornings; but we may have left 10x the revenue on the table during the 11 am Saturday slots, where we filled 80% of seats but had significantly more capacity.
For this reason, we also visualize unsold capacity value (or, unrecognized revenue).
How To Create It
See our tutorial on Visualizing Tour Capacity. Add another field to your underlying data that multiplies the unsold seats by the average seat price. In your existing pivot table or a new one, use that new field.
Paid vs. Non-Paid Traffic | 100% Stacked Area Chart
How dependent are you on paid traffic?
How To Create It
In Google Data Studio, in your Google Analytics data source, create a custom field that assigns a value of either Paid or Non-Paid based on the default channel grouping. Use this as the breakdown dimension in your 100% stacked area chart.
Traffic (Or Guests or Revenue) By Location Band
Geographic reports in Google Analytics can be noisy. For many tourism clients, Region is too broad, but City is too detailed. That’s where location banding is helpful. We can then look at this data in a stacked area chart as shown here, but we can also look at things like conversion rate by market location.
How To Create It
Create a custom field called Market Location in Looker Studio, based on city and region fields.
How Are Future Bookings Trending?
It’s possible to visualize the state of this year’s bookings vs. last year’s. In this example, we’re looking at guest count from bookings created in January – May, and we can see that not only was May a good month, but also we’re trending ahead for most of the upcoming months as well. You could look at revenue in addition to guest count, and if you offer multiple experiences, you could filter by experience.
How To Create It
Your data source (a spreadsheet in our case) needs to include the date of the booking and the date of the experience. Limit the chart to show only bookings created within the selected date range, but use the experience date as the dimension of the chart.
Growth vs. Previous Year
A regular line chart can show growth or decline from the previous year (top right), but it can be helpful to also have a chart (bottom) that displays the growth rate alone.
How To Create It
We use a Google Sheets workbook with 2 sheets:
- Sheet 1: The detailed transactions or bookings report
- Sheet 2: Date, this year’s revenue, last year’s revenue—calculated from Sheet 1.
At that point, it’s easy to create a custom field in Looker Studio called Growth.
Share of Revenue By Source
Our clients often want to become less dependent on 3rd party bookings, to improve their margin and not be subject to sudden changes in a platform (ever dropped far down on an important Tripadvisor page?). This split is simple to visualize.
How To Create It
Booking platforms each have their own way of displaying source. We typically create a calculated spreadsheet column called Source Group, to ‘roll up’ the sources into Direct or 3rd Party groups.
Pivot Table Heatmaps
Pivot tables allow you to see many combinations of 2 dimensions. This example shows conversion rate by location and device category (for example, our Florida location on desktop, tablet, and mobile). You could also look at revenue by tour and revenue source (3rd party vs. online vs. in-office) or a number of other possibilities.
How To Create It
The “pivot table with heatmap” in Looker Studio works well for this, but this is easily created in Excel or Google Sheets too, using a pivot table with conditional formatting.
Direct vs. Indirect Cost of Acquisition
Tracking your cost of acquisition by channel (particularly direct vs. affiliate) can keep things in perspective as you increase your marketing investment to drive direct bookings.
How To Create It
To generate this report, we merged Fareharbor booking data (utilizing some of the affiliate fields), digital ad spend, and marketing labor costs.
Revenue Or Guest Growth By Channel
Look beyond overall revenue to see which channels are growing or declining.
How To Create It
Sheet 1: A bookings list with a custom field that assigns a value of Direct – Online, Direct – Offline, or Affiliate, based on the booking platform’s Source field.
Sheet 2:
- Column A: Date
- Column B: Channel Group (create 1 row per channel group, per day. In our example, each day then has 3 rows)
- Column C: Revenue This Year. We use a SUMIF formula.
- Column D: Revenue Last Year. Another SUMIF formula. (Watch out for leap days; a workaround is required.)
Sheet 2 becomes the data source, and Growth is a custom field based on Revenue This Year and Revenue Last Year.
Review Velocity | Time Series Combo Chart
If you’re trying to get more reviews, you’ll want to track the percentage of guests that leave reviews.
How To Create It
Use a blend in Looker Studio to merge booking data (which we store in Google Sheets) with Google Business Profile data (which we get via a data connector). Divide review count by guest count.
Guest Count or Revenue By Zip Code
If your booking data or waiver data contains a combination of zip codes and guest count, you can quickly create a map of that data. This can be useful in identifying areas where you may need additional marketing. This example uses a bubble map to show guest count by zip code, color-coded by business location for this multi-location company.
How To Create It
Use Excel’s 3D Maps feature. Try different options until you get the proper level of detail for your data set.
Revenue vs. Projections
If you have monthly revenue targets, it can be helpful to visualize your target and your progress. In this example, we also included a comparison to last year.
How To Create It
We use the same technique describe for other growth metrics shown in this post, but with the addition of projected revenue.
Sheet 1: Booking data (we export all bookings into Google Sheets monthly)
Sheet 2: Projection data by month
Sheet 3:
- Column A: Date
- Column B: Revenue This Year (using SUMIF)
- Column C: Revenue Last Year (using SUMIF)
- Column D: Projected Revenue (using SUMIF or XLOOKUP)
The comparison metrics can then be created as custom fields in Looker Studio.
KPI Gauges
Once you’ve decided on your KPIs, gauges can be a great way to keep an eye on your progress.
How To Create It
The gauge we used here is available as a Community Visualization in Looker Studio. Showing standard metrics is straightforward, but showing growth rates—such as the web revenue growth rate shown here—requires some extra work as mentioned previously in this article. In this case, none of the data requires manual exports from a booking platform, so the data auto-updates every day.
Simple Daily Charts
For many of our data visualizations, we’re looking at weekly or monthly intervals, because daily data is quite volatile and noisy. However, it does have its uses. This example shows a part of our page of daily GA4 data, which we use often, just to see how things are trending or whether there are any issues—such as after we’ve made a change to the website, booking engine, or measurement.
How To Create It
Simple column chart in Looker Studio, Google Sheets, or Excel. We use Looker Studio for this, with the standard Google Analytics connector.
