Metrics for Extracted Inner Join by Date Are Not Aligning

[GaryS]

Using Looker Studio Pro.

Note on table "Extracted Data from Google Ads (Conversions + Conversion Value Only)" the columns for Conversions and Conversion Value on August 30, 2024 @ 6 and $1,922.13 respectively are accurate but are NOT correctly reflected on the table "Blended Extracted Data Inner Joined by Date".  Same for other dates.

???  Can't figure out why.

[Lobo-Google]

Hi @GaryS, thanks for your post!

As the Blending tips and advanced concepts documentation page states, each table in a blend is queried before they are joined together. In that case, I might suggest playing around with the join configuration for the two tables. Are the fields that you are using to join the tables together in the join configuration truly the same fields that the two tables have in common? Are there any filters applied to the tables in the blend? Maybe change the order of the tables in the blend? 

I hope this is helpful, and gives you some things to try as you troubleshoot!

[GaryS]

I'm thankful for the response, but what you are proposing seems like you're guessing?

"playing around with the join configuration"

instead of experimenting... wouldn't using the very common standard inner join be better?  shouldn't it work as expected?  an inner join is an inner join isn't it?  i don't understand the guesswork approach.

Are the fields that you are using to join the tables together in the join configuration truly the same fields

they are both extracted from the very same Google Ads data so isn't it safe to assume they are the same?

Are there any filters applied to the tables in the blend

no filters applied to anything.

Maybe change the order of the tables in the blend?

I'm open to trying, but if they are joined on the same (date) why would the order make a difference?

[Lobo-Google]

I appreciate the reply! Yes, since we don't have access to see how blends work under the hood, sometimes it takes a certain amount of experimentation when results are not as expected. This can be one of the most common issues with data blending.

Is there any other field you can add to the tables to join on besides the date? I wonder if using the date may delivering unexpected results. 

[GaryS]

"...since we don't have access to see how blends work under the hood..."

I'm confused... since you are Staff @ Google shouldn't you (or someone you work with) have access to the information on how blends are supposed to work?

Diner: "Waitress, can you tell me what is in this food?"

Waitress: "No one employed here at this restaurant that makes the food knows what is in this food."

Diner: "..."

[Mehdi_Oudjida]

@GaryS 

I suspect a problem of blending configuration generating duplicated rows when a key is found. Can you make a screenshot of the blending configuration with the 2 blending tables and the join configuration from the popin please.

Usually, it is easy to solve without experimentation 😛

Mehdi

[GaryS]

You're the man, Mehdi! 

It is good to know there is a solution based on logic.

Here you are, Sir.

[Mehdi_Oudjida]

🙂 Thank you.

Found

- remove the conversion category if it is not on the other side (the main guilty part).
- switch the join type to left join (let's say that there are always daily impressions when there are daly conversions)

voila.

[Lobo-Google]

Thanks, @Mehdi_Oudjida! There had been no reply so I wanted to help as I could. This is great.

[GaryS]

Ah.  Unfortunately we need the conversion category to filter on (later after we get the basic join working).  Because we want to report on (count and value) of particular conversion categories we were forced to create a separate data source to bring those in via join.  This is because as per Google API documentation only certain fields are usable with conversion category.  For example we can't bring in conversion category and clicks - weird i know.  we can and will try the left join however.  ps - dm me if you're fore hire with simple Q&A here and there.

[Mehdi_Oudjida]

ok, but just to finish with this problem, you cannot also add the conversion category in the right table and use it as a second join key with the date?

[GaryS]

when we add conversion category to the [extract] data source google throws an error - likely because of the earlier reference to their api where they don't allow the combination of pulling conversion category with other standard metrics like clicks, costs, impressions, etc.  the source of this entire join work-around to begin with.

[GaryS]

Most simply put we're trying to get single data source so we can output to charts, graphs etc including:

• dimension of date (commonly as, for example: day, week, month year)
• metric of cost
• metric of impressions
• metric of clicks
• metric of conversions (purchases only) *
• metric of conversion value (purchases only) *

because we need to

a) report conversions and conversion value for the conversion category of purchases only and

b) google api doesn't allow us to pull those together - we've been working through this join process. 

and lasty

c) for speed we're attempting to do this via extract.

here are the data sources

1. google ads (via supermetrics) for cost, impressions and clicks
2. extract that source of "google ads"
3. google ads conversions only (via supermetrics)
4. extract that source of "google ads conversions only"

then we are/were attempting to join #2 and #4 on date.

[GaryS]

Been able to get it this far... the data for each row is a day (eg. Aug 1, Aug 2, etc) but choosing to display the dimension as Date (Year Month) it is unfortunately returning multiple of the same month instead of aggregating the metrics (all set to Sum).  The formatting of Date for each source is identical.