Restructuring Transactions Data View Correctly

I’m facing an issue with restructuring my transactions dataset in Causal.

I’m seeking help to understand the problem and find a solution to format the data correctly.

Currently, when I import the dataset into ‘Causal,’ the table does not match my desired output.

Dataset Example:
The dataset I have consists of the following columns: Date, Country, City, and Total Gross Revenue.

Here’s an example of the data:

Date Country City Total Gross Revenue
2023-04-30 Australia ADLCity 4677.59
2023-04-30 Australia Albany 378.67
2023-04-30 Turkey Ankara 3032.17
2023-04-30 Turkey Antalya 2388.75

Expected View in Causal:
I want to present the data in a specific format, where the headers represent dates, and the data is grouped by country and city. The desired output is as follows:

Country 2023-04-30
Australia ADLCity 4677.59
Albany 378.67
Turkey Ankara 3032.17
Antalya 2388.75

Current View in Causal:
However, the current output in ‘Causal’ does not match the desired format. The data is duplicated and does not group the information correctly:

Country 2023-04-30
Australia ADLCity 4677.59
Albany 378.67
Ankara 3032.17
Antalya 2388.75
Turkey ADLCity 4677.59
Albany 378.67
Ankara 3032.17
Antalya 2388.75

Seeking Assistance:
I would appreciate guidance on why the data is duplicating in Causal and how I can restructure it to achieve the desired format.

Thank you for your support and expertise!

Hey @sarviinageelen - Country and City need are two separate categories that relate to each other, i.e. every City sits within a Country. If you haven’t linked them together though, Causal does not understand this inherently!

So in order for Causal to understand this relationship, you need to link them together. The best way to do this is to adjust the column header in your google sheet from “Country” to “City > Country” (the “>” representing the mapping).

Once you’ve done that, you should refresh the data source in Causal, and you’ll then see in your data table that each City maps to a Country. Example of what this would look like in screenshot below

Then you’ll see your Revenue variable broken down by City only, and in a separate variable can group Revenue by City, by Country instead :slight_smile: Unfortunately you can’t see them both at the same time right now in one variable (coming soon).

Its also worth noting that in your screenshot you’ve explicitly applied the categories on the Revenue variable. You should only do this if you want to set a different value for each category item. In this case, your Revenue variable should just inherit the categories from data. See more on explicit vs. inherited/implicit here.

Hello @heidi_causal, thank you for your assistance with the workflow.

I would like to provide an update on the changes I made to my Google Sheet and the issue I encountered while trying to group the data.

  1. I made a modification to the column header in my Google Sheet. Instead of “Country,” I changed it to “City > Country.”

  2. As a result, I was able to visualize the data in a table format, where each city was mapped to its corresponding country.

  3. Next, I created a variable for revenue, but it seems to be broken down by all cities rather than aggregated by country.

  4. My objective was to group the cities based on their respective countries. However, I encountered an issue where the option to group the data by cities was disabled or greyed out.

Screenshot 2023-05-24 at 4.28.24 PM

  1. Consequently, I’m seeking guidance on how to group the Revenue by cities first, and then have those cities nested under their respective countries.

Additionally I have recorded my screen with voiceover to help out where I am stuck at here

Please let me know if there’s anything else you need clarification on or if you require additional information.


Hey @sarviinageelen,

The missing step is to transform the data item! Please see docs here. If you select the data icon on the variable you will be able to group by the Country Category since you have now created this mapping. That should give you the desired result.


Hi @Sanjeev_Sandhar thanks for the tip!

I selected the data item and then selected grouped by ‘country’.

However the entire table reverted to aggregating to the country level without the breakdown by cities.

Appreciate any help on next steps

Hey @sarviinageelen - please see this comment from Heidi above. It’s currently not possible to see both breakdowns in the same variable. You could have them in separate variables or you could put the variable broken down by City into a Table (see docs) and if you added a Country filter, you could see the different breakdowns this way.

Hope that helps!

Thanks @Sanjeev_Sandhar, that’s very clear!

1 Like