Tag Archives: Generating
Generating JSON Document from SQL Query
If you are looking for ways to generate JSON files from SQL server data or if you need to pass JSON data to downstream systems, then you are reading the proper blog! This blog describes how to utilize the FOR JSON feature of SQL server to generate JSON data, and eventually JSON file with well-formatted JSON data in it.
Generating JSON data is straightforward. The syntax is as follows:
Select
contactid,
firstname,
lastname
From contact
FOR JSON PATH
This is as simple as appending your SQL select query with the FOR JSON clause. Multiple options are available for the structure of the output, including FOR JSON PATH and FOR JSON AUTO. More information can be found here: Microsoft Documentation.
To generate files out of the retrieved JSON data, a little bit of coding is necessary, as SQL returns chunks of rows of JSON data and each row is not a complete JSON. To combine the JSON data retrieved by the FOR JSON clause to generate complete JSON, use a small piece of C# code as follows:
var queryWithForJson = “SELECT … FOR JSON”;
var conn = new SqlConnection(“<connection string>”);
var cmd = new SqlCommand(queryWithForJson, conn);
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
jsonResult.Append(“[]”);
}
else
{
while (reader.Read())
{
jsonResult.Append(reader.GetValue(0).ToString());
}
}
One thing to keep in mind is that if the result of the SQL query is too large, the JSON builder might produce a memory exception. To avoid this, open a file and start appending incoming rows into it as the code loops through each retrieved row. Example code snippet:
File.AppendAllText(path, jsonResult);
Hopefully this helps you generate a JSON document from a SQL query.
As always, happy D365’ing!
See the KPIs that Dynamics 365 is Generating for Moneris
Moneris has 600 users on Microsoft Dynamics 365, managing the customer experience from onboarding and post-sale, to call center and field service interactions. Beyond the customer-facing roles, which make up about half of their end users, Moneris also uses Dynamics 365 for some of their back-office and risk management teams. This ensures that all customer information is tracked and managed in one system. We sat down with Allan Measor, Vice President, Business Transformation and Customer Enablement at Moneris to hear about their experience.
Moneris overview:
Why did you choose to start using Dynamics 365 for all new support inquiries immediately following the implementation?
“For a short time, we ran our old ticket system as a reference tool alongside Dynamics 365. We analyzed our old ticket data, and over 80% of our inquiries were one and done, with 90% solved in a week or less. Because of this, we chose not to import our old tickets. In terms of data migration into Dynamics 365, we imported standard items like customer company data, name, address, account numbers, email address, phone numbers, all key payment device data, and work order information. We also authored over 1,900 knowledge articles into Dynamics 365 for reference.”
Why did you choose to be on the Cloud?
“Our legacy systems tended to take longer in terms of features and updates, so the idea that Cloud solutions get features firsts, thereby empowering our organization to be more current, was appealing to us. We also could avoid costly infrastructure investments and gain reliability and redundancy when on the cloud.”
Why did you choose Dynamics 365 for your CRM platform?
“Since we were already using Office 365, our team members were able to easily adjust to the Dynamics 365 and our customers now receive a seamless experience because of our investment in the Microsoft stack. The value of Microsoft’s Canadian data center, the feature richness of Dynamics 365, and the ease of integration into our existing technology footprint were key deciding factors for us.”
Why did you choose PowerObjects as your partner?
“PowerObjects came highly recommended from multiple individuals at Microsoft. We also had several vendor meetings with them, and ultimately liked their collaborative approach. We looked at other big consulting firms but liked the agile and flexible approach that PowerObjects offers, along with their deep Dynamics experience, so we went ahead with PowerObjects and haven’t regretted it once. We also liked that PowerObjects has an office and local staff right here in Toronto.”
What are some of the KPIs that you’ve seen since implementing Dynamics 365?
“We’ve seen a 4% reduction in average handle times (AHT) and a 5% increase in first call resolution (FCR) on top of our already high FCR which was around 80%. We’ve also seen improvements in our onboarding processes for new team members since there are more standardized processes in place, resulting in a decrease in onboarding time and an increase in employee performance. Additionally, we’re experiencing better Interactive Voice Response to Dynamics integration, improving screen pop accuracy for Customer Service Representatives.”
To learn more and read the full case study, click here.
Happy Dynamics 365’ing!
SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python
The series so far:
- SQL Server Machine Learning Services – Part 1: Python Basics
- SQL Server Machine Learning Services – Part 2: Python Data Frames
- SQL Server Machine Learning Services – Part 3: Plotting Data with Python
- SQL Server Machine Learning Services – Part 4: Finding Data Anomalies with Python
- SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python
SQL Server Machine Learning Services (MLS) offers a wide range of options for working with the Python language within the context of a SQL Server database. This series has covered a number of those options, with the goal of familiarizing you with some of Python’s many capabilities. One of the most important of these capabilities, along with analyzing data, is being able to visualize data. You were introduced to many of these concepts in the third article, which focused on how to generate individual charts. This article takes that discussion a step further by demonstrating how to generate multiple charts that together provide a complete picture of the underlying data.
The examples in this article are based on data from the Titanic dataset, available as a .csv file from the site https://vincentarelbundock.github.io/Rdatasets/datasets.html. There you can find an assortment of sample datasets, available in both .csv and .doc formats. This is a great resource to keep in mind as you’re trying out various MLS features, whether using the R language or Python.
The Titanic dataset includes a passenger list from the Titanic’s infamous voyage. For each passenger, the data includes the individual’s name, age, gender, service class and whether the passenger survived. The following figure shows a sample of the data.
A value of 1 in the Survived column indicates that the passenger survived the voyage. A value of 0 indicates that the passenger did not. The SexCode column is merely a binary duplicate of the Sex column. A value of 0 indicates that the passenger was male, and a value of 1 indicates that the passenger was female.
I created the examples for this article in SQL Server Management Studio (SSMS). Each example retrieves data from a locally saved file named titanic.csv, which contains the Titanic dataset. In most cases, if you’re running a Python script within the context of the SQL Server databases, you’ll likely want to use data from that database, either instead of or in addition to a .csv data, but the principles covered in this article apply regardless of where the data originates, and using a .csv file helps to keep things simple, while demonstrating several important concepts, including how to retrieve data from that file.
Retrieving data from a .csv file
Python, with the help of the pandas module, makes it easy to retrieve data from a .csv file and save the data to a DataFrame object. The pandas module contains a variety of tools for creating and working with data frames, including the read_csv function, which does exactly what the name suggests, reads data from a .csv file.
The Python script in following example uses the function to first retrieve data from the titanic.csv file and then to update the resulting dataset in preparation for grouping the data for the visualizations:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE@pscriptNVARCHAR(MAX); SET@pscript=N‘ # import pandas module import pandas as pd # define the titanic data frame titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0) titanic = titanic[titanic[“Age”].notnull()] titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”}) for i, row in titanic.iterrows(): if (row[“Age”].is_integer() == False): titanic.drop(i, inplace=True) print(titanic)’; EXECsp_execute_external_script @language=N‘Python’, @script=@pscript; GO |
The sp_execute_external_script stored procedure, which is used to run Python and R scripts, should be well familiar to you by now. If you have any questions about the procedure works, refer to the first article in this series. In this article, we focus exclusively on the Python script.
The first statement in the script imports the pandas module and assigns the pd alias:
|
importpandas aspd |
You can then use the pd alias to call the read_csv function, passing in the full path to the titanic.csv file as the first parameter value:
|
titanic=pd.read_csv(“c:\datafiles\titanic.csv”,index_col=0) |
On my system, the titanic.csv file is saved to the c:\datafiles\ folder. Be sure to update the path as necessary if you plan to try out this example or those that follow.
The second function parameter is index_col, which tells the function to use the specified column as the index for the outputted data frame. The value 0 refers to the first column, which is unnamed in the Titanic dataset. Without this parameter, the data frame’s index would be auto-generated.
When the read_csv function runs, it retrieves the data from the file and saves it to a DataFrame object, which, in this case, is assigned to the titanic variable. You can then use this variable to modify the data frame or access its data. For example, the original dataset includes a number of passengers whose ages were unknown, listed as NA in the .csv file. You might choose to remove those rows from the dataset, which you can do by using the notnull function:
|
titanic=titanic[titanic[“Age”].notnull()] |
To use the notnull function, you merely append it to the column name. Notice, however, that you must first call the dataset and then, within brackets, reference the dataset again, along with the column name in its own set of brackets.
Perhaps the bigger issue here is whether it’s even necessary remove these rows. It turns out that over 500 rows have unknown ages, a significant portion of the dataset. (The original dataset includes over 1300 rows.) The examples later in article will use the groupby function to group the data and find the average ages in each group, along with the number of passengers. When aggregating grouped data, the groupby function automatically excludes null values, so in this case, the aggregated results will be the same whether or not you include the preceding statement.
I’ve included the statement here primarily to demonstrate how to remove rows that contain null values. (The subsequent examples also include this statement so they’re consistent as you build on them from one example to the next.) One advantage to removing the rows with null values is that the resulting dataset is smaller, which can be beneficial when working with large datasets. Keep in mind, however, that modifying datasets can sometimes impact the analytics or visualizations based on those datasets. Always be wary of how you manipulate data.
The next statement in the example replaces the numeric values in the Survived column with the values deceased and survived:
|
titanic[“Survived”]=titanic[“Survived”].map({0:“deceased”,1:“survived”}) |
To change values in this way, use the map function to specify the old and new values. You must enclose the assignments in curly braces, separating each set with a comma. For the assignments themselves, you separate the old value from the new value with a colon.
The next part of the Python script includes a for statement block that removes rows containing Age values that are not whole numbers:
|
fori,row intitanic.iterrows(): if(row[“Age”].is_integer()==False): titanic.drop(i,inplace=True) |
The Titanic dataset uses fractions for infant ages, and you might decide not to include these rows in your data. As with the notnull function, I’ve included this logic primarily to demonstrate a useful concept, in this case, how you can loop through the rows in a dataset to take an action on each row.
The initial for statement specifies the i and row variables, which represent the current index value and dataset row in each of the loop’s iterations. The statement also calls the iterrows function on the titanic data frame for iterating through the rows.
For each iteration, the for statement block runs an if statement, which evaluates whether the Age value for the current row is an integer, using the is_integer function. If the function evaluates to False, the value is not an integer, in which case the drop function is used to remove that row identified by the current i value. When using the drop function in this way, be sure to include the inplace parameter, set to True, to ensure that the data frame is updated in place, rather than copied.
Once again, you should determine whether you do, in fact, want to remove these rows. In this case, the aggregated passenger totals will be impacted by their removal because there will be eight fewer rows, so be sure to tread carefully when making these sorts of changes.
The final statement in the Python script uses the print function to return the titanic data to the SSMS console:
|
print(titanic) |
The following figure shows a portion of the results. Note that, when returning a dataset that is considered wide, Python wraps the columns, as indicated by the backslash to the right of the column names. As a result, only the index and the first four columns are included in this particular view.
If you scroll down the results, you’ll find the remaining columns, similar to those shown in the following figure.
Notice that the index value for the first row is 1. This is the first value from the first column from the original dataset. If the index for the titanic data frame had been auto-generated, the first value would be 0.
Grouping and aggregating the data
Once you get the titanic data frame where you want it, you can use the groupby function to group and aggregate the data. The following example uses the function to group the data first by the PClass column, then the Sex column, and finally the Survived column:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE@pscriptNVARCHAR(MAX); SET@pscript=N‘ # import pandas module import pandas as pd # define the titanic data frame titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0) titanic = titanic[titanic[“Age”].notnull()] titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”}) for i, row in titanic.iterrows(): if (row[“Age”].is_integer() == False): titanic.drop(i, inplace=True) # define the df data frame based on titanic data df = titanic.groupby([“PClass”, “Sex”, “Survived”], as_index=False).agg({“Age”: [“mean”, “count”]}) df[“Age”] = df[“Age”].round(2) df.columns = [“class”, “gender”, “status”, “age”, “count”] print(df)’; EXECsp_execute_external_script @language=N‘Python’, @script=@pscript; GO |
You call the groupby function by tagging it onto the titanic data frame, specifying the three columns on which to base the grouping (in the order that grouping should occur):
|
df=titanic.groupby([“PClass”,“Sex”,“Survived”], as_index=False).agg({“Age”:[“mean”,“count”]}) |
After specifying the grouped columns and as_index parameter, you add the agg function, which allows you to find the mean and count of the Age column values. (The groupby function is covered extensively in the first three articles of this series, so refer to them for more details about the function’s use.)
The groupby function results are assigned to the df variable. From there, you can use the variable to call the Age column in order to round the column’s values to two decimal points:
|
df[“Age”]=df[“Age”].round(2) |
As you can see, you need only call the round function and pass in 2 as the parameter argument. You can then assign names to the columns in the df data frame:
|
df.columns=[“class”,“gender”,“status”,“age”,“count”] |
That’s all you need to do to prepare the data for generating visualizations. The last statement in the example again uses the print function to return the data from the df data frame to the SSMS console. The following figure shows the results.
You now have a dataset that is grouped first by class, then by gender, and finally by the survival status. If the rows with fractional Age values had not been removed, the passenger counts would be slightly different.
Generating a bar chart
From the df data frame you can create multiple charts that provide different insights into the data, but before we dive into those, let’s start with a single chart. The following example creates a bar chart specific to the first-class passengers, showing the average age of the males and females who survived and who did not:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
DECLARE@pscriptNVARCHAR(MAX); SET@pscript=N‘ # import Python modules import matplotlib matplotlib.use(“PDF”) import matplotlib.pyplot as plt import matplotlib.patches as ptch import pandas as pd # define the titanic data frame titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0) titanic = titanic[titanic[“Age”].notnull()] titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”}) for i, row in titanic.iterrows(): if (row[“Age”].is_integer() == False): titanic.drop(i, inplace=True) # define the df data frame based on titanic data df = titanic.groupby([“PClass”, “Sex”, “Survived”], as_index=False).agg({“Age”: [“mean”, “count”]}) df[“Age”] = df[“Age”].round(2) df.columns = [“class”, “gender”, “status”, “age”, “count”] # define the subplot data frame based on df data df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “age”]] # define variables for the legend dc = ptch.Patch(color=”navy”, label=”deceased”) sv = ptch.Patch(color=”darkgreen”, label=”survived”) # define the ax1 bar chart ax1 = df1.plot.bar(x=”gender”, y=”age”, alpha=.8, color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax1.set_title(label=”First-class passengers”, y=1.02) ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax1.xaxis.label.set_visible(False) ax1.set_ylabel(“Average ages”, labelpad=10, fontsize=12) ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax1.legend(handles=[dc, sv], loc=”best”) # save the bar chart to PDF file plt.savefig(“c:\datafiles\titanic1.pdf”, bbox_inches=”tight”, pad_inches=.5)’; EXECsp_execute_external_script @language=N‘Python’, @script=@pscript; GO |
The Python script starts with several new import statements related to the matplotlib module, along with the use function, which sets PDF as the backend:
|
importmatplotlib matplotlib.use(“PDF”) importmatplotlib.pyplot asplt importmatplotlib.patches asptch |
The first three statements should be familiar to you. If they’re not, refer back to the third article. The fourth statement is new. It imports the patches package that is included with the matplotlib module. The patches package contains tools for refining the various charts. In this case, you’ll be using it to customize the chart’s legend.
The next step is to create a data subset based on the df data frame:
|
df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“age”]] |
The df1 data frame filters out all rows except those specific to first-class passengers. It also filters out all but the gender, status, and age columns. Filtering out the data in advance helps to simplify the code used to create the actual plot.
The next step is to define the property values for the legend. For this, you use the Patch function that is part of the patches package:
|
dc=ptch.Patch(color=“navy”,label=“deceased”) sv=ptch.Patch(color=“darkgreen”,label=“survived”) |
The goal is to provide a legend that is consistent with the colors of the chart’s bars, which will be color-coded to reflect whether a group represents passengers that survived or passengers that did not. You’ll use the dc and sv variables when you define your chart’s properties.
Once you have all the pieces in place, you can create your bar chart, using the plot.bar function available to the df1 data frame:
|
ax1=df1.plot.bar(x=“gender”,y=“age”,alpha=.8, color=df1.status.map({“deceased”:“navy”,“survived”:“darkgreen”})) |
You’ve seen most of these components before. The chart uses the gender column for the X-axis and the age column for the Y-axis. The alpha parameter sets the transparency to 80%. What’s different here is the color parameter. The value uses the map function, called from the status column, to set the bar’s color to navy if the value is deceased and to dark green if the value is survived.
From here, you can configure the other chart’s properties, using the ax1 variable to reference the chart object:
|
ax1.set_title(label=“First-class passengers”,y=1.02) ax1.set_xticklabels(labels=df1[“gender”],fontsize=9,rotation=0) ax1.xaxis.label.set_visible(False) ax1.set_ylabel(“Average ages”,labelpad=10,fontsize=12) ax1.grid(color=“slategray”,alpha=.5,linestyle=“dotted”,linewidth=.5) |
Most of this should be familiar to you as well. The only new statement is the third one, which uses the set_visible function to set the visibility of the X-axis label to False. This prevents the label from being displayed.
The next step is to configure the legend. For this, you call the legend function, passing in the dc and sv variables as values to the handles parameter:
|
ax1.legend(handles=[dc,sv],loc=“best”) |
Notice that the loc parameter is set to best. This setting lets Python determine the best location for the legend, based on how the bars are rendered.
The final step is to use the savefig function to save the chart to a .pdf file:
|
plt.savefig(“c:\datafiles\titanic1.pdf”, bbox_inches=“tight”,pad_inches=.5)‘; |
When you run the Python script, it should generate a chart similar to the one shown in the following figure.
The chart includes a bar for each first-class group, sized according to the average age of that group. In addition, each bar is color-coded to match the group’s status, with a legend that provides a key for how the colors are used.
Generating multiple bar charts
The bar chart above provides a straightforward, easy-to-understand visualization of the underlying data. Restricting the results to the first-class group makes it clear how the data is distributed. We could have incorporated all the data from the df dataset into the chart, but that might have made the results less clear, although in this case, one chart might have been okay, given that the df dataset includes only 12 rows.
However, another approach that can be extremely effective when visualizing data is to generate multiple related charts, as shown in the following example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
DECLARE@pscriptNVARCHAR(MAX); SET@pscript=N‘ # import Python modules import matplotlib matplotlib.use(“PDF”) import matplotlib.pyplot as plt import matplotlib.patches as ptch import pandas as pd # define the titanic data frame titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0) titanic = titanic[titanic[“Age”].notnull()] titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”}) for i, row in titanic.iterrows(): if (row[“Age”].is_integer() == False): titanic.drop(i, inplace=True) # define the df data frame based on titanic data df = titanic.groupby([“PClass”, “Sex”, “Survived”], as_index=False).agg({“Age”: [“mean”, “count”]}) df[“Age”] = df[“Age”].round(2) df.columns = [“class”, “gender”, “status”, “age”, “count”] # define the subplot data frames based on df data df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “age”]] df2 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “age”]] df3 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “age”]] # define variables for the legend dc = ptch.Patch(color=”navy”, label=”deceased”) sv = ptch.Patch(color=”darkgreen”, label=”survived”) # set up the subplot structure fig, (ax1, ax2, ax3) = plt.subplots(nrows=1, ncols=3, sharey=True, figsize=(12,6)) # define the ax1 subplot ax1 = df1.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax1, color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax1.set_title(label=”First-class passengers”, y=1.02) ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax1.xaxis.label.set_visible(False) ax1.set_ylabel(“Average ages”, labelpad=10, fontsize=12) ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax1.legend(handles=[dc, sv], loc=”best”) # define the ax2 subplot ax2 = df2.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax2, color=df2.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax2.set_title(label=”Second-class passengers”, y=1.02) ax2.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax2.xaxis.label.set_visible(False) ax2.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax2.legend(handles=[dc, sv], loc=”best”) # define the ax3 subplot ax3 = df3.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax3, color=df3.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax3.set_title(label=”Third-class passengers”, y=1.02) ax3.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax3.xaxis.label.set_visible(False) ax3.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax3.legend(handles=[dc, sv], loc=”best”) # save the bar chart to PDF file plt.savefig(“c:\datafiles\titanic2.pdf”, bbox_inches=”tight”, pad_inches=.5)’; EXECsp_execute_external_script @language=N‘Python’, @script=@pscript; GO |
Much of the code in the Python script is the same as the preceding example, with a few notable exceptions. The first is that the script now defines three subsets of data, one for each service class:
|
df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“age”]] df2=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“age”]] df3=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“age”]] |
The next addition to the script is a statement that creates the structure necessary for rendering multiple charts:
|
fig,(ax1,ax2,ax3)=plt.subplots(nrows=1, ncols=3,sharey=True,figsize=(12,6)) |
The statement uses the subplots function that is part of the matplotlib.pyplot package to define a structure that includes three charts, positioned in one row with three columns. The nrows parameter determines the number of rows, and the ncols parameter determines the number of columns, resulting in a total of three charts. This will cause the function to generate three AxesSubplot objects, which are saved to the ax1, ax2, and ax3 variables. The function also generates a Figure object, which is saved to the fig variable, although you don’t need to do anything specific with that variable going forward.
The third parameter in the subplots function is sharey, which is set to True. This will cause the Y-axis labels associated with the first chart to be shared across the row with all charts, rather than displaying the same labels for each chart. This is followed with the figsize parameter, which sets this figure’s width and height in inches.
The next step is to define each of three charts. The first definition is similar to the preceding example, with one important difference in the plot.bar function:
|
ax1=df1.plot.bar(x=“gender”,y=“age”,alpha=.8,ax=ax1, color=df1.status.map({“deceased”:“navy”,“survived”:“darkgreen”})) |
The function now includes the ax parameter, which instructs Python to assign this plot to the ax1AxesSubplot object. This ensures that the plot definition is incorporated into the single fig object.
The remaining chart definitions take the same approach, except that ax1 is swapped out for ax2 and ax3, respectively, and the df1 data frame is swapped out for df2 and df3. The two remaining chart definitions also do not define a Y-axis label. The following figure shows the resulting charts.
Notice that only the first chart includes the Y-axis labels and that each chart is specific to the class of service. In addition, the legend is positioned differently within the charts, according to how the bars are rendered.
Breaking data into smaller charts can make the groups easier to grasp and to compare. You can do the same thing with the passenger counts, as shown in the following example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
DECLARE@pscriptNVARCHAR(MAX); SET@pscript=N‘ # import Python modules import matplotlib matplotlib.use(“PDF”) import matplotlib.pyplot as plt import matplotlib.patches as ptch import pandas as pd # define the titanic data frame titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0) titanic = titanic[titanic[“Age”].notnull()] titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”}) for i, row in titanic.iterrows(): if (row[“Age”].is_integer() == False): titanic.drop(i, inplace=True) # define the df data frame based on titanic data df = titanic.groupby([“PClass”, “Sex”, “Survived”], as_index=False).agg({“Age”: [“mean”, “count”]}) df[“Age”] = df[“Age”].round(2) df.columns = [“class”, “gender”, “status”, “age”, “count”] # define the subplot data frames based on df data df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “count”]] df2 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “count”]] df3 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “count”]] # define variables for the legend dc = ptch.Patch(color=”navy”, label=”deceased”) sv = ptch.Patch(color=”darkgreen”, label=”survived”) # set up the subplot structure fig, (ax1, ax2, ax3) = plt.subplots(nrows=1, ncols=3, sharey=True, figsize=(12,6)) # define the ax1 subplot ax1 = df1.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax1, color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax1.set_title(label=”First-class passengers”, y=1.02) ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax1.xaxis.label.set_visible(False) ax1.set_ylabel(“Total passenger count”, labelpad=10, fontsize=12) ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax1.legend(handles=[dc, sv], loc=”best”) # define the ax2 subplot ax2 = df2.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax2, color=df2.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax2.set_title(label=”Second-class passengers”, y=1.02) ax2.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax2.xaxis.label.set_visible(False) ax2.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax2.legend(handles=[dc, sv], loc=”best”) # define the ax3 subplot ax3 = df3.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax3, color=df3.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax3.set_title(label=”Third-class passengers”, y=1.02) ax3.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax3.xaxis.label.set_visible(False) ax3.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax3.legend(handles=[dc, sv], loc=”best”) # save the bar chart to PDF file plt.savefig(“c:\datafiles\titanic3.pdf”, bbox_inches=”tight”, pad_inches=.5)’; EXECsp_execute_external_script @language=N‘Python’, @script=@pscript; GO |
This example is very similar to the preceding one, except that the data subsets have been updated to return the count column, rather than the age column:
|
df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“count”]] df2=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“count”]] df3=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“count”]] |
You must also update the plot.bar function calls that defines the subplots to reflect that the count column should be used for Y-axis:
|
ax1=df1.plot.bar(x=“gender”,y=“count”,alpha=.8,ax=ax1, color=df1.status.map({“deceased”:“navy”,“survived”:“darkgreen”})) |
Also, be sure to update the Y-axis labels:
|
ax1.set_ylabel(“Total passenger count”,labelpad=10,fontsize=12) |
Other than these changes, the rest of the script is the same. Your charts should now look like those shown in the following figure:
Although the charts are similar to those in the preceding example, the data is much different. However, you can still easily find meaning in that data.
Generating multiple types of bar charts
You can also mix things up, providing charts that reflect both the average ages and passenger counts within one figure. You need only do some rearranging and make some additions, as shown in the following example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
DECLARE@pscriptNVARCHAR(MAX); SET@pscript=N‘ # import Python modules import matplotlib matplotlib.use(“PDF”) import matplotlib.pyplot as plt import matplotlib.patches as ptch import pandas as pd # define the titanic data frame titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0) # 1313 rows titanic = titanic[titanic[“Age”].notnull()] # 756 rows titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”}) for i, row in titanic.iterrows(): if (row[“Age”].is_integer() == False): titanic.drop(i, inplace=True) # 748 rows # define the df data frame based on titanic data df = titanic.groupby([“PClass”, “Sex”, “Survived”], as_index=False).agg({“Age”: [“mean”, “count”]}) df[“Age”] = df[“Age”].round(2) df.columns = [“class”, “gender”, “status”, “age”, “count”] # define the subplot data frames based on df data df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “age”]] df2 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “count”]] df3 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “age”]] df4 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “count”]] df5 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “age”]] df6 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “count”]] # define variables for the legend dc = ptch.Patch(color=”navy”, label=”deceased”) sv = ptch.Patch(color=”darkgreen”, label=”survived”) # set up the subplot structure fig, ((ax1, ax2), (ax3, ax4), (ax5, ax6)) = plt.subplots(nrows=3, ncols=2, sharex=True, figsize=(14,12)) # define the ax1 subplot ax1 = df1.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax1, color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax1.set_title(label=”First-class passengers”, y=1.02) ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax1.xaxis.label.set_visible(False) ax1.set_ylabel(“Average ages”, labelpad=5, fontsize=12) ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax1.legend(handles=[dc, sv], loc=”best”) # define the ax2 subplot ax2 = df2.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax2, color=df2.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax2.set_title(label=”First-class passengers”, y=1.02) ax2.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax2.xaxis.label.set_visible(False) ax2.set_ylabel(“Total passenger count”, labelpad=5, fontsize=12) ax2.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax2.legend(handles=[dc, sv], loc=”best”) # define the ax3 subplot ax3 = df3.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax3, color=df3.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax3.set_title(label=”Second-class passengers”, y=1.02) ax3.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax3.xaxis.label.set_visible(False) ax3.set_ylabel(“Average ages”, labelpad=5, fontsize=12) ax3.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax3.legend(handles=[dc, sv], loc=”best”) # define the ax4 subplot ax4 = df4.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax4, color=df4.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax4.set_title(label=”Second-class passengers”, y=1.02) ax4.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax4.xaxis.label.set_visible(False) ax4.set_ylabel(“Total passenger count”, labelpad=5, fontsize=12) ax4.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax4.legend(handles=[dc, sv], loc=”best”) # define the ax5 subplot ax5 = df5.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax5, color=df5.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax5.set_title(label=”Third-class passengers”, y=1.02) ax5.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax5.xaxis.label.set_visible(False) ax5.set_ylabel(“Average ages”, labelpad=5, fontsize=12) ax5.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax5.legend(handles=[dc, sv], loc=”best”) # define the ax6 subplot ax6 = df6.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax6, color=df6.status.map({“deceased”:”navy”, “survived”:”darkgreen”})) ax6.set_title(label=”Third-class passengers”, y=1.02) ax6.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0) ax6.xaxis.label.set_visible(False) ax6.set_ylabel(“Total passenger count”, labelpad=5, fontsize=12) ax6.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5) ax6.legend(handles=[dc, sv], loc=”best”) # save the bar chart to PDF file plt.savefig(“c:\datafiles\titanic4.pdf”, bbox_inches=”tight”, pad_inches=.5)’; EXECsp_execute_external_script @language=N‘Python’, @script=@pscript; GO |
This time around, the script includes six subsets of data, three specific to age and three specific to passenger count:
|
df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“age”]] df2=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“count”]] df3=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“age”]] df4=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“count”]] df5=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“age”]] df6=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“count”]] |
The idea here is to include each class of service in its own row, giving us a figure with three rows and two columns. To achieve this, you must update the subplots function call to reflect the new structure:
|
fig,((ax1,ax2),(ax3,ax4),(ax5,ax6))=plt.subplots(nrows=3, ncols=2,sharex=True,figsize=(14,12)) |
In addition to the updated nrows and ncols parameter values, the statement also includes six subplot variables, rather than three, with the variables grouped together by row. In addition, the sharey parameter has been replaced with the sharex parameter and the figure size increased.
The next step is to define the six subplots, following the same structure as before, but providing the correct data subsets, subplot variables, column names and labels. The result is a single .pdf file with six charts, as shown in the following figure.
The challenge with this approach is that each chart scales differently. Mixing charts in this way can make it difficult to achieve matching scales. On the plus side, you’re able to visualize a great number of details within a collected set of charts, while still making it possible to quickly compare and understand the data.
The approach taken in this example is not the only way to create these charts. For instance, you might be able to set up a looping structure or use variables for repeated parameter values to simplify the subplot creation, but this approach allows you to see the logic behind each step in the chart-creation process. Like any other aspect of Python, there are often several ways to achieve the same results.
Python’s expansive world
As datasets become larger and more complex, the need is greater than ever for tools that can present the data to various stakeholders in ways that are both meaningful and easy to understand. SQL Server’s support for Python and the R language, along with the analytic and visualization capabilities they bring, could prove an invaluable addition to your arsenal for working with SQL Server data.
When it comes to visualizations in particular, Python has a great deal to offer, and what we’ve covered so far barely scratches the surface. Of course, we could say the same thing about most aspects of Python. It is a surprisingly extensive language that is both intuitive and flexible, and can be useful in a wide range of circumstances. Being able to work with Python within the context of a SQL Server database makes it all the better.
Generating HTML from SQL Server Queries
You can produce HTML from SQL because SQL Server has built-in support for outputting XML, and HTML is best understood as a slightly odd dialect of XML that imparts meaning to predefined tags. There are plenty of edge cases where an HTML structure is the most obvious way of communicating tables, lists and directories. Where data is hierarchical, it can make even more sense. William Brewer gives a simple introduction to a few HTML-output techniques.
Can you produce HTML from SQL? Yes, very easily. Would you ever want to? I certainly have had to. The principle is very simple. HTML is really just a slightly odd dialect of XML that imparts meaning to predefined tags. SQL Server has built-in ways of outputting a wide variety of XML. Although I’ve had in the past to output entire websites from SQL, the most natural thing is to produce HTML structures such as tables, lists and directories.
HTML5 can generally be worked on in SQL as if it were an XML fragment. XML, of course, has no predefined tags and is extensible, whereas HTML is designed to facilitate the rendering and display of data. By custom, it has become more forgiving than XML, but in general, HTML5 is based on XML.
Generating Tables from SQL expressions.
In HTML5, tables are best done simply, but using the child elements and structures so that the web designer has full control over the appearance of the table. CSS3 allows you to specify sets of cells within a list of child elements. Individual TD tags, for example, within a table row (TR) can delimit table cells that can have individual styling, but the rendering of the table structure is quite separate from the data itself.
The table starts with an optional caption element, followed by zero or more colgroup elements, followed optionally by a thead element. This header is then followed optionally by a tfoot element, followed by either zero or more tbody elements or one or more tr elements, followed optionally by a tfoot element, but there can be only one tfoot element.
The HTML5 ‘template’ for tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<table> <caption></caption> <colgroup></colgroup>1..n <thead> <tr> <th></th>1..n </tr>1..n </thead> <tfoot> <tr> <td></td>1..n </tr>1..n </tfoot> <tbody> <tr> <td></td>1..n </tr>1..n </tbody>1..n </table> |
In SQL Server, one can create the XML for a table like this with this type of query which is in the form of a template with dummy data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT (SELECT‘Table I’FORXMLPATH(”),TYPE)AS‘caption’, (SELECT‘first’ASth,‘second’ASthFORXMLraw(‘tr’),ELEMENTS,TYPE)AS‘thead’, (SELECT‘sum’ASth,‘twenty’ASthFORXMLraw(‘tr’),ELEMENTS,TYPE)AS‘tfoot’, (SELECTF.unusAStd,F.duoAStd FROM (VALUES (‘one’,‘two’), (‘three’,‘four’), (‘five’,‘six’), (‘seven’,‘eight’) )F(unus,duo) FORXMLRAW(‘tr’),ELEMENTS,TYPE )AS‘tbody’ FORXMLPATH(”),ROOT(‘table’) |
Which produces (after formatting it nicely) this
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
|
So, going to AdventureWorks, we can now produce a table that reports on the number of sales for each city, for the top thirty cities.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECTCONVERT(NVARCHAR(MAX),(SELECT (SELECT‘Top Thirty Sales per City’FORXMLPATH(”),TYPE)AS‘caption’, (SELECT‘Sales’ASth,‘City’ASthFORXMLRAW(‘tr’),ELEMENTS,TYPE)AS‘thead’, ( SELECTTOP30COUNT(*)AStd, a.City+‘, ‘+sp.Name+‘, ‘+cr.NameAStd FROMSales.SalesOrderHeaderASsoh JOINPerson.AddressASa ONa.AddressID=soh.BillToAddressID JOINPerson.StateProvinceASsp ONsp.StateProvinceID=a.StateProvinceID JOINPerson.CountryRegionAScr ONcr.CountryRegionCode=sp.CountryRegionCode GROUPBYa.City+‘, ‘+sp.Name+‘, ‘+cr.Name ORDERBYCOUNT(*)DESC FORXMLRAW(‘tr’),ELEMENTS,TYPE )AS‘tbody’ FORXMLPATH(”),ROOT(‘table’))); |
I’ve left out the tfoot row because I didn’t need that. Likewise colgroup. I use tfoots mostly for aggregate lines, but you are limited to one only at the end, so it is not ideal for anything other than a simple ‘bottom line’.
When this is placed within and html file, with suitable CSS, it can look something like this
Generating directory lists from SQL expressions.
The HTML is for rendering name-value groups such as dictionaries, indexes, definitions, questions and answers and lexicons. The name-value group consists of one or more names (dt elements) followed by one or more values (dd elements). Within a single dl element, there should not be more than one dt element for each name.
We’ll take as an example an excerpt from the excellent SQL Server glossary
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SELECTF.TermASDT,F.DefinitionASDD FROM ( VALUES (‘cache aging’, ‘The mechanism of caching that determines when a cache row is outdated and must be refreshed’ ), (‘cache client’, ‘A .NET application that uses the Windows Server AppFabric client APIs to communicate with and store data to a Windows Server AppFabric distributed cache system.’ ), (‘cache cluster’, ‘The instantiation of the distributed cache service, made up of one or more instances of the cache host service working together to store and distribute data. Data is stored in memory to minimize response times for data requests. This clustering technology differs from Windows Clustering.’ ), (‘cache invalidation’, ‘The process of flagging an object in the cache so that it will no longer be used by any cache clients. This occurs when an object remains in cache longer than the cache time-out value (when it expires).’ ), (‘cache item’, ‘An object that is stored in the cache and additional information associated with that object, such as tags and version. It can be extracted from the cache cluster using the GetCacheItem client API.’ ), (‘cache notification’, ‘An asynchronous notification that can be triggered by a variety of cache operations on the cache cluster. Cache notifications can be used to invoke application methods or automatically invalidate locally cached objects.’ ), (‘cache operation’, ‘An event that occurs on regions or cached items that can trigger a cache notification.’ ), (‘cache port’, ‘A TCP/IP port used by cache hosts to transmit data to and from the cache clients. The port number used for the cache port can be different on each cache host. These settings are maintained in the cluster configuration settings.’ ), (‘cache region’, ‘A container of data, within a cache, that co-locates all cached objects on a single cache host. Cache Regions enable the ability to search all cached objects in the region by using descriptive strings, called tags.’ ), (‘cache service’, ‘The distributed, in-memory caching solution that enables users to build highly scalable and responsive applications by bringing data closer to end users.’ ), (‘cache tag’, ‘One or more optional string-based identifiers that can be associated with each cached object stored in a region. Regions allow you to retrieve cached objects based on one or more tags.’ ), (‘cache-aside programming pattern’, ‘A programming pattern in which if the data is not present in the cache, the application, not the distributed cache system, must reload data into the cache from the original data source.’ ), (‘cache-enabled application’, ‘An application that uses the Windows Server AppFabric cache client to store data in cache on the cache cluster.’ ) )F(Term,Definition) FORXMLRAW(”),ROOT(‘DL’),ELEMENTS,TYPE; |
This produces a directory list which can be rendered as you wish
Generating hierarchical lists from SQL expressions.
HTML Lists represent probably the most useful way of passing simple hierarchical data to an application. You can actually use directories (DLs) to do this for lists name-value pairs and even tables for more complex data. Here is a simple example of a hierarchical list, generated from AdventureWorks. You’d want to use a recursive CTE for anything more complicated.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECTCONVERT(NVARCHAR(MAX), (SELECTf.[Group]ASli, (SELECTg.NameASli FROMSales.SalesTerritoryg WHEREg.[Group]=f.[Group] FORXMLRAW(”),ROOT(‘ul’),ELEMENTS,TYPE ) FROMSales.SalesTerritoryf GROUPBYf.[Group] FORXMLRAW(”),ROOT(‘ul’),ELEMENTS,TYPE ) ); |
…giving…
Conclusions
There are quite a few structures now in HTML5. Even the body tag has subordinate header, nav, section, article, aside, footer, details and summary tags. If you read the W3C Recommendation it bristles with good suggestions for using markup to create structures. The pre tag can use child code, samp and kbd tags to create intelligent formatting. Data in SQL Server can easily generate this sort of structured HTML5 markup. This has obvious uses in indexes, chaptering, glossaries as well as the obvious generation of table-based reports. There is quite a lot of mileage in creating HTML from SQL Server queries
Demand Generation 101: 7 Tactics For Generating High Quality Leads
Not all nurtured leads are always going to be sales ready. But if they aren’t ready to buy now, that doesn’t mean that they won’t ever buy. Sales can return those leads back to marketing for further nurturing. Marketing can re-engage those leads, and try to again reach that benchmark level of interaction to return them back to sales for further conversations.
3 parts to demand generation programs
There are three main parts to successful demand generation programs: the players, the methods, and the goals.
- The players
Demand gen should be a collaborative activity between your company’s sales and marketing teams. Some of the players you can expect to see have job titles such as demand gen marketer, marketing operations, or marketing technologist. On the sales side typical titles include sales operations or sales managers. “The sales people play a key role in helping to define these processes and maintain an open line of communication with the marketing team members, which really makes a huge difference,” Rosin said.
- The methods
Demand generation uses coordinating multi-tiered marketing activities to identify and engage buyers through targeted inbound and outbound activities. There are seven key tactics or methods of demand generation: web insights and inbound marketing, content marketing, social media engagement, lead nurturing, lead scoring, measuring and optimization, and sales and marketing alignment. We’ll dive deeper into each of these methods in a bit.
Your goal should be to have a well-oiled demand generation process that improves lead quality, accelerates the buying cycle, and improves conversion rates from your initial inquiries to qualified opportunities.
“The most important goal, however, is to generate higher revenue from your marketing source leads,” said Janelle Johnson, Act-On’s Senior Director for Global Segment Marketing. “What it all boils down to is quality, not quantity.”
Pumping your pipeline full of high volume with low quality leads, isn’t gonna help your company, especially in the long run. And it can definitely deteriorate your relationship with your sales team. You really have to focus your time and energy on generating those high quality leads that will convert in order to truly achieve demand generation success.
3 challenges for today’s marketer
Generating those high quality leads is made tougher by three challenges for today’s marketer: empowered buyers, high expectations, and pressure to perform.
- Empowered buyers
Consumers are now in the driver’s seat in their buying journey. In fact, 40 percent of buyers wait longer than they did in the past to initiate contact with a vendor, and buyers typically get 60 percent through the buying journey before they engage with a salesperson. Today’s marketers have to find a way to position their companies as trusted advisors to buyers as they move along their journey. With 58 percent of buyers spending more time researching their options than they did in the past, it’s imperative that you engage with your buyers by building relationships and trust. Because if you don’t, you better believe that your competitors will. Read about proven strategies, in this free eBook: 10 Ways to Nurture the Buyer’s Journey.
- Higher expectations
With so much access to information and choices, buyers expect more from the companies with whom they interact. Today’s buyers live in an always-on world, filled with instantly available, highly personalized apps, messages, offers, and services. That’s pretty incredible, but it also challenges us as marketers to anticipate the needs of our consumers and deliver thoughtful content related to their needs. Cookie-cutter marketing content and one-size-fits all communication does not work in this new environment. Check out this infographic to see the new buyer’s funnel.
- Pressure to perform
With all the technology available at our fingertips, we can now tie most of our marketing efforts to revenue. It’s becoming more and more common for marketing to have a revenue goal, just like sales. A well-planned demand gen process can address these challenges by offering an efficient and reliable process to identify and engage these empowered buyers and turn quality leads into revenue that’s measured.
The Simple, 3-Step Process to Generating Leads and Revenue through Social Media Channels
Social media is a huge opportunity for businesses to spread their message to a large number of people in a relatively short amount of time. However, businesses now pay for exposure through sponsored posts and ads that don’t always produce the results they hope for. Facebook click rates are unimpressive, LinkedIn sponsored posts are quite expensive, and tweets get lost in the mix.
So first, how do you drive more traffic to your site through social channels? And second, how do you convert these visitors who do end up on your site into email leads and customers? Below, I’ve laid out a straightforward process that will generate tangible results from your social media marketing campaigns.
Step 1: Give People a Reason to Visit Your Site
Before you can acquire new leads, you need to get people onto your website. Social channels should be used as a way to get your marketing message and content in front of a larger audience. The end goal here is to provide a solid reason for people to click on your post/ad and get them to land on your site. Let’s look at a few ways that you can drive people to your website.
Actionable Content
Successful social posts provide useful information that individuals and businesses can actually use. “How to” guides and lists of tips will drive the most social engagement and will entice visitors to your website, giving you the opportunity to convert them into new leads. Keep attention span in mind when developing content. Whether it’s short or long form content, make sure it’s scannable and provides value to even the casual reader who’s just skimming subheads. Craft intriguing headlines and use examples whenever applicable.
Webinars
Webinars are a fantastic way to appeal to high-value leads on social platforms. Typically, you can ask for additional information besides an email address and gain insight on what benefit your products and services can provide them. Since this is a long form of content, develop actionable titles that individuals will want to engage with. Instead of using “Best Practices for Marketing Overlays” try something like “10 Tips to make your Marketing Overlays Pop!”
Contests
This one is a no-brainer for any business that wants to see incredible email opt-in results. Select an item of value (this could be a product or service package). Then prompt website visitors to submit their email for an opportunity to win that item. The great thing about this tactic is it’s very appealing on social platforms. The offer is attractive and the process to enter is straightforward for individuals.
In this ecommerce example, The Fifth Watches offers visitors a package of goodies upon arrival.
Sales/Discounts
Running a sale or offering a special discount? Advertise it on social media. According to Forrester Research, 100% of B2B decision makers use social media for work. This is a tremendous opportunity to sway these decision makers with attractive savings. Sometimes it just takes a bit of an extra push to get someone’s business and sales/discounts are a proven way to do just that.
Step 2: Drive people from social channels to relevant pages on your website
This step has to do with aligning the proper path for an individual to arrive on your site, making it more likely that they will perform the desired action that you have in mind. It starts with your social post, company profile, or advertisement. Clearly state what you are posting about and why it is beneficial. Headlines like “How to Generate More Revenue with Transactional Emails” or “5 Simple Email Headline Tweaks that will Increase Open Rates” tend to do better in social posts because they are actionable and offer an immediate benefit. Also, include an image within your social post. Images help your post stand out and will increase engagement (as noted in this article from Social Media Examiner).
Marketing Land does an excellent job with their social posts by crafting enticing headlines and also including an image. These two aspects can rapidly increase social post engagement. Here’s how they execute on Facebook:
…and the same post looks much the same on Twitter:
…and in both cases the call to action takes you to their website, and very obvious encouragement to sign up:
Next, choose where you want to send visitors once they’ve clicked on the ad or post. Send them to a page that‘s relevant to what they clicked on. If they clicked on an ad for a free eBook, then send them to a landing page where they can get the eBook. Make it easy for visitors to find exactly what they want and expect.
Step 3: Engage visitors in an effective manner
If you want to convert visitors then you have to be the proactive one. According to experts at Chartbeat, 55% of visitors spend fewer than 15 seconds on your website. People have short attention spans and can leave your site in the blink of an eye, so capitalize on the opportunity that’s at hand! Here are some effective ways to engage onsite visitors in order to drive conversions.
Simple Landing Pages
Landing pages are obviously a key component of lead generation, yet many businesses are still missing the boat. The first step of building a landing page is to determine what its sole purpose is. If you want to get visitors to fill out a form to receive an eBook, then design this page to drive this action. There is no added value by including distracting links to other areas of your website. Keep it simple by creating a clear path for visitors to sign up and download your eBook.
Here’s a fantastic landing page example from ZenPayroll. The form is front and center allowing visitors to sign up for free. Additional information like testimonials, features, and pricing is accessible lower down on the page so it does not distract the visitor from the initial sign up form.
Overlays
The most effective way to engage individuals on your site is with overlays. Overlays (whether over video, a web page, or a graphic), or float your message over the top of prime real estate to grab people’s attention and provide businesses with many unique ways to engage incoming traffic. Perhaps the largest advantage that overlays provide marketers is the ability to target segments of traffic with specific messages and offers. This allows you to hit visitors with more relevant information, convert more incoming social traffic, and maximize ROI from social PPC campaigns.
The Fifth Watches uses an overlay to collect the emails from visitors who enter the contest giveaway. Since they only sell on specific days each month, they rely heavily on reaching customers and leads through email marketing. The use of overlays has enabled them to collect an average of 500 new email subscribers on a daily basis!
Side Tabs
For a more subtle way to engage visitors, consider using a side tab. Visitors can click a side tab which would then open an overlay where they can opt-in easily. This can be a fun way to market to website visitors if you get a little creative. Here’s a tasty side tab for a promotion from the web’s top bacon retailer, Bacon Freak:
The rasher of bacon slides in on the right, and says “Click for a tasty offering.” If you click, you’re served a tempting offer:
Exit Offers
Exit offers are an incredibly effective tactic for acquiring more email subscribers, decreasing bounce rates, and saving shopping cart abandonment. Engage visitors right before they leave your site for another chance for a conversion. To prevent cart abandonment directly, set up an exit offer for shoppers that are directly leaving from your cart and checkout pages.
Bill for First Lady uses this hilarious exit offer to prevent shoppers from leaving with items in their cart. By making the promo code available, they can directly drive purchases from the cart page.
Social channels are beneficial to businesses by providing ways to reach a lot of people. However, the inefficiencies with social media marketing make it difficult to create engaging relationships. By focusing on converting social traffic into email leads, you open a direct channel to market to these individuals … which increases the likelihood of getting their business.
To learn more about how to create revenue from your social media campaigns, take a look at our eBook Likes are Great. Leads are Better. You’ll more about the individual twists each platform requires, how to budget your campaigns, and how to segment and nurture.
Related Blog Posts
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.