Preparing Your Data for Integration

Before connecting Loops to an existing database or data warehouse, you’ll need to prepare your data accordingly. This ensures the quality, integrity and usability of your data, and maximizes the value of your analytics efforts by leading to more accurate, reliable and actionable results.

Note that data preparation is not necessary if you’re syncing data to your warehouse from Amplitude, Mixpanel, Firebase or Segment, since these platforms already have a structured integration with data warehouses.

Schema Requirements

Here are some guidelines for integrating your data with Loops:

Dimension tables

Dimension tables contain descriptive attributes about your users, and should include the user base you wish to analyze:

• User ID as primary key
• Creation date for each user (timestamp)
• Other dimensions can be included as either additional columns or separate aggregated DIM tables, as long as the data can be joined by User ID.

Fact tables

Fact tables contain numeric or measurable data that represents the events being analyzed, and should include the following:

• User ID
• Event name
• Timestamp

If you only have a single events table, you may share the table as-is.

If each event is stored in a table of its own, Loops will automatically join those tables and use the relevant ones for each analysis.

KPIs

A KPI refers to any business metric your team is optimizing for. There are the three ways to define product KPIs with Loops (if your desired KPI doesn’t fit these criteria, talk to your integration specialist):

• Based on a timestamp column in a dimension table
  • The timestamp should be static throughout the user lifecycle.
  • Examples: subscription creation timestamp, first translation timestamp, churn timestamp
• Based on the occurrence of an event in a fact table (a.k.a. event table)
  • Examples: transaction completed, invite sent
  • When using Loops, you’ll be able to define more complex KPIs, such as sequences of events, or using a feature X times within Y days
• Based on a daily/weekly/monthly aggregation table (e.g. the “Daily KPIs” table below)
  • Examples: minutes of activity per week, average order value per month, daily KPI achievement (as in the below example)
  • Daily/weekly/monthly aggregated tables will contain a composite key of user ID + day/week/month of year.

Frequently Asked Questions

Below are some answers to frequently asked questions about preparing your data for integration with Loops.

I don’t have a distinct table that contains the user base to be analyzed. What should I do?

Your Loops integration specialist can help you create one by aggregating your event table(s).

I have existing tables that follow the requirements, but they also include other unrelated columns. What should I do?

Don’t worry about adding columns that you aren’t sure will be used. If you don’t want them to be used, Loops will never scan the information within them.

What should I do if some of my KPIs are represented by a backend event?

You can integrate Loops with as many event tables (a.k.a. fact tables) as you wish, from both backend (server) and frontend systems, communication platforms, and other general logs that may affect the users.

What if I have a custom KPI calculation that doesn’t follow any of the above structures (such as single timestamp per user, daily aggregated metric, or an event basis)?

If you have a different way of calculating KPIs, or if your tables follow a different structure, don’t hesitate to reach out – we’ll be happy to help.

I provide a B2B product and want to analyze account-level metrics alongside user-level ones. Can I define both in Loops?

Yes! We support multi-entity integration.