Hi, I need to create a bar/line chart like these.
The first serie (bar blue) is on Data Source 1, and the second serie (line green) is on Data Source 2. How can I make X Asis identify which line on Data Source 2 should be considered in the graph? How can I "match" the X Asis between two data sources?
Thanks.
Being more specific.
I have two databases, in DB1 I have a column with the category and another column with the name of the campaign. In DB2 I have a column with the name of the campaign and the amount spent by that campaign.
My X Asis is the name of the campaign.
In the first chart (blue line) I need to count the number of lines I have in a specific category (Negócio - Etapa). I got it.
In graph 2 (green line) I need to inform the amount spent for each campaign (Pessoa - utm_campaign) and divide it by the number of lines in a specific category.
In the end I need to know the cost per customer for a given category in a given campaign.
For example: How much I invested in campaign (campaing_1), divided by how much it brought me from clients in the category (Lead only email).
DB1: Category, Campaign name
DB2: Campaign name, Campaign investment
I tried some formulas with "Lookup" but I was unsuccessful.
Hi Jeferson,
You are correct we would often use LOOKUP to line up 2 data sources.
e.g.
Lookup (&x-axis,
GROUP( @B:B),
GROUPBY(@B:B,@C:C))
will give you the cost per campaign assuming the x-axis is campaign names and @B:B and @C:C are referencing DB2.
In your case you should be able to divide by the first series (e.g. series1) and get the result you want.
Lookup (&x-axis,
GROUP( @B:B),
GROUPBY(@B:B,@C:C))/ &series1
Note this example is for one specific category. e.g. if series1 is a count of "lead only email" it will give you the result for the "lead only email" category.
If you need more specific help you can also reach out to our support desk via a ticket at support@klipfolio.com
Keely
Hi Keely, thanks for the reply. It's worked perfectly :)
I am trying to use the same logic for another Klip, but it is not working as I expected.
In DB1 (Spreadsheet) I have a column with the date (Negócio - Data atualizada) and another column with the information I need to count (Negócio - Etapa).
In DB2 (Google Analytics) I have a column with the date (ga: date) and another column with the number that I need to count (ga: users).
My X-axis is DB1's "Negócio - Data atualizada" column.
I need to generate a graphic like the example below, where it shows me:
Serie 1: The data number of the column (Negocio - Etapa) of DB1
Series 2: The number of users to my site (DB2)
Serie 3: The conversion rate of users who accessed the site versus the number of data in DB1's "Negócio - Etapa" column.
Hi Jeferson,
If the first 2 series are correct then for conversion rate you should be able to use results references.
e.g. &Clientes Full/&Usarios *100
Results references take the result from the series or column they are referencing. Here is an article about them.
You can add *100 if that is appropriate e.g a conversion rate of 0.1 can be shown as 10% by adding the *100
Keely