10 January, 2025 POR Oriol Ribó

Learn how to calculate users' purchase anticipation in Looker Studio with GA4 data.

For many businesses, understanding how far in advance users make purchases is key to analyzing their behavior and planning. This knowledge is especially valuable in sectors such as events, hospitality, services, catering... where anticipating customer decisions can make all the difference in strategic planning and resource optimization.

In this article, I will show you how to calculate this value using Looker Studio with a field that measures the difference in days between the purchase date and the session date. So for that, it will be essential to have an event parameter configured that tells us the session/booking date. 

*Let's assume that the session/booking date parameter is named session_date.

Step 1: Make sure your data is in date format

Before you begin, you need to confirm that both date fields (the date of purchase and the date of the session/booking) are set correctly as type "Date."

To verify:

  1. Go to your data settings in Looker Studio.
  2. Confirm that the Date (date of purchase) and the session/reservation date parameter are set to Date.
  3. If any of the fields appear as text or in a different format, change their data type to Date. If one of the fields is in text format (such as "dd/MM/yy"), you can convert it to date using the PARSE_DATE function. For this, create a new calculated field with the following formula:

PARSE_DATE("%d/%m/%y",date_session)

This step will transform the date_sesion field into a value recognizable as a date.

Step 2: Calculate the Anticipation in Days

Once both fields are correctly set as dates, you can calculate the anticipation in days between the purchase date and the session date.

To do so, follow these steps:

  1. Add a new calculated field by clicking on "Add a Field"
  2. Name the field, e.g., "Anticipated Days".

Enter the following formula to calculate the difference in days:
DATE_DIFF(PARSE_DATE("%d/%m/%y", date_sesion), Date)

Note that in this formula Date represents the date of purchase and date_sesion is the session date or the day for which the user has made the purchase.

Step 3: Interpretation of the results

If everything went correctly, the resulting value in the "Days in Advance" field shows us how many days before the session date the user made the purchase:

  • 0 means that the user purchased for the same day.
  • 1 indicates that the purchase was for the next day.
  • 2 or more positive values indicate that the user bought several days in advance.

With this calculated field we will be able to create tables or charts in Looker Studio to analyze user patterns and extract interesting insights.
ANTERIOR
SIGUIENTE

TIPS DE EXPERTOS

Suscríbete para impulsar tu negocio.

ÚLTIMOS ARTÍCULOS

2 June, 2025 POR Luz Castillejo Flor
Big Query clustering SQL GA4

Group your data like a pro: clustering with K-Means and BigQuery ML

Working with large volumes of marketing data—whether it’s web traffic, keywords, users, or campaigns—can feel overwhelming. These data sets often aren’t organized or categorized in a useful way, and facing them can feel like trying to understand a conversation in an unfamiliar language.

But what if you could automatically discover patterns and create data groups—without manual rules, endless scripts, or leaving your BigQuery analysis environment?

That’s exactly what K-Means with BigQuery ML allows you to do.

What is K-Means and why should you care?

K-Means is a clustering algorithm—a technique for grouping similar items. Imagine you have a table with thousands of URLs, users, or products. Instead of going through each one manually, K-Means can automatically find groups with common patterns: pages with similar performance, campaigns with similar outcomes, or users with shared behaviors.

And the best part? With BigQuery ML, you can apply K-Means using plain SQL—no need for Python scripts or external tools.

How does it actually work?

The process behind K-Means is surprisingly simple:

  1. You choose how many groups you want (the well-known “K”).

  2. The algorithm picks initial points called centroids.

  3. Each row in your data is assigned to the nearest centroid.

  4. The centroids are recalculated using the assigned data.

  5. This process repeats until the groups stabilize.

The result? Every row in your table is tagged with the cluster it belongs to. Now you can analyze the patterns of each group and make better-informed decisions.

How to apply it in BigQuery ML

BigQuery ML simplifies the entire process. With just a few lines of SQL, you can:

  • Train a K-Means model on your data

  • Retrieve the generated centroids

  • Classify each row with its corresponding cluster

This opens up a wide range of possibilities to enrich your dashboards and marketing analysis:

  • Group pages by performance (visits, conversions, revenue)

  • Detect behaviors of returning, new, or inactive users

  • Identify products often bought together or with similar buyer profiles

  • Spot keywords with unusual performance

How many clusters do I need?

Choosing the right number of clusters (“K”) is critical. Here are a few strategies:

  • Business knowledge: If you already know you have 3 customer types or 4 product categories, start there.

  • Elbow Method: Run models with different K values and watch for the point where segmentation no longer improves significantly.

  • Iterate thoughtfully: Test, review, and adjust based on how your data behaves.

Real-world examples

With K-Means in BigQuery, you can answer questions like:

  • What types of users visit my site, and how do they differ?

  • Which pages show similar performance trends?

  • Which campaigns are generating outlier results?

Grouping data this way not only saves time—it reveals opportunities and issues that might otherwise go unnoticed.

Conclusion

If you're handling large data sets and need to identify patterns fast, clustering with K-Means and BigQuery ML can be a game-changer. You don’t need to be a data scientist or build complex solutions from scratch. You just need to understand your business and ask the right questions—BigQuery can handle the rest.

Start simple: take your top-performing pages, group them by sessions and conversions, and see what patterns emerge. You might uncover insights that completely shift how you approach your digital strategy.

 
SEGUIR LEYENDO
26 May, 2025 POR Alba Fiorella Aviles
Artificial Intelligence Automatisation AI tools Claude 4.0

Claude 4.0: Advances and Challenges in Conversational AI

Artificial Intelligence (AI) continues to progress at an accelerated pace, and Claude 4.0, developed by Anthropic, marks a major milestone in this journey. This next-generation language model stands out for its ability to comprehend complex contexts, deliver accurate responses, and adapt to a wide range of business needs.
SEGUIR LEYENDO
20 May, 2025 POR Luz Castillejo Flor
automatización Artificial Intelligence gemini Alpha Evolve

AlphaEvolve: The new coding agent powered by Gemini

In a world where technology advances at unprecedented speed, artificial intelligence has emerged as a key driver of transformation. Among the most promising innovations today is AlphaEvolve, an evolutionary coding agent that combines the creative power of large language models (LLMs) with automated evaluators, opening new frontiers in software development, algorithm optimization, and solving complex problems in mathematics and computing.
SEGUIR LEYENDO
13 May, 2025 POR Luz Castillejo Flor
Artificial Intelligence digital transformation Design Figma

How AI Is Revolutionizing Design and Development

At its Config 2025 event, Figma made it clear: the future of digital design will be deeply shaped by artificial intelligence. Beyond announcing new features, the company highlighted a paradigm shift — design is no longer a standalone process, but the core that connects creativity, technology, and product development.
SEGUIR LEYENDO
VER MÁS
data
Mallorca 184, 08036
Barcelona, Spain