Kaggle Marketing Analytics
Table of Contents
Aim/Objective/Goal
The goal of this case study is to demonstrate how we can use Japio to transform the data and use it wherever needed, maybe the transformed data would be used to train a model or use in an external BI tool like PowerBI, Tableau. After transforming the data in Japio we can also visualize it in the form of various metrics and dashboards. This is all you can do without a coding skill using the Japio platform. In the below section we will compare one of the kaggle articles where user had to use coding skills (R- language) to analyze, clean & transform the data. In the later section we will demonstrate how you can achieve the same using Japio without any coding.
Lets see how user had to use python coding for analyzing, cleaning, transforming & visualizing the data in one of the kaggle article
https://www.kaggle.com/code/vionnlow/kaggle-marketing-analytics
#Read in the csv file
df = read.csv("../input/marketing-data/marketing_data.csv")
sum(is.na(df))
Drop irrelevant columns
df_regress<-df%>%select(-ID)
Frequency of Num Store Purchase
p1<-df_regress%>%ggplot(aes(NumStorePurchases))+
geom_histogram(fill="#666699")+
scale_x_continuous(n.breaks=13)+
ggtitle("Frequency of Number of Store Purchases")+
ylab("Frequency")
p2<-df_regress%>%ggplot(aes(y=NumStorePurchases))+
geom_boxplot(fill="#666699")+
theme(axis.ticks.x=element_blank(),
axis.text.x = element_blank())
grid.arrange(p1,p2,ncol=1)
Observation:
- Most individuals purchase about 3 items from the store. The frequency decreases as the number of store purchases increases.
- Mean number of store purchases are 5.
Japio without coding
Now, let’s see below how we can achieve the Kaggle things in Japio without coding skills
Dataset
We have used this sample data for the demonstrations
https://www.kaggle.com/code/vionnlow/kaggle-marketing-analytics/input
Loading Data
To upload data into Japio from various sources like CSV files, Excel spreadsheets, and JSON files, you can follow these general steps:
Following steps demonstrate how to load data if you have CSV file:
Step 1
After logging in to the Japio application at https://app.japio.com , you will find the “Data Manager” option on the left side of the page . Click on the Data Manager to proceed .
Step 2
If you already have a connection on which you want to transform or visualize the data, click on “My Connections” at the top of the page. You will be able to see your existing connections there.
If you don’t have any existing connections, click on “+NEW CONNECTION” located in the top right corner of the page to create a new connection.
Step 3
https://app.japio.com/data-manager/data-source
After clicking on “+NEW CONNECTION” you will be presented with various data sources from which you can fetch your data into Japio. This will enable you to transform and work with your data within Japio’s platform.
Suppose you want to upload a csv file then hover over the CSV data source, and you will see a “+ connect” button as shown below.
Step 4
After clicking on “+ connect” a dialog box will appear
Step 5
After clicking on the “+connect” button, a dialog box will appear where you will be prompted to provide a name for the connection and upload your CSV file. This step allows you to establish the connection between Japio and the selected data source. By filling in the required fields and clicking the “Connect” button, you will initiate the process of importing the data from the CSV file into Japio. This will enable you to perform further analysis and manipulation of the data within Japio’s platform.
After successfully loading the data, you will be redirected to the data connection page in Japio, specifically to the URL: https://app.japio.com/data-manager/data-connections/data-connection/ connector-id. Here, you will be able to see the connection you loaded with the name you provided during the data connection setup. From this page, you can access and work with the loaded data for further analysis, transformation, visualization, or any other desired operations within Japio.
Access of loaded data
To access the data you have loaded into Japio and perform further operations, you can follow these steps:
- Click on ” Data Manager >My Connections” to view your connected data sources.
2. Search for the specific data source from which you have loaded your data such as CSV as Data Source.
3. Once you have located the data source, you will see a count or number associated with it, indicating the number of connections.
4. Once you have located the data source, you will see a count or number associated with it, indicating the number of connections.
5. After clicking on the number of connections, you will be able to see all your connections and access your active connections. From there, choose the specific connection on which you want to transform the data . Click on the “+” symbol. This will allow you to proceed with the data transformation process.
6. After clicking on the “+” symbol, you will be redirected to a page with a URL like “https://builder.japio.com/public/datasetbuilder-view/” followed by a connection ID. This page allows you to transform your data. Select the required fields and click on “Get Data” to proceed with the data transformation process.
Clean /Transform Data
- Access the page “https://builder.japio.com/public/datasetbuilder-view/” followed by the connection ID by following the steps mentioned in the “Access of loaded data” section.
2. On the page, locate and click on the “+ Add Transform” button, which is located on the left side of the page. This will open up the transformation options as shown below.
Check Null/missing Values
Following steps will explain how to check Null values
- Click on + Add Transform > Filter Data
2. In the transformation panel, select the desired column from the “Column” dropdown. This is the column in which you want to see null/empty values.
3. Choose the operator “Equal” from the operator field and leave the value field blank.
4. By clicking on the “Run” button, you will get the rows where the selected column has null values.
“I selected the “Income” column, set the operator as ‘Equal,’ and left the value field blank. After selecting these fields, I clicked on ‘Run,’ and as a result, I obtained all the rows with missing value in the Income column as shown below.”
5. If you want to add additional filters, click on the “+ Add another filter“. This allows you to apply multiple filters to further refine your data based on different conditions.
6. Repeat steps 2 and 3 for each additional filter you want to add. Select the desired column and operator for each filter. After adding as many filters as you want, click on RUN to get the desired results.
7. If you are adding more than one filter, make sure to select the appropriate logical operator (AND / OR) based on your requirements.
- Select “AND” if you want to retrieve data only if all the selected filter conditions are satisfied.
- Select “OR” if you want to retrieve data if any one of the filters is satisfied.
Replace Or Remove
After analyzing the dataset, it has been observed that the Income column contains many missing values. To ensure data quality and accuracy, it is recommended to remove the rows where the Income value is missing. This can be achieved by using the Clean transformation rule
Clean transformation rule provides options to replace values with desired replacements or remove rows based on specific conditions such as matching a particular value or being blank/null. This allows you to clean and preprocess the data according to your requirements.
1 . Click on + Add Transform > Clean.
2. Choose the column name from which you want to remove or replace some values.
3. After clicking on the RUN button you will be able to remove those rows
4. To verify if the rows with missing values in the Income column have been removed, you can use the Filter transformation rule. Follow these steps:
- Select the Income column from the column field.
- Choose the “Equal” operator from the operator field.
- Leave the value field blank.
- Click on the RUN button.
This will filter the data and show only the rows where the Income column still contains missing values. If no rows are displayed, it indicates that the rows with missing values in the Income column have been successfully removed.
This indicates that the rows with missing values in the Income column have been successfully removed
Drop irrelevant columns
You can use the “Remove Column” transformation rule to remove any irrelevant column from the dataset. In this dataset I am going to remove the ID column as it is irrelevant and has no use.
- Select ID in the column field.
2. After clicking on the “RUN” button, you will obtain the dataset with the “ID” column removed.
You can see in the dataset , the ID column has been removed.
Save this data after adding transformation rules
To save the transformed data, follow these steps:
- Add all the desired transformation rules to the data as per your requirements.
2.At the top of the page, on the Japio Dataset Builder interface located at “https://builder.japio.com/public/datasetbuilder-view /” followed by the connector ID, you will find a field where you can enter a title for the dataset. Enter a title in this field to save the dataset with the specified title.
- Optionally, you can add a description for the dataset in the provided field (this step is optional but can be helpful for documentation purposes).
- Finally, click on the “Save” button to save the transformed data with the specified title and description.
By following these steps, you will be able to save the transformed data in Japio with a title and an optional description for future reference and usage.
Here i have taken No missing value in income column as dataset title name
How to use saved transformed dataset
There is two way to use saved transformed dataset
1. To proceed after saving data instantly
Click on “Proceed” to navigate to the desired page. If you want to continue transforming your data on the Dataset Builder page(“https://builder.japio.com/public/datasetbuilder-view/” followed by {connection id}/{dataset id} ), click on “Proceed” for “I want to transform data on the Dataset Builder page” Alternatively, if you want to visualize your data by adding a metric page ( https://builder.japio.com/public/metricbuilder/{dataset id} ), click on “Proceed” for “I want to add a metric with this dataset”
2. If you Already have saved dataset
- Go to Data Manager > My Datasets you will all your saved dataset here
- If you want to add more transformation rules then Click on Edit icon .
- If you want to visualize your data by adding various metrics then click on the metric arrow icon .
Download the transformed data for further usages
After you analyzed the data and transformed it, you can now use it wherever you need. For example, you may need this to train a model, use a BI tool like PowerBI or use in Data warehouse.
To download your transformed data follow the following steps
- Log in to Japio if you haven’t already done so.
- Go to the Data Manager section, which is typically located in the sidebar or menu.
- Click on “My Datasets” to access your saved datasets. Here, you will see a list of your datasets with the titles you provided when saving the data.
- After locating your desired dataset in Japio’s Data Manager > My Datasets section, you should be able to see a download icon. By clicking on this icon you will be able to download the dataset in either JSON format or CSV format, depending on your selection.
Data Visualization
To visualize your data, navigate to the page https://builder.japio.com/public/metricbuilder/ followed by the dataset ID. You can reach this page by following the instructions in the “How to use saved transformed dataset” section or by clicking on the “Proceed” button next to “I want to add a metric with this dataset” as discussed in the “To proceed after saving data instantly” section. Once you’re on this page, you can add metrics to analyze your data. Below are the metrics that have been added
Frequency of number of store purchases
- Click on the “Chart type” button and select “Column chart” if not selected .
- For the y-axis, choose the “Count” operator to calculate the frequency.
- Select “NumOfStorePurchases” as the column name for the y-axis.
- For the x-axis, select “NumOfStorePurchases” as well.
Mean of number of store purchases
Once you have selected the operator and column name according to your requirement, such as selecting “average” as the operator to calculate the mean and selecting “NumOfStorePurchases” as the column name from the column dropdown, you will obtain the key value for the mean added.
Observation:
- Most individuals purchase about 3 items from the store. The frequency decreases as the number of store purchases increases.
- Mean number of store purchases are 5.7.
To save this metric refer “save metrics” section
Save metrics
- After configuring the metric, you can save it by giving it a title. At the top of the page, you will see a field labeled “Click to add a title.” Click on that field and enter a title for the metric. This will allow you to save the metric with the specified title for future reference and use.
2. After clicking on the “Save” button, a dialog box will appear giving you two options to save your metrics. You can either choose to create a new dashboard specifically for these metrics or save them to an existing dashboard that you have already created.
Create New Dashboard
After selecting the option to create a new dashboard, you can follow the steps below:
- After clicking on save button you will be redirected to the https://app.japio.com/dashboard/view/ followed by metric id page where you can edit the metric or you can save more metrics there
2.After clicking on the “Edit” button on the https://app.japio.com/dashboard/view/ {followed by the metric id} page, you can click on the “Untitled” label and provide a name for the dashboard.
3. Click on Apply
Select Existing Dashboard
save metrics by choosing “Select Existing Dashboard” and choose your dashboard name such as “market analysis”
After selecting your existing dashboard, click on the Save button, and you will find all the metrics of that selected dashboard, including the newly added metric.
Conclusion
As we see above how Japio can help Data Scientists, Marketers and others to analyze their data without coding.