Power BI and SharePoint: Enhancing Sales with Google Ads Data

In the fast-paced world of digital marketing, analyzing campaign performance is essential for quick and well-adjusted decisions. Google Ads is a rich source of data, but the platform's standard reports often fail to meet the specific needs of the team. With this in mind, I sought to automate the collection and integration of campaign data into Power BI using CSV files stored in SharePoint, a practical and quickly implemented solution for teams with limited access to the Google Ads API.

The Importance of Data in Google Ads Campaigns

For a marketing department, constantly monitoring Google Ads (and other) campaigns is more than just tracking results: it enables data-driven adjustments that can determine the success or failure of a strategy.

With campaigns involving daily investments, every data-based decision is crucial to optimize the budget and ensure a positive return on investment (ROI).

Marketing teams need to track metrics such as impressions, clicks, conversion rates, and cost per acquisition (CPA), which reveal how the audience interacts with ads. From this information, real-time adjustments can be made, whether increasing investment in a high-converting campaign or pausing one that is not delivering the expected results. This dynamic requires tools that facilitate the visualization and analysis of this data, enabling quick and well-informed decisions.

Having a consolidated and customized view of metrics in Power BI helps the marketing department align campaign objectives with overall company goals, maximizing the impact of each action and identifying opportunities to optimize the advertising budget. In an environment where every decision can directly affect the bottom line, continuous monitoring is more than a necessity—it's a competitive advantage.

The Need for Customized Reports

While Google Ads' native reports provide a good overview, they have significant limitations. Often, a marketing team requires specific data that isn't captured in these reports, such as customized regional information, relationships with the company's products, internal engagement channel details, or insights into direct sales associated with the campaign. These elements, which vary from company to company, make personalized reports a strategic necessity.

With Power BI, it's possible to explore an array of filters and cross data across various metrics and dimensions, giving marketing an unparalleled level of flexibility in analysis.

Additionally, creating and publishing a robust Semantic Model within Power BI Services ensures that data is organized and contextualized, allowing any department member to access information intuitively. This model makes the data available to all department professionals, fostering a data-driven culture where infinite combinations and analyses are possible.

These possibilities, combined with Power BI's customization flexibility, allow marketing to have a detailed and integrated view of campaign performance, facilitating data-driven decision-making.

The Reality of Google Ads API Access for Marketing Teams

In theory, the Google Ads API is the best way to get up-to-date data directly into an analytics tool, but in practice, it's different. In many cases, marketing departments face difficulties accessing the API due to a combination of factors, such as the team's lack of technical knowledge, the absence of dedicated support for more complex integrations that require hiring specialized vendors, computational environments to run the developed programs, and security policies for accessing external data.

Faced with these challenges, an alternative approach to centralizing data was to use CSV files exported from Google Ads. This solution requires fewer technical resources and can be managed internally by the marketing team, which is already familiar with accessing the platform's reports.

However, limiting analysis to a single CSV file doesn't meet the need, so the solution evolved into something more robust: consolidating multiple CSV files automatically within Power BI, allowing for a more complete analysis. This strategy allows the marketing team to add new data to Power BI by simply adding new data files to a specific folder.

Choosing SharePoint for Data Storage

For companies using the Microsoft 365 ecosystem, SharePoint is the natural choice for storing these campaign CSV files, combining security with accessibility. SharePoint facilitates access control, offering a robust security layer, and allows file sharing between the marketing team and other departments, all in compliance with the company's data governance standards.

On the other hand, despite its benefits, connecting to SharePoint in Power BI presents practical challenges. When connecting to a SharePoint Document Library, Power BI lists all files available on the site, ignoring folder organization and returning a volume of data that is difficult to filter. This behavior not only delays the import process but also requires manual intervention or the direct use of Power Query M to filter the correct files in the desired folder.

I discussed this topic in another article, where I provide a step-by-step guide on how to connect Power BI to a file located on SharePoint.?

Power BI: How to access an Excel file in a SharePoint Document Library

Reading Multiple CSVs via Power Query M

My previous article covered how to read a specific file, not multiple files located in a folder, which required entirely new development.

Like many technical challenges, I started creating the query with the support of ChatGPT. I have a custom chat tailored for working with Power BI, Fabric, and Power Query M. However, the AI model encountered some limitations when dealing with multiple files, making it difficult to interpret columns in each individual file and properly concatenate the data into a single table. Consequently, the solution was to develop the Power Query M query manually, a process that involved:

1. Connecting to SharePoint: I used the SharePoint.Files function to list all files within the specific SharePoint site, ensuring access to the entire file structure, regardless of folder organization.
2. Filtering for the Folder and Selecting CSV Files: The query applied filters that limit the reading to the correct folder and extract only files with the CSV extension using Text.EndsWith([Name], ".csv"). This eliminated the risk of loading incorrect or unnecessary files, allowing a focus solely on campaign reports.
3. Transforming and Combining Data from CSV Files: For each CSV file, the Power Query M query loads and processes the content based on a model. It's a handy feature that lets you define what to do with the files found, and the changes are applied one by one. At the end, the query concatenates the data into a single table, ready for analysis. The function and template structure is created automatically when you click the button:

The final aspect is this:

This process was adjusted so that each new CSV added to the SharePoint folder would be automatically read, allowing the marketing team to update the data efficiently without complex interventions.

Data Available for Analysis and the Next Steps

With the integration of Google Ads campaign data configured, the marketing department now has easy and centralized access to its campaign data in Power BI. Updating reports has become a simple and agile process: the analyst only needs to download the CSV from Google Ads and place it in the shared folder on SharePoint. With that, Power BI will already be set to automatically process the new information, consolidating it into the analysis table without complex manual steps.

This solution is quickly implemented and delivers immediate results, eliminating the need for advanced development or complex API configurations. At the same time, it provides consistent and reliable returns, ideal for meeting the marketing team's demands for continuous and effective analyses. The next step will be to develop a direct integration with the Google Ads API once access is allowed, but until then, the current method proves to be a robust and accessible alternative, perfectly suited for teams looking for a practical and quickly implemented solution to monitor the performance of their strategic campaigns.

Additional Advantage: Adaptable Solution for Other Data Sources

One of the main advantages of this approach is its adaptability. Although it was created to consolidate Google Ads data, the same structure can be used to integrate data from other platforms that export CSV reports, such as social media, Google Analytics, and email marketing tools. This solution is ideal for teams that struggle to create direct access to complex APIs, offering a practical way to centralize data from different sources in Power BI and facilitate campaign performance monitoring in one place.

If you or your team face similar challenges, I’m available to help implement solutions that turn data into strategic insights. Let’s enhance your results!