“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.
Step 1: Import data into separate sheets by platform (we typically use Supermetrics or a manual copy/paste)
Step 2: Make sure your columns align (i.e., impressions, clicks, etc. should be in the same columns on every sheet)
Step 3: Add in a Platform column
Step 4: Use a Query function to aggregate the sheets
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 “https://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
