Temporal Joins define how you incorporate additional activities (join activities) to your dataset.

The Power of Temporal Joins

🧙‍♂️✨

Temporal Joins are the secret to working with the single-table time-series table.
They are incredibly powerful and allow you to construct any dataset you need using activities.

How they're used

They enable you to enrich your dataset with information about specific customer behaviors. Every dataset requires a primary activity to start. Once you've selected your primary activity, you can add additional fields about other activities using joins.

A relationship could help you add an attribute about a behavior your customer did (ie. lead source on the lead submitted) or an indicator of whether or not they did an activity (ie. 1/0 flag to indicate if they paid their invoice).

Types of Joins

---

FIRST EVER

Adds the FIRST EVER join activity for each customer in your dataset, regardless of when the primary activity occurred.

Useful when you want to...

• Segment your dataset by the first ad source (first touch attribution) (dataset example)
• Segment your dataset by the first item they ordered (split by customer tenure)
• Segment your dataset by the date of the first subscription (think retention curves)
• Segment your dataset by the first page the customer saw

LAST EVER

Adds the LAST EVER join activity for each customer in your dataset, regardless of when the primary activity occurred.

Useful when you want to...

• Segment your dataset by the last time the customer ordered (RFM analysis)
• Segment your dataset by the last time the customer came to the site
• Enrich your dataset with the last plan type they updated their account with
  
  
  

FIRST BEFORE

Adds the FIRST join activity, but only if it happened BEFORE the primary activity.

Useful when you want to...

• Enrich your dataset with the ad source from the first session, but only if that session happened before the first order was placed. (dataset example)

LAST BEFORE

Adds the LAST join activity that happened BEFORE the primary activity.

Useful when you want to...

• Enrich your dataset with the last support ticket that someone submitted before they churned (dataset example)
• Enrich your dataset with the last web page that they viewed before submitting a support ticket (dataset example)
• Segment your dataset by the last email that they viewed right before placing an order
• Segment your dataset by the ad source of the last session before they purchased (last touch attribution) (dataset example)

FIRST IN BETWEEN

Adds the FIRST join activity that happened IN BETWEEN that primary activity and the next instance of the primary activity. Said another way, append the first activity that happened before the next primary activity.

Example: "For each Started Session, add the first Completed Order that happened but only if it happened within that session (before the next session). "

Useful when you want to...

• Calculate a conversion rate from the primary activity to the join activity (dataset example)
• Enrich your dataset with the first order after a session started (dataset example)
• Find the first order that happened after each order occurred (Order Retention) (dataset example)

LAST IN BETWEEN

Adds the LAST join activity that happened IN BETWEEN that primary activity and the next instance of the primary activity. Said another way, append the last activity that happened before the next primary activity.

Example: "For each Started Session, add the last FAQ page that was viewed but only if it happened before the next session. "

Useful when you want to...

• Identify the action that caused a customer to leave/abandon.
• Find the last search term in between every started session (what caused the customer to leave)
• Find the last Viewed Ad in between every page view
• Find the last FAQ page of a customer's session

AGGREGATE IN BETWEEN

Adds an AGGREGATION of all the join activities that happened IN BETWEEN that primary activity and the next instance of the primary activity.

Example: "For each Started Session, include the count (agg) of pages that were viewed after the session started but before the next session started"

Useful when you want to...

• Enrich your dataset with the COUNT of all viewed pages in between started sessions (total page views per session) (dataset example)
• Enrich your dataset with the SUM of the revenue in between started subscriptions (revenue from a subscription
• Enrich your dataset with the COUNT of all events attended in between every Received Invoice (How many events did they attend per active month)
• Enrich your dataset with the COUNT DISTINCT of all Viewed Product Kind between each session (How many unique product kinds did the user view ) (dataset example)

AGGREGATE ALL EVER

Adds an AGGREGATION of ALL the join activities that happened EVER regardless of when the primary activity occurred.

Useful when you want to...

• Enrich your dataset with the SUM of the revenue from all Purchased Products (LTV)
• Enrich your dataset with the COUNT of all Paid Invoices (Total active months)
• Enrich your dataset with the COUNT of all Tickets (How many tickets did this customer submit)
  
  
  

AGGREGATE BEFORE

Adds an AGGREGATION of all the join activities that happened BEFORE the primary activity occurred.

Useful when you want to...

• Enrich your dataset with the COUNT of all Calls Submitted before a Started Subscription (How many calls does it take per subscription?) (dataset example)
• Enrich your dataset with the COUNT DISTINCT of all Session ad sources before the first purchase (Is there a media mix before first purchase?) (dataset example)
• Enrich your dataset with the SUM of the revenue before an upgrade (Does revenue affect likelihood to upgrade?)

AGGREGATE AFTER

Adds an AGGREGATION of ALL the join activities that happened AFTER the primary activity occurred (opposite of Aggregate Before).

📘

The Join relates that activity to the PRIMARY activity

Each join is interpreted in relation to the primary activity regardless of how many activities have been added to the dataset.

Still have questions?

---

Our data team is here to help! Here are a couple ways to get in touch...

💬 Chat us from within Narrator

💌 Email us at [email protected]

🗓 Or schedule a 15 minute meeting with our data team