Combinar Datos de Ventas y Clientes en Looker Studio

Customer analytics is a powerful tool that enables companies to better understand who buys their products or services. By combining sales data with customer information, businesses can discover buying patterns, identify their best customers, and make evidence-based strategic decisions. In this article, we will explore how to create a customer analytics dashboard in Looker Studio for the company Freshmark, enabling them to gain valuable insights about their customer base.

How to prepare data for effective customer analytics?

To perform an in-depth analysis of Freshmark's customer buying patterns, it is necessary to properly structure the data. In this case, some important changes were made to the original sales dataset:

• A unique customer ID was incorporated in order to identify and track each individual customer.
• An additional sheet was created with detailed customer information, including:
  • Frequency of purchase (how many times they have purchased).
  • Total spent by each customer
  • Customer type categorization (new, frequent, VIP)

These changes allow you to cross-reference sales information with customer data, creating a more complete view of buying behavior and allowing you to segment customers for more targeted analysis.

How to import and manage multiple data sources in Looker Studio?

The process for importing data into Looker Studio is relatively straightforward:

1. Go to "Resources" > "Manage added data sources."
2. Select "Add a data source".
3. Choose the type of source (in this case, a Google spreadsheet).
4. Add the document URL and select the specific sheet to import.

To keep the dashboard organized, it is advisable to remove data sources that are obsolete or will no longer be used. This avoids confusion and makes working with the data more efficient.

An important aspect is to make sure that all charts are linked to the correct and up-to-date data source, especially if changes have been made to the original sources.

How to combine different data tables for a more complete analysis?

Combining tables is fundamental to relate information from different sources. In Looker Studio, this process is done through the "Manage combinations" function:

1. Go to "Resources" > "Manage Combinations".
2. Select "Add new combination".
3. Choose the tables to be combined (in this case, sales and customers).
4. Select the fields that will serve as the key for the merge (customer ID)
5. Determine the most appropriate type of combination

Which merge type to choose?

The choice of the type of merge depends on the analysis you want to perform:

• Inner merge: Displays only records that have a match in both tables.
• Outer left merge: Displays all records in the left table and only matching records in the right table.
• Outer right merge: Displays all records in the right table and only matching records in the left table.
• Outer Merge Full: Displays all records from both tables.

In the case of Freshmark, a left outer join was chosen to display all sales, even those that are not associated with a specific customer in the database.

It is important to carefully select the fields to be included in the combination, considering both dimensions (categories) and metrics (numerical values) that will be useful for analysis.

Which visualizations are most effective for analyzing customer behavior?

Once the data is prepared, it is time to create visualizations that answer specific business questions:

Total spend by customer type.

To answer the question "Which type of customer spends the most money?", a bar chart is ideal:

1. Select combined data as the source.
2. Set "Type of customer" as the dimension
3. Use "Total Spent" as a metric
4. Enable data labels for clarity

This analysis revealed that new customers spend the most money in total at Freshmark.

Purchase frequency by customer type

To visualize which type of customer buys most frequently, a pie chart is effective:

1. Use "Customer Type" as a dimension.
2. Set "Frequency of purchase" as the metric.

The result showed that customers categorized as "frequent" make the most purchases, which validates the categorization used.

Average ticket by customer type

To calculate how much each type of customer spends on average per purchase, a table with a calculated field was used:

Average Ticket = Total Spent / Purchase Frequency.

This visualization revealed that:

• VIP customers spend on average 2,600,000 Colombian pesos throughout their relationship with the business.
• The average ticket per purchase for VIP customers is 222,000 pesos.
• Frequent customers have the highest average ticket per purchase.

Frequency of purchase by branch

To analyze in which branch customers shop most frequently:

1. Add the field "Branch" to the data combination.
2. Create a bar chart with "Branch" as the dimension and "Frequency of purchase" as the metric.

This analysis showed that branch number 3 is where customers make purchases most frequently.

Creating a customer analysis dashboard provides valuable information for strategic decision making in any business. By combining sales data with customer information, it is possible to identify patterns, segment customers and optimize marketing and sales strategies. Have you created a similar dashboard for your business? Share your experience in the comments and show how you have organized your visualizations to gain valuable insights.