LinkedIn
Twitter
Hi all, I wanted to share a simple solution for period-over-period and pacing reporting.
Period-Over-Period
1. Start with adding the date field you would like to use in LookML:
2. Create a version of this same date, but with 364 days added to it (this ensures you are referencing the same day-of-week).
3. Create a new Look that aggregates the data you want to report on period-over-period (We'll call it Look A). For this example, let's say we're summing up revenue data. Select "Merge Results" using the gear at the top right to merge to an identical report (Look B). Save this merged results report to a new dashboard.
4. Create a dashboard date filter (Filter A) that is applied to Look A on the sale_date field, and Look B on the pop_364_days field. Name it "Activity Period".
5. You can now change Filter A to any timeframe you want, and you'll have columns with current-year revenue and prior-year revenue data in the underlying merged query. You can then add a new calculation field that compares the two.
Pacing
5. To add pacing, you'll need to create another identical report to the existing merged query (Look C).
6. Add a second dashboard date filter (Filter B), and apply it to the pop_364_days field for Look C . Remove links to Look A and Look B. Name it "Full Period".
7. Edit Filter A so that it does not link to Look C.
8. Set Filter A to a period you are interested in. For example, let's say today is January 15th, and we want to see how we're pacing for the quarter. Set Filter A to January 1 through January 15th. Next, set Filter B to the full period you want to calculate pacing for (in this case, January 1 through March 31).
9. After reloading the dashboard, you will now have revenue values for
• current year quarter-to-date
• prior year quarter-to-date
• prior year full quarter
10. Create a new calculation field within the merged query that divides Look C revenue (last year's total quarter revenue) by Look B revenue (prior year quarter-to-date), and call it "Pacing Multiplier".
11. Create another calculation field that multiplies this "Pacing Multiplier" value by Look A revenue. Call this "Pacing Estimate". This will give you a pacing estimate for total revenue this quarter.
12. You can then create another calculation field to compare Look C revenue (last year's total quarter revenue) to your "Pacing Estimate" to see how well you are performing so far this quarter vs last year.
This is just a basic example of this approach. You can create different PoP fields with different lookbacks (e.g. vs. last week), or even give a single PoP a dynamic lookback value controlled by a separate "lookback days" parameter filter.
-Pete
