How to integrate PostHog data into Power BI: a complete step-by-step guide

Nowadays, within an organisation, effective decision-making is based on data, which is one of the reasons why consolidating most sources within a single visualisation tool like Power BI is essential. For this reason, in an environment where marketing and product teams require agile responses and data-driven decisions, connecting specialised tools like PostHog and Power BI becomes essential, as their integration allows for capturing user events in real-time and transforming them into dashboards ready to share with internal or external users.

This practical guide will show you step-by-step how to connect PostHog with Power BI, configure your query with the API, obtain the data and build your dashboard. All of this is accompanied by examples to ensure an efficient and secure implementation.

Why integrate PostHog with Power BI?

PostHog is an all-in-one suite of product and data tools, including product analytics, web analytics, session replay, heatmaps, feature flags, experiments, surveys, and more. In other words, it is a product analytics platform that allows you to capture events, perform trend analysis, and filter for more accurate data, all without the need to send data to external servers, which allows for greater data control.

Power BI, on the other hand, is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights.

The benefits of integrating these capabilities are numerous and significant. Firstly, the ability to obtain actionable insights from user behavioural data will allow companies to understand user behaviour, identify trends, and patterns, and lead managers to make informed decisions based on this information, resulting in quick and timely action in any eventuality. It is also important to note that this will allow strategic actions to be taken within the company to achieve the proposed objectives.

Secondly, by directly linking product events to marketing campaigns, an invaluable feedback loop is created, optimising acquisition and retention strategies. In addition, automating real-time reporting without manual intervention frees up valuable resources and ensures that you always have access to the most up-to-date information. Ultimately, the integration of diverse data sources offers a comprehensive and global view of the business, enabling more informed decision-making at all levels.

Preparations: What you need before connecting

When starting any integration between two applications, you need to ensure that you have the necessary elements in place for a smooth and successful connection. Proper preparation of these requirements will ensure a smooth and efficient process. Here's what you need to have:

1. Active PostHog Project: You must have access to an active project in PostHog. This will serve as the source of the data you intend to analyse.
2. PostHog Developer/Administrator Permissions: Your PostHog credentials must include developer or administrator permissions. This is critical for secure and complete data extraction.
3. PostHog Personal API Key: You will need your personal PostHog API Key, which acts as the authentication token to access your data programmatically. You can find this API Key in your project settings (we'll show you how in the next step).
4. Power BI Desktop: Ensure Power BI Desktop is installed on your system and you have an active session.

Step-by-step to connect PostHog to Power BI

1. Creating your API Key in PostHog

The first and crucial step is to get your personal PostHog API Key. Think of it as your exclusive access pass; without it, Power BI won't be able to ‘talk’ to your PostHog data.

To generate it, simply log in to your PostHog project. Once inside, click on your avatar, which is usually located in the top right corner of the screen. From the drop-down menu, select ‘Account Settings’. Within your account settings, find and navigate to the ‘API Keys’ tab. There, click on the ‘Create personal API key’ button. Once it is generated, copy the value immediately and save it in a safe place, as for security reasons, it will only show up this one time!

2. Understanding the PostHog Query API

Once you have the API Key, the next step is to tell PostHog exactly what data you need. To do this, we will use PostHog's Query API. This API allows you to make POST requests to extract a wide range of information such as metrics, trends, conversion funnels and more.

The key to communicating with the Query API is through a request body in JSON format, where you specify the parameters of your query. For example, if you are interested in getting the number of page views for the last 30 days, your JSON body would look like this:

The above code is asking PostHog for a ‘trend’ (‘insight’: ‘TRENDS’) from the ‘pageview’ event (‘events’: [{‘id’: ‘pageview’, ‘name’: ‘Page View’}]) with a date range spanning the last 30 days (“date_from”: ‘-30d’). This is an example; the Query API provides the flexibility to build much more complex queries tailored to specific analytics needs.

Connecting from Power BI

After obtaining the API Key and identifying the data required from Posthog, open Power BI Desktop and establish the connection. There are two primary ways to do this:

Method 1: Fetching Data from the Web (Simpler for Basic Queries) 

1. From the Home tab on the top ribbon, select "Get data" and then choose the "Web" option.

2. This will open a window where you can specify the details of your request. It's crucial to select the "Advanced" option, as it will allow you to configure the headers and body of the POST request.

3. In the URL parts field, enter thePostHog API endpoint address: https://app.posthog.com/api/projects/YOUR_PROJECT_ID/query/Important: Remember to replace YOUR_PROJECT_ID with your actualPostHog project ID. You can find your project ID in yourPostHog project settings, typically in the URL whenyou're within your project (e.g., app.posthog.com/project/YOUR_PROJECT_ID/insights).

4. In the HTTP request header parameters section, add two custom headers:

Name:AuthorizationValue:Bearer YOUR_POSTHOG_API_KEY

Name:Content-TypeValue:application/json

5. In the HTTP request body field, simply paste the JSON query you defined in the previous step (e.g., the pageview trend query).

6. Once all the fields are correctly filled out, click OK. Power BI will send the request to PostHog. If everything is in order, you will receive a response in JSON format, ready to be transformed and visualized. Note that this option often requires further data transformation in Power Query Editor to display the information correctly.

Method 2: Using Power Query Editor (More Control & Flexibility)

This method offers greater control over your data extraction and transformation processes, making it more functional for complex or evolving data requirements.

1. Once you've opened Power BI Desktop, navigate to the Home tab.
2. Select "Get Data", and then choose "Blank Query".
3. In the Queries pane on the left, right-click on Query1 and rename it to something more descriptive, like "PostHog Events".

Rename Query:

4. Finally, open the Advanced Editor, which you'll find back on the Home tab (or by right-clicking the query).

Once the Code Editor window opens, you are ready to enter the M code (Power Query Formula Language). This code will connect to the API, retrieve data, and perform initial transformations. Replace the existing content with the following:

let // Your actual API key apiKey = "APY KEY",

// Your PostHog project base URL with the project ID
baseUrl = "https://us.posthog.com/api/projects/PROJECT_id/events/",

// Add query parameters to filter $pageview events and limit results
url = baseUrl & "?event=$pageview&limit=1000",

// HTTP headers including Bearer token authorisation
headers = [
#"Authorization" = "Bearer " & apiKey,
#"Content-Type" = "application/json"
],

// Call the API
rawData = Web.Contents(url, [Headers = headers]),

// Parse JSON
json = Json.Document(rawData),

// Extract results array
results = json[results],

// Convert list to table
dataTable = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Expand main event columns
expanded = Table.ExpandRecordColumn(dataTable, "Column1", {"event", "timestamp", "distinct_id", "properties"}),

// Expand properties you want to analyse (now includes $host)
propertiesExpanded = Table.ExpandRecordColumn(expanded, "properties", {
"$current_url", "$browser", "$os", "$screen_height", "$screen_width", "$device_type", "$referrer", "$host"
}),

// Rename columns for clarity
renamed = Table.RenameColumns(propertiesExpanded, {
{"event", "Event"},
{"timestamp", "Timestamp"},
{"distinct_id", "UserID"},
{"$current_url", "URL"},
{"$browser", "Browser"},
{"$os", "OperatingSystem"},
{"$screen_height", "ScreenHeight"},
{"$screen_width", "ScreenWidth"},
{"$device_type", "DeviceType"},
{"$referrer", "Referrer"},
{"$host", "Host"}
}),

// Change timestamp type
#"Changed Type" = Table.TransformColumnTypes(renamed, {{"Timestamp", type datetime}}),

// Apply Host filters
filtered = Table.SelectRows(#"Changed Type", each
not Text.Contains([Host], "dev.talentblocks.app") and
not Text.Contains([Host], "test.talentblocks.app") and
not Text.Contains([Host], "localhost") and
not Text.Contains([Host], "127.0.0.1") and
not Text.Contains([Host], "dev.") and
not Text.Contains([Host], "branch-")
)
 

in filtered

The previousM code will allow you to retrieve information from the specific pageviews event, which is the example in our case. However, the interesting thing about creating the connection this way is that it allows you to have greater control over the data and queries required by the project. The previous query is an example of many that can be created. The important thing is to understand the data and the information you want to evaluate.

Essential Tips for Success

• Filter Events Judiciously: Don't request all data; only fetch the necessary events. This optimises performance and simplifies data management.
• Validate Data Regularly: Compare the data generated in PostHog directly with the data displayed in Power BI to ensure accuracy and consistency.
• Document Your Queries: Save your JSON and M code queries for future reference, troubleshooting, or auditing purposes.
• Exclude Internal Users: For the highest quality data in your analysis, filter out interactions from your own team. The provided Power Query example includes a robust filter for common development and testing environments.

Conclusion

Integrating PostHog with Power BI is a game-changer. It lets you pivot from raw event data to dynamic, insightful dashboards, directly fuelling your strategic marketing and product decisions. Whether you're pulling data via the web or through direct query code, this connection is incredibly valuable, empowering any project with real-time, data-driven decision-making. We highly encourage you to explore these powerful connections for yourselves!