“Data data everywhere, nor any report to view”

Data comes in all shapes and sizes, from multiple platforms and in various formats. When it comes time to build a report or run an analysis, it oftentimes is very cumbersome to merge data sources. This post will describe common methods of PPC data aggregation.

Combining Data In Google Sheets

Perhaps the simplest method of combining data is to use Google Sheets. Here I’ll outline my favorite method: the Query function.

Of course, there are other options to aggregate data within Google sheets. SUMIFS and INDEX MATCH are great options, here’s an example of an index match –

Once you get your data aggregated, it’s much easier to use pivot tables, Query functions, etc. to group and organize your information. If you’re exporting to Data Studio for visualizations, now you only have to link to this one sheet, vs. each platform-specific sheet.

Considerations

• This method is still a bit manual and requires some comfort with Excel
• Errors are very likely to occur with this method, and QA is important

Data Studio Blending

Data Studio is a great tool for data visualization, but it really shines when your data is already properly cleaned and structured. 

In the event that you need to merge data from two sources, I highly recommend reading through the About data blending support page. This page will walk you through everything you need to know in detail before you get started.

One thing I do want to highlight here is that blending acts as a left outer join, so it will include everything in data source A, and only data from source B that matches with the join keys specified. 

Considerations:

• I recommend using “date” as a join key
• This method requires some understanding of data structure and left outer joins

Third-party data aggregation platforms – Funnel

Third-party data aggregation platforms such as Funnel exist to ease this very problem of data cleaning, transformation, and aggregation. While it comes with a price tag, it offers substantial benefits, including many pre-built connections to popular platforms as well as custom dimensions and metrics that are ready to go out of the box.

For this particular client, we needed to match Facebook ad links with Google Analytics landing pages. A custom dimension allows us to clean up the URLs with regular expressions to remove any “http://www.” and any trailing URL tags at the end.

After you have added your data sources and created any custom dimensions and metrics you need, the Data Explorer is where you’ll head next:

This is the perfect place to QA your data before pushing it to a platform like Data Studio or Sheets for visualization. Or, if you’re looking into something specific, feel free to adjust the dimensions and metrics to find the answer to your question.

Considerations:

• As mentioned before, this platform does come with a pretty hefty price tag
• Custom metrics are simple to set up but do require some consideration on which method you use (formula vs. rules)
• Funnel can also export to Google BigQuery, if you’re looking to use SQL, R, etc. to run bigger analysis