 Hey guys welcome to SSUnitech, Sushil this side and this is continuation of Power BI projects. So today we are going to start with a very new domain which is on the email marketing. So here you will see step by step things from scratch to end in the email marketing. So this is the first video of the email marketing. So here we will discuss about the email campaign analysis. So we will see step by step. So let's get started. So when forward if you haven't subscribed my channel so I would strongly request to you all please subscribe our channel and press the bell icon so you will get the newly uploaded videos first. So what is the email campaign analysis? So basically for example my organization is ecommerce organization and we are going to sale the things online. Amazon is the ecommerce website where we can purchase the items and items will be delivered on your place. According to your billing address. So here in the email marketing like we are getting the emails from the Amazon. So where we can get the coupon code and we can apply those coupon codes on your items and you will get the discount. So this is very similar like that. For example we are going to create any campaign and we are going to send the email to one like users and users will get those emails and if they will open and click the link which will be available on that campaign email. So that analysis will appear in this dashboard. So let's get started in this dashboard. So first of all you can see here we have the campaign code like you can see all these campaigns after that the second thing that is year of email send. So here you can see 2k 18 2k 19 and 2k 20 only. So we have only three years data then month of email send. So here you can see Jan to December after that the last you can see day of email send. So here you can see from 1 to 30 I guess so 31. So this is the filters that will be appear on this after that you can see campaign KPI. So this campaign KPI will be going to have total campaigns like how many campaign we have created. So we have total 269 campaigns in this data set after that you can see unique account like customer got the email and they are going to click on. That link which is provided on that email. So this is the unique click count after that unique open count you can see like 1000 customer got the email and only 5 of them has been open the email which we have sent. So that will be unique open count after that total email click count. So this is the total email click count for example and the customer and I am getting the email and on that email I am going to click 3 times 4 times 5 times. So this is basically total email click counts in the unique click count like for individual user on the email will click only one like I am getting the email 4 times for the campaign. So I am going to click only 4 times. So that will be the unique click count after that total email open count. So this is the total how many times customer has been opened the email after that total bounced email. So what is this bounced email? So basically this is the bounced email like we are going to send the email in bulk around 1 lakh at a time. And how many has been delivered and how many has not been delivered? We can see over here like total bounced email like these are not going to delivered after that we can see the total delivered email. So these are delivered emails and here you can see total email send. So how many emails we have sent? So we have sent like 10,000 emails in our data set in the real time this figure will be very high. And in the right side you can see member campaign analysis. So here members who open the email like we can see 1,602. So this is the user count which has been opened the email after that you can see member who clicked on the email. This is the 157. This is the link which you have provided if member are going to click on those links. So this is that one. After that here you can see the CTC like click through rate after that bounce rate then open rate then delivery rate. So this is the exact formula that we have I will show you later on this video and how we can calculate all this formula. So this is going to display CTR bounce rate open rate and delivery rate. After that in the right side you can see email send status. So this is going to indicate like how many percentage send and how many is rejected. So we can get that in this pie chart. After that here we can see the month wise email send. So here we can see Jan 2020 then Jan 2019 Jan 2018. So this is how many emails we have sent on the monthly basis. So here we can see month and year in the x-axis. After that in the bottom we are having this table. In this table we have campaign code and here we can see the how many emails has been bounced. Then delivered email click count unique click count each informations which is required to analysis about the email. So this is overall about the email campaign analysis and we will start and try to design this dashboard step by step. So before going forward let's have a quick look of the formula that will be used. So here first this is the click rate. So this is the click rate which is how many emails has been clicked. And after that we need to divide that email send minus bounce. So this is the formula for the clicked rate. Here you can see the bounce rate which is the bounced email divide by send email multiplied by 100. That will be the bounce rate. Here you can see email open rate. So the formula for the email open rate is number of email read divided by number of delivered email multiplied by 100. Read means the open. After that you can see the delivery rate. So this is the delivered email divide by total send multiplied by 100. So it is going to indicate the delivered rate. And this is again the second formula for the delivery rate. Either you can use the first one or you can use the second one as per your company recommendation. So I am going to use the first one not the second one. So now go to on the power by desktop. So this is the training one. So first let's get started. Try to get the data from the source. So here go to on the get data click on the Excel. And we need to wait few seconds. So this is our Excel file. Let me click on that and click on open. We need to wait few seconds. It will be connected. So here we can see all these tables. So I am going to use email communication table. So this is the TVL communication. Now click on the load. So this will be loaded. And we need to wait few seconds. Now the table has been loaded successfully. So first of all we will start one by one. Like we need to have this email campaign analysis as a text box after that the total rate analysis and after that the CTR bounds open and all. So before going to design all these let me have this filters like campaign code after that year of email send then month of email send and day of email send. So go to on the training one. First of all go to on the view and try to use a theme. So I am going to use this theme. So we can click on this and your pace will be like that. Now I am going to have the insert button and here go to on the text box and click on that. And let me try to make it bigger and narrow down from the bottom and put it like that. And here we should write the email campaign analysis. After that go to on this size and try to make it bigger like 36. And here go to on the background color. Let me off that and here go to on this and try to make it white and make it bold. So you can see this email campaign analysis. Try to make it little bit bigger. Try to put it on the meeting. So this is the email campaign analysis. Next thing I am going to use the filters. Those are required. So first filter that will be the campaign code. So go to on the slicer and put it on the left side. Try to make it little bit smaller and adjust like that. And try to drag and drop this campaign code right here. So now you can see all these campaign code. Try to click on this make it as drop down. And after that we can little bit narrow down from the bottom. After that go to on the format and here you can see this slicer header. So I am not going to use this slicer header. And here go to on the title and I am going to use this title. So in the title it should be campaign code. Now here you can see this font color. Let me put it as white and background color that should be this color. So now it looks good after that here go to on the background color. Let me off that and after here you can see the items. So click on the item. The font color it should be white. We can select as white in the background color. We can use this. So now it looks good. Now we can make it little bit smaller from the right side. Now let me copy this and paste. So second will have the year of campaign. Now go to on the fields. Try to cancel this and here you can see email send. Let me drag and drop this and here go to on the hierarchy of this. And I am going to use the year only. So we can delete other part. So it should have the year only and go to on the format. Go to on the title and let me try to rename this as year of email send. So we can call this as year of email send. Now let me try to copy this and paste one more time and this time I am going to use as month. So in the format in the title I am going to use this as month. Go to on the fields and here let me use as email send after that date hierarchy. This time I am going to use this as month. Now let me try to copy and paste one more time. So this time it will be going to show as day. So go to on the title instead of the month it should be day. So we can rename this as day. Go to on the fields and here let me try to make it as date. Go to on the hierarchy and this time let me delete year quarter month only day should be here. Now let me check the values. So here we can see the campaign code. Here we can see the year. Here we can see the month and here we can see the date. The only one thing that is remaining like here we don't see the select all option. Select this size go to on the format selection control here. So select all for second as well for the month and last for the day. Now you can see this one thing that we can see the background color of this title. It shouldn't good. So go to on the format and try to click on the title in the background color. We want to use this as no field. Similarly go to on the format try to put on the second then third and then fourth. Now it looks good. The second thing that we need to take care for the total rate analysis as you can see over here. So this is the total rate analysis. So now go to on training one. So for that go to on the insert and here click on this click on rectangle. Let me put it on the right side of this. Now let me try to make it bigger and bigger from the bottom as well. Here go to on the fill and off this and go to on the line and here the width we should off this like zero. So now we can see this after that here we want to have the border. So click on the border and color it should be white. So now it looks good after that go to on the insert and click on the text box. So let me add a text box and try to make it smaller and put it on the rectangle and try to make it bigger. And here we can also increase the email campaign analysis text box. Here we should write this as total rate analysis. So we can call this as total rate after that analysis. Now go here and try to make it as a red color make it as bold and try to make it as 14 or we can 16. Now make it bold we need to change this as aerial and make it as 14 little bit 16. Now it looks good like total rate analysis after that go to on the existing one. So here we can see the CTR then bounce rate open rate then delivery rate. So go back to the training one and try to add four text box first put it on the below of this and call this as CTR. So it should be CTR after that we can call this as click through rate. Let me try to background color this we don't want a background color of this. We need to have this color as white and little bit increase the size like 14 make it as bold. So we can see like CTR which is click through rate. Now let me try to copy this text box and paste one more time. Now put it on the right side of this CTR and it should be bounce rate. So let me call this as BR bounce rate. Let me try to make it smaller from the right side. Let me try to copy and paste one more time and put it on the right side of this and this is the open rate. So we can call this as OP open rate. Now let me try to copy this one more time and paste and it should be delivery rate. So we can call this as delivery rate. So this is delivery rate. Now go to on the existing one and here you can see these three formula that will be used first for the CTR, second for the bounce rate after that the operate then delivery rate. So this will be calculated later. Here go to on the campaign KPIs. So go to on the training one and try to add one more rectangle and it should be below of this. Let me try to make it bigger little bit like that. We don't want to see the fill of this and here let me increase little bit size like that. Now we can do one more thing like go to on the line and width it should be 0. Now we need to add first text box and put it over here and let me put this as this and try to make it bigger. Go to on this, go to on border and try to add the border with the white color. Here we can write this as campaign KPI. So this is the campaign KPI. Let me try to make it bold, increase the size here, change the format after that it should be red color. That looks good. Now we need to add few text boxes as we can see in the distinct one like total campaign, then unique click count, unique open count all these. So go to on the training one and try to add the first text box and try to add below of this. And here make it smaller, make it smaller from the right side as well. Go to on the background color, we do not want to see the background color of this and it is the total campaign. So let me write this as total campaign and here make it bold, increase the size little bit and change the font family and here it should be white color. So now we can see all this, we need to increase size like 18, it looks good. Now go to here and try to add the border with the white color. So this looks good. So this is the total campaign. The second one let me try to copy this and paste one more time below of this and let me call this as unique click count. So unique click count. Now we need to increase from the right side like this. Now let me try to increase this rectangle as well and this text box as well. Now so this is the total campaign then unique click count. Now let me try to copy this and paste and this should be then it should be unique open count. So let me call this as unique open count. Now let me copy and paste one more time and this should be total email click count. So let me call this as total email click count. Now we can see it is not going to fit in our condition. So let me try to make it as 16 and 16 this as well and 16 this after that this count it should be 16. Now it looks good. Now let me try to copy this and paste one more time. So that will be total email open count. So this is the total email open count after that let me try to copy and paste one more time and put it on the below of this. This is the total bounce email. So let me call this as total bounce email. After that let me try to copy one more time and paste it here and put it on the below of this. So this will have the total delivered email. So let me call this as total delivered email. The last one that we need to copy will be the total email sent. So let me try to copy and paste one more time put it below of this total email send. So we can call this as total email send. Now we can see this and it looks good. Now after that we need to try to create few measures by using the DAX formula and this is very important. So first let me try to have the total campaign. So for that let me add a card and on this card let me try to drag and drop the campaign code. And here let me have this count distinct. So we can see 269 go to on the format go to on the category. We don't want to see the category go to on the data level and here it should be 14. Now go to on below in the color we should have this as white. Now go to on the below and here we can see the background. So we don't want to see the background color of this. Let me try to make it smaller and try to put on the right side of that. Let me try to have this after that go to on the data level and try to increase the size little bit like 16. That makes sense after that go to on the border and try to add the border with the white color instead of the black. So it should have the white color. So the first thing that we have done with the 269. Now the second is the unique click count. So how we can calculate the unique click count. So here you can see the unique click count is the 156. So here go to on the training one go to on the data view. So in this data view you can see we have the email click count. Let me have the data so we can see 0 1 2 3 4 7 8 and 12 and this is a total email click count. But we want to see the unique click count. So for that let me add a new column on this. And after that we need to call this as unique click. So this will have the column name and here we need to check if the value in the email click count. If this value is greater than 0 then we want to see 1 otherwise we want to see 0. So here what it will do it will going to return the 1 if value is greater than 1. Doesn't matter what value we have in the email click count but it will going to return only 1. So now go to here and here let me try to copy this and paste put it on the below of this. And here let me try to drag and drop this unique click on this and it should be the sum. So now we can see the value that is 156 which is the same in the existing one. The second that is the unique open count the same thing that we need to do for the open count as well. So here you can see the email open count. For the email open count we need to add one more column and this time it should be unique open. So here we need to check if the value of this email open count is greater than 0 then 1 otherwise 0. Now go to on the report view and here let me try to copy this and paste this card put it on the below of this unique open count. And here you can see unique open let me try to drag and drop it here and here we can see the sum. So it is 1609 which is the same in the existing one. After that you can see the email total email click count. So go to on the training one. So total email click count let me try to copy and paste and put it on below of this. So in the total email click count here we have the email click count. So we can directly use that column and drag and drop it here and it should be the sum. So it will have the total email click count for the total email open count. We can use that column which is the email open count. So we can directly drag and drop that column and check it should be some. So we can see this is the total email open count. After that the next that will be the total bounced email. Now go to on the data and here we can see we should have a column which is the email send status. So here you can see the send bounced. So we need to add one more column where we need to check if this status is bounced then 1 otherwise 0. So let me click on the new column and here let me call this as bounced email. And here let me have the if status of this is equals to bounced then 1 otherwise 0. Let me put it enter and here we need to wait few seconds and let me check the value. Now go to on the report view. Let me try to copy this and paste and put it below of this and here go to on the bounced email try to drag and drop in the fields. And here let me check this it should be some. So this is the total bounced email. Now here for the total delivered email again we need to go on the data in the status if that is send then that will be delivered. Otherwise not delivered. So let me add one more column and that will have the delivered email. So let me call this as delivered email and here let me check if the status that is send then 1 otherwise 0. Let me put enter and this is the delivered email go to on the report view and here let me try to copy and paste put it below of this. And here we can see this delivered email so we can drag and drop it here it should be some. So this is the total delivered email that you can see the last that is the total email send. So we need to calculate this. So for that let me try to copy this and paste one more time put it below of that. Now here we can see we have a column that is is send if you go on the data and here we can see the column which is on the left side which is indicating is email send one is indicating the email is sent. So for that we need to use that column is email send drag and drop it here and here we need to use the sum. So it is going to indicate 10k but we don't want to see the value in the thousands but on the data level display unit it should be none. So here you can see 1000. So this part has been done now we need to take care for the how we can calculate the CTR bounced rate open rate and delivery rate. So now let's calculate CTR. So the formula for the CTR as I already told you the total email clicked divided by total email send minus total email bounced. So this is the formula for the CTR. So for that go to on the training one and here let me add a new measure. So go to on the modeling and here you can see officer to add a new measure. So let me click on the new measure. So now let me call this as CTR. So as I told you it should have the total email clicked. So let me have the sum of email click count and it should be divided by first total send email. So for that we should use sum of is email sent close the bracket track the bounce one. So for that as we have already calculated one more column which is the bounced email. So let me have the sum of that column. So this is the bounced email. Let me close the bracket and put inter. So this will have the CTR. Now we need to wait few seconds and here let me add a new card and try to drag and drop that measure which we have created. So we can see the value this is 0.02 but we want to see this value as percentage. So go to on that measure and here you can see this percentage. So we can click on that or you can see it is going to show like 2.46% and go to on the format and try to do the formatting like category. You don't want to see the category and try to make it a little bit smaller and put it below of this CTR. And here let me try to put more smaller and like this. So it is going to show the value of the CTR. Next we need to calculate the BR bounce rate. So how we can calculate that? As you can see in the formula. So bounce rate that will be the bounced email divided by send email multiplied by 100. So this will be the bounce rate. So go to on the modeling and try to add a new measure. So click on that. Let me call this as BR which is bounced email. Now it should be sum of bounced email. Then we need to divide this by the total email send. So go to on the sum of is is email send. Now we can put enter. So we need to multiply it by 100. So we can get the value of this bounce rate. So instead of multiplying I am going to indicate this as percentage. So it is automatically multiplied by 100. Now let me try to copy this and paste and put it on the right side of the CTR. Now try to make it a little bit smaller like this. And here go to on the format and go to on the data level. And here we can see the 45. So let me put it as 30. Similarly use the format and use in the CTR. Now here you can see this is indicating CTR. So we have calculated bounce rate. So try to drag and drop in the feeds. So here we can see 1.61 percentage which is the bounce rate. Now go to on the next formula which is going to email open rate. So it is number of email reads divided by number of delivered emails and multiplied by 100. So we need to add one more measure where we will calculate the number of read emails which will be the open one and need to divide the delivered one. So go to on the training and here let me add a new measure. So click on the new measure and now let me call this as open rate. So it should be OR and sum of total email read which will be the open one. So here email open count then divide this sum of delivered email. So we have calculated one more column which is delivered email. Let me close the bracket and put enter. Now we need to wait few seconds and try to format this as percentage. So we can click on the percentage. Now we can see this is open rate. Now let me try to copy this card and paste one more time. Put it on the right side of the bounce rate and here let me try to drag and drop the OR which is the open rate. Now you can see 22.7% is open rate. Next will be the delivery rate. So again go to on the formula for the delivery rate. It should be the delivered email divided by total send multiplied by 100 which will be going to indicate the percentage. So we need to divide the delivered email with the total send. So go to on the training one and try to add a new measure. So we can click on that and it should be called as delivered rate like dr then sum of total delivered email. So we have calculated column which is delivered email should be divided by sum of total email send which is the is email send and sum of that. Put it enter and here we need to change this as percentage. So we can click on the percentage and now we need to wait few seconds. So let me try to copy this and paste it here and put it on the right side on the delivery rate and try to make it a little bit bigger from the right and left both sides. Now here we have calculated the delivery rate. So we can drag and drop that into the fields. Here we can see we don't have any decimal places. So let me use two decimal places. So we will see the data like it has like we can see 92.02% which is the delivery rate. Now let me try to adjust this little bit and put it little bit left side. Now try to make this after that this for CTR like this. Now it seems good. So what we have done as of now we have calculated the CTR then bounce rate then open rate and then delivery rate. Here we can see the campaign KPI we need to use a border. So go to on the border and try to add a border with the black color. So it seems little bit different from this total rate analysis. Now go to on the existing one and next we need to calculate few things that is the member campaign analysis. So next we need to calculate that. So how we can calculate this member who opened the email member who clicked on the email. So these two we need to calculate. So for that go to the training one and go to on the model view and here let me add a new column. So here what we are going to check. So who opened the email. So let me call this as member open. Here we need to check if member email open count is greater than 0 then we want to see the party UID. Otherwise we don't want to see anything. So we can leave it and put in there. Now we need to wait few seconds. So it will have going to have one more column. If email will be opened then we can see the member open field which will have the customer ID like this as you can see. Next as you can see in the existing one we need to calculate member who clicked on the email. So go to on the training one and try to add one more column and this time instead of the open we need to check for the clicked count. So here let me call this column as member clicked. So we can call this as member clicked. And we need to check if the value of the email click count is greater than 0 then we want to see the party UID close the bracket and put enter. So here in this column if member will click on that email so we will see the member ID right here. Now go to on the design view and here let me try to add one more rectangle. So go to on the insert and here click on the add shapes and add a rectangle. Try to put right side of this campaign KPI and try to make it bigger like this. After that we don't want to see the fill. Go to on the lines we don't want to see the line. After that try to go on the border and add a border with the white color. So we can see like this. So it seems good. Now what next we need to do? Here we need to design as we has like member campaign analysis the first. So we need to add a text box and try to put over here and adjust this like this. So it will be called as member campaign analysis. So let me call this as member campaign analysis. Now let me try to increase the size as 16 I guess. Put it as bold and this color will be red color. So it seems good like member campaign analysis. We can also change the form family as Ariel. So now it seems good. So for this text box we need to add the border. So click on the border and here it should be on the black color so it seems good. Now we can see the member campaign analysis. Now we need to add two more text boxes which will be going to have member who picked email member who open email. So I had a text box first and try to put this below of this member campaign analysis and here adjust this little bit like this and in this we want to see the member who opened email. Let me try to change the font family as Ariel and increase the size as 18 or 20 I guess. Put it on the middle and make it as bold and we can put one to enter. So it should be like member who opened email. Now let me try to copy this and paste one more time. Now let me try to copy this and paste one more time and put it below of this. So this time we are going to see the member who clicked on the email. So let me try to adjust this first. After that go to here instead of the open we should see clicked like this. Now here we can adjust this little bit on the right side to make this in alignment. Now it seems good. After that what next we want to see. So here we want to see the total member who opened the email and the member who clicked on the email. So before going forward let me add the border of this so we can differentiate between these two. Now it seems good. Let me try to add the border for the first one as well. Now it seems good. Here on the right side member who opened email. So for that let me add a card and on this card put it on the right side of this and try to make this. First of all go to on the table and here we have calculated two columns. First is member open. So we can drag and drop the member open and let me check this. So we can see this count. Now we can see the value which is 1609. Go to on the format. We don't want to see the category. We don't want to see the background color. We can see the data level and this should be white color. Now it seems good. Now let me try to copy this and paste one more time. Put it below of this and here go to on the fields and here we have calculated member who clicked on the email. So we can drag and drop and it should be count of this. So now we can see 156. The only one thing that is remaining like we need to add the border so we can differentiate between these two. So go to on the format in the border. Let me try to put this as white color in the border. Similarly for the first one as well so it should be white color. Now it seems good. Like we are having the information for member campaign analysis. Next go to on the existing one. Here we can see email send status. So here we need to add this pie chart which will be going to see the member status with the percentages. So let me add a pie chart. So this is the pie chart and try to increase the size of this and increase the size of this as well. Like that. Now here we can see we are having a column which will be going to indicate the status of the email. If you will go on the model and you will see that column. So scroll on the left side and here we can see email send status. So we can see send, bounce, default, rejected and other options. So let me try to drag and drop that in the legend. So here we should have email send status so we can drag and drop in the legend. In the values go to on the campaign code and try to drag and drop in the values. And here it should be count of this. So now we can see all these values. But go to on the format. We don't want to see this format like this. So go to on the legend. We don't want to see the legend first. Second go to on the detail level. In the detail level we just want to see the category and percentage of totals. Like send is 92.02% and other. Now here go to the data colors. In the data colors we don't want to see the data color like this. So go to on the send. We want to see like this. Go to on the rejected. We can see the red. Null we can leave it. For the bounced email we want to see other colors like yellow. For the soft bounce we can put this as black. For the soft bounce again. So put this as other color which should be like this one. And here for the deferred we can use blue. So we can use according to our requirement. Next here we can see the count of campaign codes by email status. So we don't want to see this title. We want to see the email send status. So go to on the title. And go to on the color and try to call this as email send status. Scroll down little bit. Font color we want to see this as white. So the background color let me add this color. And try to increase the size little bit like 15 I guess. So it seems good. Next what we need to do here we don't have the border of this. So we can also add the border. Let me go and add that. So it has the border with the black color. Now it seems good. Next go to on the existing one. Here we want to see the month wise email send status. So how we can do that. So here directly we don't have any column which will be going to contain the month and year. So go to on the model view. So now let me add a column. So click on this new column and it will be going to combine the year and month. So here go to the columns. Let me call this column as month year. And after that here if we will extract the month from the date. So we will get the numbers. So we don't want to see the numbers. So we should use the format. And after that we have send date. So we can use that and put comma. And here we use three m's and close the bracket. Put the enter. Take April and other months. Now we need to concatenate this with the year. So how we can do that. So for that go here and try to use the concatenate. And text one comma in the second test we want to use as dash close the bracket. And we need to use one more concatenate. So let me use the concatenate with comma. And this time we want to extract the year part from this email send date. Now we can close the bracket and put enter. Now we will see the month and year. It should have the month and year. Now go to on the report view and try to add a column chart. So we can add this column chart and here in the axis we want to see the month year which we have calculated. So we can see we should have a column with the month year. We can drag this and drop in the axis. So now we will see the values on this. But in the values what we want to see. So we want to see the is email send. So we have that column like total how many emails we have send we will see right here. So here we can see the March then Feb then other and if you scroll on the right side then we will see all these values and it is sorted according to the set. So next what we want to see. We have done about this but here we need to do little bit formatting like we do not want to see these titles on the x axis and y axis and what title it is going to show we do not want to see that. So go to on the format and here go to on the x axis and off the title. So here we do not want to see the title. Go to on the y axis and try to off the title off. So here we can off this title as well. In the main title go to on that and try to modify that. So this is the title. So we do not want to see that title. We want to see the month wise email send. So we can write here month wise email send. Now scroll down little bit in the font color let me as this white and in the background color let me as this color and try to increase the size little bit like 15. So now we can see the value which is the month wise email send. Now in the bottom of this we need to add a table. So let me add a table and try to put bottom of this make it a little bit smaller make it bigger from the right side. First we want to use the campaign code. So we can drag and drop in the web news. So it is the campaign code. After that we will see how many columns we have. So like bounced delivered email click count unique click then email open count all these we need to use one by one. So go to on the training one and here next we want to try to drag and drop the br which is the bounce rate. Next we have the deliver rate so we can drag and drop that one as well after that we have email click count. So we have that column which is the email click count we can drag and drop and it should be some by default it is looks good after that we have a unique click. So we have already calculated one more column which indicates the unique click count. So here we can see this unique click we can try to drag and drop it here after that email open count so we have this column we can drag and drop it here and check the value it should be some it looks good after that unique open try to drag and drop this over here and it should be some again so we can see all these after that we can see total email send. So how we can get that we have a column which is email send we can drag and drop it here and this will be some as well it looks good the last thing that we need to take care for the delivered email so we can try to drag this and drop it here so we can use according to our requirement so here I am going to use all these columns now go to on the format and here go to on the style in the style let me have this as bold header so it should be like this now go on the values in the values let me try to increase the size it is 10 as of now let me use this as 12 now we can see all these values now we can also little bit adjust this header of this and other things like column headers what background we want like if you want this background so we can also use that and we can also little bit adjust this and here we can see the Br so it is not going to very good name let me try to rename this over here it should be bounce rate so let me call this as bounce rate so it should be like bounce rate after that it should be delivery rate so we can modify this as well so you can play on this in the upcoming videos we will see continuation of this email marketing so this is the first dashboard which we have created for the email campaign analysis next will be the member campaign analysis and after that we will see many more dashboards in the upcoming videos thank you so much for watching this video if you like this video please subscribe our channel to get many more videos see you in the next video