How can we help?

New post
0

Join multiple data sources with no common key

Avatar
FaceValue Admin
None
Follow

Hello there! I'm trying to see if there's a way to join multiple data sources (3-4) that do not share a common key, like a user ID. 

 

Use Case: 

I use Stripe, Google AdWords, HubSpot, and a Call Tracking application. All support REST APIs, but each of the applications has their own user IDs for each of my clients. I want a dashboard that joins all of their data into a single place, but since there are no common keys, it's not a simple join.

I know how I might do this in a BI tool by creating a new table with the keys and then joining my datasets to that, but can something like this be done in Klip?

Date Votes

2 comments

  • 0
    Avatar
    Meggan King

    Hello -

    Thanks for posting! This can absolutely be done using a table with keys. What I would suggest is to create a very simple mapping file which has a table with details for your clients. Make sure you have an id per client - either something meaningful to you, or a simple numbering system to track them. 

    You could have columns like

    1 client id, stripe account, google adwords id, hubspot id, call tracking id

    2 client id, stripe account, google adwords id, hubspot id, call tracking id

    and on and on. This file can easily be updated as new clients are added. If some clients don't have one of those services, just leave the cell blank. 

     

    You can upload this mapping file in each client account to find the details for the client based on the client id. The client id can be a company property. 

     

     

    The tricky part will be with any service which requires and OAuth token - such as Google Adwords. Do you have an MCC account which gives you access to all the client adwords accounts? If so, that is good and makes it easier. 

     

    The areas you'll want to read more on are:

    Company properties: https://support.klipfolio.com/hc/en-us/articles/223222128-User-and-Company-Properties

    https://support.klipfolio.com/hc/en-us/articles/215548438-Company-Properties

    And dynamic data sources

    https://support.klipfolio.com/hc/en-us/articles/216183237-BETA-Working-with-dynamic-data-sources

     

    Also, variables might be helpful: https://support.klipfolio.com/hc/en-us/articles/215547108-Variables

     

    If you haven't had client management turned on in your account yet, you can reach out to success@klipfolio.com We also have a number of resources for partners managing clients which might be useful. 

    I hope this helps,

    Meggan

  • 0
    Avatar
    FaceValue Admin

    Awesome, Meggan, thank you! It looks like this can work! I'll give it a shot.

Please sign in to leave a comment.