Assignment 4: Visitor Behavior and Time Trends

BUS220 — Business Intelligence and Analytics

Overview

Same Google Merchandise Store data as Assignment 3. The store manager liked your funnel dashboard and now wants to understand visitors, not just sessions, and see how the business is trending over time.

This assignment uses LOD expressions (FIXED and EXCLUDE) for visitor-level and cross-grain metrics, and table calculations for time-based trends.

Total: 8 points + 2 bonus points

Deadline: See Moodle. Late policy: 1–7 days = 50% credit, >7 days = 0.

---

Deliverables

Submit in Moodle (file upload + submission text box):

1. Tableau workbook (.twbx) containing your worksheets
2. Moodle submission text with:
   • Visitor-level findings (Part 1)
   • Channel revenue share observations (Part 2)
   • Time trend observations (Part 3)

WarningSubmission rules

• File uploads only. No external links. -1 point deduction for links instead of files.
• Worksheet naming. Descriptive tab names, not “Sheet 1.”

File naming: LastName_FirstName_assignment_4.twbx

NoteUseful references

You have already been assigned the foundational chapters for this assignment (PT Ch13 on table calculations, PT Ch16 on LOD expressions). If you want additional reference while working:

• PT Ch59 — Using LOD Expressions to Create Benchmarks. Walks through EXCLUDE for share-of-total calculations — the same pattern you will use in Part 2.
• BBoD Ch10 — Showing Year-to-Date and Year-over-Year at the Same Time. Running totals and period comparisons — directly relevant to Part 3.
• BBoD Ch24 — Showing Churn or Turnover. Cohort-style analysis — relevant if you attempt the bonus.

---

Setup

Use the same three CSV files from Assignment 3 (sessions, hits, products). You can continue in your A3 workbook or start a new one. Either way, you need the same data model:

• Sessions -> Hits on uniqueVisitId
• Hits -> Products on hitId

You will also need [Revenue USD] from A3 (micro-currency / 1,000,000).

Visitor ID

In A3, you worked at the session level using uniqueVisitId. Now you need to identify visitors — people who come back across multiple sessions.

Recall that uniqueVisitId has the structure browserID:sessionTimestamp. The part before the colon is the same across all sessions from the same browser. Create a calculated field:

[Visitor ID]: extract the browser ID from uniqueVisitId (everything before the :).

Tip

Hint: LEFT([uniqueVisitId], FIND([uniqueVisitId], ":") - 1) extracts the part before the colon.

Quick check: you should get ~22,300 unique visitors from ~27,400 sessions. If your numbers are very different, revisit your formula.

---

Part 1: Visitor-Level Metrics with FIXED (3 points)

A session is a single visit. A visitor is a person who may have come once or a hundred times. Metrics that look one way at the session level can look quite different at the visitor level.

FIXED LOD expressions let you compute at the visitor grain regardless of what’s in the view. The syntax: {FIXED [dimension] : aggregation([measure])}.

1.1 Create visitor metrics

Create these calculated fields using FIXED on [Visitor ID]:

• [Visitor Sessions]: how many sessions does each visitor have?
• [Visitor Revenue]: total revenue per visitor across all their sessions
• [First Visit Date]: the date of this visitor’s earliest session

1.2 New vs. returning visitors

Create [Visitor Type] that classifies each session as belonging to a new or returning visitor.

Tip

Hint: The sessions table has a field that tracks how many times this visitor has come to the site. Think about what value that field has on someone’s very first visit.

Build a worksheet showing how sessions and revenue split between new and returning visitors.

In your Moodle submission, write 2–3 sentences: how do the two splits compare, and what does the difference mean for the store?

---

Part 2: Channel Revenue Share with EXCLUDE (2 points)

FIXED computes at exactly the dimensions you specify, ignoring whatever is in the view. EXCLUDE works differently: it takes the dimensions already in your view and removes one, giving you a coarser-grain total you can use as a benchmark or denominator.

The question

The store manager asks: “How does each channel’s share of revenue change month to month?”

You have a view with two dimensions: channel and month. You want each channel’s percentage of that month’s total revenue. The denominator (monthly total) needs to ignore the channel dimension while keeping the month.

Build it

You need two calculated fields:

1. [Monthly Revenue] — total revenue for the month, ignoring the channel split. This is where EXCLUDE comes in: you want an LOD expression that removes channelGrouping from the grain.
2. [Channel Revenue Share] — each channel’s revenue as a fraction of the monthly total.

Build a worksheet showing how channel revenue shares shift across the 5 months. Format as percentages.

Note

You could also compute this with a Percent of Total table calculation. The result looks the same, but there’s a practical difference: the LOD field is a stable calculated field you can reuse in tooltips, filters, and other worksheets. A table calculation is tied to the specific view it’s computed in. Both approaches are valid; know when to reach for each.

In your Moodle submission, write 2–3 sentences: do channel shares stay stable or shift over the 5 months? Does any channel gain or lose ground heading into the holidays?

---

Part 3: Time Trends with Table Calculations (3 points)

3.1 Weekly trends

Build a worksheet showing weekly sessions over August–December 2016. Use WEEK(date) or a date truncated to week as your time axis.

Add table calculations:

• Week-over-week % change for sessions
• Cumulative revenue as a running total

Tip

Hint: Tableau’s quick table calculations include one that computes percentage change from the previous value. Look for it in the right-click menu on a measure.

Compare the session trend to the revenue trend — they may not behave the same way. If weekly revenue looks noisy, check how many transactions actually happen per week. Low-volume metrics can be volatile at fine time granularities.

3.2 New vs. returning mix over time

Build a worksheet showing the share of sessions from new vs. returning visitors for each month. The result should show whether the store is consistently acquiring new visitors or increasingly relying on returning ones.

Tip

Hint: You solved a similar problem in A2 Worksheet 2 (education level shares within each course). The same technique applies here with different dimensions.

In your Moodle submission, write 2–3 sentences covering both worksheets: what do the weekly trends reveal about the holiday season? Does the new/returning balance shift over the 5 months, and what does that suggest about the store’s traffic?

---

Part 4: Cohort Retention (2 bonus points)

“Do visitors who first arrive in August behave differently from those who arrive in November?”

Build a cohort retention grid:

1. Truncate [First Visit Date] to month — this is the cohort month
2. Calculate the number of months between [First Visit Date] and each session’s date (use DATEDIFF)
3. For each cohort, show the percentage of visitors who returned in each subsequent month

The result is a grid: rows are cohort months (Aug, Sep, Oct, Nov, Dec), columns are months since first visit (0, 1, 2, …), cells show the return rate as a color-encoded percentage.

Tip

You will need:

• A COUNTD of [Visitor ID] for each cohort x months-since-first-visit cell
• A table calculation to divide each cell by the cohort’s month-0 count

This is one of the harder visualizations to build in Tableau. A simpler version — just showing what percentage of each cohort’s visitors returned at all, without the month-by-month grid — is worth partial credit.

In your Moodle submission, write 2–3 sentences on what the cohort view reveals.

---

Grading

Part	Points
1. Visitor-Level Metrics (FIXED LOD)	3
2. Channel Revenue Share (EXCLUDE LOD)	2
3. Time Trends (table calculations)	3
4. Cohort Retention (bonus)	+2

What loses points:

• Session-level metrics where visitor-level is asked. If your “revenue per visitor” is actually revenue per session, the numbers and interpretation will both be wrong.
• LOD at the wrong grain. If a FIXED expression doesn’t produce one value per visitor, downstream calculations will be unreliable. Test LOD fields in a text table before using them in charts.
• Table calculations along the wrong dimension. A running total that resets at each category, or a percent-of-total that sums to 100% across months instead of within each month, means the Compute Using is misconfigured.
• Default design: “SUM of…” tooltips, “Sheet 1” tabs.

---

AI Usage

You may use AI tools to help with Tableau syntax (LOD expressions, table calculation formulas) or debugging unexpected results. If using AI, mention it in your submission. The analytical choices and written interpretation must be your own.